Ms Sql Server Error 5173
Contents |
SERVER - FIX - Error: One or more files do not match the primary file of the database November 11, 2014Pinal DaveSQL, SQL Server, SQL Tips and TricksNo CommentsWriting about SQL sql server log file does not match the primary file Server for close to 8 years, almost every single day means I
Cannot Detach A Suspect Or Recovery Pending Database. It Must Be Repaired Or Dropped.
get a lot of questions from people on a daily basis. Though I try to answer as attach database without ldf many as possible (via email, blog, twitter and Facebook), I get many interesting questions. There are a variety of questions ranging from installation, scalability, performance, TSQL, new feature
The Log Cannot Be Rebuilt Because There Were Open Transactions/users When The Database Was Shutdown
and a variety of error messages. Recently I got an email which had below the question:Hi Pinal,I was given task to move the files for few databases and also to rename them. While doing this activity I did some mistake and few of my databases are not coming online. Here is the error I am getting ad hoc updates to system catalogs are not allowed. for those databases.2014-10-20 17:52:39.08 spid37s Error: 5173, Severity: 16, State: 1.2014-10-20 17:52:39.08 spid37s One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.2014-10-20 17:52:39.08 spid37s Log file ‘E:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\SQLAuth_log.ldf' does not match the primary file. It may be from a different database or the log may have been rebuilt previously. Can you please help me how I can resolve this issue and what needs to be done to mitigate?On further investigation of ERRORLOG it was found that we have “RECOVERY_PENDING” state for the database because recovery of database could not be complete. The cause of the error is all files which are getting used by database don’t belong to the same database. There is a safety mechanism in the database startup where it checks whether all files below to
by FeedBurner SQLServer-DBA.com Links Recent Posts How can I tell if hyperthreading is on Windows 2012 How to schedule Powershell Script with Task Scheduler Lots of NULL values can cause trouble ALTER INDEX REBUILD clears sys.dm_db_index_usage_stats 7 mistakes DBAs make on CV or resume 5 ways globalisation has impacted IT Is a LOG BACKUP allowed on a SIMPLE RECOVERY database How to turn AUTO_SHRINK off 7 Essential Steps to Recover a Corrupt SQL Server Database DBA vacancy at Travelers - UK based Powered by http://blog.sqlauthority.com/2014/11/11/sql-server-fix-error-one-or-more-files-do-not-match-the-primary-file-of-the-database/ TypePad One or more files do not match the primary file of the database – Error 5173 01 March,2013 by Jack Vamvas A SQL Server Instance was shut down suddenly. When the SQL Server recovery a number of databases were in a RECOVERY_PENDING state. The error messages in the SQL Server Error Log were : --An example error message http://www.sqlserver-dba.com/2013/03/one-or-more-files-do-not-match-the-primary-file-of-the-database-error-5173.html Log file 'F:\MSSQLSERVER\MSSQL$MYINST\Data\db1_log.LDF' does not match the primary file. It may be from a different database or the log may have been rebuilt previously. Error: 5173, Severity: 16, State: 1. One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup. RECOVERY_PENDING means “Recovery of the file has been postponed. The file was online or did not exist before being changed to RECOVERY_PENDING by a partial restore” Main reasons for database stuck in RECOVERY_PENDING after a sudden shutdown 1) Log is missing 2) Log is corrupt 3) Drive not online Possible actions 1) If the drive is available and online – try a SQL Server restart , as the drive may have been unavailable during restart 2) If the restart doesn’t solve the problem , then RESTORE from a valid BACKUP 3) A Workaround For Error: 5173, Severity
insight of this error type : The generic form is : Error 5173: Cannot associate files with different databases. There is no format specifier, so all failure in attaching any possibly corrupted database will end up with this https://ekasiswanto.wordpress.com/2009/02/13/resolving-sql-error-5173/ message. One of the causes of this error is the mismatch of some ID http://www.officerecovery.com/mssql/faq.htm internally maintained by SQL Server 2000 between the MDF and LDF file. This ID is in the form of GUID and is called binding ID in SQL Server terminology. This kind of checking is good, considering the possibility of associating the MDF with wrong LDF file and vice versa. But sometimes, this error can be a disaster when sql server you absolutely sure that you have already make some necessary steps to verify that it is indeed the right binding between your MDF and LDF file, yet, SQL Server still complains that is different. In this case, you have to resort to manually verifying the internal GUID of your MDF and LDF file, the way SQL Server was doing it. The internal GUID of MDF and LDF file is located at file ms sql server header page (page 0) in the file header record. The file header record offset location is determine by the one and only slot in row offset array (slot 0), at offset 0x1FFE, word size. Let's have a look of our troubling MDF file to get the row offset value : The slot 0 value : The m_tornBits value : Please beware that the byte at offset 0x1FFF is actually the torn bit, and in detached state, it tends to point to wrong value. So, you should perform some correction of this byte to obtain correct row offset location for the file header record. First, this value is masked with 0xFC with AND operation. This can be done by ubiquitous calculator program with scientific mode : 0x01 AND 01xFC = 0x00 Next the m_tornBits value 0x80102001 is right shifted by 0x1E, again this can be accomplished by calculator (make sure you checked the Inv to perform the Rsh operation, because default operation is Lsh) : 0x80102001 Rsh (right shift) 0x1E = 0x00000002. For those who are curious, the 0x1E value is the value that is used for torn page bits calculation. Because this is the last sector, it is right shifted with 0x1E, the previous sector with 0x1C, etc. Further information can be obtained from my
limitations does the demo version of Recovery for SQL Server have?I have tried the demo. How do I decide whether to purchase the full version of Recovery for SQL Server?What are the differences between product licenses?Why do results of recovery contain less data than the original SQL Server database, backup and log?Running Recovery for SQL Server from command line (through Windows Start -> Run menu).After running Recovery for SQL Server on the corrupted database, backup and log, a folder with sql script(s) and batch file has been created. How could these files be transfigured into a new database, backup and log?When trying to execute the commit.bat file created by Recovery for SQL Server I receive the error which claims that isql.exe utility cannot be found. What should I use instead of missing program?I have 3 databases, backups and logs in hand: MDF, NDF, LDF. Which one should I use to recover my database information?The corrupted database, backup and log consists of a large number of records. Is there a way to set the number of sql scripts that Recovery for SQL Server creates during recovery session? Recovery for SQL Server Error Messages Recovery for SQL Server Error: Internal Program Error message.Recovery for SQL Server Error: Not enough disk space. SQL Server Error Messages Error 5172Error 5173Error 823Error 5172Error 5181Error 3243The media family on device '' is incorrectly formed. SQL Server cannot process this media family. RESTORE DATABASE is terminating abnormally. Questions And Answers Q: Can Recovery for SQL Server repair my SQL Server database, backup and log? A: The effective way to find out if a SQL Server database, backup and log is recoverable is to try the demo version of Recovery for SQL Server on it. Visit the Demo page for a free copy of the demo. Product specifications with lists of items recovered and not recovered is available at the Product Information page. Note: Always backup your original corrupted SQL Server database, backup and log before proceeding with any recovery activity! Q: What limitations does the demo version of Recovery for SQL Server have? A: The demo version recovers a limited number of rows in each table. The remaining rows will contain "demo" in text fields and null values in other types of fields. Full version of Recovery for SQL Server will