SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


move transaction log to a different drive.


move transaction log to a different drive.

Author
Message
baabhu
baabhu
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1669 Visits: 1218
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. Crying

Any help with this matter is really appreciated?

Thank you very much.
MysteryJimbo
MysteryJimbo
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2211 Visits: 15344
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
MysteryJimbo
MysteryJimbo
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2211 Visits: 15344
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?
baabhu
baabhu
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1669 Visits: 1218
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.
MysteryJimbo
MysteryJimbo
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2211 Visits: 15344
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?

Perry Whittle
Perry Whittle
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19834 Visits: 17242
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" ;-)
baabhu
baabhu
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1669 Visits: 1218
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.
baabhu
baabhu
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1669 Visits: 1218
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search