Feeds:
Posts
Comments

Archive for August, 2011

          This post was used to learn how to rebuild a system database when a SQL Server service was not started because of system database corruption. In SQL Server System databases Master database is Very Important for SQL Server Startup because all SQL Server Configuration and All User database informations was stored in these database. So DBA must Monitor the Master database with Extra care and take a backups regularly.

Here we intentionally corrupt the master database (.mdf)  data file using Hex Editor and rebuilding the system databases using setup.exe in the versions SQL Server 2005 and 2008.In Sql Server 2005 and 2008 have Same Methods were applied for rebuilding the system databases but syntax slightly difference between SQL Server 2005 and 2008.

Here I am using SQL Server 2008 Named Instance for this post.

Intentionally Corrupt the Master Database using Hex Editor.

1.Stop the SQL Server Service using SQL Server Configuration Manager.

2.Open Hex Editor (Download Freeware Hex Editor XVI32 in http://www.chmaas.handshake.de/delphi/freeware/xvi32/xvi32.htm )

 3.In Hex Editor File –> Open –>  Select the Master Database mdf File in the corresponding Location.

4.Put value to 0 in Hex Editor Cells for Corrupt the mdf file Informations and the save the mdf file in Hex Editor.

5.Now We Start the SQL Server Service in SQL Server Configuration Manager we will get a error message.

6.In Windows Event Viewer get Some Error message.

7.Open SQL Server Error Log File you can see a below failed message.

2011-08-20 17:50:48.60 Server      Microsoft SQL Server 2008 (RTM) – 10.0.1600.22 (Intel X86)

                Jul  9 2008 14:43:34

                Copyright (c) 1988-2008 Microsoft Corporation

                Standard Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)

 

2011-08-20 17:50:48.60 Server      (c) 2005 Microsoft Corporation.

2011-08-20 17:50:48.60 Server      All rights reserved.

2011-08-20 17:50:48.60 Server      Server process ID is 4288.

2011-08-20 17:50:48.60 Server      Authentication mode is MIXED.

2011-08-20 17:50:48.60 Server      Logging SQL Server messages in file ‘C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2K8\MSSQL\Log\ERRORLOG’.

2011-08-20 17:50:48.60 Server      This instance of SQL Server last reported using a process ID of 1900 at 8/20/2011 5:49:13 PM (local) 8/20/2011 12:19:13 PM (UTC). This is an informational message only; no user action is required.

2011-08-20 17:50:48.60 Server      Registry startup parameters:

                 -d C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2K8\MSSQL\DATA\master.mdf

                 -e C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2K8\MSSQL\Log\ERRORLOG

                 -l C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2K8\MSSQL\DATA\mastlog.ldf

2011-08-20 17:50:48.62 Server      SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.

2011-08-20 17:50:48.62 Server      Detected 2 CPUs. This is an informational message; no user action is required.

2011-08-20 17:50:48.74 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.

2011-08-20 17:50:48.79 Server      Node configuration: node 0: CPU mask: 0x00000003 Active CPU mask: 0x00000003. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.

2011-08-20 17:50:48.80 spid7s      Starting up database ‘master’.

2011-08-20 17:50:48.82 spid7s      Error: 5172, Severity: 16, State: 15.

2011-08-20 17:50:48.82 spid7s      The header for file ‘C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2K8\MSSQL\DATA\master.mdf’ is not a valid database file header. The PageAudit property is incorrect.

2011-08-20 17:50:48.83 spid7s      Error: 5173, Severity: 16, State: 1.

2011-08-20 17:50:48.83 spid7s      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.

2011-08-20 17:50:48.83 spid7s      Error: 5173, Severity: 16, State: 1.

2011-08-20 17:50:48.83 spid7s      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.

 Rebuild the Corrupted System Databases.

1.Copy the SQL Server Setup Files to Hard Disk.

2.Open the Command Prompt.

3.Run the appropriate Command for version in Command Line

–SQL 2005 DEFAULT INSTANCE

start /wait setup.exe /qn INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=sa@123

–SQL 2005 NAMED INSTANCE

start /wait setup.exe /qn INSTANCENAME=MSSQL$ INSTANCENAME  REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD= sa@123

–SQL 2008 DEFAULT INSTANCE

Setup.exe /QUIET /ACTION=REBUILDDATABASE /SQLSYSADMINACCOUNTS=”Domain\Username” /INSTANCENAME=MSSQLSERVER /SAPWD=sa@123

–SQL 2008 NAMED INSTANCE

setup.exe /QUIET /ACTION=REBUILDDATABASE /SQLSYSADMINACCOUNTS=”Domain\Username” /INSTANCENAME= INSTANCENAME  /SAPWD=sa@123

4.Now the All System databases are Rebuilded Successfully.

but  all saved informations (users,logins and scheduled jobs) were erased from system databases . So we have to restore the system databases from old backups files for the minimal data loss.

Advertisements

Read Full Post »

This post taken from  :- http://www.teratrax.com/articles/sql_server_blocking_deadlocks.html

SQL Server blocking

SQL Server blocking occurs when one connection (user process or application process) places a lock on a table (or a number of rows) and a second connection attempts to read or modify the data under the lock. Depending on the type of the lock, this can cause the second connection to wait until the first connection releases its lock. A blocked connection waits indefinitely for the blocking connection to release its lock.

The more blocking happens on the server the less concurrency the system achieves. A certain amount of blocking is unavoidable but too many blocks for longer periods of time can degrade the performance of SQL Server.

SQL Server deadlocks

The combination of two blocked connections where the first is blocking the second and the second is blocking the first is called a deadlock. Since deadlocks are not naturally resolved with time, SQL Server automatically kills one of the connection (Deadlock victim) once it detects a deadlock. This allows the other connection to continue with its transaction.

Although deadlocks can be caused by two short blocks (Fraction of a second), it is often the long blocks that increase the chances of a deadlock to happen.

Blocks escalating to deadlocks

The following diagram shows the sequence of events leading to a deadlock. Consider two applications (A1, A2) accessing two different table (T1, T2):

Event 1: A1 places a lock on T1 inside its transaction and continues to execute other statements

Event 2: A2 places a lock on T2 inside its transaction and continues to execute other statements

Event 3: A1 attempts to place a lock on T2 (Needs to access T2 before it can finish the transaction) but has to wait for A2 to release its lock

At this point, a block is created since A2 is blocking A1

Event 4: While A1 is waiting, A2 attempts to place a lock on T1 (Needs to access T1 before it can finish its own transaction)

A deadlock is created since two connections have blocked one another. SQL Server automatically resolves the deadlock by choosing one of the connections as a deadlock victim and killing it.

Read Full Post »