March 31, 2014 at 3:52 am
I am finding that a user running a daily search ([font="Times New Roman"]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[/font]) through an application is giving these type of errors:
[font="Times New Roman"]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[/font]
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?
March 31, 2014 at 8:50 am
When TempDB runs out of space, SQL Server forces the version stores to shrink. So increase space for the tenpDB.
--
SQLBuddy
March 31, 2014 at 8:55 am
Do you need to have an isolation level in use that causes row-versioning and thus causes use of the VS in tempdb?
If not, try changing the isolation level.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 31, 2014 at 9:27 am
unfortunately not - we upgraded the app recently and the isolation level was necessary as part of that version change. rather strangely there are 3 instances and only 1 has the problem, despite the search running (that seems to be the cause) being fairly general.
i'm not sure if additional indexing on the table in question my help? i think it would only improve performance, since it will return as many records as it needs to return, but as long as the order-by remains, it will need to do the sort within tempdb
March 31, 2014 at 9:32 am
An index in that case can occasionally help. It's worth the try.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply