by Here's my problem:
I have a database (web_db1) which consists of two data files (and of
course a transaction log file which is not the focus here):
web_db1_Data.mdf
web_db1_Data2_Data.mdf
The first file web_db1_Data.mdf is HIGHLY fragmented. I let it
auto-grow from 1MB over a long period of time on a disk which gets a
lot of usage from other processes. The file is now 6.5GB in size and
the data only consumes 1.7 GB of it. When I try to run:
USE web_db1
GO
DBCC SHRINKFILE (web_db1_Data)
I get:
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Then I look at the size of the file in explorer and the file size is
the same, 6.5GB.
I've also tried running:
DBCC SHRINKFILE (web_db1_Data, 2000)
Same...
DBCC SHRINKFILE (web_db1_Data, 2000, TRUNCATEONLY)
Same...
DBCC SHRINKFILE (web_db1_Data, 0, EMPTYFILE)
This command moved all the user data over to the other data file, but
it did not shrink the file.
I cannot shrink this file! All I want to do is defrag it by shrinking
the file in SQL, stopping SQL service, copy the small file out to a
net resource, copy it back in, start SQL Service, pre-allocate a full
6GB, and have a contiguious 6G data file.
What I gathered from reading, was that the first data file defined is
the primary FILE (I'm not using any other filegroup besides PRIMARY),
which contains all the system tables (i.e.:sysobjects) which cannot be
dropped with ALTER DATABASE REMOVE FILE *Correct me if I'm wrong*. Why
won't DBCC SHRINKFILE(DataFile1, 0 , EMPTYFILE) work on primary files?
Any help would be greatly appreciated!
-Joanthan Davis