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 |
|
tbrothers
Yak Posting Veteran
83 Posts |
 Posted - 2011-09-08 : 10:36:25
|
| We are running SQL 2005 Ent. I log ship my databases to a backup server. The logs are shipped every 15 minutes. I do full backups every night.The database is question is 102 GB.The log file is normally around 25 GB and I believe most of this is due to the optimization and organization maintenance job that runs weekly. The optimization and organixation job takes 4 hours to run and:a) Checks DB Integrityb) Reorganizes Indexes (Compact large objects is checked)The log file never decreases in size. This past weeked it increased to 54 GB ... I had to do the unforgivable DBCC SHRINKFILE and shrink the log because space was getting low. I did a full backup first.Questions1) With the logs getting backed up every 15 minutes and the database getting backed up nightly, shouldn't the log file decrease in size?2) Does Index Reorginization help with database fragmentation?Thanks,Terry |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-09-08 : 10:53:57
|
| 1. Not necessarily. The log will grow to the size needed for whatever operations are occurring. If it was a one-off that caused to grow to 54 GB, fine with the shrink. If it grows right back, might as well leave it and find more disk space.2. It helps with index fragmentation. |
 |
|
|
tbrothers
Yak Posting Veteran
83 Posts |
Posted - 2011-09-08 : 11:08:28
|
| Thanks RussellHow can I check DB fragmentation? I'm looking at the Idera tools right now and they seem pretty nice.Terry |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-08 : 13:27:48
|
| "I believe most of this is due to the optimization and organization maintenance job that runs weekly"Couple of thoughts.We run index rebuild / reorganise every night. We have some "quiet time" from around midnight to 5am. We use a custom stored procedure that only rebuilds indexes that are heavily fragmented. As far as I know the standard housekeeping tools supplied with SQL don't provide this "selective rebuild" (after all these year, and all these versions of SQL Server, "Why not?" I ask myself ...)The benefit is that a) relatively few indexes are rebuilt each time, because they don't all get heavily fragmented every 24 hours!b) Once fragmented they are rebuilt within 24 hours, rather than delaying until the weekendc) Must less LOG space is required than the "rebuild everything" approach that the standard MS Maintenance routines do.Notwithstanding that we increase the Log Backup rate from every 15-minutes to every 2-minutes during housekeeping, to reduce the risk that the TLog file gets extended. Like you our Maintenance Routines are the biggest Log-space hog of all that operations we do. |
|
|
|
tbrothers
Yak Posting Veteran
83 Posts |
Posted - 2011-09-08 : 14:39:37
|
| Thank you Kristen! This helps a lot.I'm looking at the Idera Defrag tool that will allow me to do exactly what you said ... schedule a defrag of only the fragmented objects (e.g. If 20% fragmented then reorganize and if 30% then rebuild).I'll look at backing up transaction logs more frequently during maintenance operations. I just don't know if I can do that with log shipping.Thanks again!Regards,Terry |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-08 : 17:17:26
|
| "I just don't know if I can do that with log shipping."Nothing is ever simple ...We have an SProc which we use for all backups. It means that if we ever need a quick-backup, before doing something risky, and/or to ensure that we can restore if an operation fails, and also to ensure that the backup file is stored in the correct/centralised place etc ... thus presumably such an Sproc could also ensure that the backup is available for log shipping (although I've never done formal log shipping so not quite sure what is involved) |
|
|
|
|
|
|
|
|
Another Log File Size Question