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.

SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.


SQL Server Agent Job History with Status, StartDate, EndDate and Duration


As a DBA we create various jobs and schedule them, these jobs may be related to maintenance tasks like Database Backups, Rebuild Indexes, Integrity Check, Application related jobs running queries or Stored Procedures, etc. In most of the environments there may be automated monitoring tools which raises an alert or ticket when there is any job failure and DBA’s take appropriate action.

Periodically, DBA’s may be asked for some reports related to jobs like management of Developers or other teams may require list of all jobs which were running since last 24 hours(or different threshold) along with their job run status, job start date and time, job end date and time and job run duration. There may be request to send this data regularly, so manually getting these details can take lot of time especially when there are many SQL Server instances and jobs.

Below is the script which can be used to generate a quick report from SQL Server Management Studio (SSMS) with list of job names, Job Step Name, Last Job Start Date, Last Job End Date, Last Job Run Status, Last Job Run Duration, and description of the job if available.

Declare @stdt datetime, @endt datetime;
Declare @temp_jobhistory table (JobName nvarchar(100),JobStepName nvarchar(100),StartDate datetime,EndDate datetime, RunStatus int)
Declare @query1 nvarchar(1024)
Set @Query1 = 
'SELECT j.Name, jh.Step_name,
CONVERT(DATETIME, RTRIM(jh.run_date)) + ((jh.run_time/10000 * 3600) + ((jh.run_time%10000)/100*60) +
(jh.run_time%10000)%100 /*run_time_elapsed_seconds*/) / (23.999999*3600 /* seconds in a day*/) AS Start_DateTime, 
CONVERT(DATETIME, RTRIM(jh.run_date)) + ((jh.run_time/10000 * 3600) + ((jh.run_time%10000)/100*60) +
(jh.run_time%10000)%100) / (86399.9964 /* Start Date Time */)
+ ((jh.run_duration/10000 * 3600) + ((jh.run_duration%10000)/100*60) + (jh.run_duration%10000)%100
/*run_duration_elapsed_seconds*/) / (86399.9964 /* seconds in a day*/) AS End_DateTime, run_status
from msdb..sysjobhistory jh, msdb..sysjobs j
where jh.job_id=j.job_id and 
CONVERT(DATETIME, RTRIM(jh.run_date)) + ((jh.run_time/10000 * 3600) + ((jh.run_time%10000)/100*60) +
(jh.run_time%10000)%100 /*run_time_elapsed_seconds*/) / (23.999999*3600 /* seconds in a day*/) >
ORDER BY  name asc, step_name asc,run_date desc,run_time desc'
print @query
Insert into @temp_jobhistory Exec sp_executesql @query1
select JobName,JobStepName,StartDate,EndDate,  
      WHEN RunStatus = 0 THEN 'Failed' 
      WHEN RunStatus = 1 THEN 'success' 
      WHEN RunStatus = 2 THEN 'Retry' 
      WHEN RunStatus = 3 THEN 'Canceled' 
      WHEN RunStatus = 4 THEN 'In progress' 
   END as JobStatus, 
DATEDIFF(minute,StartDate,EndDate) as 'DurationMinutes'
from @temp_jobhistory

Output would looks like below.









(Job outcome)

2014-11-01 23:25:00.003

2014-11-01 23:25:04.000



Perform Full Backup of All User Databases



2014-11-01 23:25:01.003

2014-11-01 23:25:03.000



Perform Full Backup of All User Databases


(Job outcome)

2014-11-02 04:30:00.000

2014-11-02 07:43:00.000



Performs statistics update of all databases.



2014-11-02 06:11:45.000

2014-11-02 06:28:49.000



Performs statistics update of all User databases.

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.

SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.


Maintenance Plan Cleanup Task is Not Deleting the Old Backup Files


It is common in most environments to have maintenance plans used to perform regular maintenance tasks like Full, Differential and Log Backups, Rebuild Indexes, Statistics Update and Integrity Check.

Backups are considered one of the most important task and proper care needs to be taken while creating the maintenance plans for performing the database backups in SQL Server. There are many options available while creating the backup maintenance plan and need to choose options correctly to avoid any failures or problems in future.

Although there can be various problems related to configuring backup maintenance plans, one of the small but difficult one to identify for many DBA’s seem to that they enable the task “Maintenance Cleanup Task” to delete old backups older than 3 days or so and they later identify the old backups are not getting deleted. The backup job will run successfully and does not report any errors in this case, the only way DBA’s identify the issue mostly is when the disk drive where backups are stored gets full or has grown to large size and alerts are raised.

Coming to the solution of this problem is mostly due to incorrect values provided to the “Maintenance Cleanup Task”. In the maintenance cleanup task, verify the option “File extension” and see if the extension of the backup to delete is correct, for example if backup task is creating the backups with extension .bak, make sure “bak” is mentioned, if for transaction log backups, extension is .trn, then make sure that the option provided is “trn”. It is important to note to use “bak”(without quotes) instead of “.bak” as using .bak will not delete the old backup files. So, make sure there is no .(Dot) infront of the extension bak or trn.

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.

SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.


SQL Server AlwaysON Availability Group Common Errors or Failures


AlwaysON Availability Groups has been introduced with SQL Server 2012 and has been a very popular and most used feature as it provides both High Availability (HA) and Disaster Recovery (DR) solutions. As it is a new feature, there are many issues which DBA’s face while configuring AlwaysON Availability Groups.

Below are some of the common errors or failures related to SQL Server AlwaysON Availability Groups.

Checking for compatibility of the database file location on the secondary replica resulted in an error. (Microsoft.SqlServer.Management.HadrTasks)
The following folder locations do not exist on the server instance that hosts secondary replica Node1\AGTest: S:\MSSQL11.AG1\SQL_DATA; Microsoft.SqlServer.Management.HadrTasks)

– This error occurs if the drive letters or the folder path does not match between primary and secondary replica.
– To resolve this error, make sure that same drive letter and folder path exists on both the servers or perform manual synchronization of secondary server databases using backup/restore.

Failed to create, join or add replica to availability group ‘AGTest’, because node ‘Node1’ is a possible owner for both replica ‘AGTest\AGTest’ and ‘Node1\AGTest1’. If one replica is failover cluster instance, remove the overlapped node from its possible owners and try again. (Microsoft SQL Server, Error: 19405)
– This error occurs, if SQL Server instance where you are setting up AlwaysON Availability Group is part of SQL Server failover clustering and has both nodes as possible owners and then You tried to add another availability Group for another SQL cluster instance involving these nodes.
– To resolve this error, either follow the solution suggested in the error message “If one replica is failover cluster instance, remove the overlapped node from its possible owners and try again” or instead of using cluster SQL instances, just use standalone instances.

Error while trying to connect to AlwaysON Availability group using Listener Name
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)

– This error occurs if the port number of the Listener is not 1433. To resolve this issue either use the port number along with the listener name like SQLAGLSTNR,1467 or create an alias.
– Make sure listener port is open to communicate. Test using telnet listenername portnumber
– Communication only works when using TCP network protocol.

Database Mirroring login attempt by user ‘Domain\ComputerName$.’ failed with error:
‘Connection handshake failed. The login ‘Domain\ComputerName$’ does not have CONNECT permission on the endpoint. State 84.’

– This error will be logged in SQL errorlog when trying to setup AlwaysON or Database Mirroring. This error occurs if the SQL Server services are running under local system accounts or if the SQL Service account does not have connect permission on the endpoint.
– To resolve the error, Change the SQL Server services to run under a domain account and then grant connect permission on endpoint to SQL Server service account.
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [Domain\SQLServiceAccount]

Summary for the replica hosted by ServerName\INSTANCE2 Replica mode: Asynchronous commit
This replica will use asynchronous-commit availability mode and support only forced failover
(with possible data loss).
Note: This is a Failover Cluster Instance. Failover Cluster Instances do not support AlwaysOn automatic failover.

– This error occurs, if you are using SQL clustered instances to setup AlwaysON Availability Group.
– Cluster SQL Server instance will not support the automatic failover in availability groups. If you want automatic failover, install SQL server instance as standalone instance.

Cluster network name resource ‘TestAG’ failed to create its associated computer object in domain ‘testdomain.com’ during: Resource online.The text for the associated error code is: A constraint violation occurred.Please work with your domain administrator to ensure that:
The cluster identity ‘SQLClus$’ has Create Computer Objects permissions. By default all computer objects are created in the same container as the cluster identity ‘SQLClus$’.The quota for computer objects has not been reached. If there is an existing computer object, verify the Cluster Identity ‘SQLClus$’ has ‘Full Control’ permission to that computer object using the Active Directory Users and Computers tool.

– This error occurs if Cluster Name Object does not have rights to create a new Virtual Network Name Object. Refer below article for instructions to prestage the Virtual Name Object.

Joining database on secondary replica resulted in an error.
Failed to join the database ‘AGTest’ to the availability group ‘AGGrpTest′ on the
availability replica ‘Replica2′. (Microsoft.SqlServer.Smo)
The connection to the primary replica is not active. The command cannot be processed.
(Microsoft SQL Server, Error: 35250 Level 16, State 7)

– Try below steps to fix the error
o Make sure that the alwaysON endpoint [Hadr_endpoint] is not blocked by firewall
o Verify and make sure that SQL Server service account of primary server is added as a login on all the secondary servers and vice-versa.
o If SQL Server service accunt is “Nt service\” or local system account then ensure system account (Domainname\systemname$) of each replica is added as a login to other replicas.
o Grant connect permission on alwaysON endpoints on each replicas for SQL Server service account of all other replicas On Secondary replica run below query by changing the domain and replica server names
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [Domain\replica1$]
On primary replica run below query by changing the domain and replica server names
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [Domain\replica2$]
o Make sure SQL Server name (select @@servername) matches with hostname of the server.
o Make sure cluster service startup account is added as SQL Server login.

Error while trying to setup AlwaysOn availability group
Operating System Error 1265(The system cannot contact a domain controller to service the authentication request. Please try again later.).

– This error can occur if the password of inter-domain trust account is not synchronized on both sides of the trust relationship.
– Refer below link to see if it helps fixing the error, else engage your domain Administrator.

This is applicable on below versions of SQL Server

SQL Server 2012
SQL Server 2014

Hope this was helpful.

SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.


SQL Server Management Studio (SSMS) Common Errors or Failures

SQL Server Management Studio (SSMS) by default gets installed on all servers where we install SQL Server database engine or related components. SQL Server DBA’s often use SSMS in order to manage the SQL Server instances for tasks like creating databases, creating logins, users and granting permissions, Creating maintenance plans or jobs, checking for any errors and troubleshoot various issues with SQL Server instance by running queries. SSMS has become one of the most important tool for a DBA to function their job. Most of the time, there wont be any errors with SQL Server Management Studio (SSMS), but at times, we may end up with some errors while using SSMS and we need to fix them quickly.

SQL Server Management Studio is dependent on many other components like Visual Studio, .Net Framework, etc, so changes to any of these components can also result in the failure or errors with SSMS.

Below are some of the common errors which we may see with SQL Server Management Studio (SSMS).
Error while trying to create new database diagram, which is crashing the SSMS with error
“Attempted to read or write protected memory. This is often an indication that other memory is corrupt. (Microsoft.VisualStudio.OLE.Interop)”

You can resolve this issue by following below steps
– Copy and replace the DLL “dsref80.dll” in below path from a machine where SSMS is working fine in to the machine where the problem exists.
C:\Program Files (x86)\Common Files\microsoft shared\Visual Database Tools\dsref80.dll
– Replace all the files in below folder using the files from another server where SSMS is working fine.
C:\Program Files (x86)\Common Files\microsoft shared\MSDesigners8\
– Close and Open SSMS.

Error in SQL Server Management Studio: Failed to start debugger. Additional information: The EXECUTE permission was denied on the object ‘sp_enable_sql_debug’, database ‘mssqlsystemresource’, schema ‘sys’. (Microsoft SQL Server, Error: 229)
– You may receive above error message if you try to use the Debug button in SSMS, if it was by mistake, then no need to worry, but if you want to use the debug option in SSMS, then make sure below are true.
o SQL Server Management Studio must be running under a Windows account that is a member of the sysadmin fixed server roll.
o The Database Engine Query Editor window must be connected by using either a Windows Authentication or SQL Server Authentication login that is a member of the sysadmin fixed server role.
o The Database Engine Query Editor window must be connected to an instance of the SQL Server 2008 Database Engine. You cannot run the debugger when the Query Editor window is connected to an instance that is in single-user mode.

Error while trying to start SQL Server 2012 Management Studio (SSMS)
Cannot find one or more components. Please reinstall the application.
Exception has been thrown by the target of an invocation

– You may receive above errors while trying to open SSMS after you install Visual Studio 11. Visual studio might have been installed separately or may have been installed as part of some other software installation.
– To resolve this issue, follow the below steps
o Open registry editor from Start -> Run -> Type regedit
o In registry Editor navigate to HKEY_CURRENT_USER\Software\Microsoft\SQL Server Management Studio
o Backup and Delete the key 11.0_Config
o Start the SQL Server Management Studio and it should not open without any more errors.
– Restart ssms.exe

Operation not supported on SQL Server 2005
– You may received above error if you are using SQL Server 2005 Management Studio and connected to higher version of SQL instance like SQL 2012 or SQL 2008 R2 and then use a feature which was introduced in these higher versions. SSMS 2005 cannot be used to use features introduced in higher versions of SQL Server, you need to install and use higher version of SQL Server management Studio(SSMS)

microsoft .net framework error.unable to read the list of previously registered servers on this system.Re-register your servers in the registered servers windowvalue cannot be null.parameter name:viewinfo
Microsoft.SqlServer.Management.Sdk.Sfc.SfcSerializationException: Serialization operation on
ServerGroup[@Name=’SqlServerCompactEditionServerGroup’] has failed.
System.IO.DirectoryNotFoundException: Could not find a part of the path ‘c:\winnt\temp\qhn8zgxb.tmp’.
at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)

– This error can occur if the user profile is corrupted. Normally we RDP to the server where SQL Server is installed using a domain account or local account and then open SSMS and then try to connect to SQL Server instance, if the profile of this domain or windows account is corrupt, then this error occurs.
– To resolve this error either create a new windows or domain account and login using the new account and connect to SSMS and then to SQL Server instance or repair the profile of the existing account.

The File %CommonDir%\dte80.olb could not be loaded. An attempt to repair this condition failed because the file could not be found. Please reinstall this program.”
The application cannot start

– This error can occur if one or more files related to SSMS is corrupt or missing. dte80.olb is a shared file used by the Visual Studio shell, Management Studio, Books Online, and the MSDN help viewer.
– To resolve this issue, either uninstall and reinstall the SSMS component or check if
C:\Program Files\Common Files\Microsoft Shared\MSEnv\dte80.olb file exists, if it does not exist, then copy this file from another server which has same SQL Server version and build, but better option is to reinstall the SSMS component.

Value cannot be null.
Parameter name: viewInfo (Microsoft.SqlServer.Management.SqlStudio.Explorer)

– There can be various reasons why you may see this error. Follow below to resolve this error
o Make sure that C:\ drive has enough free space.
o Launch SSMS by right click and choose run-as-administrator
o Create empty folder with name 2, if it does not exist in below path, so that you can navigate to below folders.
o Make sure from start>run>%temp% and start>run>%tmp% folders can be opened without any error, if it returns any error, contact your server administrator to fix the same.
o Apply latest Service pack or Cumulative Update
o Uninstall and reinstall SQL Server Management Studio

Evaluation Period has expired. For information how to upgrade your evaluation software please go to. Microsoft SQL Server Management Studio Complete (expires in x days)
– This error occurs, if you have installed SQL Server Management Studio or Database Engine using SQL Server Enterprise Evaluation Edition, which will expire after 180 days.
– You need to perform an Edition upgrade to an licensed version of SQL Server to fix the error.
– Sometimes, ever after successful edition upgrade, still you may receive this error, and to fix the error, set the CommonFiles registry key value to 3 under
HKLM\SOFTWARE\\Microsoft\Microsoft SQL Server\100\ConfigurationState and then rerun the edition upgrade.

Microsoft SQL Server Management Studio
Object reference not set to an instance of an object.

– Try below options to see if it resolves the error
o Repair the .net framework
o Open a command prompt by choosing run asa dministrator and then run below commands
C:\Program Files\Internet Explorer>regsvr32 /u ieproxy.dll
C:\Program Files\Internet Explorer>regsvr32 ieproxy.dll
C:\Program Files (x86)\Internet Explorer>regsvr32 /u ieproxy.dll
C:\Program Files (x86)\Internet Explorer>regsvr32 ieproxy.dll
regsvr32 actxprxy.dll
o Uninstall SSMS and reinstall SSMS

There are various other errors which cause failure of SSMS and will try to add as many as possible.

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.

SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

1 33 34 35