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.
I usually just blame the BI team for database growth.