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:



image


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

Script.txt


Comments (0)