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?

  • Is there a transaction that is running forever ? Use this query to identify long running transactions. social.msdn.microsoft.com/Forums/sqlserver/en-US/… – Stijn Wynants Apr 16 '15 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 '15 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) – mayooran Apr 16 '15 at 7:37
  • Change autogrowth from 10% to something in MB. 10 % autogrowth really causes unnecessary growth. – Shanky Apr 16 '15 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? – mayooran Apr 17 '15 at 2:53
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.

  • 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? – mayooran Apr 16 '15 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 '15 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 '15 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 '15 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. – mayooran Apr 17 '15 at 3:04

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.