1

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? – Scott Hodgin Sep 12 '16 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 '16 at 10:26
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
begin
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 '16 at 10:30
  • Thanks for the follow-up! Do you know why the two names are showing differently? – Cody Konior Sep 12 '16 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 '16 at 13:23
  • 1
    Please, add end; at the end of the script – new2ios Jun 29 '18 at 11:19
1

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, privacy policy and cookie policy

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