TroubleshootingSQL

Category: Data

Posts about data platforms, SQL Server, databases, Azure SQL, analytics, and data engineering.

  • Finding out root cause for Cluster Failovers

    We do get quite a few issues regarding root cause analysis for Cluster Failovers. Failovers mostly happen due to the IsAlive check failing for the SQL Server resource after which the following two conditions arise:
    1. SQL Server service restarts on the same cluster node
    2. SQL Server resource fails over to a member cluster node

    So, for looking into the possible root causes of a cluster failover, a SQL version of the MPS Reports capture is required from the node on which SQL is currently active. From the data requested by the PSS Engineer, the following files would be of utmost importance:
    1. All the SQL Server ERRORLOGs
    2. Windows Event Logs (System/Application)
    3. Cluster Log

    Based on the SQL Server ERRORLOGs, we would check for any errors or tell-tale signs which would point us to why the IsAlive check failed for the SQL Server resource. After that, we would look into the cluster log and the windows event logs to find out co-relation among the events during the failover time on the server.

    Since, the cluster log rolls over and also the SQL Server ERRORLOGs can roll over very quickly if a job is in place to recycle if after a certain size, it is a very good idea to save the cluster log and the SQL Server ERRORLOG(s) right after the failover to prevent them from rolling over and overwriting valuable data from the problem time period.

    Sometimes, a post mortem analysis provides us a hypothesis of what happened but doesn’t paint the picture completely due to lack of data from the period the problem happened. Based on the nature of the problem, the PSS Engineer might ask to you to do the following for the the next problem occurrence along :
    1. Capture a light-weight PSSDIAG round the clock with file rollover so that we can track what sort of events were happening on the SQL instance right before the failover.
    2. Or a filtered dump of the SQL Process during the problem period if there is heavy blocking on the server or if the failover had occurred due to memory dump(s) on the server
    3. OR a round the clock Perfmon log if there were possible external memory pressure on the server.

    NOTE: Keep in mind that the cluster logs are always in GMT format. So you need to add/substract the time difference between your timezone and GMT when analyzing the cluster logs.

  • Allow Updates Option for SQL Server 2005

    SQL Server 2005 doesn’t have the allow updates option. So, if you execute:

    sp_configure ‘allow_updates’,1

    and then if you execute reconfigure, you would get the following error:

    Msg 5808, Level 16, State 1, Line 2
    Ad hoc update to system catalogs is not supported.

    After this all changes to the sp_configure settings followed by a reconfigure would yield this error. To rectify this, you will have to change the allow_updates option back to 0 and run reconfigure. As per SQL Server 2005 Books Online:

    This option is still present in the sp_configure stored procedure, although its functionality is unavailable in Microsoft SQL Server 2005 (the setting has no effect). In SQL Server 2005, direct updates to the system tables are not supported.

    Changing the allow updates option will cause the RECONFIGURE statement to fail. Changes to the allow updates option should be removed from all scripts.

    So, if in case you use allow_updates in any script in SQL Server 2005, please refrain from doing so. Updates to the system catalogs are not permitted in SQL Server 2005 and any attempt/changes made to the System Resource database would get you into an unsupported scenario.

  • Reorganize data and index pages

    There are a few ways that this can be achieved:

    1. Shrinking the database files (data file or transaction log file)

    2. DBCC DBREINDEX (Similar to dropping and re-creating the indexes)

    3. DBBC INDEXDEFRAG (De-fragmenting the indexed pages to remove logical scan fragmentation)

    Note: It needs to be kept in mind that DBREINDEX is a fully-logged operation and can cause the transaction log to grow by large amounts when the database size is considerably large. So there have been situations when the transaction log has grown out of proportion and filled up the entire disk. If the T-log does grow out of proportion and you do choose to truncate/shrink the file, then please do so after verifying the points below:

    1. Transactional replication is not configured for the database

    2. Log shipping is not configured for the database

    3. Database mirroring is not configured for the database (For SQL 2005 SP1 and above)

    4. Any other operations are not being performed which need transactional log consistency in terms of LSN chains

    Online re-indexing is a feature which is available in SQL Server 2005 Enterprise Edition only. Please note that rebuilding the indexes would cause the statistics associated with those indexes to also be updated. This however would not update the AUTO CREATED statistics.

    The following articles could be useful:

    SET OPTION considerations when running DBCC with indexes on computed columns

    http://support.microsoft.com/kb/301292/

    DBCC DBREINDEX

    http://msdn2.microsoft.com/en-us/library/ms181671.aspx

    The DBCC SHOWCONTIG output for SQL Server 2000 and 2005 will help you find out the current status of fragmentation for the database. A sample output would look like this:

    DBCC SHOWCONTIG scanning ‘Customers’ table…

    Table: ‘Customers’ (2073058421); index ID: 1, database ID: 6

    TABLE level scan performed.

    – Pages Scanned…………………………..: 3

    – Extents Scanned…………………………: 2

    – Extent Switches…………………………: 1

    – Avg. Pages per Extent……………………: 1.5

    – Scan Density [Best Count:Actual Count]…….: 50.00% [1:2]

    – Logical Scan Fragmentation ………………: 0.00%

    – Extent Scan Fragmentation ……………….: 50.00%

    – Avg. Bytes Free per Page…………………: 246.7

    – Avg. Page Density (full)…………………: 96.95%

    The logical scan fragmentation should ideally be below 20% but anything above 40% would mean an INDEXDEFRAG or DBREINDEX is required. The script which is from the same MSDN article can help you run an INDEXDEFRAG or DBREINDEX based on the logical scan fragmentation count.

    A sample script for the same can be found under the topic of "DBCC SHOWCONTIG" in SQL Server Books Online (2000 and 2005)

    In SQL Server 2005, the following features may be helpful to you while using the ALTER INDEX command:

    REBUILD [ WITH ( [ ,… n]) ]

    Specifies the index will be rebuilt using the same columns, index type, uniqueness attribute, and sort order. This clause is equivalent to DBCC DBREINDEX. REBUILD enables a disabled index. Rebuilding a clustered index does not rebuild associated nonclustered indexes unless the keyword ALL is specified. If index options are not specified, the existing index option values stored in sys.indexes are applied. For any index option whose value is not stored in sys.indexes, the default indicated in the argument definition of the option applies.

    The options ONLINE and IGNORE_DUP_KEY are not valid when you rebuild an XML index.

    If ALL is specified and the underlying table is a heap, the rebuild operation has no affect on the table. Any nonclustered indexes associated with the table are rebuilt.

    The rebuild operation can be minimally logged if the database recovery model is set to either bulk-logged or simple.

    WITH ( LOB_COMPACTION = { ON OFF } )

    Specifies that all pages that contain large object (LOB) data are compacted. The LOB data types are image, text, ntext, varchar(max), nvarchar(max), varbinary(max), and xml. Compacting this data can improve disk space use. The default is ON.

    ON

    All pages that contain large object data are compacted.Reorganizing a specified clustered index compacts all LOB columns that are contained in the clustered index. Reorganizing a nonclustered index compacts all LOB columns that are nonkey (included) columns in the index. For more information, see Creating Indexes with Included Columns. When ALL is specified, all indexes that are associated with the specified table or view are reorganized, and all LOB columns that are associated with the clustered index, underlying table, or nonclustered index with included columns are compacted. OFF Pages that contain large object data are not compacted.

    OFF

    has no affect on a heap.The LOB_COMPACTION clause is ignored if LOB columns are not present.

    REORGANIZE

    Specifies the index leaf level will be reorganized. This clause is equivalent to DBCC INDEXDEFRAG. ALTER INDEX REORGANIZE statement is always performed online. This means long-term blocking table locks are not held and queries or updates to the underlying table can continue during the ALTER INDEX REORGANIZE transaction. REORGANIZE cannot be specified for a disabled index or an index with ALLOW_PAGE_LOCKS set to OFF.

    Furthermore, the dynamic management view sys.dm_db_index_physical_stats would help you get statistical information pertaining to indexes on SQL Server 2005. The columns mentioned below in this DMV output could be helpful in analyzing the need for index reorganization.

    avg_fragmentation_in_percent: Logical fragmentation for indexes, or extent fragmentation for heaps in the IN_ROW_DATA allocation unit. The value is measured as a percentage and takes into account multiple files. For definitions of logical and extent fragmentation, see Remarks. 0 for LOB_DATA and ROW_OVERFLOW_DATA allocation units.NULL for heaps when mode = SAMPLED.

    fragment_count: Number of fragments in the leaf level of an IN_ROW_DATA allocation unit. For more information about fragments, see Remarks.NULL for nonleaf levels of an index, and LOB_DATA or ROW_OVERFLOW_DATA allocation units. NULL for heaps when mode = SAMPLED.

    avg_fragment_size_in_pages: Average number of pages in one fragment in the leaf level of an IN_ROW_DATA allocation unit. NULL for nonleaf levels of an index, and LOB_DATA or ROW_OVERFLOW_DATA allocation units. NULL for heaps when mode = SAMPLED.

    The following article could be helpful:Microsoft SQL Server 2000 Index Defragmentation Best Practices

    http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

  • Database Maintenance Plans

    Database Maintenance Plans for SQL Server 2000
    Database Maintenance Plans offer the following features in SQL Server 2000:
    1. Backing up databases
    2. Reorganization of indexes and data pages
    3. Statistics Update
    4. Shrinking the database
    5. Database integrity checks
    The above tasks of the maintenance plans would have associated with respective SQL Agent jobs and also these could be scheduled to suit your needs. Furthermore, these jobs could be created manually also without the help of maintenance plans.

    Database Maintenance Plans for SQL Server 2005
    In addition to the above mentioned features, SQL Server 2005 Enterprise Edition provides the option of rebuilding the indexes as an online operation. This makes it possible for the indexes to remain online while a rebuild index task is being performed on the database.
    ONLINE = { ON OFF }
    Specifies whether underlying tables and associated indexes are available for queries and data modification during the index operation. The default is OFF.
    Note: Online index operations are available only in SQL Server 2005 Enterprise Edition.
    ON
    Long-term table locks are not held for the duration of the index operation. During the main phase of the index operation, only an Intent Share (IS) lock is held on the source table. This enables queries or updates to the underlying table and indexes to continue. At the start of the operation, a Shared (S) lock is held on the source object for a very short amount of time. At the end of the operation, for a short time, an S (Shared) lock is acquired on the source if a nonclustered index is being created; or an SCH-M (Schema Modification) lock is acquired when a clustered index is created or dropped online and when a clustered or nonclustered index is being rebuilt. ONLINE cannot be set to ON when an index is being created on a local temporary table.
    OFF
    Table locks are applied for the duration of the index operation. An offline index operation that creates, rebuilds, or drops a clustered index, or rebuilds or drops a nonclustered index, acquires a Schema modification (Sch-M) lock on the table. This prevents all user access to the underlying table for the duration of the operation. An offline index operation that creates a nonclustered index acquires a Shared (S) lock on the table. This prevents updates to the underlying table but allows read operations, such as SELECT statements.

    The frequency of these tasks and which tasks are needed need to be performed need to be decided on the following factors:
    1. Importance of the database in question
    2. Size of the database
    3. Maintenance window frequency
    4. Load on the server
    5. Whether it is an OLTP or OLAP database or both
    6. The kind of transactions that take place on the database (Point 5 and 6 would decide how often index reorganization/index rebuild/statistics updates are required and whether a shrink database/file is required)

  • Database Integrity Checks

    Database integrity checks are recommended to ensure that the database consistency is intact and if there is a problem with consistency, then it is reported to the appropriate team(s) so that necessary action can be taken to rectify it. This can be done with the help of Database Maintenance Plans. In the event that a CHECKDB on a database fails, then it needs to be reported by Email(using xp_sendmail or SQL Agent Mail or Database Mail for SQL Server 2005) or events fired in the Operating System Event Logs (if these are monitored regularly) with the help of Operators which can be configured for SQL Server Agent. The xp_sendmail feature is not available for SQL 2005 64-bit versions.
    The frequency of these checks largely depends on the following factors:
    1. Importance of the database
    2. How often data changes in the database (If a database integrity check fails for a database where data is not modified, then it would be advisable to restore the last known good backup rather than trying to repair the consistency database)
    3. The size of the database
    4. In the event of consistency checks failing, it needs to be determined which is the most feasible option:
    a. Restore the last known good backups in accordance with the recovery model for that database to allow for a minimum amount of data loss
    b. Or try and repair the database and falling back on Option (a) only if this fails In case a repair option is suggested in the CHECKDB output, it is important to note that a REPAIR_ALLOW_DATA_LOSS be never performed on the database without understanding its full consequences and consulting Microsoft PSS. In the event, that this route needs to be taken, it is always recommended to fall back on the last known good backups if possible. The REPAIR_FAST and REPAIR_REBUILD repair options can be performed without having any data loss. Please note that these are time consuming operations and in the event of database inconsistency it is not possible for us to predict how long these tasks would run for. Also, the time taken for CHECKDB on a database cannot be predicted. An educated guess can be made to how long it would take by referring to the last durations of the CHECKDB operations on that particular database. For the above mentioned repair options, please refer the following article: http://msdn2.microsoft.com/en-us/library/aa258278(SQL.80).aspx

    REPAIR_ALLOW_DATA_LOSS
    Performs all repairs done by REPAIR_REBUILD and includes allocation and deallocation of rows and pages for correcting allocation errors, structural row or page errors, and deletion of corrupted text objects. These repairs can result in some data loss. The repair may be done under a user transaction to allow the user to roll back the changes made. If repairs are rolled back, the database will still contain errors and should be restored from a backup. If a repair for an error has been skipped due to the provided repair level, any repairs that depend on the repair are also skipped. After repairs are completed, back up the database. This option shouldn’t be used without the assistance of a SQL Support Engineer.
    REPAIR_FAST
    Performs minor, nontime-consuming repair actions such as repairing extra keys in nonclustered indexes. These repairs can be done quickly and without risk of data loss.
    REPAIR_REBUILD
    Performs all repairs done by REPAIR_FAST and includes time-consuming repairs such as rebuilding indexes. These repairs can be done without risk of data loss. In case of SQL Server 2005, you have the option of checking the suspect_pages in the MSDB database to find out the affected pages.

    Please refer the following articles for more detailed information:
    Suspect_pages table (SQL 2005)
    http://msdn2.microsoft.com/en-us/library/ms174425.aspx
    Understanding and managing the suspect_pages table
    http://msdn2.microsoft.com/en-us/library/ms191301.aspx
    Designing a Backup and Restore Strategy
    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_bkprst_63eh.asp

    In the event, that only a few pages have a problem, then for SQL Server 2005 a page level restore can be performed. Online page restore operation is a feature available in SQL Server 2005 Enterprise Edition but all other Editions of SQL Server 2005 support offline page level restores. The page level restores are done using the NORECOVERY option for that database. Then a backup of the current transaction log is taken and applied to the database with the RECOVERY option. This feature is applicable to databases in FULL or BULK-LOGGED recovery models.
    For performing page level restores, please refer the following article: http://msdn2.microsoft.com/en-us/library/ms175168.aspx
    It is highly important that a disaster recovery plan is in place to ensure the following:

    • · A plan to acquire hardware in the event of hardware failure
    • · A communication plan.
    • · A list of people to be contacted in the event of a disaster.
    • · Instructions for contacting the people involved in the response to the disaster.
    • · Information on who owns the administration of the plan.
    • · A checklist of required tasks for each recovery scenario. To help you review how disaster recovery progressed, initial each task as it is completed, and indicate the time of completion on the checklist.

    In conclusion, if the database is of a considerably large size, then an integrity check needs to be scheduled during a window when the load on the server is at a minimum. The definition of minimum here refers to a load which is lesser than the normal workload on the server. If the database sizes are quite small, then daily integrity checks on the database would be the order of the day.
    It is recommended that DBCC CHECKDB be run during hours when the load is light on the server. If DBCC CHECKDB is run during heavy peak usage time, expect a performance hit on the transaction throughput as well as DBCC CHECKDB completion time.

    Recommendations for Good DBCC Performance
    · Run CHECKDB when the system usage is low.
    · Be sure that you are not performing other disk I/O operations, such as disk backups.
    · Place tempdb on a separate disk system or a fast disk subsystem.
    · Allow enough room for tempdb to expand on the drive. Use DBCC with ESTIMATE ONLY to estimate how much space will be needed for tempdb.
    · Avoid running CPU-intensive queries or batch jobs.
    · Reduce active transactions while a DBCC command is running.
    · Use the NO_INFOMSGS option to reduce processing and tempdb usage significantly.