Category Archives: MSSQL

MSSQL How long will data be in cache/buffer

Leave a reply

How fast is data leving the cache, when it not access again.

With this query, you can see how fast data will get expire, and overwritten by new data in the cache.

SELECT  @@servername AS INSTANCE
,[object_name]
,[counter_name]
, UPTIME_MIN = CASE WHEN[counter_name]= 'Page life expectancy'
          THEN (SELECT DATEDIFF(MI, MAX(login_time),GETDATE())
          FROM   master.sys.sysprocesses
          WHERE  cmd='LAZY WRITER')
      ELSE ''
END
, [cntr_value] AS PLE_SECS
,[cntr_value]/ 60 AS PLE_MINS
,[cntr_value]/ 3600 AS PLE_HOURS
,[cntr_value]/ 86400 AS PLE_DAYS
FROM  sys.dm_os_performance_counters
WHERE   [object_name] LIKE '%Manager%'
          AND[counter_name] = 'Page life expectancy'

Witch the query you can see how long time data will be in the cache/buffer.

MSSQL – See buffer hit ratio

Leave a reply

Whit this code, you can see how much of query in percent at this moment is reading from the cache/buffer.

DECLARE @value numeric(25, 2), @basevalue numeric(25, 2)

declare @Cachehitratio real

set @Cachehitratio = null

--Cache hit ratio
SELECT @value = cntr_value FROM master..sysperfinfo (nolock) 
WHERE counter_name = 'Buffer cache hit ratio'
SELECT @basevalue = cntr_value FROM master..sysperfinfo (nolock) 
WHERE counter_name = 'Buffer cache hit ratio base'
set @Cachehitratio= (@value / @basevalue) *100

select @Cachehitratio

See if a database is using Enterprise features of an Microsoft SQL server.

Leave a reply

Run this command to see if any database on the SQL Server is using enterprise features

IF OBJECT_ID('tempdb.dbo.##enterprise_features') IS NOT NULL
  DROP TABLE ##enterprise_features

CREATE TABLE ##enterprise_features
  (
     dbname       SYSNAME,
     feature_name VARCHAR(100),
     feature_id   INT
  )

EXEC sp_msforeachdb
N' USE [?] 
IF (SELECT COUNT(*) FROM sys.dm_db_persisted_sku_features) >0 
BEGIN 
   INSERT INTO ##enterprise_features 
    SELECT dbname=DB_NAME(),feature_name,feature_id 
    FROM sys.dm_db_persisted_sku_features 
END '
SELECT *
FROM   ##enterprise_features

 

Downgrade an SQL Server from Enterprise to Standard – Disable compression

Leave a reply

If you got an error about compression, when trying to attach the database from an enterprise version to a standard version. You will have to attach the database to an enterprise database, and remove the compression first.

The error:

sg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.
Msg 909, Level 21, State 1, Line 2
Database 'abc' cannot be started in this edition of SQL Server because part or all of object 'def' is enabled with data compression or vardecimal storage format. Data compression and vardecimal storage format are only supported on SQL Server Enterprise Edition.
Msg 933, Level 21, State 1, Line 2
Database 'abc' cannot be started because some of the database functionality is not available in the current edition of SQL Server.

The SQL Command to run to disabled compression. It should be run on a query on the database with compression.
(Baware of the database will then fill something extra on the storage, so keep in mind that you need to have space available for that.)

SELECT DISTINCT 'ALTER TABLE [' + SCHEMA_NAME(schema_id) + '].[' + NAME + '] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);'
FROM sys.partitions p
join sys.objects o
on p.object_id = o.object_id
WHERE o.TYPE = 'u'
and data_compression_desc != 'NONE'
UNION
SELECT 'ALTER INDEX ALL ON [' + SCHEMA_NAME(schema_id) + '].[' + NAME + '] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);'
FROM sys.partitions p
join sys.objects o
on p.object_id = o.object_id
WHERE o.TYPE = 'u'
and data_compression_desc != 'NONE'

If the database have compression it will output with multiple SQL commands. Run these command against you database aswell

Example: (You should run what you rnu of the script output)

ALTER INDEX ALL ON [dbo].[MSSBatchHistory] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);
ALTER INDEX ALL ON [dbo].[MSSChangeLogCookies] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);
ALTER INDEX ALL ON [dbo].[MSSCrawlDeletedURL] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);
ALTER INDEX ALL ON [dbo].[MSSCrawlDeletedURLLog] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);
ALTER INDEX ALL ON [dbo].[MSSCrawlHistoryLocal] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);
ALTER INDEX ALL ON [dbo].[MSSCrawlReportCrawlErrors] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);
ALTER INDEX ALL ON [dbo].[MSSCrawlURL] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);
ALTER INDEX ALL ON [dbo].[MSSCrawlURLLog] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);
ALTER INDEX ALL ON [dbo].[MSSCrawlURLReport] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);
ALTER INDEX ALL ON [dbo].[MSSDocIDsAvailable] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);
ALTER INDEX ALL ON [dbo].[MSSDocIDsDeleted] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);
ALTER INDEX ALL ON [dbo].[MSSNextDocID] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);
ALTER TABLE [dbo].[MSSBatchHistory] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);
ALTER TABLE [dbo].[MSSChangeLogCookies] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);
ALTER TABLE [dbo].[MSSCrawlDeletedURL] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);
ALTER TABLE [dbo].[MSSCrawlDeletedURLLog] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);
ALTER TABLE [dbo].[MSSCrawlHistoryLocal] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);
ALTER TABLE [dbo].[MSSCrawlReportCrawlErrors] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);
ALTER TABLE [dbo].[MSSCrawlURL] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);
ALTER TABLE [dbo].[MSSCrawlURLLog] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);
ALTER TABLE [dbo].[MSSCrawlURLReport] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);
ALTER TABLE [dbo].[MSSDocIDsAvailable] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);
ALTER TABLE [dbo].[MSSDocIDsDeleted] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);
ALTER TABLE [dbo].[MSSNextDocID] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);

 

MSSQL Free Space in Databases files

Leave a reply

To get a list of the databases files, and the current disk size in MB, and the Free Space in MB in each fil, you can use this commands

USE [DATABASE_NAME]

SELECT DB_NAME() AS DbName, 
name AS FileName, 
size/128.0 AS CurrentSizeMB, 
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB 
FROM sys.database_files;

If you need a overview of all yours database this TSQL can be used

CREATE TABLE #FileSize
(dbName NVARCHAR(128), 
    FileName NVARCHAR(128), 
    type_desc NVARCHAR(128),
    CurrentSizeMB DECIMAL(10,2), 
    FreeSpaceMB DECIMAL(10,2)
);
    
INSERT INTO #FileSize(dbName, FileName, type_desc, CurrentSizeMB, FreeSpaceMB)
exec sp_msforeachdb 
'USE [?]; 
 SELECT DB_NAME() AS DbName, 
        name AS FileName, 
        type_desc,
        size/128.0 AS CurrentSizeMB,  
        size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128.0 AS FreeSpaceMB
FROM sys.database_files
WHERE type IN (0,1);';
    
SELECT * 
FROM #FileSize
WHERE dbName NOT IN ('distribution', 'master', 'model', 'msdb');
    
DROP TABLE #FileSize;

Shring SQL log file

Leave a reply

USE [TestDb]
GO
ALTER DATABASE [TestDb] SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE([TestDb_Log], 1)
ALTER DATABASE [TestDb] SET RECOVERY FULL WITH NO_WAIT
GO

 

Where TestDB, is the database name, and TestDB_Log is the SQL Log file name.