> I have about 40 Gb database where is about 98% free space (I deleted data
> from tables).
>
> dbcc checkdb -> OK.
> dbcc reindex all tables -> OK
>
> Shrink database (with reorganize and release unused pace) -> no any
> affets, data file size will be same still, 98% free space.
> Log size 1 Mb.
>
> Shrink datafile with log works fine, but not with datafile (No any errors
> occured).
>
> I tried without any s:
> - Database full backup->restore to different SQL Server -> same problem in
> other server.
> - Change recovery model to full and back to simple.
> - compability level 80 and 90.
>
>
> I know that lastest "option" is that I should script the database and copy
> data from old to new one,
> but do you have any other ideas?`

This is now resolved:

I see that dbcc checktable shows that there were xx rows in xxxx pages,
but the table was empty (rows were deleted from table, count(*) was zero).

I should drop all foreign keys, truncate all empty tables and recreate
foreign keys.
After it shrink file will fork fine, now database size is 50 Mb ;-)...

Major Drake (major.drake@pp.inet.fi.HALOOOOOOO.fi) writes:
> I see that dbcc checktable shows that there were xx rows in xxxx pages,
> but the table was empty (rows were deleted from table, count(*) was zero).

Page and extent deallocation can be deferred for large deletes, so that they
are not deleted until later. Although I think I've seen situations where
it never came to the end of the road. Reindexing the table could have
ed and saved you from dropping the FKs.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx