find out who / what casued my database to increase in size?

Question

find out who / what casued my database to increase in size?

jCoke

Ten Centuries

Points: 1121
More actions
June 21, 2017 at 8:03 am

#338261

So one of my production databases just grew 100 gb in 10 minutes during the lunch break... its been stable at about 115gb for months, and that makes me think its a user doing something fishy.
How can i find it what user and what the query looked like?
Any and all suggestions are much appreciated
/J

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

You can't, unless you already had something in place to capture such events. If you're lucky, you may find something in the default trace. Do any of your tables have a column that shows inserted or last modified date? You could restore a copy of the database just before and just after and compare them. Was it a data file that grew, or the log file? Do you have any jobs that run around the time that the growth occurred?

John

Eric M Russell SSC Guru Points: 125560 More actions · Answer 2

The following default trace query will tell you when file growth events occurred, the database name and size of the growth, and login name of the user who executed the operation.

select te.name as event_name, tr.DatabaseName, tr.FileName, (tr.IntegerData * 8) / 1024 AS GrowthMB, tr.LoginName, tr.StartTime, tr.EndTime from sys.fn_trace_gettable(convert(nvarchar(255), (select value from sys.fn_trace_getinfo(0) where property=2)), 0) tr inner join sys.trace_events te on tr.EventClass = te.trace_event_id where tr.EventClass in ( 92, -- Data File Auto Grow 93, -- Log File Auto Grow 94, -- Data File Auto Shrink 95 -- Log File Auto Shrink ) order by EndTime desc;

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

DimUser Ten Centuries Points: 1043 More actions · Answer 3

do you have auto-growth enabled?
if so, have you checked the auto-growth values for the files?
100gb is an awfully round number, you could have something misconfigured here?
start simple then work in to the more complicated processes (detailed above).

HappyGeek SSCoach Points: 18729 More actions · Answer 4

You have a daily report which shows your file size and growth right? If not set something up now!
Had a similar problem some years ago and it turned out to be a third party application that was essentially growing the log during an update process, the consultant in charge of the project was not happy I spotted that!!

John is right you should have something in place. In terms of the culprit, if it is reseller or third party software you may find it is the server account, not helpful at all. I settled for a window of opportunity on a test server in the end.

...

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

If proc cache hasn't been cleared, it may show up in the built in reports as one of the queries that used the most I/O.

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

Eric M Russell SSC Guru Points: 125560 More actions · Answer 6

Assuming that the default trace is running (which it should be by default) and the retention window covers the time frame you're interested in, then that query I posted earlier will tell you everything you need to know.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho