Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»

Getting an error "Exception of type 'System.OutOfMemoryException' was thrown." during stored procedure execution Expand / Collapse
Author
Message
Posted Tuesday, September 10, 2013 6:02 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, July 28, 2014 12:44 AM
Points: 91, Visits: 322
problem happenings only db restore activity rest of ssms activities working fine.

Pradeep
Post #1493129
Posted Tuesday, September 10, 2013 6:47 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, December 18, 2014 1:37 AM
Points: 997, Visits: 3,089
What do these queries return?

SELECT COUNT(1) FROM msdb.dbo.backupfile;
SELECT COUNT(1) FROM msdb.dbo.backupfilegroup;
SELECT COUNT(1) FROM msdb.dbo.restorefile;
SELECT COUNT(1) FROM msdb.dbo.restorefilegroup;
SELECT COUNT(1) FROM msdb.dbo.restorehistory;
SELECT COUNT(1) FROM msdb.dbo.backupmediafamily;





The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1493144
Posted Tuesday, September 10, 2013 7:00 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, July 28, 2014 12:44 AM
Points: 91, Visits: 322
backupfile - 44095724
backupfilegroup - 22047863
restorefile - 974
restorefilegroup - 487
restorehistory - 487
backupmediafamily - 22047869


Pradeep
Post #1493152
Posted Tuesday, September 10, 2013 7:19 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, December 18, 2014 1:37 AM
Points: 997, Visits: 3,089
pradeep.mohan (9/10/2013)
backupfile - 44095724
backupfilegroup - 22047863
restorefile - 974
restorefilegroup - 487
restorehistory - 487
backupmediafamily - 22047869

There is your problem. You have LOADS of backup history. The GUI is trying to read those tables and you don't have enough memory. You can cleanup old history using sp_delete_backuphistory.

http://technet.microsoft.com/en-us/library/ms188328.aspx





The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1493167
Posted Tuesday, September 10, 2013 11:27 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, July 28, 2014 12:44 AM
Points: 91, Visits: 322
Thanks for your reply...

when i am executing sp_delete_backuphistory '08/31/2013' its taking too long time to execute and MSDB log file is increasing very huge during this time.

why log file size is increasing, initially its was 700 MB only after start to execute the query it was 25GB then i stop this process.

How to over come this issue.

Thanks.



Pradeep
Post #1493447
Posted Wednesday, September 11, 2013 5:25 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, July 28, 2014 12:44 AM
Points: 91, Visits: 322
After creating index i can successfully deleted old history records from backup tables but MSDB database size is remains same.

I almost delete 50 Lakh records from in below respective tables in MSDB.

backupfile
backupfilegroup
restorefile
restorefilegroup
restorehistory
backupmediafamily

Note: MSDB database is simple recovery mode.


Pradeep
Post #1493585
Posted Wednesday, September 11, 2013 7:04 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, December 18, 2014 1:37 AM
Points: 997, Visits: 3,089
You may need to shrink msdb after a large cleanup.

Are you able to restore via the GUI now?




The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1493653
Posted Wednesday, September 11, 2013 7:10 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, July 28, 2014 12:44 AM
Points: 91, Visits: 322
Shrink msdb data file or log file?

why because i already shrink log files, meanwhile log shipping is configured on this server.

So if i shrink the data file (If i am right) is there any impact of log shipping process?


Pradeep
Post #1493658
Posted Wednesday, September 11, 2013 8:18 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, December 18, 2014 1:37 AM
Points: 997, Visits: 3,089
If disk space is not an issue then I would probably leave it. Are you able to restore via the gui?



The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1493709
Posted Friday, September 13, 2013 5:55 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, July 28, 2014 12:44 AM
Points: 91, Visits: 322
Yes, now i can able to restore via GUI...Many thanks.

but MSDB database size still 50GB how can i reduce the size after cleanup backup history table.

So what is the impact if am going to shrink the msdb data file why because log shipping is configure in this server.


Pradeep
Post #1494549
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse