0

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.

rvphx
  • 2,324
  • 6
  • 40
  • 69
  • it can go through a lot of swapping/cleaning/whatever throughout a lot of time to attempt to make space.... if you're working on a disk that houses say, your transaction log, and it hasn't been backed up in some time, could cause your job to fail until the transaction log is backed up (which would then clear up some disk) - this is just an example – Kritner Dec 14 '16 at 18:48
  • Thanks for that explanation. This problem does not occur on a daily basis and sometimes it goes for a week or so without any problems. – rvphx Dec 14 '16 at 18:49

1 Answers1

0

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.

pacreely
  • 1,881
  • 2
  • 10
  • 16