Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

move transaction log to a different drive. Expand / Collapse
Author
Message
Posted Wednesday, October 10, 2012 3:30 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 7:37 PM
Points: 1,293, Visits: 1,140
Hi SQL Guru's,

When we migrated our mission critical application to a different server, one DBA placed the transaction log in a different SAN drive. Without noticing it we went live and created multiple peer nodes (P2P replication) to the server. Suddenly today we realized the SAN is of lesser space we may have one or two days before the transaction log to fill up. I will have to move the log to another drive. Is there any way I could do it without having a downtime to the DB. Downtime is not really an option.

Any help with this matter is really appreciated?

Thank you very much.
Post #1370788
Posted Wednesday, October 10, 2012 4:24 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 18, 2014 3:04 AM
Points: 1,331, Visits: 15,269
You can add another log file to the new drive and set the old log to not grow.

You can then
DBCC SHRINKFILE statement and specify the EMPTYFILE clause


Then remove the old log file from the other drive.

http://msdn.microsoft.com/en-us/library/ms191433(v=sql.90).aspx
Post #1370820
Posted Wednesday, October 10, 2012 4:26 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 18, 2014 3:04 AM
Points: 1,331, Visits: 15,269
I'd do each step in order as a precaution.

Are you managing your transaction log with regular backups? If so, why do you say the log file is going to continue growing?
Post #1370821
Posted Thursday, October 11, 2012 1:24 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 7:37 PM
Points: 1,293, Visits: 1,140
MysteryJimbo (10/10/2012)
I'd do each step in order as a precaution.

Are you managing your transaction log with regular backups? If so, why do you say the log file is going to continue growing?


Thank you very much for your suggestion. I tried in the dev environment.

This the message I get.

Msg 5020, Level 16, State 1, Line 3
The primary data or log file cannot be removed from a database

It seems like the primary file group cannot be dropped. Any suggestions.
Post #1371243
Posted Thursday, October 11, 2012 1:36 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 18, 2014 3:04 AM
Points: 1,331, Visits: 15,269
baabhu (10/11/2012)
MysteryJimbo (10/10/2012)
I'd do each step in order as a precaution.

Are you managing your transaction log with regular backups? If so, why do you say the log file is going to continue growing?


Thank you very much for your suggestion. I tried in the dev environment.

This the message I get.

Msg 5020, Level 16, State 1, Line 3
The primary data or log file cannot be removed from a database

It seems like the primary file group cannot be dropped. Any suggestions.


To be honest I've never tried with a primary log file before, only additional. Adding an additional log file will buy you time until you can get an outage

You never answered my question though as this shouldnt be needed.

MysteryJimbo (10/10/2012)
Are you managing your transaction log with regular backups? If so, why do you say the log file is going to continue growing?
Post #1371246
Posted Thursday, October 11, 2012 2:19 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:21 AM
Points: 6,643, Visits: 14,230
That is correct, you cannot drop the first created (primary) data or log file . If you want to move this you will incur some minimal downtime.

Create your alter database ... modify file statement and run it. Then perform the following


  • take the database offline.

  • Copy the log file to the new location

  • once copied bring the database back online

  • Once the database is online delete the old file



-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1371272
Posted Thursday, October 11, 2012 7:26 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 7:37 PM
Points: 1,293, Visits: 1,140
MysteryJimbo (10/11/2012)
baabhu (10/11/2012)
MysteryJimbo (10/10/2012)
I'd do each step in order as a precaution.

Are you managing your transaction log with regular backups? If so, why do you say the log file is going to continue growing?


Thank you very much for your suggestion. I tried in the dev environment.

This the message I get.

Msg 5020, Level 16, State 1, Line 3
The primary data or log file cannot be removed from a database

It seems like the primary file group cannot be dropped. Any suggestions.


To be honest I've never tried with a primary log file before, only additional. Adding an additional log file will buy you time until you can get an outage

You never answered my question though as this shouldnt be needed.

MysteryJimbo (10/10/2012)
Are you managing your transaction log with regular backups? If so, why do you say the log file is going to continue growing?


Sorry I missed this part when I replied your solution.

Yes we are managing the transaction log with regular backup. Though the transaction log will fill up this weekend when we reindex the database.
Post #1371853
Posted Thursday, October 11, 2012 7:29 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 7:37 PM
Points: 1,293, Visits: 1,140
Perry Whittle (10/11/2012)
That is correct, you cannot drop the first created (primary) data or log file . If you want to move this you will incur some minimal downtime.

Create your alter database ... modify file statement and run it. Then perform the following


  • take the database offline.

  • Copy the log file to the new location

  • once copied bring the database back online

  • Once the database is online delete the old file



Thank you Perry for your suggestion. As MysteryJimbo suggested We have ordered additional disc and created additional transaction log to mitigate the risk of running out of space in transaction log.
Post #1371854
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse