Microsoft SQL Server Database and DB log File Shrinking in one shot

Microsoft SQL Server Database and DB log File Shrinking in one shot

Most of the time  our database server use to go out of memory, whenever we restore Prod copy on Dev, then we use to do shrinking of log files and Database, doing this one by one is big task. Then I thought of doing this in one shot, here I am sharing a SQL code for the same.

I have used [sysdatabases] and [master_files] tables to get required metadata, and “while loop” for repeating DBCC Shrink for all databases, we can even do this using Courser but I prefer While loop in simple cases.

----------------------------------------------------------------------------

--#1 Get all Database names, Log file names and metadata by this SQL

----------------------------------------------------------------------------

select db.name as DBName,db.[dbid],df.name as DBFileName,df.type_Desc,df.State_desc

into #DBNames from [master].[sys].[sysdatabases] db inner join

[master].[sys].[master_files] df on df.database_id = db.[dbid]

and db.name not in ('master','tempdb','model','msdb') and df.State_desc='ONLINE'

-- Sample Result

/*

DBName        dbid   DBFileName           type_Desc    State_desc

-----------------------------------------------------------

AccountsDW    5             AccountsDW_Data      ROWS         ONLINE

AccountsDW    5             AccountsDW_log       LOG          ONLINE

SalesMaster   6             SalesMaster_Data ROWS             ONLINE

SalesMaster   6             SalesMaster_Log      LOG          ONLINE

*/

----------------------------------------------------------------------------

--#2 Declare required variables

----------------------------------------------------------------------------

Declare @DBID int =0, @MaxDBID int =0,@MinDBID int =0

Declare @SQL varchar(max)='',@DBName varchar(200),@Filename varchar(100)=''

----------------------------------------------------------------------------

--#3 Take Min Max DB id for further looping logic

----------------------------------------------------------------------------

Select @MinDBID=Min([dbid]),@MaxDBID=Max([dbid]) from #DBNames

-- Setting Starting point from Minimun DBID

Set @DBID = @MinDBID

----------------------------------------------------------------------------

--#4 Take DB name, log file name for shrinking in loop.

----------------------------------------------------------------------------

---- Here i have used 'LOG' in where condition because we need Log file name in ShringFile() function .

---- and Databse name for ShringDatabase() Function.

Select @DBName = DBName, @Filename=DBFileName from #DBNames where [dbid] = @MinDBID and type_Desc = 'LOG'

----------------------------------------------------------------------------

--#5 Loop for all databases

----------------------------------------------------------------------------

while @DBID<=@MaxDBID

begin

  -- Used Dynamic SQL for all databases.

  Set @SQL ='Use '+@DBName+ ' '+Char(10)

  Set @SQL += 'DBCC SHRINKFILE('+@Filename+',5)' +Char(10)

  Set @SQL += 'DBCC SHRINKDATABASE('+@DBName+')'+Char(10)

 

  --#6 Increment DBid for looping over all databases

  Set @DBID = @DBID+1

  Select @DBName = DBName, @Filename=DBFileName from #DBNames where [dbid] = @DBID and type_Desc = 'LOG'

  Print (@SQL)

  Exec (@SQL)

end

----------------------------------------------------------------------------

--#7 Final Output Print, wheich will execute one by one.

/*

Use AccountsDW

DBCC SHRINKFILE(AccountsDW_log,5)

DBCC SHRINKDATABASE(AccountsDW)

Use SalesMaster

DBCC SHRINKFILE(SalesMaster_Log,5)

DBCC SHRINKDATABASE(SalesMaster)

*/

----------------------------------------------------------------------------

Sys table help

https://docs.microsoft.com/en-us/sql/relational-databases/system-compatibility-views/sys-sysdatabases-transact-sql?view=sql-server-2017

Thank you,
please leave your feedback and suggestions, your new ideas could help many more developers.