Ive got a job that is suddenly making tempdb DATA file grow by 50 gigs in
3-4 hours. When the drive starts to run out of room Im forced to do a DBCC
SHRINKFILE on the DATA file, but that also kills the job with the message
"Executed as user: domain\Administrator. Could not allocate new page for
database 'TEMPDB'. There are no more pages available in filegroup DEFAULT.
Space can be created by dropping objects, adding additional files, or
allowing file growth. [SQLSTATE 42000] (Error 1101). The step failed."
I had the data file set to grow at 10% and this happened. I changed it to 50 MB and it happened. I now changed it to 10 MB and will see if it happens again.
To get me through the weekend, is there a way I can shrink the file without
killing the job while it's running? I tried leaving 500 MB in it last time
but that didnt help.