SQL Server Performance Forum – Threads Archive

Shrink Database command fails

Hi, Our database is keep growing and we would like to reclaim the unused space. It has come to 5 GB, but the actual data should be in 500 MB or so. I executed the following command, DBCC SHRINKDATABASE(N’Test1′, 10, TRUNCATEONLY) It gives the following error, DBCC SHRINKDATABASE: File ID 1 of database ID 19 was skipped because the file does not have enough free space to reclaim.
Cannot shrink log file 2 (Test1_log) because all logical log files are in use. (1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator. I stopped the services and restarted, when I try it again, it is throwing the same error, please help me to fix this, thank you. Regards,
Deva

Hi,why dont u shrink individual files instead of dbs using dbcc shrinkfile………
http://msdn2.microsoft.com/en-us/library/aa258824(SQL.80).aspx
Regards
Deepak
SQL DBA

Why do you need to shrink, do you have any disk space issues?
Also check what kind of jobs, processes are running on daily & weekly basis, if you have weekly DBCC checks & reindexing jobs then you will huge transaction log and not performing the transaction log backups. http://sqlserver-qa.net/blogs/tools/archive/2007/06/19/transaction-log-guidelines.aspx
http://sqlserver-qa.net/blogs/tools…nable-to-shrink-the-transaction-log-help.aspx too if you are unable to shrink. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Are you using sql server 2005? You can’t shrink the database less than its true size…
I believe it is message/warning not the error… run DBCC UPDATEUSAGE and check the database size and free space if there is any run the dbcc shrinkfile or database and see what you get…
MohammedU.
Microsoft SQL Server MVP
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

]]>