The last two articles help you identify where the issue is? this one will take you through step by step process of what one may do to regain the space occupied by the databases. Hence freeing some resources on the SQL server and improving the performance too.
Identify and clean Log/Temp Tables
Truncate/Delete the log and temp tables after you identify, what you can clean.
Shrink the Log File
In most cases, we do not need the transaction logs forever. It is a good idea to shrink the log file or even drop the transaction logs, depending on the intent of the database. it is really simple with DBCC command
ALTER DATABASE '<Logical Name of a log/LDF File>' SET RECOVERY SIMPLE WITH NO_WAIT DBCC SHRINKFILE ('<Logical Name of a log/LDF File>', 1) ALTER DATABASE '<Logical Name of a log/LDF File>' SET RECOVERY FULL WITH NO_WAIT
However, this will just do it for one DB at once, if your server has 100 Dbs, then you will have to spare several hours to find the logical name of each DB file and then use DBCC command. so let’s put this to an automated way of finding the logical name for each database and then shrink their LDF(log) files. Let’s use a cursor to perform this Operation.
DECLARE @dbName nvarchar(200), @logName nvarchar(200); DECLARE @iCnt int DECLARE @sqlCommand varchar(1000) DECLARE dbLog_Cursor CURSOR FOR SELECT sd.NAME as dbName ,smf.NAME as logName FROM sys.databases sd INNER JOIN sys.master_files smf ON sd.database_id = smf.database_id WHERE smf.type_desc IN ('LOG') and sd.NAME Not IN('model','tempdb','master','msdb') ORDER BY smf.size DESC; OPEN dbLog_Cursor FETCH NEXT FROM dbLog_Cursor INTO @dbName, @logName set @iCnt = 0 WHILE @@FETCH_STATUS = 0 AND @iCnt < 20 BEGIN set @iCnt = @iCnt + 1 print 'Truncating Log for Database: ' + @dbName SET @sqlCommand = 'USE ' + @dbName SET @sqlCommand = @sqlCommand + ' ALTER DATABASE ' + @dbName + ' SET RECOVERY SIMPLE WITH NO_WAIT' --SET @sqlCommand = @sqlCommand + ' DBCC SHRINKFILE ( ' + @logName + ' , TRUNCATEONLY)' SET @sqlCommand = @sqlCommand + ' DBCC SHRINKFILE ( ' + @logName + ' , 1)' SET @sqlCommand = @sqlCommand + ' ALTER DATABASE ' + @dbName + ' SET RECOVERY FULL WITH NO_WAIT' EXEC (@sqlCommand) FETCH NEXT FROM dbLog_Cursor INTO @dbName, @logName END CLOSE dbLog_Cursor; DEALLOCATE dbLog_Cursor;
You may exclude the databases, under “Not in” Clause. Currently, I am excluding the default system DBs. you may focus on specific DBs to be targeted using
Shrink Files and Recover Free Space
Now, we will use DBCC SHRINKFILE command to shrink the file. there are two approaches. First, one is recommended if you are really sure that no one is using the DB and you are ok to lock the DB while it is shrinking, as it may take longer for large DBs. You may use this command in the cursor written above to target all DBs.
DBCC SHRINKFILE('<Logical Name of a Data/MDF File>',0);
There is no reason to try to shrink the file in one operation. You can do it in multiple operations. Also, you can pause between each shrink step to let other processes work with the database. Doing it this way, you minimize blocking and logging, and the CPU and I/O can be hidden in the noise of other work.
USE YourDB GO DECLARE @FileName sysname = N'YourDBLogicalFileNameForDataFile'; DECLARE @TargetSize INT = (SELECT 1 + size*8./1024 FROM sys.database_files WHERE name = @FileName); DECLARE @Factor FLOAT = .999; WHILE @TargetSize > 0 BEGIN SET @TargetSize *= @Factor; DBCC SHRINKFILE(@FileName, @TargetSize); DECLARE @msg VARCHAR(200) = CONCAT('Shrinking Done. Target Size: ', @TargetSize, ' MB. Timestamp: ', CURRENT_TIMESTAMP); RAISERROR(@msg, 1, 1) WITH NOWAIT; WAITFOR DELAY '00:00:01'; END;
Just a tip, you may use the following command to see the available space on any DB. Running this before and after shrinking will tell you how much space have you regained.
SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB FROM sys.database_files;
Ensure that the DBs (big ones) don’t have the unused space.
- Right Click the DB within SQL Server Management Studio
- Task> Shrink > Files
Set the “Shrink File to:” Minimum (3 in this case) and click ok.