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!

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!

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.developersdex.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.developersdex.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.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Cannot delete SQL 2000 backup file

I have a .bak file that I am trying to delete because the backup failed due
to insufficient space on the disk drive. The message I get is that it is
in use by another person or program. I cannot find the person or program.
What should I be looking for? I use native SQL backup.
Message posted via http://www.sqlmonster.com
You may have to stop the SQLServerAgent service so that it will release the
file.
If that doesn't work (try the MSSQLServer service). One of the two is
holding on to the file.
Rick Sawtell
MCT, MCSD, MCDBA
"Debra Camacho-Padron via SQLMonster.com" <forum@.SQLMonster.com> wrote in
message news:2a3f51e677de49ec92f681f87f14936c@.SQLMonster.c om...
> I have a .bak file that I am trying to delete because the backup failed
due
> to insufficient space on the disk drive. The message I get is that it is
> in use by another person or program. I cannot find the person or program.
> What should I be looking for? I use native SQL backup.
> --
> Message posted via http://www.sqlmonster.com
|||That's what I thought too. I tried both, and it still indicates that the
file is in use.
Message posted via http://www.sqlmonster.com
|||Try using FileMon from sysinternals to see what is accessing
the file:
http://www.sysinternals.com/ntw2k/source/filemon.shtml
-Sue
On Tue, 15 Mar 2005 18:01:24 GMT, "Debra Camacho-Padron via
SQLMonster.com" <forum@.SQLMonster.com> wrote:

>That's what I thought too. I tried both, and it still indicates that the
>file is in use.

Cannot delete SQL 2000 backup file

I have a .bak file that I am trying to delete because the backup failed due
to insufficient space on the disk drive. The message I get is that it is
in use by another person or program. I cannot find the person or program.
What should I be looking for? I use native SQL backup.
Message posted via http://www.droptable.comYou may have to stop the SQLServerAgent service so that it will release the
file.
If that doesn't work (try the MSSQLServer service). One of the two is
holding on to the file.
Rick Sawtell
MCT, MCSD, MCDBA
"Debra Camacho-Padron via droptable.com" <forum@.droptable.com> wrote in
message news:2a3f51e677de49ec92f681f87f14936c@.SQ
droptable.com...
> I have a .bak file that I am trying to delete because the backup failed
due
> to insufficient space on the disk drive. The message I get is that it is
> in use by another person or program. I cannot find the person or program.
> What should I be looking for? I use native SQL backup.
> --
> Message posted via http://www.droptable.com|||That's what I thought too. I tried both, and it still indicates that the
file is in use.
Message posted via http://www.droptable.com|||Try using FileMon from sysinternals to see what is accessing
the file:
http://www.sysinternals.com/ntw2k/source/filemon.shtml
-Sue
On Tue, 15 Mar 2005 18:01:24 GMT, "Debra Camacho-Padron via
droptable.com" <forum@.droptable.com> wrote:

>That's what I thought too. I tried both, and it still indicates that the
>file is in use.

Cannot delete SQL 2000 backup file

I have a .bak file that I am trying to delete because the backup failed due
to insufficient space on the disk drive. The message I get is that it is
in use by another person or program. I cannot find the person or program.
What should I be looking for? I use native SQL backup.
--
Message posted via http://www.sqlmonster.comYou may have to stop the SQLServerAgent service so that it will release the
file.
If that doesn't work (try the MSSQLServer service). One of the two is
holding on to the file.
Rick Sawtell
MCT, MCSD, MCDBA
"Debra Camacho-Padron via SQLMonster.com" <forum@.SQLMonster.com> wrote in
message news:2a3f51e677de49ec92f681f87f14936c@.SQLMonster.com...
> I have a .bak file that I am trying to delete because the backup failed
due
> to insufficient space on the disk drive. The message I get is that it is
> in use by another person or program. I cannot find the person or program.
> What should I be looking for? I use native SQL backup.
> --
> Message posted via http://www.sqlmonster.com|||That's what I thought too. I tried both, and it still indicates that the
file is in use.
--
Message posted via http://www.sqlmonster.com|||Try using FileMon from sysinternals to see what is accessing
the file:
http://www.sysinternals.com/ntw2k/source/filemon.shtml
-Sue
On Tue, 15 Mar 2005 18:01:24 GMT, "Debra Camacho-Padron via
SQLMonster.com" <forum@.SQLMonster.com> wrote:
>That's what I thought too. I tried both, and it still indicates that the
>file is in use.

cannot create a stable subkey under a volatile parent key

When starting Microsoft SQL Server Management Studio after installing it from the SQL 2005 Enterprise disk, I get the error
cannot create a stable subkey under a volatile parent key
I click Continue and the get another warning box
Failed to open connection dialog. Unable to cast object of type 'Microsoft.SqlServer.Managemt.UI.ConnectionDlg.Personalization' to type 'Microsoft.SqlServer.Management.UI.ConnectionDlg.Personalization'.(ConnectionDlg)
Since I cannot use the old Enterprise Manger that we had for SQL 2000, I now have no way to manage the SQL via GUI. Anyone have any ideas for a fix for this?
Run setup first try repair option see if it works if not uninstall and reinstall. Another thing copy the contents of the CDs into your hardrive before running setup. Hope this helps.|||I'm getting the same thing. Happened during installation, also while using. bhopkins reported the same thing, said uninstaslling and reinstalling does not help. Has anyone seen any other info on this?|||I was getting the second message on startup. I removed the express version of Management Studio and corrected|||

Apparently there is a problem caused by SQL Books Online mixing up with Management Studio, as I reinstalled Management Studio to no avail.

Removing SQL Books Online was not enough in my specific case. I had to remove Management Studio as well. Ldubois, I noticed you did so as well. Did you have SQL Books Online installed also?

Just to be safe, I rebooted after removing SQL Books Online and Management Studio..

Then I reinstalled Management Studio & it works (the pesky startup exceptions went away).

There is another related problem, and that is choosing 'File -> Open -> File' from the Management Studio's menu bar & receiving a nice AV and app exit.

I have not reinstalled SQL Books Online, as likely that will resurface the problem.

For me, the procedure I just described resolved these seriously easy to find bugs that made it out of Microsoft.

Hope this helps. Happy New Year 2007!:))

-yoshiro (mark) aoki

|||

I know this is old. but here is a KB article with the solution.

http://support.microsoft.com/kb/922214