March 20, 2017 at 12:33 pm
How to find what cause the log file growth? This happened during index maintenance.How to find the exact cause?
March 20, 2017 at 2:30 pm
Index maintenance such as rebuild or reorganization will both contribute to transaction log file growth:
https://blogs.msdn.microsoft.com/timchapman/2012/09/28/index-rebuild-vs-reorganize-the-transaction-log-edition/
How are you performing your index maintenance? Hopefully something like the free scripts from one of these places:
https://ola.hallengren.com/
http://minionware.net/reindex/
If you're using SQL Server's Maintenance Plan feature, you'll have a lot less control over what's going on and have less visibility as to what has/is happening during your index maintenance.
March 22, 2017 at 3:16 am
This was removed by the editor as SPAM
March 22, 2017 at 10:30 am
If the database is in Full Recovery mode and log backups not scheduled or log backups happening in very less frequency on database which has very high number of transactions.
Thank You.
Regards,
Raghavender Chavva
March 25, 2017 at 10:28 pm
Admingod - Monday, March 20, 2017 12:33 PMHow to find what cause the log file growth? This happened during index maintenance.How to find the exact cause?
If you did a reorg on a large index(no matter the Recovery Model) or a rebuild on a large index in the FULL Recovery Model, you already have your answer. If you used REBUILD ALL on a large table with a shedload of indexes, you're made it all worse because all of the indexes, including the clustered index, are all rebuilt within the same transaction, which will be as almost as large as the total reserved space in the transaction log.
As a bit of a sidebar, what performance gains do you think you're going to get from your index rebuild efforts? I haven't done any index maintenance on my main production server since the 17th of Jan, 2016 (more than a year ago) and was astonished to find that performance improved in the first three months before flattening out. It's a 2TB server so I've saved lots and lots of time and log file space. If you try the same thing, don't forget that you must be aggressive in your stats rebuilds.
If you don't want to try it, ask yourself what you're getting for all that index maintenance work and then prove that it's worth it, especially if you're on a SAN. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
March 26, 2017 at 11:21 pm
This was removed by the editor as SPAM
March 27, 2017 at 1:22 am
If you have your files set to Autogrow: Check your default traces, they contain info on automatic file growth !
/*
* read SQL default trace file name
*/
SET nocount ON
declare @wrkTraceFilename nvarchar(500)
-- read trace info
SELECT @wrkTraceFilename = convert(nvarchar(500), [value])
FROM fn_trace_getinfo(1) -- 1 = default trace
where [property] = 2
select *
-- used this to be sure I have ALL available columns ( never mind edition, servicepack, CU, HF, ...)
into #tmpTrace
from fn_trace_gettable(@wrkTraceFilename, default)
;
/* Show Auto Grow events */
Select TE.name as EventName
, T.StartTime
, T.EndTime
, T.Duration /* Length of time (in milliseconds) necessary to extend the file. ! */
, DatabaseName
, FileName
, T.LoginName
, T.HostName
, T.ApplicationName
, T.ServerName
, T.SessionLoginName
from #tmpTrace T
inner join sys.trace_events TE
on TE.trace_event_id = T.EventClass
Where TE.name in ('Data File Auto Grow',
'Data File Auto Shrink',
'Log File Auto Grow',
'Log File Auto Shrink')
order by StartTime desc
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 27, 2017 at 1:36 pm
Thanks for everyone for the replies. The log growth is happened during index maintenance plan. However, trying to find what particular index is causing the log growth. I load the results in the table. I have list of indexes which were rebuild. But not able to find what particular index is culprit for the growth any idea how to find that? Yes, its full recovery model and t log backups happens every 15 mins. Thanks in advance.
March 27, 2017 at 2:14 pm
Are you using replication, mirroring or Availability Groups, then you might also want to run select name, log_reuse_wait_desc from sys.databases.
There might be some additional reasons why your logs grow. Maybe not. Doesn't hurt to check, right.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
March 27, 2017 at 2:43 pm
I see that particular database log_reuse_wait_desc value is XTP_CHECKPOINT. Do you think this might be the problem for the log growth?
March 27, 2017 at 4:12 pm
Admingod - Monday, March 27, 2017 2:43 PMI see that particular database log_reuse_wait_desc value is XTP_CHECKPOINT. Do you think this might be the problem for the log growth?
Maybe, you'll need to see if this is applicable to your situation.
If log space can't be reused, it would grow that log as space is required,and growing, and growing.
First, check if the log file space is empty after a log backup.
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/729cd718-7e7e-493b-b69e-c5b885c3c91b/log-reuse-wait-is-xtpcheckpoint?forum=sqldatabaseengine
https://connect.microsoft.com/SQLServer/feedback/details/1021865/log-not-truncating-due-to-xpt-checkpoint
You'll need to dig a bit deeper.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
March 27, 2017 at 6:04 pm
Admingod - Monday, March 27, 2017 1:36 PMThanks for everyone for the replies. The log growth is happened during index maintenance plan. However, trying to find what particular index is causing the log growth. I load the results in the table. I have list of indexes which were rebuild. But not able to find what particular index is culprit for the growth any idea how to find that? Yes, its full recovery model and t log backups happens every 15 mins. Thanks in advance.
Here is an article I wrote on the topic. This will help flesh out what the exact statement was at the time of the growth. Look toward the end where I have the XE session for it. This was fairly basic in setup and can be customized considerably to meet your needs.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 27, 2017 at 7:32 pm
Admingod - Monday, March 27, 2017 1:36 PMThanks for everyone for the replies. The log growth is happened during index maintenance plan. However, trying to find what particular index is causing the log growth. I load the results in the table. I have list of indexes which were rebuild. But not able to find what particular index is culprit for the growth any idea how to find that? Yes, its full recovery model and t log backups happens every 15 mins. Thanks in advance.
I have to ask, what will you do with the information once you find it? Stop rebuilding it? Probably not. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply