Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Is there any way t automate the auto shrink the transaction logs Expand / Collapse
Author
Message
Posted Thursday, April 25, 2013 9:50 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 10:49 PM
Points: 90, Visits: 388
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
Post #1446583
Posted Thursday, April 25, 2013 9:58 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:13 AM
Points: 12,993, Visits: 12,408
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 Moden's 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)
Post #1446588
Posted Thursday, April 25, 2013 10:05 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 5,871, Visits: 12,972
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.


---------------------------------------------------------------------

Post #1446594
Posted Thursday, April 25, 2013 10:06 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, September 19, 2014 3:37 AM
Points: 708, Visits: 3,288
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
Post #1446597
Posted Thursday, April 25, 2013 10:51 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:20 AM
Points: 39,960, Visits: 36,315
Please read through this - Managing Transaction Logs


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1446617
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse