Geek Answers Handbook

Limit the size of the result set returned to SSMS

I have a set of users who have access to SSMS on Prod. I cannot deny their access, but each time they will start a request large enough so that the temporary file takes up all the free space on C: \ and crashes on the server.

I am looking for a way to limit the size of the result set returned to any SSMS connection to less than 2 GB or some limitation.

I had a problem in all versions from 2005 to 2014, so solutions on any or all of these versions would be welcome.

Currently, the only thing I have in mind to mitigate this problem is the Nagios warning for large temp result files.

FYI I know very well how bad this is, so I would rather not discuss why this situation exists.

+3
source share
1 answer

Oooh. Good.

Step 1 is a limitation that user users can cause damage through system configuration. Typically, when you configure SQL Server, you want to have 4 different partitions:

  • OS
  • Paging
  • Tempdb
  • input
  • Data

Doing this not only improves performance, but also prevents automatic copying of logs or TempDB in your OS partition. You only kill an instance of SQL Server when you run out of disk, not the OS itself. As a first step, I will create a new partition for your Temp DB and move it:

Temp db move example

, , . SQL Server Resource Governor, , , .

MSDN

, , . , , . , , ( ), (--), , , .

+1

More articles:


All Articles