2 Transaction Log Files
-
I was asked to help out another group dealing with their database. This database is a 3rd party SQL Server - where it's an off the shelf application and the database was created by them.
The problem was that there was no space on the drive where the transaction log file resides. So I went an did a complete backup and a backup of the transaction log. When I went into the database properties, I see 2 Transaction Logs listed and it was that way when I went into the database at first. I then looked at the two files and one of them shrunk but the other one stayed the same size. I also looked at when they were last modified, thinking that one of them is being used and the other one was an old transaction log. They both were changed at the same time.
My questions are: Why does this database have 2 transaction log files, as I thought a database can only have 1 transaction log file that gets shrunk when doing a complete backup or a backup of the transaction log - the data moves over into the database file and clears up the transaction log file?
I'm perplexed with this issue as if one of them is being used and the other isn't - what would happen if I were to just remove the one that wasn't changed and run the database. Would it corrupt the database?
If anyone could give me any insight or direction on how to proceed with this issue, that would be great.
-
You can have multiple transaction log files per database.
Backing up the database, by itself, won't usually shrink the log files. You have to do the backup, then issue a shrink command.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
Every DB can have only 1 trans log, though that log can be split into as many files as you like. This is to allow you to stripe them for speed as well as place them on different drives as space becomes an issue. You don't have to do anything with this 2nd file. It's fine just like it is. You're welcome to move them both to the same drive or keep them separated. Don't worry, everything's fine.
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
-
crever (8/8/2008)
This is to allow you to stripe them for speed as well as place them on different drives as space becomes an issue.Multiple log files don't allow striping. It's a circular log file. SQL will use one, then the other, then go back to the beginning of the first.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
You're entirely right. I misspoke.
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply