Friday, August 14, 2009

Shrink all Databases and Transaction Logs T-SQL

How to shrink all the databases and/or transaction logs on a Microsoft SQL server:


--This will shrink all transaction logs
declare @sql varchar(1000)
set @sql = 'IF ''?'' not in (''master'', ''tempdb'', ''model'', ''tempdb'', ''msdb'') BEGIN '
+
'USE ? DBCC SHRINKFILE (N''?_log'' , 0, TRUNCATEONLY)'
+ ' END'
exec sp_msforeachdb @sql




--This will shrink all databases
declare @sql varchar(1000)
set @sql = 'IF ''?'' not in (''master'', ''tempdb'', ''model'', ''tempdb'', ''msdb'') BEGIN '
+
'USE ? DBCC SHRINKDATABASE(N''?'' )'
+ ' END'
exec sp_msforeachdb @sql


No comments:

Post a Comment

There was an error in this gadget