| Author |
Topic |
|
Wrangler
Starting Member
35 Posts |
 Posted - 2011-10-31 : 12:45:27
|
| GreetingsWe have a server with the log file and data file located on the same drive (data drive). The database can not be taken offline without significant hardship. I'm wondering if it's possible to create a second log file on the other drive (log drive) then delete the one on the data drive. : ) Sounds good to me. Any issues?Thanks,Wrangler |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-10-31 : 14:20:57
|
| You cannot remove the primary log file - so, no this will not work.What you need to do is move the log file, so you need a downtime. The steps are quite simple:1) Alter Database {your database} Modify File (Filename = {new location}); -- look this up in BOL for exact syntax2) Take Database Offline - Alter Database {your database} Set Offline With rollback immediate; -- again, verify syntax3) Copy (don't move) log file to new location4) Bring database online - Alter Database (your database) Set Online;5) Once database is online - and you have validated/tested everything is working correctly, then you can delete the old log file on the old drive.Make sure you validate and verify the syntax and confirm the new file location is correct before taking the database offline.Jeff |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-10-31 : 15:19:12
|
I haven't tried it, but can you add a Log File (on different drive) and then after the next log backup, or two, shrink the original log file so that it is using little / negligible disks space - and then actually remove it at the next scheduled downtime opportunity?Presumably:3) Copy (don't move) log file to new location could take a considerable amount of time if the file is large |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-11-01 : 14:32:33
|
quote:
Originally posted by Wrangler
GreetingsWe have a server with the log file and data file located on the same drive (data drive). The database can not be taken offline without significant hardship. I'm wondering if it's possible to create a second log file on the other drive (log drive) then delete the one on the data drive. : ) Sounds good to me. Any issues?Thanks,Wrangler
Did you try detaching the DB and then attaching to the new location ?PBUH |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-11-01 : 14:49:50
|
| Kristen, you cannot remove the primary log file. No other option other than moving it is available.And yes, if the log file is large - it could take a long time to move it. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-11-01 : 14:52:16
|
| Thanks, but can you add another (second) log file on the new drive and shrink the primary so it plays no "significant" part from that time onwards? and then physically remove it at a suitable downtime in the future?Sorry, you may have been answering that question, but I wasn't sure, hence asking for clarification. |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-11-01 : 19:26:39
|
| No, you cannot remove the primary log file. What you could do is shrink it down to minimal and move it at a later downtime. You still cannot remove that file. |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-11-02 : 05:02:24
|
| Just not sure why cant the OP simply detach the database move the ldf file to new location and then attach it pointing to the new log location.PBUH |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-11-02 : 05:35:20
|
quote:
Originally posted by Sachin.Nand
Just not sure why cant the OP simply detach the database move the ldf file to new location and then attach it pointing to the new log location.
I was assuming that the file is "large" and thus the downtime to "move" it was too long. |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-11-02 : 09:58:13
|
quote:
Originally posted by Kristen
quote:
Originally posted by Sachin.Nand
Just not sure why cant the OP simply detach the database move the ldf file to new location and then attach it pointing to the new log location.
I was assuming that the file is "large" and thus the downtime to "move" it was too long.
Nowhere did the OP mention that file was to large.Rather he had mentioned this quote:
...The database can not be taken offline without significant hardship.
Also if it is to large on our internal network a 10 GB file easily gets transferred in about 45-60 secs and if we zip it takes much more less.PBUH |
|
|
|
vikki.seth
Yak Posting Veteran
66 Posts |
Posted - 2011-11-02 : 10:28:43
|
| He can try shrinking the log file. If there is not enough space for this activity, add a log file to another drive, then shrink the primary log file. move log file using alter database. Take db offline. Move the physical log file to another drive and bring db online. If need be, drop the newly created log file. |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-11-02 : 11:57:38
|
quote:
Originally posted by vikki.seth
...Take db offline. Move the physical log file to another drive and bring db online. If need be, drop the newly created log file.
Isn't that the exact problem OP is facingquote:
...The database can not be taken offline without significant hardship.
That's the reason I advised detaching as there is difference between detaching and taking a DB offline.PBUH |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-11-02 : 12:18:28
|
quote:
Originally posted by Sachin.Nand
quote:
Originally posted by vikki.seth
...Take db offline. Move the physical log file to another drive and bring db online. If need be, drop the newly created log file.
Isn't that the exact problem OP is facing
I think that if a new, additional, LOG file is created on the new drive, and then the original log file is shrunk to very small size, then the downtime of moving the original, now very small, log file will be short enough to fit into a maintenance window (assuming that, previously, the log file was so big that moving it would have taken too long)But I am only guessing the scenario that the O/P is faced with ... |
|
|
|
|
move the .ldf file