SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 SQL Reindex and Reorganize
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dastaub
Starting Member

3 Posts

Posted - 11/15/2013 :  11:10:30  Show Profile  Reply with Quote
A maintenance job runs that REORGANIZES all the indexes and rebuilds all the statistics. Prior to creating and scheduling this job, I dropped and REBUILT all the indexes on all the tables. This job is created to prevent the need to drop and rebuild indexes while maintaining low fragmentation and updated stats.

Why does the maintenance job that REORGANIZES indexes only 24 hours after all the indexes are REBUILT cause the transaction log to grow to the size of the DB itself?
The tables are a few billion bytes in size and the DB is about 12 billion bytes in size.
I thought a reorganize would cause minimal activity if the indexes it is reorganizing had recently been dropped and rebuilt?

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 11/15/2013 :  12:24:26  Show Profile  Visit tkizer's Homepage  Reply with Quote
How often are you backing up the transaction log?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

dastaub
Starting Member

3 Posts

Posted - 11/15/2013 :  12:44:42  Show Profile  Reply with Quote
The transaction log is backed up twice a day. I can see the size growth of the log by looking at the backup of the transaction log after the maintenance plan runs and the next backup goes back to the usual size.

quote:
Originally posted by tkizer

How often are you backing up the transaction log?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/



Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 11/15/2013 :  12:47:59  Show Profile  Visit tkizer's Homepage  Reply with Quote
You aren't backing up your transaction log often enough. You should consider doing hourly log backups at a minimum. It is most common for people to do them every 15 minutes. Some people increase it to every 5 minutes during index maintenance.

All of our systems are set for 15 minutes.

There is no downside really to more frequent backups. The total size of the files will be the same. The two biggest benefits of more frequent log backups are more recovery points (less data loss in case of an issue) and less resources needed when it runs.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000