April 13, 2010 at 3:47 pm
Hi Folks,
Here is my question, I have database that is in full recovery mode, I have mdf and ldf files on two different drives and Logfile continuously growing.
a. My question here is, can how can we move a ldf file to go to the mdf file and
b. coming to the shrinking of log file I cannot shrink the log file this is going to be a problem as this is the production database. Can I use "Truncate_only" command to do this? and before using this command what I need to do
Do I need to take a full backup and transaction log backup and do dbcc shrink-file command
How can I proceed this problem? considering the scenario(b) what Exactly do I need to do first do I need to first take a full or Transaction log backups respectively
Can any one explain me the scenario.
Thanks&Regards,
Ravi Shankar
April 13, 2010 at 6:29 pm
How frequent are your full backups ?
How frequent are your transaction log backups ?
" ... how can we move a ldf file to go to the mdf file ..."
Do you mean you want to move the log file from one drive, such as C, to another drive, such as D ? If the MDF and LDF are on the same drive, then you may have performance problems because they will be battling for the same drive resources.
To move a file, detach the database, move the file, attach the file pointing to the new location. The database will be unavailable during this process.
April 13, 2010 at 8:35 pm
Yes, my mdf(datafile) is in one drive and ldf file is another drive.
Coming to the full backups they are taken for every 12 hrs and transaction log backup is taken for every 30 min.
Scenario 1: My question was how can move ldf file on one drive to another drive containing mdf file and how this can be done?
Scenario 2: And solution would be, taking a full backup and transaction log backup and then do DBCC shrinkfile command. For doing the "scenario 2" how can I go a head further?
And when to use "Truncate_only"
Thanks & Regards,
Ravi S
April 14, 2010 at 12:35 am
Scenario 1) Why do you want them on the same drive ?
To move a file, detach the database, move the file, attach the database specifying the new file location. The database will be unavailable during this process.
Scenario 2) I never use truncate_only. You can shrink the log file to free up space, but it may grow again if you have lots of transactions. Maybe you should take log backups every 10 or 15 minutes.
There are many threads about transaction logs, with answers much better than mine. Try searching them to see if you get more information.
April 14, 2010 at 3:56 am
Hi,
Can go through this links.
http://support.microsoft.com/kb/224071
http://support.microsoft.com/kb/907511
Thanks.
April 15, 2010 at 6:23 am
Truncate Only is not available in SQL 2008
April 15, 2010 at 7:45 am
RaviShankar1234 (4/13/2010)
Hi Folks,Here is my question, I have database that is in full recovery mode, I have mdf and ldf files on two different drives and Logfile continuously growing.
a. My question here is, can how can we move a ldf file to go to the mdf file and
b. coming to the shrinking of log file I cannot shrink the log file this is going to be a problem as this is the production database. Can I use "Truncate_only" command to do this? and before using this command what I need to do
Do I need to take a full backup and transaction log backup and do dbcc shrink-file command
How can I proceed this problem? considering the scenario(b) what Exactly do I need to do first do I need to first take a full or Transaction log backups respectively
Can any one explain me the scenario.
Thanks&Regards,
Hello,
Before making any comments on your questions, can you tell us about
1. are you using SQL 2000 / 2005 or 2008 ?
2. Why do you want o put your log and data files on same drive ?
3. If its a production DB, why do you want to use truncate_only ?
Here is Paul's article why you should not use it:
Please let us know to help you better 🙂
Thanks,
\\K
Ravi Shankar
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
April 15, 2010 at 7:45 am
RaviShankar1234 (4/13/2010)
Hi Folks,Here is my question, I have database that is in full recovery mode, I have mdf and ldf files on two different drives and Logfile continuously growing.
a. My question here is, can how can we move a ldf file to go to the mdf file and
b. coming to the shrinking of log file I cannot shrink the log file this is going to be a problem as this is the production database. Can I use "Truncate_only" command to do this? and before using this command what I need to do
Do I need to take a full backup and transaction log backup and do dbcc shrink-file command
How can I proceed this problem? considering the scenario(b) what Exactly do I need to do first do I need to first take a full or Transaction log backups respectively
Can any one explain me the scenario.
Thanks&Regards,
Hello,
Before making any comments on your questions, can you tell us about
1. are you using SQL 2000 / 2005 or 2008 ?
2. Why do you want o put your log and data files on same drive ?
3. If its a production DB, why do you want to use truncate_only ?
Here is Paul's article why you should not use it:
Please let us know to help you better 🙂
Thanks,
\\K
Ravi Shankar
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply