Constantly running out of memory

wayneph

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...)

Chris Auckland

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?

wayneph

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.

Chris Auckland

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

wayneph

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...