2

I noticed that my database log file has reached 160GBs. My database is in simple recovery mode and I used the following query to see if there are any waiting transactions.

SELECT d.name,d.log_reuse_wait,d.log_reuse_wait_desc
FROM sys.databases d

It returned log_reuse_wait 0 and description 'NOTHING'.
Why is my transaction log growing then?

8
  • Is there a transaction that is running forever ? Use this query to identify long running transactions. social.msdn.microsoft.com/Forums/sqlserver/en-US/… Apr 16, 2015 at 6:19
  • What is so called intial size of log file you can see it from SSMS. What is autogrowth, is it in percentage ?. Does DBCC OPENTRAN(DB_name) return anything. What is output of Select @@Version
    – Shanky
    Apr 16, 2015 at 7:00
  • @Shanky the initial size is the default value and autogrowth is enabled and its 10%. DBCC OPENTRAN doesnt return anything. It says no running transactions. @@Version output is Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64) Oct 19 2012 13:38:57 Copyright (c) Microsoft Corporation Enterprise Evaluation Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
    – AnOldSoul
    Apr 16, 2015 at 7:37
  • Change autogrowth from 10% to something in MB. 10 % autogrowth really causes unnecessary growth.
    – Shanky
    Apr 16, 2015 at 9:06
  • @Shanky I have a free space of only 40%. And dbcc opentran gives an output. It shows the name of the transaction as user_transaction. How can I find which stored procedure is doing this?
    – AnOldSoul
    Apr 17, 2015 at 2:53

1 Answer 1

2

Your file is probably large but empty.

In SSMS, right-click on your database and go into Tasks, Shrink Files. DO NOT HIT OK IN THIS DIALOG - but it's the easiest way of seeing how empty your files are. Choose Log in the dropdown, and I imagine you'll see that your log file is actually 99% empty.

You probably had a large process that grew the file to 160GB. You should probably shrink your file and then resize it to 160GB again manually (to reduce fragmentation), and then monitor how full it is using PerfMon counters.

8
  • How can I avoid this from happening? Like do I have to check regularly and shrink it all the time? Are there any ways to avoid this from happening?
    – AnOldSoul
    Apr 16, 2015 at 7:38
  • You can create a SQL Server agent job that checks the log size and shrinks it to a predefined value (to avoid internal fragmentation) something like this: IF ((select size from DATABASE.sys.database_files where type = 1) > X) dbcc shrinkfile (file_id, target_size, truncateonly)
    – Spörri
    Apr 16, 2015 at 9:14
  • 1
    Don't do it!!! You should never shrink automatically... you need to understand why the log file is growing and either fix what makes it grow if unnecessary growth happens or size it appropriately. If you figure out that it needs to be 180GB then size it manually to 180GB... don't automatically shrink it to 160GB from 180GB! It will grow again and it will hurt the performance on your instance!
    – DenisT
    Apr 16, 2015 at 12:41
  • If the log grew, it had a reason. Be happy that it's mostly empty. Shrink it back ONCE and then regrow it immediately to the same size as it is now, because that will reduce the number of VLFs. Then monitor the size and don't shrink it. Definitely do NOT make a job to make it shrink regularly. Any time it autogrows, that's bad.
    – Rob Farley
    Apr 16, 2015 at 12:53
  • @Spörri When I run the dbcc opentran now im getting an output. It says "user_Transaction". How do I find which stored procedure is generating this? Also the free space is shown as 40%. Means there is so much live transaction in the logs. Please advice.
    – AnOldSoul
    Apr 17, 2015 at 3:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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