Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Shrinking log files

Author  Topic 

10Dawg
Starting Member

46 Posts

Posted - 2013-02-21 : 11:42:45
Sql 2008
Below is what I use to shrink log files. With this code, I have to name each db therefore I am constantly updating it. Is there a way to say shrink all user defined log files?

USE Exxxxxxxxx;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE Exxxxxxxxx
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 GB.
DBCC SHRINKFILE (Exxxxxxxxx_log, 0);
GO
ALTER DATABASE Exxxxxxxxx
SET RECOVERY FULL;
GO

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-21 : 12:33:42
You can find the names of all the log files using this query:
SELECT * FROM sys.master_files  WHERE type_Desc = 'LOG'
Once you have that, you can construct the SQL needed to shrink the files.

BUT, before you do any of that, please read this article by Paul Randal. http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/ I realize that there are occasions when one needs to shrink the log files such as after one-time operations that may have caused the log file to grow. But, shrinking log file routinely is very counter productive.
Go to Top of Page
   

- Advertisement -