Shrink All databases SQL Server 2012

SELECT
‘USE [‘ + d.name + N’]’ + CHAR(13) + CHAR(10)
+ ‘DBCC SHRINKFILE ( ‘+ s.name + ‘ , 1)’
+ CHAR(13) + CHAR(10)
+ ‘BACKUP LOG ‘ + d.name + ‘  TO DISK = ”nul”’
+ CHAR(13) + CHAR(10)
+ ‘DBCC SHRINKFILE ( ‘+ s.name + ‘ , 1)’
+ CHAR(13) + CHAR(10)
+ ‘GO’
FROM sys.databases D,master..sysaltfiles S
WHERE S.dbid=d.database_id
AND d.database_id > 4
AND S.filename LIKE ‘%.ldf’
ORDER BY S.size DESC

 

Version 2:

 

ALTER DATABASE [TootleCFD]
SET RECOVERY SIMPLE;
GO
— Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (TootleCFD_Log, 1);
GO
— Reset the database recovery model.
ALTER DATABASE [TootleCFD]
SET RECOVERY FULL;
GO 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s