Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Tempdb and version store clarification needed.


Tempdb and version store clarification needed.

Author
Message
rarara
rarara
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 419
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?
sqlbuddy123
sqlbuddy123
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1212 Visits: 2243
When TempDB runs out of space, SQL Server forces the version stores to shrink. So increase space for the tenpDB.

--
SQLBuddy
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21113 Visits: 18259
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

rarara
rarara
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 419
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
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21113 Visits: 18259
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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search