recent

Titulo

Is Your Transaction Log Full?

This Afternoon, I was contacted by a developer with an error message “The transaction log for database ‘database name’ is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc_column in sys.database [SQL STATE 42000] (Error 9002). The step failed.” The error message is pretty clear as why our SQL Server is throwing the error 9002. For each databases in SQL Server, there is at least one transaction log file and a data file. The log file records all the activities within the database. When the space allocated for a transaction log is full, the database engine cannot write to a log file and the database ceases

What can cause error 9002?
  1. A long running query
  2. Too many queries running at a same time.
  3. The log file isn't truncated or backed up
  4. The space allocated to log file is small
  5. Full Recover model with no Transaction Log backup.
How do to fix the issue?
Like everyone else, I searched online for a solution and I landed on Microsoft  MSDN site. Their site recommends the following:
  1. Backing up the log.
  2. Freeing disk space so that the log can automatically grow.
  3. Moving the log file to a disk drive with sufficient space.
  4. Increasing the size of a log file.
  5. Adding a log file to a different disk.
  6. Completing or killing a long-running transaction.
How is cleaning a disk or moving a log file to a disk drive with sufficient space going to help resolve the issue?  The database transaction log file of our SQL Server is configured to use 9 GB of disk space. The database is set to use Full Recovery Model with a daily full backup and hourly transaction log backup. These backup jobs are running successfully which also means the truncates of log file are happening hourly. Since I had few unused space, I decided to add 6 additional GB and asked the developer to run his job. The problem didn't go away with additional space, it only took little longer to throw an error. Now, someone please explain me, what is the definition of sufficient space? How is the above recommendation going to help us when you don't know the exact size of your log file? There isn't a way to find out how big the log file should be, can you?

How I would tackle this?
We know the problem but we don't know what is causing it. The first step is to find out what is the root cause? Never ever jump into fixing a problem without knowing the root cause. The solution you are going to implement or may not fix your problem. Let's check the space available for log file using the query below.

Run the query below to get the size and space available for a Log and Data file.

SELECT name AS [File Name] , file_id, physical_name AS [Physical Name],
size/128 AS [Total Size in MB],size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0
AS [Available Space In MB]
FROM sys.database_files;

Output:
This will give you the total size of  your log and data file and space available. If the available space is low, that mean you reaching the size limit.

This below query tells us what is causing the error? This is what the error message is asking us to run.

select log_reuse_wait_desc
from sys.databases where name ='DB NAME';

Output:
ACTIVE TRANSACTION ( in our case!)

The above result says the query transaction is causing the log to be filled. This is the only query running on this database while we got this error. Why is my single query taking that much of disk space?  I checked the stored procedure which the job was calling. The first line of the procedures was  "delete from a table". I converted a delete into select statement to check the delete count and guess what what I found? The procedure was trying to delete 5 millions record from a table -No Kidding!!.
Now, you know what is going here, you can not do a massive delete in a single transaction. For every row it deletes, the SQL Server records the database activities into a transaction log file. This is why the log file gets filled up fast. The transactions should be committed after certain interval of delete to avoid the log fill.

How did I fix this?
I changed the DELETE query to use TRUNCATE.  The job ran fine with TRUNCATE and then everyone was happy when the job completed successfully. The truncate deletes all the data without logging it. Therefore, the truncate is much faster. It is very critical to know the difference between delete and truncate and when to use them. To solve any problem, you must find the root cause and then find the solution. This problem solving technique has always worked for me and I recommend this problem solving strategy to anyone.


Interested in working with me? I can be reached at pbaniya04[at]gmail.com for any questions, consulting opportunities or you may drop a line to say HELLO. Thank your again for visiting my blog and looking forward to serving you more.

Have a Database-ious Day!

35 comments

  1. Excellent Article Prabin

    ReplyDelete
  2. awesoma article, I have shared this page to my google plus profile...

    please keep posting..

    ReplyDelete
  3. I agree with him...

    ReplyDelete
  4. Kudos to a great site.

    I think one of the things many DBAs get confused is on truncate vs shrink.To move LSN forward we have two options, truncate or shrink: Truncate: removes unwanted passive entries and makes space in transaction log which can be reused. But truncate doesn’t reduce transaction log file size because it only eliminate entries but that virtual log is still there which can be reused.

    So to actually free up some space, we will need to use DBCC SHRINKFILE command:
    http://stackoverflow.com/questions/7193445/dbcc-shrinkfile-on-log-file-not-reducing-size-even-after-backup-log-to-disk
    http://msdn.microsoft.com/en-us/library/ms189493.aspx

    ReplyDelete
  5. "The database is set to use Simple Recovery Model with a daily full backup and hourly transaction log backup. These backup jobs are running successfully which also means the truncates of log file are happening hourly."
    How do you support your above statement ??
    Can we take a log backup if DB is simple recovery model ?

    ReplyDelete
  6. You can only take a log back up with Full or Bulk Recovery model not with Simple. Thank you for pointing out, it is a typo and I will fix that ASAP.

    ReplyDelete
  7. The article looks magnificent, but it would be beneficial if you can share more about the suchlike subjects in the future. Keep posting. compact tractor attachments

    ReplyDelete
  8. Thanks for the blog filled with so many information. Stopping by your blog helped me to get what I was looking for. Now my task has become as easy as ABC. vervoer op maat rotterdam

    ReplyDelete
  9. An fascinating discussion is value comment. I think that it is best to write extra on this matter, it won’t be a taboo topic however generally people are not enough to talk on such topics. To the next. Cheers 토토사이트

    ReplyDelete
  10. Wow, cool post. I’d like to write like this too – taking time and real hard work to make a great article… but I put things off too much and never seem to get started. Thanks though. 안전놀이터

    ReplyDelete
  11. Hi there! Nice stuff, do keep me posted when you post again something like this! 먹튀검증커뮤니티

    ReplyDelete
  12. Nice to be visiting your blog once more, it has been months for me. Well this article that ive been waited for therefore long. i want this article to finish my assignment within the faculty, and it has same topic together with your article. Thanks, nice share. buy youtube views high retention

    ReplyDelete
  13. Hi there! Nice stuff, do keep me posted when you post again something like this! pii-email

    ReplyDelete
  14. i never know the use of adobe shadow until i saw this post. thank you for this! this is very helpful. 88카

    ReplyDelete
  15. I’ve read some good stuff here. Definitely worth bookmarking for revisiting. I surprise how much effort you put to create such a great informative website. สล็อต

    ReplyDelete
  16. Merely a smiling visitant here to share the love (:, btw outstanding style. shuttlesky.in

    ReplyDelete
  17. Cool stuff you have got and you keep update all of us. buy twitch followers

    ReplyDelete
  18. Wow, cool post. I’d like to write like this too – taking time and real hard work to make a great article… but I put things off too much and never seem to get started. Thanks though. ivanka trump twitter

    ReplyDelete
  19. Really I enjoy your site with effective and useful information. It is included very nice post with a lot of our resources.thanks for share. i enjoy this post. 토토사이트

    ReplyDelete
  20. Liposuction should never be considered an alternative to a healthy lifestyle, it is one of the steps that can get you to a better version of yourself through fat reduction. read review

    ReplyDelete
  21. I can’t imagine focusing long enough to research; much less write this kind of article. You’ve outdone yourself with this material. This is great content. 바카라사이트
    (mm)

    ReplyDelete
  22. Pretty good post. I have just stumbled upon your blog and enjoyed reading your blog posts very much. I am looking for new posts to get more precious info. Big thanks for the useful info. 대전마사지

    ReplyDelete
  23. Impressive web site, Distinguished feedback that I can tackle. Im moving forward and may apply to my current job as a pet sitter, which is very enjoyable, but I need to additional expand. Regards. https://www.abercrombieand-fitch.com.co/

    ReplyDelete
  24. An interesting dialogue is price comment. I feel that it is best to write more on this matter, it may not be a taboo topic however usually individuals are not enough to talk on such topics. To the next. Cheers. www.digitalanalog.in

    ReplyDelete
  25. What is an outstanding post! “I’ll be back” (to read more of your content). Thanks for the nudge! 먹튀검증

    ReplyDelete
  26. I like your post. It is good to see you verbalize from the heart and clarity on this important subject can be easily observed... 토토사이트

    ReplyDelete
  27. Yes, I am entirely agreed with this article, and I just want say that this article is very helpful and enlightening. I also have some precious piece of concerned info !!!!!!Thanks. start a credit card processing business

    ReplyDelete
  28. Wow, excellent post. I'd like to draft like this too - taking time and real hard work to make a great article. This post has encouraged me to write some posts that I am going to write soon. Blissy pillow case reviews

    ReplyDelete
  29. Your articles are inventive. I am looking forward to reading the plethora of articles that you have linked here. Thumbs up! North American Bancard Agent Program

    ReplyDelete
  30. I’ve read some good stuff here. Definitely worth bookmarking for revisiting. I surprise how much effort you put to create such a great informative website. new internet device

    ReplyDelete
  31. We are really grateful for your blog post. You will find a lot of approaches after visiting your post. I was exactly searching for. Thanks for such post and please keep it up. Great work. Dentitox pro review

    ReplyDelete
  32. I think this is an informative post and it is very useful and knowledgeable. therefore, I would like to thank you for the efforts you have made in writing this article. winnerslot

    ReplyDelete
  33. It proved to be Very helpful to me and I am sure to all the commentators here! tadalafil,

    ReplyDelete
  34. It proved to be Very helpful to me and I am sure to all the commentators here! 토토사이트

    ReplyDelete
  35. This is the type of information I’ve long been trying to find. Thank you for writing this information. 먹튀검증

    ReplyDelete

Powered by Blogger.