August 27, 2011 at 2:32 pm
Dear all,
Herewith i want to know that how do we delete log file in sql server 2008 .if we have 2 log file .
so,as i also want to know that why there are 2 log file for 1 db.
as i have already tried to delete it but was not success full because of 2 log file.
kindly explain me why it is happening.
August 28, 2011 at 12:17 pm
what is the recovery model of the database for which you want to remove the extra file?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 28, 2011 at 1:36 pm
http://msdn.microsoft.com/en-us/library/ms175574.aspx
The log file you want to delete cannot have active VLFs and it must be empty. So if you are in full/bulk logged mode, you'll need to backup the log, then shrink the one you want to delete. There's a link at the top of the one above to help you. If the wrong log file is in use, you'll need to add enough transaction data to move to the other one, then run a backup.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
August 28, 2011 at 1:43 pm
Ivan Mohapatra (8/27/2011)
so,as i also want to know that why there are 2 log file for 1 db.
Short answer: because someone added a second file. It's not necessary, it's not even useful to have 2 log files.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 28, 2011 at 10:42 pm
I have full recovery model
.
August 28, 2011 at 10:44 pm
hi gila,
how to remove 1 log file dear
August 29, 2011 at 4:19 am
Steve already told you how to remove it.
Steve Jones - SSC Editor (8/28/2011)
http://msdn.microsoft.com/en-us/library/ms175574.aspxThe log file you want to delete cannot have active VLFs and it must be empty. So if you are in full/bulk logged mode, you'll need to backup the log, then shrink the one you want to delete. There's a link at the top of the one above to help you. If the wrong log file is in use, you'll need to add enough transaction data to move to the other one, then run a backup.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 29, 2011 at 4:24 am
dear will it put any effect in my Data & MDF . if i remove 1 log file from 2 log file
August 29, 2011 at 4:32 am
Ivan Mohapatra (8/28/2011)
how to remove 1 log file dear
p.s. That term of address is becoming offensive. I am not your 'dear'
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 29, 2011 at 4:36 am
well i am sorry .Gila plz suggest
August 29, 2011 at 4:45 am
Hope this is not production environment where you are trying to delete 2nd log file.
On following the steps given by Steve (backup the log, then shrink the one you want to delete) deleting 2nd log file will not impact your data file. But, to be on safer side backup your database before you act on it.
- SAMJI
If you marry one they will fight with you, If you marry 2 they will fight for you 🙂
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply