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. <br /><br />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=’
![Smile :) :)](styles/default/xenforo/smilies/smile.png)
![Big Grin :D :D](styles/default/xenforo/smilies/biggrin.png)
]]>