SQL Server 7 SP3a.
In an effort to reclaim a large amount of space on disk, I have moved the
logfile from one drive to another using the steps outlined in this article:
http://www.microsoft.com/sql/techinfo/tips/administration/movingtransactionlogs.asp
when I attempted to reattach the database to the new log file on the new
drive, errors were thrown and SQL Server reported that the database was
corrupt.
Further research indicated that I had to hack the sysfiles1 table to change
the filename path to reflect the new drive location. I did this and rebooted
the server. This now created a suspect database.
How can I get this to work properly?
ThanksHi
I think there may be some problems with access rights /
permissions accessing new drive and files.
Detach and Attach usually works fine.
The indications of suspect status points the problem
accessing file / drive.
Thanks
Suri
>--Original Message--
>SQL Server 7 SP3a.
>In an effort to reclaim a large amount of space on disk,
I have moved the
>logfile from one drive to another using the steps
outlined in this article:
>http://www.microsoft.com/sql/techinfo/tips/administration/
movingtransactionlogs.asp
>when I attempted to reattach the database to the new log
file on the new
>drive, errors were thrown and SQL Server reported that
the database was
>corrupt.
>Further research indicated that I had to hack the
sysfiles1 table to change
>the filename path to reflect the new drive location. I
did this and rebooted
>the server. This now created a suspect database.
>How can I get this to work properly?
>Thanks
>
>.
>
Showing posts with label disk. Show all posts
Showing posts with label disk. Show all posts
Monday, March 26, 2012
Monday, March 12, 2012
Placing Log on another drive
We have a database that uses the same drive for logs and
data. After a new disk has been installed what is the
easiest way (and reliable) way to move the logs to the new
disk.
I'm thinking of dumping and restoring the database using
the log on clause of restore. Any other ideas ?
TIA,
Jack
Your two best options are probably
BACKUP and RESTORE (WITH MOVE)
or
sp_detach_db and sp_attach_db
More information on each of these can be found within Books Online =
(within your SQL Server program group).
--=20
Keith
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message =
news:1b06e01c44fce$eadf5a60$a601280a@.phx.gbl...
>=20
> We have a database that uses the same drive for logs and=20
> data. After a new disk has been installed what is the=20
> easiest way (and reliable) way to move the logs to the new=20
> disk.
>=20
> I'm thinking of dumping and restoring the database using=20
> the log on clause of restore. Any other ideas ?
>=20
> TIA,
> Jack
|||Hi,
Steps to move LDF file to new drive
1. Execute detach database (SP_DETACH_DB <dbname>
2. Copy the LDF file to new drive
3. Attach the database (SP_DETACH_DB <dbname>,'MDF file with path','LDF file
with path'
Before doing the above steps , make the database single user
alter database <dbname> set single_user with rollback immediate
After the activity turn the databse to multi user by executing
alter database <dbname> set multi_user
Thanks
Hari
MCDBA
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:1b06e01c44fce$eadf5a60$a601280a@.phx.gbl...
> We have a database that uses the same drive for logs and
> data. After a new disk has been installed what is the
> easiest way (and reliable) way to move the logs to the new
> disk.
> I'm thinking of dumping and restoring the database using
> the log on clause of restore. Any other ideas ?
> TIA,
> Jack
|||Backup and then Restore WITH MOVE
Detach\Reattach requires downtime
Greg Jackson
PDX, Oregon
|||Just to be clear: both options require some downtime. =20
--=20
Keith
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message =
news:%234EOGi$TEHA.1260@.TK2MSFTNGP11.phx.gbl...
> Backup and then Restore WITH MOVE
>=20
> Detach\Reattach requires downtime
>=20
>=20
>=20
> Greg Jackson
> PDX, Oregon
>=20
>
|||In article <OZKelQgUEHA.1012@.TK2MSFTNGP09.phx.gbl>, "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote:
>Just to be clear: both options require some downtime. =20
>--=20
>Keith
Huh?
Why wouldn't you just add a second logfile, move the data to the secondfile,
then remove the first logfile?
data. After a new disk has been installed what is the
easiest way (and reliable) way to move the logs to the new
disk.
I'm thinking of dumping and restoring the database using
the log on clause of restore. Any other ideas ?
TIA,
Jack
Your two best options are probably
BACKUP and RESTORE (WITH MOVE)
or
sp_detach_db and sp_attach_db
More information on each of these can be found within Books Online =
(within your SQL Server program group).
--=20
Keith
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message =
news:1b06e01c44fce$eadf5a60$a601280a@.phx.gbl...
>=20
> We have a database that uses the same drive for logs and=20
> data. After a new disk has been installed what is the=20
> easiest way (and reliable) way to move the logs to the new=20
> disk.
>=20
> I'm thinking of dumping and restoring the database using=20
> the log on clause of restore. Any other ideas ?
>=20
> TIA,
> Jack
|||Hi,
Steps to move LDF file to new drive
1. Execute detach database (SP_DETACH_DB <dbname>
2. Copy the LDF file to new drive
3. Attach the database (SP_DETACH_DB <dbname>,'MDF file with path','LDF file
with path'
Before doing the above steps , make the database single user
alter database <dbname> set single_user with rollback immediate
After the activity turn the databse to multi user by executing
alter database <dbname> set multi_user
Thanks
Hari
MCDBA
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:1b06e01c44fce$eadf5a60$a601280a@.phx.gbl...
> We have a database that uses the same drive for logs and
> data. After a new disk has been installed what is the
> easiest way (and reliable) way to move the logs to the new
> disk.
> I'm thinking of dumping and restoring the database using
> the log on clause of restore. Any other ideas ?
> TIA,
> Jack
|||Backup and then Restore WITH MOVE
Detach\Reattach requires downtime
Greg Jackson
PDX, Oregon
|||Just to be clear: both options require some downtime. =20
--=20
Keith
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message =
news:%234EOGi$TEHA.1260@.TK2MSFTNGP11.phx.gbl...
> Backup and then Restore WITH MOVE
>=20
> Detach\Reattach requires downtime
>=20
>=20
>=20
> Greg Jackson
> PDX, Oregon
>=20
>
|||In article <OZKelQgUEHA.1012@.TK2MSFTNGP09.phx.gbl>, "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote:
>Just to be clear: both options require some downtime. =20
>--=20
>Keith
Huh?
Why wouldn't you just add a second logfile, move the data to the secondfile,
then remove the first logfile?
Placing Log on another drive
We have a database that uses the same drive for logs and
data. After a new disk has been installed what is the
easiest way (and reliable) way to move the logs to the new
disk.
I'm thinking of dumping and restoring the database using
the log on clause of restore. Any other ideas ?
TIA,
JackYour two best options are probably
BACKUP and RESTORE (WITH MOVE)
or
sp_detach_db and sp_attach_db
More information on each of these can be found within Books Online =(within your SQL Server program group).
-- Keith
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message =news:1b06e01c44fce$eadf5a60$a601280a@.phx.gbl...
> > We have a database that uses the same drive for logs and > data. After a new disk has been installed what is the > easiest way (and reliable) way to move the logs to the new > disk.
> > I'm thinking of dumping and restoring the database using > the log on clause of restore. Any other ideas ?
> > TIA,
> Jack|||Hi,
Steps to move LDF file to new drive
1. Execute detach database (SP_DETACH_DB <dbname>
2. Copy the LDF file to new drive
3. Attach the database (SP_DETACH_DB <dbname>,'MDF file with path','LDF file
with path'
Before doing the above steps , make the database single user
alter database <dbname> set single_user with rollback immediate
After the activity turn the databse to multi user by executing
alter database <dbname> set multi_user
Thanks
Hari
MCDBA
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:1b06e01c44fce$eadf5a60$a601280a@.phx.gbl...
> We have a database that uses the same drive for logs and
> data. After a new disk has been installed what is the
> easiest way (and reliable) way to move the logs to the new
> disk.
> I'm thinking of dumping and restoring the database using
> the log on clause of restore. Any other ideas ?
> TIA,
> Jack|||Backup and then Restore WITH MOVE
Detach\Reattach requires downtime
Greg Jackson
PDX, Oregon|||Just to be clear: both options require some downtime. -- Keith
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message =news:%234EOGi$TEHA.1260@.TK2MSFTNGP11.phx.gbl...
> Backup and then Restore WITH MOVE
> > Detach\Reattach requires downtime
> > > > Greg Jackson
> PDX, Oregon
> >|||In article <OZKelQgUEHA.1012@.TK2MSFTNGP09.phx.gbl>, "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote:
>Just to be clear: both options require some downtime. >-- >Keith
Huh?
Why wouldn't you just add a second logfile, move the data to the secondfile,
then remove the first logfile?
data. After a new disk has been installed what is the
easiest way (and reliable) way to move the logs to the new
disk.
I'm thinking of dumping and restoring the database using
the log on clause of restore. Any other ideas ?
TIA,
JackYour two best options are probably
BACKUP and RESTORE (WITH MOVE)
or
sp_detach_db and sp_attach_db
More information on each of these can be found within Books Online =(within your SQL Server program group).
-- Keith
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message =news:1b06e01c44fce$eadf5a60$a601280a@.phx.gbl...
> > We have a database that uses the same drive for logs and > data. After a new disk has been installed what is the > easiest way (and reliable) way to move the logs to the new > disk.
> > I'm thinking of dumping and restoring the database using > the log on clause of restore. Any other ideas ?
> > TIA,
> Jack|||Hi,
Steps to move LDF file to new drive
1. Execute detach database (SP_DETACH_DB <dbname>
2. Copy the LDF file to new drive
3. Attach the database (SP_DETACH_DB <dbname>,'MDF file with path','LDF file
with path'
Before doing the above steps , make the database single user
alter database <dbname> set single_user with rollback immediate
After the activity turn the databse to multi user by executing
alter database <dbname> set multi_user
Thanks
Hari
MCDBA
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:1b06e01c44fce$eadf5a60$a601280a@.phx.gbl...
> We have a database that uses the same drive for logs and
> data. After a new disk has been installed what is the
> easiest way (and reliable) way to move the logs to the new
> disk.
> I'm thinking of dumping and restoring the database using
> the log on clause of restore. Any other ideas ?
> TIA,
> Jack|||Backup and then Restore WITH MOVE
Detach\Reattach requires downtime
Greg Jackson
PDX, Oregon|||Just to be clear: both options require some downtime. -- Keith
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message =news:%234EOGi$TEHA.1260@.TK2MSFTNGP11.phx.gbl...
> Backup and then Restore WITH MOVE
> > Detach\Reattach requires downtime
> > > > Greg Jackson
> PDX, Oregon
> >|||In article <OZKelQgUEHA.1012@.TK2MSFTNGP09.phx.gbl>, "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote:
>Just to be clear: both options require some downtime. >-- >Keith
Huh?
Why wouldn't you just add a second logfile, move the data to the secondfile,
then remove the first logfile?
Placing Log on another drive
We have a database that uses the same drive for logs and
data. After a new disk has been installed what is the
easiest way (and reliable) way to move the logs to the new
disk.
I'm thinking of dumping and restoring the database using
the log on clause of restore. Any other ideas ?
TIA,
JackYour two best options are probably
BACKUP and RESTORE (WITH MOVE)
or
sp_detach_db and sp_attach_db
More information on each of these can be found within Books Online =
(within your SQL Server program group).
--=20
Keith
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message =
news:1b06e01c44fce$eadf5a60$a601280a@.phx
.gbl...
>=20
> We have a database that uses the same drive for logs and=20
> data. After a new disk has been installed what is the=20
> easiest way (and reliable) way to move the logs to the new=20
> disk.
>=20
> I'm thinking of dumping and restoring the database using=20
> the log on clause of restore. Any other ideas ?
>=20
> TIA,
> Jack|||Hi,
Steps to move LDF file to new drive
1. Execute detach database (SP_DETACH_DB <dbname>
2. Copy the LDF file to new drive
3. Attach the database (SP_DETACH_DB <dbname>,'MDF file with path','LDF file
with path'
Before doing the above steps , make the database single user
alter database <dbname> set single_user with rollback immediate
After the activity turn the databse to multi user by executing
alter database <dbname> set multi_user
Thanks
Hari
MCDBA
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:1b06e01c44fce$eadf5a60$a601280a@.phx
.gbl...
> We have a database that uses the same drive for logs and
> data. After a new disk has been installed what is the
> easiest way (and reliable) way to move the logs to the new
> disk.
> I'm thinking of dumping and restoring the database using
> the log on clause of restore. Any other ideas ?
> TIA,
> Jack|||Backup and then Restore WITH MOVE
Detach\Reattach requires downtime
Greg Jackson
PDX, Oregon|||Just to be clear: both options require some downtime. =20
--=20
Keith
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message =
news:%234EOGi$TEHA.1260@.TK2MSFTNGP11.phx.gbl...
> Backup and then Restore WITH MOVE
>=20
> Detach\Reattach requires downtime
>=20
>=20
>=20
> Greg Jackson
> PDX, Oregon
>=20
>|||In article <OZKelQgUEHA.1012@.TK2MSFTNGP09.phx.gbl>, "Keith Kratochvil" <sqlguy.back2u@.comcas
t.net> wrote:
>Just to be clear: both options require some downtime. =20
>--=20
>Keith
Huh?
Why wouldn't you just add a second logfile, move the data to the secondfile,
then remove the first logfile?
data. After a new disk has been installed what is the
easiest way (and reliable) way to move the logs to the new
disk.
I'm thinking of dumping and restoring the database using
the log on clause of restore. Any other ideas ?
TIA,
JackYour two best options are probably
BACKUP and RESTORE (WITH MOVE)
or
sp_detach_db and sp_attach_db
More information on each of these can be found within Books Online =
(within your SQL Server program group).
--=20
Keith
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message =
news:1b06e01c44fce$eadf5a60$a601280a@.phx
.gbl...
>=20
> We have a database that uses the same drive for logs and=20
> data. After a new disk has been installed what is the=20
> easiest way (and reliable) way to move the logs to the new=20
> disk.
>=20
> I'm thinking of dumping and restoring the database using=20
> the log on clause of restore. Any other ideas ?
>=20
> TIA,
> Jack|||Hi,
Steps to move LDF file to new drive
1. Execute detach database (SP_DETACH_DB <dbname>
2. Copy the LDF file to new drive
3. Attach the database (SP_DETACH_DB <dbname>,'MDF file with path','LDF file
with path'
Before doing the above steps , make the database single user
alter database <dbname> set single_user with rollback immediate
After the activity turn the databse to multi user by executing
alter database <dbname> set multi_user
Thanks
Hari
MCDBA
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:1b06e01c44fce$eadf5a60$a601280a@.phx
.gbl...
> We have a database that uses the same drive for logs and
> data. After a new disk has been installed what is the
> easiest way (and reliable) way to move the logs to the new
> disk.
> I'm thinking of dumping and restoring the database using
> the log on clause of restore. Any other ideas ?
> TIA,
> Jack|||Backup and then Restore WITH MOVE
Detach\Reattach requires downtime
Greg Jackson
PDX, Oregon|||Just to be clear: both options require some downtime. =20
--=20
Keith
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message =
news:%234EOGi$TEHA.1260@.TK2MSFTNGP11.phx.gbl...
> Backup and then Restore WITH MOVE
>=20
> Detach\Reattach requires downtime
>=20
>=20
>=20
> Greg Jackson
> PDX, Oregon
>=20
>|||In article <OZKelQgUEHA.1012@.TK2MSFTNGP09.phx.gbl>, "Keith Kratochvil" <sqlguy.back2u@.comcas
t.net> wrote:
>Just to be clear: both options require some downtime. =20
>--=20
>Keith
Huh?
Why wouldn't you just add a second logfile, move the data to the secondfile,
then remove the first logfile?
Placing data and log files seperately
Hi, Gurus, I am running SQL-Server 2000 on SCSIIs with Raid-5. Three disks. I see only one Logical Disk with 3 partitions. Since it's recommended to have Log files seperate to Data files physicially, currently both exist in the D-Drive. How can it be accomplished?
Another question for ur kind suggestion. I have a merge-repl setup. Publisher and distributor at the same machine. One subscriber over the internet. Replication works through VPN rather that FTP. Whenever i apply SNAPSHOT it takes more that 12 hrs to replicate the 2 GB DB. Although VPN is more secured but the tunneling makes the replication rather slow i guess. Will there be a major difference if i open the default port of ftp on the ISA and change the subscriber to get the SNAPSHOT as Anonymous Subsciption. I mean What's better, Security VS Performance. During the SNAPSHOT the Web-users running on the Subscriber wait for the latest data which is very much annoying.
Another question please, I wanna replace the current publisher-DB machine with a new Hardware-Machine. I don't wanna lose Replication and re-running the SNAPSHOT. Is it possible to retain the publication by restoring the Master, Publication and Distributor respectively on the new machine. The new machine will have the same Operating system name and configuration.
Regards!!Howdy,
Buy another disk for your log if you want to go that way. As you have a RAID 5 array, all disks in the array will besame speed. The benefit of a separate disk for logs is that logs are written sequentially ( so faster ).
When you have new disk for logs, detach the database from the sevrer, move the log files ( .LDF ) to new log disk, reattach database with logs in the new location.
Security is worth the slow speed....especially if you get hacked. But, there may be a way to get data onto a secure FTP site.
Restore should be OK. Treat this like a disaster recovery test.
Cheers,
SG.|||Another question please, I wanna replace the current publisher-DB machine with a new Hardware-Machine. I don't wanna lose Replication and re-running the SNAPSHOT. Is it possible to retain the publication by restoring the Master, Publication and Distributor respectively on the new machine. The new machine will have the same Operating system name and configuration.
No problem refer to books online for 'Backing Up and Restoring Replication Databases' topic for more details.|||Thanx. I'll let u know if i face any problem while doing the restore.
Regards.
Another question for ur kind suggestion. I have a merge-repl setup. Publisher and distributor at the same machine. One subscriber over the internet. Replication works through VPN rather that FTP. Whenever i apply SNAPSHOT it takes more that 12 hrs to replicate the 2 GB DB. Although VPN is more secured but the tunneling makes the replication rather slow i guess. Will there be a major difference if i open the default port of ftp on the ISA and change the subscriber to get the SNAPSHOT as Anonymous Subsciption. I mean What's better, Security VS Performance. During the SNAPSHOT the Web-users running on the Subscriber wait for the latest data which is very much annoying.
Another question please, I wanna replace the current publisher-DB machine with a new Hardware-Machine. I don't wanna lose Replication and re-running the SNAPSHOT. Is it possible to retain the publication by restoring the Master, Publication and Distributor respectively on the new machine. The new machine will have the same Operating system name and configuration.
Regards!!Howdy,
Buy another disk for your log if you want to go that way. As you have a RAID 5 array, all disks in the array will besame speed. The benefit of a separate disk for logs is that logs are written sequentially ( so faster ).
When you have new disk for logs, detach the database from the sevrer, move the log files ( .LDF ) to new log disk, reattach database with logs in the new location.
Security is worth the slow speed....especially if you get hacked. But, there may be a way to get data onto a secure FTP site.
Restore should be OK. Treat this like a disaster recovery test.
Cheers,
SG.|||Another question please, I wanna replace the current publisher-DB machine with a new Hardware-Machine. I don't wanna lose Replication and re-running the SNAPSHOT. Is it possible to retain the publication by restoring the Master, Publication and Distributor respectively on the new machine. The new machine will have the same Operating system name and configuration.
No problem refer to books online for 'Backing Up and Restoring Replication Databases' topic for more details.|||Thanx. I'll let u know if i face any problem while doing the restore.
Regards.
Place your data and log files on a SAN disk
Hi all!
I have a question about SQL Server 2000 and SAN.
Is it supported by MS to place your data and log files on a SAN disk? I have tryed to get info about this on MS site but can't find everything.
Links or info would be greatly appreciated!
Regards
Fredrik
Fredrik,
Yes it is supported, in fact I would recommend it. Do not use NAS as
mentioned in this article:
Microsoft Knowledge Base Article - 304261
INF: Support for Network Database Files
http://support.microsoft.com/?id=304261
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Fredrik" <anonymous@.discussions.microsoft.com> wrote in message
news:DEC7445D-2912-4B7C-A7D9-1ECC627DADEA@.microsoft.com...
> Hi all!
> I have a question about SQL Server 2000 and SAN.
> Is it supported by MS to place your data and log files on a SAN disk? I
have tryed to get info about this on MS site but can't find everything.
> Links or info would be greatly appreciated!
> Regards
> Fredrik
I have a question about SQL Server 2000 and SAN.
Is it supported by MS to place your data and log files on a SAN disk? I have tryed to get info about this on MS site but can't find everything.
Links or info would be greatly appreciated!
Regards
Fredrik
Fredrik,
Yes it is supported, in fact I would recommend it. Do not use NAS as
mentioned in this article:
Microsoft Knowledge Base Article - 304261
INF: Support for Network Database Files
http://support.microsoft.com/?id=304261
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Fredrik" <anonymous@.discussions.microsoft.com> wrote in message
news:DEC7445D-2912-4B7C-A7D9-1ECC627DADEA@.microsoft.com...
> Hi all!
> I have a question about SQL Server 2000 and SAN.
> Is it supported by MS to place your data and log files on a SAN disk? I
have tryed to get info about this on MS site but can't find everything.
> Links or info would be greatly appreciated!
> Regards
> Fredrik
Place your data and log files on a SAN disk
Hi all!
I have a question about SQL Server 2000 and SAN.
Is it supported by MS to place your data and log files on a SAN disk? I have
tryed to get info about this on MS site but can't find everything.
Links or info would be greatly appreciated!
Regards
FredrikFredrik,
Yes it is supported, in fact I would recommend it. Do not use NAS as
mentioned in this article:
Microsoft Knowledge Base Article - 304261
INF: Support for Network Database Files
http://support.microsoft.com/?id=304261
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Fredrik" <anonymous@.discussions.microsoft.com> wrote in message
news:DEC7445D-2912-4B7C-A7D9-1ECC627DADEA@.microsoft.com...
> Hi all!
> I have a question about SQL Server 2000 and SAN.
> Is it supported by MS to place your data and log files on a SAN disk? I
have tryed to get info about this on MS site but can't find everything.
> Links or info would be greatly appreciated!
> Regards
> Fredrik
I have a question about SQL Server 2000 and SAN.
Is it supported by MS to place your data and log files on a SAN disk? I have
tryed to get info about this on MS site but can't find everything.
Links or info would be greatly appreciated!
Regards
FredrikFredrik,
Yes it is supported, in fact I would recommend it. Do not use NAS as
mentioned in this article:
Microsoft Knowledge Base Article - 304261
INF: Support for Network Database Files
http://support.microsoft.com/?id=304261
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Fredrik" <anonymous@.discussions.microsoft.com> wrote in message
news:DEC7445D-2912-4B7C-A7D9-1ECC627DADEA@.microsoft.com...
> Hi all!
> I have a question about SQL Server 2000 and SAN.
> Is it supported by MS to place your data and log files on a SAN disk? I
have tryed to get info about this on MS site but can't find everything.
> Links or info would be greatly appreciated!
> Regards
> Fredrik
Subscribe to:
Posts (Atom)