SQL Server 2005 Practical Troubleshooting: Fundamentals

Build up your SQL Server 2005 fundamental data recovery knowledge-base and leverage that knowledge to protect your customer's data in this excerpt from SQL "Server 2005 Practical Troubleshooting."

Fundamentals & SQL Server 2005 storage internals

Anyone who is good at solving problems will tell you it is important to know something about "how things work." Therefore, in this section, I help build your knowledge of important internal information for SQL Server 2005 related to the general topic of data recovery. But first, I recommend you review the following sections of SQL Server Books Online. Fundamental information in these sections provides a foundation for what I discuss:

  • Database Engine Manageability Enhancements
  • Database Engine Availability Enhancements
  • Understanding Databases
  • Database Snapshots
  • Partitioned Tables and Indexes
  • Understanding and Managing Transaction Logs
  • Backing Up and Restoring Databases
  • Using a Dedicated Administrator Connection
  • Physical Database Architecture
  • BACKUP (Transact-SQL)
  • DBCC CHECKDB (Transact-SQL)
  • System Views (Transact-SQL)

Database and File States

In SQL Server 2000, the state (or status) of the database was at best hard to understand. You had to decode special bits in the sysdatabases table, and the behavior of when statuses changed was not consistent. SQL Server 2005 does a nice job of cleaning this up. First, a descriptive state of the database can be found in the sys.databases catalog view (in a column called state). Second, the Resource Database meaning and behavior of database states are easy to understand and consistent. For example, when you force a database offline with ALTER DATABASE SET OFFLINE, the database goes to the OFFLINE state. This was not always the case in SQL 2000.

To understand what these database states mean and how a database can move into each state, look at Figure 2-1.

This does not represent every possible scenario for state change for a database, but it covers the most common scenarios. A database is typically started during server startup, when it is first created, or when it is attached. When this occurs, the state of the database is temporarily changed to RECOVERING. If there is a resource problem with the database (such as a failure to open the database files), the state is changed to RECOVERY_PENDING. This state is persisted, so after the resource problem has been corrected, the user must use ALTER DATABASE SET ONLINE to start it again.

figure 2-1 Status of the database

If no resource issues exist, the engine tries to run recovery on the database. If recovery is successful, the database state changes to ONLINE and is persisted. If recovery fails, the database state is changed to SUSPECT but is not persisted. This is a change from previous versions when the SUSPECT state was persisted. This caused customers and PSS headaches, because if the server was restarted, the ERRORLOG did not contain the original reason for the recovery failure. Now SUSPECT is temporary, so if the server is restarted, recovery runs again at database startup. Therefore, the cause for a recovery failure can be investigated from the ERRORLOG.

When the database is ONLINE, you can take if offline (which closes the database and the database file handles along with it) by running ALTER DATABASE SET OFFLINE. This syntax changes the state to OFFLINE, which is persisted. At any persisted state, you can use ALTER DATABASE SET EMERGENCY to change the database state to EMERGENCY, which is then persisted. This is another nice change from SQL Server 2000, where changing to EMERGENCY state required an update to the sysdatabases table setting specific bits. This was error-prone, so putting this syntax into ALTER DATABASE made perfect sense. As the name implies, setting the database state to EMERGENCY makes sense only when you cannot access the database, such as when it is marked SUSPECT. We discuss later in the chapter why it sometimes makes sense to use EMERGENCY.

It is also important to mention the states that occur when you restore a database. When you restore a database and the database is created as part of loading it from the backup, the database state starts out as RESTORING. When all the database pages have been restored, the state is changed to RECOVERING as the database is recovered. Then the process is the same as normal database startup.

Database files also have states.

Resource Database

A major change for SQL Server 2005 is the resource database. In previous versions of SQL Server 2000, all executable system objects were stored in the master database. By executable, I mean any object not used to store data such as stored procedures, views, functions, and triggers. If you look at a typical SQL Server 2000 master database, you will find approximately 1,200 objects. With SQL Server 2005, there are now only about 70. So where are these objects now stored? In a database called the resource database. The actual name of this database is mssqlsystemresource, but there is a twist. You cannot directly access this database. (In other words, you won't see this in Management Studio, and you get an error if you try to execute 'use mssqlsystemresource') So how do you get a list of objects in this database and execute them?

SQL Server 2005 introduces a new ownership concept called schemas. One special reserved schema is called sys. By using the sys schema, you can reference any system object that is physically stored in the resource database in the context of your database. For example, we discuss in the next section a view called sys.objects. The view definition for objects is stored in the resource database, but you can reference it in a select statement in your database like this: 'select * from sys.objects'. So when you use the sys schema name for an object, the engine looks for the definition of the object in the resource database.

Why was this change made in SQL Server 2005? There are many reasons, but here is my perspective.

System executable objects are like code from Microsoft, so they are now stored in a protected, read-only database much in a manner similar to a dynamic link library (DLL). The files for this database are not actual DLL executable code. They are two files, mssqlsystemresource.mdf and mssqlsystemresource.ldf, installed by default in the same directory as the master database files.

These system objects are now in a self-contained location. It is now simple to identify all of them and simpler to replace them. Changes to these objects come in the form of a new database and log file. Copy in a new version of these files for upgrades to these objects. I address versioning and servicing of this database later when I talk about system database recovery.

Referencing a system object is now done in a consistent and clear approach. No more special rules such as "If the name starts with sp_, first check the master database." You just use the sys schema name to reference a system executable object.

Catalog Views and Base System Tables

Around the same time the resource database was created, the system tables were also redesigned. SQL Server has always been a design where the storage of metadata and the interfaces to view them are the same. SQL Server 7.0 did implement INFORMATION_SCHEMA views for ANSI compliance, but most users directly accessed system tables such as sysobjects and sysindexes. But the access is wide open for administrators with the right access. You can query, insert, delete, or update these tables directly. Great for enthusiasts and certain PSS engineers trying to solve some advanced problem, but a nightmare for those trying to ensure consistency of the database. I've seen the root cause of some fairly complex customer case be the result of a mistake made by a customer trying to touch an obscure system table column value.

The sheer scope and number of new features for SQL Server 2005 would require major modifications to a 10-year-old system table architecture. An entirely new system table design was needed to support new features (such as partitions). At the same time, the design of these tables and the interfaces used to access and modify data within them needed to be decoupled. The challenge was to prevent users from "shooting themselves in the foot" while providing a comprehensive metadata interface so that users would have access to everything they need. The result was catalog views. The catalog of the database is all the metadata storage within any database. Approximately 200 views were created and stored in the resource database for users to query the database catalog. Furthermore, support for some operations that formerly required direct system table modification was added to the Transact-SQL language. For example, to put the database in emergency mode, you now use ALTER DATABASE instead of directly changing a system table.

This last point is important, because it highlights a restriction that was put in place as part of this change for catalog views and system tables. Users cannot directly read or change system tables. Your first reaction may be anger or frustration. Why is Microsoft taking away something from me? The intention is not to take anything away from you. The intention is to help you keep a consistent database, provide you with the best interface to view the catalog, and to help avoid backward-compatibility problems should system tables change in future releases. In fact, to help with backward compatibility with SQL Server 2000 users, the "old" system tables such as sysobjects exist in SQL Server 2005 as catalog views.

To help put this together, let's take a look at a few queries using catalog views to get metadata information about the database.

If I want to find out all objects physically stored in my database that I have access to, I run the following query:

select * from sys.objects

If you were a system administrator and you ran this query in the old pubs sample database (installed on SQL Server 2005), you would get the following results:

click here to see code.

Notice all the rows with this type of SYSTEM_TABLE. But earlier I said you cannot read system tables. This is true, but you can see a list of all of them. These system tables are the base system tables stored in your database. Earlier beta releases did not expose these table names in this view, but the decision was made to show them primarily because the object ID values were showing up in error messages and users were confused when they tried to figure out what table was associated with the message.

If you tried to query one of these tables like this:

select * from sysschobjs

you would get the following error:

Msg 208, Level 16, State 1, Line 1 Invalid object name 'sysschobjs'.

What about the executable system objects stored in the resource database? If I cannot actually access the resource database directly, how do I see a list of these object? Well, SQL Server includes a catalog view just for this purpose:

select * from sys.system_objects name object_id principal_id schema_id parent_object_id type type_desc create_date modify_date is_ms_shipped is_published is_schema_published -------------------------------------------------------------------------------------------------------------------------------- ----------- ------------ ----------- ---------------- ---- ------------------------------------------------------------ ----------------------- ----------------------- ------------- ------------ ------------------- fn_cColvEntries_80 -61545096 NULL 4 0 FN SQL_SCALAR_FUNCTION 2006-02-17 14:43:52.820 2006-02-17 14:43:52.820 1 0 0 fn_fIsColTracked -242919846 NULL 4 0 FN SQL_SCALAR_FUNCTION 2006-02-17 14:43:53.040 2006-02-17 14:43:53.040 1 0 0 fn_GetCurrentPrincipal -986367695 NULL 4 0 FN SQL_SCALAR_FUNCTION 2006-02-17 14:43:46.130 2006-02-17 14:43:46.130 1 0 0 fn_GetRowsetIdFromRowDump -633331936 NULL 4 0 FN SQL_SCALAR_FUNCTION 2006-02-17 14:41:07.853 2006-02-17 14:41:07.853 1 0 0 fn_IsBitSetInBitmask -92987834 NULL 4 0 FN SQL_SCALAR_FUNCTION 2006-02-17 14:44:11.087 2006-02-17 14:44:11.087 1 0 0 fn_isrolemember -977642094 NULL 4 0 FN SQL_SCALAR_FUNCTION 2006-02-17 14:44:15.147 2006-02-17 14:44:15.147 1 0 0 . . sp_help -784136858 NULL 4 0 P SQL_STORED_PROCEDURE 2006-02-17 14:41:29.803 2006-02-17 14:41:29.803 1 0 0 sp_help_agent_default -930237674 NULL 4 0 P SQL_STORED_PROCEDURE 2006-02-17 14:46:15.130 2006-02-17 14:46:15.130 1 0 0 sp_help_agent_parameter -244913556 NULL 4 0 P SQL_STORED_PROCEDURE 2006-02-17 14:46:16.443 2006-02-17 14:46:16.443 1 0 0 sp_help_agent_profile -463476349 NULL 4 0 P SQL_STORED_PROCEDURE 2006-02-17 14:46:14.913 2006-02-17 14:46:14.913 1 0 0 sp_help_datatype_mapping -721021307 NULL 4 0 P SQL_STORED_PROCEDURE 2006-02-17 14:46:22.350 2006-02-17 14:46:22.350 1 0 0 . . . indexes -397 NULL 4 0 V VIEW 2006-02-17 14:38:43.433 2006-02-17 14:38:43.433 1 0 0 internal_tables -468 NULL 4 0 V VIEW 2006-02-17 14:38:42.340 2006-02-17 14:38:42.340 1 0 0 KEY_COLUMN_USAGE -784887024 NULL 3 0 V VIEW 2006-02-17 14:43:25.880 2006-02-17 14:43:25.880 1 0 0 key_constraints -406 NULL 4 0 V VIEW 2006-02-17 14:38:46.057 2006-02-17 14:38:46.057 1 0 0 key_encryptions -465 NULL 4 0 V VIEW 2006-02-17 14:39:26.200 2006-02-17 14:39:26.200 1 0 0 linked_logins -222 NULL 4 0 V VIEW 2006-02-17 14:39:17.120 2006-02-17 14:39:17.120 1 0 0 login_token -77438453 NULL 4 0 V VIEW 2006-02-17 14:39:49.777 2006-02-17 14:39:49.777 1 0 0 master_files -216 NULL 4 0 V VIEW 2006-02-17 14:39:11.870 2006-02-17 14:39:11.870 1 0 0 master_key_passwords -243 NULL 4 0 V VIEW 2006-02-17 14:39:14.167 2006-02-17 14:39:14.167 1 0 0 message_type_xml_schema_collection_usages -478 NULL 4 0 V VIEW 2006-02-17 14:38:59.730 2006-02-17 14:38:59.730 1 0 0 messages -225 NULL 4 0 V VIEW 2006-02-17 14:38:57.980 2006-02-17 14:38:57.980 1 0 0 module_assembly_usages -484 NULL 4 0 V VIEW 2006-02-17 14:38:51.307 2006-02-17 14:38:51.307 1 0 0 numbered_procedure_parameters -419 NULL 4 0 V VIEW 2006-02-17 14:38:52.510 2006-02-17 14:38:52.510 1 0 0 numbered_procedures -418 NULL 4 0 V VIEW 2006-02-17 14:38:52.183 2006-02-17 14:38:52.183 1 0 0 objects -385 NULL 4 0 V VIEW 2006-02-17 14:38:36.870 2006-02-17 14:38:36.870 1 0 0 . . . sysindexes -134 NULL 4 0 V VIEW 2006-02-17 14:39:33.090 2006-02-17 14:39:33.090 1 0 0 sysindexkeys -135 NULL 4 0 V VIEW 2006-02-17 14:39:33.417 2006-02-17 14:39:33.417 1 0 0 syslanguages -194 NULL 4 0 V VIEW 2006-02-17 14:39:26.853 2006-02-17 14:39:26.853 1 0 0 syslockinfo -204 NULL 4 0 V VIEW 2006-02-17 14:41:00.633 2006-02-17 14:41:00.633 1 0 0 syslogins -205 NULL 4 0 V VIEW 2006-02-17 14:39:37.463 2006-02-17 14:39:37.463 1 0 0 sysmembers -141 NULL 4 0 V VIEW 2006-02-17 14:39:34.400 2006-02-17 14:39:34.400 1 0 0 sysmessages -206 NULL 4 0 V VIEW 2006-02-17 14:39:38.777 2006-02-17 14:39:38.777 1 0 0 sysobjects -105 NULL 4 0 V VIEW 2006-02-17 14:38:39.823 2006-02-17 14:38:39.823 1 0 0 . . . xp_adsirequest -60872162 NULL 4 0 X EXTENDED_STORED_PROCEDURE 2006-02-17 14:53:06.237 2006-02-17 14:53:06.237 1 0 0 xp_availablemedia -196500590 NULL 4 0 X EXTENDED_STORED_PROCEDURE 2006-02-17 14:53:06.033 2006-02-17 14:53:06.033 1 0 0 xp_cleanupwebtask -113576977 NULL 4 0 X EXTENDED_STORED_PROCEDURE 2006-02-17 14:53:20.300 2006-02-17 14:53:20.300 1 0 0 xp_cmdshell -1008137134 NULL 4 0 X EXTENDED_STORED_PROCEDURE 2006-02-17 14:41:25.383 2006-02-17 14:41:25.383 1 0 0

This is not a complete list, because more than 1,700 system objects are accessible via the sys schema. You will see in this list some familiar names that used to be stored in the master database in SQL Server 2000, such as sp_help. You will also see new names such as views called indexes, master_files, and objects. Notice that names like sysindexes and sysobjects have a type VIEW. Also, you can see at the end of this list that Microsoft internal extended stored procedures are stored in the resource database.

Notice something interesting about the object_id of these names. They are listed as values < 0. Whenever you see an object_id listed as < 0, you know it is a system object that comes from the resource database.

I reference many of the catalog views in the remaining sections of this chapter. SQL Server Books Online has a complete reference of all the views, including the descripton of the column definitions.

One specific catalog view that I should mention is sys.system_sql_modules. In the early versions of the beta, there was no method to see the text of any system object. Based on user input during the beta, the SQL Server development team chose to expose the definitions of these objects with sys.system_sqAllocation Structures select * from sys.system_sql_modules where object_id = object_id('sys.sysobjects') shows you the definition of the sysobjects catalog view. If you run this query, you will see it references the sys.sysschobjs base system table. Be careful relying on the results of this view. The SQL Server development team exposed this information so that you can see how views, procedures, and so on were built. But you cannot change the text, and the development team certainly could change these over time as they fix bugs, enhance performance, or change the design of base system tables. To help give you a quick start on catalog views, Table 2-1 shows a comparison of what catalog view to use based on the SQL 2000 equivalent system table.

TABLE 2-1 What catalog view to use based on the SQL 2000 equivalent system table

SQL Server 2000 System Table SQL Server 2005 Catalog View
sysobjects sys.objects
sysindexes sys.indexes+sys.partitions +sys.allocation_units
syscolumns sys.columns
syscomments sys.sql_modules
sysdatabases sys.databases
sysfiles sys.database_files
sysfiles sys.database_files
sysaltfiles sys.master_files

Let me make one final comment about base system tables, catalog views, and the resource database. Base system tables exist in every database and store all the metadata. All the catalog views and executable system objects (sometimes called just system objects in Books Online) are not stored in your database. The definition of these objects exists only in the resource database. The resource database is a database, so it has base system tables. Think of it this way. When you create a view in your database, SQL Server stores the definition of this view in base system tables in your database. The resource database is simply preloaded with a bunch of views, procedures, and so forth. No user tables exist in the resource database.

Allocation Structures

The internal structures used to organize allocation for SQL Server 2005 have not changed dramatically. Concepts such as GAM, IAM, SGAM, and PFS all still exist to internally track and organize allocation of pages and extents.

There are some differences at a higher level of allocation. The first difference is support for partitions. SQL Server 2000 enables you to place tables or indexes on specific disks by using filegroups. SQL Server 2005 expands this capability to place horizontal slices of tables or indexes on specific disks using partitions. Partitions allow you to specify that a particular range of values within a table or index is stored on specific filegroups. Every table or index has at least one partition, as can be seen by querying the sys.partitions catalog view. Each partition can have up to three different allocation units. An allocation unit is equivalent to an IAM chain. In SQL Server 2000, a table could have two different IAM chains: one for the data and one for TEXT/IMAGE data. SQL Server 2005 supports three different IAM chains for an object: data, LOB (TEXT/IMAGE), and SLOB. A Small Large Binary Object (SLOB) is also referred to as row overflow data. SQL Server 2005 enables you to store data in a row that is larger than the SQL Server page size (8KB). This is done by supporting a different IAM chain to store this extended row data.

Figure 2-2 shows the allocation structure objects and corresponding catalog views.

Figure 2-2 Allocation structure objects and corresponding catalog views

Let's take a look at a few of the catalog views that describe allocation structures. Let's create a table and index using the following query:

create table test_table (col1 int primary key clustered, col2 int, col3 text, col4 varchar(5000) not null, col5 varchar(5000) not null) go create index test_idx on test_table (col2) go insert into test_table values (1, 1, 'this is text', 'this is test', 'this is test') go

To see the organization of allocation for this table and index, first look at the sys.partitions catalog view:

select * from sys.partitions where object_id = object_id('test_table') partition_id object_id index_id partition_number hobt_id rows -------------------- ----------- ----------- ---------------- -------------------- ------ 72057594038583296 2137058649 1 1 72057594038583296 1 72057594038648832 2137058649 2 1 72057594038648832 1

As you can see, the table and nonclustered index each have one partition. Notice the second column from the right, hobt_id. Hobt stands for heap or b-tree. Each partition is made up of exactly one heap or b-tree index. Notice the value of hobt_id is the same as partition_id. Why is the column needed? Very early designs of SQL Server 2005 included the ability for a heap or b-tree to store rows from multiple partitions. In other words, a partition could have its data spread across multiple allocation units of the same type. The final version of SQL Server 2005 doesn't contain this capability, but the base system tables still support this concept. (This is why you see in the list of tables the name syshobts.) In SQL Server 2005, a partition and hobt are basically equivalent, which is why the ID for both is the same. You can see a reflection of this design in the page header of a database page. The following is part of the output for DBCC PAGE for a page in a table I created in the master database:

PAGE HEADER: Page @0x0386A000 m_pageId = (1:509) m_headerVersion = 1 m_type = 1 m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0xe200 m_objId (AllocUnitId.idObj) = 87 m_indexId (AllocUnitId.idInd) = 256 Metadata: AllocUnitId = 72057594043629568 Metadata: PartitionId = 72057594038779904 Metadata: IndexId = 1 Metadata: ObjectId = 1291151645 m_prevPage = (0:0) m_nextPage = (0:0) pminlen = 12 m_slotCnt = 1 m_freeCnt = 8017 m_freeData = 173 m_reservedCnt = 0 m_lsn = (2006:16:1) m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0 m_tornBits = -1570474108

Instead of storing the object_id and index_id values as SQL Server did in previous versions, it stores the allocation_unit_id as seen in the sys.allocation_units catalog view. DBCC PAGE decodes the allocation_unit id value such that it includes the object_id and index_id from the system catalog metadata. You can see this from the areas I highlighted that start with the word metadata.

Let's now look at the allocation units for the table and index using the sys.allocation_units catalog view:

The table has a text column and possible row overflow ---- data so there should be 3 rows for the clustered index --- and 1 row for the ncl index. select object_name(pt.object_id), pt.index_id, au.* from sys.allocation_units au join sys.partitions pt on au.container_id = pt.partition_id and pt.object_id = object_id('test_table') order by container_id name index_id allocation_unit_id type type_desc container_id data_space_id total_pages used_pages data_pages -------------------------------------------------------------------------------------------------------------------------------- ----------- -------------------- ---- ------------------------------------------------------------ -------------------- ------------- -------------------- -------------------- -------------------- test_table 1 72057594042843136 1 IN_ROW_DATA 72057594038583296 1 2 2 1 test_table 1 72057594042908672 3 ROW_OVERFLOW_DATA 72057594038583296 1 0 0 0 test_table 1 72057594042974208 2 LOB_DATA 72057594038583296 1 2 2 0 test_table 2 72057594043039744 1 IN_ROW_DATA 72057594038648832 1 2 2 1

Notice that index_id = 1 (the clustered index for test_table) has three types: IN_ROW_ DATA (the actual data rows), ROW_OVERFLOW_DATA (this is the SLOB because we created the table with variable-length character columns that exceed 8KB), and LOB_DATA (the TEXT/IMAGE data). index_id = 2, the nonclustered index, has one allocation unit. Notice in the preceding query the column to join back to sys.partitions is container_id. Because of what I described with hobts, you could join to either partition_id or hobt_id. If you are a veteran SQL Server support engineer like me, you might see that something is missing from sys.allocation_units. What about first, root, and first_IAM? Initially, the SQL Server development team did not think anyone needed to see these columns. Feedback to Microsoft was that it would be valuable to still see these values. So, late in the beta cycle, the sys.system_internals_allocation_units catalog view was created. Let's run the preceding query again, this time changing it to use this view:

-- IAM information is only found in this internal catalog --- view. This view may change so could -- break your application if you rely on the result set select object_name(pt.object_id) as name, pt.index_id, sau.* from sys.system_internals_allocation_units sau join sys.partitions pt on sau.container_id = pt.partition_id and pt.object_id = object_id('test_table') order by container_id go name index_id allocation_unit_id type type_desc container_id filegroup_id total_pages used_pages data_pages first_page root_page first_iam_page -------------------------------------------------------------------------------------------------------------------------------- ----------- -------------------- ---- ------------------------------------------------------------ -------------------- ------------ -------------------- -------------------- -------------------- -------------- -------------- -------------- test_table 1 72057594042843136 1 IN_ROW_DATA 72057594038583296 1 2 2 1 0x9A0000000100 0x9A0000000100 0x9B0000000100 test_table 1 72057594042908672 3 ROW_OVERFLOW_DATA 72057594038583296 1 0 0 0 0x000000000000 0x000000000000 0x000000000000 test_table 1 72057594042974208 2 LOB_DATA 72057594038583296 1 2 2 0 0x980000000100 0x980000000100 0x990000000100 test_table 2 72057594043039744 1 IN_ROW_DATA 72057594038648832 1 2 2 1 0x9C0000000100 0x9C0000000100 0x9D0000000100

Now the same page numbers in hex as you could find from sysindexes in SQL Server 2000 are available. Be careful with the usage of this view. As mentioned in the documentation, it might change over time, depending on changes to the architecture for allocation.

Database Checksum

One of the challenges in PSS for customer cases involving database corruption is to pinpoint whether the problem is specifically caused by a hardware or system problem. SQL Server 2005 adds checksum capabilities for database pages and log blocks. This is an important feature to determine how a database page might have become damaged. Here is how it works.

If you enable a database for checksum (it is on by default, or you enable it with ALTER DATABASE), right before the engine writes a page to disk, it calculates a checksum value based on the bits on the page and writes this value into the page header. When the page is read back from disk, SQL Server calculates the checksum value based on bits on the page (excluding the checksum itself in the header) and compares that to the checksum value in the page header. If they do not match, SQL Server knows the page must have been altered after it submitted it to be written to disk. The server doesn't know who altered it, but the path of IO from the operating system to the disk is subject to scrutiny.

Fast Recovery

A number of customer cases come into PSS in which the customer is struggling because the database is not accessible because of the length of database recovery on startup. Database recovery has three basic phases: analysis, redo, and undo. The final phase, undo, typically takes the longest and is the source of pain. SQL Server 2005 takes a step forward in database availability by allowing access to the database during the undo phase of recovery. How is this accomplished? The engine during recovery acquires locks during the redo phase and holds these locks for any uncommitted transaction. When the undo phase begins, recovery is in effect a "user" who is running transactions to undo operations that need to be rolled back. Because locks are acquired to perform this operation, transaction consistency can be maintained. Users can now access the database and run standard transactions. The caveat is that users may get blocked by a system session that is running recovery (because it is holding locks while processing undo). Users who need to access data not related to any transactions being rolled back by recovery will not be affected. The restrictions on this feature are as follows:

  • It is supported only for Enterprise Edition.
  • It works only for databases set up for full recovery.
  • It works only for crash recovery. (In other words, it does not work when restoring a database.)

Deferred Transactions

Another important enhancement to database recovery is the concept of deferred transactions. Consider this scenario. Your system has an unexpected crash and reboots. SQL Server restarts and now has to run recovery on all databases. Because of a problem with your disk system, one of the database pages needed by recovery is damaged. Because the database is enabled for checksum, recovery detects a checksum error. In SQL Server 2000, a damaged page detected during recovery would result in an instant SUSPECT database. The entire database would be inaccessible, and the only method to recover reliably would be to restore the entire database from backup. SQL Server addresses this with a clever solution. In situations where damage to a single page is detected during recovery, the page is marked with a special bit called RestorePending so that it cannot be accessed. Furthermore, the transaction associated with the page is "deferred" if it is an active transaction that must be rolled back. What this means is that locks associated with the transaction are held after redo and are not released after undo. In fact, undo for the transaction is skipped.

So at the time recovery is complete, the database is still online, the page that is damaged is not accessible, and any locks associated with an associated uncommitted transaction are held. But the database status remains ONLINE and not SUSPECT. So, users can access the database but will encounter an error when accessing the page. Also, if the transaction associated with the page is deferred, a user could be blocked. (The locks are held by session_id = -3.) Resolution to the problem could then be to just restore the damaged page. More on the details behind this when I talk about recovering an inaccessible database.

Deferred transactions are supported for both crash and restore recovery, but this does not work for recovery when a database is attached.

Read-Only Compressed Databases

A frequent question I see about databases is whether you can use NTFS compression for the database and log files. The largest concern is consistency, because the server cannot guarantee sector-aligned writes. However, a read-only database has no consistency concern. Therefore, the SQL Server development team decided to allow a read-only database to be stored on NTFS compressed files. SQL Server 2000 did not prevent you from creating a read-write database on an NTFS compressed volume, even though this is not a supported configuration. However, in SQL Server 2005, if you shut down SQL Server, compress the files of a read-write database, and then try to restart SQL Server, you see the following errors in the ERRORLOG:

2006-03-18 22:49:14.87 spid15s Starting up database 'pubs'. 2006-03-18 22:49:15.01 spid15s Error: 5118, Severity: 16, State: 1. 2006-03-18 22:49:15.01 spid15s The file "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATApubs.mdf" is compressed but does not reside in a read-only database or filegroup. The file must be decompressed.

As the message implies, it is also possible to mark a filegroup as read-only and compress the files just for that filegroup even though the rest of the database is read-write.

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
Book and author box:
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
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.

Dig Deeper on Database software management

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.