question

ervaibhavg avatar image
ervaibhavg asked

Unable to Shrink Database file in SQL 2000.

We have a Database of around 300gb in Sql 2000. One of its data file has grown to 183 gb in which 141gb is used space and 42 gb is free space. This file already consumed 100% of its drive space and we have a company policy of 10% free space on each drive that's why I need to shrink this file. I have tried dbcc shrinkfile(), GUI feature & one more script which shrink the file in batches. But none of the method worked. Scripts Keep on executing without any result. Please suggest.
shrink-database
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ramesh 1 avatar image
ramesh 1 answered
how to u try to shrink the 1. DBCC SHRINKDATABASE 2. DBCC SHRINKFILE (N'DemoSuspect' , 2)--- this one for shrinknig the mdf filw 3. DBCC SHRINKFILE (N'DemoSuspect_log' , 0)-- this is used to shrink the log (LDF) file of a database
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

vaiydeyanathanvs avatar image
vaiydeyanathanvs answered
Try the following options. DBCC SHRINKDATABASE (YourDBname, Size to allow free space) GO autoshrink - Check if your db is True (Turned On) for auto shrinking.... DBCC SHRINKFILE (Give your MDF/LDF name, Specify the number to shrink the size of your current file.) GO
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ervaibhavg avatar image
ervaibhavg answered
I have tried all these options..I even find one query to shrink database in small chunks...But none of them worked..Whenever I execute any statement to shrink this mdf file the underlined process is getting blocked by itself leaving the process in hang state. Hence it is still consuming total space of the drive
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.