April 25, 2013 at 9:50 am
Hi,
We have production database with huge records and every day 100 of users are using the application, because of this transaction log file size is increasing in OS . To avoid this , we have T-SQL queries to clear/shrink the transaction log but we are running this query manually.
Do we have any options to automate this shrinking/clearing the transaction logs?
Thanks in advance.
Ram
April 25, 2013 at 9:58 am
sram24_mca (4/25/2013)
Hi,We have production database with huge records and every day 100 of users are using the application, because of this transaction log file size is increasing in OS . To avoid this , we have T-SQL queries to clear/shrink the transaction log but we are running this query manually.
Do we have any options to automate this shrinking/clearing the transaction logs?
Thanks in advance.
Ram
Why are you shrinking your transaction log? That is destroying your ability to recover your database to anything other than a full back up.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 25, 2013 at 10:05 am
if you don't need point in time recovery set your recovery mode to simple, else schedule regular transaction log backups, that will prevent the log growth.
There is NO need to repeatedly shrink the log if you do the above.
---------------------------------------------------------------------
April 25, 2013 at 10:06 am
I'm guessing the recovery model of the database is set to FULL but no log backups are taken so the TLog grows and grows, gets shrinked every day only to grow again the next day.
If point in time recovery is not a requirement then change your recovery mode to SIMPLE and resize your log file to an appropriate size that can handle your daily activity.
And if you REALLy want to shrink then nothing stopping you from scheduling a SQL Agent job to do this every night.
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
April 25, 2013 at 10:51 am
Please read through this - Managing Transaction Logs[/url]
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply