Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

My transaction log file is full, i have 5 log files , 2 in D: drive & 3 in E: drive. all the log file is full, each log file is 10GB in size.

I need to delete all 5 transaction log file and need to have one fresh truncation log file

i have tried method, 1. converted from Full to simple and shrik files (but not great effect , it does not reduce size.

Any assist for this, Thnk in advance

share|improve this question

migrated from stackoverflow.com Oct 19 '10 at 23:15

This question came from our site for professional and enthusiast programmers.

    
With content relevance preservation in mind, I have voted for this question to be migrated to ServerFault in light of the content being more focused toward server administration rather than programming and I encourage others to do the same. –  John Sansom Oct 19 '10 at 14:50

3 Answers 3

This is a very common SQL Server issue and is heavily documented on the internet already. For example in the less than 5 seconds on Google I was able to locate the following article which will assist you with troubleshooting your issue:

Help! My SQL Server Log File is too big!!!

share|improve this answer
    
John thank for you quick reply, i tried attach detach mehtod it throws err - log file is already in use ? –  Dhiva Oct 19 '10 at 15:06

So you have a big transaction that is using a lot of tran space right? What I usually do is do batch commits with the DML causing the issue. That is after so many records force a commit. You can do this in a while loop.

Or you can temporarily increase your tran log to the needed size run the huge transaction and then recreate your tran log at the normal size you had it at.

share|improve this answer
    
+1 - You could also have issues with tempdb depending on the nature of the transaction you are running too. –  JNK Oct 19 '10 at 13:32
    
i cant increae my Tlog, bcoz server is full no space in it that's the reason i dont want any tlog and want to create new T-log - its dev server so i dont need old transaction –  Dhiva Oct 19 '10 at 15:09

Check out this ServerFault question on the same issue.

The upshot is that you need to either:

  1. Take regular log backups if you really need to be in full recovery mode
  2. Switch to simple mode if you don't
  3. (less common issue) Perform huge operations in batches instead of all at once

You normally wouldn't need more than one log file (or perhaps one log file per drive).

After switching to simple mode, use DBCC SHRINKFILE with the EMPTYFILE parameter to shrink the extra log files (might take multiple attempts if the log files are in use, or you might have to wait for active transactions to finish).

Then use ALTER DATABASE MODIFY FILE to drop the extra log files.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.