Databas Shrink
-
Hello there,
I have a database where we have archived and purged 95% of the data, leaving a 230GB file where only 7GB of it is in use by the data. As part of the purge script I have written, we need to conduct a shrink on the data file down to 50GB, leaving room for growth.
The collection of dbcc shrinkfile commands on my workstation, a quad core, with 12GB, takes around 5 minutes to complete. On our test kit, which has it's own dedicated SAN with SSDs, oodles of memory and 80+ cores takes over an hour.
I also tried creating a new filegroup, moving all the data across to the new filegroup, and shrinking the now empty primary file, and it still takes over an hour.
I know that database shrinking is bad, but under these circumstances it is warranted.
I'm at a loss as to why this may be. The SQL log show's nothing out of the ordinary. Does anyone have any suggestions ?
-
Paul Treston (5/24/2013)
Hello there,I have a database where we have archived and purged 95% of the data, leaving a 230GB file where only 7GB of it is in use by the data. As part of the purge script I have written, we need to conduct a shrink on the data file down to 50GB, leaving room for growth.
The collection of dbcc shrinkfile commands on my workstation, a quad core, with 12GB, takes around 5 minutes to complete. On our test kit, which has it's own dedicated SAN with SSDs, oodles of memory and 80+ cores takes over an hour.
I also tried creating a new filegroup, moving all the data across to the new filegroup, and shrinking the now empty primary file, and it still takes over an hour.
I know that database shrinking is bad, but under these circumstances it is warranted.
I'm at a loss as to why this may be. The SQL log show's nothing out of the ordinary. Does anyone have any suggestions ?
I've had this before when a process is using the DB I was trying to shrink. run an SP_who2\SP_whoisactive to find out what it is.
However, do you actually need to reclaim the space on physical disk? If not then why shrink?
If you absolutely do need to shrink make sure that you run your reindexing jobs immediately after as you will have some nice fragmentation after the shrink. If this is production make sure you have planned for some downtime.
[font="Times New Roman"]There's no kill switch on awesome![/font] -
Loundy (5/24/2013)
I've had this before when a process is using the DB I was trying to shrink. run an SP_who2\SP_whoisactive to find out what it is.
However, do you actually need to reclaim the space on physical disk? If not then why shrink?
If you absolutely do need to shrink make sure that you run your reindexing jobs immediately after as you will have some nice fragmentation after the shrink. If this is production make sure you have planned for some downtime.
Hi Loundy, thanks for your comments.
- This is a standalone database on our test server so is not being actively used, only by our maintenance work
- Yes, we need to shrink it back down
- The last step of the maintenance is to do a rebuild of all indexes so this is covered.
- Yes, we will have a maintenance downtime window when we do get to run this in Production.
-
its unlikely to shrink while there is an active connection, try disabling the account that's connecting and try again. Ensure you are aware of the impact of doing that first though.
[font="Times New Roman"]There's no kill switch on awesome![/font] -
Loundy (5/24/2013)
its unlikely to shrink while there is an active connection, try disabling the account that's connecting and try again. Ensure you are aware of the impact of doing that first though.I think you may have misread my previous post. There are no active connections to the database, only the one connection that is performing the shrink.
-
Paul Treston (5/24/2013)
Loundy (5/24/2013)
its unlikely to shrink while there is an active connection, try disabling the account that's connecting and try again. Ensure you are aware of the impact of doing that first though.I think you may have misread my previous post. There are no active connections to the database, only the one connection that is performing the shrink.
oops sorry,
How long have you left the process running for? I've had a database (none prod) take hours before. It did eventually finish though....
[font="Times New Roman"]There's no kill switch on awesome![/font]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply