SQL Server Performance Forum – Threads Archive

Looking for Shrink T-Log script….

I am trying to piece together a SQL query that will sort through each database on a SQL server ans shrink the t-log for each database found. I know how to shrink the t-log and truncate backup but do not know how to get all the databases and the t-0log file names and then perform the procedure on each one of them. Can elaborate more if need be. Any help?[?] Thanks!

I prefer performing the shrink process manually and not via programatically. DBCC SHRINKFILE applies to the files in the current database. Switch context to the database to issue a DBCC SHRINKFILE statement referencing a file in that particular database. And if any of the database maintenance plans perform integirty checks & reindex, by enabling this shrink process will dent the performance gain. (Kindly post the thread in relevant forums). Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Usually I prefer to perform this taks manually as well but I am running short on disk space and would like to shrink ALL the T-Logs at one time to get the space back.
Sorry for posting I the wrong area.

What is the schedule of Tlog backups for all the databases?
What is the current disk space and free space, alongwith databases count? Differentiate the databases which are filling up rapidly and slowly, in order to get the space schedule a job to shrink Tlogs on these databases and manually on troubled databases.
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

This script will generate all the code and meet your needs, it will shrink data and log files, I commented out the where clause, but you can inlude the where clause to exclude any databases from the cursor. You can also include Trucate_Only and a defualt target size. You can also modify it to run DBCC shrinkDatabase instead. Instead of changing the PRINT command for and EXEC query I would get this code is generated and incorporate it info a multi-step Agent job with desired Target sizes and stuff. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] I don’t want to recommend query system tables but in certain situations you must use them, I can’t find any INFORMATION.SCHEMA that provides file information so if anyone knows of one let me know. Anyways here it is, and stop being so Lazy!! [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] DECLARE @strDBName VARCHAR(100) DECLARE @strLogName VARCHAR(100) DECLARE Cur_Name CURSOR FAST_FORWARD FOR SELECT db.[name] AS DBNAME, alt.[name] AS LOGNAME FROM sysaltfiles alt INNER JOIN dbo.sysdatabases db ON alt.dbid = db.dbid –WHERE db.dbid <> 1 ORDER BY DBNAME OPEN Cur_Name FETCH NEXT FROM Cur_Name INTO @strDBName, @strLogName WHILE @@FETCH_STATUS = 0 BEGIN print (‘USE ‘[email protected]+’ DBCC SHRINKFILE (‘[email protected]+’) GO’) FETCH NEXT FROM Cur_Name INTO @strDBName, @strLogName END CLOSE Cur_Name DEALLOCATE Cur_Name GO

]]>