SSMS closes without warning during query run

Question

SSMS closes without warning during query run

Brandie Tarvin

SSC Guru

Points: 172995
More actions
September 19, 2013 at 8:01 am

#279999

Does anyone know why SSMS would close without warning during the middle of a query run?
It's done this to me twice this morning. I do have Statistics_IO and Statistics_Time and Include Actual Execution Plan on (which I usually have off), but it seems odd to me that that would be the only reason why SSMS shuts down.
No error BTW. The window just vanishes. I haven't checked the logs yet. Doing that after I finish posting. Would love to know if anyone else has seen this problem before.
Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Tom257 SSChampion Points: 11543 More actions · Answer 1

I haven't had this behaviour with SSMS but I have had it with Visual Studio and the XAML applications I'm working on and IE10 - but only on this new laptop I'm using - which has a solid state disk. (Otherwise its a standard windows 7 machine) The apps that just close without warning are the ones I'm using all the time, I'd kind of assumed SSDs were a bit flaky and when part of it fails it just marks that area 'bad', reassigns the affected areas somewhere else and carries on, leading to the occasional essential dll needing to be relocated.

This is pure conjecture though.

Brandie Tarvin SSC Guru Points: 172995 More actions · Answer 2

My box doesn't have SSDs, so that's probably not it for me... Thanks for the info, though.

EDIT: I am using Windows 7 x64 (just got a new box) with SQL 2008 SP2... I know I have another problem with SSMS (Job Activity Monitor) that just started as soon as I got upgraded...

I wonder if it's related to the OS.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

crmitchell SSCarpal Tunnel Points: 4557 More actions · Answer 3

This can happen if something on your system corrupts the memory used by SSMS. It suggest that some application installed on your system has a memory leak or otherwise accesses memory incorrectly.

I suppose that a badly written CLR stored proc could do so but I'd suspect some external application would be more likely.

MikeRen Ten Centuries Points: 1295 More actions · Answer 4

This doesn't help much, but I'm running Windows 7 Pro x64 and am regularly using SSMS from SQL 2008 R2 and I don't have the reported issue.

Conclusion: I don't think it's the OS.

Mr. Kapsicum SSCertifiable Points: 6128 More actions · Answer 5

visual studio does this kind of thing few times,,,

bt never witnessed SSMS doing this.

Looking forward to see the solution for ur problem.

Brandie Tarvin SSC Guru Points: 172995 More actions · Answer 6

It's not doing it all the time, which is what confuses me. And now that I think about it, I did see this behavior with SQL 2005 and 2008 on my XP machine, but very rarely.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Sean Pearce SSCoach Points: 15999 More actions · Answer 7

I have had this before and it was due to SSMS running out of memory. My experience of it was when I had SET NOCOUNT OFF while doing a batch update, basically millions of updates. I could see the memory usage for SSMS climb until it just had enough. I also noticed page faults climbing at the same time.

SSMS doesn't like it when it has to print loads of messages.

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]

Brandie Tarvin SSC Guru Points: 172995 More actions · Answer 8

Sean Pearce (9/20/2013)
SSMS doesn't like it when it has to print loads of messages.

Which would explain why it did it after I set Statistics Time and Stitistics IO on AND THEN added the Execution Plan to the query...

Thanks, Sean.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Sean Pearce SSCoach Points: 15999 More actions · Answer 9

Try the following code and watch the memory usage for SSMS in task manager. Make sure you have saved all your work 😉

WHILE 1=1

BEGIN

PRINT '(1 row(s) affected)';

END;

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]

Ed Wagner SSC Guru Points: 287024 More actions · Answer 10

I've seen this very occasionally when I've left SSMS open too long. I rarely close it, so every so often it lets me know I should close it once in a while.

I just experienced something new this morning. I had remoted in last night and did some work and everything was fine. I came in today and the window wouldn't respond. It would take the focus when I clicked the icon in the start bar, but nothing inside the top-level window responded to anything. I used Process Explorer and everything looked fine from the system perspective. I eventually had to kill it. Of course, a new instance functioned normally. I just found this curious. Good thing I save often. 🙂

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Tom257 SSChampion Points: 11543 More actions · Answer 11

Sean Pearce (9/20/2013)
Try the following code and watch the memory usage for SSMS in task manager. Make sure you have saved all your work 😉
WHILE 1=1
BEGIN
PRINT '(1 row(s) affected)';
END;

What the heck ... Its Friday afternoon

Lasted about 4 minutes then crashed with. 'The program has stopped working' used about 750 Mb

Windows 7 64bit with 8Gb RA0m

Tom257 SSChampion Points: 11543 More actions · Answer 12

Sean Pearce (9/20/2013)
Try the following code and watch the memory usage for SSMS in task manager. Make sure you have saved all your work 😉
WHILE 1=1
BEGIN
PRINT '(1 row(s) affected)';
END;

What the heck ... Its Friday afternoon

Lasted about 4 minutes then crashed with. 'The program has stopped working' used about 750 Mb

Windows 7 64bit with 8Gb RA0m

Steven Willis SSCrazy Eights Points: 9893 More actions · Answer 13

Brandie Tarvin (9/19/2013)
Does anyone know why SSMS would close without warning during the middle of a query run?
It's done this to me twice this morning. I do have Statistics_IO and Statistics_Time and Include Actual Execution Plan on (which I usually have off), but it seems odd to me that that would be the only reason why SSMS shuts down.
No error BTW. The window just vanishes. I haven't checked the logs yet. Doing that after I finish posting. Would love to know if anyone else has seen this problem before.

Check the folder where the .tmp files are saved. I suspect you'll find hundreds of zero byte files that you can just delete and that should fix your problem.

mar1jxm Ten Centuries Points: 1303 More actions · Answer 14

I have seen these type of cases when a Query Designer contains a SELECT INTO statement on a big table or it has several general T-SQL statements (IF, DROP , Cursor... etc). Make sure your Graphical Query Designer is not used for these types of things.

Good Luck!

Jacob Milter

Database Manager

UPS

Jacob Milter