This month T-SQL Tuesday is hosted by Robert Davis (blog/@SQLSoldier) and his topic of choice is “Be The Change”

(If you don’t know about T-SQL Tuesday check out the information here – each month there is a new topic and it is a great excuse to write each month (and to start writing!) because someone offers a topic, so you already have taken the first step!).

--

Robert's choice of topics threw me:

The topic for T-SQL Tuesday #74 is Be the Change. More specifically, data changes. How do you track changing data? How do you do your ETL? How do you clean or scrub your data? Anything related to changing data. Give us your most interesting post involving changing data next Tuesday, January 12, 2016.

As a mostly operational DBA, I have very limited contact with data changes and tracking them.  I have some very limited experience with Change Data Capture, but others have already written about it and done a very good job (also - read Mickey Stuewe's (blog/@SQLMickey) post "Knowing When Data Changes Occur in Standard Edition" about how to work around CDC being Enterprise Only!)

I had a breakthrough earlier today reading Jason Brimhall's (blog/@sqlrnnr) post "All about the Change" in which he writes about changes as related to SQL Audit.  I realized that a fair amount of the administrative triage that I do is directly caused by data changes, especially large scale ones. Here is a solution that I have modified over the years to help me track the changes in my systems *caused* by data changes.  (see how I spun that?)

--

The changes in question here are file size changes - as operational DBAs one of the problems we constantly deal with is files that grow out of control, often from maintenance work such as index rebuilds or from unusual ad-hoc operations such as the analyst working at 1am trying to create a personal copy of the giant Sales table.

We all know that the "best" thing to do (remember, #ItDepends) is to appropriately size your DATA and LOG files ahead of time, and if possible to manually grow those files after hours so that there isn't any production impact.  Even in this absolute best case (almost theoretical) scenario, it is still usually right to leave auto-growth enabled "just in case."

Very few of us live in that world, and we size our files as best as we can with almost no real business requirements using our DBA "Spidey Sense."  Our files are then "managed by auto-grow" as we try to find the best steady state for the DATA and LOG files and minimize impact while getting the best performance.

Does that sound familiar?

http://cdn.meme.am/instances/53655925.jpg

As a service provider, we monitor the drive free space and SQL Server Error Logs on our client servers (along with dozens of other things) - auto-growth problems can often be seen through messages like this:

Could not allocate space for object 'dbo.Table1'.'PK_Table1' in database 'myDatabase' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

...or...

The transaction log for database 'SomeOtherDatabase' is full due to 'ACTIVE_TRANSACTION'.

These messages usually mean that either the file has hit its MAXSIZE cap or that the drive hosting the file is full (or at least sufficiently full that it can't hold another FILEGROWTH increment worth of space). The first question that comes up in the root cause analysis is often "Who grew the database?"

Sometimes there are obvious culprits - is an index maintenance job running?  Is there a nightly ETL running that had an exceptionally large file last night?  Often there is no such smoking gun...and then you need something more.

--

I feel a little dirty writing about the Default Trace in the world of Extended Events, but I also know that many people simply don't know how to use XEvents, and this can be faster if you already have it in your toolbox.  Also it will work back to SQL 2005 where XEvents were new in SQL 2008.

I have modified this several times to improve it - I started with a query from Tibor Karaszi (blog/@TiborKaraszi), modified it with some code from Jason Strate (blog/@StrateSQL), and then modified that myself for what is included and what is filtered.  There are links to both Tibor's and Jason's source material in the code below.

--

/*

Default Trace Query

Especially useful for Auto Grow and Shrink Events 
but works for all default trace info

Modified from Tibor Karaszi at 
http://sqlblog.com/blogs/tibor_karaszi/archive/2008/06/19/did-we-have-recent-autogrow.aspx 

Default Trace Path query modified from
http://www.jasonstrate.com/2013/01/determining-default-trace-location/

*/

DECLARE @fn VARCHAR(1000), @df bit
SELECT @fn =REVERSE(SUBSTRING(REVERSE(path),CHARINDEX('\',REVERSE(path)),LEN(path)))+'log.trc',
/* reading log.trc instead of a specific file will query all current log files */
@df = is_default 
FROM sys.traces 
WHERE id = 1

IF @df = 0 OR @df IS NULL
BEGIN
  RAISERROR('No default trace running!', 16, 1)
  RETURN
END

SELECT te.name as EventName
, t.DatabaseName
, t.FileName
, t.TextData
, t.StartTime
, t.EndTime
, t.ApplicationName
, HostName
, LoginName
, Duration
, cast(Duration/1000000.0 as decimal(10,2)) as DurationSeconds
FROM fn_trace_gettable(@fn, DEFAULT) AS t  
INNER JOIN sys.trace_events AS te 
ON t.EventClass = te.trace_event_id  
WHERE 1=1 /* necessary to cleanly build WHERE clause */
/* find autogrowth events */ 
--and te.name LIKE '%grow%'
/* find autoshrink events */ 
--and te.name LIKE '%shrink%'
/* find manual shrink events */ 
--and (te.name = 'Audit DBCC Event' and (TextData like '%shrinkfile%' or TextData like '%shrinkdatabase%'))
--and DatabaseName='tempdb'
--and StartTime>'01/10/2016 00:00'
--and EndTime<='01/11/2016 13:00'
ORDER BY StartTime desc