2

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

1
  • 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. Oct 19, 2010 at 14:50

3 Answers 3

4

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!!!

1
  • John thank for you quick reply, i tried attach detach mehtod it throws err - log file is already in use ?
    – Dhiva
    Oct 19, 2010 at 15:06
3

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.

2
  • +1 - You could also have issues with tempdb depending on the nature of the transaction you are running too.
    – JNK
    Oct 19, 2010 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, 2010 at 15:09
1

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.

You must log in to answer this question.