Management Studio freezing after writing a few lines of code
-
Hi all
I've got a bit of an odd scenario.
We're using SSMS 2012 against an 2012 Business Intelligence SQL install.
Sometimes (it appears to happen at random), SSMS will freeze (you can't type, manipulate Object Explorer, minimize or close the session).
The only add-on we've put in is Apex Software's Auto-Complete module.
I've tried uninstalling this (when SSMS is working) but the freeze still happens.I'm using a windows 7 Pro SP1 laptop with 8GB RAM and an i5 processor.
This time, it's been frozen for about 15 minutes (I usually force-close the session using Task Manager).Anyone else come across this problem?
Anyone any ideas on what to do (apart from re-install which I've tried)?::EDIT::
This is the error I get on a semi-regular basis.
Sometime it lasts for a few minutes, sometimes a lot longer. - richardmgreen1 - Tuesday, April 4, 2017 5:54 AMHi all
I've got a bit of an odd scenario.
We're using SSMS 2012 against an 2012 Business Intelligence SQL install.
Sometimes (it appears to happen at random), SSMS will freeze (you can't type, manipulate Object Explorer, minimize or close the session).
The only add-on we've put in is Apex Software's Auto-Complete module.
I've tried uninstalling this (when SSMS is working) but the freeze still happens.I'm using a windows 7 Pro SP1 laptop with 8GB RAM and an i5 processor.
This time, it's been frozen for about 15 minutes (I usually force-close the session using Task Manager).Anyone else come across this problem?
Anyone any ideas on what to do (apart from re-install which I've tried)?::EDIT::
This is the error I get on a semi-regular basis.
Sometime it lasts for a few minutes, sometimes a lot longer.Are you connecting to a heavily used server? This happened to me with servers that have lots of objects and heavy workload. This is because there are requests being made by SSMS to get information about the objects.
At least, that's my experience. -
How big is the database?
I don't know Apex, but I know Redgate SQL Prompt. We sometimes have trouble when the database is very large. Same thing could be possible from Apex.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Luis - the server is hardly used at the moment (it will get a bit more usage when various things go live).
Grant - It's around the 60GB mark so nothing outlandish.
Bizarrely, SSMS also freezes on different servers accessing different databases (not sure if that helps or not).
-
By size in this case, I should have been more clear, how many objects?
If it's freezing on other databases, it's possibly Apex (don't intend to throw rocks here).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
There are a good couple of hundred tables, views, etc.
I'll contact Apex and see what they say.
I've just done a repair install of SSMS and it's behaving itself (for the time being).
-
I've also seen this issue when I leave SSMS open for weeks at a time or when my system gets low on resources. You say you have 8GB of RAM total, when that message pops up, how much is free, how much is cached, and how much is available?
I find when my "free" gets too low, everything gets laggy even though available is still pretty high.
If you wait long enough, does it start behaving normally again? if so, what do your resources look like at that point?The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
The laptop is restarted every night (I set off a restart as I'm leaving so it's clean for when I get in the following morning).
When I've checked Task Manager, I've only checked the Memory "stack graph" on the Performance tab and it's usually got around 3GB free. -
Are others experiencing similar issues?
Is your version of SSMS compatible with your version of Apex?
Is your SSMS version the same as the SQL Server version you are connecting to?
Does the freezing occur if you do a fresh install of SSMS (even a different version, just for testing purposes)?I have SSMS 2005, 2008, 2012 and 2016 all installed on my computer and I use all of them depending on what I am connecting to. I find that mismatched versions work IF SSMS is newer than SQL Server, but matching versions up works better. Not sure how many people have tried looking at (and working with) SSIS catalogs. Those you NEED to match the SSMS and SQL versions up or you will get odd errors when deploying and horrible delays if SSMS is newer than the SSIS server.
Also, when it freezes, do any other processes on the computer use up a lot of CPU, memory or disk I/O? Could be something else (like the antivirus) hogging all of your resources.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. - richardmgreen1 - Tuesday, April 4, 2017 8:59 AM
Do that. That's not very big. I was worried about thousands or more. That messes us up.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
I occasionally have similar issues with SSMS 2016, more so than with previous versions, but I see you're posting in 2012 forum. The following will work in any case.
While SSMS is in it's frozen state, shell out from Windows to a DOS command prompt and use SQLCMD to run SP_WHO2 and look for blocked processes, SPIDs where where the BlkBy column contains another SPID. The -o parameter can be used to output result to a log file. If you have a dedicated admin connection, then use the -A option, because it can work around some resource contention issues that would normally block a regular connection.
https://docs.microsoft.com/en-us/sql/tools/sqlcmd-utilityFor example:
sqlcmd -S myserver -E -d master -Q "sp_who2" -o myserver-debug.log
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
-
Thanks all
Haven't had a chance to contact APEX as yet, but I'll do that this week (not entirely sure when).
I'll also check out the SP_WHO2 (I can launch another session of SSMS and that seems to work fine short-term (and then freezes like the first one).I'm using SSMS2012 and connecting to a 2012 instance (either BI or Dev).
-
I've just had it freeze again, and managed to run the sp_who2 command using sqlcmd.
Looking at the output file, I can't see anything in the BlkBy column.
As far as I could see, there's nothing else running on this server (i.e. no-one else is running anything).
::edit::
Just turned off the Auto-Complete from APEX and (so far) SSMS is behaving itself.I like having that option as it completes the table name and gives it an alias.
If, as current evidence suggests, it is the APEX software, is there any freeware out there that will do the same?
All I want it something that will complete table names, give them aliases and (this would be a big bonus) allows me to select fields from a table (after typing in the alias) using tick-boxes. - bmg002 - Tuesday, April 4, 2017 9:55 AM
I've noticed similar problems with addons in general, it is like SSMS wants to play by itself in its own sandbox 🙁
As far as using different SSMS versions for their relative database version counterparts, I've noticed, depending on what I'm doing, I need to use a matched version; otherwise, I run into strange issues at times.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
One of the greatest tragedies in life is to lose your own sense of self and accept the version of you that is expected by everyone else. -
I've never noticed any issues using mis-matched ssms and SQL versions.
I just find I needed to remember that certain commands don't exist in previous versions (like CAST(XXX as DATE) in 2005).
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply