Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dastaub
Starting Member
3 Posts |
 Posted - 2013-11-15 : 11:10:30
|
| 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
38200 Posts |
Posted - 2013-11-15 : 12:24:26
|
| How often are you backing up the transaction log?Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
dastaub
Starting Member
3 Posts |
Posted - 2013-11-15 : 12:44:42
|
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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2013-11-15 : 12:47:59
|
| 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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
|
|
|
SQL Reindex and Reorganize