October 10, 2012 at 3:30 am
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.
October 10, 2012 at 4:24 am
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
October 10, 2012 at 4:26 am
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?
October 11, 2012 at 1:24 am
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.
October 11, 2012 at 1:36 am
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?
October 11, 2012 at 2:19 am
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
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 11, 2012 at 7:26 pm
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.
October 11, 2012 at 7:29 pm
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.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply