February 10, 2014 at 9:28 am
I know shrinking a database is not recommended but our management needs the precious disk space back after deleting more than last 2 years old data consuming around 350 GB space. I know it can affect the performance adversely so what else better than seeking help from this great community in order to do it gracefully. Thanks.
February 10, 2014 at 9:35 am
February 10, 2014 at 10:02 am
@Keith Tate Thanks a lot for quick the reply. I already had this in my mind but I am more comfortable with Michelle Ufford's gem (No disrespect to Olla's excellent/great maintenance script). Just curious, this could lead to new execution plans? Hopefully, largely good ones but some bad plans as well. This could also mean a very large log file and sadly I have Standard edition to work with. I see some very hard days ahead where I would be explaining and convincing that some processes were slow for a reason :crazy:
Also what if I leave 100 GB space for future growth. Will it cause less fragmentation? OR the result would be the same? Any more ideas on this?
Moreover, what if I re-organize the indexes only?
February 10, 2014 at 10:19 am
Shrinking a database wouldn't cause the creation of any new execution plans.
Yes, this will cause your log file(s) to fill/grow (especially so with the index maintenance)
If space isn't a huge issue, I'd leave the space available to the database for future growth (as growing the database files introduces heavy IO/contention across your disk subsystem). Leaving the space as-is won't cause fragmentation (which i assume you mean index fragmentation), however, growing the database files and physically shrinking them will cause fragmentation at the OS-level and should be avoided.
Re-organizing your indexes will work fine, but unless you run a separate database statistics update for the tables/indexes being reorg'd, your performance may still suffer.
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
February 10, 2014 at 10:21 am
jewel.sacred (2/10/2014)
@Keith Tate Thanks a lot for quick the reply. I already had this in my mind but I am more comfortable with Michelle Ufford's gem (No disrespect to Olla's excellent/great maintenance script). Just curious, this could lead to new execution plans? Hopefully, largely good ones but some bad plans as well. This could also mean a very large log file and sadly I have Standard edition to work with. I see some very hard days ahead where I would be explaining and convincing that some processes were slow for a reason :crazy:Also what if I leave 100 GB space for future growth. Will it cause less fragmentation? OR the result would be the same? Any more ideas on this?
Moreover, what if I re-organize the indexes only?
You can kick off the index maint using her script and force more of a reorg than a rebuild by bumping the thresholds. Reorg will require less log space and is available in standard edition.
When you shrink, I would shrink to a size that leaves about 1.5x the largest table in free space.
Once the reorg is complete - you will probably want to update the statistics on key tables.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 10, 2014 at 10:24 am
MyDoggieJessie (2/10/2014)
Shrinking a database wouldn't cause the creation of any new execution plans.Yes, this will cause your log file(s) to fill/grow (especially so with the index maintenance)
If space isn't a huge issue, I'd leave the space available to the database for future growth (as growing the database files introduces heavy IO/contention across your disk subsystem). Leaving the space as-is won't cause fragmentation (which i assume you mean index fragmentation), however, growing the database files and physically shrinking them will cause fragmentation at the OS-level and should be avoided.
Re-organizing your indexes will work fine, but unless you run a separate database statistics update for the tables/indexes being reorg'd, your performance may still suffer.
Shrinking a data file will cause massive fragmentation within the indexes.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 10, 2014 at 10:29 am
@SQLRNNR - I agree with you 100% - I didn't say it wouldn't - I was saying it would not force new execution plans to be created.
I was answering the OP's question:
Also what if I leave 100 GB space for future growth. Will it cause less fragmentation? OR the result would be the same? Any more ideas on this?
To which I said: Leaving the space as-is won't cause fragmentation (which i assume you mean index fragmentation), however, growing the database files and physically shrinking them will cause fragmentation at the OS-level and should be avoided.
I guess it was unclear :hehe:
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
February 10, 2014 at 10:31 am
MyDoggieJessie (2/10/2014)
Shrinking a database wouldn't cause the creation of any new execution plans.Yes, this will cause your log file(s) to fill/grow (especially so with the index maintenance)
If space isn't a huge issue, I'd leave the space available to the database for future growth (as growing the database files introduces heavy IO/contention across your disk subsystem). Leaving the space as-is won't cause fragmentation (which i assume you mean index fragmentation), however, growing the database files and physically shrinking them will cause fragmentation at the OS-level and should be avoided.
Re-organizing your indexes will work fine, but unless you run a separate database statistics update for the tables/indexes being reorg'd, your performance may still suffer.
Thanks for the key suggestions. As far as new execution plans are concerned I was more inclined to new execution plans in case of rebuilding indexes and hence stats update 🙂
Since the management wants the space back, I have no other option but to shrink the database 🙂
February 10, 2014 at 10:31 am
MyDoggieJessie (2/10/2014)
@SQLRNNR - I agree with you 100% - I didn't say it wouldn't - I was saying it would not force new execution plans to be created.I was answering the OP's question:
Also what if I leave 100 GB space for future growth. Will it cause less fragmentation? OR the result would be the same? Any more ideas on this?
To which I said: Leaving the space as-is won't cause fragmentation (which i assume you mean index fragmentation), however, growing the database files and physically shrinking them will cause fragmentation at the OS-level and should be avoided.
I guess it was unclear :hehe:
It is definitely more clear now 😀
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 10, 2014 at 10:36 am
SQLRNNR (2/10/2014)
jewel.sacred (2/10/2014)
@Keith Tate Thanks a lot for quick the reply. I already had this in my mind but I am more comfortable with Michelle Ufford's gem (No disrespect to Olla's excellent/great maintenance script). Just curious, this could lead to new execution plans? Hopefully, largely good ones but some bad plans as well. This could also mean a very large log file and sadly I have Standard edition to work with. I see some very hard days ahead where I would be explaining and convincing that some processes were slow for a reason :crazy:Also what if I leave 100 GB space for future growth. Will it cause less fragmentation? OR the result would be the same? Any more ideas on this?
Moreover, what if I re-organize the indexes only?
You can kick off the index maint using her script and force more of a reorg than a rebuild by bumping the thresholds. Reorg will require less log space and is available in standard edition.
When you shrink, I would shrink to a size that leaves about 1.5x the largest table in free space.
Once the reorg is complete - you will probably want to update the statistics on key tables.
1.5x the largest table...this is a very important point you make in addition to other valid points. Thanks. This is why this community is so great 😎
February 10, 2014 at 12:27 pm
Take a backup before doing the shrink and a backup prior to doing what comes up next...
If you don't mind NOT having point-in-time backups while the indexes are being rebuilt (NOT reorganized), then slip the database into the BulkLogged recovery model and REBUILD (not REORGANIZE) all of your indexes. REBUILDing indexes in the Bulk Logged recovery model is a "minimally logged" task.
When you're done, don't forget to return the recovery model to FULL.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 10, 2014 at 1:32 pm
Jeff Moden (2/10/2014)
Take a backup before doing the shrink and a backup prior to doing what comes up next...If you don't mind NOT having point-in-time backups while the indexes are being rebuilt (NOT reorganized), then slip the database into the BulkLogged recovery model and REBUILD (not REORGANIZE) all of your indexes. REBUILDing indexes in the Bulk Logged recovery model is a "minimally logged" task.
When you're done, don't forget to return the recovery model to FULL.
Just be careful with Bulk Logged if PIT is a requirement. There are cases where PIT recovery cannot be accomplished in Bulk Logged should a failure occur.
If needed I can find the reference for that (it's in Gail's book on log management).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 10, 2014 at 7:06 pm
SQLRNNR (2/10/2014)
Jeff Moden (2/10/2014)
Take a backup before doing the shrink and a backup prior to doing what comes up next...If you don't mind NOT having point-in-time backups while the indexes are being rebuilt (NOT reorganized), then slip the database into the BulkLogged recovery model and REBUILD (not REORGANIZE) all of your indexes. REBUILDing indexes in the Bulk Logged recovery model is a "minimally logged" task.
When you're done, don't forget to return the recovery model to FULL.
Just be careful with Bulk Logged if PIT is a requirement. There are cases where PIT recovery cannot be accomplished in Bulk Logged should a failure occur.
If needed I can find the reference for that (it's in Gail's book on log management).
I've pretty much said the same thing although not as sternly. 🙂 Since you brought it up and to cover all of the bases, any minimally logged action that takes place with Bulk Logged turned on will render that particular log file useless for PIT restores. You'll need to use either the whole PIT file or none of it. If you use none of it, that's the end of the restore and you'll lose anything from the beginning of the log file up until the point you wanted to stop the restore at. I'm not sure if a DIF backup has that problem during restores because I don't use DIF's.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 10, 2014 at 7:11 pm
SQLRNNR (2/10/2014)
jewel.sacred (2/10/2014)
@Keith Tate Thanks a lot for quick the reply. I already had this in my mind but I am more comfortable with Michelle Ufford's gem (No disrespect to Olla's excellent/great maintenance script). Just curious, this could lead to new execution plans? Hopefully, largely good ones but some bad plans as well. This could also mean a very large log file and sadly I have Standard edition to work with. I see some very hard days ahead where I would be explaining and convincing that some processes were slow for a reason :crazy:Also what if I leave 100 GB space for future growth. Will it cause less fragmentation? OR the result would be the same? Any more ideas on this?
Moreover, what if I re-organize the indexes only?
You can kick off the index maint using her script and force more of a reorg than a rebuild by bumping the thresholds. Reorg will require less log space and is available in standard edition.
When you shrink, I would shrink to a size that leaves about 1.5x the largest table in free space.
Once the reorg is complete - you will probably want to update the statistics on key tables.
Reorg is fully logged even in BULK LOGGED mode. It's MDF space that it will save on because it doesn't create extra pages like a REBUILD usually will. You could do a CREATE WITH DROP EXISTING and get the best of both worlds. Don't forget that REORGANIZE doesn't reorg the B-TREE if that's important.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 10, 2014 at 7:27 pm
Jeff Moden (2/10/2014)
SQLRNNR (2/10/2014)
jewel.sacred (2/10/2014)
@Keith Tate Thanks a lot for quick the reply. I already had this in my mind but I am more comfortable with Michelle Ufford's gem (No disrespect to Olla's excellent/great maintenance script). Just curious, this could lead to new execution plans? Hopefully, largely good ones but some bad plans as well. This could also mean a very large log file and sadly I have Standard edition to work with. I see some very hard days ahead where I would be explaining and convincing that some processes were slow for a reason :crazy:Also what if I leave 100 GB space for future growth. Will it cause less fragmentation? OR the result would be the same? Any more ideas on this?
Moreover, what if I re-organize the indexes only?
You can kick off the index maint using her script and force more of a reorg than a rebuild by bumping the thresholds. Reorg will require less log space and is available in standard edition.
When you shrink, I would shrink to a size that leaves about 1.5x the largest table in free space.
Once the reorg is complete - you will probably want to update the statistics on key tables.
Reorg is fully logged even in BULK LOGGED mode. It's MDF space that it will save on because it doesn't create extra pages like a REBUILD usually will. You could do a CREATE WITH DROP EXISTING and get the best of both worlds. Don't forget that REORGANIZE doesn't reorg the B-TREE if that's important.
Yes Reorg is fully logged but is not a single transaction like in a rebuild (it's a whole bunch of smaller transactions). So if your tlog is properly sized, you should never see the log grow and the reorg should have minimal impact - compared to a rebuild.:cool:
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply