SQL Server TempDB Common Errors or Failures
SQL Server instance has system databases Master, Model, MSDB and TempDB used for managing and functioning of the SQL Server instance. All these databases needs to be online and accessible for successful functioning of SQL Server and SQL Server Agent services. There can be various error with these system databases which may bring down the SQL Server instance and causes service outage.
In this post we will look at some of the common errors with TempDB database which causes outage or problems to SQL Server instance thus causing problems to the User Applications. TempDB database as name says holds temporary system and user tables which will persist only till SQL Server is restarted. Every time SQL Server instance is restarted, tempdb will be recreated and any old data will be lost, so never create any user tables or objects in tempdb.
Below are some of the common errors with tempdb database.
Error Message: 1105 Could not allocate space for object ‘dbo.SORT temporary run storage: 140759625302016’ 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
– This error occcurs if there is no more free space left inside a tempdb data files. This can occur if the tempdb does not have auto-growth enabled or if auto-growth is set to a size which is filled and new requests needs additional space in tempdb or if the disk drive holding tempdb files runs out or space.
– When this error occurs, check the initial size and auto-growth settings of tempdb file and if it too small, then increase the file size. It is important to estimate the tempdb usage by capacity planning and set the tempdb data file initial size and set the auto-growth settings accordingly.
– If the tempdb has grown large while runninn a particular query or Stored Procedure or a job, then we need to tune the query to limit the usage of tempdb of that particular query.
– Below article should be helpful in troubleshooting insufficient free space with tempdb.
Error: 5242, Severity: 22, State: 1.
An inconsistency was detected during an internal operation in database ‘tempdb'(ID:2) on page (1:74532). Please contact technical support. Reference number3. Warning: Fatal error 5243 occurred at Jan 01 2012 11:05PM. Note the error and time, and contact your system administrator.
Attempt to fetch logical page (1:408) in database 2 failed. It belongs to allocation unit.
SQL Server detected a logical consistency-based I/O error: incorrect pageid. It occurred during a read of page in database ID 2 at offset in file
– Apply latest service pack or Cumulative updates to the SQL Server instance.
– Follow general tempdb best practices like adding additional tempdb files as per CPU cores ratio, testing and using trace flag -T1118,
– Try disabling Auto Update Statistics and Auto Create Statistics options for tempdb database.
– Set exclusions in Anti-virus for SQL Server files (.mdf, .ndf, .ldf, .bak, .trn, and sqlservr.exe)
– Try moving tempdb files to a new dedicated disk drive.
– This error can occur if there is any corruption with the tempdb database, so check for any corruption on disk on caused by any drivers. Consider scheduling down time and ask System admin team to run full hardware diagnostics to detect any potential issues.
Clearing tempdb database.
Error: 17053, Severity: 16, State: 1.
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\tempdb.mdf: Operating system error 1224(The requested operation cannot be performed on a file with a user-mapped section open.) encountered.
Error: 1802, Severity: 16, State: 4.
CREATE DATABASE failed. Some file names listed could not be created. Check related errors. Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.
– This error can occur if the tempdb data(.mdf, .ndf) or log(.ldf) files are being locked by some other process. In order to identify the process which is locking the tempdb files, you would need to use tool like process monitor.
– Ensure Anti-Virus tools are set to ignore database files from scanning.
Error while opening properties of tempdb database
Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AS” in the equal to operation. (.Net SqlClient Data Provider)
– This error occurs if the collation of the Model database and other system databases like Master, Tempdb are different than of Model.
– To fix this error you need to rebuild the SQL Server instance by specifying the required collation for the system. You cannot have different collations or system databases.
– You can use below article to rebuild the system databases
Error: 5123, Severity: 16, State: 1.
CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file ‘D:\TempdbPath\tempdb.mdf’.
– This error occurs if the path to tempdb files does not exist. In this case the path D:\TempdbPath did not exist which resulted in this error.
Error: 3147, Severity: 16, State: 1.
Backup and restore operations are not allowed on database tempdb
– You cannot perform backup or restore operations on tempdb database. Make sure that the maintenance plans or custom jobs ignore backup of tempdb.
– Tempdb is recreated everytime SQL Server instance is restarted, so no need to worry about backing up tempdb and importantly do not store permanent objects in tempdb as this is system database used by SQL Server to store temorary data which is not required to be persisted after SQL Server restart.
Error: 3958, Severity: 16, State: 1.
Transaction aborted when accessing versioned row in table TestTable in database ‘dbo.TestDB’. Requested versioned row was not found. Your tempdb is probably out of space. Please refer to BOL on how to configure tempdb for versioning.
– This error can occur if Snapshot Isolation Level is enabled on databases which will cause increase in the usage and size of tempdb file to store the version data.
– You would need to estimate the amount of tempdb size required with Snapshot Isolation Level enabled and set the tempdb database file size accordingly.
– Also, if there are any large operations causing long running transactions will keep the versions in tempdb for longer time, so need to break the transactions into smaller size.
This is applicable on below versions of SQL Server
SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
Hope this was helpful.
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.