I am finding that a user running a daily search (SELECT T1.* FROM TABLE T1 WHERE T1.acct = 5 AND T1.dep LIKE @P1 AND (T1.state = 3 OR T1.state = 4 OR T1.state = 7) AND (T1.flag = @P2 OR T1.flag = @P3) ORDER BY T1.r_group) through an application is giving these type of errors:
2014-03-31 09:26:46.130 spid142 Error: 3967, Severity: 17, State: 1.
2014-03-31 09:26:46.130 spid142 Insufficient space in tempdb to hold row versions. Need to shrink the version store to free up some space in tempdb. Transaction (id=119256131 xsn=29386698 spid=142 elapsed_time=436) has been marked as victim and it will be rolled back if it accesses th
2014-03-31 09:26:46.140 spid142 Error: 1105, Severity: 17, State: 2.
2014-03-31 09:26:46.140 spid142 Could not allocate space for object 'dbo.SORT temporary run storage: 1412361313320' 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
So I understand the search they are doing is on a huge table and includes (automatically by the application it seems) an 'order by' clause at the end, which would explain why tempdb may not be able to cope. I am not certain though why the version store message also initially appears. Am I correct in thinking that the portion of tempdb for VS is a fixed amount? Is it proportional to tempdb as a whole? I am seeing version store reach a max of about 2MB during my observations.
Is there anything I can do here?