Cannot empty a data file to another using DBCC SHRINKFILE EMPTYFILE

Hi,
A month ago, we were running out of disk space on a drive that stores
the data file for a SQL Server 7 database. So I added a new drive and
created a new data file on the primary filegroup and restricted the
growth of the first data file.
We have a new server now with heaps of space and I want to merge the two
data files into one but am running into huge issues. The reason why I
want to do this is to eliminate the possibilty that the creation of the
new data file is why our database has started to run a bit slower.
When I try to execute the DBCC SHRINKFILE data2,EMPTYFILE) I get the the
result "DBCC execution completed. If DBCC printed error messages,
contact your system administrator." within a matter of seconds... the
data2 file is still sitting on 11GB and the data1 file is on 28GB
(restricted file growth)... I noticed that the log file grew
significantly and the data2 file emptied out a little but it was still
at 11GB...
I have tried doing log backups, database backups then shrinking again
but still nothing...
If anyone knows how to get rid of the second primary data file it would
be MUCH appreciated...
Regards,
Auday Alwash
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
What service pack level do you have? There was a bug in SQL 7.0 with DBCC
SHRINKFILE with the EMPTYFILE option where heaps (tables with no clustered
index) & text pages were not always emptied properly - it was fixed in SP3.
For more info see: http://support.microsoft.com/kb/279511/EN-US/
HTH.
Cheers,
Mike
"Auday Alwash" <odies@.hotmail.com> wrote in message
news:eN7xN2RzEHA.3408@.tk2msftngp13.phx.gbl...
> Hi,
> A month ago, we were running out of disk space on a drive that stores
> the data file for a SQL Server 7 database. So I added a new drive and
> created a new data file on the primary filegroup and restricted the
> growth of the first data file.
> We have a new server now with heaps of space and I want to merge the two
> data files into one but am running into huge issues. The reason why I
> want to do this is to eliminate the possibilty that the creation of the
> new data file is why our database has started to run a bit slower.
> When I try to execute the DBCC SHRINKFILE data2,EMPTYFILE) I get the the
> result "DBCC execution completed. If DBCC printed error messages,
> contact your system administrator." within a matter of seconds... the
> data2 file is still sitting on 11GB and the data1 file is on 28GB
> (restricted file growth)... I noticed that the log file grew
> significantly and the data2 file emptied out a little but it was still
> at 11GB...
> I have tried doing log backups, database backups then shrinking again
> but still nothing...
> If anyone knows how to get rid of the second primary data file it would
> be MUCH appreciated...
> Regards,
> Auday Alwash
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Hi Mike,
SQL Server is on Service Pack 3 already...
Can you think of anything else that could be causing this?
Regards,
Auday
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Auday
It removes the file which was emtied.
CREATE DATABASE test
GO
ALTER DATABASE test
ADD FILE
( NAME = ww,
FILENAME = 'D:\wwdat1.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
create table test..test(id int identity) on [primary]
--Performed many many inserts here.
dbcc shrinkfile(ww,EMPTYFILE)
GO
ALTER DATABASE test REMOVE FILE ww
select * from test.dbo.sysfiles
"Auday Alwash" <odies@.hotmail.com> wrote in message
news:OBXOVjUzEHA.1452@.TK2MSFTNGP11.phx.gbl...
> Hi Mike,
> SQL Server is on Service Pack 3 already...
> Can you think of anything else that could be causing this?
> Regards,
> Auday
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Make sure there is no one using data in the file... I can imagine that would
prevent the data movement...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Auday Alwash" <odies@.hotmail.com> wrote in message
news:eN7xN2RzEHA.3408@.tk2msftngp13.phx.gbl...
> Hi,
> A month ago, we were running out of disk space on a drive that stores
> the data file for a SQL Server 7 database. So I added a new drive and
> created a new data file on the primary filegroup and restricted the
> growth of the first data file.
> We have a new server now with heaps of space and I want to merge the two
> data files into one but am running into huge issues. The reason why I
> want to do this is to eliminate the possibilty that the creation of the
> new data file is why our database has started to run a bit slower.
> When I try to execute the DBCC SHRINKFILE data2,EMPTYFILE) I get the the
> result "DBCC execution completed. If DBCC printed error messages,
> contact your system administrator." within a matter of seconds... the
> data2 file is still sitting on 11GB and the data1 file is on 28GB
> (restricted file growth)... I noticed that the log file grew
> significantly and the data2 file emptied out a little but it was still
> at 11GB...
> I have tried doing log backups, database backups then shrinking again
> but still nothing...
> If anyone knows how to get rid of the second primary data file it would
> be MUCH appreciated...
> Regards,
> Auday Alwash
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!