April 13, 2016 at 2:19 pm
We had installed SQL Server 2008 r2 standard edition on a standalone server. The vendor requested 20 GB for one of their databases (this was done a while back). I thought the database size request was a bit much but they insisted so we did it. Well now we're moving it, took a few minutes to see what was actually used.
exec sp_spaceused yielded the following:
database_sizeunallocated space
20481.00 MB 20476.27 MB
While the application is actually pretty interesting, database storage allocation requested was...well you can see for yourself. The database has not grown. Rather than continue to use that kind of storage, we ran a "DBCC SHRINKFILE('filename', 100)", reducing the size down to 100 MB. After that we ran update statistics against the database.
My question - are there alternatives to this?
April 13, 2016 at 4:37 pm
Did you also rebuild the indexes?
April 23, 2016 at 2:15 pm
alternatives to shrinking to reclaim space? No.
However, as Lynn noted, indexes should be rebuilt. Once you shrink, you might dramatically fragment data. Worth keeping 2x your largest table in free space and rebuilding indexes.
April 28, 2016 at 10:34 pm
If you want to free the space at windows level, then Shrinking is the only way, no other alternative.
and yes, as other said, do rebuild the indexes.
Also, set the auto_growth on with reasonable space(depending on the requirements) as frequent auto_growth can be an unwanted overhead to the SQL Server.
April 29, 2016 at 6:29 am
Has it been this way for a year? I usually go back to my customers after a year with a usage report.
basically something like this
This is what you said you needed:
Here is what you actually used:
Here is my recommendations:
Usually a cost breakdown of space, If they are paying those costs, motivates them to "shrink" their DB sizes.
May 11, 2016 at 12:11 am
This was removed by the editor as SPAM
May 11, 2016 at 12:34 am
Sorry to hijack this thread, but I've always read and been told that if disk space is needed instead of Shrinking the .mdf to reclaim the unallocated space, it should be avoided due to problems that could occur with the DB. Always got told to throw more disk space as its easier, cheaper & less chance of failures.
I know the op's scenario is different as its a choice compared to the above where if you don't reclaim or allocate more you will eventually run out.
Have I just been told or read incorrect information??
edit: submitted too early
May 11, 2016 at 9:49 am
There's nothing inherently wrong with shrinking the database, it's not that it's unsafe and it has a very specific purpose that it achieves. The problem is when people don't understand what exactly it's doing or more importantly why their database was using so much space to begin with and see ZOMG free space I should shrink the file.
There's usually no harm in leaving a large .mdf around even it's mostly free space, what you're probably seeing is that people are saying never use shrinkdatabase because some people misuse it, for example running an automated shrink after running their automated index rebuild.
May 11, 2016 at 4:16 pm
ZZartin (5/11/2016)
There's nothing inherently wrong with shrinking the database, it's not that it's unsafe and it has a very specific purpose that it achieves. The problem is when people don't understand what exactly it's doing or more importantly why their database was using so much space to begin with and see ZOMG free space I should shrink the file.There's usually no harm in leaving a large .mdf around even it's mostly free space, what you're probably seeing is that people are saying never use shrinkdatabase because some people misuse it, for example running an automated shrink after running their automated index rebuild.
Lets say in a scenario, i have 5gb left out of 100gb on my drive. The .mdf is the only thing on this drive and takes 95gb.
From.this 95 there is 90gb unallocated space, so if shrunk i would reclaim 90gb.
If I decide to shrink and it works- great no problem.... but nothing is ever that simple in life.
What can go wrong? Shrink just doesnt work or something is corrupt.
Is it just a simple process of backup prior, restore backup and any logs if required or can you fix it without needing to do any restore
May 11, 2016 at 4:29 pm
Tava (5/11/2016)
ZZartin (5/11/2016)
There's nothing inherently wrong with shrinking the database, it's not that it's unsafe and it has a very specific purpose that it achieves. The problem is when people don't understand what exactly it's doing or more importantly why their database was using so much space to begin with and see ZOMG free space I should shrink the file.There's usually no harm in leaving a large .mdf around even it's mostly free space, what you're probably seeing is that people are saying never use shrinkdatabase because some people misuse it, for example running an automated shrink after running their automated index rebuild.
Lets say in a scenario, i have 5gb left out of 100gb on my drive. The .mdf is the only thing on this drive and takes 95gb.
From.this 95 there is 90gb unallocated space, so if shrunk i would reclaim 90gb.
If I decide to shrink and it works- great no problem.... but nothing is ever that simple in life.
What can go wrong? Shrink just doesnt work or something is corrupt.
Is it just a simple process of backup prior, restore backup and any logs if required or can you fix it without needing to do any restore
Just as long as you rebuild your indexes afterward. The shrink operation will result in fragmentation of your indexes.
May 11, 2016 at 5:25 pm
Lynn Pettis (5/11/2016)
Just as long as you rebuild your indexes afterward. The shrink operation will result in fragmentation of your indexes.
Yeap, that's part of the process that should be followed after a shrink but I'm talking more so bout the problems a shrink could cause.
for e.g. - perform a shrink and for some reason the DB goes into Suspect mode or becomes corrupted? Can this happen with a shrink DB? is it likely / unlikely?
If something gets corrupted from a shrink is it easy to fix with a command or does it require the whole process like anything restore db backup etc..
May 12, 2016 at 7:58 am
As Lynn mentioned, you need to rebuild indexes, or at least check them for fragmentation. Which means Shrink should not be used to clear all space out. Enough space should be left for maintenance operations.
Shrink is a simple procedure with no intelligence. It merely moves all used extents/pages from the end of the file to the first open space near the beginning. This means that if you have contiguous extents for your clustered index, an efficient format of storage, they extents can get moved all out of order. There's no guarantee, and this can impact performance. Both SELECT performance for reads and insert/update performance when page splits occur.
If you create a 100GB mdf, add 1GB of data and then shrink to 10GB, you might be OK. This is more of an issue as your database is used over time, inserts/updates/deletes cause page splits and new allocations, or leave holes. In these cases, shrink can be a problem. So don't use it as maintenance (or every use AUTO SHRINK). Instead, manage space.
May 12, 2016 at 8:22 am
Tava (5/11/2016)
Lynn Pettis (5/11/2016)
Just as long as you rebuild your indexes afterward. The shrink operation will result in fragmentation of your indexes.
Yeap, that's part of the process that should be followed after a shrink but I'm talking more so bout the problems a shrink could cause.
for e.g. - perform a shrink and for some reason the DB goes into Suspect mode or becomes corrupted? Can this happen with a shrink DB? is it likely / unlikely?
If something gets corrupted from a shrink is it easy to fix with a command or does it require the whole process like anything restore db backup etc..
No I don't think there's any extra inherent danger of your database randomly going into suspect mode during a shrink than any other operation. It's not doing any magic to move data around it's just using different logic to determine where to move the data to in the data file.
But in general if you don't need the space why are you worried about it? And if you need the space you don't really have a choice and your database should be meeting whatever your restore SLA is regardless of what operation you're doing on it.
May 12, 2016 at 5:22 pm
thanks for clearing all that up, appreciate it
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply