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.

1. Right Click the DB within SQL Server Management Studio
2. Task> Shrink > Files

Set the “Shrink File to:” Minimum (3 in this case) and click ok.