Red Gate forums :: View topic - Constantly running out of memory
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Prompt 5
SQL Prompt 5 forum

Constantly running out of memory

Search in SQL Prompt 5 forum
Post new topic   Reply to topic
Jump to:  
Author Message
wayneph



Joined: 19 Jul 2007
Posts: 17

PostPosted: Mon Aug 06, 2012 4:58 pm    Post subject: Constantly running out of memory Reply with quote

I've got SQL Prompt installed in Management Studio primarially for the functionality that used to be Refactor. I am working with PeopleSoft databases that each have over 29,000 Tables and 21,000 Views so whenever I switch between DEV and TST environments, I get a message from SQL Prompt that there has been a memory exception error and I have to unload all of the databases.

Any recommendations on how to fix this? (Assuming that re-releasing SQL Refactor as a separate product isn't going to be an option...)
_________________
--wayne
Back to top
View user's profile Send private message
Chris Auckland



Joined: 24 Oct 2006
Posts: 757
Location: Red Gate Software Ltd.

PostPosted: Tue Aug 07, 2012 8:24 pm    Post subject: Reply with quote

Thanks for your post.

Which exact version of SQL Prompt are you using? I believe a recent update had some code added that would stop registering objects if it was going to cause it to run out of memory.

What happens after you unload the databases? Is this a performance issue, or does it mean you can't use any of the refactoring functionality?
_________________
Chris
Back to top
View user's profile Send private message Send e-mail
wayneph



Joined: 19 Jul 2007
Posts: 17

PostPosted: Wed Aug 08, 2012 3:35 pm    Post subject: Reply with quote

Thanks for the help Chris.

I currently have SQL Prompt 5.3.4.1 installed. (Officially I'm still in the Trial period, but we have a PO that is being worked on for 10 people on our team. I'd like to have this worked out before we get it on everyone's machine.)

When I unload the databases, things continue to work until the next time I switch databases at which point I get the error message again. For the most part, I am able to use the Refactor product in between loads, however with SQL Prompt installed (and Display Suggestions turned off) I have to manually trigger the SQL Server Intellisense which is also cumbersome.

After playing with it a little more, I seem to have the biggest problems with using multiple databases at the same time. As long as I only use objects in the database that I am primarially connected to, it works pretty well. As soon as I try to compare data between databases for test validation, etc... I run into the Outof MemoryException.

With the Databases Unloaded I was able to successfully refactor, but I lost the ability to use snippits and auto insert object/column names through SQL Prompt or Intellisense. Opening a new query window allowed me to start getting suggestions again until the next time it unloaded databases.
_________________
--wayne
Back to top
View user's profile Send private message
Chris Auckland



Joined: 24 Oct 2006
Posts: 757
Location: Red Gate Software Ltd.

PostPosted: Wed Aug 08, 2012 3:56 pm    Post subject: Reply with quote

It's normally the number of columns in a database that cause this sort of behaviour. Can you run the following and let me know the result:

SELECT COUNT(*) FROM sys.syscolumns AS S

Can you also run:

SELECT COUNT(*) FROM sys.sysobjects AS S
_________________
Chris
Back to top
View user's profile Send private message Send e-mail
wayneph



Joined: 19 Jul 2007
Posts: 17

PostPosted: Tue Aug 21, 2012 8:34 pm    Post subject: Reply with quote

Chris,
Sorry for the delayed response. It's been a busy couple of weeks and there isn't a slowdown in sight... That being said, here is the information you requested from our Production Database:

Columns: 658508
Objects: 51084

One of our servers has multiple Training and Test databases that each have the numbers above. We are getting ready to start a PeopleSoft Financials implementation (these databases are PeopleSoft HCM) and from what I hear there are even more objects and columns in that one...
_________________
--wayne
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group