OOM error when we access Schema changes report from SSMS
If your profiler traces have a lot of information collected in the traces, then you might encounter an OOM(Out of memory) condition in SSMS when we try to access the reports (Server (Right Click) -> Standard Reports - > Schema Changes History report)
The reason this error is generated is because SQL Server Management Studio is a 32-bit application and you would hit an OOM condition if memory requirements exceeded the user-mode address space limit (2GB)
The error we hit into is:
An error occurred during local report processing
An unexpected error occurred in Report processing
Exception of type 'System.OutOfMemoryException' was thrown
If you see your server physical memory increasing or Available Mbytes value in Perfmon decreasing, then we might want to run the report's TSQL separately with additional filters
We encountered one such condition when we attempted to access Schema Changes History to check when a particular deletion was done. We needed to find the login/application which had performed the deletion.
In case, you encounter the error message below, then you can use the given script to fetch the data from the Default traces using the filters mentioned in the comments. This surely beats the long winding approach of manually sifting through the default profiler traces collected by SQL server 2005
Directions to modify the WHERE clause in the script:
We are tracking the following events:
Event ID 46: Object:Created
Event ID 47: Object:Deleted
Event ID 164: Object:Altered
If we need to track one particular event then we can modify the filters in the script as follows:
To track only Object: Deleted Events, the WHERE clause would be as follows:
where EventClass in (47) and EventSubclass = 0 and DatabaseID <> 2
We can also filter it further by means of an object.
The object type decides what type of object is being modified. If for example we wanted to find out only DROP DATABASE events, then we can add an additional filter as follows to the SELECT statement:
and object_type = 16964 -- This would be a database
(For additional reference this has also been commented in the script.)
The Script to achieve this provided below:
SAMPLE OUTPUT:
We can obtain a wealth of information from this:
· The start time
· We get the login name which fired the “DROP DATABASE” command
· The application name from where it was fired
Amrutha Varshini
SE, Microsoft Sql Server
Reviewed by
Amit Banerjee
TL, Microsoft Sql Server