|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Order of maintenance procedures?What is the suggested order for these procedures if they all have to be run
once on a weekend WITHOUT dependencies? 1) DBCC DBREINDEX all user database indexes based on a logical fragmentation threshold 2) sp_updatestats on all user databases 3) DBCC SHRINKFILE on all user databases to a specified target size 4) DBCC CHECKDB on all databases 5) Backup all databases 6) Backup all logs Anything missing? Thanks! Don't shrink. It will to some extend undo your DBREINDEX:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ "Stephanie" <Stepha***@discussions.microsoft.com> wrote in message news:9AA42CBF-2BCD-422C-B315-5E2E0914ED9D@microsoft.com... > What is the suggested order for these procedures if they all have to be run > once on a weekend WITHOUT dependencies? > > 1) DBCC DBREINDEX all user database indexes based on a logical fragmentation > threshold > 2) sp_updatestats on all user databases > 3) DBCC SHRINKFILE on all user databases to a specified target size > 4) DBCC CHECKDB on all databases > 5) Backup all databases > 6) Backup all logs > > Anything missing? Thanks! Tibor,
I just wanted to follow-up. The DBREINDEX seems to leave a lot of allocated but now unused space in the database files. (My databases can run from 1 to 10 GB so I can easily have several GB allocated but unused during the week.) I am trying to reclaim some portion of that, not all. 1) Do you have another recommendation for reclaiming some space efficiently? 2) Is the order of the maintenance procedures correct? 3) Would you add anything to the maintenance procedures? Thanks. I really appreciate your feedback. Stephanie Show quote "Tibor Karaszi" wrote: > Don't shrink. It will to some extend undo your DBREINDEX: > http://www.karaszi.com/SQLServer/info_dont_shrink.asp > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://www.solidqualitylearning.com/ > Blog: http://solidqualitylearning.com/blogs/tibor/ > > > "Stephanie" <Stepha***@discussions.microsoft.com> wrote in message > news:9AA42CBF-2BCD-422C-B315-5E2E0914ED9D@microsoft.com... > > What is the suggested order for these procedures if they all have to be run > > once on a weekend WITHOUT dependencies? > > > > 1) DBCC DBREINDEX all user database indexes based on a logical fragmentation > > threshold > > 2) sp_updatestats on all user databases > > 3) DBCC SHRINKFILE on all user databases to a specified target size > > 4) DBCC CHECKDB on all databases > > 5) Backup all databases > > 6) Backup all logs > > > > Anything missing? Thanks! > > She shrink is just waste of time and resources if it will grow back to that size. So use only shrink
under special circumstances. See my article. The order doesn't matter too much. But if you shrink it does: If you shrink before dbreindex, then the database will autogrow to large size during dbreindex. If you dbreindex before shrink, much of the dbreindex will be undone by the shrink operation. See why you shouldn't shrink unless in special circumstances? Also updatestats is rarely needed. If the stats that comes with indexes are updated by dbreindex. But even if they weren't, the auto update stats generally is sufficient. Also, I trust you do log backups on a more frequent interval? Say every day you do db backup and every 10 minutes or every hour you do log backup. -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ "Stephanie" <Stepha***@discussions.microsoft.com> wrote in message news:CD114804-9166-461D-B20D-B842EB8B7982@microsoft.com... > Tibor, > > I just wanted to follow-up. The DBREINDEX seems to leave a lot of allocated > but now unused space in the database files. (My databases can run from 1 to > 10 GB so I can easily have several GB allocated but unused during the week.) > I am trying to reclaim some portion of that, not all. > > 1) Do you have another recommendation for reclaiming some space efficiently? > 2) Is the order of the maintenance procedures correct? > 3) Would you add anything to the maintenance procedures? > > Thanks. I really appreciate your feedback. > > Stephanie > > "Tibor Karaszi" wrote: > >> Don't shrink. It will to some extend undo your DBREINDEX: >> http://www.karaszi.com/SQLServer/info_dont_shrink.asp >> >> -- >> Tibor Karaszi, SQL Server MVP >> http://www.karaszi.com/sqlserver/default.asp >> http://www.solidqualitylearning.com/ >> Blog: http://solidqualitylearning.com/blogs/tibor/ >> >> >> "Stephanie" <Stepha***@discussions.microsoft.com> wrote in message >> news:9AA42CBF-2BCD-422C-B315-5E2E0914ED9D@microsoft.com... >> > What is the suggested order for these procedures if they all have to be run >> > once on a weekend WITHOUT dependencies? >> > >> > 1) DBCC DBREINDEX all user database indexes based on a logical fragmentation >> > threshold >> > 2) sp_updatestats on all user databases >> > 3) DBCC SHRINKFILE on all user databases to a specified target size >> > 4) DBCC CHECKDB on all databases >> > 5) Backup all databases >> > 6) Backup all logs >> > >> > Anything missing? Thanks! >> >> Thanks for the clarifications. Yes, we do full backups daily M-F 6 AM and
transaction log backups every 30 minutes M-F 7 AM - 7 PM. One other quick question: Any need for UPDATEUSAGE? Show quote "Tibor Karaszi" wrote: > She shrink is just waste of time and resources if it will grow back to that size. So use only shrink > under special circumstances. See my article. > > The order doesn't matter too much. But if you shrink it does: > > If you shrink before dbreindex, then the database will autogrow to large size during dbreindex. > If you dbreindex before shrink, much of the dbreindex will be undone by the shrink operation. > See why you shouldn't shrink unless in special circumstances? > > Also updatestats is rarely needed. If the stats that comes with indexes are updated by dbreindex. > But even if they weren't, the auto update stats generally is sufficient. > > Also, I trust you do log backups on a more frequent interval? Say every day you do db backup and > every 10 minutes or every hour you do log backup. > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://www.solidqualitylearning.com/ > Blog: http://solidqualitylearning.com/blogs/tibor/ > > > "Stephanie" <Stepha***@discussions.microsoft.com> wrote in message > news:CD114804-9166-461D-B20D-B842EB8B7982@microsoft.com... > > Tibor, > > > > I just wanted to follow-up. The DBREINDEX seems to leave a lot of allocated > > but now unused space in the database files. (My databases can run from 1 to > > 10 GB so I can easily have several GB allocated but unused during the week.) > > I am trying to reclaim some portion of that, not all. > > > > 1) Do you have another recommendation for reclaiming some space efficiently? > > 2) Is the order of the maintenance procedures correct? > > 3) Would you add anything to the maintenance procedures? > > > > Thanks. I really appreciate your feedback. > > > > Stephanie > > > > "Tibor Karaszi" wrote: > > > >> Don't shrink. It will to some extend undo your DBREINDEX: > >> http://www.karaszi.com/SQLServer/info_dont_shrink.asp > >> > >> -- > >> Tibor Karaszi, SQL Server MVP > >> http://www.karaszi.com/sqlserver/default.asp > >> http://www.solidqualitylearning.com/ > >> Blog: http://solidqualitylearning.com/blogs/tibor/ > >> > >> > >> "Stephanie" <Stepha***@discussions.microsoft.com> wrote in message > >> news:9AA42CBF-2BCD-422C-B315-5E2E0914ED9D@microsoft.com... > >> > What is the suggested order for these procedures if they all have to be run > >> > once on a weekend WITHOUT dependencies? > >> > > >> > 1) DBCC DBREINDEX all user database indexes based on a logical fragmentation > >> > threshold > >> > 2) sp_updatestats on all user databases > >> > 3) DBCC SHRINKFILE on all user databases to a specified target size > >> > 4) DBCC CHECKDB on all databases > >> > 5) Backup all databases > >> > 6) Backup all logs > >> > > >> > Anything missing? Thanks! > >> > >> > > > One other quick question: Any need for UPDATEUSAGE? If you have the time, run it. I don't think the optimizer is affected by the information it updates in sysindexes (at least I haven't heard recommendations running it for this reason). But it doesn't hurt. -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ "Stephanie" <Stepha***@discussions.microsoft.com> wrote in message news:22823101-B1C7-44AF-8081-7EA35130C12D@microsoft.com... > Thanks for the clarifications. Yes, we do full backups daily M-F 6 AM and > transaction log backups every 30 minutes M-F 7 AM - 7 PM. > > One other quick question: Any need for UPDATEUSAGE? > > "Tibor Karaszi" wrote: > >> She shrink is just waste of time and resources if it will grow back to that size. So use only >> shrink >> under special circumstances. See my article. >> >> The order doesn't matter too much. But if you shrink it does: >> >> If you shrink before dbreindex, then the database will autogrow to large size during dbreindex. >> If you dbreindex before shrink, much of the dbreindex will be undone by the shrink operation. >> See why you shouldn't shrink unless in special circumstances? >> >> Also updatestats is rarely needed. If the stats that comes with indexes are updated by dbreindex. >> But even if they weren't, the auto update stats generally is sufficient. >> >> Also, I trust you do log backups on a more frequent interval? Say every day you do db backup and >> every 10 minutes or every hour you do log backup. >> >> -- >> Tibor Karaszi, SQL Server MVP >> http://www.karaszi.com/sqlserver/default.asp >> http://www.solidqualitylearning.com/ >> Blog: http://solidqualitylearning.com/blogs/tibor/ >> >> >> "Stephanie" <Stepha***@discussions.microsoft.com> wrote in message >> news:CD114804-9166-461D-B20D-B842EB8B7982@microsoft.com... >> > Tibor, >> > >> > I just wanted to follow-up. The DBREINDEX seems to leave a lot of allocated >> > but now unused space in the database files. (My databases can run from 1 to >> > 10 GB so I can easily have several GB allocated but unused during the week.) >> > I am trying to reclaim some portion of that, not all. >> > >> > 1) Do you have another recommendation for reclaiming some space efficiently? >> > 2) Is the order of the maintenance procedures correct? >> > 3) Would you add anything to the maintenance procedures? >> > >> > Thanks. I really appreciate your feedback. >> > >> > Stephanie >> > >> > "Tibor Karaszi" wrote: >> > >> >> Don't shrink. It will to some extend undo your DBREINDEX: >> >> http://www.karaszi.com/SQLServer/info_dont_shrink.asp >> >> >> >> -- >> >> Tibor Karaszi, SQL Server MVP >> >> http://www.karaszi.com/sqlserver/default.asp >> >> http://www.solidqualitylearning.com/ >> >> Blog: http://solidqualitylearning.com/blogs/tibor/ >> >> >> >> >> >> "Stephanie" <Stepha***@discussions.microsoft.com> wrote in message >> >> news:9AA42CBF-2BCD-422C-B315-5E2E0914ED9D@microsoft.com... >> >> > What is the suggested order for these procedures if they all have to be run >> >> > once on a weekend WITHOUT dependencies? >> >> > >> >> > 1) DBCC DBREINDEX all user database indexes based on a logical fragmentation >> >> > threshold >> >> > 2) sp_updatestats on all user databases >> >> > 3) DBCC SHRINKFILE on all user databases to a specified target size >> >> > 4) DBCC CHECKDB on all databases >> >> > 5) Backup all databases >> >> > 6) Backup all logs >> >> > >> >> > Anything missing? Thanks! >> >> >> >> >> >> > I just wanted to follow-up. The DBREINDEX seems to leave a lot of And Tibor's article lays out that, if your database size is going to > allocated > but now unused space in the database files. (My databases can run from 1 > to > 10 GB so I can easily have several GB allocated but unused during the > week.) fluctuate like this, your server will be much happier if you leave the szize large. What is the point of spending all the time and resources necessary to reorganize the data files and pages within the database if it is just going to grow large again? Why free up the space if nobody else can use it? A The bottom line is that I wanted do something proactively to ensure a low
number of fragmented indexes for the most efficient data access and a low amount of allocated but unused space at the same time. I didn't know that these two goals are really mutually exclusive. I'll try not to be so idealistic in the future. Show quote "Aaron Bertrand [SQL Server MVP]" wrote: > > I just wanted to follow-up. The DBREINDEX seems to leave a lot of > > allocated > > but now unused space in the database files. (My databases can run from 1 > > to > > 10 GB so I can easily have several GB allocated but unused during the > > week.) > > And Tibor's article lays out that, if your database size is going to > fluctuate like this, your server will be much happier if you leave the szize > large. What is the point of spending all the time and resources necessary > to reorganize the data files and pages within the database if it is just > going to grow large again? Why free up the space if nobody else can use it? > > A > > > > The bottom line is that I wanted do something proactively to ensure a low And if all you're going to do with sound advice is spew sarcasm, I'll try > number of fragmented indexes for the most efficient data access and a low > amount of allocated but unused space at the same time. I didn't know that > these two goals are really mutually exclusive. I'll try not to be so > idealistic in the future. not to be so helpful in the future. I apologize. You definitely took it the wrong way. I certainly was not
spewing sarcasm. I was really trying to be honest about not expecting to be able to be so idealistic. If it came across as anything else, please accept my apologies. Show quote "Aaron Bertrand [SQL Server MVP]" wrote: > > The bottom line is that I wanted do something proactively to ensure a low > > number of fragmented indexes for the most efficient data access and a low > > amount of allocated but unused space at the same time. I didn't know that > > these two goals are really mutually exclusive. I'll try not to be so > > idealistic in the future. > > And if all you're going to do with sound advice is spew sarcasm, I'll try > not to be so helpful in the future. > > >  |
|||||||||||||||||||||||
Other interesting topics