February 5, 2015 at 10:09 am
I received alerts from a couple of the production servers last night stating that the log file is running out of space. So, I took some log backups and shrunk those files. However, I would like to find out what made the log grow like that. After all, I have t-log backups scheduled every 30 minutes.
Is there a way I can find out the reason behind the log growth?
February 5, 2015 at 10:20 am
look at
SELECT name ,
recovery_model_desc ,
log_reuse_wait_desc
FROM sys.databases
this should point you into right direction next time.
However my first thought would be - long running/uncommited transacion that was preventing the log truncation after a log backup.
Other thing (if you say it happened at night) - performing index maintenance
February 5, 2015 at 10:39 am
Tell us how are your log files configured? What is the auto-growth?
You may have lots of virtual log files with small sizes and t-log shrinks don't help you.
This article is very helpful - http://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/
Igor Micev,
My blog: www.igormicev.com
February 5, 2015 at 11:23 am
the log files have an initial size of 512 MB. It grows in 100 MB sized chunk and the auto growth is restricted to 2 GB. Our databases are quite small and the system isn't very busy. We didn't have this issue yet, but I do not want it to happen again.
February 5, 2015 at 11:29 am
Srl832 (2/5/2015)
the log files have an initial size of 512 MB. It grows in 100 MB sized chunk and the auto growth is restricted to 2 GB. Our databases are quite small and the system isn't very busy. We didn't have this issue yet, but I do not want it to happen again.
2GB is not a big size. However you should investigate the process that is pumping the transaction log and take some preventive measures like temporary changing the recovery model to reduce the data being logged by it. Or put some more space for the logs :).
Igor Micev,
My blog: www.igormicev.com
February 5, 2015 at 11:41 am
that's exactly my question. How do I find out which process had a role in making the log bigger?
February 5, 2015 at 11:47 am
Srl832 (2/5/2015)
that's exactly my question. How do I find out which process had a role in making the log bigger?
Try with this query
SELECT creation_time
,last_execution_time
,total_physical_reads
,total_logical_reads
,total_logical_writes
, execution_count
, total_worker_time
, total_elapsed_time
, total_elapsed_time / execution_count avg_elapsed_time
,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY total_elapsed_time / execution_count DESC;
If you have some monitoring tools you can use them as well, there are more ways to find it.
Igor Micev,
My blog: www.igormicev.com
February 5, 2015 at 11:57 am
thank you
February 5, 2015 at 11:16 pm
If you don't mind, could you tell me what are some of the other ways? I would like to learn various different ways if possible.
Thanks
February 6, 2015 at 12:58 am
Srl832 (2/5/2015)
If you don't mind, could you tell me what are some of the other ways? I would like to learn various different ways if possible.Thanks
If you're on sql 2012+ you can run an Extended events session to catch queries; you can filter which ones to catch. It can be run all the time not causing performance issues to the instance.
You can also run a profiler, but be careful because it can be bad for the performances. Run it for short time periods if you decide for it.
Then you can use some other queries (more or less similar to the above) to also collect data into a temporary table, and then query them. This is useful if you often restart the instance but you want to make analysis for longer periods as you keep data in a temp user table.
You can use the perfmon.exe tool of the OS to see diagrams, and you can collect data as well for multiple periods. There are plenty of counters it offers to monitor.
And you can use the various tools offered on the market. They are detailed.
I'm sure there are still other ways.
Igor Micev,
My blog: www.igormicev.com
February 6, 2015 at 2:50 am
This sounds to me like you have a re-indexing job running at night, this will cause the log to grow. If not is there an overnight process that maybe manipulating data on the system when no one is one.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply