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.
