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 database on drive other than OS drive?

I'm trying to migrate a SQL Server 2000 database to a SQL Server 2005
machine and am having difficulties.
The new server (Server 200303) has two drives, C and E. The OS is installed
on C, but there is limited space, so I want to put the data files on E.
To migrate the database from the SQL 2000 machine, I tried both a manuak
detach/attach and the Copy Database feature. When I attempt to tell the new
server to put the data files on drive E, it tells me that it's an invalid
path. To add to the confusion, if I try to alter the "database default
location" from the server's Properties dialog, it shows only a C drive.
I then uninstalled SQL Server 2005 from the C drive and reinstalled it, this
time putting all components that I could change the target path for on the E
drive. The install was successful, the folders were created properly, along
with the master, model, msdb, etc. databases on the E drive. However, when
I try to change a database's file location or alter the default database
location, it still insists that only the C drive is available and that
anything referring to the E drive is invalid.
I tried migrating the database to another SQL 2000 machine and putting it on
a separate data drive and it works just fine.
Is there some setting that SQL Server 2005 is expecting, or some limitation
that the data files must reside on the OS install drive? I assume that I'm
just not doing something right, but I've really not done any SQL admin since
7.0 and right now I'm just trying to migrate a database to a newer, better
machine.
-Chris
Chris Tacke, eMVP wrote:
> I'm trying to migrate a SQL Server 2000 database to a SQL Server 2005
> machine and am having difficulties.
> The new server (Server 200303) has two drives, C and E. The OS is installed
> on C, but there is limited space, so I want to put the data files on E.
> To migrate the database from the SQL 2000 machine, I tried both a manuak
> detach/attach and the Copy Database feature. When I attempt to tell the new
> server to put the data files on drive E, it tells me that it's an invalid
> path. To add to the confusion, if I try to alter the "database default
> location" from the server's Properties dialog, it shows only a C drive.
> I then uninstalled SQL Server 2005 from the C drive and reinstalled it, this
> time putting all components that I could change the target path for on the E
> drive. The install was successful, the folders were created properly, along
> with the master, model, msdb, etc. databases on the E drive. However, when
> I try to change a database's file location or alter the default database
> location, it still insists that only the C drive is available and that
> anything referring to the E drive is invalid.
> I tried migrating the database to another SQL 2000 machine and putting it on
> a separate data drive and it works just fine.
> Is there some setting that SQL Server 2005 is expecting, or some limitation
> that the data files must reside on the OS install drive? I assume that I'm
> just not doing something right, but I've really not done any SQL admin since
> 7.0 and right now I'm just trying to migrate a database to a newer, better
> machine.
> -Chris
>
>
Hi Chris
I assume that you have ensured that the E drive is actually working and
can be seen by the OS?
The only other thing I can think of, is that the path you have entered
for the new datafiles is wrong or doesn't exists. When you want to move
data and log files, the folder you're moving it to must already exists
otherwise it will fail.
Regards
Steen
|||> I'm trying to migrate a SQL Server 2000 database to a SQL Server 2005
> machine and am having difficulties.
> The new server (Server 200303) has two drives, C and E. The OS is installed
> on C, but there is limited space, so I want to put the data files on E.
> To migrate the database from the SQL 2000 machine, I tried both a manuak
> detach/attach and the Copy Database feature. When I attempt to tell the new
> server to put the data files on drive E, it tells me that it's an invalid
> path. To add to the confusion, if I try to alter the "database default
> location" from the server's Properties dialog, it shows only a C drive.
> I then uninstalled SQL Server 2005 from the C drive and reinstalled it, this
> time putting all components that I could change the target path for on the E
> drive. The install was successful, the folders were created properly, along
> with the master, model, msdb, etc. databases on the E drive. However, when
> I try to change a database's file location or alter the default database
> location, it still insists that only the C drive is available and that
> anything referring to the E drive is invalid.
> I tried migrating the database to another SQL 2000 machine and putting it on
> a separate data drive and it works just fine.
> Is there some setting that SQL Server 2005 is expecting, or some limitation
> that the data files must reside on the OS install drive? I assume that I'm
> just not doing something right, but I've really not done any SQL admin since
> 7.0 and right now I'm just trying to migrate a database to a newer, better
> machine.
It looks like the account on which the SQL Server is running has no rights
to access the E:\ folder (and subfolders), so it does not show it nor
is able to move any files there.
Check which account you are using for the SQL Server and check effective
rights for this account to the folders you want the server to access.
Hilarion

Cannot create database on drive other than OS drive?

I'm trying to migrate a SQL Server 2000 database to a SQL Server 2005
machine and am having difficulties.
The new server (Server 200303) has two drives, C and E. The OS is installed
on C, but there is limited space, so I want to put the data files on E.
To migrate the database from the SQL 2000 machine, I tried both a manuak
detach/attach and the Copy Database feature. When I attempt to tell the new
server to put the data files on drive E, it tells me that it's an invalid
path. To add to the confusion, if I try to alter the "database default
location" from the server's Properties dialog, it shows only a C drive.
I then uninstalled SQL Server 2005 from the C drive and reinstalled it, this
time putting all components that I could change the target path for on the E
drive. The install was successful, the folders were created properly, along
with the master, model, msdb, etc. databases on the E drive. However, when
I try to change a database's file location or alter the default database
location, it still insists that only the C drive is available and that
anything referring to the E drive is invalid.
I tried migrating the database to another SQL 2000 machine and putting it on
a separate data drive and it works just fine.
Is there some setting that SQL Server 2005 is expecting, or some limitation
that the data files must reside on the OS install drive? I assume that I'm
just not doing something right, but I've really not done any SQL admin since
7.0 and right now I'm just trying to migrate a database to a newer, better
machine.
-ChrisChris Tacke, eMVP wrote:
> I'm trying to migrate a SQL Server 2000 database to a SQL Server 2005
> machine and am having difficulties.
> The new server (Server 200303) has two drives, C and E. The OS is install
ed
> on C, but there is limited space, so I want to put the data files on E.
> To migrate the database from the SQL 2000 machine, I tried both a manuak
> detach/attach and the Copy Database feature. When I attempt to tell the n
ew
> server to put the data files on drive E, it tells me that it's an invalid
> path. To add to the confusion, if I try to alter the "database default
> location" from the server's Properties dialog, it shows only a C drive.
> I then uninstalled SQL Server 2005 from the C drive and reinstalled it, th
is
> time putting all components that I could change the target path for on the
E
> drive. The install was successful, the folders were created properly, alo
ng
> with the master, model, msdb, etc. databases on the E drive. However, whe
n
> I try to change a database's file location or alter the default database
> location, it still insists that only the C drive is available and that
> anything referring to the E drive is invalid.
> I tried migrating the database to another SQL 2000 machine and putting it
on
> a separate data drive and it works just fine.
> Is there some setting that SQL Server 2005 is expecting, or some limitatio
n
> that the data files must reside on the OS install drive? I assume that I'
m
> just not doing something right, but I've really not done any SQL admin sin
ce
> 7.0 and right now I'm just trying to migrate a database to a newer, better
> machine.
> -Chris
>
>
Hi Chris
I assume that you have ensured that the E drive is actually working and
can be seen by the OS?
The only other thing I can think of, is that the path you have entered
for the new datafiles is wrong or doesn't exists. When you want to move
data and log files, the folder you're moving it to must already exists
otherwise it will fail.
Regards
Steen|||> I'm trying to migrate a SQL Server 2000 database to a SQL Server 2005
> machine and am having difficulties.
> The new server (Server 200303) has two drives, C and E. The OS is install
ed
> on C, but there is limited space, so I want to put the data files on E.
> To migrate the database from the SQL 2000 machine, I tried both a manuak
> detach/attach and the Copy Database feature. When I attempt to tell the n
ew
> server to put the data files on drive E, it tells me that it's an invalid
> path. To add to the confusion, if I try to alter the "database default
> location" from the server's Properties dialog, it shows only a C drive.
> I then uninstalled SQL Server 2005 from the C drive and reinstalled it, th
is
> time putting all components that I could change the target path for on the
E
> drive. The install was successful, the folders were created properly, alo
ng
> with the master, model, msdb, etc. databases on the E drive. However, whe
n
> I try to change a database's file location or alter the default database
> location, it still insists that only the C drive is available and that
> anything referring to the E drive is invalid.
> I tried migrating the database to another SQL 2000 machine and putting it
on
> a separate data drive and it works just fine.
> Is there some setting that SQL Server 2005 is expecting, or some limitatio
n
> that the data files must reside on the OS install drive? I assume that I'
m
> just not doing something right, but I've really not done any SQL admin sin
ce
> 7.0 and right now I'm just trying to migrate a database to a newer, better
> machine.
It looks like the account on which the SQL Server is running has no rights
to access the E:\ folder (and subfolders), so it does not show it nor
is able to move any files there.
Check which account you are using for the SQL Server and check effective
rights for this account to the folders you want the server to access.
Hilarion

Cannot create database on drive other than OS drive?

I'm trying to migrate a SQL Server 2000 database to a SQL Server 2005
machine and am having difficulties.
The new server (Server 200303) has two drives, C and E. The OS is installed
on C, but there is limited space, so I want to put the data files on E.
To migrate the database from the SQL 2000 machine, I tried both a manuak
detach/attach and the Copy Database feature. When I attempt to tell the new
server to put the data files on drive E, it tells me that it's an invalid
path. To add to the confusion, if I try to alter the "database default
location" from the server's Properties dialog, it shows only a C drive.
I then uninstalled SQL Server 2005 from the C drive and reinstalled it, this
time putting all components that I could change the target path for on the E
drive. The install was successful, the folders were created properly, along
with the master, model, msdb, etc. databases on the E drive. However, when
I try to change a database's file location or alter the default database
location, it still insists that only the C drive is available and that
anything referring to the E drive is invalid.
I tried migrating the database to another SQL 2000 machine and putting it on
a separate data drive and it works just fine.
Is there some setting that SQL Server 2005 is expecting, or some limitation
that the data files must reside on the OS install drive? I assume that I'm
just not doing something right, but I've really not done any SQL admin since
7.0 and right now I'm just trying to migrate a database to a newer, better
machine.
-ChrisChris Tacke, eMVP wrote:
> I'm trying to migrate a SQL Server 2000 database to a SQL Server 2005
> machine and am having difficulties.
> The new server (Server 200303) has two drives, C and E. The OS is installed
> on C, but there is limited space, so I want to put the data files on E.
> To migrate the database from the SQL 2000 machine, I tried both a manuak
> detach/attach and the Copy Database feature. When I attempt to tell the new
> server to put the data files on drive E, it tells me that it's an invalid
> path. To add to the confusion, if I try to alter the "database default
> location" from the server's Properties dialog, it shows only a C drive.
> I then uninstalled SQL Server 2005 from the C drive and reinstalled it, this
> time putting all components that I could change the target path for on the E
> drive. The install was successful, the folders were created properly, along
> with the master, model, msdb, etc. databases on the E drive. However, when
> I try to change a database's file location or alter the default database
> location, it still insists that only the C drive is available and that
> anything referring to the E drive is invalid.
> I tried migrating the database to another SQL 2000 machine and putting it on
> a separate data drive and it works just fine.
> Is there some setting that SQL Server 2005 is expecting, or some limitation
> that the data files must reside on the OS install drive? I assume that I'm
> just not doing something right, but I've really not done any SQL admin since
> 7.0 and right now I'm just trying to migrate a database to a newer, better
> machine.
> -Chris
>
>
Hi Chris
I assume that you have ensured that the E drive is actually working and
can be seen by the OS?
The only other thing I can think of, is that the path you have entered
for the new datafiles is wrong or doesn't exists. When you want to move
data and log files, the folder you're moving it to must already exists
otherwise it will fail.
Regards
Steen|||> I'm trying to migrate a SQL Server 2000 database to a SQL Server 2005
> machine and am having difficulties.
> The new server (Server 200303) has two drives, C and E. The OS is installed
> on C, but there is limited space, so I want to put the data files on E.
> To migrate the database from the SQL 2000 machine, I tried both a manuak
> detach/attach and the Copy Database feature. When I attempt to tell the new
> server to put the data files on drive E, it tells me that it's an invalid
> path. To add to the confusion, if I try to alter the "database default
> location" from the server's Properties dialog, it shows only a C drive.
> I then uninstalled SQL Server 2005 from the C drive and reinstalled it, this
> time putting all components that I could change the target path for on the E
> drive. The install was successful, the folders were created properly, along
> with the master, model, msdb, etc. databases on the E drive. However, when
> I try to change a database's file location or alter the default database
> location, it still insists that only the C drive is available and that
> anything referring to the E drive is invalid.
> I tried migrating the database to another SQL 2000 machine and putting it on
> a separate data drive and it works just fine.
> Is there some setting that SQL Server 2005 is expecting, or some limitation
> that the data files must reside on the OS install drive? I assume that I'm
> just not doing something right, but I've really not done any SQL admin since
> 7.0 and right now I'm just trying to migrate a database to a newer, better
> machine.
It looks like the account on which the SQL Server is running has no rights
to access the E:\ folder (and subfolders), so it does not show it nor
is able to move any files there.
Check which account you are using for the SQL Server and check effective
rights for this account to the folders you want the server to access.
Hilarion