SQL Server 2005 Practical Troubleshooting: User database inaccessible

Learn SQL Server 2005 tools that allow access to an inaccessible database. This excerpt from "SQL Server 2005" provides the toolbox.

User Database Inaccessible

Let's shift focus now and talk about scenarios where you may have problems accessing your database. The scenarios are similar to some of the system database situations, but other options are available for recovery, because SQL Server can start even if your database cannot.

Database Marked RECOVERY_PENDING

Remember the database state diagram I explained earlier in the chapter. If there is any problem opening the database or transaction log files for a database, the database state is changed to RECOVERY_PENDING. In this scenario, if you attempt to access the database, you get the following error:

 use pubs go Msg 945, Level 14, State 2, Line 1 Database 'pubs' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details. 

Much like the case with MSDB, the ERRORLOG shows errors like these:

 2006-03-23 14:26:05.99 spid18s Error: 17207, Severity: 16, State: 1. 2006-03-23 14:26:05.99 spid18s FCB::Open: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATApubs.mdf'. Diagnose and correct the operating system error, and retry the operation. 2006-03-23 14:26:06.10 spid18s Error: 17204, Severity: 16, State: 1. 2006-03-23 14:26:06.10 spid18s FCB::Open failed: Could not open file C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATApubs.mdf for file number 1. OS error: 2(The system cannot find the file specified.). 2006-03-23 14:26:06.21 spid18s Error: 5120, Severity: 16, State: 101. 2006-03-23 14:26:06.21 spid18s Unable to open the physical file "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATApubs.mdf". Operating system error 2: "2(The system cannot find the file specified.)". 

Unfortunately in this case, Management Studio does not give you any clue in Object Explorer that the database may have a problem (see Figure 2-6).

Figure 2-6 Management Studio does not indicate that the database may have a problem.

You can see, though, that next to the pubs database icon there is no plus sign (+) to examine objects in that database. This is a clue that there may be an issue with pubs. If you run into this situation, try to right-click the database icon and select Properties. This forces Management Studio to try and access the database (trying to obtain metadata about the state of files in the database).

In this particular scenario, you get a dialog box with any errors encountered trying to access properties for the database (in this case, Msg 945), as shown in Figure 2-7.

 

Figure 2-7 Dialog box with errors encountered while trying to access properties for the database

If you query the sys.databases catalog view, you can see the pubs database status of RECOVERY_PENDING:

 select state_desc, name from sys.databases where name = 'pubs' go state_desc name ------------------------------------------------------------ ---------------------------- RECOVERY_PENDING pubs 

If the transaction log file is not available, the database state is marked RECOVERY_PENDING, but errors reported in the ERRORLOG are slightly different:

 2006-03-23 14:47:12.81 spid13s Error: 17207, Severity: 16, State: 1. 2006-03-23 14:47:12.81 spid13s FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATApubs_log.LDF'. Diagnose and correct the operating system error, and retry the operation. 2006-03-23 14:47:12.81 spid13s File activation failure. The physical file name "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATApubs_log.LDF" may be incorrect. 

There is an interesting exception to this scenario when the transaction log file cannot be opened and the database uses a recovery mode of SIMPLE. If the database was shut down "cleanly," which means no recovery is required for the database on startup and the database recovery mode is SIMPLE, it is a safe operation for SQL Server to rebuild the transaction log. Why?

  • A SIMPLE recovery has no log backups, so media recovery is not an issue.
  • If there is no recovery to run on the database, no consistency is lost by rebuilding the log.

In this situation, on database startup, you will see some failures in the ERRORLOG but also a message indicating the transaction log is being rebuilt:

 2006-03-23 14:42:59.28 spid13s Error: 17207, Severity: 16, State: 1. 2006-03-23 14:42:59.28 spid13s FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATApubs_log.LDF'. Diagnose and correct the operating system error, and retry the operation. 2006-03-23 14:42:59.36 spid13s File activation failure. The physical file name "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATApubs_log.LDF" may be incorrect. 2006-03-23 14:42:59.68 spid12s Starting up database 'troy'. 2006-03-23 14:43:00.23 spid15s CHECKDB for database 'test_checksum' finished without errors on 2005-09-22 11:31:57.547 (local time). This is an informational message only; no user action is required. 2006-03-23 14:43:01.06 spid12s CHECKDB for database 'troy' finished without errors on 2006-03-19 19:36:09.937 (local time). This is an informational message only; no user action is required. 2006-03-23 14:43:01.17 spid13s New log file 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATApubs_log.LDF' was created. 2006-03-23 14:43:01.17 spid13s New log file 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATApubs_log.LDF' was created. 

As I discuss later in this section, this comes in handy for attaching SIMPLE model databases with a single transaction log file. You don't even need the transaction log file to attach, because it is perfectly safe for SQL Server to rebuild one for you. Unfortunately, the log is rebuilt to a default size of 1MB, so you need to resize this to your needs.

Troubleshooting Steps

If the cause for RECOVERY_PENDING is inability to open the database files, the steps are simple, exactly as they were for MSDB:

  1. Correct the problem that caused the file open failure.
  2. Run ALTER DATABASE SET ONLINE.
  3. If ALTER DATABASE doesn't report any errors, you know that the database started successfully. You can also see your actions allowed the database to start in the ERRORLOG:

 2006-03-23 15:07:44.18 spid51 Setting database option ONLINE to ON for database pubs. 2006-03-23 15:07:44.25 spid51 Starting up database 'pubs'. 

If no errors exist in the ERRORLOG after the "Starting up" message, the database should now be ONLINE (which you can also confirm by looking at sys.databases).

There is an interesting situation involving a failure to open a database or log file I want you to know about because it is tricky to troubleshoot. Look at the following ERRORLOG entries and notice the OS Error description for why the file cannot be opened:

 2006-03-23 15:15:25.71 spid15s Error: 17207, Severity: 16, State: 1. 2006-03-23 15:15:25.71 spid15s FCB::Open: Operating system error 32(The process cannot access the file because it is being used by another process.) occurred while creating or opening file 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATApubs.mdf'. Diagnose and correct the operating system error, and retry the operation. 2006-03-23 15:15:25.71 spid15s Error: 17204, Severity: 16, State: 1. 2006-03-23 15:15:25.71 spid15s FCB::Open failed: Could not open file C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATApubs.mdf for file number 1. OS error: 32(The process cannot access the file because it is being used by another process.). 2006-03-23 15:15:25.71 spid15s Error: 5120, Severity: 16, State: 101. 2006-03-23 15:15:25.71 spid15s Unable to open the physical file "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATApubs.mdf". Operating system error 32: "32(The process cannot access the file because it is being used by another process.)". 

How can this situation occur? Why would SQL Server not be able to open a file because another program had it open? How do I find what program is doing this? There is a nice utility on the web called Process Explorer (which you can download from www.sysinternals.com).

To find which program has this open, launch Process Explorer and select the Find, Find Handle menu option. Type in the name of the file in the SQL error, and the program will tell you exactly what process has your database file handle open (see Figure 2-8).

What process your database file handle opened

In this case, someone has accidentally opened the file using Microsoft Word. Well, okay, it was me deliberately doing this to demonstrate the problem.

The same troubleshooting steps I've provided for database files apply to transaction log files. But what if you cannot resolve the problem with accessing the transaction log file? Do you have any other options? You do, but there are limits on how successful they will be.

In this situation, you can change the database state to EMERGENCY using ALTER DATABASE SET EMERGENCY. At this point, you can access the database to query your data. But only SELECT statements to read data. You will encounter the following error if you try to modify the database:

 Msg 3908, Level 16, State 1, Line 1 Could not run BEGIN TRANSACTION in database 'pubs' because the database is in bypass recovery mode. 

You can run some diagnostics like DBCC CHECKDB. However, you may get errors from this command, because the transaction log file was not available and recovery could not run. Recovery may be necessary to bring the database into a consistent state. So, your options are to copy all the data from your database to a new one or to use emergency mode repair. I covered this in the earlier section on enhancements to DBCC CHECKDB for SQL Server 2005.

Here are the steps to use CHECKDB in this situation (assuming the preceding example, where the pubs transaction log file could not be opened):

  1. . ALTER DATABASE pubs SET EMERGENCY.
  2. . ALTER DATABASE pubs SET SINGLE_USER.
  3. . DBCC CHECKDB (pubs, REPAIR_ALLOW_DATA_LOSS).
  4. . CHECKDB returns the following messages:

 File activation failure. The physical file name "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATApubs_log.LDF" may be incorrect.Warning: The log for database 'pubs' 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, you will need to reset database options and delete any extra log files. DBCC results for 'pubs'. Service Broker Msg 9675, State 1: Message Types analyzed: 14. Service Broker Msg 9676, State 1: Service Contracts analyzed: 6. Service Broker Msg 9667, State 1: Services analyzed: 3. Service Broker Msg 9668, State 1: Service Queues analyzed: 3. Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0. Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0. Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0. DBCC results for 'sys.sysrowsetcolumns'. There are 626 rows in 6 pages for object "sys.sysrowsetcolumns". DBCC results for 'sys.sysrowsets'. There are 97 rows in 1 pages for object "sys.sysrowsets". DBCC results for 'sysallocunits'. There are 110 rows in 2 pages for object "sysallocunits". DBCC results for 'sys.sysfiles1'. There are 2 rows in 1 pages for object "sys.sysfiles1". DBCC results for 'sys.syshobtcolumns'. There are 626 rows in 6 pages for object "sys.syshobtcolumns". DBCC results for 'sys.syshobts'. There are 97 rows in 1 pages for object "sys.syshobts". DBCC results for 'sys.sysftinds'. There are 0 rows in 0 pages for object "sys.sysftinds". DBCC results for 'sys.sysserefs'. There are 110 rows in 1 pages for object "sys.sysserefs". DBCC results for 'sys.sysowners'. There are 14 rows in 1 pages for object "sys.sysowners". DBCC results for 'sys.sysprivs'. There are 123 rows in 1 pages for object "sys.sysprivs". DBCC results for 'sys.sysschobjs'. There are 99 rows in 2 pages for object "sys.sysschobjs". DBCC results for 'sys.syscolpars'. There are 497 rows in 9 pages for object "sys.syscolpars". DBCC results for 'sys.sysnsobjs'. There are 1 rows in 1 pages for object "sys.sysnsobjs". DBCC results for 'sys.syscerts'. There are 0 rows in 0 pages for object "sys.syscerts". DBCC results for 'sys.sysxprops'. There are 0 rows in 0 pages for object "sys.sysxprops". DBCC results for 'sys.sysscalartypes'. There are 30 rows in 1 pages for object "sys.sysscalartypes". DBCC results for 'sys.systypedsubobjs'. There are 0 rows in 0 pages for object "sys.systypedsubobjs". DBCC results for 'sys.sysidxstats'. There are 127 rows in 2 pages for object "sys.sysidxstats". DBCC results for 'sys.sysiscols'. There are 244 rows in 1 pages for object "sys.sysiscols". DBCC results for 'sys.sysbinobjs'. There are 23 rows in 1 pages for object "sys.sysbinobjs". DBCC results for 'sys.sysobjvalues'. There are 147 rows in 22 pages for object "sys.sysobjvalues". DBCC results for 'sys.sysclsobjs'. There are 14 rows in 1 pages for object "sys.sysclsobjs". DBCC results for 'sys.sysrowsetrefs'. There are 0 rows in 0 pages for object "sys.sysrowsetrefs". DBCC results for 'sys.sysremsvcbinds'. There are 0 rows in 0 pages for object "sys.sysremsvcbinds". DBCC results for 'sys.sysxmitqueue'. There are 0 rows in 0 pages for object "sys.sysxmitqueue". DBCC results for 'sys.sysrts'. There are 1 rows in 1 pages for object "sys.sysrts". DBCC results for 'sys.sysconvgroup'. There are 0 rows in 0 pages for object "sys.sysconvgroup". DBCC results for 'sys.sysdesend'. There are 0 rows in 0 pages for object "sys.sysdesend". DBCC results for 'sys.sysdercv'. There are 0 rows in 0 pages for object "sys.sysdercv". DBCC results for 'sys.syssingleobjrefs'. There are 163 rows in 1 pages for object "sys.syssingleobjrefs". DBCC results for 'sys.sysmultiobjrefs'. There are 133 rows in 1 pages for object "sys.sysmultiobjrefs". DBCC results for 'sys.sysdbfiles'. There are 2 rows in 1 pages for object "sys.sysdbfiles". DBCC results for 'sys.sysguidrefs'. There are 0 rows in 0 pages for object "sys.sysguidrefs". DBCC results for 'sys.sysqnames'. There are 91 rows in 1 pages for object "sys.sysqnames". DBCC results for 'sys.sysxmlcomponent'. There are 93 rows in 1 pages for object "sys.sysxmlcomponent". DBCC results for 'sys.sysxmlfacet'. There are 97 rows in 1 pages for object "sys.sysxmlfacet". DBCC results for 'sys.sysxmlplacement'. There are 17 rows in 1 pages for object "sys.sysxmlplacement". DBCC results for 'sys.sysobjkeycrypts'. There are 0 rows in 0 pages for object "sys.sysobjkeycrypts". DBCC results for 'sys.sysasymkeys'. There are 0 rows in 0 pages for object "sys.sysasymkeys". DBCC results for 'sys.syssqlguides'. There are 0 rows in 0 pages for object "sys.syssqlguides". DBCC results for 'sys.sysbinsubobjs'. There are 0 rows in 0 pages for object "sys.sysbinsubobjs". DBCC results for 'publishers'. There are 8 rows in 1 pages for object "publishers". DBCC results for 'titles'. There are 18 rows in 1 pages for object "titles". DBCC results for 'titleauthor'. There are 25 rows in 1 pages for object "titleauthor". DBCC results for 'stores'. There are 6 rows in 1 pages for object "stores". DBCC results for 'sales'. There are 21 rows in 1 pages for object "sales". DBCC results for 'roysched'. There are 86 rows in 1 pages for object "roysched". DBCC results for 'discounts'. There are 3 rows in 1 pages for object "discounts". DBCC results for 'jobs'. There are 14 rows in 1 pages for object "jobs". DBCC results for 'pub_info'. There are 8 rows in 1 pages for object "pub_info". DBCC results for 'employee'. There are 43 rows in 1 pages for object "employee". DBCC results for 'x'. There are 0 rows in 0 pages for object "x". DBCC results for 'sys.queue_messages_1977058079'. There are 0 rows in 0 pages for object "sys.queue_messages_1977058079". DBCC results for 'sys.queue_messages_2009058193'. There are 0 rows in 0 pages for object "sys.queue_messages_2009058193". DBCC results for 'sys.queue_messages_2041058307'. There are 0 rows in 0 pages for object "sys.queue_messages_2041058307". DBCC results for 'authors'. There are 23 rows in 1 pages for object "authors". CHECKDB found 0 allocation errors and 0 consistency errors in database 'pubs'. DBCC execution completed. If DBCC printed error messages, contact your system administrator. 

Pay special attention to the following message in this chain:

Warning: The log for database 'pubs' 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, you will need to reset database options and delete any extra log files. 

What does this message mean? Well, it has a bunch of warnings, so let me summarize them for you:

  • Transactional consistency

Even though DBCC CHECKDB reports no errors, it doesn't mean that the database is logically consistent. What I mean is that since the log was rebuilt there could have been modifications to the database that need to be redone or undone during recovery for transactions to be consistent.

Consider the following scenario. Your application credits a customer account for $10,000. The rows on the database page are updated to reflect a credit to the customer's account. Let's say the user of the application is not done with this customer, so by design the transaction for this operation is still active. But, because of memory pressure in the engine, this page is flushed to disk. To ensure consistency, SQL Server also flushes the log records associated with the transaction to disk. But the user realizes he made a mistake and clicks the Cancel button. This causes the application to roll back the transaction. In memory, the page and log records are changed. Within the next few minutes, a serious problem occurs on the server, and it crashes and reboots. SQL Server is robust to crash recovery. Even though the database page on disk shows a credit for $10,000, the transaction log never shows this transaction was committed. So when recovery runs, the modification undone. But what if the engine encountered an error opening the transaction log file and you choose to use emergency mode repair? Recovery never ran, so a credit exists for $10,000 for this customer account. Who would be able to find this problem? Could this really occur? The timing might be difficult, but who would want to take this risk? This is why the SQL Server development team put the text in this message and is why Microsoft PSS always has a serious conversation with customers who want to rebuild their transaction log.

  • RESTORE chain broken

    Because we rebuild the transaction log, your backup log chain sequence is broken. You cannot rely on these backups, and you need to take a full backup of the database to restart a new backup sequence.

  • Database options

    The database is left in SINGLE_USER mode and is changed to the SIMPLE recovery model.

    The SQL Server development team put emergency mode repair in the product for customers to use, but only, as the name implies, for emergency situations. The development team also put in this message so that it would be clear to you that the server cannot vouch for the transactional consistency of your database. Just to make sure you understand that you or another DBA performed this operation, the server writes the following messages to the ERRORLOG:

 2006-03-23 15:37:48.01 spid51 Warning: The log for database 'pubs' 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, you will need to reset database options and delete any extra log files. 2006-03-23 15:37:48.01 spid51 Warning: The log for database 'pubs' 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, you will need to reset database options and delete any extra log files. 2006-03-23 15:37:48.57 spid51 EMERGENCY MODE DBCC CHECKDB (pubs, repair_allow_data_loss) executed by NORTHAMERICAbobward found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds. 

Handling Deferred Transactions

As discussed in the "Storage Internals" section, when recovery fails for a user database but the cause of the recovery is damage to a database page, SQL Server may not mark the database SUSPECT. Instead, the engine marks a bit on the damaged page in the header (set to a status called RestorePending). If the transaction associated with the operation that encountered the damaged page is still active after redo, all locks are held that are part of the transaction, and undo for the transaction is skipped (or deferred). The good news is that the database is still ONLINE and not SUSPECT. Only users who need to access this page or access data associated with any deferred transaction are affected.

How do you know whether recovery had to defer transactions but not mark the database SUSPECT?

  • You see evidence in the ERRORLOG.
  • You encounter an error when accessing a page that is marked RestorePending.
  • You are blocked by session_id = -3.

The choices and steps for recovering a deferred transaction are simple:

  • Restore a backup. But in this case, because only a single page is damaged, you could use online page restore to keep the rest of the database online and affect only the deferred transaction.
  • If you don't have a backup to use, you can repair the page (but lose the data on it) by using DBCC CHECKDB and the REPAIR_ALLOW_DATA_LOSS option.

The steps for using page-level restore are documented in Books Online in the section "Performing Page Restores." Although I won't add anything about these steps in this section, there is an exercise in the end of this chapter to walk you through using this feature during a data recovery scenario.

Finding the root cause for a deferred transaction is not complicated, because the cause is damage to the database page, as outlined in the ERRORLOG (for example, a checksum error on a page). Therefore, you must investigate the cause of this problem just like you would for any checksum failure. As I mentioned earlier in this chapter, the cause of a checksum page error is not SQL Server but a problem in the IO path.

Database Marked SUSPECT

Suppose recovery fails and one of the conditions for a deferred transaction doesn't qualify or the failure is not associated with a single database page (for example, damage to the transaction log is detected). In this situation, the engine changes the database state to SUSPECT.

The following is an example of an ERRORLOG entry for a database marked SUSPECT:

 2006-03-26 22:14:10.98 spid13s Error: 824, Severity: 24, State: 4. 2006-03-26 22:14:10.98 spid13s SQL Server detected a logical consistency-based I/O error: (bad checksum). It occurred during a read of page (0:-1) in database ID 8 at offset 0x00000000013800 in file 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAsuspect_db_log.LDF'. 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. 2006-03-26 22:14:11.43 spid13s Error: 3414, Severity: 21, State: 1. 2006-03-26 22:14:11.43 spid13s An error occurred during recovery, preventing the database 'suspect_db' (database ID 8) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support. 

Any attempt to access the database results in the following error:

 Msg 926, Level 14, State 1, Line 1 Database 'suspect_db' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information. 

Fortunately, Management Studio gives us a visual clue to the status of the database (see Figure 2-9).

Figure 2-9 Management Studio gives a visual clue to the status of the database

As in the case of a deferred transaction, your options to recover are similar:

  • You can restore from a backup. Page-level restore is not an option here, because the problem is not specific to a database page.
  • Use DBCC CHECKDB for emergency mode repair. I outlined how this works when I discussed the scenario of the transaction log being unavailable.
  • If emergency mode repair does not work, you could change the database state to EMERGENCY and try to copy as much data as possible.

Remember that the SUSPECT state for a database is temporary. This means to find the original cause for why the database was marked SUSPECT, you can either restart SQL Server or you can try to change the database state to ONLINE. Consider the example of the database suspect_db. If I try to change the database state to ONLINE, I get the following messages:

 Msg 926, Level 14, State 1, Line 1 Database 'suspect_db' 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 1 ALTER DATABASE statement failed. Msg 824, Level 24, State 4, Line 1 SQL Server detected a logical consistency-based I/O error: (bad checksum). It occurred during a read of page (0:-1) in database ID 8 at offset 0x00000000013800 in file 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAsuspect_db_log.LDF'. 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. Msg 3414, Level 21, State 1, Line 1 An error occurred during recovery, preventing the database 'suspect_db' (database ID 8) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support. 

The first message after 5069 is the root cause of the SUSPECT failure, which in this example is a checksum failure (Msg 824) when reading the transaction log. (Yes, the transaction log uses checksum technology, too, to detect IO consistency.)

Attach Database Failures

It is possible to encounter different types of errors when attaching a database. First, let me eliminate one consideration that could occur in previous versions of SQL Server. You can't detach a database whose state is SUSPECT. Attempting to do this results in the following error:

 Msg 3707, Level 16, State 2, Line 1 Cannot detach a suspect database. It must be repaired or dropped. 

Although this may be true, you could technically do something you are not supposed to do. You could shut down SQL Server and still use the files from a SUSPECT database to attach to another server. In this case, the attach fails because the database was SUSPECT. Of course, you are supposed to always detach a database before you attach it. I've just now showed you an example of why it is important to do this.

Most problems I see with attaching databases have to do with missing some of the files associated with the database. If the database was detached properly (which means it was cleanly shut down) and the database has only one transaction log file, all you need to attach the database are the database files. The log will be rebuilt safely as I've described earlier when the log file is not available at startup (even for a database using FULL recovery). But if you do not have all the transaction log files as they existed for the original database, the server returns the following error:

 File activation failure. The physical file name "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAtest_attach_log2.ldf" may be incorrect. The log was not rebuilt because there is more than one log file. Msg 1813, Level 16, State 2, Line 1 Could not open new database 'test_attach'. CREATE DATABASE is aborted 

In SQL Server 2000, you had no options at this point. SQL Server 2005 offers a new option to FOR ATTACH_REBUILD_LOG. This option still requires the database to be detached properly, but it lets you rebuild the log if you don't have all transaction log files.

Use the following table of contents to navigate to chapter excerpts, or click here to view Data corruption and recovery issues in its entirety.

 

 


SQL Server 2005 practical troubleshooting


  Home:  Introduction
  1: Fundamentals & SQL Server 2005 storage internals
  2:SQL Server 2005 enhancements
  3:Data Recovery Best Practices
  4:Data recovery trouble shooting scenarios
  5:User Database Inaccessible
  6:BACKUP/RESTORE Failures
  7:Database consistency errors

About the book:   
 
In this book, bestselling author and SQL Server guru Ken Henderson has worked with the SQL Server Development team to write "the" troubleshooting SQL Server 2005 book. All of the content comes directly from the creators and developers of SQL Server. Ken has edited each chapter to ensure that the writing is clear and sound. Written by the people who know the product best - the people who built it - SQL Server 2005 Practical Troubleshooting teaches developers and dbas how to troubleshoot and diagnose problems they may encounter with SQL Server. Purchase the book from Addison-Wesley Publishing
ABOUT THE AUTHOR:   
 
Ken Henderson has been a developer for more than 25 years. He has worked with SQL Server since 1990 and has built software for a number of firms throughout his career, including H&R Block, the Central Intelligence Agency, the U.S. Navy, the U.S. Air Force, Borland International, JP Morgan, and various others. He joined Microsoft in 2001 and is currently a developer in the Manageability Platform group within the SQL Server development team. He is the creator of SQL Server 2005's SQLDiag facility and spends his days working on SQL Server management tools and related technologies. He is the author of eight books on a variety of computing topics, including the popular Guru's Guide series of SQL Server books available from Addison-Wesley. He lives with his family in the Dallas area and may be reached via email at khen@khen.com.


This was first published in March 2007

Dig deeper on Database Management Products and Solutions

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

MicroscopeUK

SearchCloudProvider

SearchSecurity

SearchStorage

SearchNetworking

SearchCloudComputing

SearchConsumerization

SearchDataManagement

SearchBusinessAnalytics

Close