Recently, my colleague came across a problem while trying to open the ‘Restore Database’ page in a SQL server database management tool , SSMS. Whether he right-clicked on the ‘Databases’ folder or on a specific database, the page wouldn’t open and eventually, he received an “out of memory” error message.
Exception of type ‘System.OutOfMemoryException’ was thrown. (Microsoft.SqlServer.Management.Sdk.Sfc)
Sometimes, when using SSMS, which is a program for managing databases, you might get an error that says there isn’t enough memory. This often happens if you try to deal with a huge amount of data at once. SSMS can only handle 2 GB of memory because it’s built as a 32-bit application. If the data you’re working with goes over that limit, you’ll see the out of memory error. For more information on this, you can check out this MS article.
As in our particular case, we were not running any query but were just trying to open ‘Restore Database’ page through GUI, out of memory exception was not expected.
Later on when we checked the size of msdb database, it was around 50 GB and tables backupset and backupfile tables were more than 16 GB each.
When we try to open ‘restore database’ page in SSMS GUI, it loads the backupset information to select from for the database restore. As backupset information was too large for SSMS to load, it was failing with out of memory exception.
Msdb database was having 3 years of data and we decided to purge the backup history information in msdb database to retain only 3 months of data.
Below script can be used to achieve the same.
DECLARE @Date datetime
SET @Date = DATEADD(dd,-90,GETDATE())
select @Date
exec sp_delete_backuphistory @oldest_date = @Date
Once done, we were able to open restore database page successfully without any issue.
Hope you would find this article useful!
Happy learning!!