Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Tempdb and version store clarification needed. Expand / Collapse
Author
Message
Posted Monday, March 31, 2014 3:52 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 23, 2014 3:52 AM
Points: 32, Visits: 343
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?
Post #1556400
Posted Monday, March 31, 2014 8:50 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 3:00 PM
Points: 1,194, Visits: 2,226
When TempDB runs out of space, SQL Server forces the version stores to shrink. So increase space for the tenpDB.

--
SQLBuddy
Post #1556510
Posted Monday, March 31, 2014 8:55 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 2:20 PM
Points: 17,940, Visits: 15,925
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
Post #1556513
Posted Monday, March 31, 2014 9:27 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 23, 2014 3:52 AM
Points: 32, Visits: 343
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
Post #1556540
Posted Monday, March 31, 2014 9:32 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 2:20 PM
Points: 17,940, Visits: 15,925
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
Post #1556546
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse