Cannot shrink database

[mkal]

I have a database w/ the following spec's.

Total Size: 37769.69MB
Free Space: 10078MB
Used Space: 22691.69MB

I want to remove the free space from the database. I've tried the following

dbcc shrindatabase('dbName',0, truncateonly)
dbcc shrinkdatabase('dbNmae', 23000)
dbcc shrinkfile('dbName_Data', 0,truncateonly)
dbcc shrinkfile('dbNmae', 23000)

None of these statements has changed the files size. So what am I missing?

Thanks

 

[mrdenny]

Please post the output of dbcc shrinkfile command when you run it. Please post the data from both the results and the messages tab.

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

 

[mkal]

dbcc shrinkdatabase('WarehouseDev',0, truncateonly)
dbid FieldId CurSize MinSize UsedPages EstPages
7 1 4194520 256000 1281592 1281560
7 2 3185 3185 3184 3184

dbcc shrinkdatabase('WarehouseDev', 23000)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.


dbcc shrinkfile('WarehouseDev_Data', 0,truncateonly)
dbid FieldId CurSize MinSize UsedPages EstPages
7 1 4194520 256000 1281592 1281560

dbcc shrinkfile('WarehouseDev_Data', 23000)
This is still running 35min and counting.

Also after some investigation I found this:

http://support.microsoft.com/default.aspx?scid=kb;en-us;324432

the thing is the table that holds the image data has been dropped.

Best Regards,
Mike

 

[mrdenny]

I have seen in the past where dropping a table with blob data in it still leaves data fragments within the datafile which prevent the data file from being shrunk correctly.

This may be the issue that you are seeing.

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

 

[SQLWilts]

You are shrinking the data file - what size is the log? Is the size problem in the log?
what's the size of the log file?

 

[JohnBates]

You might try updating statistics on the largest tables and then run UpdateUsage against the database. I have seen instances where SQL Server reports an incorrect size.

HTH, John