TEMPDB Getting FULL

Question

Hello,
We have Sql Server 2012 and suddenly TEMPDB getting Full when User running the report.
Before Problem started, report was running fine even it's just retrieving under 100 records.
I have started to trace the session and it's coming from one of the application and mostly it's similar kind of query text I found which causing an issue.
Currently whenever it's happening (everyday), either I have to kill the session or start the Sql service to release the space.
Query it's coming from application so i might not have liberty to fix the query if it's really causing as not sure what's the exact issue.
Is it any way I can tell that Query optimization issue or it should be Index issue?
How can i pinpoint to underlying issue to overcome the TEMPDB problem.
I have added Temp file to make it same number of Processor, Growth and initial size is standard.
If It's Index fragmented? What exactly I should be looking when i am running Fragmented Index query?
Is it AvgFragmentationInPercent% will be more reliable or AvgFragmentSizeInPages or NumberOfFragments?

I am getting following errors:

E:\MSSQL12.MSSQLSERVER\MSSQL\DATA\templog.ldf: Operating system error 112(There is not enough space on the disk.) encountered.

another message:
Could not allocate space for object '<temporary system object: 422215107411968>' 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 the filegroup, or setting autogrowth on for existing files in the filegroup.

Message
Insufficient space in tempdb to hold row versions. Need to shrink the version store to free up some space in tempdb. Transaction (id=2441927 xsn=753751 spid=85 elapsed_time=14052) 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.

Thanks for your quick help!

Answer 1

Thanks Everyone for your valuable feedback and suggestions, Adding index helped great.

Appreciate your time and help!

Answer 2

If you know that this is a specific query what is causing issue, you will need to work with it. Analyse the query plan and see if you can add any indexes to save the show. Hints can also be induced with a plan guide (but that is not fun at all), or through quey store on more recent versions.

Index fragmentation has nothing to do with it. (But an index rebuild may change things, since that will cause a statistics update which could be beneficial.)

Or get more disk space so that you can increase the size of tempdb so it is adapted to the workload.

Answer 3

Thanks Erland.
I have already found the query but it's from 3rd part application so not sure how easily i can tune the query but looking into Estimated Execution plan, I see one of Index SEEK in plan having 57% cost and that Index seek using ID column so I have created Non cluster Index on.
Index Rebuild we have already Weekly job but doesn't looks like it has much impact as last week end it's already ran.
I don't think so increasing Disk space will help as this was running fine for many months but it's just started to make trouble from last week.
Any suggestion what could be changed or need to more dig into any specific area?

For fragmented index, any tips what exactly needs to be check like any pages related info apart from avg Fragmentation%?

Thanks for your help!

Answer 4

Hi @pdsqsql ,

> If It's Index fragmented? What exactly I should be looking when i am running Fragmented Index query?

Suggest you check the value of avg_fragmentation_in_percent. After the degree of fragmentation is known, use the following table to determine the best method to remove the fragmentation:

Please refer to Resolve index fragmentation by reorganizing or rebuilding indexes.

Best regards,
Cathy

---

If the response is helpful, please click "Accept Answer" and upvote it.