SQL Nightly Job and transaction log size

Question

Post reply

SQL Nightly Job and transaction log size

Barkingdog

SSCoach

Points: 18670
More actions
May 30, 2011 at 2:02 pm

#250971

On two of our sql 2008 servers I'm finding some massive transaction logs files --- typically appearing after, not before, the Nightly Maintenance job has run. The Nightly job does this:
>>>>
Check DB Integrity
Shrink DB
Rebuild index
Update State
>>>>
Not only are the trans logs large but so are the associated dump files. I am looking for a way to "trim" things. Suggestions, notions, appreciated.
TIA,
Barkingdog
P.S. I suspect that last three items need not be done on a nightly basis (how does one tell that to an automated process?)

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Firstly stop shrinking your database. It's not something that should ever be scheduled and automated.

For the rebuild indexes, two things

- Switch to bulk-logged recovery for the duration if you can afford the slight risk. Won't help with the log backup sizes, will help with the log size

- Only rebuild indexes that need rebuilding (though if you insist on shrinking beforehand, all indexes will need a rebuild). There are logs of good index rebuild scripts available. Try http://www.sqlfool.com to start

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

Syed Jahanzaib Bin hassan SSChampion Points: 11410 More actions · Answer 2

Avoid shrinking the database if possible as Gila mentioned and follow this dynamic management function

For Rebuild Indexes

==============

Select * from sys.dm_db_index_physical_stats(db_id('your database'),null,null,null,null)

where avg_fragmentation_in_percent > 30 and page_count > 1000

apply rebuild indexes only these indexes of this query result mentioned

For Reorganize Indexes

=================

select * from sys.dm_db_index_physical_stats(db_id('your database'),null,null,null,null)

where avg_fragmentation_in_percent between 10 and 30

apply reorganize indexes only these indexes of this query result mentioned

If you dont required Log entries at the time of Maintenance then you can schedule Log truncation at every 15 mins for the duration of Maintenance only

My Blog
www.aureus-salah.com