Issue with SQL Server standard reports

Question

Post reply

Issue with SQL Server standard reports

sizal0234

SSCrazy

Points: 2272
More actions
October 15, 2019 at 10:07 pm

#3689130

Unable to open schema change history report after migration. It is loading for almost 30 mins.
You can right click on instance - standard rerport - schema change history

What could be the reason? I am using the updated ssms.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

heb1014 Hall of Fame Points: 3790 More actions · Answer 1

I would look at the SQL coming in to SQL Server to understand what is happening.

sizal0234 SSCrazy Points: 2272 More actions · Answer 2

Here is the sql below:

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'EXAMPLE',

@recipients = 'EXAMPLE@EXAMPLE.com',

@from_address = 'EXAMPLE@EXAMPLE.com',

@subject = 'TEST - EXAMPLE Scripts',

@body = 'EXAMPLE UPDATE'

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 3

that report uses the default trace, if the service hasn't been restarted in a long time the trace may be quite large. You can see how long it has been running:

SELECT start_time, path FROM sys.traces WHERE is_default = 1;

If you just want to see more recent events, you can look in the current file, and even filter by DatabaseName if you want:

SELECT td.DatabaseName, te.name AS Event, td.StartTime,
    td.ObjectID, OBJECT_SCHEMA_NAME (td.ObjectID, td.DatabaseID) AS SchemaName, td.ObjectName, td.LoginName, td.HostName, td.ApplicationName
  FROM sys.traces t
    CROSS APPLY ::fn_trace_gettable(t.path, default) td
    INNER JOIN sys.trace_events te ON td.EventClass = te.trace_event_id
  WHERE t.is_default = 1
    AND td.EventClass IN (46,47,164)
    AND td.DatabaseName = 'mydbname'
    AND td.EventSubClass = 0
  ORDER BY td.StartTime DESC;

To see all the contents you'd need a slightly more complex query:

DECLARE @path NVARCHAR(260);
SELECT @path = REVERSE(SUBSTRING(REVERSE([path]), CHARINDEX('\', REVERSE([path])), 260)) + N'log.trc'
  FROM    sys.traces
  WHERE   is_default = 1;

SELECT td.DatabaseName, td.Filename, te.name AS Event,  (td.IntegerData * 8) / 1024 AS ChangeMB, td.StartTime, td.ObjectID, td.ObjectName, td.LoginName, td.HostName, td.ApplicationName, td.TransactionID
  FROM sys.fn_trace_gettable(@path, DEFAULT) td
    INNER JOIN sys.trace_events te ON td.EventClass = te.trace_event_id
  WHERE EventClass IN (46,47,164)
    AND td.DatabaseName = 'mydbname'
  ORDER BY StartTime DESC;

sterling3721 SSChasing Mays Points: 658 More actions · Answer 4

yes, this could take a while if you didn't disable telemetry extended events. At least that's the case on my local SQL 2016 instance, although there is no load, only used as scratch paper. It collected a lot.