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.
RESTORE DATABASE DatabaseName FROM DISK = 'C:\DatabaseNameFull.bak' WITH NORECOVERY; RESTORE DATABASE DatabaseName FROM DISK = 'C:\DatabaseNameDiff.bak' WITH RECOVERY;
Conclusion
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
Problem
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:
DBCC CHECKDB (adventureworks) WITH NO_INFOMSGS
- RECOVERY_PENDING option
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.
Tip:
- 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.
Conclusion
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!
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.
Issues with CU6 for SQL Server 2014 SP1
Microsoft released an update to CU6 (link) for SQL Server 2014 SP1; this update was to address an issue where even having NOLOCK hint in queries was leading to blocking and deadlocks in the default SQL Server lock-based isolation level or high levels. From the KB article:
Executing a parallelized SELECT (…) INTO Table FROM SourceTable statement, and specifically using the NOLOCK hint, under the default SQL Server lock-based isolation level or higher. In this scenario, other queries that try to access SourceTable will be blocked.
While one transaction is holding an exclusive lock on an object (for example, an ongoing table update), another transaction is executing parallelized SELECT (…) FROM SourceTable by using the NOLOCK hint. In this scenario, the SELECT query that is trying to access SourceTable will be blocked.
Reference: CU6 for SQL 2014 SP1 (Deprecated) [Link].
Therefore if you have the older build installed (12.0.4449) please update to newer build (12.0.4457).
This post is cross posted on my SQLCAN Blog, MSDN Blog, and SQL server Consultation blog.
Dissecting SQL Server Execution Plans
I remember my days before, Microsoft SQL Server PFE. I wanted to learn everything and know everything about SQL Server. However, getting hold of good resources was tough, as I didn’t have any mentor when I started down my journey to becoming a SQL Server Database Administrator.
Along the way I did pick up lots of books and references. One of such books is Dissecting SQL Server Execution Plans.
I read this book before becoming PFE, I read this now, and I recommend everyone read this book more then once.
Grant Fritchey (Blog|Twitter) wrote the book back in 2008; I would still recommend take ready. This book will only help you be better DBA and Developer.
SQL Central, Jeff Moden, Dissecting SQL Server Execution Plans
http://www.sqlservercentral.com/articles/Book+Reviews/69019/
Amazon, SQL Server Execution Plans
SQL Central, Red Gate, EBook
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
Overview
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:
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.
Conclusion
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.
SQL Server 2016 Launch Event in Edmonton SQLPASS
SQL Server 2016 release date is June 1st; to help get the moment started, Edmonton SQL Pass is holding a launch event on June 25, 2016. Keep your calendar clear and come on down. Not a lot of seats open. Register here!
I am going, see Edmonton SQL Geeks there🙂.
SQL Skills: SQL Server Wait Types & Latch Classes Library
Microsoft has documented wait types and latches in a minimum fashion on books online; often there are wait types that are just not documented.
Paul Randel (Blog|Twitter) has put together compressive list of these with recommendations and even SQL dump traces.
Check it out here..
Troubleshooting Database Mirroring in MS SQL Server
Database Mirroring ensures availability of SQL Server database in scenarios of data disasters. It is implemented in SQL databases that use Full Recovery model. It maintains two copies of a particular database, which reside in different locations. When the primary server is unavailable due to any reason, the secondary server comes into action and proceeds with the functioning.
In this blog, we will illustrate some of the measures to troubleshoot database mirroring in SQL Server. Below is the list of some factors that need to be considered whilst troubleshooting:
Accounts
- It is to be noted that the accounts, which are facilitating the SQL server to run, should be correctly configured. In case the accounts run on the same domain accounts, there are less chances of misconfiguration.
- However, in case the accounts are running on different domains, the login of one of the accounts should be created on the other computer in master. In addition to this, the login should be granted CONNECT permission in the endpoint.
- In case SQL Server is running as SaaS and is using the local system account, certificates must be used for authentication purposes.
Endpoints
Following key points should be considered to make sure that the endpoints are configured correctly:
- Make sure that all the server instances- principal, mirror and witness server should possess database mirroring endpoint. Database Mirroring Endpoint can be created either by using Windows Authentication or by using certificates.
- Make sure that all the port numbers are correct. In order to identify the port number associated with database mirroring endpoint use the following catalog views:
- sys.database_mirroring_endpoints
- sys.tcp_endpoints
- Check whether the status of the endpoints is STARTED. For each server instance, make use of the following Transact-SQL command:
SELECT state_desc FROM sys.database_mirroring_endpoints
- For starting an endpoint use the following command:
ALTER ENDPOINT Endpoint_Mirroring STATE = STARTED AS TCP (LISTENER_PORT = <port_number>) FOR DATABASE_MIRRORING (ROLE = ALL);
- In order to check whether the ROLE is correct or not, use the following command:
SELECT role FROM sys.database_mirroring_endpoints;
System Address
For naming the server instance in the database mirroring, any name, which identifies the system can be used. If the systems reside in the same domain, the system name can be used as the server name. In addition to this, a static IP address or a fully qualified domain name can also be used as the server names.
Network Access
If the server instances are in different domains, it is necessary for every server instance to access the ports of all the server instances or the instances over the TCP. This puts a stop to the communication between the instances of the server.
Mirror Database Preparation
The most important thing that is to be made sure is that the database is prepared for mirroring, no matter if the mirroring is done again or for the first time. It is to be noted that whilst creating a mirror database on the mirror server, restoration of principal database backup with the same name of the database is to be done. Moreover, all the log backups created prior to backup creation should also be applied. Both the things should be done WITH NORECOVERY.
It is recommended to store the mirror database at the same location as of the principal database. However, if the file paths are different, MOVE option should be included with RESTORE statement.
In case the mirroring was stopped earlier and is being restarted, all the backups taken afterwards should be applied to the mirror database.
Fixing Failed Create-File Process
In order to add a file without having any impact on the mirroring session, the path of the file should exist on both the servers. Due this very fact, if the database files are moved while mirror database creation, the add-file operation might fail and the mirroring can be suspended.
In order to fix the issue, follow the below-mentioned steps:
- The complete mirroring session should be removed and the full backup of the file group containing the added file must be restored.
- The log containing the add-file operation should be then backed up. Moreover, the log backup should be restored manually by using WITH NORECOVERY and WITH MOVE. This will create a file path on the mirror server and the file will be restored to a new location.
Conclusion
Care should be taken while troubleshooting database mirroring in SQL server irrespective of the fact that the mirroring has been previously done or not. With the help of the above-mentioned measures, the users can easily fix the issues while creating database mirroring.
SQL Server 2016 Release Date
SQL Server 2016 GDR has been set to release on June 1, 2016 (Ref).
SQL 2016 is going to be a major blast in how SQL Server is used for OLTP, OLAP and how it integrates with Microsoft Azure. If you are not already playing with it, take time to install it and explore the gems hidden in multitude of new functionality.
