Archive for the ‘SQL Errors’ Category

How to Fix Microsoft SQL Restore Database Error 3154

November 23, 2018 Leave a comment

“Yesterday I made backup of my SQL database and tried to restore my database. But, unfortunately my database has been deleted, then, I tried to re-create a new database with common names. I thought this will work, but this message “The backup set holds a backup of a database other than the existing database error 3154” encountered. Now, I am stuck in a very difficult situation and unable to figure out what to do next. I do not even know how to fix SQL database error 3154. Kindly suggest me a feasible approach to resolve it.”

Are you getting SQL Restore database error 3154? Needs an appropriate solution to fix it? Do not worry, you are landed at the right page. In this blog, we are going to explain different methods that will help you to resolve SQL error 3154. Before moving towards the solution, let us understand all possible reasons that are responsible for SQL database error 3154.

SQL Restore Database Error 3154 – Reason

When we analyze the root cause of an error, it becomes easy that the name of database they want to restore the backup set and database of backup is same. Although the difference is CREATE DATABSE statement between two databases. This simply means that the similarity is of name, but the database is completely different from each other. Follow the possible reasons given below:

  • For log shipping, more than one database was configured.
  • Transaction logs for databases were backed up to similar folder.
  • The only difference between both database names is “_tlog”. For example, xyz_test and xyz_test_tlog.

Fix Restore Database is Terminating Abnormally. (Microsoft SQL Server, Error: 3154)

Go through the following methods that will help to resolve SQL Database error 3154:

Approach 1: Using T-SQL command

You can resolve this SQL error with the help of T-SQL command:

a) Use WITH REPLACE while using RESTORE command.

b) Delete old database which is conflicting and again restore with the help of RESTORE command.

c) You just take an Example :

Approach 2: Using SQL Server Management Studio (SSMS)

If you want to do same with SSMS, follow the steps given below:

  • Create the database with your preferred name
  • Then, right click on database and select Tasks option
  • In the next step, navigate to Restore and choose Database option
  • Under the restore screen, select “Overwrite the existing database (WITH REPLACE)” checkbox
  • Click OK button. It should restore the database successfully.

Note: When you restore the database WITH REPLACE it will overwrite an existing database.

Fix SQL Database Error 3154 – Feasible Solution

Most people face challenges while implementing the manual method. Thus, to overcome all the problems faced by non-technical users while executing the manual steps, it is recommended to opt for an instant solution. One such application is SQL Backup Recovery Tool. With this utility, users can repair corrupt SQL Server bak file without any risk or data loss. The tool is capable enough to repair both primary (.mdf) and secondary (.ndf) SQL database. It is having user-friendly interface, with some useful features.

Time to Conclude

SQL Server is used by several users across the globe to manage and store their crucial data. While restoring the SQL database, the user may encounter an error message “The backup set holds a backup of a database other than the existing database Error 3154”. Therefore, in this blog, we have discussed the step-by-step process to fix SQL Restore Database Error 3154. But, at times, it becomes very difficult for a user to implement the manual steps. Thus, users can make use of a commercial software that is SQL backup Recovery to repair a corrupt bak file.

Categories: SQL Errors

SQL Server Error 3117: Restore Database is Terminating Abnormally

SQL Server being a largely used relational database management system regularly encounters issues resulting in errors. One such errors, is the “restore database is terminating abnormally SQL error 3117” which has been causing a lot of trouble for the SQL Server users worldwide. The segment talks about the error as well as the reason due to which it occurs. Moreover, the workaround to overcome the consequences of this error have been discussed in the form of suggestions regarding the restore procedure and state to be used.

The Perpetrators of Microsoft SQL Server Error 3117

When the transaction log backup is tried, being restored to the same database it has been taken from, this error has high chances of taking place. SQL error 3117 clearly states that ‘no files are ready to rollforward’. This happens because; the log file that you are trying to restore is already available in the database. You can take the help of third party tool to view SQL transaction log.

TIP: In order to carry out the restoration process your way, you will have to perform the restoration of full backup of the database that had been taken right before the transaction log backup.

Recovery vs. NoRecovery State: Difference in Restoration

NORECOVERY state is to ensure that rollback does not take place. This state continues the roll forward along with the following statement mentioned in the sequence.

The restore sequence in this case helps restore other backups and then roll them forward.

RECOVERY (default) is the state that indicates that a rollback must only be performed once the roll forward has completed for the backup currently being restored.

Database recovery procedure demands the entire set of data that is to be restored (roll forward set) is in a consistent state with the database. The Database Engine issues an error 3117 in a condition where the data set is not rolled forward despite when it was meant to be to a state far enough to make it consistent enough with the database while RECOVERY is specified.

NOTE: In a condition like the one discussed above, one needs to make sure that the target database is not in a functional mode while the differential backup is being restored as this act can interrupt the procedure.

Restoring Differential Backup Post Full Backup Restore

To avoid the situation of coming across Microsoft SQL Server error 3117 would be to restore full backup with NORECOVERY and differential backup WITHRECOVERY.

FROM DISK = 'C:\DatabaseNameFull.bak'

FROM DISK = 'C:\DatabaseNameDiff.bak'


When queries are generated, they are executed manually. Therefore, the chances of failure become high due to a non-automated approach being made. Similarly, other technical failures tend to take place resulting in restore database is terminating abnormally Microsoft SQL Server error 3117. Technical glitches are common when working with big data concept because of the load, processing and mass data involved. However, dealing with the issue carefully by understanding the cause of its occurrence helps overcome it precisely without taking rounds. When dealing with the restore issue, most DBAs suggest NORECOVERY as the roll forward set to restore backup without a failure encountered. Drop in a comment just in case of further queries regarding the same issue in restoring SQL server database. A suitable solution will be shared via response in the soonest possible time to help deal with the situation at hand.

Troubleshooting Steps For Corrupt or Suspect Databases


Sometimes, while working on SQL Server, users receive various error messages like SQL Server Error 5172 due to encounter of suspect or corruption in databases. This happens because of operational mistakes or faulty hardware, which can be resolved by using the various actions for corrupt or suspect databases.

Solution for Corrupt or Suspect Database

There are some recommended actions for corruption or suspect databases that help users in handling a situation as discussed below. However, before using these actions users must ensure to have backup strategy that helps to recover the data from failures.

    • ERRORLOG File

Firstly, check the ERRORLOG file for SQL Server to find the occurrence of an error. Many times, the data or log file is missing due to which, there are chances of suspect or corruption of databases. During the startup of SQL Server, it will help to encounter the problem.

    • NO_INFOMSG Option

User can run the DBBCC CHECKDB against the databases to find the reasons of the occurrence of an error message by showing the error number. There might some specific recommendation for error message. This option is helpful as it return only error message such as:



If the log file is missing then, user cannot run DBCC CHECKDB to perform last log backup. Instead of this, users can use SELECT name, state_desc, database_id from databases. It will help them to more to understand the problem. With this, they can see RECOVERY_PENDING if any database is missing or hindering in SQL Server from executing automatic recovery at startup. It will show all the status for database.

    • Hardware Diagnostics

To remove the occurrence of an error, users can run check errorlog, eventlog. If still the error occurs then, they can perform the hardware diagnostics. It will help to resolve the corruption of database issue. If there is some problem detected then, ensure the replacement of faulty hardware.

    • Tail Log Backup

The tail log backup contains the transaction log backup, which is backed up most recently. It helps to recover right up to the point of disaster. It makes easy for users as they get all those log records back that was corrupted.

    • Repair Command

If the database is not suspected then, run DBCC CHECKDB with REPAIR command. It is a secondary option because it will result in data loss. This command will repair and solve the issue or it will result in loss of whole data.

    • Enable Checksum

In a way to detect the corruption issue, users can enable the CHECKSUM verification. It helps them to give the timely alerts when any error is occurred because SQL Server is a robust to check for other disk errors.


  • Never detach the suspect database otherwise, all chances are lost to have backup.
  • Depending upon the error, manually rebuild non-cluster indexes, drop, and reload table if data is static.


The recommended actions for corrupt or suspect databases help to bring back the data in accessible mode. However, users cannot repair a database of SQL Server in case of unavailable or not updated backup.

Help! I have -2, -3, or -4 Session ID!

June 21, 2016 Leave a comment

We can kill a session by using KILL command.  However, KILL command requires a positive number; executing KILL with negative number returns an error:

Msg 6101, Level 16, State 1, Line 1
Session ID -4 is not valid.

In order to kill the session ID, you need to find the unit of work (UOW) guid.

SELECT DISTINCT(request_owner_guid) AS UOW
  FROM sys.dm_tran_locks
 WHERE request_session_id IN (-2,-3,-4)

Now you can kill this using UOW:

KILL 'D5499C66-E398-45CA-BF7E-DC9C194B48CF'

Like all normal transactions, killing a session causes any work performed by it to be rolled back to bring the database back into consistent state.

The negative session ID are orphaned or stuck sessions that SQL Server; they are rare occurrences. Most often the only one I have seen is -2; what do they mean?

Session ID Description
 -2 The blocking resource is owned by an orphaned distributed transaction.
-3 The blocking resource is owned by a deferred recovery transaction.
-4 Session ID of the blocking latch owner could not be determined due to internal latch state transitions.

Reference: Books Online, sys.sysprocesses (Transact-SQL)

This post is cross posted on my SQLCAN Blog, MSDN Blog, and SQL server Consultation blog.

Know How to Resolve Microsoft SQL Server Error 5172

May 26, 2016 1 comment


SQL Server is a relational database management system, developed by Microsoft that is used for storage and retrieval of data whenever required. Database on SQL Server contains one master data file (MDF) and may be associated with number of new data file (NDF). Each MDF file is attached with a separate transaction log database (LDF). Sometimes, when user tries to attach the MDF/LDF database file, an Error may be encountered displaying error message “The header for file ‘xxx.mdf’ is not a valid database file header. The FILE SIZE property is incorrect. (SQL Server Error 5172)”The page will be discussing about the same error and solution on how to resolve MS SQL Error 5172.

What is Microsoft SQL Server Error 5172?

One of the most common error seen by the user of SQL Server while they are trying to attach the MDF or LDF Database from existing system to a new system. User needs to copy the database from the source system onto an external drive, attached the external drive to target system and copy them onto new system. Some of the possible causes of this error are damaged or corrupt log file/data file, attaching higher version database to a lower version SQL Server etc.

Precautions to Avoid MS SQL Error 5172

  • The Database files of the source system should only be copied when SQL Server is properly shutdown or Database is detached.
  • The Release and build number of SQL Server must be either exactly same for both source & target or the target must be higher. A database detached from SQL 2005 version needs to be attached to the same or higher version only.
  • Presence of UNICODE type database attached to the target server, that user no longer wants to use. It should either be detached or renamed or delete it permanently.
  • Attach the database file copied from source to target system using the sp_attach_db command or use SQL Server Management Studio while SQL Server is running. No files of an existing database should be replaced while SQL Server is down.

How to Fix SQL Server Header Error 5172

SQL Server Database files store their data as pages, each page is 8KB. The first page is called file header page containing the most important information about the whole file, like file signature, file size, compatibility, etc. Error occurs when certain value in header information in the boot page (page0) of LDF/MDF file is incorrect. It is also possible that file header page is damaged/corrupted making it unrecognizable by Microsoft SQL Server.

The user cannot attach the database without fixing the file header page manually. Let us consider a sample header of page 0:

Error 5172 Header

Some of the modifications that need to be made in the above header file using Hex Editor are:

  • Value at offset 0X00 (header version) should be 0X01
  • Value at offset 0X01 (m_type) should be between 0X01 and 0X66
  • Value at offset 0x04 (m_flagBits) should not be 0x02
  • Value at offset 0x18 (m_objId) should be 0x63 or higher
  • Combined value at offset 0x1E and 0x1F (m_freeData) should be 0x60 or higher (m_freeData is the offset where the next record will be placed)
  • Page size (0x2000)– m_SlotCnt * 4 should be higher than m_freeData. If not, any subsequent operation will overwrite already existing data & is disaster to SQL Server and users.
  • Offset 1C-1D (m_freeCnt) should be less than 0x1FA0 (8096). m_freeCnt denotes available free bytes within page, if it is greater then, SQL Server has reason to drop further execution because this will end up outside the 0x2000 boundary.
  • value 0x1FA0 (8096) is derived from page size (8192) – total header byte size (0x60) = 96 = 8192 – 96 = 8096
  • Offset 1E-1F (m_freeData) should not be zero
  • Offset 16-17 (m_SlotCnt) should less than 0xFD0 (4048)
  • The value at offset 0x40 thru 0x5F should be zero

After the changes made in the entries of header file using Hex Editor, user can try to re-attach the SQL Server Database files to overcome the issue of SQL Server Error 5172.


The blog will be discussing about one of the most common error encountered while user tries to attach their SQL Server Database files. The in-depth details of the SQL Server header error 5172 with the possible causes have been described. Some of the precautions that should be kept in mind are also discussed. The Error occurs when the header of the database file is invalid, which is why we need to make some modifications in the header file entries using Hex Editor.

The above elaborated description is sufficient enough, but if it fails to fix The header for file ‘xxx.mdf’ is not a valid database file header. The FILE SIZE property is incorrect then you can go with SQL Database Recovery Tool which allows you to recover data from corrupt database file.

sys.foreign_keys does not have matching row in sys.indexes

November 19, 2015 Leave a comment

Running DBCC CHECKDB you are getting following error message:

Check Catalog Msg 3853, State 1: Attribute (referenced_object_id=194099732,key_index_id=7) of row (object_id=2040565179) in sys.foreign_keys does not have a matching row (object_id=194099732,index_id=7) in sys.indexes

This error means, that Unique key constraint (index_id 7) in the primary table (object_id 194099732) is missing, which was referenced by child table’s FK constraint (FK object_id 2040565179).  This should not happen, SQL Server will not allow you to drop a constraint that is referenced by FK.  If attempted should get following error message:

Msg 3723, Level 16, State 6, Line 1
An explicit DROP INDEX is not allowed on index ‘dbo.a.NonClusteredIndex-20151119-085219’. It is being used for FOREIGN KEY constraint enforcement.

So if we are suppose to get errors? Why do we have corruption; simple answer, someone be making updates to system tables directly, which is not allowed or supported!

Actually we are not able to update system tables in SQL Server 2005+ (ref), however in SQL 2000 days, we had setting called allow updates in sp_configure options.  Also supported by the KB2787112.

So question is how do you fix it?

First, identify the child table name from sys.foreign_keys:

SELECT object_name(parent_object_id) AS TableName
FROM sys.foreign_keys
WHERE name = 'FK_b_a'

Second, script our constraint definition:

  1. Find the table, we got in SQL Statement above.
  2. Go to Keys.
  3. Right click on FK constraint name.
  4. Script Key As.
  5. Create To.
  6. New Query Window.

Third, drop the FK constraint:

ALTER TABLE [schema].[tablename] DROP CONSTRAINT [fk_constraint_name]

Fourth, Re-create the constraint, with script generated in Step 2.

If it was issue of someone playing around in system tables, this should resolve it.  However, if you get error similar to below:

Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table ‘dbo.a’ that match the referencing column list in the foreign key ‘FK_b_a’.

This means, that the key is missing in parent table and appropriate index needs be created before FK constraint can be created.  Since SQL doesn’t allow the index to be dropped there most likely are other corruption issues that have gone unnoticed.  If that is an issue, you will have to rely on your backups for recovery.

This post is cross posted on my SQLCAN Blog, MSDN Blog, and SQL server Consultation blog.

Error 17182: SQL Service Error

May 30, 2010 Leave a comment

While fixing SQL Server configuration I ran into some rather interseting/unique errors:

Server local connection provider failed to listen on [ \\.\pipe\sql\query ]. Error: 0x50
Error: 17182, Severity: 16, State: 1.

TDSSNIClient initialization failed with error 0x50, status code 0x50
Error: 17182, Severity: 16, State: 1.

TDSSNIClient initialization failed with error 0x50, status code 0x1.

As the error indicates that it can’t listen to named piped connection.  [1] pointed me to looking if multiple instances were installed which was not the case.  But looking at the network protocols in SQL Server Manager found Named Pipes were disabled.  Enabled the Named Pipes *poof* went the error.


  1. SQL Protocols. Microsft SQL Server Protocol Team. TDSSNIClient initialization failed with error 0x50, status code 0x50. Link.

CREATE FILE encountered operating system error 5(Access is denied.)

April 23, 2010 7 comments

When trying to attach the database to SQL Server with an account other then yours you might get following error.

TITLE: Microsoft SQL Server Management Studio

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

For help, click:


An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)


CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file ‘D:\MSSQL10.MSSQLSERVER\MSSQL\DATA\DBName.mdf’. (Microsoft SQL Server, Error: 5123)

For help, click:



If you follow the link it explains nothing; a little confused I did search on the Google (yeeh?).  With no answers as to why this is happening, I made sure SQL Server had proper permissions to the MSSQL Data and Log folders.  I decided to run Process Monitor to see what service account is trying to access the file when I am attaching it; it was as expected my SQL Server Service account was trying to access the file.  So why the permission denied?  File was located in same folder as rest of mdf/ldf files…

Looking into it I saw the file had a small lock icon on it like “Lock Icon“. Looking into the permissions on the file I see it was only accessible by my user name.  I don’t know why this was the case (actually I found why, I’ll post on it tomorrow; need to do a bit more research on that topic).  I edited the permission properties on the file to inhert form parent folder.

Reattached the database with no issues.

Issue: Can’t attach mdf and ldf file to the SQL Server, keeps getting permissions deined even though file is in default data/log directory and SQL Server has access to it.
Solution: Check file to make sure it is inheriting permissions from parent and is not owned by single user.

Unable to Delete Records in using API Cursors

March 11, 2010 1 comment

I am not very familer with the internals of API Cursors; so this was an annoying problem to solve.  I was working with web guy, he had a user working on Web Front-End application.  When user tried to delete record from the database they got following error:

Microsoft OLE DB Provider for SQL Server error ‘80004005’ 
Login failed for user ‘Domain\ServiceAccount’.

I thought it was a typical error so I my first thought was it will be easy problem.  Looking at SQL logs I had following error logged for this error:

Login failed for user ‘Domain\ServiceAccount’. [Client:]
Error: 18456, Severity: 14, State: 8

State 8, means that the user login in is not a SQL Login account, but is trying to login in like one.  With this information in hand I talked to web guy and asked him to confirm his application pool credentials and connection string for me.  He confirmed for me the application pool and connection string are not using the ‘Domain\ServiceAccount’; so we were confused as to where it was getting this account from.  The IP listed in second error message gave us an idea where to look.  It was the SQL Servers’ IP address; but that confused me again. Why did we have transaction activity from web server switched to SQL Server?  I started SQL Server Profiler and logged the following:

SET @p1=180150013 
SET @p3=1
SET @p4=16386 
SET @p5=1 
EXEC sp_cursoropen @p1 output,N'SELECT * FROM Table WHERE ID=''1223'' AND SiteID = ''ZZZZ''',@p3 output,@p4 output,@p5 output
SELECT @p1, @p3, @p4, @p5 
EXEC sp_cursorfetch 180150013,16,1,1
EXEC sp_cursorfetch 180150013,16,2,1
EXEC sp_cursorfetch 180150013,1040,1,1
EXEC sp_cursor 180150013,34,1
Login failed FOR USER 'Domain\ServiceAccount'. [CLIENT:]
EXEC sp_cursorclose 180150013

Notice again same failure is noted in middle of API cursor calls; “EXEC sp_cursor ….,34,1” statement means to delete the current record.  What was most confusing was why was SQL Server it self trying to login using another service account when delete got executed?

Digging into the configuration a bit; I found someone had created a Linked Server to the local server.  If you try to create Linked Server under (Server Objects -> Linked Servers) to local server you get an error message “You cannot create a local SQL Server as a linked server“.  So how did this get added using the ‘Domain\ServiceAccount’ account?

Looking at it a bit more it is possible to add a linked server to local server (its useless to do, as far as I can tell) by using sp_addserver stored procedure and then using sp_serveroption stored procedure you can set properties.  But the GUI interface will not let you interact with the object.

Now I had answer to why SQL Server was trying to login to it self using that service account; it was mystery to me why was the API Cursors were using the linked server.  I confirmed the SQL code being from front end (above) did not have four-part name for table object (Server.Database.Schema.Table).  There should be no reason for it to fall back on linked server, as this was not a distributed query coming from another SQL Server either.  I checked sys.server, it had one entry in it with id value of 1 but nothing for id value of 0 (default value).  So I decided to delete the linked server (id = 1 in sys.servers) and got web guy to try again; now error changed to following:

Microsoft OLE DB Provider for SQL Server error ‘80004005’
Could not find server ‘SQLServer’ in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

I started Googling to see if I can find the issue; with not much luck.  But while reading Pinal Dave’s blog I got idea to check @@ServerName property; which returned ‘NULL’.  Again running into further confusion I decided to do listing on sys.servers to see what entries exist in there as per the error messages suggestion.  It returned 0 rows (as I deleted the only row visible); but there should be at least one row in this table referencing the local server with id value of 0.  This server was not renamed, it was always the same name; I don’t know why this entry was missing.  My guess was someone added the missing entry using “sp_addserver ‘SQLServer’, ‘SQLServer'” which created a remote server connection instead of local server connection.  I executed following commands to adjust entries in this table:

EXEC sp_addserver 'SQLServer', 'local' 

This added a new entry called id = 0 (which what the default entry should be).  So since the local server was no longer viewed as a linked server under server objects I got web guy to try again, the error changed to following:

Microsoft OLE DB Provider for SQL Server error ‘80004005’
Server ‘
SQLServer‘ is not configured for DATA ACCESS.

Now reading Pinal Dave’s blog some people suggested enabling RPC Out property; now this is valid for a linked server.  But this was not linked server so why did I have this error?  Looking at @@ServerName, it was still ‘NULL’; even with record id of 0 in sys.servers.  This is because global variables like that are populated at boot time, so I had to restart SQL Server services before that variable was populated.

After @@ServerName returned proper value; the web service started running also.  I’ll be investigating the link between API cursors and @@ServerName in another article; for now problem solved.

Problem: Unable to do delete operations in an application that relies on API Cursors.
Solution: Check to make sure the @@ServerName property is set; if not check sys.servers.  If there are no rows in the table.  Add new entry using sp_addserver and restart SQL Services.


  1. SQL Learnings, Error 14274. Link.
  2. SQL Learnings, Error 18456. Link.
  3. Journey to SQL Authority with Pinal Dave, Error 7411. Link.
  4. API Cursors. System Stored Procedures. Link.
  5. SQL Server Performance. Exposing API Server Cursors. Link.
  6. MSDN. sp_addserver. Link.
  7. MSDN. sp_serveroption. Link.
%d bloggers like this: