Thursday, November 22, 2012 9:20 AM
I have an User DB called AG, in which i have 2 Data Files and 2 Ldf files,
now my point is the LDF files are created 1 by the time of creating the Db an year ago, and after 6 months i created another LDF file , so totally its now 2 LDF files.
Surprisingly , though i created the 2nd LDF file for this DB , but i can find the trans are loading in 1st ldf file , alone, i can find this by the file size, but usually which ever the file created last ll be in use by SQL righgt?
How should i solve this problem ? i want the 2nd ldf file to be used , not the 1st ldf file thats been created at the time of DB creation
please suggest me a meathod
Thursday, November 22, 2012 10:01 AM
but usually which ever the file created last ll be in use by SQL righgt?
--No I don't think so.
Having multiple log files is not suggested. Log writing is sequential so multiple ldf's wouldn't help. SQL Sever would start writing to second LDF only when the first one is completely full. As for why the data is not getting in the second log file here's what I think
1. you have two log file LDF1 and LDF2.
2. SQL Server starts writing to LDF1.
3. A transaction log backup (or other operation which frees up space on transaction log) happens and frees up space on LDF1
4. SQL Sever starts writing to LDF 1 again never actually needing to go to LDF2.
Please mark posts as answer or helpful when they are.
Thursday, November 22, 2012 10:03 AM
There's really no merit in having 2 transaction logs. It doesn't help performance at all and the 2nd will only be used if the first one fills up. You should get rid of the 2nd redundant log file. If you want to relocate the transaction log, you should do something like detach/attach
- Proposed As Answer by Mohit K. GuptaMicrosoft Employee Thursday, November 22, 2012 2:57 PM