Good day. I didn't know where else to post this, so forgive my ignorance if this isn't the place.
I have a situation that involves a single Windows Server with SSMS installed that has the only permissions to hit another organization for our Data Warehousing team. There may be as many as 4 BIDS plus us three DBA's and architects all logged in on that server. The BIDS work in extremely complex queries at times. The SSMS client is installed on E: drive, yet when they get doing work, at times, the C: drive fills up with data, and is immediately released when I ask them to log off.
Any thoughts on how to remedy this?
March 7, 2023 at 6:46 pm
Do you know where SSMS is storing temp files? I believe the default is %USERPROFILE%\AppData\Local\Temp.
I just ran a really stupid query, returning millions of rows to the screen and it created a large temp file on the C drive which persisted until I closed the query window, or cleared the results.
There are free tools that map out the space use on a disk and make it easy to find causes of excess file use. On our old dev box I used to have to navigate to the users folder to see who was using too much space (often I was the culprit), then ask them to delete/move files or close queries. You may find that one or two users are keeping large datasets on the screen, or opening 50+ query windows. SSIS projects might also be creating temp files. I would have a look around to see what folders get bigger through the day.
Where do users keep their projects and query files, or source control repos? If those are on the C drive that might be the root cause of the problem. Download folders are often a problem too. I would look to see what can be moved from the C drive so that the SSMS activity is less relevant. We ended up adding another swap file on a different drive and shrinking the one on C. I assume this is bad practice, but it was a dev box that was critically short of C drive space.
One thing you could check the location of the TEMP and TMP environmental variables. If they are set to C you could change them to another drive with more space.
https://superuser.com/questions/949560/how-do-i-set-system-environment-variables-in-windows-10
March 7, 2023 at 8:41 pm
Is TempDB on the C Drive? Is it growing/filling the drive?
Or is it actually SSMS temp files mentioned previously?
Check/tune their queries if possible.
March 7, 2023 at 8:47 pm
Thanks folks, that was the ticket. I made all of our BIDS their own temp space on our much larger drive, and had them repoint their environment variables to there. Seems fine now. Will keep this thread posted of any issues.
March 7, 2023 at 8:50 pm
Do you know where SSMS is storing temp files? I believe the default is %USERPROFILE%\AppData\Local\Temp.
I just ran a really stupid query, returning millions of rows to the screen and it created a large temp file on the C drive which persisted until I closed the query window, or cleared the results.
There are free tools that map out the space use on a disk and make it easy to find causes of excess file use. On our old dev box I used to have to navigate to the users folder to see who was using too much space (often I was the culprit), then ask them to delete/move files or close queries. You may find that one or two users are keeping large datasets on the screen, or opening 50+ query windows. SSIS projects might also be creating temp files. I would have a look around to see what folders get bigger through the day.
Where do users keep their projects and query files, or source control repos? If those are on the C drive that might be the root cause of the problem. Download folders are often a problem too. I would look to see what can be moved from the C drive so that the SSMS activity is less relevant. We ended up adding another swap file on a different drive and shrinking the one on C. I assume this is bad practice, but it was a dev box that was critically short of C drive space.
No way to mark both you and the other person after you both as the answer, but thank you for the great reply.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply