March 20, 2013 at 8:35 am
In the properties of a user database, we can obtain the database size and the available space. We can shrink the database such that the available space is zero.
However, if we execute sp_spaceused against this database, we can see the unused space is not zero.
How to interprete the unused space in executing the stored procedure?
Is there any way in removing the unused space?
Many thanks in advance for any input.
March 20, 2013 at 8:47 am
There's a limit to how small you can shrink a database. I can't remember off the top of my head what it its - I think it's either the size of the model database or the size of the database when it was created?
Why do you want to do this, anyway? If you shrink the database, it will only grow again, unless you're going to set it as read only.
John
March 20, 2013 at 8:54 am
Are you talking about a production db. I would not shrink a production db POINT BLANK.
March 20, 2013 at 8:59 am
The auto grow setting on the db has been triggered at some point and the file/s have been extended. Allowing data to be written into the free space. If you shrink it the db will grow again anyway. If you lower the setting it will extend the file more frequently at a cost of performance each time.
Make sure the auto grow is set to an appropriate amount of MB and not %. All dbs should have a decent amount of free space to grow into.
See it like this: Its the same as buying clothes for small children. You never buy clothes that just fits today always buy the size up so they can grow into it.
March 20, 2013 at 9:03 am
John Mitchell-245523 (3/20/2013)
There's a limit to how small you can shrink a database. I can't remember off the top of my head what it its - I think it's either the size of the model database or the size of the database when it was created?Why do you want to do this, anyway? If you shrink the database, it will only grow again, unless you're going to set it as read only.
John
Thank you so much for your input promptly.
If your statement is true, then the info on the properties of a database is confusing. The info on the properties of a database confuses people when they refer to the results when executing the sp_spaceused.
The setting in the model system database is used to set the default values. We may change any setting when creating any database, and also, we can change these settings after creating the database.
Shrinking the database such that it has zero available space is requested by our users. They use ithe database for creating reports.
Once again, tons of thanks to you.
March 20, 2013 at 9:04 am
Bobby Glover (3/20/2013)
Are you talking about a production db. I would not shrink a production db POINT BLANK.
Thank you for your input. Unfortunately, I am asking a solution not asking why I want to do so.
March 20, 2013 at 9:07 am
Bobby Glover (3/20/2013)
The auto grow setting on the db has been triggered at some point and the file/s have been extended. Allowing data to be written into the free space. If you shrink it the db will grow again anyway. If you lower the setting it will extend the file more frequently at a cost of performance each time.Make sure the auto grow is set to an appropriate amount of MB and not %. All dbs should have a decent amount of free space to grow into.
See it like this: Its the same as buying clothes for small children. You never buy clothes that just fits today always buy the size up so they can grow into it.
Thank you for your input.
After executing the sp_spaceused stored procedure, I checked the properties of the database, the available space is still zero.
The database is used for creating reports, no data modification occurs.
March 20, 2013 at 9:11 am
SQL ORACLE (3/20/2013)Shrinking the database such that it has zero available space is requested by our users. They use ithe database for creating reports.
You're the database professional - it's OK, indeed expected, for you to tell users that they are making an inappropriate request. Users should only be concerned what data is in the database; management of free space is your job.
Unfortunately, I am asking a solution not asking why I want to do so.
Finding out why you want to do something helps us to understand your problem, and perhaps even propose a solution that you hadn't considered.
John
March 20, 2013 at 9:13 am
You do realize that shrinking a database will fragment the indexes used by the queries run to generate the reports and that this will impact the performance of the reporting database.
March 20, 2013 at 9:54 am
Unused space returned by sps_spaceused will almost never be zero.
BOL says: Total amount of space reserved for objects in the database, but not yet used.
This is extents or pages that have been allocated to an objects but are not completely full.
This is particularly true of extents (8 pages). An extent may be reserved for a given object but may have as many as 7 pages free.
March 20, 2013 at 2:12 pm
Lynn Pettis (3/20/2013)
You do realize that shrinking a database will fragment the indexes used by the queries run to generate the reports and that this will impact the performance of the reporting database.
Technically I think it's more accurate to state:
shrinking a database will very likely fragment the indexes used by the queries run to generate the reports, and that this will impact the performance of the reporting database.
That is, it's not 100% guaranteed to fragment index(es), but it is very likely.
Because of all the factors everyone has stated, shrinking a db to regain a (relatively) small amount of just isn't worth it. Indeed, shrinking the db and then rebuilding the resulting fragmented index(es) often results in a larger db than when you started out!
If you genuinely need to shrink the db, there are some steps you can take to get the more shrinkage with less fragmentation, but it's extra effort.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
March 21, 2013 at 3:47 am
Don’t see what you are trying to achieve, are you low on disk space.
If the db is read only. Then set it has read only. If you are low on disk space. Increase the disk space or dettach the db and move to a larger disk.
Users should not be affected by what is free in the db. It has no bearing to what they are doing. If the ldfs are on the same drive move these to a separate drive.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply