February 16, 2011 at 4:28 pm
I have been getting several severity 17 alerts in my SQL environment about the primary filegroup in tempdb being full, example below
DESCRIPTION:Could not allocate space for object 'dbo.SORT temporary run storage: 141282124169216' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
I was a little suspicious, I have my tempdb allocated for 96gb and in the few times I received this alert during working hours I could never figure out what process or job was causing this to happen. I decided to create a companion alert that would fire off a few queries to find active processes and email me the results when tempdb's free space dropped below 10%. The object for my alert is 'MSSQL:transactions', counter 'free space in tempdb (KB), alert if counter 'falls below' value '10065920' which works out to be ~9.6gb. Since I created this alert it hasn't been triggered once but I continue to receive the severity 17 alerts about the primary filegroup being full in tempdb. Anybody have any ideas what might be going on here?
February 16, 2011 at 7:51 pm
February 17, 2011 at 9:43 am
thanks I had read that link prior to posting, it doesn't really give me an explanation as to why my 2nd alert that should be triggered when < 10% free space remains in tempdb isn't running. My goal here is to identify which process/user/job is causing the severity 17 errors and I thought my plan of having the 2nd alert would achieve that.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply