CODEWITHSUNDEEP
sql-serversql-server-2012
LCJ
LCJ

Reputation: 22662

Check whether TempDB was ever out of space

I am using SQL Server 2012. Is there any DMV to verify whether TempDB was ever out of space? I am looking for a query that will provide the times on which tempdb was full.

Note: I already know that following query will provide current details.

USE tempdb
exec sp_spaceused

REFERENCES

  1. Troubleshooting Insufficient Disk Space in tempdb

Upvotes: 0

Views: 5425

Answers (3)

Shiva
Shiva

Reputation: 20935

Have you looked at the Server Activity History Report?

There's a section in the report devoted to tempdb in the Sql Server Activity Graph in the report. That might show the history of the disk space etc for tempdb in the past.

Different aspects of SQL Server activity can be accessed from the SQL Server Activity graph. The reports that you can obtain by clicking a point on the SQL Compilations/sec graph line are as follows:
Connections and sessions
Requests
Plan cache hit ratio
tempdb characteristics

Upvotes: 1

Rahul Tripathi
Rahul Tripathi

Reputation: 172458

You can check the error logs which will specify you that the temdb is full and you need to increase the space or better manage the space and also check Troubleshooting Insufficient Disk Space in tempdb

Upvotes: 1

Andomar
Andomar

Reputation: 238116

You can check the error logs for a message saying that tempdb tried to grow but couldn't.

The message should look soemthing like:

"Could not allocate space for object ... in database ... because the ... 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. "

Upvotes: 3

Related Questions