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.
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
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
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