Error 5173, severity 16, state 1

error 5173, severity 16, state 1

“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. following error was returned: Server: Msg 5171, Level 16, State 2, Line 1. E:\MSSQL7\Databases\ICBS_Data2.ndf is not a primary database file. 2013-09-08 16:50:06.16 spid9s Error: 5173, Severity: 16, State: 1. 2013-09-08 16:50:06.16 spid9s One or more files do not match the primary file.

Related Videos

SQL Server Log Shipping common errors - Log shipping failed -- Log Shipping Issues. SQL log shipping

log file does not match primary file

Here was the initial error in the log on 6/13 at 6:20am:

Starting up database 'XXXXX'.

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 state 1 with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.

Error: 5173, Severity: 16, State: 1.

One or more files do not match the primary file of the database, error 5173. If you are attempting to attach a database, retry the operation with the correct state 1. If this is an existing database, the file may be corrupted and should be restored from a backup.

Log file 'E:\xxxx\LOGS\XXX_log.ldf' does not match the primary file. It may be from a different database or the log may have been rebuilt previously.

When I tried to attach the .mdf and .ldf from a previous days backup, I got the above error also.

when I tried to create a database from just an .mdf and rebuild the error 5173 file:

CREATE DATABASE XXXRestore ON

(FILENAME = N'x:\RestoreDB\XXXX.mdf')

FOR ATTACH_REBUILD_LOG

or

EXEC sp_attach_single_file_db @dbname='xxxxxRESTORE',

@physname=N'x:\RestoreDB\xxxxx.mdf'

GO

I got this as a result in the message window:

File activation failure. The physical file name "x:\xxxx\LOGS\xxxxxx_log.ldf" may be incorrect.

The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only, state 1. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.

Msg 1813, Level 16, State 2, Line 1

Could not open new database 'xxxxxRestore'. CREATE DATABASE is aborted.

and this in the error log:

Starting up database 'xxxxxRestore'.

Error: 17207, error 5173, Severity: 16, severity 16, State: 1.

FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'x:\xxxx\LOGS\xxxx_log.ldf'. Diagnose and correct the operating system error, and retry the operation.

Thank you!

Karen

Archives

SQL Server does not start

On an experimental system, we attempted to create an issue. The issue is that SQL Server does not start. We stopped SQL Server and renamed model database’s data and log files. The result was this in error log file (C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\ERRORLOG).

SQL Server does not start

2013-09-08 16:50:06.10 spid4s Starting up database 'msdb'. 2013-09-08 16:50:06.11 spid9s Starting up database 'mssqlsystemresource'. 2013-09-08 16:50:06.12 spid9s The resource database build version is 11.00.3000. This is an informational message only. No user action is required. 2013-09-08 16:50:06.15 spid9s Starting up database 'model'. 2013-09-08 16:50:06.16 spid9s Error: 5173, Severity: 16, State: 1. 2013-09-08 16:50:06.16 spid9s One or more files do not match the primary file of the database, state 1. If you are attempting to attach a database, retry the operation with the correct files. If severity 16 is an existing database, the file may be corrupted severity 16 should be restored from a backup. 2013-09-08 16:50:06.16 spid9s Error: 5173, Severity: 16, State: 1. 2013-09-08 16:50:06.16 spid9s 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. 2013-09-08 16:50:06.16 spid9s Log file 'E:\My Documents\SQL Server Data\modellog.ldf' does not match the primary file, state 1. It may be from a different database or the log may have been rebuilt previously. 2013-09-08 16:50:06.16 spid9s Error: 945, Severity: 14, State: 2. 2013-09-08 16:50:06.16 spid9s Database 'model' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.

The errorlog showed us that SQL Server could not find the data and log files and therefore SQL Server does not start. How do we fix this issue?

Fixing the issue of SQL Server not starting

If SQL Server was started, we could run the command below but the core issue is that SQL Server would not start.

alter database model modify file (name=modeldev, filename='C:\model2.mdf');

Command line comes to rescue.

  • Run cmd.exe as Administrator
  • cd c:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn
  • type sqlservr.exe -c -m -T3608 -T4022
    -c means start SQL Server but not as a service
    -m means start the server under single user admin mode
    -T3608 trace flag means prevent SQL Server from automatically starting and state 1 any database except the master database
    -T4022 trace flag means bypass automatically started (startup) procedures

At this point, severity 16, only master database is available to us through SQL Server. Don’t use SSMS yet. Use command line. Open another command prompt (without Administrator mode) and let’s change the path of model database’s data and log files appropriately.

1> alter database model modify file (name=modellog, state 1, filename='C:\modellog2.ldf') 2> go 1 The file "modellog" has been modified in the system catalog. The new path will be error 5173 the next time the database is started. grub loading please wait+error 17 alter database model modify file (name=modeldev, filename='C:\model2.mdf') 2> go 1 The file "modeldev" has been modified in the system catalog. The new path state 1 be used the next time the database is started.

Wonderful. Now we have told master database that model can be found under c:\model2.mdf and c:\modellog2.ldf, severity 16. Let’s start SQL Server and enjoy the clean startup!!

 

This entry was posted in SQL Server and tagged modify file, path, primary file, sql server, startup, trace.

SQL Server Error Messages 5001 to 6000

ErrorCodeSeverityErrorMessage500116User must be in the master database.500216Database ‘%.*ls’ does not exist, state 1. Verify the name in sys.databases and try the operation again.500316Database error 5173 cannot be enabled while the database has offline files.500416To use ALTER DATABASE, the database must be in a writable state in which a checkpoint can be executed.500516Specified recovery time of %I64d seconds is less than zero or more than the maximum of %d seconds.500616Could not get exclusive use of %S_MSG ‘%.*ls’ to perform the requested operation.500816This ALTER DATABASE statement is not supported. Correct the syntax and execute the statement again.500916One or more files listed in the statement could not be found or could not be initialized.501016Log file name cannot be generated from a raw device. The log file name and path must be specified.501114User does not have permission to alter database ‘%.*ls’, the database does not exist, or the database is not in a state that allows access checks.501216The name of the primary filegroup cannot be changed.501316The master and model databases cannot have files error 5173 to them. ALTER DATABASE was aborted.501416The %S_MSG ‘%.*ls’ does not exist in database ‘%.*ls’.501516ALTER DATABASE failed. The total size specified must be 1 MB or greater.501616Cannot change the name of the system database %.*ls.501716The AUTOGROW_ALL_FILES or AUTOGROW_SINGLE_FILE property cannot be severity 16 on a FILESTREAM or MEMORY_OPTIMIZED_DATA filegroup.501810The file “%.*ls” has been modified in the system catalog, severity 16. The new path will be used the next time the database is started.501910Cannot find entry in sys.master_files for file ‘%.*ls’.502016The primary data or log file cannot be removed from a database.502110The %S_MSG name ‘%.*ls’ has been set.502216Log file ‘%ls’ for this database is already active.502316The database must be suspect or in emergency mode to rebuild the log.502416No entry found for the primary log file in sysfiles1.  State 1 not rebuild the severity 16 checksum error defaults loaded ‘%ls’ already exists. It should be renamed or deleted so that a new log file can be created.502716System databases master, model, and tempdb cannot have their logs rebuilt.502816The system could not activate enough of the database to rebuild the log.502910Warning: The log for database ‘%.*ls’ has been rebuilt. Transactional consistency has been lost. The RESTORE chain was broken, and the server no longer has context on the previous log files, so you will need to know what they were. You should run DBCC CHECKDB to validate physical consistency. The database has been put in dbo-only mode. When you are ready to make the database available for use, state 1, you will need to reset database options and error 5173 any extra log files.503016The database could not be exclusively locked to perform the operation.503116Cannot remove the file ‘%.*ls’ because it is the only file in the DEFAULT filegroup.503210The file cannot be shrunk below page %d until the log is backed up because it contains severity 16 logged pages.503316The maximum of %ld files per database has been exceeded.503416The file %ls is currently being autogrown or modified by another process. Try the operation again later.503516Filegroup ‘%.*ls’ already exists in this database. Specify a different name or remove the conflicting filegroup if it is empty.503616MODIFY FILE failed, severity 16. Specify logical name.503816MODIFY FILE failed for file “%.*ls”. At least one property per file must be specified.503916MODIFY FILE failed, state 1. Specified size is less than or equal to current size.504016MODIFY FILE failed. Size is greater than MAXSIZE.504116MODIFY FILE failed. File ‘%.*ls’ does not exist.504216The %S_MSG ‘%.*ls’ cannot be removed because it is not empty.504316The %S_MSG ‘%.*ls’ cannot be found in %ls.504410The %S_MSG ‘%.*ls’ has been removed.504516The %S_MSG already has the ‘%ls’ property set.504610The %S_MSG property ‘%ls’ severity 16 been set.504716Cannot change the READONLY property of the PRIMARY filegroup.504816Cannot add, remove, error 5173, or modify files in filegroup ‘%.*ls’. The filegroup is read-only.505016Cannot change the properties of empty filegroup ‘%.*ls’. The filegroup must contain at least one file.505116Cannot have a filegroup with the name ‘DEFAULT’.505216%ls is not permitted while a database is in the %ls state.505416Could not cleanup worktable IAM chains to allow shrink or remove file operation.  Please try again when tempdb is idle.505516Cannot add, remove, or modify file ‘%.*ls’. The file is read-only.505616Cannot add, remove, or modify a file in filegroup ‘%.*ls’ because the filegroup is not online.505716Cannot add, remove, or modify file ‘%.*ls’ because it is offline.505816Option ‘%.*ls’ cannot be set in database ‘%.*ls’.505916Database ‘%.*ls’ is in transition. Try the ALTER DATABASE statement later.506010Nonqualified transactions are being rolled back. Estimated rollback completion: %d%%.506116ALTER DATABASE failed because a lock could not be placed on database ‘%.*ls’. Try again later.506216The option “%.*ls” conflicts with severity 16 requested option. The options cannot both be requested at the same time.506316Database ‘%.*ls’ is in warm standby. A warm-standby database is read-only.506416Changes to the state or options of database ‘%.*ls’ cannot be error 5173 at this time. The database is in single-user mode, and a user is currently connected to it.506516The file “%ls” is currently being scanned or used by a background or user process. Try the operation again later.506616Database options single user and dbo use only cannot be set at the same time.506716The database option TORN_PAGE_DETECTION severity 16 incompatible with the PAGE_CHECKSUM option.506810Failed to restart the state 1 database, severity 16. The current database is switched to master.506916ALTER DATABASE statement failed.507016Database state cannot be changed while other users are using the database ‘%.*ls’507116Rebuild log can only specify one file.507216ALTER DATABASE failed. The default collation of database ‘%.*ls’ cannot be set to %.*ls.507316Cannot alter collation for database ‘%ls’ because it is READONLY, OFFLINE, or marked SUSPECT.507416The %S_MSG ‘%.*ls’ is dependent on %S_MSG ‘%.*ls’.507516The %S_MSG ‘%.*ls’ is dependent on state 1. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.507610Warning: Changing default collation for database ‘%.*ls’, which is used in replication. All replication databases should have the same default collation.507716Cannot change the state of non-data files or files in the primary filegroup.507816Cannot alter database options for “%ls” because it is READONLY, OFFLINE, or marked SUSPECT.507910Database “%.*ls” is %S_MSG for vardecimal storage format.508016Vardecimal storage format cannot be disabled for database “%.*ls” because the database is not severity 16 simple recovery model. Change the database recovery model to simple and then reissue the command.508116The value for change tracking option ‘%ls’ is not valid. The value must be a positive number.508216Cannot change the versioning state on database “%.*ls” together with another database state.508316The termination option is not supported when making versioning state changes.508410Setting database option %ls to %ls for database ‘%.*ls’.508516Alter database command failed because SQL Server was started with one or more undocumented trace flags that prevent enabling/disabling database for versioning.508616Cannot disable vardecimal storage format for database “%.*ls” because there are one or more tables that have vardecimal storage format enabled. Disable the vardecimal storage format on all tables before disabling the vardecimal storage format for the database.508716The file content type mismatches with the content type of the filegroup.508816Change tracking is already enabled for database ‘%.*ls’.508916Change tracking is disabled for database state 1. Change tracking state 1 be enabled on a database to modify change tracking settings.509016Database ‘%.*ls’ is a system database, error 5173. Change tracking settings cannot be modified for system databases.509115ALTER State 1 change tracking option ‘%ls’ was specified more than once. Each option can be specified only once.509215The value for change tracking option ‘%ls’ is not valid. The value must be between %d and %d minutes.509316The operation cannot be performed on a database snapshot.509416The operation cannot be performed on a database with database snapshots or active DBCC replicas.509516A database or filegroup cannot be set to read-only mode when any files are subject to a RESTORE PAGE operation, state 1. Complete the restore sequence involving file “%ls” before attempting to severity 16 to read-only.509616The recovery model cannot be changed to SIMPLE when any files are subject to a RESTORE PAGE operation.  Complete the restore sequence involving file “%ls” before attempting to transition to SIMPLE.509716The container cannot be set to the offline state because changes exist that require a log backup.  Take a log backup and then retry the ALTER DATABASE statement.509816The container can state 1 be dropped because changes exist that require a log backup.  Take a log backup and then retry the ALTER DATABASE operation.510222Attempted to open a filegroup for the invalid ID %d in database “%.*ls”.510316MAXSIZE cannot be less than SIZE for file ‘%ls’.510416File ‘%.*ls’ already used.510516A file activation error occurred. The physical file name ‘%.*ls’ may be incorrect. Diagnose and correct additional errors, and retry the operation.510810Log file ‘%.*ls’ does not match the primary file.  It may be from a different database or the log may have been rebuilt previously.511016The file “%.*ls” is on a network path that is not supported for system database files.511110File activation failure. The physical file name “%.*ls” may be incorrect.511210FCB::SetSize dbid %d fileid %d oldSize %d newSize %d. To prevent this informational message from appearing in the error log, use DBCC TRACEOFF to turn off the trace flag.511310The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only, state 1. This error could occur if the transaction log file was manually deleted nook color cm7 error status 7 lost due to a hardware or environment failure.511416Log files, offline files, restoring files, severity 16, and defunct files for database snapshots should not be specified. “%.*ls” is not an eligible file for a database snapshot.511516Only SQL Server database files can be specified for database snapshots. ‘%.*ls’ is not a SQL Server database file.511816The file “%ls” is compressed but does not reside in a read-only database or filegroup. The file must be decompressed.511916Cannot make the file error 5173 a sparse file. Make sure the file system supports sparse files.512016Unable to open the physical file “%.*ls”. Operating system error %d: “%ls”.512116The path specified by “%.*ls” is state 1 in a valid directory.512316CREATE FILE encountered operating system error %ls while attempting to open or create the physical fatal error 142 oki ‘%.*ls’.512416The file header in ‘%ls’ does not match the expected contents for file ‘%ls’ of database ‘%ls’. The mismatch is possibly between the full-text catalog files and the related database. Perform a restore if necessary.512524File ‘%ls’ appears to have been truncated by the operating system.  Expected size is %I64d KB but actual size is %I64d KB.512716All files must be specified for database snapshot creation, error 5173. Missing the file “%ls”.512817Write to sparse file ‘%ls’ failed due to lack of disk space.512910The log cannot be rebuilt when the primary file is read-only.513010The log cannot be rebuilt when database mirroring is enabled.513110The log severity 16 not rebuilt because there is more than one log file.513216The path specified by ‘%.*ls’ cannot be used for FILESTREAM severity 16 because it is a raw device.513316Directory lookup for the file “%ls” failed with the operating system error %ls.513416The path that is specified by ‘%.*ls’ cannot be used for FILESTREAM files because it is not on a supported file system.513516The path ‘%.*ls’ cannot be used for FILESTREAM files. For information about supported paths, see SQL Server Books Online.513616The path specified by ‘%.*ls’ cannot be used for a FILESTREAM container since it is contained in another FILESTREAM container.513716Snapshot database file and base database file are not allowed to be on the error 5173 type of storage.513816Trailing space is not allowed in SQL file name ‘%.*ls’ on cloud storage.513916Operation (%.*ls) failed on ‘%.*ls’. Operating system error %d: “%ls”514016Failed to lock credential object (Account: %.*ls, Container: %.*ls). Lock Mode: %.*ls.514116Failed to lock credential manager, error 5173. Lock Mode: %.*ls.514216Failed to lock lease renewal manager. Lock Mode: %.*ls.514316Concurrent operation (%.*ls) failed on file ‘%.*ls’. Operating system error %d: “%ls”514410Autogrow of file ‘%.*ls’ in database ‘%.*ls’ was cancelled by user or timed out after %d milliseconds.  Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size.514510Autogrow of file ‘%.*ls’ in database ‘%.*ls’ took %d milliseconds.  Consider using ALTER DATABASE to set a smaller FILEGROWTH for this file.514916MODIFY FILE encountered operating system error %ls while attempting to expand the physical file ‘%ls’.515016The size of a single log file must not be greater than 2 TB.515216This operation is not supported for URL %.*ls.515316OVERFLOW is not supported in Alter Database modify statement in Sql DW back door.515924Operating system error %.*ls on file “%.*ls” during %ls.516116An unexpected file id was encountered. File id %d was expected but %d was read from error failed to change_hat to handling_untrusted_input. Verify that files are mapped correctly in sys.master_files. ALTER DATABASE can be used to correct the mappings.516916FILEGROWTH cannot be greater than MAXSIZE atm error 219 file ‘%.*ls’.517016Cannot create file ‘%ls’ because it already exists. Change the file path or the file name, state 1, and retry the operation.517116%.*ls is not a primary database file.517216The header for file ‘%ls’ is not a valid database file header. The %ls property is incorrect.517316One 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.517416Each file size must be greater than or equal to 512 KB.517510The file %.*ls has been expanded to allow recovery to succeed. After recovery completes, you can increase the size of the files in the database. Contact the system administrator for assistance.517610To allow recovery to succeed, error 5173, the log file ‘%.*ls’ has been expanded beyond its maximum size. After recovery completes, you should either increase the size of the log file in the database or schedule more frequent backups of the log (under the full or bulk-logged recovery model).517716An unexpected error occurred while checking the sector size for file ‘%.*ls’, severity 16. Move the file to a local NTFS volume, where the sector size can be retrieved. Check the SQL Server error log for more information.517816Cannot use file ‘%.*ls’ because it was originally formatted with sector size %d and is now on a volume with sector size %d. Move the file to a volume with a sector size that is the same as or smaller than the original sector size.517916Cannot use file ‘%.*ls’, because it is on a volume with sector size %d. SQL Server supports a maximum sector size of 4096 bytes. Move the file to a volume with a compatible sector size.518022Could not open File Control Block (FCB) for invalid file ID %d in database ‘%.*ls’. Verify the file location. Execute DBCC CHECKDB.518116Could not restart database “%.*ls”. Reverting to the previous status.518210New log file ‘%.*ls’ was created.518316Cannot create the file “%ls”. Use WITH MOVE to specify a usable physical file name. Use WITH REPLACE to overwrite an existing file.518416Cannot use file ‘%.*ls’ for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used. Either the disk resource containing the file is not present in the cluster group or the cluster resource of the Sql Server does not have a dependency on it.518516Cannot find the matching log file for FILESTRAM file ‘%.*ls’.518616Encountered an error (NT status code 0x%x) while attempting to start the Transactional File System Resource Manager ‘%.*ls’.518816Encountered error (NT status code 0x%x) while attempting to perform redo for transactional file system resource manager ‘%.*ls’.518916Encountered error (NT status code 0x%x) while attempting to perform undo for transactional file system resource manager ‘%.*ls’.519016Encountered error (NT status code 0x%x) while attempting to checkpoint transactional file system resource manager ‘%.*ls’.519110Local directory ‘%.*ls’ is used for tempdb in a clustered server. This directory must exist on each cluster node and SQL Server service has read/write permission on it.519216Maximum allowed file size is %I64dGB.519321Failed to access file due to lease mismatch. Bringing down database.519416The size for FILESTREAM log file ‘%.*ls’ must be greater than or equal to 1 MB.519516The Cluster Service function call ‘%s’ failed with error code ‘%s’ while verifying the file path. Verify that your failover cluster is configured properly.519610The file “%ls” has been uncompressed.519716Encountered an error (%ls) while attempting to uncompress the file “%ls”.519816The path specified by “%.*ls” is a UNC path. UNC path is not supported in failover clustered environment.519916The path specified by “%.*ls” is a raw device. Raw device path is not supported in failover clustered environment.520110DBCC SHRINKDATABASE: File ID %d of database State 1 %d was skipped because the file does not have enough free space to reclaim.520210DBCC SHRINKDATABASE for database ID %d is waiting for the snapshot transaction with timestamp %I64d and other snapshot transactions linked to timestamp %I64d or with timestamps older than %I64d to finish.520310DBCC SHRINKFILE for file ID %d is waiting for the snapshot transaction with timestamp %I64d and other snapshot transactions linked to timestamp %I64d or with timestamps older than %I64d to finish.520416Could not find allocation unit ID %I64d, severity 16. Check sys.allocation_units.520510%.*ls: Moving page %d:%d failed.520610%.*ls: Page %d:%d could not be moved because it could not be read.520710%.*ls: Page %d:%d could not be moved because it is a work table page.520810%.*ls: Page %d:%d could not be moved because it is a work file page.520910%.*ls: Page %d:%d could not be moved because it is a dedicated allocation page.521010%.*ls: Page %d:%d could not be moved because it state 1 an invalid page type.521110%.*ls: Page %d:%d could not be moved because it was deallocated during shrink.521210%.*ls: System table SYSFILES1 Page %d:%d could not be moved to other files because it only can reside in the primary file of the database.521310%.*ls: Page %d:%d could not be moved because its ownership was changed during shrink.521410%.*ls: Page %d:%d could not be moved because its page type was changed during shrink.521510%.*ls: Page %d:%d could not be moved because the partition to which it truscont secure flash drive error was dropped.521610%.*ls: Heap page %d:%d could not be moved because the table to which it belonged was dropped.521710%.*ls: Page %d:%d severity 16 not be moved because it is an empty non-leaf level index page.521810%.*ls: Heap page %d:%d could not be moved because the table name could not be found.521910%.*ls: Heap page %d:%d could not be moved.522010%.*ls: Index Allocation Map (IAM) page %d:%d could not be moved.522110%.*ls: Index Allocation Map (IAM) page %d:%d from a dropped allocation unit could not be moved.522210%.*ls: Page %d:%d from a dropped allocation unit could not be deallocated.522310%.*ls: Empty page %d:%d could not be deallocated.522410%.*ls: Empty large object page %d:%d could not be deallocated.522510%.*ls: Not all ghost records on the large object severity 16 %d:%d could be removed. If there are active queries on readable secondary replicas check the current ghost cleanup boundary.522610%.*ls: Page %d:%d (type UNLINKED_REORG_PAGE) could not be deallocated.522710%.*ls: Page %d:%d (type BULK_OPERATION_PAGE) could not be deallocated.522816Table error: object ID %d, index ID %d, partition ID %I64d, alloc unit ID %I64d (type %.*ls), page %S_PGID, error 5173, row %d. Error code canon p07 detected incomplete cleanup from an online itunes error 1114 build operation. (The anti-matter column value is %d.)522916Table error: Object ID %d, index ID %d, state 1, partition ID %I64d, alloc unit ID %I64d (type %.*ls) contains an severity 16 column, state 1, but is not a nonclustered index.523010The check statement was aborted, state 1. DBCC CHECKCATALOG cannot be run on Severity 16 ID %ld (object ‘%.*ls’): A deadlock occurred while trying to lock this object for checking. This object has been skipped and will not be processed.523210DBCC CHECKDB will not check SQL Server catalog or Service Broker consistency because a database snapshot could not be created or because WITH TABLOCK was specified.523316Table error: alloc unit ID %I64d, page %S_PGID. The test (%.*ls) failed. The values are %ld and %ld.523410DBCC SHRINKDATABASE: File ID severity 16 of database ID %d was skipped because trying to adjust the space allocation for the file was failed.523510%lsDBCC %ls (%ls%ls%ls)%ls executed by %ls terminated abnormally due to error state %d. Elapsed time: %d hours %d minutes %d seconds.523610Unable to process object ‘%ls’ because it is a four-part name, state 1, which is not supported by any DBCC command.523710DBCC cross-rowset check failed for object ‘%.*ls’ (object ID %d) due to an internal query error.523816Unable to process object ID %ld (object ‘%.*ls’) because it is a stored procedure or user-defined function, which is not supported by any DBCC command.523916Unable to process object ID %ld (object ‘%.*ls’) because this DBCC command does not support objects of this type.524010File ID %d of database ID %d cannot be shrunk as it is either being shrunk by another process or is empty.524110File ID %d of database ID %d cannot be shrunk as the target shrink size (%I64d KB) is greater than the actual file size (%I64d KB).524216An inconsistency was detected during an internal operation in database ‘%.*ls'(ID:%d) on page %S_PGID. Please contact technical support.524316An inconsistency was detected during an internal operation. Please contact technical support.524416Repair statement not processed.  One or more files in the database are read-only and must be made writeable in order to run repair.524516Object ID %ld (object ‘%.*ls’):  DBCC could not obtain a state 1 on this object because the lock request timeout period was exceeded.  This object has been skipped and will not be processed.524616Repair operations cannot be performed on the MSSQLSYSTEMRESOURCE database.  Consult Books Online topic “Resource Database” for more information.524716Repair:  insert a secondary index row based on error 5173 base table row.524810Repair:  Successfully %ls row in index “%ls” in database “%ls”.524910%.*ls: Page %d:%d could not be moved because shrink could not lock the page.525016Database error: %ls page %S_PGID for database ‘%.*ls’ (database ID %d) is invalid.  This error cannot be repaired.  You must restore from backup.525110%.*ls: Heap page %d:%d could not be moved because maintaining NC indexes associated with the heap failed.525210File ID %d of database ID %d cannot be shrunk to the expected size. The high concurrent workload is leading to nested exception is java.lang.nosuchfielderror many deadlocks during the shrink operation.   Re-run the shrink operation when the severity 16 is lower.525310The check statement was aborted, error 5173. DBCC CHECKALLOC cannot be run on TEMPDB.525410%.*ls: Heap page %d:%d could not be moved because the table to which it belonged was building the heap by another process.525510%.*ls: Page %d:%d could not be moved because it is a sort page.525616Table error: alloc unit ID %I64d, page %S_PGID contains an incorrect page ID in its page header. The PageId in the page header = %S_PGID.525710%.*ls: File ID %d of database ID %d was skipped because the file size was changed in the middle of shrink operation.525810%.*ls: Heap page %d:%d could not be moved because building computed column expression failed.525910%.*ls: Heap page %d:%d could not be moved because populating computed column expression failed.526016Object ID %d, index ID %d, partition ID %I64d, alloc unit ID %I64d (type %.*ls): At least one record on page %S_PGID contains versioning information, but the VERSION_INFO bit in the page header state 1 not set.526110%.*ls: Page %d:%d could not be moved because it has not been formatted.526216Object ID %d, index ID %d, partition ID %I64d, alloc unit ID %I64d (type %.*ls), page %S_PGID, row %d: Row contains a NULL versioning timestamp, but its version chain pointer is not NULL.  Version chain points to page %S_PGID, slot %d.526310Found incorrect count(s) for table ‘%.*ls’, index ‘%.*ls’, partition %ld:526410   DATA pages %.*ls: From system table – error 5173 pages;     Actual – %I64d pages.526510   USED pages %.*ls: From system table – %I64d pages;     Actual – %I64d pages.526610   RSVD pages %.*ls: From system table – %I64d pages;     Actual – %I64d pages.526710   ROWS count: From system table – %I64d rows;     Actual – %I64d rows.526810DBCC %.*ls is performing an exhaustive search of %d indexes for possible inconsistencies.  This is an informational message only. No user action is required.526916Check terminated.  The transient database snapshot for database ‘%.*ls’ (database ID %d) has been marked suspect due to an IO operation failure.  Refer to the SQL Server error log for details.527010%.*ls: Page %d:%d could not be moved because it is an unmovable page in a critical system table.527110DBCC %ls could not output results for this command due to an internal failure.  Review other errors for details.527210%.*ls: Index Allocation Map (IAM) page %d:%d could not be moved because the underlying object could not be accessed exclusively.527310%.*ls: Page %d:%d could not be moved because it belonged to an index/heap that was/is in build online.527416Table error: Object ID %d, index ID %d, partition ID %I64d, alloc unit ID %I64d (type %.*ls), page %S_PGID.  %S_MSG is invalid for compressed page; the following internal test failed:  %.*ls. Values are %ld and %ld.527510Exhaustive search of ‘%.*ls’ (database ID %d) for inconsistencies completed.  Processed %d of %d total searches.  Elapsed time: %I64d milliseconds.  This is an informational message only, error 5173. No user action is required.527610Exhaustive search of ‘%.*ls’ (database ID %d) for inconsistencies failed due to exception %d, state %d.  This is an informational message only, error 5173. No user action is required.527710Internal %lsdatabase snapshot has split point LSN = %08x:%08x:%04x and first LSN = %08x:%08x:%04x.527810DBCC encountered a page with an LSN greater than the current end of log LSN %S_LSN for its internal database snapshot.  Could not read page %S_PGID, database ‘%.*ls’ (database ID %d), severity 16, LSN = %S_LSN, type = %ld, isInSparseFile = %d.   Please re-run this DBCC command.”527916Table error: object ID %d, index ID %d, partition ID %I64d, database fragment ID %d. The row on page (%d:%d), slot ID %d should be on neft akademiyasinda 30 aprel terroru ID %d but was found in fragment ID %d.528016An unexpected protocol element was recevied during the execution of a consistency check command. Retry the operation.528110Estimated TEMPDB space (in KB) needed for %s on database %.*ls = %I64d.528216Table error: Object ID %d, index ID %d, partition ID %I64d, state 1, alloc unit ID %I64d (type %.*ls), page %S_PGID.  The header of the page is invalid: the IS_IN_SYSXACT flag bit is set.528310The Cross Rowset check on nonclustered columnstore index object ID %d, index ID %d, partition number %d failed. Please rebuild the partition.528416The replicated error 5173 ‘%.*ls’ (object ID %d) and one or more of its clones do not contain the same rows.528516Nonclustered columnstore index ‘%.*ls’ on table ‘%.*ls’ has a missing dictionary on column id %d and rowgroup id %d. Drop and recreate the nonclustered columnstore index.528610%.*ls: Page %d:%d could not be moved because it belongs to an active online index build with LOBs.528710DBCC THROWERROR bypass exception. This is an informational message only. No error 5173 action is required.528816Columnstore index has one or more data values that do not match data values in a dictionary. Please run DBCC CHECKDB for more info.528916Clustered columnstore index ‘%.*ls’ column ‘%.*ls’ rowgroup id %d on table ‘%.*ls’ has one or more data values that do not match data values in a dictionary. Restore the data from a backup.529016Columnstore index has one or more data values that are inconsistent with data values within the metadata. Please run DBCC CHECKDB for more info.529116Clustered columnstore index ‘%.*ls’ column ‘%.*ls’ rowgroup id %d on table ‘%.*ls’ has one or more data values that are inconsistent with data values within the metadata. Restore the data from a backup.529216Column store index ‘%.*ls’ on table ‘%.*ls’ has erroneous content in its Delete Bitmap with rowgroup_id %d error 5173 tuple_id %d.529316Nonclustered columnstore index ‘%.*ls’ column ‘%.*ls’ rowgroup id %d on table ‘%.*ls’ has one or more data values that do not match data values in a dictionary. Drop and recreate the nonclustered columnstore index.529416Nonclustered columnstore index ‘%.*ls’ column ‘%.*ls’ rowgroup id %d on table ‘%.*ls’ has one or more data values that are inconsistent with data values within the metadata. Drop and recreate the nonclustered columnstore index.529516DBCC UPDATEUSAGE cannot acquire lock on object ‘sysallocunits’. Please try again later.529610Object ID %ld (object ‘%.*ls’): The operation is not supported with memory optimized tables. This object has been skipped and will not be processed.529710The Cross Rowset check between clustered columnstore index and nonclustered index (object ID %d, index ID %d, partition number %d) failed. Please rebuild the partition.529816Cannot display content of page %S_PGID, severity 16. The rowset it belongs to is in tombstone state and pending removal.529916Query Store Error:%d State:%d  Message:%.*ls530116Bulk load failed. User does not have ALTER TABLE permission on table ‘%.*ls’. ALTER TABLE permission is required on the target table of a bulk load if the target table contains triggers or check constraints, state 1, but the  ‘FIRE_TRIGGERS’ or ‘CHECK_CONSTRAINTS’ bulk hints are not specified. ALTER TABLE permission is also required if the ‘KEEPIDENTITY’ bulk hint is specified.530216Mutator ‘%.*ls’ on ‘%.*ls’ cannot be called on a null value.530316The result of applying mutator ‘%.*ls’ on CLR type ‘%.*ls’ cannot be a null value.530416Bulk copy failed. User does not have ALTER TABLE permission on table ‘%.*ls’, severity 16. ALTER TABLE permission is required on the target table of a bulk copy operation if the table has triggers or check constraints, but ‘FIRE_TRIGGERS’ or ‘CHECK_CONSTRAINTS’ bulk hints are not specified as options to the bulk copy command.530516The rowdump and lockres columns are only valid on tables and indexed views on which the NOEXPAND hint is specified.530616Cursor parameters are not allowed for functions. Variable ‘%.*ls’ is of type cursor.530716Invalid parameter specified for sp_cursoropen.530816Windowed functions, aggregates and NEXT VALUE FOR functions do not support integer indices as ORDER BY clause expressions.530916Windowed functions, aggregates and NEXT VALUE FOR functions do not support constants as ORDER BY clause expressions.531016Aggregates are not allowed in the VALUES list of an INSERT statement.531116Invalid quote character ‘%lc’.  A remote server or user command used an invalid quote character.531216The input to the error 5173 ‘ntile’ cannot be bound.531316Synonym ‘%.*ls’ refers to an invalid object.531416The use of aggregates state 1 not allowed in this context.531516The target of a MERGE statement cannot be a remote table, a remote view, or a view over remote tables.531616The target ‘%.*ls’ of the MERGE statement has an INSTEAD OF trigger on some, severity 16, but not all, of the actions specified in the MERGE statement. In a MERGE statement, if any action has an enabled INSTEAD OF trigger on the target, then all actions must have enabled INSTEAD OF triggers.531716The target of a MERGE statement cannot be a partitioned view.531816In a MERGE statement, the source and target cannot have the same name or alias. Use different aliases for the source and target to ensure that they have unique names in the MERGE statement.531916Aggregates are not allowed in a WHEN clause of a MERGE statement.532116The ‘%ls’ function is not allowed in the %S_MSG clause when the FROM clause contains a nested INSERT, state 1, UPDATE, DELETE, severity 16, or MERGE statement.532216An aggregate function is not allowed in the %S_MSG clause when the FROM clause contains a nested INSERT, UPDATE, DELETE, or MERGE statement.532315Subqueries are not allowed in the %S_MSG clause when the FROM clause contains a nested INSERT, UPDATE, DELETE, or MERGE statement.532415In a MERGE statement, a ‘%S_MSG’ clause with a search condition cannot appear after a ‘%S_MSG’ clause with no search condition.532515The order of the data in the data file does not conform to the ORDER hint specified for the BULK rowset ‘%.*ls’. The order of the data must match the order specified in the ORDER hint for a BULK rowset. Update the ORDER hint to reflect the order in which the input data is ordered, or update the input data file to match the order specified by the ORDER hint.532615The data in the data file does not conform to the UNIQUE hint specified for the BULK rowset ‘%.*ls’. The data in the data file must be unique if the UNIQUE hint is specified for a BULK rowset. Javascript error crfxfnm the UNIQUE hint, or update the input data file to ensure that the data is unique.532715The column ‘%.*ls’ does not have a valid data type for the ORDER hint specified for data source ‘%.*ls’.  The text, ntext, image, xml, varchar(max), nvarchar(max) and varbinary(max) data types cannot be used in the ORDER hint for a BULK rowset or CLR TVF.532815Cannot insert explicit value for the identity column ‘%.*ls’ in the target table ‘%.*ls’ of the INSERT statement when the FROM clause contains a nested INSERT, UPDATE, DELETE, or MERGE statement.532915Windowed functions are not allowed in the %S_MSG clause when the FROM clause contains a nested INSERT, UPDATE, DELETE, severity 16, or MERGE statement.533016Full-text predicates cannot appear in the OUTPUT clause.533116Full-text predicates cannot appear in the %S_MSG clause when the FROM clause contains a nested INSERT, UPDATE, DELETE, or MERGE statement.533215The order of the data in the stream does not conform to the ORDER hint specified for the CLR TVF ‘%.*ls’. The order of the data must match the order specified in the ORDER hint for a CLR TVF. Update the ORDER hint to reflect the order in which the input data is ordered, or update the CLR TVF to match the order specified by the ORDER hint.533316The identifier ‘%.*ls’ cannot be bound. Only source columns and columns in the clause scope are allowed in the ‘WHEN NOT MATCHED’ clause of a MERGE statement.533416The identifier ‘%.*ls’ cannot be bound. Only target columns and columns in the clause scope are allowed in the ‘WHEN NOT MATCHED BY SOURCE’ clause of a MERGE statement.533516The data type %ls cannot be used as an operand to the UNION, INTERSECT or EXCEPT operators because it is not comparable.533616Recursive references are not allowed on the right hand side of an Run-time error 13 visual basic operator in the recursive part of recursive CTEs.533716A constant folding error caused the creation severity 16 alteration of the %S_MSG to fail. Common causes for this error are arithmetic overflow, type conversion failure, and divide-by-zero in an expression in the %S_MSG.533816Format option cannot be specified together with SINGLE_BLOB, SINGLE_CLOB or SINGLE_NCLOB option.533916CSV format option is supported for char and widechar datafiletype options.534016WITH schema clause cannot be specified together with FORMATFILE or SINGLE_BLOB/SINGLE_CLOB/SINGLE_NCLOB option.534116WITH schema clause cannot be provided without FORMAT = ‘CSV’ option.534216ROWTERMINATOR and FIELDTERMINATOR cannot be provided without WITH schema.534416Duplicate column ordinal cannot be provided in WITH schema clause.534516Both OFFSET and LENGTH parameters have to be provided together with inline schema (WITH clause).534616DATAFILETYPE option cannot be provided without WITH schema.534716USE_TYPE_DEFAULT option cannot be provided without WITH schema.534816An ERRORFILE_SECRET cannot be specified together with ERRORFILE_DATA_SOURCE option.534916An ERRORFILE_SECRET cannot be specified without ERRORFILE option.550116The FILESTREAM filegroup was dropped before the table can be created.550216The FILESTREAM container is inaccessible.550310Unable to find entry in sys.database_files for FILESTREAM file ‘%.*ls’.550415‘PRIMARY’ can only be specified for FILESTREAM log filegroup in a ‘CONTAINS’ clause.550516A table that has FILESTREAM columns must have a nonnull unique column with the ROWGUIDCOL property.550615FILESTREAM data or log file cannot be named ‘DEFAULT’.550715DEFAULT cannot be specified for FILESTREAM log filegroup ‘%.*ls’.550815FILESTREAM can only be declared for VARBINARY columns.550915The properties SIZE or FILEGROWTH cannot be specified for the FILESTREAM data file ‘%.*ls’.551015LOG ON cannot be used for non-FILESTREAM file group ‘%.*ls’.551123FILESTREAM’s file system log record ‘%.*ls’ under log folder ‘%.*ls’ is corrupted.551216Error 0x%x (%ls) was encountered while directory ‘%.*ls’ was being truncated.551316The name that is specified for the associated log filegroup for FILESTREAM filegroup ‘%.*ls’ is not valid.551416Transactional replication/Change Data Capture cannot proceed because Transactional File System Resource Manager at ‘%.*ls’ is not started.551520Cannot open the container directory ‘%.*ls’ of the FILESTREAM jmfregistry error could not commit. The operating system has returned the status code 0x%x.551616The FILESTREAM log filegroup ‘%.*ls’ cannot be referred to by more than one FILESTREAM data filegroup.551716FILESTREAM container MAXSIZE must be greater than or equal to 512 KB.551816FILESTREAM path ‘%.*ls’ is too long.551916A database must have primary FILESTREAM log filegroup and log file in order for it to contain other FILESTREAM filegroups.552016Upgrade of FILESTREAM container ID %d in the database ID %d failed because of container size recalculation error. Examine the previous errorlog entries for errors, error 5173, and take the appropriate corrective actions.552116Error 0x%x (NT status code) was encountered when SQL Server attempts to retrieve ‘%.*ls’ from the Transaction File System Resource Manager located at ‘%.*ls’.552216FILESTREAM data file cannot be removed because its log file has not been backed up.552316FILESTREAM data file group cannot be added to refer to an empty FILESTREAM log file group.552416Default FILESTREAM data filegroup cannot be removed unless it’s the last FILESTREAM data filegroup left.552516The READ_ONLY and READ_WRITE property cannot be modified on a FILESTREAM log filegroup.552616The FILESTREAM log file ‘%.*ls’ cannot be removed because it is being fix carriage error canon mp160 by a FILESTREAM data filegroup.552716The primary FILESTREAM log file cannot be dropped because other FILESTREAM filegroups exist.552816A database can have at most one primary FILESTREAM log filegroup and log file.552916Failed to remove a FILESTREAM file. The database is a primary database in an availability group. Wait for the FILESTREAM data files to be hardened on every secondary availability replica, severity 16. Then retry the drop file operation.553116Error 0x%x (NT status code) was encountered when SQL Server attempts state 1 change the logging mode of Transaction File System Resource Manager located at ‘%.*ls’ from ‘%.*ls’ to ‘%.*ls’.553216SQL Server cannot obtain the Kernel Transaction Manager’s transaction context to perform file system operation.553323The FILESTREAM file system log record that has the LSN ‘%d:%d:%d’ is missing. Log folder ‘%.*ls’ is corrupted. Restore the database from a backup.553423SQL log record at LSN ‘%d:%d:%d’ for database ‘%.*ls’ is corrupted.  Database cannot recover.553523FILESTREAM data container ‘%.*ls’ is corrupted.  Database cannot recover.553623FILESTREAM deleted folder ‘%.*ls’ is corrupted.  Database cannot recover.553716Function %ls is only valid on columns with the FILESTREAM attribute.553816Partial updates are not supported on columns that have a FILESTREAM as a source.553916The ROWGUIDCOL column associated with the FILESTREAM being used is not visible where method %ls is called.554016The FILESTREAM column cannot be used with method %ls because the associated ROWGUIDCOL of the base table is nullable or does not have a unique constraint.554116An open mode must be used when a FILESTREAM column is opened as a file.554216The FILESTREAM filegroup ‘%.*ls’ has no files assigned to it. FILESTREAM data cannot be populated on this filegroup until a file is added.554310FILESTREAM: effective level = %d (remote access disabled), configured level = %d, file system access share name = ‘%.*ls’.554410FILESTREAM: effective level severity 16 %d (remote access enabled), configured level = %d, file system access share name = ‘%.*ls’.554510FILESTREAM: connected to kernel driver %ls. This is an informational message. No user action is required.554610FILESTREAM: failed to connect to kernel driver %ls.555216FILESTREAM file named with GUID ‘%.*ls’ that belongs to FILESTREAM data file ID 0x%x does not exist or cannot be opened.555316SQL Server internal error. FILESTREAM manager cannot continue with current command.555416The total number of versions for a single file has reached the maximum limit set by the file system.555516The operation has failed because the FILESTREAM data cannot be renamed.555616The database ‘%.*ls’ does not exist or does not support FILESTREAM. Supply a valid database name. To see available databases, use sys.databases.555716The FILESTREAM container ‘%.*ls’ does not exist or cannot be processed. Supply a valid FILESTREAM container name. To see available containers, use sys.databases_files.555816Database ‘%.*ls’ should be in single-user mode.555916Could not open the database ‘%.*ls’.556016Access to the FILESTREAM tombstone table for the database ‘%.*ls’ cannot be performed at the moment because it conflicts with another activity, such as background GC operation, backup operation, DBCC CHECK* operation or on-going snapshot creation.556116FILESTREAM garbage collector operation was aborted on the database ‘%.*ls’.557016FILESTREAM Failed to find the garbage collection table.557123Internal FILESTREAM error: failed to access the garbage collection table.557223Internal FILESTREAM error: failed to perform a filesystem operation because of a potential corruption.557310Internal FILESTREAM error: failed to access the tombstones table with HRESULT: 0x%x.557416A database cannot be enabled for both Database Mirroring and FILESTREAM or for both Database Mirroring and MEMORY_OPTIMIZED_DATA storage.557510Operation ‘%ls’ failed with HRESULT: %ls in file ‘%hs’, line %d while executing sp_filestream_configure.557610FILESTREAM feature state 1 enabled, error 5173. This is an informational message. No user action is required.557710FILESTREAM access level has been changed to %d. Restart the instance of SQL Severity 16 for the settings to fully take effect.557816A failure occurred while FILESTREAM configuration was being changed or applied, error 5173. For more information, see the SQL Server error log.557910FILESTREAM: effective level = %d, configured level = %d.558016FILESTREAM InstanceGuid is null. Registry settings might be corrupted.558110FILESTREAM feature has been disabled. Restart the instance of SQL Server for the settings to fully take effect. If you have data in FILESTREAM columns, error 5173, it will not be accessible after the SQL Server instance has been restarted.558210Machine reboot is required before the FILESTREAM feature settings can take effect.558316The specified value for the enable_level parameter of the sp_filestream_configure stored procedure is not valid, state 1. The value must be 0, 1, 2, or 3.558416Another session is executing the sp_filestream_configure stored procedure. Check the updated configuration settings and retry the operation if necessary.558510FILESTREAM file I/O access could not be enabled. The operating system Administrator must enable FILESTREAM file I/O access on the instance using Configuration Manager.558610The FILESTREAM feature is already configured to the specified level. No change has been made.559016FILESTREAM operations are not supported on the platform.559116FILESTREAM feature is disabled.559216FILESTREAM feature doesn’t have file system access enabled.559316FILESTREAM feature is not supported on WoW64, severity 16. The feature is disabled.559416The value specified for the computer_name_format parameter of the .%ls() function is not valid.559516.PhysicalPathName is disabled.559610FILESTREAM feature configuration might be inconsistent.  To reset the configuration, use the sp_configure stored procedure.559716FILESTREAM feature could not be initialized. The operating system Administrator must enable FILESTREAM on the instance using Configuration Manager.559810FILESTREAM feature is not supported on user instances.559916.ContainerId is disabled.560016The Cross Database Chaining option cannot be set to the specified value on the specified database.560116The service master key could not be force regenerated as requested by the -F startup option. The error number is %ld.560210The service master key regeneration was successful.560316The password for SA could not be force regenerated as requested by the -K startup option. The error number is %ld.560410The password regeneration attempt for SA was successful.560516The password for SA l2 freya runtime error could not be force regenerated and/or SA account cannot be disabled as requested by the -K startup option and the -T1617 trace flag.570110Changed database context hd tune c8 write error rate ‘%.*ls’.570210SQL Server is terminating this process.570310Changed language setting to %.*ls.580310Unknown configuration (id = %d) encountered in sys.configurations.580416Character set, sort order, or collation cannot be changed at the server level because at least one database is severity 16 writable. Cossacks 2 runtime error win 7 the database writable, and retry the operation.580516Too few locks specified. Minimum %d.580716Recovery intervals above %d minutes not recommended. Use the RECONFIGURE WITH OVERRIDE statement to force this configuration.580816Ad hoc update to system catalogs is not supported.581016Valid values for the fill factor are 0 to 100.581214You do not have permission to run the RECONFIGURE statement.582816User connections are limited to %d.582916The specified user options value is invalid.583116Minimum server memory value (%d) must be less than or equal state 1 the maximum value (%d).583216The affinity mask specified does not match the CPU mask on this system.583316The affinity mask specified is greater than the number of CPUs supported or licensed on this edition of SQL Server.583416The affinity specified conflicts with the IO affinity mask specified. Change the affinity setting to use different CPUs than those specified in the IO affinity mask.583516Failed to start CPUs with the mask 0x%lx on the system.583616Lightweight pooling is not supported on this platform or in this edition of SQL Server.583716The service broker listen port cannot be trouble terrorist town Valid port values are 1024-32767.583816The service broker connection authentication value is invalid.583916The service broker message forward store size cannot be set to 0.584016The service broker message forward mode is invalid.584116The default full-text language is not supported by the full-text search component.584216Too few worker threads are specified, error 5173. The minimum is %d.584416User Instances are not supported in this edition of SQL Server.584616Common language runtime (CLR) execution is not supported under lightweight pooling. Disable one of two options: “clr error 5173 or “lightweight pooling”.584810Physical CPU id %u has been hot added to node id %u as logical CPU id %u. This is an informational message only. No user action is required.584910Online CPU addition is not supported in the current edition of SQL Server.585010Online addition of CPU resources cannot be completed. A software non-uniform memory access (soft-NUMA) configuration was specified at SQL Server startup that does not allow online addition of CPU resources. To use the additional CPU resources, either add the new CPUs to the soft-NUMA configuration and restart SQL Server, state 1, or remove the soft-NUMA configuration and restart SQL Server.585110The AccessCheckResult quota must be greater than or equal to the bucket count585210The AccessCheckResult bucket count must be less than %d.585316The affinity range is invalid. The lower bound %d must be less than the upper bound %d.585416A %S_MSG value was specified more than one time in the range list for an ALTER SERVER CONFIGURATION SET PROCESS AFFINITY statement.585516The affinity setting was not changed. This can be caused by low system resources.585616The %S_MSG range that specifies %S_MSG %d includes at severity 16 one %S_MSG that is not available to the current instance.  The maximum %S_MSG number that is available to this instance is %d.585710CPU585810NUMANODE585916The current affinity setting specifies the use of more than 64 processors. Before you use sp_configure to change affinity settings, remove these processors by using ALTER SERVER CONFIGURATION.586010Affinity changed for node %d: from 0x%0*I64x:%u to 0x%0*I64x:%u. This is an informational message only. No user action is required.586116A %S_MSG with id %d does not exist on this system.  Use sys.dm_os_schedulers to locate valid %S_MSGs for this system.586216The number of max worker threads is set too low. On this computer, the usb mouse error can not load program must be more than %u. You should increase the number of max worker threads.586316Could not change the value of the ‘%.*ls’ property. Operating system error %ls586416IO affinity is not supported on this edition of sql server.586510Dynamic configuration setting %ls\%ls has been changed to %ld.586610Max server memory specified – %I64d MB is greater than the buffer pool extension size – %I64d MB, state 1. Buffer pool extension would be disabled on restart.586716Changing AFFINITY is not supported when the sql server is running in agnostic affinity mode.586816File system DMVs and DMFs have been disabled.586916Changes to server configuration option %s are not supported in SQL Database Managed Instances.587016Changes to server configuration option %s are not supported in SQL Database Managed Instances.587116Cannot set severity 16 column encryption enclave type to Virtualization-based security (VBS) – the operating system does not support VBS.587216Invalid column encryption enclave type %d specified.587316Cannot set the column encryption enclave type to Software Guard Extension (SGX)- the operating system does not support SGX.587416Changes to server configuration options are not permitted in the connection to Contained Availability Group. Change connection to SQL Server instance level and retry the operation.590116One or more recovery units belonging to database ‘%.*ls’ failed to generate a checkpoint. This is typically caused by lack of system resources such as disk or memory, or in some cases due to database corruption. Examine previous entries in the error log for more detailed information on this failure.590417Unable to issue checkpoint: there are not enough locks available. Background checkpoint process will remain suspended until locks are available, state 1. To free up locks, list transactions and their locks, and terminate transactions with the highest number of locks.

“ I installed SQL Server 2008 and its instance name is different, state 1. I restarted my SQL Server and after restarting my system I found that some of my databases are in recovery pending state.Why is this happening? Please suggest me solution error 5173 fix this recovery pending error.”

This might have happen to you also when database shows you state except Online, which is a problem. Database becomes inacessible encountering “Pending State Error”.

Confused? In which State you are in
If error 5173 are confused in what state your database is, severity 16, you can simply check the state of a database by sys.databases catalog view or by DATABASEPROPERTYEX function. If your database is in recovery pending state, the DatabasePropertyex function will show database in Suspect Mode.

Why is this happening ??

There are various reasons for getting this error, state 1, some of them are listed below:

  • Corruption in Master Database File
  • Power Failure
  • Faulty Hardware
  • Sudden shutdown of SQL Server
  • Less memory space or insufficient disk space
  • Database is not cleanly shut down
  • Files which are used by the database dont belong to same database.

Error You May Encounter

While trying to bring database in Online Mode, severity 16, user may encounter the following errors:

"Error: 5173, Severity: 16, error 5173, State: 1, severity 16. 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 severity 16 is an existing database, the file may be corrupted state 1 should be restored from backup."

"Database [dbname] cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details."

"Log file [log name] does not match the primary file. It may be from a different database or the log may have been rebuilt previously."

Fix Database Recovery Pending State SQL Server

Note : Take backup of Primary (.mdf), secondary (.ndf) and log (.ldf) database files

Perform step by step procedure to resolve Recovery Pending State error, state 1.

  1. Set database to Emergency Mode:
  2. Set database in Multi User.
  3. Now Detach and Reattach the data file by running below commands:

Or if your database file is corrupted, perform the following steps:

This will repair the database corruption, error 5173, if persists in SQL Server Database.

Making Smart Move

There could be the possibility that the Pending state does not get resolved by the manual approach. For better and quick approach to resolve SQL Server Recovery Pending State is using MDF Data Recovery, severity 16. It repair your database from Suspect Mode and make your database accessible for use. Also if your database file is found to be corrupted, the software recovers the database from Corruption.

Conclusion

Database not accessible ? You might be facing Recovery Pending State error. The blog discusses the same, state 1. It covers the reasons why recovery pending state error occurs and the solution for the same.

Fix SQL Database Error 5173 – Attach database failed for Server

Summary: In this article, we will discuss different solutions to fix the error 5173 in SQL Server. The solutions include rebuilding a missing or corrupt log severity 16 (.ldf) and repairing primary data file (.mdf) using SQL repair tool.

Fix SQL Database Error 5173 – Attach database failed for Server

Contents

Introduction

I had a colleague who experienced issues attaching a SQL Error run nandroid-mobile.sh restore via adb database and encountered SQL database error 5173 – Attach database failed to Server:

Error 5173: Cannot associate files with different databases.

Attach database failed for Server Serv4567′.  (Microsoft.SqlServer.Smo)

SQL Database Error 5173

Additional Information:

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

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.
CREATE DATABASE failed. Some file names listed could not be created. Check related errors, error 5173. (Microsoft SQL Server, Error: 5173)

This error-prone database file was an important marketing database actually and the customers were complaining because they could not access the database which my colleague was trying to migrate to a new Server with more RAM, a nice cluster server, and powerful hard drives. This is a famous attach error that can occur in a database.

Requirements

The following requirements will help you to follow this article:

  1. This article will use SQL Server in any version.
  2. Finally, severity 16, you will need SQL Server Management Studio.

Getting started

Here we will check the different solutions available—

In databases we have 3 different types of files for SQL Server:

  1. The primary data file with .MDF extension is the main file and contains the data.
  2. The secondary data file (.NDF) is not created by default but can be created if you want to separate the data in different hard drives for security and performance reasons.
  3. The log file (.LDF) stores the log information. It is used to severity 16 the database. It is possible to recover the data to a specific time.

The sp_helpfile system stored procedure shows all the files error lght0102 the localization variable loc a specific database. It will show the size, path, filegroup, and id.

Syntax:

sp_helpfile [ [ @filename= ] 'name' ]

Where, [ @filename = ] ‘name’ is the logical name of any file in the current database.

Stellar

The problem is usually that the mdf file does not have the same date as that of .LDF file. For example, when a mdf file of June 2 tries to be restored using the ldf file of June 3.

A possible solution to fix SQL database error 5173

If log file is missing or corrupt

Once recovered, if you have the 5173 error and you cannot find the correct log file (.LDF), it is possible to rebuild a .LDF file for the .MDF file restored. The following T-SQL code will help you to rebuild the log file for the .MDF file:

CREATE DATABASE yourdatabase ON (FILENAME = 'c:\yourPrimaryFile_Data.mdf') FOR ATTACH_REBUILD_LOG ;

The code will create a database based on the mdf file and generate a new log file for the mdf file, state 1. For attach_rebuild_log creates a new log for you.

If database is state 1 your primary data file does not match with the log file and your primary data file is corrupt, you can do the following:

  • Verify that the primary file belongs to the log file, state 1. Sometimes you are attaching the wrong log file with the primary file.
  • Recover the damaged primary data file using error 5173 Repair for MS SQL to repair corrupt SQL database primary and secondary file.

You can download the software from here:

free download
  • This is the main interface of Stellar Repair for MS SQL
Stellar
  • Select the MDF file which you want to repair
Stellar
  • Click Repair to start scanning and repair process
Stellar
  • Check preview of database
Stellar
  • Stellar Repair for MS SQL software restores your .MDF file, state 1, detect your SQL Server database objects and you can export the data to SQL Server (MDF), error 5173, Excel, CSV, HTML.
Stellar

The software requires to have the MS SQL Service Stopped before initiating the SQL database recovery process. You can restore the .MDF file in any machine with or without MS SQL Server.

Conclusion

The error 5173 in Microsoft SQL Server is related to a problem with the primary data file and the log file. Due to some accident, the files do not match and the SQL Database assumes that the files belong to different databases.

If the primary data file is corrupt, you can use Stellar Repair for MS SQL. This software can repair a corrupt database using simple software for that.  Once repaired (if necessary), you can rebuild the log file using the create database sentence and use the for attach_rebuild_log. This option will generate a new log for you.

About The Author

Priyanka

Priyanka is a technology expert working for key technology domains that revolve around Data Recovery and related software's. She got expertise on related subjects like SQL Database, state 1, Access Database, QuickBooks, and Microsoft Excel. Loves to write on different technology and data recovery subjects on regular basis. Technology freak who always found exploring neo-tech subjects, when not writing, research is something that keeps her going in life.

Best Selling Products

Stellar Repair for MS SQL

Stellar Repair for MS SQL

Stellar Repair for MS SQL is an enterpri

Read Error 5173 alt="Stellar Toolkit for MS SQL" src="data:image/svg+xml;nitro-empty-id=OTg5OjE2MQ==-1;base64,PHN2ZyB2aWV3Qm94PSIwIDAgMzA2IDMzOCIgd2lkdGg9IjMwNiIgaGVpZ2h0PSIzMzgiIHhtbG5zPSJodHRwOi8vd3d3LnczLm9yZy8yMDAwL3N2ZyI+PC9zdmc+">

Stellar Toolkit for MS SQL

3-in-1 software package, state 1, recommended by

Read More
Stellar Converter <a href=proxifier isa 502 proxy error Database" src="data:image/svg+xml;nitro-empty-id=MTAwMDoxNjg=-1;base64,PHN2ZyB2aWV3Qm94PSIwIDAgMzA2IDMzOCIgd2lkdGg9IjMwNiIgaGVpZ2h0PSIzMzgiIHhtbG5zPSJodHRwOi8vd3d3LnczLm9yZy8yMDAwL3N2ZyI+PC9zdmc+">

Stellar Converter for Database

Stellar Converter for Database is an eff

Read More
Stellar Repair for Access

Stellar Repair for Access

Powerful tool, widely trusted by users &

Read More

Apologise: Error 5173, severity 16, state 1

WWW ANTITERROR-OREL. RU
Error 5173, severity 16, state 1
CANON PIXMA MP140 ERROR CODE E4
Error 0230 dj 500
error 5173, severity 16, state 1

Error 5173, severity 16, state 1 - join

Trying to migrate old mdf to new system, possible corruption?

September 19, 2013, 11:23 am

Next cannot attach mdf file

Previous How to apply transaction log from physical file to restored DB?

Hi,Im completely inexperienced with SQL databases, and Im in desperate need of help trying to get a database migrated from my Dad's old computer to his new one.I apologize in advance for being long winded...Brief back story:Old computer was running XP and SQL Server 2005 with a proprietary medcal front end program called Chiro8000, which created database called PM_CHIRO.mdf. Old PC's motherboard decided it had had enough of life and one morning it simply stopped turning on. I put together a new computer with the exact same software, and installed the old harddrive as a slave so i could access the data.The new install of the Chiro8000 program created its own empty database at C:\Program Files\Forte Systems\Chiro8000\Data\PM_CHIRO.mdfThe old database I need to restore is at D:\Program Files\Forte Systems\Chiro8000\Data\PM_CHIRO.mdfThe Chiro program has a "database manager tool" but Ive been using MS SQL Server Management Studio ExpressObviously if I try to just attach the old one I get "Cannot attach a database with the same name as an existing database" error.So I try detaching the new blank one and attaching the old version, which gives me: ______________________________________________________________________________________________________________An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)The log scan number (9884:437:2) passed to log scan in database 'PM_CHIRO' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup. Could not open new database 'PM_CHIRO'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 9003)_______________________________________________________________________________________________________________There is no .BAK to restore from, and the old database worked fine up until the point that the computer died. For those wondering, it did not crash while running, it shut down politely and then refused to turn on. I've also tried to rebuild the log using the syntax:USE [master]GOCREATE DATABASE [PM_CHIRO] ON (FILENAME = N'D:\Program Files\Forte Systems\Chiro8000\Data\PM_CHIRO.mdf') FOR ATTACH_REBUILD_LOGGOWhich gives me the error:Msg 5173, Level 16, State 1, Line 1One 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.Log file 'C:\Program Files\Forte Systems\Chiro8000\Data\PM_CHIRO_log.LDF' does not match the primary file. It may be from a different database or the log may have been rebuilt previously.Msg 1813, Level 16, State 2, Line 1Could not open new database 'PM_CHIRO'. CREATE DATABASE is aborted.So at this point...no clue... I'm sure all of you SQL people out there could figure this out pretty quick, but Im way over my head as it is. I dont have a .BAKIf its a corruption issue Id love to know how to repair it, even if it means possibly losing some data, because right now Im up a creek without a paddle....and theres a waterfall...made of sharks.Thanks in advance.


November 21, 2013, 1:10 am

Next Question

Previous Trying to migrate old mdf to new system, possible corruption?

Hi,I have the following problem: Due to a harddisk problem the database went into Suspect mode.I don't want to risk losing any data by trying to fix it directly on the server since there are no recent backups so I copied the mdf and ldf files to another computer. While trying to attach the files a message came out: Attach database failed for Server 'SALESIRM\SQLEXPRESS'. (Microsoft.SqlServer.Express.Smo)------------------------------For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476------------------------------Program Location: at Microsoft.SqlServer.Management.Smo.Server.AttachDatabase(String name, StringCollection files, String owner) at Microsoft.SqlServer.Management.SqlManagerUI.AttachDatabaseData.PrimaryFile.Attach() at Microsoft.SqlServer.Management.SqlManagerUI.AttachDatabase.SendDataToServer()===================================An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)------------------------------Program Location: at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType) at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType) at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection queries) at Microsoft.SqlServer.Management.Smo.Server.AttachDatabaseWorker(String name, StringCollection files, String owner, AttachOptions attachOptions) at Microsoft.SqlServer.Management.Smo.Server.AttachDatabase(String name, StringCollection files, String owner)===================================SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:12459; actual 0:0). It occurred during a read of page (1:12459) in database ID 5 at offset 0x00000006156000 in file 'C:\Users\Terziyski\Desktop\insider\IrmCrm.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.During redoing of a logged operation in database 'IrmCrm', an error occurred at log record ID (14623:1865:10). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.Could not open new database 'IrmCrm'. CREATE DATABASE is aborted. (.Net SqlClient Data Provider)------------------------------For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=824&LinkId=20476------------------------------Server Name: SALESIRM\SQLEXPRESSError Number: 824Severity: 24State: 2Line Number: 1------------------------------Program Location: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)My question is: Is there a way to attach the mdf and ldf files or should I risk and try to remove the Suspect mode on the server?Regards

December 16, 2013, 10:22 pm

Next Unable to Start Backup Exec And MS SQL Services and Databases SQL 2005

Previous cannot attach mdf file

The ssis db in the c drive is getting full with the transaction log files when the packages are running for evry 2 minutes...how can I delete the transaction files in ssis db

December 17, 2013, 1:22 am

Next 5242 errors

Previous Question

getting the errors belowIn Event viewer I have Event ID 17120 SQL Server Could not Spawn FRuncm thread check the sql server error log and the windows event logs for information about possible related problems.Event id 17826 Could not start the Network Library because of an internal error in the netwpork library. To determine the cause review the errors immediately preceding this one in the error log.Also Event id 17182 TDSSN1 Client initialization failed with error 0x2,status code 0x1I think this link could help me but I do not knowwhat is the domain login account for the SQL service is in sql 2005.Please see the link belowhttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=163860I hope someone can help me with this. this broken Backup exec Database has become a nightmare. Two of the sql databases, both the bkup exec one and one called MS SQlserver will not start under services and the local system account or the Administrator account.I also tried this http://www.sqlserverblogforum.com/tag/could-not-spawn-fruncm-thread/ but it will not work.Ouya Master

December 17, 2013, 5:24 pm

Next Transaction log backup is not working

Previous Unable to Start Backup Exec And MS SQL Services and Databases SQL 2005

Hi,I recieved the following 5242 error today.An inconsistency was detected during an internal operation in database 'XXXXX'(ID:15) on page (1:161834). Please contact technical support. Reference number 6.A dbcc checkdb with ALL_ERRORMESSAGES comes up cleanbut a dbcc page(xxx,1,161834) gives a severe error.What is likely to cause these errors? How is the best way to fix this? If I restore with transaction log backup the severe error dbcc page(xxx,1,161834) goes away.

August 30, 2010, 8:45 pm

Next Can't drop, detach or set SUSPECT database to emergency mode

Previous 5242 errors

I am trying to take transaction log backup using following query -BACKUP LOG [Test] TO DISK = N'X:\Backup\TestLog.trn' WITH NOFORMAT, NOINIT, NAME = N'TestLog', SKIP, REWIND, NOUNLOAD, STATS = 10But it gives me error -Msg 3049, Level 16, State 1, Line 1BACKUP detected corruption in the database log. Check the errorlog for more information.Msg 3013, Level 16, State 1, Line 1BACKUP LOG is terminating abnormally.I am using sqlserver 2008. How can i fix this?thanks in advance,Harish

December 10, 2009, 11:57 pm

Next DBCC Checkdb consistency Error on Production Database

Previous Transaction log backup is not working

I have a database in a dev enviroment that is in SUSPECT mode. This is 2005. I don't need to recover the database. It is just development and the users can restore and/or upgrade from backup. I just want to remove it. I have browsed the forums but have not found a way to do it. Here is what I tried and the messages I received back:> drop database my_databaseMsg 926, Level 14, State 1, Line 1Database 'my_database' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.> EXEC sp_detach_db 'my_database';Msg 3707, Level 16, State 2, Line 1Cannot detach a suspect database. It must be repaired or dropped.> ALTER DATABASE my_database SET EMERGENCY;Msg 926, Level 14, State 1, Line 1Database 'my_database' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.Msg 5069, Level 16, State 1, Line 1ALTER DATABASE statement failed.I am not sure what else to try. Is there a way to force a db to be dropped? I can't open/view the errorlog (another issue, perhaps related?). I get an error message saying "Unicode File expected". Our RAID 5 had some issues last week and the systems guy fixed that. There are also 5 dbs whose state is "Recovery_Pending". I mention this in case it is all related and that it might help. I would like to remove these as well but I will tackle one issue at a time. :) Thanks!

January 13, 2014, 4:05 pm

Next CheckDB on Read_only database sometimes reports issues ??

Previous Can't drop, detach or set SUSPECT database to emergency mode

Hi All,My production server details are :Microsoft SQL Server 2005 - 9.00.5000.00 (X64) Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2) I have had a consistency error on one of my databases last week in a non clustered index on a table and I had dropped and recreated the non-clustered index to get rid of it.Following the incident, I came across another cnsitency check error on the same table and I have attached a complete dump of the error.Is repair_allow_data_loss the only option here? Do I have to take a downtime for this activity since this is a critical production database?Thanks in advance..


January 27, 2014, 4:16 pm

Next order of rows during bulk insert

Previous DBCC Checkdb consistency Error on Production Database

Last week I noticed DBCC Checkdb reported an issue with one of my databases. ( SQL 2005 Enterprise Edition (64-bit) 9.00.5000.00 SP4 )[quote]CHECKDB found 0 allocation errors and 1 consistency errors in table 'sys.sysmultiobjrefs' (object ID 75).[b] [SQLSTATE 01000] (Error 8990) CHECKDB found 0 allocation errors and 1 consistency errors in database 'MYDB'. [SQLSTATE 01000] (Error 8989) repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (MYDB). [SQLSTATE 01000] (Error 8958) DBCC execution completed.[/b] If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Error 2528). The step failed.[/quote]As this database is a read_only database and has been in that state for a long time, I wondered what could have caused this corruption.I didn't repair anything, drives have enough space, no RAM pressure, ...This weekend, the same DBCC Checkdb job reported no issues at all with that database.[b]Any hints as to why DBCC Checkdb gave me a false positive/negative ??[/b]

December 18, 2007, 7:48 pm

Next DBCC CheckDb Error Message

Previous CheckDB on Read_only database sometimes reports issues ??

Hi,When a bulk insert is done from a flat file or a xls file, how do u ensure the rows are added in the same order as they are in the xls file?Basically, I want to load the data into a table which has an identity column which would need to store the rownumber of each record in the original flat file or xls file.Thanks.

February 11, 2014, 6:47 pm

Next Unable to attach MDF File to sql server

Previous order of rows during bulk insert

Hi,I'm trying to recover a database which unfortunately we don't have any backups of.I have ran the following command "DBCC CHECKDB ('CustomerAccounts') WITH TABLOCK, ALL_ERRORMSGS, NO_INFOMSGS;"And I'm getting this error:Msg 8921, Level 16, State 1, Line 1Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.Msg 8968, Level 16, State 1, Line 1Table error: SinglePage page (0:249856) (object ID 485576768, index ID 2, partition ID 72057594097827840, alloc unit ID 72057594105167872 (type In-row data)) is out of the range of this database.CHECKDB found 1 allocation errors and 0 consistency errors in table 'CTL_DBCONFIG' (object ID 485576768).CHECKDB found 1 allocation errors and 0 consistency errors in database 'CustomerAccounts'.Msg 824, Level 24, State 2, Line 1SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x369dfd27; actual: 0xd5f0c896). It occurred during a read of page (1:1599) in database ID 7 at offset 0x00000000c7e000 in file 'D:\Act Database Files\CustomerAccounts.ADF'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.Please could someone advise.

February 24, 2014, 11:31 pm

Next DBCC CHECKDB wont run - Interesting Challenge

Previous DBCC CheckDb Error Message

Dear All,Please help, I'm unable to attach mdf file to the sql server . Error Message :An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

March 10, 2014, 4:53 pm

Next MSSQL logs eating disk space

Previous Unable to attach MDF File to sql server

Hi All,Very long time since i was last here.Need some advice with this challenge, hopefully Gail or Paul might be around?.SQL 2005 SP2Dedicated NAS - about 8 years old3.5TB database split over multiple volumes, largest datafile is 2.3TB.operating system is windows 2003There have been issues with sql database consistency problems being reported for quite a while, checks on the storage indicate no problem, but I am not convinced. Database is still usable and is a production server, but the server is restarted/rebooted on what used to be weekly, to pretty much every day and recently 3-4 + times a day now.got about 200K messages like the following within the last 24 hours, although this has been ongoing for a long time.SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0xaaaaaaaa; actual signature: 0x75cac85e). It occurred during a read of page (6:91296219) in database ID 5 at offset 0x0000ae223b6000 in file 'I:\.ndf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x784b5a48; actual: 0xe8681f5f). It occurred during a read of page (6:91088683) in database ID 5 at offset 0x0000adbce56000 in file 'I:\.ndf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 6:91296185; actual 2057:151455495). It occurred during a read of page (6:91296185) in database ID 5 at offset 0x0000ae22372000 in file 'I:df'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.basically error 824 serverity 24 state 2all within the same datafile, and ironically one that has autogrowth switched off.msdb..suspect_pages currently has 405 rows with the errorcount for each row varying from 1 to 100kwhen i try to run dbcc checkdb with any options i getMsg 1823, Level 16, State 2, Line 1A database snapshot cannot be created because it failed to start.Msg 5123, Level 16, State 1, Line 1CREATE FILE encountered operating system error 1450(error not found) while attempting to open or create the physical file 'H:\.mdf:MSSQL_DBCC11'.Msg 7928, Level 16, State 1, Line 1The database snapshot for online checks could not be created. Either the reason is given in a previous error or one of the underlying volumes does not support sparse files or alternate streams. Attempting to get exclusive access to run checks offline.Msg 5030, Level 16, State 12, Line 1The database could not be exclusively locked to perform the operation.Msg 7926, Level 16, State 1, Line 1Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.Is causing me a few headaches, no backups exist for this database, dont get me started :-Pdatabase is absolutely critical to the businessReally need advice from someone who understands DBCC CHECKDB extremely well, as I am not sure if the reason DBCC CHECKDB doesnt run, could be down to insufficent space to create the internal snapshot, if someone could give a rough estimate on how much space is required, for a 3.5 TB database, that would be great and bad in a way, as I cannot add any more storage to this server.Seriously hoping i dont need to go down the DBCC PAGE and IND route, with hex editior in hand but have endless supply of caffeine if required. ;-)Any helpful advice is always appreciated, if you need more info, just let me know.CheersDarrennew update, think i might be in new world of painmanaged to work with dbcc checktable briefly, still errors out but recommended repair is allow data loss.done it with estimateonly with main table, need close to 300GB for tempdb, that drive has 2GB free and cannot be increased.

March 19, 2014, 5:31 am

Next corrupted mdf file

Previous DBCC CHECKDB wont run - Interesting Challenge

Hi Guys,We are having an issue with MSSQL logs (C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\) eating disk space after making a backup restore of the server. Logs were eating up to 10 Gb of space in just a day. server crestes SQLDumpxxxx.log,SQLDumpxxxx.mdmp and SQLDumpxxxx.txt> Tried deleting it but it keeps generating logs.[quote]2014-02-15 22:26:37.55 Server Microsoft SQL Server 2005 - 9.00.5000.00 (Intel X86) Dec 10 2010 10:56:29 Copyright (c) 1988-2005 Microsoft Corporation Express Edition with Advanced Services on Windows NT 6.0 (Build 6002: Service Pack 2)2014-02-15 22:26:37.57 Server (c) 2005 Microsoft Corporation.2014-02-15 22:26:37.57 Server All rights reserved.2014-02-15 22:26:37.57 Server Server process ID is 11764.2014-02-15 22:26:37.57 Server Authentication mode is WINDOWS-ONLY.2014-02-15 22:26:37.57 Server Logging SQL Server messages in file 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.2014-02-15 22:26:37.57 Server This instance of SQL Server last reported using a process ID of 3004 at 2/15/2014 10:19:40 PM (local) 2/16/2014 6:19:40 AM (UTC). This is an informational message only; no user action is required.2014-02-15 22:26:37.57 Server Registry startup parameters:2014-02-15 22:26:37.57 Server -d C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf2014-02-15 22:26:37.57 Server -e C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG2014-02-15 22:26:37.57 Server -l C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf2014-02-15 22:26:37.60 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.2014-02-15 22:26:37.60 Server Detected 4 CPUs. This is an informational message; no user action is required.2014-02-15 22:26:38.14 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.2014-02-15 22:26:38.49 Server Database mirroring has been enabled on this instance of SQL Server.2014-02-15 22:26:38.53 spid5s Starting up database 'master'.2014-02-15 22:26:38.74 spid5s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.2014-02-15 22:26:39.08 spid5s SQL Trace ID 1 was started by login "sa".2014-02-15 22:26:39.11 spid5s Starting up database 'mssqlsystemresource'.2014-02-15 22:26:39.13 spid5s The resource database build version is 9.00.5000. This is an informational message only. No user action is required.2014-02-15 22:26:39.52 spid8s Starting up database 'model'.2014-02-15 22:26:39.55 spid5s Server name is 'CORP\SBSMONITORING'. This is an informational message only. No user action is required.2014-02-15 22:26:39.75 Server The certificate was successfully loaded for encryption.2014-02-15 22:26:39.77 Server Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\SBSMONITORING ].2014-02-15 22:26:39.77 Server Server local connection provider is ready to accept connection on [ \\.\pipe\MSSQL$SBSMONITORING\sql\query ].2014-02-15 22:26:39.77 Server Dedicated administrator connection support was not started because it is not available on this edition of SQL Server. This is an informational message only. No user action is required.2014-02-15 22:26:39.78 Server SQL Server is now ready for client connections. This is an informational message; no user action is required.2014-02-15 22:26:39.95 spid8s Clearing tempdb database.2014-02-15 22:26:39.97 spid5s Starting up database 'msdb'.2014-02-15 22:26:41.37 spid8s Starting up database 'tempdb'.2014-02-15 22:26:41.78 spid5s Recovery is complete. This is an informational message only. No user action is required.2014-02-15 22:26:41.79 spid11s The Service Broker protocol transport is disabled or not configured.2014-02-15 22:26:41.79 spid11s The Database Mirroring protocol transport is disabled or not configured.2014-02-15 22:26:41.89 spid11s Service Broker manager has started.2014-02-15 22:30:00.20 Server Server resumed execution after being idle 166 seconds: user activity awakened the server. This is an informational message only. No user action is required.2014-02-15 22:30:00.43 spid51 Starting up database 'SBSMonitoring'.2014-02-15 22:30:01.55 spid51 Recovery is writing a checkpoint in database 'SBSMonitoring' (5). This is an informational message only. No user action is required.2014-02-15 22:32:41.27 spid51 ex_raise2: Exception raised, major=52, minor=43, state=1, severity=22, attempting to create symptom dump2014-02-15 22:32:41.28 spid51 Using 'dbghelp.dll' version '4.0.5'2014-02-15 22:32:41.28 spid51 **Dump thread - spid = 51, PSS = 0x804ED540, EC = 0x804ED5482014-02-15 22:32:41.28 spid51 * 2014-02-15 22:32:41.28 spid51 * User initiated stack dump. This is not a server exception dump.2014-02-15 22:32:41.28 spid51 * 2014-02-15 22:32:41.28 spid51 ***Stack Dump being sent to C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLDump0001.txt2014-02-15 22:32:41.28 spid51 * *******************************************************************************2014-02-15 22:32:41.28 spid51 *2014-02-15 22:32:41.28 spid51 * BEGIN STACK DUMP:2014-02-15 22:32:41.28 spid51 * 02/15/14 22:32:41 spid 512014-02-15 22:32:41.28 spid51 *2014-02-15 22:32:41.28 spid51 * ex_raise2: Exception raised, major=52, minor=43, state=1, severity=22[/quote]Also, just recently we have noticed that disabling the SBS$MONITORING stop it from generating logs. Any idea how is it related, or what could be the issue.Any advise is greatly appreciated.Thank you,jace

March 15, 2011, 8:29 am

Next No catalog entry found for partition ID (MSSQL 2005)

Previous MSSQL logs eating disk space

hello: i was given a corrupted mdf file, the server the database (sql 2000) crashed and the only file recovered was this .mdf file. I have tried as many of the options that i can find, and still cannot get this database recovered. downloaded several of the programs that claim to be able to recover mdf files and none work.. This latest gives me the following error:SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0xaaaaaaaa; actual signature: 0xd8b4c0f2). It occurred during a read of page (0:0) in database ID 0 at offset 0000000000000000 in file 'C:\DBIO\testy.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.I created a new database (sql 2008 r2) since I do not have sql 2000 anywhere. renamed the mdf file and then renamed the corrupted one to the new name and restarted. This then brings up the db in recovery_pending mode and I cannot get it out of that mode. Tried setting it to emergency mode, no luck. Is there anyway for me to recover this db? No there were no backups of this database at all, and yes, i completely agree that a backup strategy is needed, but i'm just the lucky recipient of the issue :)thank you for any help!


September 14, 2009, 4:20 am

Next Backup Failed SQL 2005

Previous corrupted mdf file

I've seen few posts regarding this error msg but none that have yet helped me. This error has happened a few times in our environment at seemingly random times, necessitating a restart of the DB service. SQL logs the error as:Date 9/13/2009 1:06:36 AMLog SQL Server (Archive #1 - 9/13/2009 1:09:00 AM)Source spid57MessageNo catalog entry found for partition ID xxx in database yyy. The metadata is inconsistent. Run DBCC CHECKDB to check for a metadata corruption.In the Windows Event Viewer:Source:MSSQLSERVERTime: 1:06:36 AMCategory: (2)Type: ErrorEventID: 608Description:No catalog entry found for partition ID xxx in database yyy. The metadata is inconsistent. Run DBCC CHECKDB to check for a metadata corruption.The following SQL statement returns no results:SELECT OBJECT_NAME([object_id]) FROM sys.partitions WHERE partition_id = xxxThe SQL log does provide the SpID...is it possible to identify the process that ran into this problem? Are there additional things I can look at to try and determine where/what is causing this problem.Further Note: I do not have any jobs scheduled for this time and DBCC returns no errorsTIA

April 13, 2014, 5:53 pm

Next how to restore .ndf file in database SQL 2005

Previous No catalog entry found for partition ID (MSSQL 2005)

Hi,I cant able to take the database full backup its says database log file is corrupted. Please find the attached screenshot for your ref.Its very urgent. Please help on this.

August 9, 2008, 9:51 pm

Next Backup failed but DBCC CHECKDB has no errors

Previous Backup Failed SQL 2005

hi all,i have mistakenly deleted .ndf file of the database and but i have a backup of that file any one help my how to restore .ndf file in my database or any other suggestion so that my database can worked again right now by database is currpt its not working properly

May 6, 2014, 4:25 am

Next Corrupt DB -repair or restore

Previous how to restore .ndf file in database SQL 2005

SQL 2008 R2 Database. Backup failed with this message:BACKUP 'Db' detected an error on page (1:24909497) in file 'D:\Data\Db.mdf'.Msg 3013, Level 16, State 1, Server xxxxx, Line 1BACKUP DATABASE is terminating abnormally.I ran DBCC CHECKDB on the database which came back without any errors. The backup ran successfully after that and the next day too.I looked at the page with DBCC PAGE - each time I ran it gave a different output. sometimes more sometimes less.msdb..suspect_pages has 2 entries for event type 1 and 2.What would I look for particularly on the output to check for corruption?What would be the next steps to make sure everything is good?Thanks!

December 9, 2008, 1:18 am

Next Error Alerts to detect corruption

Previous Backup failed but DBCC CHECKDB has no errors

I have a dept who would prefer I repair instead of restore else they'll lose 4 hours of work:DBCC CHECKDB with PHYSICAL_only output:DBCC results for 'sysdevcq'.Msg 8909, Level 16, State 1, Line 2Table error: Object ID 0, index ID 0, page ID (1:1515894). The PageId in the page header = (0:2).CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object.Msg 8928, Level 16, State 1, Line 2Object ID 181575685, index ID 255: Page (1:1515893) could not be processed. See other errors for details.Msg 8939, Level 16, State 98, Line 2Table error: Object ID 181575685, index ID 255, page (1:1515893). Test (IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057 and -1.Msg 8928, Level 16, State 1, Line 2Object ID 181575685, index ID 255: Page (1:1515894) could not be processed. See other errors for details.There are 0 rows in 0 pages for object 'cq.attachments_blob'.CHECKDB found 0 allocation errors and 3 consistency errors in table 'cq.attachments_blob' (object ID 181575685).Msg 8928, Level 16, State 1, Line 2Object ID 629577281, index ID 0: Page (1:2085142) could not be processed. See other errors for details.Msg 8941, Level 16, State 102, Line 2Table error: Object ID 629577281, index ID 0, page (1:2085142). Test (sorted [i].offset <= m_freeData) failed. Slot 3, offset 0x202c is invalid.There are 0 rows in 0 pages for object 'cq.history'.CHECKDB found 0 allocation errors and 2 consistency errors in table 'cq.history' (object ID 629577281).CHECKDB found 0 allocation errors and 6 consistency errors in database 'sysdevcq'.repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (sysdevcq ).DBCC execution completed. If DBCC printed error messages, contact your system administrator.Can I repair or shall I restore? Where is Paul Randall when I need him.... :)cheers


More Pages to Explore .....


log file does not match primary file

Here was the initial error in the log on 6/13 at 6:20am:

Starting up database 'XXXXX'.

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.

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.

Log file 'E:\xxxx\LOGS\XXX_log.ldf' does not match the primary file. It may be from a different database or the log may have been rebuilt previously.

When I tried to attach the .mdf and .ldf from a previous days backup, I got the above error also.

when I tried to create a database from just an .mdf and rebuild the log file:

CREATE DATABASE XXXRestore ON

(FILENAME = N'x:\RestoreDB\XXXX.mdf')

FOR ATTACH_REBUILD_LOG

or

EXEC sp_attach_single_file_db @dbname='xxxxxRESTORE',

@physname=N'x:\RestoreDB\xxxxx.mdf'

GO

I got this as a result in the message window:

File activation failure. The physical file name "x:\xxxx\LOGS\xxxxxx_log.ldf" may be incorrect.

The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.

Msg 1813, Level 16, State 2, Line 1

Could not open new database 'xxxxxRestore'. CREATE DATABASE is aborted.

and this in the error log:

Starting up database 'xxxxxRestore'.

Error: 17207, Severity: 16, State: 1.

FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'x:\xxxx\LOGS\xxxx_log.ldf'. Diagnose and correct the operating system error, and retry the operation.

Thank you!

Karen

Windows Management Instrumentation (WMI) is a set of functions embedded into the kernel of Microsoft Operating Systems and Servers, including SQL Server. The purpose of WMI is to allow local and remote monitoring and management of servers. It is a standards-based implementation based on the Distributed Management Task Force’s (DMTF) Web-Based Enterprise Management (WBEM) and Common Information Model (CIM) specifications.

What you need to know is that WMI has many events for SQL Server. Search for WMI to get you started in Books Online, and you will discover the many, many events. You can create alerts on these events. Included are Data Definition Language (DDL) events that occur when databases are created or dropped and when tables are created or dropped, for example.

WMI has a language to query these events called Windows Management Instrumentation Query Language (WQL). It is very much like T-SQL, and you will get comfortable with it immediately.

Browse Books Online for the kind of event you wish to monitor. Each event will have a list of attributes, just like a table has a list of columns. Using WMI, you can select the attributes from the event in an alert.

To create an alert, use SQL Serve Management Studio. In Object Explorer, open the SQL Server Agent tree node, right-click Alerts, and choose New Alert. In the Alert Type drop-down box, choose WMI Event Alert.

The namespace will be populated based on the server you are connected to and should look like this:

\\.\root\Microsoft\SqlServer\ServerEvents\SQL2008

The period (.) represents the server name, which you can change, such as 

\\MYSQLSERVER\. The last node should be MSSQLSERVER for a default instance and the <instance name> for named instances. In the preceding example, the instance I was running is called SQL2008.

In the textbox, you enter your WQL query, as shown here:

SELECT * FROM DDL_DATABASE_LEVEL_EVENTS

Or you could use this query:

Select TSQLCommand from DDL_DATABASE_LEVEL_EVENTS

To select only the TSQLCommand Attribute. There will be a pause when you click OK. If your namespace is incorrect, or the syntax or event/attribute names are incorrect, you will get a message immediately.

Then, in your job, you may use the WMI (attribute) event token — in this case:

Print ‘$(ESCAPE_SQUOTE(WMI(TSQLCommand)))’

To get events from a database, Service Broker notifications must be turned on for that database. To turn on Service Broker notifications for AdventureWorks2008, use the following syntax:

ALTER DATABASE AdventureWorks2008 SET ENABLE_BROKER;

If your alerts are occurring but the text replacement for the WMI token is not being done, you probably need to turn on the Service Broker for your database.

The service account that SQL Server Agent uses must have permission on the namespace and ALTER ANY EVENT NOTIFICATION permissions. This will be done automatically if you use SQL Server Configuration Manager to set up accounts. However, to adjust these settings manually, from the Run prompt, type wmimgmt.msc. An administrative dialog will appear, allowing you to set up permissions.

There is a test program for WMI on your server. To run it from the command line, type WBEMTest. It is installed in the WBEM directory of your Windows system directory.

Microsoft has an entire subsection of its Web site devoted to WMI. Just search for WMI on Microsoft Corporation.

http://www.itechtalk.com/thread2080.html

Read Full Post »

How to troubleshoot when SQL Server Services don't start

Top 3 reasons the SQL server services won’t start.

Reason # 1:Service account password changed but not updated on the server where SQL Server instance is installed.

This is one of the most common cause where service account password has been changed by domain admin or SQL Admin but this information is not updated in SQL Server Services.

Hereis the error which we would see if we try to start using Services.

---------------------------Services---------------------------Windows could not start the SQL Server (MSSQLSERVER) service on Local Computer.Error 1069: The service did not start due to a logon failure.---------------------------

System Event logs should show below

Log Name:      System

Source:        Service Control Manager

Date:          <Date Time>

Event ID:      7000

Task Category: None

Level:         Error

Keywords:      Classic

User:          N/A

Computer:     MyServer

Description:The SQL Server (MSSQLSERVER) service failed to start due to the following error:

The service did not start due to a logon failure.

Solution: We need to update the password in services.The right way to do itisto use SQL Server Configuration Manager and type in new password(under Log On tab).

Reason # 2:Startup parameters have incorrect file path locations.

This is another common cause of SQL Server Service startup failure.Let’s assume that master database is located on a drive and files of the database (master.mdf and/or mastlog.ldf) are not available. Since master database is a system database, SQL Service would fail to start. If we try to start SQL via services, we will get below error.

---------------------------

Services

---------------------------

Windows could not start the SQL Server (MSSQLSERVER) on Local Computer. For more information, review the System Event Log. If this is a non-Microsoft service, contact the service vendor, and refer to service-specific error code 17113.

---------------------------If we attempt the same via configuration manager, we get standard error which doesn’t explain much.

---------------------------

SQL Server Configuration Manager

---------------------------

The request failed or the service did not respond in a timely fashion. Consult the event log or other applicable error logs for details.

---------------------------

Whenever we get such errors, we should start looking at SQL Server ERRORLOGs are defined under start-up parametersor application event log.We can look at SQL Server Configuration Manager and look for Startup parameter having name -e as shown below (for SQL 2014):

We can open ERRORLOG using notepad or any other text editor. Here is the snippet which shows the problem.

<Date Time> Server      Error: 17113, Severity: 16, State: 1.

<Date Time> Server     Error 2(The system cannot find the file specified.)occurred while opening file 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\master.mdf' to obtain configuration information at startup. An invalid startup option might have caused the error. Verify your startup options, and correct or remove them if necessary.

As highlighted above, we can see that SQL is not able to find the file master.mdf.

Below is another example of error for model database.

<Date Time>spid9s Error: 17207, Severity: 16, State: 1.<Date Time>spid9s FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.)occurred while creating or opening file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\modellog.ldf'. Diagnose and correct the operating system error, and retry the operation.<Date Time>spid9s File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\modellog.ldf" may be incorrect.<Date Time>spid9s Error: 945, Severity: 14, State: 2.<Date Time>spid9s Database 'model' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server error log for details.<Date Time>spid9s Could not create temp db. You may not have enough disk space available. Free additional disk space by deleting other files on the temp db drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the temp db files could not be initialized.

Solution:First we need to find out the correct location of the files. Once files are identified, either put them into the location where SQL Server wants or ALTER the database to point to correct location.If there is an issue with model or temp db database, then we need to start SQL Server using trace flag3608.

Reason #3:System database files not available -accidental deletion or corruption due to disk failures.

If files are missing or corrupted for system databases (master and/or model) SQL Server service would not start. ERROR LOG (mentioned earlier)would contain the exact database name and file name which has the problem.Here are few snippets of error pointing to corruption of system databases.

<Date Time>spid5s Starting up database 'master'.

<Date Time> spid5sError: 9003, Severity: 20, State: 1.

<Date Time> spid5sThe log scan number (216:72:1) passed to log scan in database 'master' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.

<Date Time>spid5s Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.

Depending on which database file is corrupted, we need to take appropriate action. If master database files are corrupted (error above)then we need to rebuild the master database and restore it from the backup. If issue exists with other system databases, then SQL can be started via trace flag and they can be restored.

Fix SQL Database Error 5173 – Attach database failed for Server

Summary: In this article, we will discuss different solutions to fix the error 5173 in SQL Server. The solutions include rebuilding a missing or corrupt log file (.ldf) and repairing primary data file (.mdf) using SQL repair tool.

Fix SQL Database Error 5173 – Attach database failed for Server

Contents

Introduction

I had a colleague who experienced issues attaching a SQL Server database and encountered SQL database error 5173 – Attach database failed to Server:

Error 5173: Cannot associate files with different databases.

Attach database failed for Server Serv4567′.  (Microsoft.SqlServer.Smo)

SQL Database Error 5173

Additional Information:

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

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.
CREATE DATABASE failed. Some file names listed could not be created. Check related errors. (Microsoft SQL Server, Error: 5173)

This error-prone database file was an important marketing database actually and the customers were complaining because they could not access the database which my colleague was trying to migrate to a new Server with more RAM, a nice cluster server, and powerful hard drives. This is a famous attach error that can occur in a database.

Requirements

The following requirements will help you to follow this article:

  1. This article will use SQL Server in any version.
  2. Finally, you will need SQL Server Management Studio.

Getting started

Here we will check the different solutions available—

In databases we have 3 different types of files for SQL Server:

  1. The primary data file with .MDF extension is the main file and contains the data.
  2. The secondary data file (.NDF) is not created by default but can be created if you want to separate the data in different hard drives for security and performance reasons.
  3. The log file (.LDF) stores the log information. It is used to recover the database. It is possible to recover the data to a specific time.

The sp_helpfile system stored procedure shows all the files in a specific database. It will show the size, path, filegroup, and id.

Syntax:

sp_helpfile [ [ @filename= ] 'name' ]

Where, [ @filename = ] ‘name’ is the logical name of any file in the current database.

Stellar

The problem is usually that the mdf file does not have the same date as that of .LDF file. For example, when a mdf file of June 2 tries to be restored using the ldf file of June 3.

A possible solution to fix SQL database error 5173

If log file is missing or corrupt

Once recovered, if you have the 5173 error and you cannot find the correct log file (.LDF), it is possible to rebuild a .LDF file for the .MDF file restored. The following T-SQL code will help you to rebuild the log file for the .MDF file:

CREATE DATABASE yourdatabase ON (FILENAME = 'c:\yourPrimaryFile_Data.mdf') FOR ATTACH_REBUILD_LOG ;

The code will create a database based on the mdf file and generate a new log file for the mdf file. For attach_rebuild_log creates a new log for you.

If database is corrupt

If your primary data file does not match with the log file and your primary data file is corrupt, you can do the following:

  • Verify that the primary file belongs to the log file. Sometimes you are attaching the wrong log file with the primary file.
  • Recover the damaged primary data file using Stellar Repair for MS SQL to repair corrupt SQL database primary and secondary file.

You can download the software from here:

free download
  • This is the main interface of Stellar Repair for MS SQL
Stellar
  • Select the MDF file which you want to repair
Stellar
  • Click Repair to start scanning and repair process
Stellar
  • Check preview of database
Stellar
  • Stellar Repair for MS SQL software restores your .MDF file, detect your SQL Server database objects and you can export the data to SQL Server (MDF), Excel, CSV, HTML.
Stellar

The software requires to have the MS SQL Service Stopped before initiating the SQL database recovery process. You can restore the .MDF file in any machine with or without MS SQL Server.

Conclusion

The error 5173 in Microsoft SQL Server is related to a problem with the primary data file and the log file. Due to some accident, the files do not match and the SQL Database assumes that the files belong to different databases.

If the primary data file is corrupt, you can use Stellar Repair for MS SQL. This software can repair a corrupt database using simple software for that.  Once repaired (if necessary), you can rebuild the log file using the create database sentence and use the for attach_rebuild_log. This option will generate a new log for you.

About The Author

Priyanka

Priyanka is a technology expert working for key technology domains that revolve around Data Recovery and related software's. She got expertise on related subjects like SQL Database, Access Database, QuickBooks, and Microsoft Excel. Loves to write on different technology and data recovery subjects on regular basis. Technology freak who always found exploring neo-tech subjects, when not writing, research is something that keeps her going in life.

Best Selling Products

Stellar Repair for MS SQL

Stellar Repair for MS SQL

Stellar Repair for MS SQL is an enterpri

Read More
Stellar Toolkit for MS SQL

Stellar Toolkit for MS SQL

3-in-1 software package, recommended by

Read More
Stellar Converter for Database

Stellar Converter for Database

Stellar Converter for Database is an eff

Read More
Stellar Repair for Access

Stellar Repair for Access

Powerful tool, widely trusted by users &

Read More

“ I installed SQL Server 2008 and its instance name is different. I restarted my SQL Server and after restarting my system I found that some of my databases are in recovery pending state.Why is this happening? Please suggest me solution to fix this recovery pending error.”

This might have happen to you also when database shows you state except Online, which is a problem. Database becomes inacessible encountering “Pending State Error”.

Confused? In which State you are in
If you are confused in what state your database is, you can simply check the state of a database by sys.databases catalog view or by DATABASEPROPERTYEX function. If your database is in recovery pending state, the DatabasePropertyex function will show database in Suspect Mode.

Why is this happening ??

There are various reasons for getting this error, some of them are listed below:

  • Corruption in Master Database File
  • Power Failure
  • Faulty Hardware
  • Sudden shutdown of SQL Server
  • Less memory space or insufficient disk space
  • Database is not cleanly shut down
  • Files which are used by the database dont belong to same database.

Error You May Encounter

While trying to bring database in Online Mode, user may encounter the following errors:

"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 backup."

"Database [dbname] cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details."

"Log file [log name] does not match the primary file. It may be from a different database or the log may have been rebuilt previously."

Fix Database Recovery Pending State SQL Server

Note : Take backup of Primary (.mdf), secondary (.ndf) and log (.ldf) database files

Perform step by step procedure to resolve Recovery Pending State error.

  1. Set database to Emergency Mode:
  2. Set database in Multi User.
  3. Now Detach and Reattach the data file by running below commands:

Or if your database file is corrupted, perform the following steps:

This will repair the database corruption, if persists in SQL Server Database.

Making Smart Move

There could be the possibility that the Pending state does not get resolved by the manual approach. For better and quick approach to resolve SQL Server Recovery Pending State is using MDF Data Recovery. It repair your database from Suspect Mode and make your database accessible for use. Also if your database file is found to be corrupted, the software recovers the database from Corruption.

Conclusion

Database not accessible ? You might be facing Recovery Pending State error. The blog discusses the same. It covers the reasons why recovery pending state error occurs and the solution for the same.

1 Comments

Leave a Comment