Moving Transaction Log to a different device

I understand how to move a transaction log to a different physical drive on
the same server but what I would like to do is move the log file to its own
server. Is this possible?
Message posted via http://www.sqlmonster.com
Hi
No. Files must reside on local storage, SAN, or at a push, on a NAS.
The network IO performance will kill any benefit of having the log on a
different server.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jason Janes via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:ea5aa3bd507542be8969e45573be732a@.SQLMonster.c om...
> I understand how to move a transaction log to a different physical drive
on
> the same server but what I would like to do is move the log file to its
own
> server. Is this possible?
> --
> Message posted via http://www.sqlmonster.com
|||Just to be clear with one exception a NAS is not supported for SQL Server
file placement.
Andrew J. Kelly SQL MVP
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:%23QxGOkeGFHA.3916@.TK2MSFTNGP12.phx.gbl...
> Hi
> No. Files must reside on local storage, SAN, or at a push, on a NAS.
> The network IO performance will kill any benefit of having the log on a
> different server.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Jason Janes via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
> news:ea5aa3bd507542be8969e45573be732a@.SQLMonster.c om...
> on
> own
>
|||Hi
Under limited circumstances:
http://support.microsoft.com/default...b;en-us;304261
If someone can afford to meet those restrictions, they can afford a SAN.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eU64apeGFHA.2752@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Just to be clear with one exception a NAS is not supported for SQL Server
> file placement.
> --
> Andrew J. Kelly SQL MVP
>
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:%23QxGOkeGFHA.3916@.TK2MSFTNGP12.phx.gbl...
drive
>

Moving Transaction Log to a different device

I understand how to move a transaction log to a different physical drive on
the same server but what I would like to do is move the log file to its own
server. Is this possible?
Message posted via http://www.droptable.comHi
No. Files must reside on local storage, SAN, or at a push, on a NAS.
The network IO performance will kill any benefit of having the log on a
different server.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jason Janes via droptable.com" <forum@.droptable.com> wrote in message
news:ea5aa3bd507542be8969e45573be732a@.SQ
droptable.com...
> I understand how to move a transaction log to a different physical drive
on
> the same server but what I would like to do is move the log file to its
own
> server. Is this possible?
> --
> Message posted via http://www.droptable.com|||Just to be clear with one exception a NAS is not supported for SQL Server
file placement.
Andrew J. Kelly SQL MVP
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:%23QxGOkeGFHA.3916@.TK2MSFTNGP12.phx.gbl...
> Hi
> No. Files must reside on local storage, SAN, or at a push, on a NAS.
> The network IO performance will kill any benefit of having the log on a
> different server.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Jason Janes via droptable.com" <forum@.droptable.com> wrote in message
> news:ea5aa3bd507542be8969e45573be732a@.SQ
droptable.com...
> on
> own
>|||Hi
Under limited circumstances:
http://support.microsoft.com/defaul...kb;en-us;304261
If someone can afford to meet those restrictions, they can afford a SAN.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eU64apeGFHA.2752@.TK2MSFTNGP12.phx.gbl...
> Just to be clear with one exception a NAS is not supported for SQL Server
> file placement.
> --
> Andrew J. Kelly SQL MVP
>
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:%23QxGOkeGFHA.3916@.TK2MSFTNGP12.phx.gbl...
drive[vbcol=seagreen]
>sql

Moving Tran Log To Another Drive

I have SQL SERVER and all the data and log files (ldf's) for 20 DB's are on
the same drive E.
I have to move the Log files to the M drive for performance.
All the databases are used 24/7 for data viewing.
Mon-Fri 9-6 used for modifcations.
The only way i can do this is at night or over the weekend.
I have to run this script for all the databases
Is this script correct?
ALTER DATABASE DB1
SET SINGLE_USER
go
EXEC sp_detach_db DB1
<<MOVE THE FILES TO M DRIVE>>
EXEC sp_attach_db DB1,
@.filename1 = N'E:\SQL1DataFiles\DB1.mdf',
@.filename2 = N'M:\SQL1LogFiles\DB1_log.ldf'
Appreciate your helpOne potential issue is that, if there are users connected, set single_user
will wait until all the users disconnect from the database.
Why don't you just detach and attach the databases manually using Enterprise
Manager o Management Studio. You will not need to specify file paths and can
disconnect users if needed.
Hope this helps,
Ben Nevarez, MCDBA, OCP
Database Administrator
"Alex Sadykov" wrote:

> I have SQL SERVER and all the data and log files (ldf's) for 20 DB's are o
n
> the same drive E.
> I have to move the Log files to the M drive for performance.
> All the databases are used 24/7 for data viewing.
> Mon-Fri 9-6 used for modifcations.
> The only way i can do this is at night or over the weekend.
> I have to run this script for all the databases
> Is this script correct?
> ALTER DATABASE DB1
> SET SINGLE_USER
> go
> EXEC sp_detach_db DB1
> <<MOVE THE FILES TO M DRIVE>>
> EXEC sp_attach_db DB1,
> @.filename1 = N'E:\SQL1DataFiles\DB1.mdf',
> @.filename2 = N'M:\SQL1LogFiles\DB1_log.ldf'
>
> Appreciate your help
>
>
>|||Ben thanks for your help
If i sp_detach_db will all the users be kicked off the DB?
If yes i should probably do this at night?
That way the downtime will be minimal?
Is there a way to move log files from sql to a new location?
If not then i can just move them from explorer and give tht paths in the
sp_attach_db statement
"Ben Nevarez" <bnevarez@.sjm.com> wrote in message
news:C29FE6DF-D758-4667-BDE1-A2B12C643E6A@.microsoft.com...[vbcol=seagreen]
> One potential issue is that, if there are users connected, set single_user
> will wait until all the users disconnect from the database.
> Why don't you just detach and attach the databases manually using
> Enterprise
> Manager o Management Studio. You will not need to specify file paths and
> can
> disconnect users if needed.
> Hope this helps,
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Alex Sadykov" wrote:
>|||> If i sp_detach_db will all the users be kicked off the DB?
No, you need to kill all connections first

> Is there a way to move log files from sql to a new location?
Use copy-paste now that detaching the database
"Alex Sadykov" <alexsadykov@.gmail.com> wrote in message
news:u7J7XTKfGHA.3588@.TK2MSFTNGP02.phx.gbl...
> Ben thanks for your help
> If i sp_detach_db will all the users be kicked off the DB?
> If yes i should probably do this at night?
> That way the downtime will be minimal?
> Is there a way to move log files from sql to a new location?
> If not then i can just move them from explorer and give tht paths in the
> sp_attach_db statement
> "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> news:C29FE6DF-D758-4667-BDE1-A2B12C643E6A@.microsoft.com...
>

Moving SQL data to another Local Disk

How simple is it to relocate the SQL data to another local disk on
the server. Our drive that we are currently using for SQL is getting
full and we are installing a RAID1 configuration to use specifically for
SQL, but I need to know the how difficult it will be to move it to
another location. If it is simple, what are the steps for this process? Than
k
you in advance.Basically you are going to Detach the DB, move the .mdf data file and
reattach the database.
Here is great how to article from Microsoft.
http://msdn.microsoft.com/library/d...>
ign_4wq1.asp
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Phil Buzzette" <PhilBuzzette@.discussions.microsoft.com> wrote in message
news:15583E07-D763-4CFE-85E5-F825AB906203@.microsoft.com...
> How simple is it to relocate the SQL data to another local disk on
> the server. Our drive that we are currently using for SQL is getting
> full and we are installing a RAID1 configuration to use specifically for
> SQL, but I need to know the how difficult it will be to move it to
> another location. If it is simple, what are the steps for this process?
> Thank
> you in advance.
>|||Thank you. This is exactly what I needed.
"Warren Brunk" wrote:

> Basically you are going to Detach the DB, move the .mdf data file and
> reattach the database.
> Here is great how to article from Microsoft.
> http://msdn.microsoft.com/library/d...
esign_4wq1.asp
>
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
> "Phil Buzzette" <PhilBuzzette@.discussions.microsoft.com> wrote in message
> news:15583E07-D763-4CFE-85E5-F825AB906203@.microsoft.com...
>
>|||before detaching the database , run checkpoint command.
Phil Buzzette wrote:[vbcol=seagreen]
> Thank you. This is exactly what I needed.
> "Warren Brunk" wrote:
>|||before detaching the database , run checkpoint command.
--krishna
Phil Buzzette wrote:[vbcol=seagreen]
> Thank you. This is exactly what I needed.
> "Warren Brunk" wrote:
>|||Not familiar with that. Could you please explain. I am somewhat new to SQL.
I
just have to do this for a customer.
"krishna" wrote:

> before detaching the database , run checkpoint command.
> --krishna
> Phil Buzzette wrote:
>|||the checkpoint command writes all data stored in memory to disk to ensure
that all data is held in the .mdf database file.
it is a good practice when moving databases using the detach method.
There are alot of things that trigger a checkpoint such as a back up comman
with No_Log option and starting and stopping the SQL server or you can run
Checkpoint in your query analyzer.
thanks,
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Phil Buzzette" <PhilBuzzette@.discussions.microsoft.com> wrote in message
news:50341C73-9539-43A2-A539-AC30BE7F7F59@.microsoft.com...[vbcol=seagreen]
> Not familiar with that. Could you please explain. I am somewhat new to
> SQL. I
> just have to do this for a customer.
> "krishna" wrote:
>

Moving SQL data to another Local Disk

How simple is it to relocate the SQL data to another local disk on
the server. Our drive that we are currently using for SQL is getting
full and we are installing a RAID1 configuration to use specifically for
SQL, but I need to know the how difficult it will be to move it to
another location. If it is simple, what are the steps for this process? Thank
you in advance.
Basically you are going to Detach the DB, move the .mdf data file and
reattach the database.
Here is great how to article from Microsoft.
http://msdn.microsoft.com/library/de...esign_4wq1.asp
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Phil Buzzette" <PhilBuzzette@.discussions.microsoft.com> wrote in message
news:15583E07-D763-4CFE-85E5-F825AB906203@.microsoft.com...
> How simple is it to relocate the SQL data to another local disk on
> the server. Our drive that we are currently using for SQL is getting
> full and we are installing a RAID1 configuration to use specifically for
> SQL, but I need to know the how difficult it will be to move it to
> another location. If it is simple, what are the steps for this process?
> Thank
> you in advance.
>
|||Thank you. This is exactly what I needed.
"Warren Brunk" wrote:

> Basically you are going to Detach the DB, move the .mdf data file and
> reattach the database.
> Here is great how to article from Microsoft.
> http://msdn.microsoft.com/library/de...esign_4wq1.asp
>
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
> "Phil Buzzette" <PhilBuzzette@.discussions.microsoft.com> wrote in message
> news:15583E07-D763-4CFE-85E5-F825AB906203@.microsoft.com...
>
>
|||before detaching the database , run checkpoint command.
Phil Buzzette wrote:[vbcol=seagreen]
> Thank you. This is exactly what I needed.
> "Warren Brunk" wrote:
|||before detaching the database , run checkpoint command.
--krishna
Phil Buzzette wrote:[vbcol=seagreen]
> Thank you. This is exactly what I needed.
> "Warren Brunk" wrote:
|||Not familiar with that. Could you please explain. I am somewhat new to SQL. I
just have to do this for a customer.
"krishna" wrote:

> before detaching the database , run checkpoint command.
> --krishna
> Phil Buzzette wrote:
>
|||the checkpoint command writes all data stored in memory to disk to ensure
that all data is held in the .mdf database file.
it is a good practice when moving databases using the detach method.
There are alot of things that trigger a checkpoint such as a back up comman
with No_Log option and starting and stopping the SQL server or you can run
Checkpoint in your query analyzer.
thanks,
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Phil Buzzette" <PhilBuzzette@.discussions.microsoft.com> wrote in message
news:50341C73-9539-43A2-A539-AC30BE7F7F59@.microsoft.com...[vbcol=seagreen]
> Not familiar with that. Could you please explain. I am somewhat new to
> SQL. I
> just have to do this for a customer.
> "krishna" wrote: