<< LINQ Query: How to simulate the behavior of the IN statment | Home | VS 2010 Productivity Power Tools: Organize Imports in VB >>

SQL Server 2008: How To Shrink all files with one command

SQL Server 2008 has been eliminated NO_LOG BACKUP command that could be useful to truncate the log files of large size.

BACKUP LOG [DatabaseName] WITH NO_LOG

Many developers need to compress the testing databases files to avoid getting the disk space used unnecessarily. By executing the following you can shrink to a minimum all the log files of a database server:

EXECUTE sp_msforeachdb

'USE ?;

ALTER DATABASE ? SET RECOVERY SIMPLE;

DECLARE @LogLogicalName nvarchar(100);

SELECT @LogLogicalName = file_name(2);

DBCC SHRINKFILE(@LogLogicalName, 100);
ALTER DATABASE ? SET RECOVERY FULL;'

 

“sp_msforeachdb” is a no-documented stored procedure that runs the script for all the db in your server.
Use with caution!
Alberto.

Print | posted on giovedì 5 maggio 2011 9.39

Feedback

# re: SQL Server 2008: How To Shrink all files with one command

Left by Full Lace Wigs at 13/09/2011 7.37
Gravatar I am not sure where you are getting your information, but great topic. I needs to spend some time learning much more or understanding more. Thanks for fantastic info I was looking for this information for my mission.

# re: SQL Server 2008: How To Shrink all files with one command

Left by Hosting India at 12/01/2012 6.34
Gravatar This is very amazing blog and information provided by the article of this blog is really nice and useful and i would like to visit the blog again.

Your comment:





 
Please add 4 and 4 and type the answer here:

Copyright © Alberto De Luca

Design by Bartosz Brzezinski

Design by Phil Haack Based On A Design By Bartosz Brzezinski