SQL Server Scripts

BAU Tasks

Disk Space Issues?

Check freespace overall, and drill into details


--Database MountPoint Details 
SELECT DISTINCT  d.name AS DBName ,        
        volume_mount_point AS [Volume_MountPoint],
        ((total_bytes/1024)/1024)/1024 AS TotalSpaceGB ,
        ((total_bytes-available_bytes)/1024)/1024/1024 AS UsedSpaceGB ,
        ((available_bytes/1024)/1024)/1024 AS AvailableSpaceGB ,
        (((available_bytes/1024)/1024)/1024*100)
        /(((total_bytes/1024)/1024)/1024) AS [Free%]        
FROM    sys.master_files AS f
        CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id)
        INNER JOIN sys.databases d ON f.database_id = d.database_id
WHERE   d.NAME = DB_NAME(DB_ID())

--Database Files By MountPoint Details
SELECT  (SELECT cob FROM dbo.appdate) [Date],
		d.name AS DBName , 
		fg.name AS FileGroup,   
        f.name AS FileName ,
        (CONVERT(BIGINT,df.size)*8/1024)/1024 as FileSizeGB,
        ((CONVERT(BIGINT,df.size)*8/1024) - (FILEPROPERTY(df.name, 'SpaceUsed') * 8/1024))/1024 As FileFreeSpaceGB,  
        volume_mount_point [Volume_MountPoint],
        ((total_bytes / 1024 ) / 1024 ) / 1024 AS TotalSpaceGB ,
        ((available_bytes / 1024 ) / 1024 ) / 1024 AS AvailableSpaceGB ,
        (((available_bytes / 1024 ) / 1024 ) / 1024 * 100 )
        /(((total_bytes / 1024 ) / 1024 ) / 1024 ) AS [Free%]
FROM    sys.master_files AS f
        CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id)
        INNER JOIN sys.databases d ON f.database_id = d.database_id
        INNER JOIN sys.database_files df ON  f.name = df.name
        INNER JOIN sys.filegroups fg ON df.data_space_id = fg.data_space_id
WHERE   d.NAME = DB_NAME(DB_ID())
ORDER BY 7,5 DESC

--Database Objects By Filegroup Details
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT  
		(SELECT cob FROM dbo.appdate) [Date],		
		FILEGROUP_NAME(a.data_space_id) AS FileGroupName ,
		(SELECT SCHEMA_NAME(schema_id) FROM sys.objects z WHERE z.object_id = p.object_id) [SchemaName],
        OBJECT_NAME(p.object_id) AS TableName ,
        i.name AS ClusteredIndexName ,
        (a.total_pages/128)/1024 AS TotalTableSizeGB ,
        (a.used_pages/128)/1024 AS UsedSizeGB ,
        (a.data_pages/128)/1024 AS DataSizeGB
FROM    sys.allocation_units AS a
INNER JOIN sys.partitions AS p ON a.container_id = CASE
                                                      WHEN a.type IN (1, 3 )
                                                      THEN p.hobt_id
                                                      ELSE p.partition_id
                                                      END                                                     
LEFT JOIN sys.indexes AS i ON i.object_id = p.object_id AND i.index_id = p.index_id
--WHERE FILEGROUP_NAME(a.data_space_id) = 'Data'
ORDER BY 6 DESC


Shrink DB Files

Incremental shrink of db files

  
--Shrink files by 1GB 
DECLARE
 @FileName VARCHAR(50) = '',  
 @ShrinkSizeMb INT = 1024,  
 @Debug BIT = 0  

DECLARE @usedspace INT = 0 ,  
  @currentspace INT = 0,  
  @freespace INT = 0,  
  @targetspace INT = 0  
    
SELECT @usedspace = size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0,  
    @currentspace = size/128.0   
FROM sys.database_files  
WHERE name = @FileName  
  
SET @freespace = @currentspace - @usedspace  
  
PRINT 'Current Space: ' + CONVERT(VARCHAR,@currentspace)  
PRINT 'Used Space: ' + CONVERT(VARCHAR,@usedspace)  
PRINT 'Free Space: ' + CONVERT(VARCHAR,@freespace)  
  
WHILE @freespace > @ShrinkSizeMb  
BEGIN  
  
SET @targetspace = @currentspace - @ShrinkSizeMb  
PRINT 'Shrink ' + @FileName + ' by ' + CONVERT(VARCHAR,@ShrinkSizeMb/1024) + 'GB'  
   
DECLARE @xSQL VARCHAR(MAX)  
SET @xSQL = 'DBCC SHRINKFILE ('''+ @FileName + ''',' + CONVERT(VARCHAR,@targetspace) + ')'  
PRINT @xSQL  
  
IF @debug = 0   
BEGIN  
 PRINT 'Current Space: ' + CONVERT(VARCHAR,@currentspace)  
 PRINT 'Used Space: ' + CONVERT(VARCHAR,@usedspace)  
 PRINT 'Free Space: ' + CONVERT(VARCHAR,@freespace)  
     
 PRINT 'Shrink - Started: @FileName=' + @FileName + ', @ShrinkSizeMb=' + CONVERT(VARCHAR,@ShrinkSizeMb) + ', @TargetSpace=' +  CONVERT(VARCHAR,@targetspace)  
  
 EXEC (@xSQL)   
   
 PRINT 'Shrink - Started: @FileName=' + @FileName + ', @ShrinkSizeMb=' + CONVERT(VARCHAR,@ShrinkSizeMb) + ', @TargetSpace=' +  CONVERT(VARCHAR,@targetspace)  
       
END   
    
SELECT @usedspace = size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0,  
    @currentspace = size/128.0   
FROM sys.database_files  
WHERE name = @FileName  
  
SET @freespace = @currentspace - @usedspace  
  
END   
  
PRINT  @FileName + ' Shrink completed Successfully'  


Backup DB

Backup Full/Differential/logs

  
Backup Script here


Restore DB

Restore Full, Differential, logs

  
Restore Script here