SQL 2005 Unshrinkable

[Ovatvvon]

Hello all,

I have a database that is currently 228 GB in size. Its initial size was set to something like 200 GB. I extended it to 210, then temporarily to 220 GB. Another DBA extended it to a 240 GB limit, and now the client is sitting at 228 GB. This is consuming all the space on our backup drive once 3 days of retention is reached...which only happens if a backup job fails, so old backups do not get deleted.

The problem is, the client is using a lower amount of data now, but the database will not release the unused space. I've attached a screenshot image of the Disk Usage Report for the database.

I've tried shrinking the data file, and also reorganizing the data within befor the shrink, but no matter what, it will not release the unused space.

Does anyone know of any secretes on how I can get rid of it, so I can lower the capped growth size on the DB to 215 GB again?



-Ovatvvon :-Q

 

[mrdenny]

When you run the DBCC command it should give you a message on the messages tab. What is outputed there?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[Ovatvvon]

I had not used DBCC before, only the GUI.

Tried DBCC ShrinkFile, but no space was freed.

The actual results are shown in the screenshot file attached to this post.

DBID: 6
FileID: 1
CurrentSize: 29266168
MinimumSize: 11796480
UsedPages: 29254536
EstimatedPages: 29254536

-Ovatvvon :-Q

 

[SQLBill]

Have you tried to alter the database to a smaller size? Remember, shrink will not reduce below the size it was set to be.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[Ovatvvon]

Hi SQLBill,

I think using the Shrink Database, you cannot shrink below the initial size, but according to the MSDN website (

http://msdn2.microsoft.com/en-us/library/ms189080.aspx),

you should be able to shrink it smaller than the initial size if you use the ShrinkFile command.

However, you can shrink the individual database files to a smaller size than their initial size by using the DBCC SHRINKFILE statement. You must shrink each file individually, instead of trying to shrink the whole database.

I did not try an Alter Database...didn't even think about it. I'll look into that and get back to you on it.



-Ovatvvon :-Q

 

[mrdenny]

What was on the Messages tab when you the DBCC command?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[Ovatvvon]

Hi Denny,

Sorry I forgot to include that...

(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

-Ovatvvon :-Q

 

[mrdenny]

Now that I look at your screenshot again... According to your screenshot the database is 223 Gigs and there is 223 Gigs of data in it. There is about 91 Megs (90.875 by my math) of free space within the database.

It would appear that some of the data wasn't actually deleted.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[Ovatvvon]

Ok, so I've been trying to differentiate between Unused and Unallocated space.

Unallocated is space that is set aside for the database, but has no data in it.

Where does the Unused space come in? Is that data that is no longer linked the database somehow, but was not removed from the disk space and database directly? The unused space is the part that will not shrink / disappear. It comprises just under 24% of the database space.


-Ovatvvon :-Q

 

[mrdenny]

Unused space is space which has been allocated to a database object but as yet has no data in it.

Unallocated space is space within the data file but the data page has not been assigned to a data object.

You can end up with a lot of unused space if you have a table with large records and a lot of white space at the end of the page which can't be used.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[Ovatvvon]

So, with 23.77 Unused space in the data file, you believe there is that much orphaned data? (i.e. The database thinks the data was deleted, but it physically still resides there?)

How would one remove such data if so?


-Ovatvvon :-Q

 

[Ovatvvon]

Argh, nevermind Denny, I overlooked your third comment about the whitespace. I knew about the whitespace aspect, but this has only been a problem very recently for them, and to my knowledge, nothing changed with the structure of teh database, or storage there-in. I will contact the client, however, and verify that, since the application itself is a Vendor app. "I'll Be Back..."


-Ovatvvon :-Q