How many Backups is Too Many?

Does this title seem strange?  Of course we know this is the true answer:

https://cdn.meme.am/cache/instances/folder633/58097633.jpg

But here is a story of a client that may have too many backups after all.

--

Our monitoring system found that the backups on a client server had suddenly started running very long.  When I signed on to look I saw that the nightly backups were still running even the next morning!  These were the three currently running processes related to backups:

--

LINE	dd hh:mm:ss.mss	session_id	sql_text	login_name
1	00 15:24:36.674	134	backup database [BobData] to virtual_device = N'CA_BAB_MSSQL_a786700_10002_4c59b51f_BobData' with differential, blocksize = 65536, buffercount = 1, maxtransfersize = 2097152	DOMAIN1\sa_arcbackup
2	00 11:27:32.043	165	BACKUP DATABASE [BobData] TO  DISK = N'B:\SQL_BAC\BobData\BobData_backup_2017_02_23_020011_9582786.bak' WITH  RETAINDAYS = 10, NOFORMAT, NOINIT,  NAME = N'BobData_backup_2017_02_23_020011_9582786', SKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 10	DOMAIN1\sqlcluster
3	00 02:58:20.540	84	backup database [FredData] to virtual_device = N'CA_BAB_MSSQL_a786080_10001_4f06c827_FredData' with blocksize = 65536, buffercount = 1, maxtransfersize = 2097152	DOMAIN1\sa_arcbackup

LINE	wait_info	CPU	tempdb_allocations	tempdb_current	blocking_session_id
1	(55475564ms)ASYNC_IO_COMPLETION	236	0	0	NULL
2	(41252214ms)LCK_M_U	0	0	0	134
3	(10699785ms)ASYNC_IO_COMPLETION	357	0	0	NULL

LINE	percent_complete	host_name	database_name	program_name	start_time	login_time
1	79.8087	Server5	BobData	Arcserve Backup	2/22/2017 22:02	2/22/2017 22:02
2	NULL	Server1	BobData	Microsoft SQL Server Management Studio	2/23/2017 2:00	2/23/2017 2:00
3	18.4672	Server5	FredData	Arcserve Backup	2/23/2017 10:30	2/23/2017 1:05

The middle process (Line 2 – session ID/SPID 165) is a regular BACKUP DATABASE statement that had been running since 2am local server time.  As you can see from the wait info, the entire life of this SPID has been spent waiting for an update lock (LCK_M_U).  Also notice that the Percent Complete is still NULL because it is being blocked by the first SPID (134) and hasn’t done anything.

The first and third SPID’s (134 and 84) are Arcserve backups of the BobData and FredData databases respectively.  Looking in the backup history in msdb for the BobData database I see this:

database_name	physical_device_name	type	backup_start_date	backup_finish_date
BobData	B:\SQL_BAC\BobData\BobData_backup_2017_02_22_020015_7258925.bak	D	02/22/2017 04:29:47	02/22/2017 04:40:41
BobData	CA_BAB_MSSQL_8ea33d0_10002_4732d2d8_BobData	I	02/21/2017 22:01:34	02/22/2017 04:29:44
BobData	B:\SQL_BAC\BobData\BobData_backup_2017_02_21_020010_3116691.bak	D	02/21/2017 11:54:41	02/21/2017 12:05:22
BobData	CA_BAB_MSSQL_8c6ff40_10002_420ce264_BobData	I	02/20/2017 22:02:02	02/21/2017 11:54:39
BobData	B:\SQL_BAC\BobData\BobData_backup_2017_02_18_020007_3945711.bak	D	02/19/2017 23:39:43	02/19/2017 23:50:38
BobData	CA_BAB_MSSQL_8d4b360_10001_329872eb_BobData	D	02/17/2017 22:00:34	02/19/2017 23:39:41

The Arcserve backup was kicking off around 10pm local server time, and taking a widely variable amount of time to complete – the first backup (start date 02/17 22:00) was a FULL backup (type D) and took just over *2 days* to complete (02/17 22:00 to 02/19 23:39)!

https://s-media-cache-ak0.pinimg.com/736x/25/b2/82/25b2825a9b57026e1932ba48909699e9.jpg

The catch is the next row up – the regular FULL backup to the B: drive looks like it kicks off right when the Arcserve backup completes, at 23:39 – this is most likely because it was being blocked by the Arcserve backup (just like the current activity above) and when the Arcserve backup completes, the FULL backup to the B: drive begins.

This history shows this same pattern of the B: drive backup kicking off right when the Arcserve backup completes through the following days.

The SQL “regular” Agent job kicks off at 2am, so it is hanging as a blocked SPID each day until the Arcserve job completes and then running in a very reasonable amount of time – BobData is a 340GB database that backs up into a 36GB file (nice compression!) and as seen in the start/finish times above only takes 10-30 minutes to run, which by my experience is very good for a database this size.

From the DBA end we can’t speak to why the Arcserve backup takes such a long time – completely guessing I would assume it isn’t compressing and therefore is sending the full 340GB across the network pipe, but this still seems very slow.

The SPID’s for the Arcserve backups are waiting on “ASYNC_IO_COMPLETION” which means it is waiting on storage read/writes, in this case certainly *writes*.

--

There are two pieces to this for follow-up – one very important and another less so.

First and foremost – as a DBA I strongly recommend not having “competing” backups like this.  The backups should either run to disk (very *very* strongly preferred) or to a tool like Arcserve.  Running to two different tools leads to exactly the kind of problem seen here.

https://imgflip.com/i/1k9t97

Note that this is not related to Arcserve in particular - any third-party backup tool that uses a database agent to directly run backups will display this behavior.

A conversation showed that the Arcserve backups had been recently added and it was definitely a match to the problems.

The workaround to this is to back the databases up to one tool and then to *copy* those backup files to the other tool.  The best recommendation I have in these situation is always to run “regular” SQL Server Agent job backups to a file location (either a local or network drive) and then to have your third-party tool use file backup for the actual BAK/TRN files from that location *rather* than running the “database agent” on the third party tool to backup the database directly.

In this model the third party tool never directly touches SQL Server – in this client's environment you would run SQL Server Agent jobs similar to the current maintenance plan (although something better than maintenance plans, such as Ola Hallengren's scripts or maybe MinionBackup) to backup to actual disk (in thiscase the B: drive) and then use Arcserve to backup the files that have been written on the B: drive.

Another advantage to this backup model is it makes it easier for the DBAs managing your SQL Servers to access the backups for disaster recovery/data copy/etc.  If you backup to the third-party tool alone it usually requires some third party (usually a server admin or SAN admin) to perform a database restore – with regular SQL backups directly to disk, the DBA can perform that work by themselves which can greatly decrease recovery time in case of a disaster.

If you absolutely *must* backup directly to a tool like Arcserve, it needs to be made more reliable than what this client was seeing – wide swings of duration from 6 hours to 13 hours to 49 hours can’t be acceptable – and the bottleneck must be determined and resolved – there is no way it can be acceptable for a backup to take over 2 days on a 340GB database.  Fixing this requires work across your network, storage, and admin teams.

--

Second – as you can see from the limited number of rows in my history grid above, the current SQL Maintenance Plan is purging backup history, etc. that is older than 4 days

I understand a desire to have backup file cleanup of 4 days, as seen in the next item in the maintenance plan, as the actual backups can take up a lot of space:

However I strongly recommend editing the “Clean Up History” task the retain records for a much longer interval, such as 35-40 days.  There are often processes that happen monthly that require troubleshooting (for example, an end-of-month data load that slows the system down), and only keeping 30-31 days can often cause an issue where something happens on a Friday or Saturday but isn’t investigated until Monday (or Tuesday) – by the time the investigation begins, the 30^th or 31^st day of history has rolled off and the troubleshooting is hampered by lack of data...

http://i0.kym-cdn.com/entries/icons/original/000/019/630/ihnmotp.jpg

Maintenance/Backup history takes up very little space and can easily be retained for even several months (such as 90-100 days) without disk space issues.

--

At the end of the day watch out for duplicative backups - some times they are necessary, but in most cases you can meet your requirements to offshore your backups, send them to a DR site, etc. using a simple file copy of BAK and TRN files.

Also watch your history retentions closely - don't swing the other way - I can't count the number of servers I have found with huge msdb's because no cleanup is happening - but set it to a realistic number that won't hamper your troubleshooting.  Consider the frequency of your key processes; in almost all environments there are at least monthly considerations, but often there are also bi-monthly or quarterly concerns that would require you to keep up to 100 days of history for effective troubleshooting.

--

Hope this helps!

YouTubing Down the SQL Server River

I recently stumbled on a YouTube channel from one of my favorite SQLPeople - she is sharp (and an MCM) and one of the nicest people out there - Kendra Little (blog/@Kendra_Little).  Since divesting herself from Brent Ozar Unlimited over a year ago Kendra has been on a "do-it-myself sabbatical to learn, write, teach, and see a bit of the world."  During that time she has produced a *lot* of SQL Server content.

I read her blog regularly but somehow never saw that she had a YouTube channel.

The channel hosts 10-30 minute performance tuning videos as well as Kendra's weekly "Dear SQL DBA" podcast.  As I said Kendra really knows her stuff, so definitely check this out!

--

I have written in the past about the PASS virtual chapter YouTube feeds, but this made me go looking for who else is out there in YouTube-land, and I found quite a bit!

--

My company Ntirety has its own YouTube channel with short videos on database administration (and a few commercials #disclosure).

--

Microsoft has quite a few "official" YouTube channels:

• Microsoft Cloud Platform - all things Azure, including SQL Server
• Microsoft Ignite - videos from the annual Ignite DBA/Developer conference
• Microsoft Power BI - business intelligence
• Guy in a Cube - Adam Saxton is a Microsoft Employee who writes about BI 

While I couldnt find an official "Microsoft SQL Server" or "Microsoft Database Platform" channel, I did see this interesting SQL Server channel - a channel "generated automatically by YouTube's video discovery system" that has managed to find many videos from various sources.

--

SQL Server MVP (and all-around fun guy) Grant Fritchey (blog/@GFritchey) has a channel here about a variety of topics.

--

SQL Server author/instructor Sharon Dooley has a frequently updated channel here.

--

Of course the big vendors/consultancies have their own channels - some of it is company/product commercials (gotta pay the bills) but a majority of it is really good free content about SQL Server.

In no particular order:

• SentryOne (formerly SQLSentry)
• Solarwinds
• Redgate Software
• Brent Ozar Unlimited
• Pragmatic Works
• Joes2Pros

--

Of course there are other non-YouTube video sources out there, but I recommend you start here - watching this free content will keep you busy learning for some time!

#SQLSpeaking this Fall!

I have some great speaking dates coming up this Fall - here is the list:

--

First - the big one.  I am honored to be chosen for the first time to speak at the PASS Summit in Seattle.  The only negative is that I am not on until Friday morning, so I will get to spend the whole week sweating it!

I am giving my health check session "Does it Hurt When I Do This? Performing a SQL Server Health Check" - http://www.sqlpass.org/summit/2016/Sessions/Details.aspx?sid=47833

IMPORTANT - the final price increase for registration is this Monday September 19th - register by midnight Sunday to beat the $200 increase!  http://www.sqlpass.org/summit/2016/RegisterNow.aspx

--

Multiple SQL Saturdays on the agenda as well:

09/24 - SQL Saturday #548 Kansas City (#SQLSATKC)  http://www.sqlsaturday.com/548/eventhome.aspx

10/01 - SQL Saturday #557 Minnesota (#SQLSATMN)  http://www.sqlsaturday.com/557/eventhome.aspx

At both of these SQL Saturdays I will be making a final tune-ups on my health check session - I have given it multiple times in the past but will be walking through the "final" version before Summit.

11/19 0 SQL Saturday #552 Lincoln, NE (#SQLSAT552) http://www.sqlsaturday.com/552/eventhome.aspx

The formal speaker list/schedule hasn't been released yet but I am hopeful to be giving either my XEvents or health check session.

--

I look forward to seeing you at any or all of these events!

Thank You SQL Saturday Sioux Falls!

I just finished giving my SQL Saturday presentation on "Getting Started with Extended Events" at SQLSaturday Sioux Falls.  The audience was great with some multiple questions and follow-ups, so it was awesome!

The sessions have been informative and the event team has done a wonderful job with all of the logistics, so the event is awesome - whether you are here now or not you should definitely put it on your calendar for next July!

My Powerpoint slides and demo scripts are now up on the SQLSaturday web page at http://www.sqlsaturday.com/539/Sessions/Details.aspx?sid=49228 - thanks!

Finding File Growths with Extended Events

I give a SQLSaturday session called "Introduction to Extended Events" and at a recent presentation I had a pair of attendees ask me afterwards for a more "real world" example.  I had a basic demo in my session showing setting up a session with a predicate filter to pull particular events for a single database, but it didn't really show using XEvents to solve a business problem.

I pondered for a while and decided to try to recreate something I normally do with the default trace.  The obvious example to me was pulling data on file growths.

What?  You didn't know you could pull information about file growths from the default trace?

http://canitbesaturdaynow.com/images/fpics/1137/kitty_-(8)__large.jpg

I discovered this some time ago when I needed to find out why a database was blowing up at 1am without needing to be awake and online at 1am...at first I was going to set up a manual trace when I found a pair of blog posts that helped me out.

The first was "Did we have recent autogrows?" from Tibor Karazi (blog/@TiborKaraszi) which clued me in to the fact that autogrow information is even in the default trace.  The catch to Tibor's query is that it relied on a specific trc file - the default trace is a rollover structure so at any given time there are usually multiple sequential trc files, and using his query meant I needed to run it, edit the file name, run it again, etc.

The next piece was the Simple Talk article "Collecting the Information in the Default Trace" from Feodor Georgiev (blog/@FeodorGeorgiev) that showed me how to dynamically build the log file path *and* the fact that I could simply reference log,trc (instead of log_123.trc) and the function would concatenate all of the trc files into a single resultset.

I have said it before and I will say it again - when you need help, odds are someone else has already solved the same (or a similar) problem - look around and you will probably find it!  When you save or reference someone else's work, always make sue to give credit (as I have done above) - even if you make modifications to code or ideas, always acknowledge the original material.

http://www.funcatpictures.com/wp-content/uploads/2013/10/funny-cat-pics-internet-high-five.jpg

I took the two queries I found and combined them into a single modified query that used Tibor's base query to pull autogrowths but Feodor's concept of the custom built log path:

/*
Default Trace AutoGrow Query

Modified from Tibor Karazi
http://sqlblog.com/blogs/tibor_karaszi/archive/2008/06/19/did-we-have-recent-autogrow.aspx
and Feodor Georgiev
https://www.simple-talk.com/sql/database-administration/collecting-the-information-in-the-default-trace/
*/

DECLARE @df bit
SELECT @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.StartTime
, t.ApplicationName
, HostName
, LoginName
, Duration
, TextData
FROM fn_trace_gettable(
(SELECT REVERSE(SUBSTRING(REVERSE(path), CHARINDEX('\', REVERSE(path)),256)) + 'log.trc'
FROM    sys.traces
WHERE   is_default = 1
), DEFAULT) AS t
INNER JOIN sys.trace_events AS te
ON t.EventClass = te.trace_event_id
WHERE 1=1
and te.name LIKE '%Auto Grow'
--and DatabaseName='tempdb'
--and StartTime>'05/27/2014'
ORDER BY StartTime

--

SELECT TOP 1 'Oldest StartTime' as Label, t.StartTime
FROM fn_trace_gettable(
(SELECT REVERSE(SUBSTRING(REVERSE(path), CHARINDEX('\', REVERSE(path)),256)) + 'log.trc'
FROM    sys.traces
WHERE   is_default = 1
), DEFAULT) AS t
INNER JOIN sys.trace_events AS te
ON t.EventClass = te.trace_event_id
ORDER BY StartTime   

The top query returns all rows for an event like '%Auto Grow' (to capture DATA and LOG autogrowths) and as you can see you can also filter on a specific database, date range, etc.

The second query simply uses the same base table set to return the datetime of the oldest row in the current default trace.  Since the default trace is a rolling first in first out (FIFO) arrangement, there is no way to definitively say how far back the trace goes other than to actually look in the trace like this.  If you have a lot of trace-relevant events or a lot of service restarts, your default trace may only have a few days (or even hours) of data and not have what you need - for example if you are trying to troubleshoot a problem from last Saturday and by Monday it has already aged out of the trace, then this isn't useful.

The resultset looks like this:

Pretty slick, right?

http://i.imgur.com/vIdCPPt.png

...but what does this have to do with Extended Events?

As I started out, I was looking for something slick I do with a trace that I could replicate in Extended Events, and this was a great candidate.

--

The catch as I discovered, is that while file growths are captured in the default trace, they are *not* in the system health session...

http://www.wicproject.com/images/2010/05/hobbes_doh.jpg

This was slightly annoying, but it also gave me an excuse to create a demo for setting up an XEvents session :)

--

First, the session:

USE [master]
GO 

SET NOCOUNT ON 

/* Create Extended Events Session */
IF EXISTS (SELECT 1 FROM master.sys.server_event_sessions WHERE name = 'DemoFileSize')
DROP EVENT SESSION [DemoFileSize] ON SERVER
GO
CREATE EVENT SESSION [DemoFileSize] ON SERVER
ADD EVENT sqlserver.database_file_size_change(SET collect_database_name=(1)
    ACTION(package0.collect_system_time,sqlos.task_time,
sqlserver.client_app_name,sqlserver.client_hostname,
sqlserver.client_pid,sqlserver.database_id,sqlserver.database_name,
sqlserver.server_instance_name,sqlserver.session_id,
sqlserver.sql_text,sqlserver.username)),
/* Note - no predicate/filter - will collect *all* DATA file size changes */
ADD EVENT sqlserver.databases_log_file_size_changed(
    ACTION(package0.collect_system_time,sqlos.task_time,
sqlserver.client_app_name,sqlserver.client_hostname,
sqlserver.client_pid,sqlserver.database_id,sqlserver.database_name,
sqlserver.server_instance_name,sqlserver.session_id,
sqlserver.sql_text,sqlserver.username))
/* Note - no predicate/filter - will collect *all* LOG file size changes */
ADD TARGET package0.event_file(SET filename=N'c:\XE\DemoFileSize.xel',
max_file_size=(500),max_rollover_files=(10))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,
MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO

ALTER EVENT SESSION [DemoFileSize] ON SERVER
STATE = START;
GO

This just creates a basic session, tracking two events, sqlserver.database_file_size_change and sqlserver.log_file_size_change, writing the output to a file target.

--

Next the database and table:

/* Create DemoGrowth database, dropping any existing DemoGrowth database */
IF EXISTS (SELECT database_id from sys.databases WHERE name = 'DemoGrowth')
BEGIN
 ALTER DATABASE [DemoGrowth] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
 DROP DATABASE [DemoGrowth]
END
CREATE DATABASE [DemoGrowth]
 ON  PRIMARY
( NAME = N'DemoGrowth_Data'
 , FILENAME = 'C:\Temp\DemoGrowth.MDF'
 , SIZE = 4MB , MAXSIZE = UNLIMITED, FILEGROWTH = 2MB )
 /* Small increments for maximum autogrowths */
 LOG ON
( NAME = N'DemoGrowth_log'
 , FILENAME = 'C:\Temp\DemoGrowth.LDF'
 , SIZE = 2MB , MAXSIZE = 2048GB , FILEGROWTH = 2MB )

ALTER DATABASE [DemoGrowth] SET RECOVERY FULL
GO

BACKUP DATABASE [DemoGrowth]
TO DISK = 'C:\TEMP\DemoGrowth.bak'
WITH STATS = 5, INIT

USE [DemoGrowth]
GO

/* Create DemoGrow table, dropping any existing DemoGrow table */
IF OBJECT_ID('dbo.DemoGrow','U') is not null
DROP TABLE dbo.DemoGrow
CREATE TABLE dbo.DemoGrow
/* Purposely large datatypes for maximum needed autogrowths */
(
ID BIGINT IDENTITY(1,1)
,BIGNAME NCHAR(4000) DEFAULT 'BOB'
)

Setting the table with an identity and a character column with a default value makes the test inserts straightforward to code.  The database was created small and the table with large datatypes in order to maximize potential auto-growths.

--

The next step is to Watch Live Data for the session in order to see that autogrowths are happening - note that the sessions as configured is set to write to an event_file target, so Watch Live Data is the easy way to watch events happen, albeit with some minor performance impact.

Next, let's insert some rows and see what happens:

/* Insert 6000 rows and return file sizes before and after inserts */
SELECT name, size*8.0/1024.0 as SizeMB from DemoGrowth.sys.sysfiles 

GO

INSERT INTO dbo.Grow DEFAULT VALUES 

GO 6000 

SELECT count(ID) from dbo.Grow (NOLOCK) 

SELECT name, size*8.0/1024.0 as SizeMB from DemoGrowth.sys.sysfiles
/* If after sizes <> before sizes, check Watch Live Data for rows */

The sysfiles query shows whether the data and log files have grown after the inserts - it is possible (although highly unlikely) that the 6000 inserts don't cause either file to grow - if this is the case (or if you just want to witness more traffic) you re-run the code block multiple times.

--

Next we need to backup the LOG (remember that we are in FULL recovery) and manually shrink the database in order to see that the shrink also causes events:

BACKUP LOG DemoGrowth
TO DISK = 'C:\TEMP\DemoGrowth.trn'
WITH STATS = 5, INIT
/* Shrink files and return sizes before and after */

SELECT name, size*8.0/1024.0 as SizeMB from DemoGrowth.sys.sysfiles 

DBCC SHRINKFILE (N'DemoGrowth_Data')
GO

DBCC SHRINKFILE (N'DemoGrowth_Log')
GO

SELECT name, size*8.0/1024.0 as SizeMB from DemoGrowth.sys.sysfiles

/* Assuming shrink occurs, check Watch Live Data for rows */
/* If the LOG doesn't show shrunk, then re-run LOG backup and shrink again */

Just as a shrink logs events, so do manual growths:

/* Manually grow DATA file to see effect on session */
SELECT name, size*8.0/1024.0 as SizeMB from DemoGrowth.sys.sysfiles

/* Check Current File Size to set an appropriate size in ALTER grow statement */
ALTER DATABASE [DemoGrowth] MODIFY FILE ( NAME = N'DemoGrowth_Data', SIZE = 128MB )
GO

SELECT name, size*8.0/1024.0 as SizeMB from DemoGrowth.sys.sysfiles
/* Check Watch Live Data for rows */

--

So what is in the event_file (XEL file)?  There is a system function (sys.fn_xe_file_target_read_file) to return that data:

/* So let's look at the XEL file */
SELECT * FROM sys.fn_xe_file_target_read_file('C:\temp\DemoFileSize*.xel',NULL, NULL, NULL);  

BLEAH!

As always, someone has already come up with a better solution.  Google brought me to SQL Zealot and his query to shred the XML at https://sqlzealots.com/2015/04/01/auto-file-growth-track-the-growth-events-using-extended-events-in-sql-server/  - I modified the query slightly to return the data elements I wanted, including the timestamp:

SELECT
Case when file_type = 'Data file' Then 'Data File Grow' Else File_Type End AS [Event Name]
, database_name AS DatabaseName
, dateadd(minute, datediff(minute, sysutcdatetime(), sysdatetime()), timestamp1) as LocalTimeStamp
/* added the timestamp and in XE is UTC - this code converts it to local server time zone */
, file_names
, size_change_mb
, duration
, client_app_name AS Client_Application
, client_hostname
, session_id AS SessionID
, Is_Automatic
FROM
(
SELECT
(n.value ('(data[@name="size_change_kb"]/value)[1]', 'int')/1024.0) AS size_change_mb
, n.value('(@timestamp)[1]', 'datetime2') as timestamp1
, n.value ('(data[@name="database_name"]/value)[1]', 'nvarchar(50)') AS database_name
, n.value ('(data[@name="duration"]/value)[1]', 'int') AS duration
, n.value ('(data[@name="file_type"]/text)[1]','nvarchar(50)') AS file_type
, n.value ('(action[@name="client_app_name"]/value)[1]','nvarchar(50)') AS client_app_name
, n.value ('(action[@name="session_id"]/value)[1]','nvarchar(50)') AS session_id
, n.value ('(action[@name="client_hostname"]/value)[1]','nvarchar(50)') AS Client_HostName
, n.value ('(data[@name="file_name"]/value)[1]','nvarchar(50)') AS file_names
, n.value ('(data[@name="is_automatic"]/value)[1]','nvarchar(50)') AS Is_Automatic
FROM
(
SELECT CAST(event_data AS XML) AS event_data
FROM sys.fn_xe_file_target_read_file(
N'C:\temp\DemoFileSize*.xel'
, NULL
, NULL
, NULL
)
) AS Event_Data_Table
CROSS APPLY event_data.nodes('event') AS q(n)) xyz
ORDER BY timestamp1 desc
/* Much Better! */

--

So as you can see, a basic XEvents session provides the information comparable to the default trace.

But....XEvents is supposed to be better right?

I neglected to tell you one thing...in the query above you can see that there is a data element for sql_text - look what's in it:

INSERT INTO dbo.DemoGrow DEFAULT VALUES

http://s.quickmeme.com/img/15/151d67b9a73228d44b1bfabea0d012b54b9cd2821a25bf4b4be1bad10c41a95d.jpg

Pretty slick, huh?  The element shows you *what query* caused the file size change - something the default trace does not.  If you go back to the default trace, you will see there is a textData field, but that field is NULL for all of the operations in question (it is used for a few other types of events such as DBCC events, but not for the file growth events).

Also you may have noticed that our XEvents are called sqlserver.database_file_size_change and sqlserver.log_file_size_change, and they return data for growths *and* shrinks.  The default trace only returns data on file growths directly (although you could scrape the "DBCC Events" looking for DBCC SHRINKDATABASE and DBCC SHRINKFILE, but there are a lot of other DBCC events to search through.

--

Extended Events are awesome, and with a little work can give you everything that trace gives you and so much more.

Hope this helps!