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?

Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600
AnOldSoul
  • 471
  • 1
  • 10
  • 22
  • Is there a transaction that is running forever ? Use this query to identify long running transactions. https://social.msdn.microsoft.com/Forums/sqlserver/en-US/2865d2d4-9d75-4d0e-86f6-08495178a71b/query-to-find-long-running-transaction-and-high-memory-consumed-process?forum=transactsql – Stijn Wynants Apr 16 '15 at 06: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 07: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 (Build 9200: ) (Hypervisor) – AnOldSoul Apr 16 '15 at 07:37
  • Change autogrowth from 10% to something in MB. 10 % autogrowth really causes unnecessary growth. – Shanky Apr 16 '15 at 09: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 '15 at 02:53
  • You would have to look deep. Use sys.dm_exec_requests to get sql_handle for the SPID or session_id and put that sql_handle in select * from sys.dm_exec_sql_text you would find the query. – Shanky Apr 17 '15 at 05:04
  • @Shanky If there is a transaction block in a SP im using, it goes to the log file if its uncommitted right? – AnOldSoul Apr 17 '15 at 05:27
  • Yes, for practical purposes every thing is logged in transaction log irrespective of whether it is committed or uncommitted. – Shanky Apr 17 '15 at 07:22

1 Answers1

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.

Rob Farley
  • 16,199
  • 2
  • 38
  • 61
  • 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 '15 at 07: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 09: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. – AnOldSoul Apr 17 '15 at 03:04
  • @DenisT 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 '15 at 03:06
  • exec sp_who2 which will show you what is running. or select * from sys.dm_exec_requests - Or get sp_whoisactive http://sqlblog.com/blogs/adam_machanic/archive/2012/03/22/released-who-is-active-v11-11.aspx – Spörri Apr 17 '15 at 08:28
  • Monitor it over time. Even long-running transactions will finish and occur again in the future. Only shrink a log to immediately grow it back. Don't try to keep it small. – Rob Farley Apr 17 '15 at 11:26