0

The database trans log is in simple recovery mode, but after bulk insert, it has reach the max log size, any advice on how to proceed, I tried to shrink it and it will not shrink. Can I create another trans log file?

2
  • 1
    You might want to check why your log isn't clearing. Unless you're inserting a huge amount of data in a single transaction, it shouldn't get that large. Oct 8 at 14:55
  • 1
    2TB is stupid big for a transaction log, are you sure there isn't an open transaction or something? Oct 8 at 15:34
1

Few have already answered and should guide you. Just adding an answer based on my experience. We have a database approx. 8 TB in size and we allocated 1.5 TB log file ldf, based on application needs and how heavy it perform write operations. Though that does not mean it uses all of that size.

However we ran into a situation , couple of times, where certain process did heavy inserts and then went into sleeping state with open transactions while other process writing and getting blocked. This just bloated ldf file for that database and shrink could not be done.

As it was emergency our L1 team did not looked into blocking scenario and to mitigate the issue, under time constraint, went ahead and added 1 TB log file. Well, yes it mitigated the issue time being by adding additional log file but later we ran into weird performance issues.

Also be aware once you need to remove that additional log file, which was suggested to us by MS engineer as it was creating an issue for us, we had some tough time removing it. So keep in mind if later you want to remove additional log file added , since it adds no benefits.

As said in comments and answer, try to be pro-active in finding why its happening. Adding a good link which i found helpful Large transaction log file

0

Yes you can add a 2nd (etc) transaction log file (normally temporarily). The other comments are helpful but don't directly answer your question.

0

I Personally never seen the requirement of 2TB Log file even the database size more than 2.5TB. I think you better look to find what stopping log file to reuse the allocated space, instead adding additional log file, although it is technically possible but not recommended (further reading link1, link2, and Brent..).

With following simple-query you can identify the cause that stops log file to reuse, and take appropriate action.

select name, log_reuse_wait_desc from sys.databases;

If the problem repeats, you may want to consider using the BULK-LOGGED Recovery Model with proper backup schedule, and process the data when there is no regular workload on the Server and Database.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.