On our SQL Server agent, a job usually takes 30 minutes everyday to finish. Today, it ran for 3 hours and 41 minutes and then failed due to "Could not allocate space for object 'dbo.SORT temporary run storage: 140907480547328' in database 'tempdb' because the 'PRIMARY' filegroup is full"
Now I understand that the tempdb got full and the job failed. But the job is set to make 2 more attempts and on the 2nd attempt this job finished in 31 minutes without this failure.
My questions around this problem is:
1) Why didn't it fail the 2nd time around?
2) If it ran out of space on the 1st attempt, why did it wait for 4 hours to do that when it usually finishes in 31 minutes?
Any ideas around these questions would be very helpful.
Thanks, R.
The database usually has separate file groups for Data, your DBA will try and ensure that PRIMARY is not the default filegroup. It's best practice to keep the Primary Filegroup small because it contains SYS tables and SQL will want to access them faster.
This error usually occurs when someone accidentally creates an Index in the Primary filegroup.
Ensure that all indexes on the table are on the correct filegroup.
Your DBA should be able to help you.