Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Getting an error "Exception of type 'System.OutOfMemoryException' was thrown." during stored...


Getting an error "Exception of type 'System.OutOfMemoryException' was thrown." during stored procedure execution

Author
Message
pradeep.mohan
pradeep.mohan
SSC-Enthusiastic
SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)

Group: General Forum Members
Points: 187 Visits: 322
problem happenings only db restore activity rest of ssms activities working fine.

Pradeep
Sean Pearce
Sean Pearce
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1578 Visits: 3432
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
pradeep.mohan
pradeep.mohan
SSC-Enthusiastic
SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)

Group: General Forum Members
Points: 187 Visits: 322
backupfile - 44095724
backupfilegroup - 22047863
restorefile - 974
restorefilegroup - 487
restorehistory - 487
backupmediafamily - 22047869

Pradeep
Sean Pearce
Sean Pearce
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1578 Visits: 3432
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
pradeep.mohan
pradeep.mohan
SSC-Enthusiastic
SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)

Group: General Forum Members
Points: 187 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
pradeep.mohan
pradeep.mohan
SSC-Enthusiastic
SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)

Group: General Forum Members
Points: 187 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
Sean Pearce
Sean Pearce
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1578 Visits: 3432
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
pradeep.mohan
pradeep.mohan
SSC-Enthusiastic
SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)

Group: General Forum Members
Points: 187 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
Sean Pearce
Sean Pearce
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1578 Visits: 3432
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
pradeep.mohan
pradeep.mohan
SSC-Enthusiastic
SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)

Group: General Forum Members
Points: 187 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search