Category Archives: ssms

How to get the size of every index in a SQL Server Instance

The code below will run against every online database (excluding the system databases bar the master database) and return the size of each index in each database. Knowing these values is especially important as it is recommended that the free space available on disk should be at least 1.5 times the size of the largest index. This is only one guideline regarding disk space however and use cases vary from database to database.

The output will include:

  • Database Name
  • Schema Name
  • Table Name
  • Index Id
  • Index Name
  • Index Size Mb

You can uncomment the last three lines to focus on nonclustered indexes.

SET NOCOUNT ON;

DECLARE @Database TABLE (DbName SYSNAME);
DECLARE @IndexStats TABLE (
	ServerName SYSNAME
	,DbName SYSNAME
	,SchemaName SYSNAME
	,TableName SYSNAME
	,IndexId INT
	,IndexType VARCHAR(12)
	,IndexName SYSNAME
	,IndexSizeMb INT
	);
DECLARE @DbName AS SYSNAME;
DECLARE @Sql AS VARCHAR(MAX);

SET @DbName = '';

INSERT INTO @Database (DbName)
SELECT NAME
FROM sys.databases
WHERE NAME NOT IN (
		'tempdb'
		,'msdb'
		,'model'
		)
	AND state_desc = 'ONLINE'
ORDER BY NAME ASC;

WHILE @DbName IS NOT NULL
BEGIN
	SET @DbName = (
			SELECT MIN(DbName)
			FROM @Database
			WHERE DbName > @DbName
			);
	SET @Sql = 'USE ' + QUOTENAME(@DbName) + ';	
	SELECT @@ServerName AS ServerName
	,''' + @DbName + ''' AS ''DbName'' 
	,OBJECT_SCHEMA_NAME(i.OBJECT_ID) AS SchemaName
	,OBJECT_NAME(i.OBJECT_ID) AS TableName
	,i.index_id AS IndexId
	,CASE WHEN i.index_id > 1 THEN ''Nonclustered'' WHEN i.index_id = 1 THEN ''Clustered'' ELSE ''Heap'' END AS IndexType
	,CASE WHEN i.NAME IS NULL THEN ''No Name'' ELSE i.Name END AS IndexName
	,(8 * SUM(a.used_pages)/1024) AS ''IndexSizeMb''
FROM sys.indexes AS i
JOIN sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID
	AND p.index_id = i.index_id
JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
WHERE a.used_pages > 0
GROUP BY i.OBJECT_ID
	,i.index_id
	,i.NAME
ORDER BY IndexSizeMb DESC; 
'

	INSERT INTO @IndexStats
	EXEC (@Sql);
END

SELECT ServerName
	,DbName
	,SchemaName
	,TableName
	,IndexId
	,IndexType
	,IndexName
	,IndexSizeMb
FROM @IndexStats
--WHERE IndexType = 'Nonclustered'
--AND IndexSizeMb > 0
--ORDER BY IndexSizeMb DESC;

 

How to tell if Virtual Machine backups are running full database backups on a SQL Server instance

Some Virtual Machine backup solutions use the SQL Server VSS writer service to make SQL Server database backups as part of the VM backup.

You may not have had visibility on this process so to confirm if this is happening in your environment open up SQL Server Management Studio (SSMS) then in Object Explorer right click on the instance name, click on new query and run the script below. 

This query looks for entries in the physical_device_name field of msdb.dbo.backupmediafamily that are not directory paths.

SELECT @@SERVERNAME AS ServerName
	,bs.backup_set_id AS BackupSetId
	,bs.database_name AS DbName
	,bs.backup_start_date AS BackupStartDate
	,bs.backup_finish_date AS BackupFinishDate
	,CAST(CAST(bs.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS [Size]
	,bs.[type] AS BackupType
	,CASE bs.[type]
		WHEN 'D'
			THEN 'Full Backup'
		WHEN 'I'
			THEN 'Differential Backup'
		WHEN 'L'
			THEN 'TLog Backup'
		WHEN 'F'
			THEN 'File or filegroup'
		WHEN 'G'
			THEN 'Differential file'
		WHEN 'P'
			THEN 'Partial'
		WHEN 'Q'
			THEN 'Differential Partial'
		END AS BackupTypeDescription
	,bmf.physical_device_name AS BackupFilePath
	,CAST(bs.first_lsn AS VARCHAR(50)) AS FirstLogSeqNo
	,CAST(bs.last_lsn AS VARCHAR(50)) AS LastLogSeqNo
FROM msdb.dbo.backupset AS bs WITH (NOLOCK)
INNER JOIN msdb.dbo.backupmediafamily AS bmf WITH (NOLOCK) ON bs.media_set_id = bmf.media_set_id
WHERE bs.[type] = 'D'
	AND bmf.physical_device_name NOT LIKE '%\%'
ORDER BY bs.database_name ASC
	,bs.backup_set_id ASC;

 

For the field BackupFilePath if you see GUID looking entries like {AQ8F5957-2804-4X05-9EE5-109EB87EBCAB}5 the VM backup is probably running full database backups on the SQL Server.

It might be time to review your recovery strategy as the VM backups could be covering the same ground as the SQL Server backups creating a lot of expensive redundancy due to unnecessary replication of work.