Best Practice Advice on Trans Logs, how to automate

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Backups » Best Practice Advice on Trans Logs, how to...

Best Practice Advice on Trans Logs, how to automate

Rate Topic

Display Mode

Topic Options

Author

Message

mark_copley

Posted Monday, November 16, 2009 8:50 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 11:37 AM
Points: 232, Visits: 201

All -
Looking for best practice or way to keep transaction logs cleaned up. Working with (5) test region databases , all set to Simple Recovery. I have saw where Best Practices wants you to stay away from Shrinking the database regularly but wondered if there is some code to run regularly that would keep transaction logs trimmed down to acceptable levels. I thought of doing a Check Point then maybe a ShrinkFile on the log file but looking for opinons on that.

Example:

Use myDatabase1;
checkpoint
dbcc shrinkfile (myDatabase1_log, 15)

Any and all help appreciated, thanks !

Post #819433

GilaMonster

Posted Monday, November 16, 2009 8:56 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:54 AM
Points: 37,726, Visits: 29,985

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 #819443

mark_copley

Posted Monday, November 16, 2009 9:25 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 11:37 AM
Points: 232, Visits: 201

I have read thru the Managing Transact Logs document but was kind of wanting some practical information. Like is the accepted practice to monitor transaction log files regularly and if they get to big, run a checkpoint and shrinkfile on the log file. (simple recovery)

Thanks

Post #819467

GilaMonster

Posted Monday, November 16, 2009 9:31 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:54 AM
Points: 37,726, Visits: 29,985

The accepted practice is to size them for the workload (regardless of recovery model) and leave them alone.

Shrinking a log is a bad idea unless it's after an unusual operation and you know absolutely 100% that the log does not need to be that size. Repeated shrink/grow leaves you with lots of little VLFs in the log (slows down database recovery) and file-level fragmentation. Neither of which is something you want.

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 #819471

mark_copley

Posted Monday, November 16, 2009 9:35 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 11:37 AM
Points: 232, Visits: 201

Thanks I really appreciate the information.

Post #819477

« Prev Topic | Next Topic »

Permissions