June 18, 2015 at 8:49 am
I am testing some maintenance tasks sql commands such as index rebuild, index reorg, update statistics and db integrity check on a SQL Server 2014 Database. This is a new non-production vendor database (DB Size 500 GBs, Log Size 25 GBs) which eventually will be created in production. Currently, it is in full recovery model and without log backups. The database has a whole lot of indexes. I am just trying to rebuild and reorganize all the indexes (that need it), in addition to trying to get an idea of how long these maintenance task will take and the space needed in the log file to complete these tasks/commands. I would like to execute these tasks manually (the first time) to gather the duration and space required information. Eventually, I would probably schedule a weekly job to perform this maintence.
I ran the index rebuild task on the database and noticed that the log file grew by over 50 GBs. I killed the process and truncated and shrunk the log file back down. This led to the following questions:
1. Does the index rebuild, index reorg, update statistics and db integrity check commands all use the log file?
2. Does Indexs Reorg have less impact on log file then Index Rebuild?
3. Should a truncate log and shrink log file be performed after these maintenance commands?
4. Should a full database backup be performed after these maintenance commands? Or before the maintenance commands?
I have read and understand that shrinking is not good for the database (could lead to more fragmentation and more data file growth when data is added) and I know about rebuilding indexes when fragmentation is GT 30% and reorganizing indexes when fragmentation is GT 5% and LE 30%.
Since this is a non-production database maybe I should set the recovery model to simple, run the maintenance commands and leave the database in simple recovery model unless the vendor needs it in full recovery model for some unknown reason.
5. With the simple recovery model the log file should be reused in a circular manner and not grow during these maintenance tasks. Is this correct?
Just looking for some thoughts and ideas. Thanks in advance.
June 19, 2015 at 11:39 pm
HookSqlDba7 (6/18/2015)
I am testing some maintenance tasks sql commands such as index rebuild, index reorg, update statistics and db integrity check on a SQL Server 2014 Database. This is a new non-production vendor database (DB Size 500 GBs, Log Size 25 GBs) which eventually will be created in production. Currently, it is in full recovery model and without log backups. The database has a whole lot of indexes. I am just trying to rebuild and reorganize all the indexes (that need it), in addition to trying to get an idea of how long these maintenance task will take and the space needed in the log file to complete these tasks/commands. I would like to execute these tasks manually (the first time) to gather the duration and space required information. Eventually, I would probably schedule a weekly job to perform this maintence.I ran the index rebuild task on the database and noticed that the log file grew by over 50 GBs. I killed the process and truncated and shrunk the log file back down. This led to the following questions:
1. Does the index rebuild, index reorg, update statistics and db integrity check commands all use the log file?
Index rebuild & index reorg use the Log heavily. Update statistics uses the Log but not enough to worry about planning for. DB integrity (e.g. DBCC CHECKDB) will not affect the Log of the database being checked but does affect tempdb.
2. Does Indexs Reorg have less impact on log file then Index Rebuild?
My head says a reorg will usually take less log space to complete since it is doing less work with the index but my gut says "it depends" on factors like index definition and level of fragmentation. You may have to experiment with this one.
Please read this book (buy it from Amazon or download free eBook) on transaction log management. It may answer this question for you and will arm you with great depth of knowledge on one of the most important areas of SQL Server you must to know to be a dependable DB admin.
3. Should a truncate log and shrink log file be performed after these maintenance commands?
No, absolutely not. Taking a log backup will truncate the log for you. Do not bother shrinking it because you'll need that space next time you run maintenance. Plus shrinking the Log, and moreso growing the Log are expensive operations.
4. Should a full database backup be performed after these maintenance commands? Or before the maintenance commands?
Log backups are more important during or after maintenance operations.
I have read and understand that shrinking is not good for the database (could lead to more fragmentation and more data file growth when data is added) and I know about rebuilding indexes when fragmentation is GT 30% and reorganizing indexes when fragmentation is GT 5% and LE 30%.
Since this is a non-production database maybe I should set the recovery model to simple, run the maintenance commands and leave the database in simple recovery model unless the vendor needs it in full recovery model for some unknown reason.
Sometimes running a database in SIMPLE is OK. It's much more common in non-production settings but I suggest you check with the data owners (people who rely this data) to find out what is their tolerable amount of data loss. Their answer will determine your backup strategy. As an example, if they say 10 minutes, tops, then you'll need FULL recovery with periodic Database Backups (maybe once per 24 hours) plus Log Backups every 10 minutes. If they say 24 hours then a Database Backup once per day would suffice. Differentials Backups are also available to meet some requirements (less common in my experience).
5. With the simple recovery model the log file should be reused in a circular manner and not grow during these maintenance tasks. Is this correct?
Read the book I linked to above for the full story. Yes, they are circular but if you are in FULL recovery the Log backup is what allows SQL Server to reuse space in the log (there are other factors too that can prevent re-use that will be in the book). In SIMPLE recovery you can still see Log growth since an index rebuild is done as a single operation (i.e. transaction) so until the rebuild completes that Log space cannot be truncated and reused. For very large indexes this can mean huge log growth even when in SIMPLE mode.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 22, 2015 at 3:16 am
HookSqlDba7 (6/18/2015)
1. Does the index rebuild, index reorg, update statistics and db integrity check commands all use the log file?
Yes, yes, yes, no.
2. Does Indexs Reorg have less impact on log file then Index Rebuild?
Depends on your recovery model and the degree of fragmentation and several other things.
Index rebuilds are minimally logged in bulk-logged and simple recovery models. Index reorg is fully logged in all recovery models. Index reorg usually does less work than rebuilds, and so usually logs less.
3. Should a truncate log and shrink log file be performed after these maintenance commands?
NO!
Truncating the log breaks your recovery chain and requires a new full backup before log backups can be taken. It's a very bad thing to run on a DB in full/bulk-logged. It has no effect on a database in simple recovery
4. Should a full database backup be performed after these maintenance commands? Or before the maintenance commands?
Maybe. Depends on your maintenance windows, your restore plan, your RPO and RTO, etc
I have read and understand that shrinking is not good for the database
Yup.
I know about rebuilding indexes when fragmentation is GT 30% and reorganizing indexes when fragmentation is GT 5% and LE 30%.
Those numbers are guidelines, not hard-and-fast rules.
5. With the simple recovery model the log file should be reused in a circular manner and not grow during these maintenance tasks. Is this correct?
Maybe. Simple doesn't mean the log won't grow, just that log backups aren't required to mark the log as reusable.
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
June 22, 2015 at 1:03 pm
Thanks Orlando and GilaMonster for taking time to respond to each question. You have given me some very good information, ideas and links to read.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply