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

Sp_MsForEachDB undocumented stored procedure in SQL Server 2005:

Sp_MsForEachDB is an un-documented stored procedure by Microsoft. It is useful when you want to perform same action in all databases in one SQL Server instance. Without Sp_MsForEachDB you have to go for cursor and you know that cursor is resource consuming and reduce the speed.

I have written article about another undocumented stored procedure named “Sp_MsForEachTable” which is for performing same action for all tables in one database. You can refer that article at:

http://ritesh-a-shah.blogspot.com/2009/03/spmsforeachtable-undocumented-but.html

http://ritesh-a-shah.blogspot.com/2009/03/empty-all-table-or-delete-all-data-from.html

Anyway, let us see how we can use Sp_MsForEachDB practically.

–this script will return all the table of all databases in sql server instance

–along with db name and schema

exec sp_MSforeachdb

‘USE ?

select ob.name,sch.name,”?” AS ”DATABASE” from ?.sys.tables ob

inner join ?.sys.schemas as sch on sch.schema_id = ob.schema_id’

This was just an example of Sp_MsForEachDB. You can use it more efficiently in your customize need like granting permission to user in every database, change some property of all databases available in instance of SQL Server etc.

Practical example of this article is created by Rashmika Vaghela (good SQL developer and my subordinate). She was inspired to write something by regularly reading my blog and asked me to contribute in any of my article. I was planning to write article about Sp_MsForEachDB so I told her to make one practical example for Sp_MsForEachDB.

Thank you Rashmika.

Reference: Ritesh Shah