I want to move 2 databases to another physical disk. They are both single file databases. It looks like a fairly straigtforward attach and detach procedure, but I have a couple of questions:
The log files (.ldf) currently reside on a separate physical disk from the data files, if I attach and reattach the data files will the logs remain where they are by default or do I have to re specify there location?
Is there any advantage to running the update statistics portion of the sp_detach_db?with the re-attach procedure you can specify the location of the log file as well. See the article in BOL and this is probably the quickest method. However the same thing can be done with backup and restore using the with move and replace arguments.
Showing posts with label disk. Show all posts
Showing posts with label disk. Show all posts
Monday, March 26, 2012
Wednesday, March 21, 2012
move T-log to another Disk?
Hello DBAs,
I think there are several different ways to move a
transaction log file to another disk. What would you do?
what method will you go with?
Currently DBs and T-logs are located in same disk, and I
learned that if they are located different disks would
increase SQL server performance from SQL trainning class.
Please advice me on this, new DBA here.
Thanks in advance.
SunnyThis is a multi-part message in MIME format.
--=_NextPart_000_0295_01C3512E.6C124CF0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Detach the database using sp_detach_db. (Don't bother updating the =statistics.) Move your log to the new drive, then re-attach, using =sp_attach_db.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"litnadsl" <lintadsl@.yahoo.com> wrote in message =news:031f01c3514e$f63ce230$a301280a@.phx.gbl...
Hello DBAs,
I think there are several different ways to move a transaction log file to another disk. What would you do? what method will you go with?
Currently DBs and T-logs are located in same disk, and I learned that if they are located different disks would increase SQL server performance from SQL trainning class.
Please advice me on this, new DBA here.
Thanks in advance.
Sunny
--=_NextPart_000_0295_01C3512E.6C124CF0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Detach the database using =sp_detach_db. (Don't bother updating the statistics.) Move your log to the new =drive, then re-attach, using sp_attach_db.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"litnadsl" wrote in =message news:031f01c3514e$f6=3ce230$a301280a@.phx.gbl...Hello DBAs,I think there are several different ways to move a =transaction log file to another disk. What would you do? what method will you =go with?Currently DBs and T-logs are located in same disk, and I =learned that if they are located different disks would increase SQL server performance from SQL trainning class.Please advice me on this, =new DBA here.Thanks in advance.Sunny
--=_NextPart_000_0295_01C3512E.6C124CF0--|||If you have a RAID system then you do not need to store the log file to a
different disk.
"litnadsl" <lintadsl@.yahoo.com> wrote in message
news:031f01c3514e$f63ce230$a301280a@.phx.gbl...
> Hello DBAs,
> I think there are several different ways to move a
> transaction log file to another disk. What would you do?
> what method will you go with?
> Currently DBs and T-logs are located in same disk, and I
> learned that if they are located different disks would
> increase SQL server performance from SQL trainning class.
> Please advice me on this, new DBA here.
> Thanks in advance.
> Sunny|||Detach the database and then when you do sp_attachdb then specify different
locations.
This you can do with user databases.
For system databases, you need to follow different approach.
"litnadsl" <lintadsl@.yahoo.com> wrote in message
news:031f01c3514e$f63ce230$a301280a@.phx.gbl...
> Hello DBAs,
> I think there are several different ways to move a
> transaction log file to another disk. What would you do?
> what method will you go with?
> Currently DBs and T-logs are located in same disk, and I
> learned that if they are located different disks would
> increase SQL server performance from SQL trainning class.
> Please advice me on this, new DBA here.
> Thanks in advance.
> Sunny|||For user databases, follow Tom's description. For master, model, tempdb,
there're more steps involved including setting up trace flags and modifying
startup parameters. Please refer to the following MS KB articles:
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
Richard
"litnadsl" <lintadsl@.yahoo.com> wrote in message
news:031f01c3514e$f63ce230$a301280a@.phx.gbl...
> Hello DBAs,
> I think there are several different ways to move a
> transaction log file to another disk. What would you do?
> what method will you go with?
> Currently DBs and T-logs are located in same disk, and I
> learned that if they are located different disks would
> increase SQL server performance from SQL trainning class.
> Please advice me on this, new DBA here.
> Thanks in advance.
> Sunny|||Danut
I'm a little puzzled how you think a RAID system affects
the way a Transaction log is sequentially written to? (As
that is the main reason for putting it on a seperate disk)
Regards
John|||Well, I'm not expert but I read some articles on MSDN. Disk stripping offers
the best performance, adding the parity will make it falut tolerant so you
get both.
When you write to a RAID logical drive it will write it to more than one
physical drive in parallel and this is what makes it performant.
Well, let's look into MSDN:
"Both Microsoft Windows NT® and Microsoft Windows® 2000-based disk striping,
and striping with parity, can improve performance. Disk striping with parity
also protects against data loss in the event of media failure."
"Disk striping writes data in stripes across a volume (created from areas of
free space). For more information about volumes, see the Windows NT or
Windows 2000 documentation.
These areas are all the same size and are spread over an array of disks (up
to 32 disks). Striping writes files across all disks, so data is added to
all partitions in the set at the same rate.
Windows NT-based disk striping and Windows 2000 volume sets implement RAID
0. Disk striping provides the best performance of all Windows NT Server
disk-management strategies, but does not provide any fault-tolerance
protection.
Disk striping with parity is similar to disk striping. Disk striping with
parity adds a parity-information stripe to each disk partition in the
volume. This provides fault-tolerance protection equivalent to that of disk
mirroring, but requires much less space for the redundant data. Windows
NT-based disk striping with parity and Windows 2000 RAID-5 volumes implement
RAID 5. "
"John Bandettini" <johnbandettini@.yahoo.co.uk> wrote in message
news:0c6901c351f3$d490d470$a501280a@.phx.gbl...
> Danut
> I don't think you have got the idea here yet. RAID 5 uses
> an extra disk for parity checking. Say you have four disks
> in your RAID 5 array. You only have three disks to store
> data as you use one quarter of the space to hold parity
> data. Sql server uses this so that if one disk fails it
> can use the parity data to tell what data is missing. When
> the disk is replaced it can then rebuild the data.
> Performance wise RAID 5 is the worst of the raid systems,
> it turns one write into two reads and two writes to
> maintain the parity information.
> RAID 5 is not that bad if the database is mostly used for
> reads, with little write activity. For databases with high
> write activity, you are much better off using RAID 10 or
> RAID 01.
> Transaction logs are written too sequentially. If you keep
> your Transaction logs on seperate mirrored drives the
> heads are not moving all over the place they are writting
> where they already are. Putting transaction logs and
> database data files together on RAID 5 disks is just about
> the worst thing you can do performance wise. It might make
> administration easy, but it is a very bad idea.
> The only exception to that is if you are using OLAP with
> no update activity on the database.
> Regards
> John|||I know what is does, I've been a DBA for years, I'm just
telling you in simple terms
RAID 5 IS THE WORST PERFOMING RAID FOR DISK WRITES
TRANSACTION LOGS PERFORM BADLY ON RAID 5 BECAUSE THEY
WRITE A LOT
HAVING A TRANSACTION LOG ON THE SAME DISK AS YOUR DATABASE
SLOWS PERFORMANCE
You will not find anyone on here that disagees with any of
those statements.|||I will attest to what John is saying. Regardless of the Raid used it is
detrimental to performance to have the log file on the same array as the
data. The data is written randomly and the log is sequential and these two
just don't mix well. A Raid 1, 1+0 or 0+1 are much better for log files
than a Raid 5 but in any case it should always be on it's own array.
--
Andrew J. Kelly
SQL Server MVP
"John Bandettini" <johnbandettini@.yahoo.co.uk> wrote in message
news:0eb101c351fb$6df46440$a401280a@.phx.gbl...
> I know what is does, I've been a DBA for years, I'm just
> telling you in simple terms
> RAID 5 IS THE WORST PERFOMING RAID FOR DISK WRITES
> TRANSACTION LOGS PERFORM BADLY ON RAID 5 BECAUSE THEY
> WRITE A LOT
> HAVING A TRANSACTION LOG ON THE SAME DISK AS YOUR DATABASE
> SLOWS PERFORMANCE
> You will not find anyone on here that disagees with any of
> those statements.|||Ok. I got it.
Thanks!
Danut
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uZdPL2fUDHA.1928@.TK2MSFTNGP12.phx.gbl...
> I will attest to what John is saying. Regardless of the Raid used it is
> detrimental to performance to have the log file on the same array as the
> data. The data is written randomly and the log is sequential and these
two
> just don't mix well. A Raid 1, 1+0 or 0+1 are much better for log files
> than a Raid 5 but in any case it should always be on it's own array.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "John Bandettini" <johnbandettini@.yahoo.co.uk> wrote in message
> news:0eb101c351fb$6df46440$a401280a@.phx.gbl...
> > I know what is does, I've been a DBA for years, I'm just
> > telling you in simple terms
> >
> > RAID 5 IS THE WORST PERFOMING RAID FOR DISK WRITES
> >
> > TRANSACTION LOGS PERFORM BADLY ON RAID 5 BECAUSE THEY
> > WRITE A LOT
> >
> > HAVING A TRANSACTION LOG ON THE SAME DISK AS YOUR DATABASE
> > SLOWS PERFORMANCE
> >
> > You will not find anyone on here that disagees with any of
> > those statements.
>
I think there are several different ways to move a
transaction log file to another disk. What would you do?
what method will you go with?
Currently DBs and T-logs are located in same disk, and I
learned that if they are located different disks would
increase SQL server performance from SQL trainning class.
Please advice me on this, new DBA here.
Thanks in advance.
SunnyThis is a multi-part message in MIME format.
--=_NextPart_000_0295_01C3512E.6C124CF0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Detach the database using sp_detach_db. (Don't bother updating the =statistics.) Move your log to the new drive, then re-attach, using =sp_attach_db.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"litnadsl" <lintadsl@.yahoo.com> wrote in message =news:031f01c3514e$f63ce230$a301280a@.phx.gbl...
Hello DBAs,
I think there are several different ways to move a transaction log file to another disk. What would you do? what method will you go with?
Currently DBs and T-logs are located in same disk, and I learned that if they are located different disks would increase SQL server performance from SQL trainning class.
Please advice me on this, new DBA here.
Thanks in advance.
Sunny
--=_NextPart_000_0295_01C3512E.6C124CF0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Detach the database using =sp_detach_db. (Don't bother updating the statistics.) Move your log to the new =drive, then re-attach, using sp_attach_db.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"litnadsl"
--=_NextPart_000_0295_01C3512E.6C124CF0--|||If you have a RAID system then you do not need to store the log file to a
different disk.
"litnadsl" <lintadsl@.yahoo.com> wrote in message
news:031f01c3514e$f63ce230$a301280a@.phx.gbl...
> Hello DBAs,
> I think there are several different ways to move a
> transaction log file to another disk. What would you do?
> what method will you go with?
> Currently DBs and T-logs are located in same disk, and I
> learned that if they are located different disks would
> increase SQL server performance from SQL trainning class.
> Please advice me on this, new DBA here.
> Thanks in advance.
> Sunny|||Detach the database and then when you do sp_attachdb then specify different
locations.
This you can do with user databases.
For system databases, you need to follow different approach.
"litnadsl" <lintadsl@.yahoo.com> wrote in message
news:031f01c3514e$f63ce230$a301280a@.phx.gbl...
> Hello DBAs,
> I think there are several different ways to move a
> transaction log file to another disk. What would you do?
> what method will you go with?
> Currently DBs and T-logs are located in same disk, and I
> learned that if they are located different disks would
> increase SQL server performance from SQL trainning class.
> Please advice me on this, new DBA here.
> Thanks in advance.
> Sunny|||For user databases, follow Tom's description. For master, model, tempdb,
there're more steps involved including setting up trace flags and modifying
startup parameters. Please refer to the following MS KB articles:
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
Richard
"litnadsl" <lintadsl@.yahoo.com> wrote in message
news:031f01c3514e$f63ce230$a301280a@.phx.gbl...
> Hello DBAs,
> I think there are several different ways to move a
> transaction log file to another disk. What would you do?
> what method will you go with?
> Currently DBs and T-logs are located in same disk, and I
> learned that if they are located different disks would
> increase SQL server performance from SQL trainning class.
> Please advice me on this, new DBA here.
> Thanks in advance.
> Sunny|||Danut
I'm a little puzzled how you think a RAID system affects
the way a Transaction log is sequentially written to? (As
that is the main reason for putting it on a seperate disk)
Regards
John|||Well, I'm not expert but I read some articles on MSDN. Disk stripping offers
the best performance, adding the parity will make it falut tolerant so you
get both.
When you write to a RAID logical drive it will write it to more than one
physical drive in parallel and this is what makes it performant.
Well, let's look into MSDN:
"Both Microsoft Windows NT® and Microsoft Windows® 2000-based disk striping,
and striping with parity, can improve performance. Disk striping with parity
also protects against data loss in the event of media failure."
"Disk striping writes data in stripes across a volume (created from areas of
free space). For more information about volumes, see the Windows NT or
Windows 2000 documentation.
These areas are all the same size and are spread over an array of disks (up
to 32 disks). Striping writes files across all disks, so data is added to
all partitions in the set at the same rate.
Windows NT-based disk striping and Windows 2000 volume sets implement RAID
0. Disk striping provides the best performance of all Windows NT Server
disk-management strategies, but does not provide any fault-tolerance
protection.
Disk striping with parity is similar to disk striping. Disk striping with
parity adds a parity-information stripe to each disk partition in the
volume. This provides fault-tolerance protection equivalent to that of disk
mirroring, but requires much less space for the redundant data. Windows
NT-based disk striping with parity and Windows 2000 RAID-5 volumes implement
RAID 5. "
"John Bandettini" <johnbandettini@.yahoo.co.uk> wrote in message
news:0c6901c351f3$d490d470$a501280a@.phx.gbl...
> Danut
> I don't think you have got the idea here yet. RAID 5 uses
> an extra disk for parity checking. Say you have four disks
> in your RAID 5 array. You only have three disks to store
> data as you use one quarter of the space to hold parity
> data. Sql server uses this so that if one disk fails it
> can use the parity data to tell what data is missing. When
> the disk is replaced it can then rebuild the data.
> Performance wise RAID 5 is the worst of the raid systems,
> it turns one write into two reads and two writes to
> maintain the parity information.
> RAID 5 is not that bad if the database is mostly used for
> reads, with little write activity. For databases with high
> write activity, you are much better off using RAID 10 or
> RAID 01.
> Transaction logs are written too sequentially. If you keep
> your Transaction logs on seperate mirrored drives the
> heads are not moving all over the place they are writting
> where they already are. Putting transaction logs and
> database data files together on RAID 5 disks is just about
> the worst thing you can do performance wise. It might make
> administration easy, but it is a very bad idea.
> The only exception to that is if you are using OLAP with
> no update activity on the database.
> Regards
> John|||I know what is does, I've been a DBA for years, I'm just
telling you in simple terms
RAID 5 IS THE WORST PERFOMING RAID FOR DISK WRITES
TRANSACTION LOGS PERFORM BADLY ON RAID 5 BECAUSE THEY
WRITE A LOT
HAVING A TRANSACTION LOG ON THE SAME DISK AS YOUR DATABASE
SLOWS PERFORMANCE
You will not find anyone on here that disagees with any of
those statements.|||I will attest to what John is saying. Regardless of the Raid used it is
detrimental to performance to have the log file on the same array as the
data. The data is written randomly and the log is sequential and these two
just don't mix well. A Raid 1, 1+0 or 0+1 are much better for log files
than a Raid 5 but in any case it should always be on it's own array.
--
Andrew J. Kelly
SQL Server MVP
"John Bandettini" <johnbandettini@.yahoo.co.uk> wrote in message
news:0eb101c351fb$6df46440$a401280a@.phx.gbl...
> I know what is does, I've been a DBA for years, I'm just
> telling you in simple terms
> RAID 5 IS THE WORST PERFOMING RAID FOR DISK WRITES
> TRANSACTION LOGS PERFORM BADLY ON RAID 5 BECAUSE THEY
> WRITE A LOT
> HAVING A TRANSACTION LOG ON THE SAME DISK AS YOUR DATABASE
> SLOWS PERFORMANCE
> You will not find anyone on here that disagees with any of
> those statements.|||Ok. I got it.
Thanks!
Danut
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uZdPL2fUDHA.1928@.TK2MSFTNGP12.phx.gbl...
> I will attest to what John is saying. Regardless of the Raid used it is
> detrimental to performance to have the log file on the same array as the
> data. The data is written randomly and the log is sequential and these
two
> just don't mix well. A Raid 1, 1+0 or 0+1 are much better for log files
> than a Raid 5 but in any case it should always be on it's own array.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "John Bandettini" <johnbandettini@.yahoo.co.uk> wrote in message
> news:0eb101c351fb$6df46440$a401280a@.phx.gbl...
> > I know what is does, I've been a DBA for years, I'm just
> > telling you in simple terms
> >
> > RAID 5 IS THE WORST PERFOMING RAID FOR DISK WRITES
> >
> > TRANSACTION LOGS PERFORM BADLY ON RAID 5 BECAUSE THEY
> > WRITE A LOT
> >
> > HAVING A TRANSACTION LOG ON THE SAME DISK AS YOUR DATABASE
> > SLOWS PERFORMANCE
> >
> > You will not find anyone on here that disagees with any of
> > those statements.
>
Move tempdb files to a new disk
I have done a new installation which is up and running.
However the disk on which the tempdb mdf and ldf files are
is giving a problem. Is there a way to move the files to a
different disk? I know we can't detach and attach it.
Alter database does not allow the primary data file to be
deleted.
I can create a new data file on the other disk and stop
the existing datafile from growing. This will effectively
move all tempdb activity to the new disk. But is there no
better way to move the mdf and ldf files to the new disk?
TIA,
JackINF: Moving SQL Server Databases to a New Location with Detach/Attach
http://support.microsoft.com/defaul...1&Product=sql2k
Scroll down to the bottom and find the section titled 'Moving Tempdb'
You are correct in that Detach/Attach doesn't work on TempDB. This KB
article does explain the proper way to move TempDB.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jack" <jacka8@.excite.com> wrote in message
news:e4a401c40ba6$b1358d50$a301280a@.phx.gbl...
> I have done a new installation which is up and running.
> However the disk on which the tempdb mdf and ldf files are
> is giving a problem. Is there a way to move the files to a
> different disk? I know we can't detach and attach it.
> Alter database does not allow the primary data file to be
> deleted.
> I can create a new data file on the other disk and stop
> the existing datafile from growing. This will effectively
> move all tempdb activity to the new disk. But is there no
> better way to move the mdf and ldf files to the new disk?
> TIA,
> Jack|||You do this using ALTER DATABASE. See KB 224071 for more info.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jack" <jacka8@.excite.com> wrote in message
news:e4a401c40ba6$b1358d50$a301280a@.phx.gbl...
> I have done a new installation which is up and running.
> However the disk on which the tempdb mdf and ldf files are
> is giving a problem. Is there a way to move the files to a
> different disk? I know we can't detach and attach it.
> Alter database does not allow the primary data file to be
> deleted.
> I can create a new data file on the other disk and stop
> the existing datafile from growing. This will effectively
> move all tempdb activity to the new disk. But is there no
> better way to move the mdf and ldf files to the new disk?
> TIA,
> Jack|||Hi,
How to Move TEMPDB from one location to another
---
USE master
go
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME =
'E:\tempdb.mdf')
go
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME =
'E:\templog.ldf')
go
where NAME refers to the logical name of the tempdb database and
log files, and where FILENAME refers to the new location of the
tempdb files. Once this command has run, you must restart the
mssqlserver service before it takes affect.
This steps are common for SQL Server 7.0 and 2000.
Thanks
Hari
MCDBA
"Jack" <jacka8@.excite.com> wrote in message
news:e4a401c40ba6$b1358d50$a301280a@.phx.gbl...
> I have done a new installation which is up and running.
> However the disk on which the tempdb mdf and ldf files are
> is giving a problem. Is there a way to move the files to a
> different disk? I know we can't detach and attach it.
> Alter database does not allow the primary data file to be
> deleted.
> I can create a new data file on the other disk and stop
> the existing datafile from growing. This will effectively
> move all tempdb activity to the new disk. But is there no
> better way to move the mdf and ldf files to the new disk?
> TIA,
> Jack
However the disk on which the tempdb mdf and ldf files are
is giving a problem. Is there a way to move the files to a
different disk? I know we can't detach and attach it.
Alter database does not allow the primary data file to be
deleted.
I can create a new data file on the other disk and stop
the existing datafile from growing. This will effectively
move all tempdb activity to the new disk. But is there no
better way to move the mdf and ldf files to the new disk?
TIA,
JackINF: Moving SQL Server Databases to a New Location with Detach/Attach
http://support.microsoft.com/defaul...1&Product=sql2k
Scroll down to the bottom and find the section titled 'Moving Tempdb'
You are correct in that Detach/Attach doesn't work on TempDB. This KB
article does explain the proper way to move TempDB.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jack" <jacka8@.excite.com> wrote in message
news:e4a401c40ba6$b1358d50$a301280a@.phx.gbl...
> I have done a new installation which is up and running.
> However the disk on which the tempdb mdf and ldf files are
> is giving a problem. Is there a way to move the files to a
> different disk? I know we can't detach and attach it.
> Alter database does not allow the primary data file to be
> deleted.
> I can create a new data file on the other disk and stop
> the existing datafile from growing. This will effectively
> move all tempdb activity to the new disk. But is there no
> better way to move the mdf and ldf files to the new disk?
> TIA,
> Jack|||You do this using ALTER DATABASE. See KB 224071 for more info.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jack" <jacka8@.excite.com> wrote in message
news:e4a401c40ba6$b1358d50$a301280a@.phx.gbl...
> I have done a new installation which is up and running.
> However the disk on which the tempdb mdf and ldf files are
> is giving a problem. Is there a way to move the files to a
> different disk? I know we can't detach and attach it.
> Alter database does not allow the primary data file to be
> deleted.
> I can create a new data file on the other disk and stop
> the existing datafile from growing. This will effectively
> move all tempdb activity to the new disk. But is there no
> better way to move the mdf and ldf files to the new disk?
> TIA,
> Jack|||Hi,
How to Move TEMPDB from one location to another
---
USE master
go
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME =
'E:\tempdb.mdf')
go
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME =
'E:\templog.ldf')
go
where NAME refers to the logical name of the tempdb database and
log files, and where FILENAME refers to the new location of the
tempdb files. Once this command has run, you must restart the
mssqlserver service before it takes affect.
This steps are common for SQL Server 7.0 and 2000.
Thanks
Hari
MCDBA
"Jack" <jacka8@.excite.com> wrote in message
news:e4a401c40ba6$b1358d50$a301280a@.phx.gbl...
> I have done a new installation which is up and running.
> However the disk on which the tempdb mdf and ldf files are
> is giving a problem. Is there a way to move the files to a
> different disk? I know we can't detach and attach it.
> Alter database does not allow the primary data file to be
> deleted.
> I can create a new data file on the other disk and stop
> the existing datafile from growing. This will effectively
> move all tempdb activity to the new disk. But is there no
> better way to move the mdf and ldf files to the new disk?
> TIA,
> Jack
Monday, March 12, 2012
move SQL data files to same letter drive but different disk
Hi,
We need to move our sql data files to new disks. Current
data are on drive D, F. New disks are U,V. We want to
move data files on D to U and F to V. Then rename U to D
and V to F (after rename original D & F to X & Y).
The quesions are
if SQL server is shutdown before this, will SQL Server
see the same data file?
Will I need to do a dettach/attach even the data file
will be on same path at the end?
Thanks.
Haii believe if you do this in correct order you don't have any problem:
1. stop SQL service
2. move the files (.mdf and .ldf)
3. rename D:, F: to X:, Y:
4. rename U:, V: to D:, F:
5. start SQL service
don't know if a reboot after step 4 would help but why don't you try it?
"Hai Le" <hle@.coair.com> wrote in message
news:02d401c3dac8$70e9a2e0$a101280a@.phx.gbl...
> Hi,
> We need to move our sql data files to new disks. Current
> data are on drive D, F. New disks are U,V. We want to
> move data files on D to U and F to V. Then rename U to D
> and V to F (after rename original D & F to X & Y).
> The quesions are
> if SQL server is shutdown before this, will SQL Server
> see the same data file?
> Will I need to do a dettach/attach even the data file
> will be on same path at the end?
> Thanks.
> Hai
We need to move our sql data files to new disks. Current
data are on drive D, F. New disks are U,V. We want to
move data files on D to U and F to V. Then rename U to D
and V to F (after rename original D & F to X & Y).
The quesions are
if SQL server is shutdown before this, will SQL Server
see the same data file?
Will I need to do a dettach/attach even the data file
will be on same path at the end?
Thanks.
Haii believe if you do this in correct order you don't have any problem:
1. stop SQL service
2. move the files (.mdf and .ldf)
3. rename D:, F: to X:, Y:
4. rename U:, V: to D:, F:
5. start SQL service
don't know if a reboot after step 4 would help but why don't you try it?
"Hai Le" <hle@.coair.com> wrote in message
news:02d401c3dac8$70e9a2e0$a101280a@.phx.gbl...
> Hi,
> We need to move our sql data files to new disks. Current
> data are on drive D, F. New disks are U,V. We want to
> move data files on D to U and F to V. Then rename U to D
> and V to F (after rename original D & F to X & Y).
> The quesions are
> if SQL server is shutdown before this, will SQL Server
> see the same data file?
> Will I need to do a dettach/attach even the data file
> will be on same path at the end?
> Thanks.
> Hai
move SQL data files to same letter drive but different disk
Hi,
We need to move our sql data files to new disks. Current
data are on drive D, F. New disks are U,V. We want to
move data files on D to U and F to V. Then rename U to D
and V to F (after rename original D & F to X & Y).
The quesions are
if SQL server is shutdown before this, will SQL Server
see the same data file?
Will I need to do a dettach/attach even the data file
will be on same path at the end?
Thanks.
Haii believe if you do this in correct order you don't have any problem:
1. stop SQL service
2. move the files (.mdf and .ldf)
3. rename D:, F: to X:, Y:
4. rename U:, V: to D:, F:
5. start SQL service
don't know if a reboot after step 4 would help but why don't you try it?
"Hai Le" <hle@.coair.com> wrote in message
news:02d401c3dac8$70e9a2e0$a101280a@.phx.gbl...
We need to move our sql data files to new disks. Current
data are on drive D, F. New disks are U,V. We want to
move data files on D to U and F to V. Then rename U to D
and V to F (after rename original D & F to X & Y).
The quesions are
if SQL server is shutdown before this, will SQL Server
see the same data file?
Will I need to do a dettach/attach even the data file
will be on same path at the end?
Thanks.
Haii believe if you do this in correct order you don't have any problem:
1. stop SQL service
2. move the files (.mdf and .ldf)
3. rename D:, F: to X:, Y:
4. rename U:, V: to D:, F:
5. start SQL service
don't know if a reboot after step 4 would help but why don't you try it?
"Hai Le" <hle@.coair.com> wrote in message
news:02d401c3dac8$70e9a2e0$a101280a@.phx.gbl...
quote:
> Hi,
> We need to move our sql data files to new disks. Current
> data are on drive D, F. New disks are U,V. We want to
> move data files on D to U and F to V. Then rename U to D
> and V to F (after rename original D & F to X & Y).
> The quesions are
> if SQL server is shutdown before this, will SQL Server
> see the same data file?
> Will I need to do a dettach/attach even the data file
> will be on same path at the end?
> Thanks.
> Hai
Move sql 2005 ee db to new server without disk access?
Is there any reasonably simple way to install the mdf and ldf files for a sq
l
server 2005 ee database on a server where one does not have access to the da
ta
folder of the sql server? IE using the ee management studio from a workstati
on?On Feb 12, 12:19 pm, "ms" <m...@.ms.ms> wrote:[vbcol=seagreen]
> Is there any reasonably simple way to install the mdf and ldf files for a
sql
> server 2005 ee database on a server where one does not have access to the
data
> folder of the sql server? IE using the ee management studio from a workstation?[/v
bcol]
Does the SQL Server service account have access to the new location?
You could try using xp_cmdshell to execute OS commands to copy the mdf/
ldf files. Detach the DB, copy with xp_cmdshell, the reattach.|||On 12/02/2007 "Tracy McKibben" <tracy.mckibben@.gmail.com> wrote:
>On Feb 12, 12:19 pm, "ms" <m...@.ms.ms> wrote:
data[vbcol=seagreen]
workstation?[vbcol=seagreen]
>Does the SQL Server service account have access to the new location?
>You could try using xp_cmdshell to execute OS commands to copy the mdf/
>ldf files. Detach the DB, copy with xp_cmdshell, the reattach.
>
Thanks Tracey, that might be possible, I'll check.
l
server 2005 ee database on a server where one does not have access to the da
ta
folder of the sql server? IE using the ee management studio from a workstati
on?On Feb 12, 12:19 pm, "ms" <m...@.ms.ms> wrote:[vbcol=seagreen]
> Is there any reasonably simple way to install the mdf and ldf files for a
sql
> server 2005 ee database on a server where one does not have access to the
data
> folder of the sql server? IE using the ee management studio from a workstation?[/v
bcol]
Does the SQL Server service account have access to the new location?
You could try using xp_cmdshell to execute OS commands to copy the mdf/
ldf files. Detach the DB, copy with xp_cmdshell, the reattach.|||On 12/02/2007 "Tracy McKibben" <tracy.mckibben@.gmail.com> wrote:
>On Feb 12, 12:19 pm, "ms" <m...@.ms.ms> wrote:
data[vbcol=seagreen]
workstation?[vbcol=seagreen]
>Does the SQL Server service account have access to the new location?
>You could try using xp_cmdshell to execute OS commands to copy the mdf/
>ldf files. Detach the DB, copy with xp_cmdshell, the reattach.
>
Thanks Tracey, that might be possible, I'll check.
Move sql 2005 ee db to new server without disk access?
Is there any reasonably simple way to install the mdf and ldf files for a sql
server 2005 ee database on a server where one does not have access to the data
folder of the sql server? IE using the ee management studio from a workstation?
On Feb 12, 12:19 pm, "ms" <m...@.ms.ms> wrote:
> Is there any reasonably simple way to install the mdf and ldf files for a sql
> server 2005 ee database on a server where one does not have access to the data
> folder of the sql server? IE using the ee management studio from a workstation?
Does the SQL Server service account have access to the new location?
You could try using xp_cmdshell to execute OS commands to copy the mdf/
ldf files. Detach the DB, copy with xp_cmdshell, the reattach.
|||On 12/02/2007 "Tracy McKibben" <tracy.mckibben@.gmail.com> wrote:[vbcol=seagreen]
>On Feb 12, 12:19 pm, "ms" <m...@.ms.ms> wrote:
data[vbcol=seagreen]
workstation?
>Does the SQL Server service account have access to the new location?
>You could try using xp_cmdshell to execute OS commands to copy the mdf/
>ldf files. Detach the DB, copy with xp_cmdshell, the reattach.
>
Thanks Tracey, that might be possible, I'll check.
server 2005 ee database on a server where one does not have access to the data
folder of the sql server? IE using the ee management studio from a workstation?
On Feb 12, 12:19 pm, "ms" <m...@.ms.ms> wrote:
> Is there any reasonably simple way to install the mdf and ldf files for a sql
> server 2005 ee database on a server where one does not have access to the data
> folder of the sql server? IE using the ee management studio from a workstation?
Does the SQL Server service account have access to the new location?
You could try using xp_cmdshell to execute OS commands to copy the mdf/
ldf files. Detach the DB, copy with xp_cmdshell, the reattach.
|||On 12/02/2007 "Tracy McKibben" <tracy.mckibben@.gmail.com> wrote:[vbcol=seagreen]
>On Feb 12, 12:19 pm, "ms" <m...@.ms.ms> wrote:
data[vbcol=seagreen]
workstation?
>Does the SQL Server service account have access to the new location?
>You could try using xp_cmdshell to execute OS commands to copy the mdf/
>ldf files. Detach the DB, copy with xp_cmdshell, the reattach.
>
Thanks Tracey, that might be possible, I'll check.
Move sql 2005 ee db to new server without disk access?
Is there any reasonably simple way to install the mdf and ldf files for a sql
server 2005 ee database on a server where one does not have access to the data
folder of the sql server? IE using the ee management studio from a workstation?On Feb 12, 12:19 pm, "ms" <m...@.ms.ms> wrote:
> Is there any reasonably simple way to install the mdf and ldf files for a sql
> server 2005 ee database on a server where one does not have access to the data
> folder of the sql server? IE using the ee management studio from a workstation?
Does the SQL Server service account have access to the new location?
You could try using xp_cmdshell to execute OS commands to copy the mdf/
ldf files. Detach the DB, copy with xp_cmdshell, the reattach.|||On 12/02/2007 "Tracy McKibben" <tracy.mckibben@.gmail.com> wrote:
>On Feb 12, 12:19 pm, "ms" <m...@.ms.ms> wrote:
>> Is there any reasonably simple way to install the mdf and ldf files for a sql
>> server 2005 ee database on a server where one does not have access to the
data
>> folder of the sql server? IE using the ee management studio from a
workstation?
>Does the SQL Server service account have access to the new location?
>You could try using xp_cmdshell to execute OS commands to copy the mdf/
>ldf files. Detach the DB, copy with xp_cmdshell, the reattach.
>
Thanks Tracey, that might be possible, I'll check.
server 2005 ee database on a server where one does not have access to the data
folder of the sql server? IE using the ee management studio from a workstation?On Feb 12, 12:19 pm, "ms" <m...@.ms.ms> wrote:
> Is there any reasonably simple way to install the mdf and ldf files for a sql
> server 2005 ee database on a server where one does not have access to the data
> folder of the sql server? IE using the ee management studio from a workstation?
Does the SQL Server service account have access to the new location?
You could try using xp_cmdshell to execute OS commands to copy the mdf/
ldf files. Detach the DB, copy with xp_cmdshell, the reattach.|||On 12/02/2007 "Tracy McKibben" <tracy.mckibben@.gmail.com> wrote:
>On Feb 12, 12:19 pm, "ms" <m...@.ms.ms> wrote:
>> Is there any reasonably simple way to install the mdf and ldf files for a sql
>> server 2005 ee database on a server where one does not have access to the
data
>> folder of the sql server? IE using the ee management studio from a
workstation?
>Does the SQL Server service account have access to the new location?
>You could try using xp_cmdshell to execute OS commands to copy the mdf/
>ldf files. Detach the DB, copy with xp_cmdshell, the reattach.
>
Thanks Tracey, that might be possible, I'll check.
Friday, March 9, 2012
Move MSDB
Hi,
Is there a way to move the MSDB database files to a different disk? A detach
command is disabled in the EM, but would it work from the QA? And would this
work?
TIA
GerryGerry,
Yes. Read KB 224071. It has instruction on how to move msdb. Follow the instructions closely.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Gerry Laenen" <gerry_laenen@.NOSPAMhotmail.com> wrote in message
news:3fa0cc78$0$264$ba620e4c@.reader1.news.skynet.be...
> Hi,
> Is there a way to move the MSDB database files to a different disk? A detach
> command is disabled in the EM, but would it work from the QA? And would this
> work?
> TIA
> Gerry
>
Is there a way to move the MSDB database files to a different disk? A detach
command is disabled in the EM, but would it work from the QA? And would this
work?
TIA
GerryGerry,
Yes. Read KB 224071. It has instruction on how to move msdb. Follow the instructions closely.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Gerry Laenen" <gerry_laenen@.NOSPAMhotmail.com> wrote in message
news:3fa0cc78$0$264$ba620e4c@.reader1.news.skynet.be...
> Hi,
> Is there a way to move the MSDB database files to a different disk? A detach
> command is disabled in the EM, but would it work from the QA? And would this
> work?
> TIA
> Gerry
>
Move logins with passwords
Hello!
I will reinstall my server who has SQL server 2000, containing a few
databases with logins.
Which is the easiest way to store these on disk and when I have reinstalled
my server, add these to SQL again, hopefully containing passwords as well.
Regards Magnushttp://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly
SQL Server MVP
"MB" <magnus.blomberg@.skanska.se> wrote in message
news:%23Qq4wnw9DHA.2404@.TK2MSFTNGP12.phx.gbl...
> Hello!
> I will reinstall my server who has SQL server 2000, containing a few
> databases with logins.
> Which is the easiest way to store these on disk and when I have
reinstalled
> my server, add these to SQL again, hopefully containing passwords as well.
> Regards Magnus
>|||Thank you. It helped
Regards Magnus
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OtBdL4w9DHA.1636@.TK2MSFTNGP12.phx.gbl...
> http://www.support.microsoft.com/?id=314546 Moving DB's between
Servers
> http://www.support.microsoft.com/?id=224071 Moving SQL Server
Databases
> to a New Location with Detach/Attach
> http://support.microsoft.com/?id=221465 Using WITH MOVE in a
> Restore
> http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
> Passwords Between SQL Servers
> http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after
a
> Restore
> http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
> users
> http://www.support.microsoft.com/?id=168001 User Logon and/or
Permission
> Errors After Restoring Dump
> http://www.support.microsoft.com/?id=240872 How to Resolve Permission
> Issues When a Database Is Moved Between SQL Servers
> http://www.sqlservercentral.com/scr...sp?scriptid=599
> Restoring a .mdf
> http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
> for SQL Server
>
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "MB" <magnus.blomberg@.skanska.se> wrote in message
> news:%23Qq4wnw9DHA.2404@.TK2MSFTNGP12.phx.gbl...
> reinstalled
well.
>
I will reinstall my server who has SQL server 2000, containing a few
databases with logins.
Which is the easiest way to store these on disk and when I have reinstalled
my server, add these to SQL again, hopefully containing passwords as well.
Regards Magnushttp://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly
SQL Server MVP
"MB" <magnus.blomberg@.skanska.se> wrote in message
news:%23Qq4wnw9DHA.2404@.TK2MSFTNGP12.phx.gbl...
> Hello!
> I will reinstall my server who has SQL server 2000, containing a few
> databases with logins.
> Which is the easiest way to store these on disk and when I have
reinstalled
> my server, add these to SQL again, hopefully containing passwords as well.
> Regards Magnus
>|||Thank you. It helped
Regards Magnus
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OtBdL4w9DHA.1636@.TK2MSFTNGP12.phx.gbl...
> http://www.support.microsoft.com/?id=314546 Moving DB's between
Servers
> http://www.support.microsoft.com/?id=224071 Moving SQL Server
Databases
> to a New Location with Detach/Attach
> http://support.microsoft.com/?id=221465 Using WITH MOVE in a
> Restore
> http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
> Passwords Between SQL Servers
> http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after
a
> Restore
> http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
> users
> http://www.support.microsoft.com/?id=168001 User Logon and/or
Permission
> Errors After Restoring Dump
> http://www.support.microsoft.com/?id=240872 How to Resolve Permission
> Issues When a Database Is Moved Between SQL Servers
> http://www.sqlservercentral.com/scr...sp?scriptid=599
> Restoring a .mdf
> http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
> for SQL Server
>
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "MB" <magnus.blomberg@.skanska.se> wrote in message
> news:%23Qq4wnw9DHA.2404@.TK2MSFTNGP12.phx.gbl...
> reinstalled
well.
>
Move log file without shutdown
I need to move a database's log file to a different disk. Can I do this without shutting down? It's pretty clear that I can create a second log file on the new disk but, what do I have to do to delete the old log file? The documentation warns that files must be empty to be deleted but I can't find any information on how to empty and disable a log file.
Will a BACKUP LOG make the log file empty enough to be deleted? How do I make sure that new transactions are all written to the new log file?
You can't move the primary log file whilst the db is online, i.e. the one you create with the database.
You could add another and then shrink the original and set the auto grow to off, but thats not ideal.
Why can't you take the DB offline for 5 minutes?
|||Dang. I guess I'll have to take the database offline for 5 minutes. I was hoping to avoid that because even 5 minutes of downtime is going to take an hour of coordination, communication etc. This db is used during hospital admissions.
Move indexes to new filegroup
If a new filegroup is created on a new disk drive, is there a way to migrate
a specific clustered index to the new filegroup?
Regards,
Jamie
I'm having trouble with the syntax. Index is created and dropped with
ALTER TABLE [dbo].[tSupplierItemXrefs] ADD CONSTRAINT
[PK_tSupplierItemXrefs] PRIMARY KEY CLUSTERED
(
[SupplierXrefKey] ASC
) ON [PRIMARY]
IF EXISTS (SELECT * FROM dbo.sysindexes WHERE id =
OBJECT_ID(N'[dbo].[tSupplierItemXrefs]') AND name = N'PK_tSupplierItemXrefs')
ALTER TABLE [dbo].[tSupplierItemXrefs] DROP CONSTRAINT [PK_tSupplierItemXrefs]
WHEN I RUN:
CREATE CLUSTERED INDEX PK_tSupplierItemXRefs ON tSupplierItemXrefs
(SupplierXrefKey) WITH DROP_EXISTING ON "DDRIVE"
I get an error:
Server: Msg 1907, Level 16, State 1, Line 1
Cannot re-create index 'PK_tSupplierItemXRefs'. The new index definition
does not match the constraint being enforced by the existing index.
DDRIVE is the additional filegroup I created for this table and which exists
on a drive other than the primary filegroup drive.
Regards,
Jamie
"Tibor Karaszi" wrote:
> Use CREATE INDEX with the DROP EXISTING clause. Example:
> USE master
> GO
> DROP DATABASE FgTest
> GO
> CREATE DATABASE FgTest
> ON PRIMARY
> ( NAME = N'FgTest', FILENAME = N'C:\FgTest.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH =
> 1024KB ),
> FILEGROUP [ForData]
> ( NAME = N'ForMyData', FILENAME = N'C:\ForMyData.ndf' , SIZE = 3072KB , MAXSIZE = UNLIMITED,
> FILEGROWTH = 1024KB )
> LOG ON
> ( NAME = N'FgTest_log', FILENAME = N'C:\FgTest_log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB ,
> FILEGROWTH = 10%)
> GO
> USE FgTest
> CREATE TABLE t(c1 int identity, c2 char(5))
> CREATE CLUSTERED INDEX x ON t(c1) ON "PRIMARY"
> INSERT INTO t (c2)
> SELECT TOP 10000 'Hello' FROM sysobjects, syscolumns
> GO
> EXEC sp_helpindex t
> CREATE CLUSTERED INDEX x ON t(c1) WITH DROP_EXISTING ON "ForData"
> EXEC sp_helpindex t
> USE master
> DROP DATABASE FgTest
> GO
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:563BAAF9-92DA-48C9-906C-2467E1E73C53@.microsoft.com...
>
a specific clustered index to the new filegroup?
Regards,
Jamie
I'm having trouble with the syntax. Index is created and dropped with
ALTER TABLE [dbo].[tSupplierItemXrefs] ADD CONSTRAINT
[PK_tSupplierItemXrefs] PRIMARY KEY CLUSTERED
(
[SupplierXrefKey] ASC
) ON [PRIMARY]
IF EXISTS (SELECT * FROM dbo.sysindexes WHERE id =
OBJECT_ID(N'[dbo].[tSupplierItemXrefs]') AND name = N'PK_tSupplierItemXrefs')
ALTER TABLE [dbo].[tSupplierItemXrefs] DROP CONSTRAINT [PK_tSupplierItemXrefs]
WHEN I RUN:
CREATE CLUSTERED INDEX PK_tSupplierItemXRefs ON tSupplierItemXrefs
(SupplierXrefKey) WITH DROP_EXISTING ON "DDRIVE"
I get an error:
Server: Msg 1907, Level 16, State 1, Line 1
Cannot re-create index 'PK_tSupplierItemXRefs'. The new index definition
does not match the constraint being enforced by the existing index.
DDRIVE is the additional filegroup I created for this table and which exists
on a drive other than the primary filegroup drive.
Regards,
Jamie
"Tibor Karaszi" wrote:
> Use CREATE INDEX with the DROP EXISTING clause. Example:
> USE master
> GO
> DROP DATABASE FgTest
> GO
> CREATE DATABASE FgTest
> ON PRIMARY
> ( NAME = N'FgTest', FILENAME = N'C:\FgTest.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH =
> 1024KB ),
> FILEGROUP [ForData]
> ( NAME = N'ForMyData', FILENAME = N'C:\ForMyData.ndf' , SIZE = 3072KB , MAXSIZE = UNLIMITED,
> FILEGROWTH = 1024KB )
> LOG ON
> ( NAME = N'FgTest_log', FILENAME = N'C:\FgTest_log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB ,
> FILEGROWTH = 10%)
> GO
> USE FgTest
> CREATE TABLE t(c1 int identity, c2 char(5))
> CREATE CLUSTERED INDEX x ON t(c1) ON "PRIMARY"
> INSERT INTO t (c2)
> SELECT TOP 10000 'Hello' FROM sysobjects, syscolumns
> GO
> EXEC sp_helpindex t
> CREATE CLUSTERED INDEX x ON t(c1) WITH DROP_EXISTING ON "ForData"
> EXEC sp_helpindex t
> USE master
> DROP DATABASE FgTest
> GO
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:563BAAF9-92DA-48C9-906C-2467E1E73C53@.microsoft.com...
>
Move indexes to new filegroup
If a new filegroup is created on a new disk drive, is there a way to migrate
a specific clustered index to the new filegroup?
--
Regards,
JamieUse CREATE INDEX with the DROP EXISTING clause. Example:
USE master
GO
DROP DATABASE FgTest
GO
CREATE DATABASE FgTest
ON PRIMARY
( NAME = N'FgTest', FILENAME = N'C:\FgTest.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH =1024KB ),
FILEGROUP [ForData]
( NAME = N'ForMyData', FILENAME = N'C:\ForMyData.ndf' , SIZE = 3072KB , MAXSIZE = UNLIMITED,
FILEGROWTH = 1024KB )
LOG ON
( NAME = N'FgTest_log', FILENAME = N'C:\FgTest_log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB ,
FILEGROWTH = 10%)
GO
USE FgTest
CREATE TABLE t(c1 int identity, c2 char(5))
CREATE CLUSTERED INDEX x ON t(c1) ON "PRIMARY"
INSERT INTO t (c2)
SELECT TOP 10000 'Hello' FROM sysobjects, syscolumns
GO
EXEC sp_helpindex t
CREATE CLUSTERED INDEX x ON t(c1) WITH DROP_EXISTING ON "ForData"
EXEC sp_helpindex t
USE master
DROP DATABASE FgTest
GO
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:563BAAF9-92DA-48C9-906C-2467E1E73C53@.microsoft.com...
> If a new filegroup is created on a new disk drive, is there a way to migrate
> a specific clustered index to the new filegroup?
> --
> Regards,
> Jamie|||I'm having trouble with the syntax. Index is created and dropped with
ALTER TABLE [dbo].[tSupplierItemXrefs] ADD CONSTRAINT
[PK_tSupplierItemXrefs] PRIMARY KEY CLUSTERED
(
[SupplierXrefKey] ASC
) ON [PRIMARY]
IF EXISTS (SELECT * FROM dbo.sysindexes WHERE id =OBJECT_ID(N'[dbo].[tSupplierItemXrefs]') AND name = N'PK_tSupplierItemXrefs')
ALTER TABLE [dbo].[tSupplierItemXrefs] DROP CONSTRAINT [PK_tSupplierItemXrefs]
WHEN I RUN:
CREATE CLUSTERED INDEX PK_tSupplierItemXRefs ON tSupplierItemXrefs
(SupplierXrefKey) WITH DROP_EXISTING ON "DDRIVE"
I get an error:
Server: Msg 1907, Level 16, State 1, Line 1
Cannot re-create index 'PK_tSupplierItemXRefs'. The new index definition
does not match the constraint being enforced by the existing index.
DDRIVE is the additional filegroup I created for this table and which exists
on a drive other than the primary filegroup drive.
--
Regards,
Jamie
"Tibor Karaszi" wrote:
> Use CREATE INDEX with the DROP EXISTING clause. Example:
> USE master
> GO
> DROP DATABASE FgTest
> GO
> CREATE DATABASE FgTest
> ON PRIMARY
> ( NAME = N'FgTest', FILENAME = N'C:\FgTest.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH => 1024KB ),
> FILEGROUP [ForData]
> ( NAME = N'ForMyData', FILENAME = N'C:\ForMyData.ndf' , SIZE = 3072KB , MAXSIZE = UNLIMITED,
> FILEGROWTH = 1024KB )
> LOG ON
> ( NAME = N'FgTest_log', FILENAME = N'C:\FgTest_log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB ,
> FILEGROWTH = 10%)
> GO
> USE FgTest
> CREATE TABLE t(c1 int identity, c2 char(5))
> CREATE CLUSTERED INDEX x ON t(c1) ON "PRIMARY"
> INSERT INTO t (c2)
> SELECT TOP 10000 'Hello' FROM sysobjects, syscolumns
> GO
> EXEC sp_helpindex t
> CREATE CLUSTERED INDEX x ON t(c1) WITH DROP_EXISTING ON "ForData"
> EXEC sp_helpindex t
> USE master
> DROP DATABASE FgTest
> GO
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:563BAAF9-92DA-48C9-906C-2467E1E73C53@.microsoft.com...
> > If a new filegroup is created on a new disk drive, is there a way to migrate
> > a specific clustered index to the new filegroup?
> > --
> > Regards,
> > Jamie
>|||Try adding UNIQUE to the index definition, since it comes from a primary key constraint:
CREATE UNIQUE CLUSTERED INDEX PK_tSupplierItemXRefs ON tSupplierItemXrefs
(SupplierXrefKey) WITH DROP_EXISTING ON "DDRIVE"
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:6FF49395-56FF-453F-8E67-34AEFD91408D@.microsoft.com...
> I'm having trouble with the syntax. Index is created and dropped with
> ALTER TABLE [dbo].[tSupplierItemXrefs] ADD CONSTRAINT
> [PK_tSupplierItemXrefs] PRIMARY KEY CLUSTERED
> (
> [SupplierXrefKey] ASC
> ) ON [PRIMARY]
>
> IF EXISTS (SELECT * FROM dbo.sysindexes WHERE id => OBJECT_ID(N'[dbo].[tSupplierItemXrefs]') AND name = N'PK_tSupplierItemXrefs')
> ALTER TABLE [dbo].[tSupplierItemXrefs] DROP CONSTRAINT [PK_tSupplierItemXrefs]
> WHEN I RUN:
> CREATE CLUSTERED INDEX PK_tSupplierItemXRefs ON tSupplierItemXrefs
> (SupplierXrefKey) WITH DROP_EXISTING ON "DDRIVE"
> I get an error:
> Server: Msg 1907, Level 16, State 1, Line 1
> Cannot re-create index 'PK_tSupplierItemXRefs'. The new index definition
> does not match the constraint being enforced by the existing index.
> DDRIVE is the additional filegroup I created for this table and which exists
> on a drive other than the primary filegroup drive.
> --
> Regards,
> Jamie
>
> "Tibor Karaszi" wrote:
>> Use CREATE INDEX with the DROP EXISTING clause. Example:
>> USE master
>> GO
>> DROP DATABASE FgTest
>> GO
>> CREATE DATABASE FgTest
>> ON PRIMARY
>> ( NAME = N'FgTest', FILENAME = N'C:\FgTest.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH
>> =>> 1024KB ),
>> FILEGROUP [ForData]
>> ( NAME = N'ForMyData', FILENAME = N'C:\ForMyData.ndf' , SIZE = 3072KB , MAXSIZE = UNLIMITED,
>> FILEGROWTH = 1024KB )
>> LOG ON
>> ( NAME = N'FgTest_log', FILENAME = N'C:\FgTest_log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB ,
>> FILEGROWTH = 10%)
>> GO
>> USE FgTest
>> CREATE TABLE t(c1 int identity, c2 char(5))
>> CREATE CLUSTERED INDEX x ON t(c1) ON "PRIMARY"
>> INSERT INTO t (c2)
>> SELECT TOP 10000 'Hello' FROM sysobjects, syscolumns
>> GO
>> EXEC sp_helpindex t
>> CREATE CLUSTERED INDEX x ON t(c1) WITH DROP_EXISTING ON "ForData"
>> EXEC sp_helpindex t
>> USE master
>> DROP DATABASE FgTest
>> GO
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> news:563BAAF9-92DA-48C9-906C-2467E1E73C53@.microsoft.com...
>> > If a new filegroup is created on a new disk drive, is there a way to migrate
>> > a specific clustered index to the new filegroup?
>> > --
>> > Regards,
>> > Jamie
>>
a specific clustered index to the new filegroup?
--
Regards,
JamieUse CREATE INDEX with the DROP EXISTING clause. Example:
USE master
GO
DROP DATABASE FgTest
GO
CREATE DATABASE FgTest
ON PRIMARY
( NAME = N'FgTest', FILENAME = N'C:\FgTest.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH =1024KB ),
FILEGROUP [ForData]
( NAME = N'ForMyData', FILENAME = N'C:\ForMyData.ndf' , SIZE = 3072KB , MAXSIZE = UNLIMITED,
FILEGROWTH = 1024KB )
LOG ON
( NAME = N'FgTest_log', FILENAME = N'C:\FgTest_log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB ,
FILEGROWTH = 10%)
GO
USE FgTest
CREATE TABLE t(c1 int identity, c2 char(5))
CREATE CLUSTERED INDEX x ON t(c1) ON "PRIMARY"
INSERT INTO t (c2)
SELECT TOP 10000 'Hello' FROM sysobjects, syscolumns
GO
EXEC sp_helpindex t
CREATE CLUSTERED INDEX x ON t(c1) WITH DROP_EXISTING ON "ForData"
EXEC sp_helpindex t
USE master
DROP DATABASE FgTest
GO
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:563BAAF9-92DA-48C9-906C-2467E1E73C53@.microsoft.com...
> If a new filegroup is created on a new disk drive, is there a way to migrate
> a specific clustered index to the new filegroup?
> --
> Regards,
> Jamie|||I'm having trouble with the syntax. Index is created and dropped with
ALTER TABLE [dbo].[tSupplierItemXrefs] ADD CONSTRAINT
[PK_tSupplierItemXrefs] PRIMARY KEY CLUSTERED
(
[SupplierXrefKey] ASC
) ON [PRIMARY]
IF EXISTS (SELECT * FROM dbo.sysindexes WHERE id =OBJECT_ID(N'[dbo].[tSupplierItemXrefs]') AND name = N'PK_tSupplierItemXrefs')
ALTER TABLE [dbo].[tSupplierItemXrefs] DROP CONSTRAINT [PK_tSupplierItemXrefs]
WHEN I RUN:
CREATE CLUSTERED INDEX PK_tSupplierItemXRefs ON tSupplierItemXrefs
(SupplierXrefKey) WITH DROP_EXISTING ON "DDRIVE"
I get an error:
Server: Msg 1907, Level 16, State 1, Line 1
Cannot re-create index 'PK_tSupplierItemXRefs'. The new index definition
does not match the constraint being enforced by the existing index.
DDRIVE is the additional filegroup I created for this table and which exists
on a drive other than the primary filegroup drive.
--
Regards,
Jamie
"Tibor Karaszi" wrote:
> Use CREATE INDEX with the DROP EXISTING clause. Example:
> USE master
> GO
> DROP DATABASE FgTest
> GO
> CREATE DATABASE FgTest
> ON PRIMARY
> ( NAME = N'FgTest', FILENAME = N'C:\FgTest.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH => 1024KB ),
> FILEGROUP [ForData]
> ( NAME = N'ForMyData', FILENAME = N'C:\ForMyData.ndf' , SIZE = 3072KB , MAXSIZE = UNLIMITED,
> FILEGROWTH = 1024KB )
> LOG ON
> ( NAME = N'FgTest_log', FILENAME = N'C:\FgTest_log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB ,
> FILEGROWTH = 10%)
> GO
> USE FgTest
> CREATE TABLE t(c1 int identity, c2 char(5))
> CREATE CLUSTERED INDEX x ON t(c1) ON "PRIMARY"
> INSERT INTO t (c2)
> SELECT TOP 10000 'Hello' FROM sysobjects, syscolumns
> GO
> EXEC sp_helpindex t
> CREATE CLUSTERED INDEX x ON t(c1) WITH DROP_EXISTING ON "ForData"
> EXEC sp_helpindex t
> USE master
> DROP DATABASE FgTest
> GO
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:563BAAF9-92DA-48C9-906C-2467E1E73C53@.microsoft.com...
> > If a new filegroup is created on a new disk drive, is there a way to migrate
> > a specific clustered index to the new filegroup?
> > --
> > Regards,
> > Jamie
>|||Try adding UNIQUE to the index definition, since it comes from a primary key constraint:
CREATE UNIQUE CLUSTERED INDEX PK_tSupplierItemXRefs ON tSupplierItemXrefs
(SupplierXrefKey) WITH DROP_EXISTING ON "DDRIVE"
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:6FF49395-56FF-453F-8E67-34AEFD91408D@.microsoft.com...
> I'm having trouble with the syntax. Index is created and dropped with
> ALTER TABLE [dbo].[tSupplierItemXrefs] ADD CONSTRAINT
> [PK_tSupplierItemXrefs] PRIMARY KEY CLUSTERED
> (
> [SupplierXrefKey] ASC
> ) ON [PRIMARY]
>
> IF EXISTS (SELECT * FROM dbo.sysindexes WHERE id => OBJECT_ID(N'[dbo].[tSupplierItemXrefs]') AND name = N'PK_tSupplierItemXrefs')
> ALTER TABLE [dbo].[tSupplierItemXrefs] DROP CONSTRAINT [PK_tSupplierItemXrefs]
> WHEN I RUN:
> CREATE CLUSTERED INDEX PK_tSupplierItemXRefs ON tSupplierItemXrefs
> (SupplierXrefKey) WITH DROP_EXISTING ON "DDRIVE"
> I get an error:
> Server: Msg 1907, Level 16, State 1, Line 1
> Cannot re-create index 'PK_tSupplierItemXRefs'. The new index definition
> does not match the constraint being enforced by the existing index.
> DDRIVE is the additional filegroup I created for this table and which exists
> on a drive other than the primary filegroup drive.
> --
> Regards,
> Jamie
>
> "Tibor Karaszi" wrote:
>> Use CREATE INDEX with the DROP EXISTING clause. Example:
>> USE master
>> GO
>> DROP DATABASE FgTest
>> GO
>> CREATE DATABASE FgTest
>> ON PRIMARY
>> ( NAME = N'FgTest', FILENAME = N'C:\FgTest.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH
>> =>> 1024KB ),
>> FILEGROUP [ForData]
>> ( NAME = N'ForMyData', FILENAME = N'C:\ForMyData.ndf' , SIZE = 3072KB , MAXSIZE = UNLIMITED,
>> FILEGROWTH = 1024KB )
>> LOG ON
>> ( NAME = N'FgTest_log', FILENAME = N'C:\FgTest_log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB ,
>> FILEGROWTH = 10%)
>> GO
>> USE FgTest
>> CREATE TABLE t(c1 int identity, c2 char(5))
>> CREATE CLUSTERED INDEX x ON t(c1) ON "PRIMARY"
>> INSERT INTO t (c2)
>> SELECT TOP 10000 'Hello' FROM sysobjects, syscolumns
>> GO
>> EXEC sp_helpindex t
>> CREATE CLUSTERED INDEX x ON t(c1) WITH DROP_EXISTING ON "ForData"
>> EXEC sp_helpindex t
>> USE master
>> DROP DATABASE FgTest
>> GO
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> news:563BAAF9-92DA-48C9-906C-2467E1E73C53@.microsoft.com...
>> > If a new filegroup is created on a new disk drive, is there a way to migrate
>> > a specific clustered index to the new filegroup?
>> > --
>> > Regards,
>> > Jamie
>>
Move indexes to new filegroup
If a new filegroup is created on a new disk drive, is there a way to migrate
a specific clustered index to the new filegroup?
--
Regards,
JamieUse CREATE INDEX with the DROP EXISTING clause. Example:
USE master
GO
DROP DATABASE FgTest
GO
CREATE DATABASE FgTest
ON PRIMARY
( NAME = N'FgTest', FILENAME = N'C:\FgTest.mdf' , SIZE = 3072KB , MAXSIZE =
UNLIMITED, FILEGROWTH =
1024KB ),
FILEGROUP [ForData]
( NAME = N'ForMyData', FILENAME = N'C:\ForMyData.ndf' , SIZE = 3072KB , MAXS
IZE = UNLIMITED,
FILEGROWTH = 1024KB )
LOG ON
( NAME = N'FgTest_log', FILENAME = N'C:\FgTest_log.ldf' , SIZE = 2048KB , MA
XSIZE = 2048GB ,
FILEGROWTH = 10%)
GO
USE FgTest
CREATE TABLE t(c1 int identity, c2 char(5))
CREATE CLUSTERED INDEX x ON t(c1) ON "PRIMARY"
INSERT INTO t (c2)
SELECT TOP 10000 'Hello' FROM sysobjects, syscolumns
GO
EXEC sp_helpindex t
CREATE CLUSTERED INDEX x ON t(c1) WITH DROP_EXISTING ON "ForData"
EXEC sp_helpindex t
USE master
DROP DATABASE FgTest
GO
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:563BAAF9-92DA-48C9-906C-2467E1E73C53@.microsoft.com...
> If a new filegroup is created on a new disk drive, is there a way to migra
te
> a specific clustered index to the new filegroup?
> --
> Regards,
> Jamie|||I'm having trouble with the syntax. Index is created and dropped with
ALTER TABLE [dbo].[tSupplierItemXrefs] ADD CONSTRAINT
[PK_tSupplierItemXrefs] PRIMARY KEY CLUSTERED
(
[SupplierXrefKey] ASC
) ON [PRIMARY]
IF EXISTS (SELECT * FROM dbo.sysindexes WHERE id =
OBJECT_ID(N'[dbo].[tSupplierItemXrefs]') AND name = N'PK_tSupplierIt
emXrefs')
ALTER TABLE [dbo].[tSupplierItemXrefs] DROP CONSTRAINT [PK_tSupp
lierItemXrefs]
WHEN I RUN:
CREATE CLUSTERED INDEX PK_tSupplierItemXRefs ON tSupplierItemXrefs
(SupplierXrefKey) WITH DROP_EXISTING ON "DDRIVE"
I get an error:
Server: Msg 1907, Level 16, State 1, Line 1
Cannot re-create index 'PK_tSupplierItemXRefs'. The new index definition
does not match the constraint being enforced by the existing index.
DDRIVE is the additional filegroup I created for this table and which exists
on a drive other than the primary filegroup drive.
--
Regards,
Jamie
"Tibor Karaszi" wrote:
> Use CREATE INDEX with the DROP EXISTING clause. Example:
> USE master
> GO
> DROP DATABASE FgTest
> GO
> CREATE DATABASE FgTest
> ON PRIMARY
> ( NAME = N'FgTest', FILENAME = N'C:\FgTest.mdf' , SIZE = 3072KB , MAXSIZE
= UNLIMITED, FILEGROWTH =
> 1024KB ),
> FILEGROUP [ForData]
> ( NAME = N'ForMyData', FILENAME = N'C:\ForMyData.ndf' , SIZE = 3072KB , MA
XSIZE = UNLIMITED,
> FILEGROWTH = 1024KB )
> LOG ON
> ( NAME = N'FgTest_log', FILENAME = N'C:\FgTest_log.ldf' , SIZE = 2048KB ,
MAXSIZE = 2048GB ,
> FILEGROWTH = 10%)
> GO
> USE FgTest
> CREATE TABLE t(c1 int identity, c2 char(5))
> CREATE CLUSTERED INDEX x ON t(c1) ON "PRIMARY"
> INSERT INTO t (c2)
> SELECT TOP 10000 'Hello' FROM sysobjects, syscolumns
> GO
> EXEC sp_helpindex t
> CREATE CLUSTERED INDEX x ON t(c1) WITH DROP_EXISTING ON "ForData"
> EXEC sp_helpindex t
> USE master
> DROP DATABASE FgTest
> GO
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:563BAAF9-92DA-48C9-906C-2467E1E73C53@.microsoft.com...
>|||Try adding UNIQUE to the index definition, since it comes from a primary key
constraint:
CREATE UNIQUE CLUSTERED INDEX PK_tSupplierItemXRefs ON tSupplierItemXrefs
(SupplierXrefKey) WITH DROP_EXISTING ON "DDRIVE"
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:6FF49395-56FF-453F-8E67-34AEFD91408D@.microsoft.com...[vbcol=seagreen]
> I'm having trouble with the syntax. Index is created and dropped with
> ALTER TABLE [dbo].[tSupplierItemXrefs] ADD CONSTRAINT
> [PK_tSupplierItemXrefs] PRIMARY KEY CLUSTERED
> (
> [SupplierXrefKey] ASC
> ) ON [PRIMARY]
>
> IF EXISTS (SELECT * FROM dbo.sysindexes WHERE id =
> OBJECT_ID(N'[dbo].[tSupplierItemXrefs]') AND name = N'PK_tSupplier
ItemXrefs')
> ALTER TABLE [dbo].[tSupplierItemXrefs] DROP CONSTRAINT [PK_tSu
pplierItemXrefs]
> WHEN I RUN:
> CREATE CLUSTERED INDEX PK_tSupplierItemXRefs ON tSupplierItemXrefs
> (SupplierXrefKey) WITH DROP_EXISTING ON "DDRIVE"
> I get an error:
> Server: Msg 1907, Level 16, State 1, Line 1
> Cannot re-create index 'PK_tSupplierItemXRefs'. The new index definition
> does not match the constraint being enforced by the existing index.
> DDRIVE is the additional filegroup I created for this table and which exis
ts
> on a drive other than the primary filegroup drive.
> --
> Regards,
> Jamie
>
> "Tibor Karaszi" wrote:
>
a specific clustered index to the new filegroup?
--
Regards,
JamieUse CREATE INDEX with the DROP EXISTING clause. Example:
USE master
GO
DROP DATABASE FgTest
GO
CREATE DATABASE FgTest
ON PRIMARY
( NAME = N'FgTest', FILENAME = N'C:\FgTest.mdf' , SIZE = 3072KB , MAXSIZE =
UNLIMITED, FILEGROWTH =
1024KB ),
FILEGROUP [ForData]
( NAME = N'ForMyData', FILENAME = N'C:\ForMyData.ndf' , SIZE = 3072KB , MAXS
IZE = UNLIMITED,
FILEGROWTH = 1024KB )
LOG ON
( NAME = N'FgTest_log', FILENAME = N'C:\FgTest_log.ldf' , SIZE = 2048KB , MA
XSIZE = 2048GB ,
FILEGROWTH = 10%)
GO
USE FgTest
CREATE TABLE t(c1 int identity, c2 char(5))
CREATE CLUSTERED INDEX x ON t(c1) ON "PRIMARY"
INSERT INTO t (c2)
SELECT TOP 10000 'Hello' FROM sysobjects, syscolumns
GO
EXEC sp_helpindex t
CREATE CLUSTERED INDEX x ON t(c1) WITH DROP_EXISTING ON "ForData"
EXEC sp_helpindex t
USE master
DROP DATABASE FgTest
GO
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:563BAAF9-92DA-48C9-906C-2467E1E73C53@.microsoft.com...
> If a new filegroup is created on a new disk drive, is there a way to migra
te
> a specific clustered index to the new filegroup?
> --
> Regards,
> Jamie|||I'm having trouble with the syntax. Index is created and dropped with
ALTER TABLE [dbo].[tSupplierItemXrefs] ADD CONSTRAINT
[PK_tSupplierItemXrefs] PRIMARY KEY CLUSTERED
(
[SupplierXrefKey] ASC
) ON [PRIMARY]
IF EXISTS (SELECT * FROM dbo.sysindexes WHERE id =
OBJECT_ID(N'[dbo].[tSupplierItemXrefs]') AND name = N'PK_tSupplierIt
emXrefs')
ALTER TABLE [dbo].[tSupplierItemXrefs] DROP CONSTRAINT [PK_tSupp
lierItemXrefs]
WHEN I RUN:
CREATE CLUSTERED INDEX PK_tSupplierItemXRefs ON tSupplierItemXrefs
(SupplierXrefKey) WITH DROP_EXISTING ON "DDRIVE"
I get an error:
Server: Msg 1907, Level 16, State 1, Line 1
Cannot re-create index 'PK_tSupplierItemXRefs'. The new index definition
does not match the constraint being enforced by the existing index.
DDRIVE is the additional filegroup I created for this table and which exists
on a drive other than the primary filegroup drive.
--
Regards,
Jamie
"Tibor Karaszi" wrote:
> Use CREATE INDEX with the DROP EXISTING clause. Example:
> USE master
> GO
> DROP DATABASE FgTest
> GO
> CREATE DATABASE FgTest
> ON PRIMARY
> ( NAME = N'FgTest', FILENAME = N'C:\FgTest.mdf' , SIZE = 3072KB , MAXSIZE
= UNLIMITED, FILEGROWTH =
> 1024KB ),
> FILEGROUP [ForData]
> ( NAME = N'ForMyData', FILENAME = N'C:\ForMyData.ndf' , SIZE = 3072KB , MA
XSIZE = UNLIMITED,
> FILEGROWTH = 1024KB )
> LOG ON
> ( NAME = N'FgTest_log', FILENAME = N'C:\FgTest_log.ldf' , SIZE = 2048KB ,
MAXSIZE = 2048GB ,
> FILEGROWTH = 10%)
> GO
> USE FgTest
> CREATE TABLE t(c1 int identity, c2 char(5))
> CREATE CLUSTERED INDEX x ON t(c1) ON "PRIMARY"
> INSERT INTO t (c2)
> SELECT TOP 10000 'Hello' FROM sysobjects, syscolumns
> GO
> EXEC sp_helpindex t
> CREATE CLUSTERED INDEX x ON t(c1) WITH DROP_EXISTING ON "ForData"
> EXEC sp_helpindex t
> USE master
> DROP DATABASE FgTest
> GO
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:563BAAF9-92DA-48C9-906C-2467E1E73C53@.microsoft.com...
>|||Try adding UNIQUE to the index definition, since it comes from a primary key
constraint:
CREATE UNIQUE CLUSTERED INDEX PK_tSupplierItemXRefs ON tSupplierItemXrefs
(SupplierXrefKey) WITH DROP_EXISTING ON "DDRIVE"
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:6FF49395-56FF-453F-8E67-34AEFD91408D@.microsoft.com...[vbcol=seagreen]
> I'm having trouble with the syntax. Index is created and dropped with
> ALTER TABLE [dbo].[tSupplierItemXrefs] ADD CONSTRAINT
> [PK_tSupplierItemXrefs] PRIMARY KEY CLUSTERED
> (
> [SupplierXrefKey] ASC
> ) ON [PRIMARY]
>
> IF EXISTS (SELECT * FROM dbo.sysindexes WHERE id =
> OBJECT_ID(N'[dbo].[tSupplierItemXrefs]') AND name = N'PK_tSupplier
ItemXrefs')
> ALTER TABLE [dbo].[tSupplierItemXrefs] DROP CONSTRAINT [PK_tSu
pplierItemXrefs]
> WHEN I RUN:
> CREATE CLUSTERED INDEX PK_tSupplierItemXRefs ON tSupplierItemXrefs
> (SupplierXrefKey) WITH DROP_EXISTING ON "DDRIVE"
> I get an error:
> Server: Msg 1907, Level 16, State 1, Line 1
> Cannot re-create index 'PK_tSupplierItemXRefs'. The new index definition
> does not match the constraint being enforced by the existing index.
> DDRIVE is the additional filegroup I created for this table and which exis
ts
> on a drive other than the primary filegroup drive.
> --
> Regards,
> Jamie
>
> "Tibor Karaszi" wrote:
>
Wednesday, March 7, 2012
Move Filegroup another disk
I can move filegroup of tempdb another disk
Actually is c:\. I need move to e
ThankYou use the ALTER DATABASE command. See Books Online for syntax.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jorge" <anonymous@.discussions.microsoft.com> wrote in message
news:DDDE61FC-2A05-4548-92CD-086E3C1705E2@.microsoft.com...
> I can move filegroup of tempdb another disk?
> Actually is c:\. I need move to e:
> Thanks
>
Actually is c:\. I need move to e
ThankYou use the ALTER DATABASE command. See Books Online for syntax.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jorge" <anonymous@.discussions.microsoft.com> wrote in message
news:DDDE61FC-2A05-4548-92CD-086E3C1705E2@.microsoft.com...
> I can move filegroup of tempdb another disk?
> Actually is c:\. I need move to e:
> Thanks
>
Monday, February 20, 2012
Moving indexes to their own disk drive
I am trying to find out if it is possible to move indexes to a separate filegroup/disk drive during database restore. I am trying this to see if it improves performance. Also if I cannot move the indexes during restore, how would I move them afterwards to a different filegroup/disk drive? Thanks in advance for all the help.Just recreate indexes:
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
ON { table | view } ( column [ ASC | DESC ] [ ,...n ] )
[ WITH < index_option > [ ,...n] ]
[ ON filegroup ] --<<<<<<<<<<<<<<<<<<<<<<|||Look up ALTER TABLE
I think you can ALTER the contraint (The indexes) and change the file group
Never done it though...let me look into it...|||I was meesing with
USE Northwind
GO
CREATE TABLE myTable99 (Col1 int IDENTITY(1,1),Col2 char(1))
GO
CREATE INDEX myTable99_IX1 ON myTable99(Col1)
GO
ALTER TABLE myTable99 NOCHECK CONSTRAINT myTable99_IX1 ON Secondary
GO
DROP TABLE myTable99
GO
But can't get it to work...
Seems like you have to drop and recreate...I would have thought there might have been a move or alter or something...
How big is the database?|||Will that SQL allow me to move the existing index to a different drive, and can you show me an example. Thanks.|||One thing to remember, you can not move a clustered index to a drive (filegroup) that is different from the filegroup the data is on. In short, a clustered index is the data. As for an example, suppose you have two filegroups named UserData and IndexData. For table Customers you would have:
create (unique) index indexname on Customers (fields) on IndexData
alter table Customers add constraint pk_customers primary key (keyfield(s)) on UserData|||I can't find a way to move the index across filegroups...
Even EM does a drop and recreate...|||if this is going to be a one time thing then i would invest the time and do it right and completely recreate the index on the new disk.
this would give you a fresh index on the new disk
and kids
dont forget your fill factor.
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
ON { table | view } ( column [ ASC | DESC ] [ ,...n ] )
[ WITH < index_option > [ ,...n] ]
[ ON filegroup ] --<<<<<<<<<<<<<<<<<<<<<<|||Look up ALTER TABLE
I think you can ALTER the contraint (The indexes) and change the file group
Never done it though...let me look into it...|||I was meesing with
USE Northwind
GO
CREATE TABLE myTable99 (Col1 int IDENTITY(1,1),Col2 char(1))
GO
CREATE INDEX myTable99_IX1 ON myTable99(Col1)
GO
ALTER TABLE myTable99 NOCHECK CONSTRAINT myTable99_IX1 ON Secondary
GO
DROP TABLE myTable99
GO
But can't get it to work...
Seems like you have to drop and recreate...I would have thought there might have been a move or alter or something...
How big is the database?|||Will that SQL allow me to move the existing index to a different drive, and can you show me an example. Thanks.|||One thing to remember, you can not move a clustered index to a drive (filegroup) that is different from the filegroup the data is on. In short, a clustered index is the data. As for an example, suppose you have two filegroups named UserData and IndexData. For table Customers you would have:
create (unique) index indexname on Customers (fields) on IndexData
alter table Customers add constraint pk_customers primary key (keyfield(s)) on UserData|||I can't find a way to move the index across filegroups...
Even EM does a drop and recreate...|||if this is going to be a one time thing then i would invest the time and do it right and completely recreate the index on the new disk.
this would give you a fresh index on the new disk
and kids
dont forget your fill factor.
Subscribe to:
Posts (Atom)