Can't shrink a database

Hi there,
I am running SQL2005 SP1 Standard.
I have a database that was originally a SQL2K db that was rolled up to 2K5.
Everything was fine with it until I truncated a few tables and dropped a
large column & index on a few tables. Since then the following symptoms are
present:
1) The size of the datafile and log file are about 2.5x the size of the
actual data
2) The DB properties ALWAYS shows "Space Available" as "0.00 MB"
3) The shrink dialog with the file type set to "Log" shows avaialble free
space to be 98% but shrinking it does not release any of the free space.
I have tried backing up the database, deleting it and restoring it but the
inflated log file still exists. I've also tried creating a new blank DB
(versus letting the restore create it) and restoring into that but the
problem persists.
Does anyone have any ideas on how to shrink this database down?
Thanks,
--
-Steven1) Is it the data or the log file which is "too big"?
2) Read about DBCC UPDATEUSAGE
3) See http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steven Berkovitz" <mbcdev@.community.nospam> wrote in message
news:F4B6BA40-9143-4D52-BC8F-3FB9CFBEB133@.microsoft.com...
> Hi there,
> I am running SQL2005 SP1 Standard.
> I have a database that was originally a SQL2K db that was rolled up to 2K5.
> Everything was fine with it until I truncated a few tables and dropped a
> large column & index on a few tables. Since then the following symptoms are
> present:
> 1) The size of the datafile and log file are about 2.5x the size of the
> actual data
> 2) The DB properties ALWAYS shows "Space Available" as "0.00 MB"
> 3) The shrink dialog with the file type set to "Log" shows avaialble free
> space to be 98% but shrinking it does not release any of the free space.
> I have tried backing up the database, deleting it and restoring it but the
> inflated log file still exists. I've also tried creating a new blank DB
> (versus letting the restore create it) and restoring into that but the
> problem persists.
> Does anyone have any ideas on how to shrink this database down?
> Thanks,
> --
> -Steven|||Hi Steven,
I would like to know the issue status and whether or not you need further
research.
You can try setting the recovery mode to simple, backing up your database
completely, truncating the log with BACKUP LOG ... WITH TRUNCATE ONLY, and
then use DBCC SHRINKFILE to shrink the log file.
Have a great day!
Charles Wang
Microsoft Online Community Support