Shrink log file for all database in SQL Server 2008/2005

Well, shrinking log file manually and frequently is not at all good idea but we have to do it once in a while when it reaches at very big size, however if it is growing unacceptably and frequently, you have to investigate the reason before shrinking it.

Anyway, our main focus today is how to shrink log file. Well, there is a simple command “DBCC SHRINKFILE” in T-SQL but what, if we want to shrink log file of all database in SQL Server? Well in this situation SP_MsForEachDB (undocumented SP) will come to your help.

Let us see small yet handy script to do so:

use master

DECLARE @SQL varchar (2000)

SELECT @SQL =

SELECT @SQL = @SQL + ‘if ”?” ”master” and ”?” ”msdb” and ”?””tempdb” and ”?” ”model” ‘

SELECT @SQL = @SQL + ‘BEGIN ‘

SELECT @SQL = @SQL + ‘USE ?; ‘

SELECT @SQL = @SQL + ‘SELECT ”?”; ‘

SELECT @SQL = @SQL + ‘DECLARE @LogFile varchar (30); ‘

SELECT @SQL = @SQL + ‘SELECT @LogFile = name FROM dbo.sysfiles WHERE (status & 0x40) 0; ‘

SELECT @SQL = @SQL + ‘dbcc shrinkfile (@LogFile, 1,truncateonly); ‘

SELECT @SQL = @SQL + ‘SELECT fileid, name, filename, size, growth, status, maxsize FROM dbo.sysfiles WHERE (status & 0x40) 0; ‘

SELECT @SQL = @SQL + ‘END’

SELECT @SQL

EXEC sp_MSforeachdb @command1=@SQL

GO

 

To know more about SP_MsForEachDB stored procedure, have a look at one of my previous article at:

http://www.sqlhub.com/2009/03/spmsforeachdb-undocumented-stored.html

http://www.sqlhub.com/2009/04/find-user-in-all-database-with.html

 

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Advertisements

8 Responses to “Shrink log file for all database in SQL Server 2008/2005”

  1. brejk Says:

    You have given a ticking timebomb in the hands of people willing to believe they really should perform this massive log truncation… IMO, you should definitely mention that log truncation causes log to be fragmented and growing again (the first effect is implied by the second one). Moreover, why not to clearly state that the key to keep your log fairly small is to backup it on a regular basis?

  2. brejk Says:

    You have given a ticking timebomb in the hands of people willing to believe they really should perform this massive log truncation… IMO, you should definitely mention that log truncation causes log to be fragmented and growing again (the first effect is implied by the second one). Moreover, why not to clearly state that the key to keep your log fairly small is to backup it on a regular basis?

  3. Ritesh Shah Says:

    Hi brejk,First of all, welcome to my blog. Yes you are absolutely right. I have mentioned that it is not good to shrink the log file manually and frequently but sometime when we want to reclaim the space, we do need it as backing up log won’t reclaim the physical space. I have read so many question in many forum that people need to reclaim the space and that lead me write this script in my blog.

  4. Ritesh Shah Says:

    Hi brejk,First of all, welcome to my blog. Yes you are absolutely right. I have mentioned that it is not good to shrink the log file manually and frequently but sometime when we want to reclaim the space, we do need it as backing up log won’t reclaim the physical space. I have read so many question in many forum that people need to reclaim the space and that lead me write this script in my blog.

  5. brejk Says:

    I prefer the approach to give people not just a direct slutions to their questions but also if applicable to let them know they are heading for a disaster 🙂

  6. brejk Says:

    I prefer the approach to give people not just a direct slutions to their questions but also if applicable to let them know they are heading for a disaster 🙂

  7. Ritesh Shah Says:

    Yes, that is true in some cases but not for all cases as some people need it but shouldn’t be apply in all case. thanks for your comments, after seeing these discussion, people will come to know that, this should be last weapon not first.

  8. Ritesh Shah Says:

    Yes, that is true in some cases but not for all cases as some people need it but shouldn’t be apply in all case. thanks for your comments, after seeing these discussion, people will come to know that, this should be last weapon not first.


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

%d bloggers like this: