We have a logging database that has been locked down from developer access. The reason it was locked down was because they say that people would run large queries and then leave the results up in Sql Server Management Studio (SSMS).

They read somewhere that doing this leaves the connection open and holds onto memory on the SQL Server.

I had never heard this, but if it is true, then I want to make sure I start closing my queries, especially when I run large ones.

Is it true that leaving a query open in SSMS will hold on to extra memory on the server? If so, is there a way around it?

  • Access has/had that issue. Pretty sure SSMS only would if a transaction was explicitly started and left hanging without a commit or rollback
    – Kevin3NF
    Nov 21 '16 at 18:48
  • The memory utilized is not significant but its really a good practice to close SSMS windows after your work is done
    – Shanky
    Nov 22 '16 at 7:29

The only significant memory that would be held would be if SSMS was being run on the server itself (and then you'd still be limited to the 2GB of memory space that SSMS can allocate).

There will be a very small amount of memory allocated to maintaining the connection in SQL, but that's truly trivial.

Of course, if you leave an open transaction, that's a whole other level of pain, but it doesn't sound as if that's the trouble your developers are trying to avoid (unless this is what they are trying to avoid, but came up with a PC way of telling them that).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.