SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Is there any way t automate the auto shrink the transaction logs


Is there any way t automate the auto shrink the transaction logs

Author
Message
sram24_mca
sram24_mca
SSC Veteran
SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)

Group: General Forum Members
Points: 210 Visits: 508
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
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25847 Visits: 17509
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
george sibbald
george sibbald
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10248 Visits: 13687
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.

---------------------------------------------------------------------
Abu Dina
Abu Dina
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1221 Visits: 3323
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

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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86376 Visits: 45232
Please read through this - Managing Transaction Logs

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search