Log file and data file growth

Question

Log file and data file growth

Tac11

SSCertifiable

Points: 6976
More actions
January 3, 2019 at 11:56 am

#378926

Hi Gurus,
Is there anyway to query to see if log or/and data file growing NOT the auto growth (I can see this thru GUI report). Another words to see growth BEFORE initial size expands. For example, my log file size is 100GB but 99% free. I like to see if the log file size occupies 50 or 60% of space, so like to query what caused the 50-60% growth within the free space?
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

LearningDBA SSCertifiable Points: 6593 More actions · Answer 1

Do you take log backups? The reason why I am asking is because if you do then you just have to find out the pattern. I mean look at the log backups you are taking, if one of the log file backup is bigger than the other log backups then you will know that something ran between that time which caused the log file to grow. Then find out if there are any jobs running which are modifying tables. If there is a pattern then you can run trace which can help you capture all queries or use extended events. Does it make sense?

"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]

Tac11 SSCertifiable Points: 6976 More actions · Answer 2

Thanks Syed for taking time to reply my question. It does make sense but I am looking for some kind of script to show.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

Tac11 - Thursday, January 3, 2019 11:56 AM
Hi Gurus,
Is there anyway to query to see if log or/and data file growing NOT the auto growth (I can see this thru GUI report). Another words to see growth BEFORE initial size expands. For example, my log file size is 100GB but 99% free. I like to see if the log file size occupies 50 or 60% of space, so like to query what caused the 50-60% growth within the free space?
Thanks

The easiest way to do this is to first shrink the log file down to a reasonable size... say 8GB or so. Then, setup an SQL Server Alert to detect when it's growing and have a bit of code to capture what's running and the code in a table so you don't have to watch the system for 24/7 to find out what's causing the log file growth.

If you're doing index maintenance (and you should be), there's a high probability that's what's causing the growth to 100GB. You can fix that by doing two things....

1. Stop using REORGANIZE. It's fully logged no matter which Recovery Model you're using and it's not as sparing on resource usage as all the documentation in the world would suggest. On a test box, I did a REORGANIZE on a 146GB Clustered Index that was only 12% logically fragmented. It caused my 20GB log file to explode to 227GB and you can imagine the effect that it had on backups. It also took one hour and 21 minutes to complete.

2. Do Bulk-Logged REBUILDS, instead. After doing a restore (not part of the suggestion... just setting up the test here) of the database from the same backup file I had originally done a restore from for the first test (in other words, identical conditions for both tests), I did a REBUILD in the Bulk-Logged Recovery Model. The REBUILD was minimally logged, only took 12 minutes to accomplish, cause the 20GB log file to only grow to 37GB (instead of 227!!!), and left the Clustered Index with less fragmentation than the REORGANIZE did.

What about the smaller indexes? Can you use REORGANIZE there? Sure... if you enjoy "Death by a Thousand Cuts". 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Tac11 SSCertifiable Points: 6976 More actions · Answer 4

Tac11

SSCertifiable

Points: 6976

January 5, 2019 at 3:54 pm

#2018078

Thanks Jeff for the info. Appreciate it!!

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

NP. Thanks for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sal Young Mr or Mrs. 500 Points: 566 More actions · Answer 6

Here is what I use to see data & log file growth

/* ------------------------------------------------------------------------------------------------- NAME: DB_RecentFileGrowth.sql MODIFIED BY: Sal Young EMAIL: saleyoun@yahoo.com DESCRIPTION: Displays information about database files growth events ------------------------------------------------------------------------------------------------- -- CHANGE HISTORY: -- TR/PROJ# DATE MODIFIED DESCRIPTION ------------------------------------------------------------------------------------------------- -- F000000 02.12.2012 SYoung Initial creation. ------------------------------------------------------------------------------------------------- DISCLAIMER: The AUTHOR ASSUMES NO RESPONSIBILITY FOR ANYTHING, including the destruction of personal property, creating singularities, making deep fried chicken, causing your toilet to explode, making your animals spin around like mad, causing hair loss, killing your buzz or ANYTHING else that can be thought up. ------------------------------------------------------------------------------------------------- */

IF(SELECT CONVERT(int,value_in_use) FROM sys.configurations WHERE [name] = 'default trace enabled' ) = 1

BEGIN

DECLARE @curr_tracefilename varchar(500) , @base_tracefilename varchar(500) , @indx int;

SELECT @curr_tracefilename = [path] FROM sys.traces where is_default = 1 ;

SET @curr_tracefilename = REVERSE(@curr_tracefilename);

SELECT @indx = PATINDEX('%\%', @curr_tracefilename) ;

SET @curr_tracefilename = REVERSE(@curr_tracefilename) ; SET @base_tracefilename = LEFT( @curr_tracefilename,LEN(@curr_tracefilename) - @indx) + '\log.trc' ;

SELECT (DENSE_RANK() OVER(ORDER BY StartTime DESC))%2 AS [l1] , CONVERT(int, EventClass) [EventClass] , DatabaseName , [Filename] , [Duration] , StartTime , EndTime , (IntegerData*8.0/1024) [ChangeInSize] FROM ::fn_trace_gettable( @base_tracefilename, default ) WHERE EventClass >= 92 AND EventClass <= 95 ORDER BY StartTime DESC;

END GO

[font="Verdana"]Sal Young[/font]
[font="Verdana"]MCITP Database Administrator[/font]

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

Sal Young - Thursday, January 10, 2019 4:04 PM
Here is what I use to see data & log file growth
/* -------------------------------------------------------------------------------------------------    NAME: DB_RecentFileGrowth.sql MODIFIED BY: Sal Young    EMAIL: saleyoun@yahoo.com DESCRIPTION: Displays information about database files growth events ------------------------------------------------------------------------------------------------- -- CHANGE HISTORY: -- TR/PROJ# DATE   MODIFIED  DESCRIPTION ------------------------------------------------------------------------------------------------- -- F000000 02.12.2012 SYoung   Initial creation. ------------------------------------------------------------------------------------------------- DISCLAIMER: The AUTHOR ASSUMES NO RESPONSIBILITY FOR ANYTHING, including the destruction of             personal property, creating singularities, making deep fried chicken, causing your             toilet to explode, making your animals spin around like mad, causing hair loss,             killing your buzz or ANYTHING else that can be thought up. ------------------------------------------------------------------------------------------------- */
IF(SELECT CONVERT(int,value_in_use) FROM sys.configurations WHERE [name] = 'default trace enabled' ) = 1
BEGIN
DECLARE @curr_tracefilename varchar(500)     , @base_tracefilename varchar(500)     , @indx int;
SELECT @curr_tracefilename = [path]   FROM sys.traces where is_default = 1 ;
   SET @curr_tracefilename = REVERSE(@curr_tracefilename);
SELECT @indx = PATINDEX('%\%', @curr_tracefilename) ;
   SET @curr_tracefilename = REVERSE(@curr_tracefilename) ;    SET @base_tracefilename = LEFT( @curr_tracefilename,LEN(@curr_tracefilename) - @indx) + '\log.trc' ;
SELECT (DENSE_RANK() OVER(ORDER BY StartTime DESC))%2 AS [l1]    , CONVERT(int, EventClass) [EventClass]    , DatabaseName    , [Filename]    , [Duration]    , StartTime    , EndTime    , (IntegerData*8.0/1024) [ChangeInSize]   FROM ::fn_trace_gettable( @base_tracefilename, default ) WHERE EventClass >= 92    AND EventClass <= 95 ORDER BY StartTime DESC;
END GO

Just a word of caution... the Default Trace can cycle very quickly and you might miss a whole lot of the growth notations by relying on it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)