Database will not shrink 1

[omacron]

Hi

This is a MS SQL Express 2005 database. It was getting close to the 4GB cap so purged some data out. It only got about 200MB back when I was expecting around 800-1,000MB. So I shrunk (MS SQL Management Studio => Expand Server => Expand Databases => Right click on the database => Right click => Tasks => Shrink. Check off "Reorganize files before..." click ok). This didn't make a difference. I ran this script;

ALTER DATABASE [DBNAME] SET AUTO_SHRINK ON
GO
ALTER DATABASE [DBNAME] SET RECOVERY SIMPLE
GO
BACKUP LOG [DBNAME] WITH TRUNCATE_ONLY
GO
DBCC SHRINKDATABASE ([DBNAME], 1, NOTRUNCATE)
GO
DBCC SHRINKDATABASE ([DBNAME], 1, TRUNCATEONLY)
GO

Still no difference. So then I ran;

exec sp_spaceused

Gives the results;

Reserved            Data        Index Size       Unused
3,581,848 KB    2,129,072 KB    545,440 KB    907,336 KB

How do a recover that unused space?

thanks

 

[mrdenny]

Why are you trying to shrink the file? It will just grow again as you add more data to it. Leave the file alone. There's 900 Megs of white space within the database. As time goes on you will slowly fill the space.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[JoeAtWork]

mrdenny - perhaps he is using SQL Server Express, in which case 900 MB is nearly a quarter of his available space.

As time goes on you will slowly fill the space.

My impression has been that this is not the case. I've seen databases that just grow and grow, yet if you shrink them the file size goes down to 10% or less of what it was (which to me proves it did not reuse the available space).

Every once in a while I go back to investigating the "proper" way to reclaim space from bloated database files, and have never found an explanation that I fully understood (I've found SQL statements that will do it, but whether I should run them is not so clear to me). In most cases I just set up backups to run, which tends to do the job but leaves me to wonder if the default settings are not optimal for that particular database.

 

[mrdenny]

And? Unless the disk has little or no free space shrinking the file will simply fragment it.

Database will grow when you rebuild the indexes, run defrag jobs, etc. If the database it growing it is for a reason. If you shrink the database it will just grow again the next time this operation runs. Keeping the database with a small amount of free space simply gives you a false sense of how much free space your disk really has.

Currently my database is sized out to 1TB in size. I've got about 650-700 Gigs of whitespace in the datafiles. I don't have to worry about the database growing for a while now, and when it starts growing my free space alarms will go off and I'll know to check the system, then add disk space as needed followed by a scheduld growth of the database.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[JoeAtWork]

And? Unless the disk has little or no free space shrinking the file will simply fragment it.

Yes, that's the main reason why shrinking databases comes up - running out of disk space. More and more custom and commercial applications these days use SQL Server Express, and there you have a hard limit of 4 GB. Even if you have a standard version of SQL Server, there are many small and medium sized businesses that simply do not have terrabytes of disk space (think "Small Business Server"). For some of my customers it has not been so much the space on the database server as the space available on their backup medium.

So let's assume for the sake of this discussion adding more disk space is not an option. What then is a good strategy to follow if one's database is approaching the size limit?

One of the products I have the responsibility to support uses SQL Server Express when the product is installed as a standalone workstation. To handle running out of space, we have an "Archive" function that simply moves old records to another database, then deletes them from the main database. But the deletion often does not seem to recover as much space as one might expect (e.g. we delete 90% of the records but the database files only shrink by 10%, or not at all). Only if we do a shrink operation do we see the database files get significantly smaller.

Is it not valid to reclaim the majority of the white space left after a large delete operation? Is there something more appropriate than using a ShrinkDatabase operation?

 

[mrdenny]

As you pointed out with SQL Express you have a 4 Gig database size limit. As you delete data from the database that leaves white space within the database file. As new data is written to the database that data will be written to the white space within the database file.

If your hard drive is so full that you can't leave the database file at 4 Gigs then buy another hard drive. You can pick up a 500 Gig drive for a computer for less than $200 US at most computer stores.

As for databases which are running on Workgroup or Standard edition as part of Small Business server you may need to look into having a database professional look at the database. This will cost you a few hours of consulting time to setup an archiving strategy to make sure that the database is setup and working correctly.

As your database is getting larger (growing into the 20+ gigs of space) not maintaining the database correctly will lead to slower and slower performance of the database.

Just by deleting data from the tables in the main database doesn't mean that the database will allow you to shrink the database file by that amount. You have fill factor of the database indexes to take into account as well as system space, possible data fragments left over from TEXT or NTEXT data types (if using SQL 2000), GAM usage, etc.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[JoeAtWork]

As new data is written to the database that data will be written to the white space within the database file.

Thankyou MrDenny, that is the key piece of information that is useful for me. I had been under the impression that the white space is not reused, and if I saw the database size close to 4GB (for SQL Express) that I would soon run out of space.

One remaining question - can I be sure that most of the white space (or the number in the "unused" column of sp_spaceused) will actually be used before SQL Server tries to increase the size of the file again? I'm assuming it doesn't wait until every bit of white space is used up before it tries to grow the file again. Could I get an "out of space" error because it tried to grow the file beyond 4GB, even though there is still enough unused space availabe for the operation I am trying to perform?

 

[mrdenny]

No SQL won't use every bit of white space before it tried to grow again. You'll need rebuild the indexes so that the whitespace in the indexes is correctly relaidout within the file. This won't allow SQL to use all the free space, but it will get closer.

There is no way to get SQL to use 100% of the space in the table as it keeps white space available within the data pages on the disk so that it can insert data in the correct location within the indexes and the tables.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog