Error when connecting the first time

donwiid

My first post, testing SQL Prompt 3.50.323. Tested in SQL 2000 SP4 environment, virtual server with 2GB RAM.

WS details: Acer Laptop, 2GHZ Pentium M with 2GB RAM.

On starting Query Analyzer, MASTER was the default database, therefore SP3.5.0.323 tried to connect to it and threw the exception below. I switched to another database, which worked fine. Then back to MASTER, which ALSO worked!

I have to confess: When I upgraded from 3.0 it required that SQL prompt had to be closed. I had to kill the process Redgate.SQLPrompt.TrayApp.exe, since the tryApp icon had disappeared.


Exception details:

Cannot provide auto-completion for database [SERVER02].[master] because there is insufficient memory.

RedGate.SqlPrompt.Engine.FairlySeriousException: Cannot provide auto-completion for database [SERVER02].[master] because there is insufficient memory. ---> System.OutOfMemoryException: Exception of type 'System.OutOfMemoryException' was thrown.
at System.Collections.ArrayList..ctor(Int32 capacity)
at RedGate.SQLCompare.Engine.SerializableCollection..ctor(NullableBinaryReader reader)
at RedGate.SQLCompare.Engine.Function..ctor(NullableBinaryReader reader)
at RedGate.SQLCompare.Engine.Functions.DeserializeItem(NullableBinaryReader reader)
at RedGate.SQLCompare.Engine.SerializableCollection..ctor(NullableBinaryReader reader)
at RedGate.SQLCompare.Engine.Database.LoadFromDisk(String fileName)
at _130._1(IAuthenticationTokenProvider )
at _130.GetMetaData(IAuthenticationTokenProvider provider)
at _35._2(_28 , IAuthenticationTokenProvider , Boolean , EventHandler`1 , EventHandler`1 , EventHandler`1 , EventHandler`1 )
--- End of inner exception stack trace ---

Bart Read

Whoops! I know exactly why that's happening. We branched the SQL Compare engine, which we use for our meta-data storage, so that we could make some changes to trim down the memory footprint. This necessitated a change in the snapshot file format because there's now some information that we no longer store.

Problem is that this can cause issues when you try to read a snapshot created with 3.1. I thought I'd handled all these scenarios but clearly not. I'm going to add versioning into the cache for the final release, or possibly an RC 2, to get round this issue, however there are two ways you can fix it for now.

(1) Open an editor and immediately open the SQL Prompt Cache Management dialog before connecting to any databases. Then just click "Delete All" to clear out your cache.

(2) You can manually delete the offending files by navigating to the SQL Prompt settings directory, which is probably called something like "C:\Documents and Settings\{username}\Local Settings\Application Data\Red Gate\SQL Prompt 3" and deleting any files with a ".dbcache" extension. You don't need to delete the "SQL Prompt Meta Data.cache" file although if you then go into the Cache Management dialog SQL Prompt will display the cached databases you just deleted but they'll just be empty cache entries so it's not a worry.

SQL Prompt 3.5 would need to recache your databases in any case so you don't lose anything by doing either of these things.

Hope that helps and sorry for the hassle.


Thanks,
Bart

donwiid

Thanks Bart, great to see such a quick and reasoned response.

Bart Read

No problem. I'm working on sorting that little gem out now.
Cheers,
Bart

Bart Read

Hi Don,


I've now fixed this by adding versioning to the cache. Any files created with a version of SQL Prompt earlier than 3.5 will be deleted on upgrade from a previous version. You don't lose anything because of this due to the fact that we'd need to recache the metadata anyway.

This will be available in the SQL Prompt 3.5 final release.


Thanks,
Bart