Wednesday, June 24, 2009

SQL Server Log File Shrink for all databases on a server

If you use SQL Server, you certainly now that the grow up of transacion log file (.ldf file) is a serious problem.

The faster method to resize the transaction log is to do a shrink.

But if there are N databases on a server, a DBA has to repeat the operation for N times. So, I've created this T-Sql script with that you can automatically execute this operation for all databases on a server.

EXECUTE sp_msforeachdb
'USE ?;
DECLARE @LogLogicalName nvarchar(100);
SELECT @LogLogicalName = file_name(2);
DBCC SHRINKFILE(@LogLogicalName, 100);'

sp_msforeachdb: an undocumented microsoft stored procedure that allow to execute T-SQL code on each database on a DB server.

?: the database name given from stored procedure.

file_name(2): a function that return the logical name of db transaction log file.

No comments: