Troubleshooting Insufficient Disk Space in tempdb

Tempdb is a SQL Server system database that's a global resource available to all users. SQL Server uses tempdb to complete internal database operations. Tempdb is used for temporary user objects and internal database engine operations, including joins, aggregations, cursors, sorting, hashing, and row versioning. Unlike the data in a typical user database, the data in tempdb isn't persisted after the SQL Server instance is shut down.

Troubleshooting Insufficient Disk Space in tempdb :

Error messages that indicate insufficient disk space in the tempdb database. 

Error

1101 or 1105 Any session must allocate space in tempdb.

3959        The version store is full. This error usually appears after a 1105 or 1101 error in the log.

3967        The version store is forced to shrink because tempdb is full.

3958 or 3966 A transaction cannot find the required version record in tempdb.

The following examples show how to determine the amount of space available in tempdb, and the space used by the version store and internal and user objects.

Determining the Amount of Free Space in tempdb :

The following query returns the total number of free pages and total free space in megabytes (MB) available in all files in tempdb.

SELECT SUM(unallocated_extent_page_count) AS [free pages], 

(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]

FROM sys.dm_db_file_space_usage;

Determining the Amount Space Used by the Version Store :

The following query returns the total number of pages used by the version store and the total space in MB used by the version store in tempdb.

SELECT SUM(version_store_reserved_page_count) AS [version store pages used],

(SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB]

FROM sys.dm_db_file_space_usage;

Determining the Longest Running Transaction:

If the version store is using a lot of space in tempdb, you must determine what is the longest running transaction. Use this query to list the active transactions in order, by longest running transaction.

SELECT transaction_id

FROM sys.dm_tran_active_snapshot_database_transactions 

ORDER BY elapsed_time_seconds DESC;

A long running transaction that is not related to an online index operation requires a large version store. This version store keeps all the versions generated since the transaction started. Online index build transactions can take a long time to finish, but a separate version store dedicated to online index operations is used. Therefore, these operations do not prevent the versions from other transactions from being removed. For more information, see Row Versioning Resource Usage.

Determining the Amount of Space Used by Internal Objects :

The following query returns the total number of pages used by internal objects and the total space in MB used by internal objects in tempdb.

SELECT SUM(internal_object_reserved_page_count) AS [internal object pages used],

(SUM(internal_object_reserved_page_count)*1.0/128) AS [internal object space in MB]

FROM sys.dm_db_file_space_usage;

Determining the Amount of Space Used by User Objects:

The following query returns the total number of pages used by user objects and the total space used by user objects in tempdb.

SELECT SUM(user_object_reserved_page_count) AS [user object pages used],

(SUM(user_object_reserved_page_count)*1.0/128) AS [user object space in MB]

FROM sys.dm_db_file_space_usage;

Determining the Total Amount of Space (Free and Used):

The following query returns the total amount of disk space used by all files in tempdb.

SELECT SUM(size)*1.0/128 AS [size in MB]

FROM tempdb.sys.database_files

Monitoring Space Used by Temp Tables and Table Variables :

You can use an approach similar to polling queries for monitoring the space used by temp tables and temp variables. Applications that acquire a large amount of user data inside temp tables or temp variables can cause space use problems in tempdb. These tables or variables belong to the user objects. You can use the user_objects_alloc_page_count and user_objects_dealloc_page_count columns in the sys.dm_db_session_space_usage dynamic management view and follow the methods described earlier.

Error: 1807 Could not obtain exclusive lock on database ‘model’. Retry the operation later

Error:

TITLE: Microsoft SQL Server Management Studio
------------------------------
Create failed for Database 'Test'.  (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1539+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Database&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Could not obtain exclusive lock on database 'model'. Retry the operation later.
CREATE DATABASE failed. Some file names listed could not be created. Check related errors. (Microsoft SQL Server, Error: 1807)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=1807&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------

Solution:

Solution 1:
            Disconnect and Reconnect your SQL Server Management Studio’s session. Your error will go away.

Solution 2:

Root Cause: Root cause of this error may be one of the following reason

1. Somebody exclusively open the Model database in different session
2. If more than one create database statements attempt to run simultaneously

How to identity:

Use master 
GO
IF EXISTS(SELECT request_session_id  FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID('Model'))
PRINT 'Model Database being used by some other session'
ELSE
PRINT 'Model Database not used by other session'

So we can identify that whether the Model database being execlusively used by any session.., If you found anybody using the Model database execlusively, You can identify what kind of statement being used by the session...using the script given below

SELECT request_session_id  FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID('Model')


The script will return the session ID (i.e: 52)
We have to identity what kind of query/activity being performed by this session(52).

DBCC InputBuffer(52) 

    EventInfo column returns the query performed, Based on that, you have to decide whether the Session ID(52) can be Aborted/ Not. If you want to abort the session(52),
run the command 
    Kill 53 

    Now you can try to create a new Database..!  

Useful DBCC Commads

1. DBCC CHECKDB

Checks the logical and physical integrity of all the objects in the specified database by performing the following operations:

Runs DBCC CHECKALLOC on the database.

Runs DBCC CHECKTABLE on every table and view in the database.

Runs DBCC CHECKCATALOG on the database.

Validates the contents of every indexed view in the database.

Validates link-level consistency between table metadata and file system directories and files when storing varbinary(max) data in the file system using FILESTREAM.

Syntax:

DBCC CHECKDB

[

[ ( database_name | database_id | 0

[ , NOINDEX

| , { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ]

) ]

[ WITH

{

[ ALL_ERRORMSGS ]

[ , EXTENDED_LOGICAL_CHECKS ]

[ , NO_INFOMSGS ]

[ , TABLOCK ]

[ , ESTIMATEONLY ]

[ , { PHYSICAL_ONLY | DATA_PURITY } ]

}

]

]

Example:

-- Check the current database.

DBCC CHECKDB;

GO

-- Check the AdventureWorks database without nonclustered indexes.

DBCC CHECKDB (AdventureWorks, NOINDEX);

GO

More Info: http://msdn.microsoft.com/en-us/library/ms176064.aspx

2. DBCC CHECKTABLE

Check the integrity of all the pages and structures that make up the table or indexed view.

Syntax:

DBCC CHECKTABLE

(

table_name | view_name

[ , { NOINDEX | index_id }

|, { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD }

]

)

[ WITH

{ ALL_ERRORMSGS ]

[ , EXTENDED_LOGICAL_CHECKS ]

[ , NO_INFOMSGS ]

[ , TABLOCK ]

[ , ESTIMATEONLY ]

[ , { PHYSICAL_ONLY | DATA_PURITY } ]

}

]

Example:

USE AdventureWorks;

GO

DBCC CHECKTABLE ("HumanResources.Employee");

GO

More Info: http://msdn.microsoft.com/en-us/library/ms174338.aspx

3. DBCC CHECKALLOC

Check the consistency of disk space allocation structures for a specified database.

Syntax:

DBCC CHECKALLOC

[

( database name | database_id | 0

[ , NOINDEX

| , { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ]

)

[ WITH

{

[ ALL_ERRORMSGS ]

[ , NO_INFOMSGS ]

[ , TABLOCK ]

[ , ESTIMATEONLY ]

}

]

]

Example:

-- Check the current database.

DBCC CHECKALLOC;

GO

-- Check the AdventureWorks database.

DBCC CHECKALLOC (AdventureWorks);

GO

More Info: http://msdn.microsoft.com/en-us/library/ms188422.aspx

4. DBCC SHOWCONTIG

Display fragmentation information for the data and indexes of the specified table or view.

Syntax:

DBCC SHOWCONTIG

[ (

{ table_name | table_id | view_name | view_id }

[ , index_name | index_id ]

) ]

[ WITH

{

[ , [ ALL_INDEXES ] ]

[ , [ TABLERESULTS ] ]

[ , [ FAST ] ]

[ , [ ALL_LEVELS ] ]

[ NO_INFOMSGS ]

}

]

Example:

USE AdventureWorks;

GO

DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES;

GO

More Info: http://msdn.microsoft.com/en-us/library/ms175008.aspx

5. DBCC DBREINDEX

Rebuild one or more indexes for a table in the specified database.

Syntax:

DBCC DBREINDEX

(

table_name

[ , index_name [ , fillfactor ] ]

)

[ WITH NO_INFOMSGS ]

Example:

USE AdventureWorks;

GO

DBCC DBREINDEX ("HumanResources.Employee", " ", 70);

GO

More Info: http://msdn.microsoft.com/en-us/library/ms181671.aspx

6. DBCC INDEXDEFRAG

Defragment indexes of the specified table or view.

Syntax:

DBCC INDEXDEFRAG

(

{ database_name | database_id | 0 }

, { table_name | table_id | view_name | view_id }

[ , { index_name | index_id } [ , { partition_number | 0 } ] ]

)

[ WITH NO_INFOMSGS ]

Example:

DBCC INDEXDEFRAG (AdventureWorks, "Production.Product", PK_Product_ProductID)

GO

More Info: http://msdn.microsoft.com/en-us/library/ms177571.aspx

7. DBCC SHOW_STATISTICS

DBCC SHOW_STATISTICS displays current query optimization statistics for a table or indexed view. The query optimizer uses statistics to estimate the cardinality or number of rows in the query result, which enables the query optimizer to create a high quality query plan.

Syntax:

DBCC SHOW_STATISTICS ( table_or_indexed_view_name , target )

[ WITH [ NO_INFOMSGS ] < option > [ , n ] ]

Example:

USE AdventureWorks;

GO

DBCC SHOW_STATISTICS ("Person.Address", AK_Address_rowguid) WITH HISTOGRAM;

GO

More Info: http://msdn.microsoft.com/en-us/library/ms174384.aspx

8. DBCC DROPCLEANBUFFERS

Remove all clean buffers from the buffer pool.

Syntax:

DBCC DROPCLEANBUFFERS [ WITH NO_INFOMSGS ]

Example:

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS

More Info: http://msdn.microsoft.com/en-us/library/ms187762.aspx

9. DBCC OPENTRAN

Display information about the oldest active transaction and the oldest distributed and nondistributed replicated transactions, if any, within the specified database.

Syntax:

DBCC OPENTRAN

[

( [ database_name | database_id | 0 ] ) ]

{ [ WITH TABLERESULTS ]

[ , [ NO_INFOMSGS ] ]

}

]

Example:

DBCC OPENTRAN

More Info: http://msdn.microsoft.com/en-us/library/ms182792.aspx

10. DBCC SQLPERF()

Provide transaction log space usage statistics for all databases. It can also be used to reset wait and latch statistics.

Syntax:

DBCC SQLPERF

(

[ LOGSPACE ]

|

[ "sys.dm_os_latch_stats" , CLEAR ]

|

[ "sys.dm_os_wait_stats" , CLEAR ]

)

[WITH NO_INFOMSGS ]

Example:

DBCC SQLPERF(LOGSPACE)

More Info: http://msdn.microsoft.com/en-us/library/ms189768.aspx

11. DBCC TRACEON

Enable the specified trace flags.

Syntax:

DBCC TRACEON ( trace# [ ,...n ][ , -1 ] ) [ WITH NO_INFOMSGS ]

Example:

DBCC TRACEON (3205);

GO

More Info: http://msdn.microsoft.com/en-us/library/ms187329.aspx

12. DBCC TRACEOFF

Disable the specified trace flags.

Syntax:

DBCC TRACEOFF ( trace# [ ,...n ] [ , -1 ] ) [ WITH NO_INFOMSGS ]

Example:

DBCC TRACEOFF (3205);

GO

More Info: http://msdn.microsoft.com/en-us/library/ms174401.aspx

13. DBCC UPDATEUSAGE

Reports and corrects pages and row count inaccuracies in the catalog views. These inaccuracies may cause incorrect space usage reports returned by the sp_spaceused system stored procedure.

Syntax:

DBCC UPDATEUSAGE

( { database_name | database_id | 0 }

[ , { table_name | table_id | view_name | view_id }

[ , { index_name | index_id } ] ]

) [ WITH [ NO_INFOMSGS ] [ , ] [ COUNT_ROWS ] ]

Example:

DBCC UPDATEUSAGE (0);

GO

More Info: http://msdn.microsoft.com/en-us/library/ms188414.aspx

14. DBCC TRACESTATUS

Display the status of trace flags.

Syntax:

DBCC TRACESTATUS ( [ [ trace# [ ,...n ] ] [ , ] [ -1 ] ] )

[ WITH NO_INFOMSGS ]

Example:

DBCC TRACESTATUS (2528, 3205);

GO

More Info: http://msdn.microsoft.com/en-us/library/ms187809.aspx

15. DBCC SHRINKDATABASE

Shrink the size of the data and log files in the specified database.

Syntax:

DBCC SHRINKDATABASE

( database_name | database_id | 0

[ , target_percent ]

[ , { NOTRUNCATE | TRUNCATEONLY } ]

)

[ WITH NO_INFOMSGS ]

Example:

DBCC SHRINKDATABASE (AdventureWorks, TRUNCATEONLY);

More Info: http://msdn.microsoft.com/en-us/library/ms190488.aspx

16. DBCC SHRINKFILE

Shrinks the size of the specified data or log file for the current database or empties a file by moving the data from the specified file to other files in the same filegroup, allowing the file to be removed from the database.

Syntax:

DBCC SHRINKFILE

(

{ file_name | file_id }

{ [ , EMPTYFILE ]

| [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]

}

)

[ WITH NO_INFOMSGS ]

Example:

USE UserDB;

GO

DBCC SHRINKFILE (DataFile1, 7);

GO

More Info: http://msdn.microsoft.com/en-us/library/ms189493.aspx

17. DBCC CHECKIDENT

Checks the current identity value for the specified table and, if it is needed, changes the identity value. You can also use DBCC CHECKIDENT to manually set a new current identity value for the identity column.

Syntax:

DBCC CHECKIDENT

(

table_name

[ , { NORESEED | { RESEED [ , new_reseed_value ] } } ]

)

[ WITH NO_INFOMSGS ]

Example:

USE AdventureWorks;

GO

DBCC CHECKIDENT ("HumanResources.Employee");

GO

More Info: http://msdn.microsoft.com/en-us/library/ms176057.aspx

18. DBCC CLEANTABLE

Reclaims space from dropped variable-length columns in tables or indexed views.

Syntax:

DBCC CLEANTABLE

(

{ database_name | database_id | 0 }

, { table_name | table_id | view_name | view_id }

[ , batch_size ]

)

[ WITH NO_INFOMSGS ]

Example:

DBCC CLEANTABLE (AdventureWorks,"Production.Document", 0)

WITH NO_INFOMSGS;

GO

More Info: http://msdn.microsoft.com/en-us/library/ms174418.aspx

19. DBCC USEROPTIONS

Returns the SET options active (set) for the current connection.

Syntax:

DBCC USEROPTIONS

[ WITH NO_INFOMSGS ]

Example:

DBCC USEROPTIONS;

More Info:http://msdn.microsoft.com/en-us/library/ms180065.aspx

20. DBCC CHECKCONSTRAINTS

Check the integrity of a specified constraint or all constraints on a specified table in the current database.

Syntax:

DBCC CHECKCONSTRAINTS

[

(

table_name | table_id | constraint_name | constraint_id

)

]

[ WITH

[ { ALL_CONSTRAINTS | ALL_ERRORMSGS } ]

[ , ] [ NO_INFOMSGS ]

]

Example:

USE AdventureWorks;

GO

DBCC CHECKCONSTRAINTS ("Production.CK_ProductCostHistory_EndDate");

GO

More Info: http://msdn.microsoft.com/en-us/library/ms189496.aspx

21. DBCC CHECKCATALOG

Check for catalog consistency within the specified database. The database must be online.

Syntax:

DBCC CHECKCATALOG

[

(

database_name | database_id | 0

)

]

[ WITH NO_INFOMSGS ]

Example:

DBCC CHECKCATALOG (AdventureWorks);

GO

More Info: http://msdn.microsoft.com/en-us/library/ms186720.aspx

22. DBCC CHECKFILEGROUP

Check the allocation and structural integrity of all tables and indexed views in the specified filegroup of the current database.

Syntax:

DBCC CHECKFILEGROUP

[

[ ( { filegroup_name | filegroup_id | 0 }

[ , NOINDEX ]

) ]

[ WITH

{

[ ALL_ERRORMSGS | NO_INFOMSGS ]

[ , TABLOCK ]

[ , ESTIMATEONLY ]

[ , PHYSICAL_ONLY ]

}

]

]

Example:

USE AdventureWorks;

GO

DBCC CHECKFILEGROUP;

GO

More Info: http://msdn.microsoft.com/en-us/library/ms187332.aspx