There are a few little gems built into SQL Server Management Studio that are easy to forget, but they sure can come in handy.
I sometimes generate a little formatted report of recent growths for a database, and how long they took. It’s really easy, and it’s built right in. Here’s how:
- Right click on a database name in Object Explorer
- Select Reports
- Then Standard Reports
- Then Disk Usage. Voila, the Disk Usage report appears!
- To see recent autogrowth or autoshrink events, click the little plus sign under the pie charts.
Here’s a 30 second video of that in action:
Where Does SQL Server Track Autogrowth Events?
This report reads from the default trace files. Those files can roll over pretty frequently in an active environment, so this only gives you recent events. You can get the same information with a query, or look for slow auto-growths using our free sp_Blitz® script.
When sp_Blitz® gives me a warning that something’s up, I like to get screenshots from this report to go the extra mile. It’s very nicely formatted and shows any troublesome latency pretty clearly.
19 Comments. Leave new
HI,
How come I do not see the link “Data/Log Files Autogrow/Autoshrink Events”
But do see the link “Disk Space Used by Data Files”
For SQL version 2008R2 and 2012
You may not have had any recent auto-growths for that database. It checks for whatever it can find in the default trace, which does roll over, so it’s only recent events.
Hello Kendra, how do I go about finding out the retention period for these auto-growth logs? I too have the same situation as Jeroen, but I need to report to my supervisor on when the last auto-growth happened, or at least establish for sure for how long we have NEW gone through an auto-growth event. I very much appreciate your insights on that. Raphael
I meant “…have NOT gone…”
If you run sp_Blitz @CheckServerInfo = 1, you can see the amount of history stored in the default trace – it’s down in the informational section. The default trace stores a set amount of data, not time, so your retention history depends on what other things are going in there. If you want to set up your own Extended Events session to catch these kinds of things and log them to a file, you certainly could – but that’s left as an exercise for the reader.
…and then 2 years later I come across a similar/related problem and I find this thread I never replied to… :-)))
Well, yes, I tried running EXEC sp_Blitz @CheckServerInfo = 1, and I did find the info. But, at least for the version os sp_Blitz that I have installed (Version = ‘7.96’, Date = ‘20200606’), the info is in the rows reading Server Info, not Informational. 🙂
Anyways, the hint and the troubleshooting scripts are MUCH appreciated!!
THANKS! Best, Raphael.
P.S. I DID try installing the latest version of sp_Blitz but I ran into an error, which may be a bug. I posted on https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues the details of the problem I ran into.
Hi Kendra,
I have SQl 2008 R2- standard Edition with SP2. When I follow the abob steps to ceck Standard report, I don’t see any option called Standard Report. it’s only showing me Custom report.
Can you please assist me to find what could be the reason?
-Anup
It sounds like your SSMS install isn’t complete. The reports should install fully with the management studio installation.
Kendra, whatever DB I run the Disk usage report, click on the little plus sign, I get nothing, only Disk Space used by data files in one row table, a message above it indicating “No entry for autogrowth/autoshrink event for myDB database in the trace log
Thanks
That means you haven’t had any recently– so they haven’t taken any time and there’s nothing for it to report.
I guess I just run Tibor’s query against the default trace… 😉
http://sqlblog.com/blogs/tibor_karaszi/archive/2008/06/19/did-we-have-recent-autogrow.aspx
I looked at Tibor’s, and it turns out Aaron Bertrand also has one. I’m not sure which one I prefer. I should integrate one of them in to my nightly monitor/maintenance routine.
http://sqlblog.com/blogs/aaron_bertrand/archive/2007/01/11/reviewing-autogrow-events-from-the-default-trace.aspx
savvy! =)
I recently saw in the reports an autogrowth event with a growth smaller than the fixed size growth setting. I wonder whether that’s a recent feature that anticipates on the available disk space. I thought I once encountered an error due the fact there was not enough free disk space to extend with the configured growth size.
Hi. Sorry ask on this old post, but hope someone might shed some light on this:
I have for some time suspected that the Disk Usage report that is talked about in this post have stopped showing autogrow events. To test I created an empty database with a very small auto grow setting. I then inserted a lot of data into a table in the DB so that I knew it would have to grow. The file was bigger when looking at file properties of the DB, but the Disk Usage report did not show any autogrow events.
I then tried running Aron Bertrands script (http://sqlblog.com/blogs/aaron_bertrand/archive/2007/01/11/reviewing-autogrow-events-from-the-default-trace.aspx), and that clearly shows every autogrow event.
So, why will not the Disk Usage report show the same growth? Any suggestions?
Btw, it is SQL Server 2014 Enterprise edition SP2
Gert – for questions, head over to http://dba.stackexchange.com.
Thanks for the tip, Brent. I followed your suggestion, and it got me the right way for figuring out what was going on. If anyone want to know: http://dba.stackexchange.com/questions/149384/ssms-disk-usage-report-does-not-show-auto-grow-events/149386#149386
[…] Jetez un oeil à Comment faire pour Voir les Dernières croissance automatique des Événements sur des Bases de donn… […]
[…] Echa un vistazo a Cómo Ver los Últimos crecimiento automático de Eventos en las Bases de datos en SQL Server […]