Sudden growth in mdf file

Question

Sudden growth in mdf file

kapfundestanley

SSCertifiable

Points: 5637
More actions
March 3, 2020 at 11:40 am

#3730498

Please assist I have faced a sharp growth in the size of my primary data file on SQL Server 2016.How can I identify the cause of the issue.
“When I hear somebody sigh, ‘Life is hard,’ I am always tempted to ask, ‘Compared to what?’” - Sydney Harris

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

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

Jo Pattyn SSC-Dedicated Points: 32068 More actions · Answer 1

You can trace filegrowth with extended events https://www.sqlservercentral.com/blogs/finding-file-growths-with-extended-events

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 2

Extended events will allow you to capture future file growths, but if you need to investigate file growths that have already happened, you can try to look at the default trace to see what caused the file growth:

SELECT td.DatabaseName, td.Filename, te.name AS Event, (IntegerData*8)/1024 AS Change_MB, td.StartTime, (Duration/1000) AS Duration_sec,
    td.LoginName, td.HostName, td.ApplicationName, td.spid, td.ClientProcessID, td.IsSystem, td.SqlHandle, td.TextData
  FROM sys.traces t
    CROSS APPLY ::fn_trace_gettable(t.path, default) td
    INNER JOIN sys.trace_events te ON td.EventClass = te.trace_event_id
  WHERE t.is_default = 1
    AND td.EventClass IN (92,93)
  ORDER BY td.StartTime DESC;

note, EventClass 92 is "Data File Auto Grow"

and EventClass 93 is "Log File Auto Grow"

Jeffrey Williams SSC Guru Points: 90250 More actions · Answer 3

Also - verify your auto growth settings. They should be a fixed size in MB and not set to the default percentage (or default 1MB which is way too small).

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 4

Last but not least, is the extra space "unallocated" or "unused"? If it's "unallocated", it could be from a recent index rebuild. If it's "unused", you might want to lookup Trace Flag 692.

I also agree with checking on the growth factors as folks pointed out above. They need to be set to MB instead of percent.

--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)

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

Enquiring minds want to know... what did you find out?

--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)

kapfundestanley SSCertifiable Points: 5637 More actions · Answer 6

he finding was that there was an audit trail function that was enable and contributing to datbase growth.

“When I hear somebody sigh, ‘Life is hard,’ I am always tempted to ask, ‘Compared to what?’” - Sydney Harris

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

kapfundestanley wrote:

he finding was that there was an audit trail function that was enable and contributing to datbase growth.

How'd you figure that out and what was the "audit trail function" that you speak of?

--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)