Azure, ALM, Development, Server and IT Solutions - by a Microsoft MVP in Visual Studio ALM
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.
DUMP TRANSACTION ? WITH NO_LOG;
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.