I have for some time suspected that the Disk Usage report in SSMS 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 Aaron Bertrand's script from this post, 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 SP2.

  • According to one of your links, "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." - How quickly are you re-running the Disk Usage Report after growing the files? Sep 12, 2016 at 9:27
  • @ScottHodgin I run it right after the growth. However, the answer from Cody below has helped me understand what is going on, I will comment there. Thx for your suggestion.
    – GHauan
    Sep 12, 2016 at 10:26

2 Answers 2


The logic for the disk usage report is baked into SSMS and while we can't know what the RDL looks like (and if any filtering is done) I grabbed this query sent by SSMS 2016 using Profiler:

if (select convert(int,value_in_use) from sys.configurations where name = 'default trace enabled' ) = 1
declare @curr_tracefilename varchar(500) ;
declare @base_tracefilename varchar(500) ;
declare @indx int ;

select @curr_tracefilename = path from sys.traces where is_default = 1 ;
set @curr_tracefilename = reverse(@curr_tracefilename);
select @indx  = patindex('%\%', @curr_tracefilename) ;
set @curr_tracefilename = reverse(@curr_tracefilename) ;
set @base_tracefilename = left( @curr_tracefilename,len(@curr_tracefilename) - @indx) + '\log.trc' ;

    select  (dense_rank() over (order by StartTime desc))%2 as l1
,       convert(int, EventClass) as EventClass
,       DatabaseName
,       Filename
,       (Duration/1000) as Duration
,       StartTime
,       EndTime
,       (IntegerData*8.0/1024) as ChangeInSize
from ::fn_trace_gettable( @base_tracefilename, default )
left outer join sys.databases as d on (d.name = DB_NAME())
where EventClass >=  92      and EventClass <=  95        and ServerName = @@servername   and DatabaseName = db_name()  and (d.create_date < EndTime)
order by StartTime desc ;

This isn't substantially different to Aaron's script and I don't see how it could return different results. It looks to me that even if the traces roll over, it will still be iterating all of them (and they wouldn't roll over 5x in the space of a few minutes or even hours of testing).

  • 1
    Hi. Thx for your post. If this really is what the Disk Usage report queries for, then it helped me to understand what is going on. It seems that on my server, there is a difference in the ServerName field reported back from @@servername and the ..fn_trace_gettable-function. So since the query you posted needs them to be equal, it will not return any rows. Again, thank you.
    – GHauan
    Sep 12, 2016 at 10:30
  • Thanks for the follow-up! Do you know why the two names are showing differently? Sep 12, 2016 at 11:21
  • 2
    I suspect it is because the server was replaced with a new one, but the servername of the new server would be the same as the old. During install of SQL server I guess the new server has gotten a placeholder name until the old server was shut down. When the new server was renamed to the real name, SQL Server has not recorded this. It seems that we must run sp_dropserver/sp_addserver to fix this.
    – GHauan
    Sep 12, 2016 at 13:23
  • 2
    Please, add end; at the end of the script
    – new2ios
    Jun 29, 2018 at 11:19

I have come across exactly this issue with a SQL Server Instance located in Amazon Web Services (AWS). This is because querying @@servername returns the AWS name for the server, which is somewhat internal and certainly doesn't match what is known as the servername. The correct name can be found by using SERVERPROPERTY('servername') instead of @@servername

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.