July 13, 2016 at 3:32 am
few days back we have moved to 2014 from 2012 server and we kept the same settings as earlier like we are using 8 datafiles in Tempdb for better performance and all placed in single drive.As this is datawarehouse environment batch loads are running continuously and tempdb drive is getting full.Even though 98% free space in Database it could not able reuse the space and throwing disk is full.
Is there any specific settings for tempdb to avoid these issues in SQL Sever 2014??
Insufficient space in tempdb to hold row versions. Need to shrink the version store to free up some space in tempdb. Transaction (id=239368387 xsn=1273322 spid=126 elapsed_time=1590) has been marked as victim and it will be rolled back if it accesses the version store. If the problem persists, the likely cause is improperly sized tempdb or long running transactions. Please refer to BOL on how to configure tempdb for versioning.
Any recommendation for tempdb performance improvement like enabling trace flag -T1118 ??
Our server specifications are below.
SQL Server detected 4 sockets with 15 cores per socket and 30 logical processors per socket, 120 total logical processors; using 120 logical processors based on SQL Server licensing.512 GB RAM.
July 13, 2016 at 7:36 am
1) What build of 2014 are you on?
2) Version store is in play. Did you add in Always On by any chance? Was some form of snapshot isolation used in the previous 2012 warehouse? It would seem that long-running transactions could be preventing version store cleanup.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 13, 2016 at 11:50 pm
Hi Kevin,
1.It is SQL Server 2014 with SP1(12.0.4100.1)
2.We are not using Always ON and we kept default settings regarding isolation level which is read committed in 2012 and 2014.even we are suspecting the long running transactions causing the issue...but the same was running in 2012 but never took all the data files (8)more than 64 GB ,but now even 460 GB not enough ??
and we are planning below changes
1)adding startup parameter -T1118
2)we are using Netapp(NTFS) storage where allocation unit size currently 4 MB to 64 MB.
Please suggest we can go ahead with above changes ? Or any other changes to improve the tempdb performance.
July 14, 2016 at 1:02 am
Have you set a high value as initial Size for TempDB Files?
July 14, 2016 at 1:53 am
No Kevin ...Its just 1 GB I have set and with 500MB auto growth/Unlimited and all files are growing till 60 GB + and throwing disk is full.And always available space it showing as 98 %.
July 14, 2016 at 3:20 am
Is there any particular job running?
You can shrink the DB but make sure no connection to TempDB
July 14, 2016 at 7:20 am
Your error message explicitly states that row versions are in play. Since you said no snapshot isolation, look into trigger usage, MARS usage or online indexing:
https://technet.microsoft.com/en-us/library/ms175492(v=sql.105).aspx
A simple web search of sql server version store is full finds this:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 18, 2016 at 12:20 am
After implementing -T1118 issue has been resolved.Thanks for your suggestions.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply