Feeds:
Posts
Comments

Archive for the ‘Administration’ Category

http://www.mssqltips.com/sql-server-tip-category/9/performance-tuning/

http://www.sql-server-performance.com/2007/gen-tips/

http://www.codeproject.com/Articles/22853/SQL-Server-Performance-Tips-and-Guidelines

http://social.technet.microsoft.com/wiki/contents/articles/5957.sql-server-performance-survival-guide.aspx

Read Full Post »

1.Apply a service pack or hotfix SQL Server 2005 to a failover cluster instance

With SQL Server 2005, Microsoft tried to be helpful in enabling its installer to apply the service pack or hotfix automatically on the passive node(s) in a cluster. In my own experience, this has not worked out too well.

Very often, when the installer flagged the upgrade status of the service pack ot the hotfix as failure next to Database Service, the information I found in the log file was that attempt to patch the passive node had failed. You could scour the log files, google the internet, and/or open a Microsoft support case to troubleshoot the problem, rectify the root cause for failure to patch the passive node, and move on. But that may be a lengthy process, although sometimes you do not have a choice but to go through it.

In many cases, I have found the following simple solution to work well (the description assumes that we have node A and node B, and node A currently owns the SQL Server resource group):

  • Use Cluster Administrator to pause the passive node, i.e. node B
  • Re-apply the service pack or the hotfix on node A (i.e. the active node–the node that owns the SQL Server resource group). Because node B is paused, the installer will not try to help you out in applying the service pack or hotfix there. And re-applying the service pack or hotfix will usually succeed.
  • Un-pause node B.
  • If you need to reboot the patched node A, go ahead and reboot it. After node A has come back online, make sure that the SQL Server resource group is on node B. If not, move it there.
  • Pause node A with Cluster Administrator.
  • Apply the service pack or hotfix on node B. Again, this should typically succeed (unless there is some other issue).
  • Un-pause node A, and reboot node B, if necessary.

In essence, you may want to patch all the nodes manually yourself.

I’m not suggesting that this is a cure all for all the problems of patching a clustered SQL Server 2005. But it has worked quite well for me when the problem was a failed attempt to patch the passive node.

 

2.Apply a service pack or hotfix SQL Server 2008 to a failover cluster instance

Installing Service Pack SQL Server 2008 in failover cluster is very different than the SQL Server 2005 cluster failover.

With SQL Server 2005, when you start installing cluster service pack (or hotfix), it must be launched on the active node (node that hosts the instance). When installing the Setup will launch simultaneously  “remote silence” on all passive nodes. All nodes in the cluster containing the SQL Server instance are updated in the same time.

With SQL Server 2008, to reduce the downtime, we have revised the method of deployment. Now if you want to apply a service pack (or hotfix), you must install in first on the passive nodes. The passive nodes are updated before the active node.

Therefore, for your instance SQL Server 2008  in failover cluster, you must follow the scenario below for the application of Service Pack, Cumulative Update or Hotfix :

1.  Apply the hotfix on pasive node N2
2.  Reboot the passive node N2
3.  Failover on SQL resource : the passive node become the active node
4.  Apply the hotfix on the passive node N1
5.  Reboot the passive node N1

You can check the version of SQL server with the following query:

SELECT

SERVERPROPERTY(‘IsClustered’) as _1_Means_Clustered ,
SERVERPROPERTY(‘Edition’) as Edition ,
SERVERPROPERTY(‘ProductVersion’) as Version  ,
SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS’) as ActiveNode

 

Links:-

http://sqlblog.com/blogs/linchi_shea/archive/2009/10/04/pause-the-passive-node-to-apply-sql2005-service-pack-or-hotfix.aspx

http://blogs.technet.com/b/mdegre/archive/2010/01/16/apply-a-sql-server-2008-service-pack-or-hotfix-to-a-failover-cluster-instance.aspx

Read Full Post »

—Table Size Information Periodically in SQL Server 2005

USE [master]

GO

CREATE DATABASE [DBA_Activities]

GO

—===========================================

USE [DBA_Activities]

GO

CREATE TABLE [dbo].[DATABASE_TABLE_SIZE_LOG](
[Database_Name] [sysname] NOT NULL DEFAULT (”),
[Table_Name] [sysname] NOT NULL,
[Row_Count] [int] NULL,
[Reserved] [varchar](50) NULL,
[Data] [varchar](50) NULL,
[Index_Size] [varchar](50) NULL,
[Unused] [varchar](50) NULL,
[Get_Date] [datetime] NULL
) ON [PRIMARY]

–====================================================

USE [msdb]

GO

/****** Object:  Job [DATABASE_TABLE_SIZE_LOG]    Script Date: 01/21/2012 13:04:28 ******/

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

/****** Object:  JobCategory [Database Maintenance]    Script Date: 01/21/2012 13:04:28 ******/

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N’Database Maintenance’ AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N’JOB’, @type=N’LOCAL’, @name=N’Database Maintenance’

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N’DATABASE_TABLE_SIZE_LOG’,

@enabled=1,

@notify_level_eventlog=0,

@notify_level_email=0,

@notify_level_netsend=0,

@notify_level_page=0,

@delete_level=0,

@description=N’No description available.’,

@category_name=N’Database Maintenance’,

@owner_login_name=N’sa’, @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object:  Step [Step1]    Script Date: 01/21/2012 13:04:28 ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’Step1′,

@step_id=1,

@cmdexec_success_code=0,

@on_success_action=1,

@on_success_step_id=0,

@on_fail_action=2,

@on_fail_step_id=0,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0, @subsystem=N’TSQL’,

@command=N’DECLARE @EachDBCmd VARCHAR(8000)

SET @EachDBCmd = ”USE [?];

IF ””?”” NOT IN (””tempdb””,””master””, ””model””, ””msdb””)

BEGIN

DECLARE @EachTableCmd VARCHAR(8000)

SET @EachTableCmd = ””EXEC sp_spaceused ”””””” + CHAR(63) + ””””””””

INSERT INTO DBA_Activities.dbo.DATABASE_TABLE_SIZE_LOG(Table_Name,Row_count,Reserved,data,index_size,unused)

EXEC sp_MSforeachtable @EachTableCmd

UPDATE DBA_Activities.dbo.DATABASE_TABLE_SIZE_LOG SET Database_Name = ””?””,Get_Date=(SELECT CONVERT(CHAR(10), GETDATE(), 101)) WHERE Database_Name = ””””

END

EXEC sp_MSforeachdb @EachDBCmd’,

@database_name=N’DBA_Activities’,

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N’Sche1′,

@enabled=1,

@freq_type=8,

@freq_interval=17,

@freq_subday_type=1,

@freq_subday_interval=0,

@freq_relative_interval=0,

@freq_recurrence_factor=1,

@active_start_date=20111003,

@active_end_date=99991231,

@active_start_time=220000,

@active_end_time=235959

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)’

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

—==========================================================

Select * From dbo.DATABASE_TABLE_SIZE_LOG

Read Full Post »

        Reasons to Backup Master Database – Why Should Master Database Backedup (Pinal Dave) :- http://blog.sqlauthority.com/2009/02/15/sql-server-reasons-to-backup-master-database-why-should-master-database-backedup/

1.Backup the Master Database.

2.For Testing purpose intentionally delete any login from SQL Server.

4. Login was Deleted from SQL Server.

Now we will restore the Master Database From Backup file

1.Open SQL Server Configuration Manager

2.Stop the SQL Server Service.

3. Add -m; Parameter in SQL Server Startup Parameters for start the SQL Server with Single User Mode.

4. Start the SQL Server Service.

5.Open command prompt for restore a master database.

6.Now Open SQL Server Using SSMS Check the Deleted Login Id restored.

Read Full Post »

--===========================Monitor DB Size Growth Report In SQL Server 2005=======================

USE [master]
GO

CREATE DATABASE [DBA_Activities]
GO

--====================================================================================================

USE [DBA_Activities]
GO

CREATE TABLE [dbo].[Get_DB_Details_DBA](
	[Server_Name] [varchar](100) NULL,
	[Database_Name] [varchar](100) NULL,
	[Logical_File_Name] [sysname] NOT NULL,
	[Physical_File_Name] [nvarchar](520) NULL,
	[File_Size_MB] [int] NULL,
	[Status] [sysname] NOT NULL,
	[Recovery_Mode] [sysname] NOT NULL,
	[Free_Space_MB] [int] NULL,
	[Free_Space_Per] [varchar](7) NULL,
	[Get_Date] [datetime] NULL
) ON [PRIMARY]

--====================================================================================================

USE [DBA_Activities]
GO

CREATE  PROC   [dbo].[Get_Database_Size_Growth]
(
     @dtFromDate DateTime=Null,
     @dtToDate DateTime=Null,
     @Option Varchar(10)='DAY_WISE' --DAY_WISE,MONTH_WISE,YEAR_WISE

)
AS

BEGIN

Declare @SQL Varchar(Max)

Declare @intFromMonth int
Declare @intToMonth int

Declare @intFromYear int
Declare @intToYear int

Declare @intI int
Declare @intDiff int

Declare @dtDate datetime

If @dtFromDate Is Null And @dtToDate Is Null
   Begin
      Set @dtFromDate = (Select Min(Get_Date) from Get_DB_Details_DBA)
      Set @dtToDate= (Select Max(Get_Date) from Get_DB_Details_DBA)
   End

If @dtFromDate Is Null
   Begin
      Set @dtFromDate = (Select Min(Get_Date) from Get_DB_Details_DBA)
   End

If @dtToDate Is Null
   Begin
      Set @dtToDate= (Select Max(Get_Date) from Get_DB_Details_DBA)
   End

If @dtFromDate > @dtToDate
   Begin
      RAISERROR ('From date should be less than To date.',10,1)
      Return
   End

If @intFromMonth < 1 Or @intFromMonth > 12
   Begin
      RAISERROR ('From Date Month between 1 to 12.',10,1)
      Return
   End

If @intToMonth < 1 Or @intToMonth > 12
   Begin
      RAISERROR ('To Date Month between 1 to 12.',10,1)
      Return
   End

If @intFromYear < 1753 Or @intFromYear > 9999
   Begin
      RAISERROR ('From Date Year between 1 to 12.',10,1)
      Return
   End

If @intToYear < 1753 Or @intToYear > 9999
   Begin
      RAISERROR ('From Date Year between 1 to 12.',10,1)
      Return
   End

Set @SQL = 'Select Database_Name'

If @Option = 'DAY_WISE'

   Begin

     Set @dtDate = @dtFromDate

     While (@dtDate <= @dtToDate)

       Begin
         Set @SQL = @SQL + ',(Sum(Case When Get_Date=' + char(39) + CONVERT(VARCHAR(10), @dtDate, 110) + char(39) + ' Then File_Size_MB Else Null End)/1024.0) As ' + char(39) + REPLACE(CONVERT(VARCHAR(11), @dtDate, 106), ' ', '-') + char(39)
         Set @dtDate = @dtDate + 1
       End

   End

If @Option = 'MONTH_WISE'

   Begin

      Set @intFromMonth = Month(@dtFromDate)
      Set @intToMonth = Month(@dtToDate)

      Set @intFromYear = Year(@dtFromDate)
      Set @intToYear = Year(@dtToDate)

      --Set @dtFromDate = (CONVERT(DATETIME, (CONVERT(Varchar(4),@intFromYear) + '-' + CONVERT(Varchar(2),@intFromMonth) +  '-' + '01') ,20))
      --Set @dtToDate = (CONVERT(DATETIME, (CONVERT(Varchar(4),@intToYear) + '-' + CONVERT(Varchar(2),@intToMonth) +  '-' + '01') ,20))

      Set @intI = 0
      Set @intDiff = (DateDiff(mm,@dtFromDate,@dtToDate))

      Set @dtDate = @dtFromDate

      While (@intI <= @intDiff)

         Begin
           Set @dtDate= (DateAdd(mm,@intI,@dtFromDate))
           Set @SQL = @SQL + ',(Sum(Case When Get_Date=' + char(39) + CONVERT(VARCHAR(10), @dtDate, 110) + char(39) + ' Then File_Size_MB Else Null End)/1024.0) As ' + char(39) + REPLACE(CONVERT(VARCHAR(11), @dtDate, 106), ' ', '-') + char(39)
           Set @intI=@intI+1
         End

   End

If @Option = 'YEAR_WISE'

   Begin

      Set @intFromYear = Year(@dtFromDate)
      Set @intToYear = Year(@dtToDate)

      --Set @dtFromDate = (CONVERT(DATETIME, (CONVERT(Varchar(4),@intFromYear) + '-' + 'Jan' +  '-' + '01') ,20))
      --Set @dtToDate = (CONVERT(DATETIME, (CONVERT(Varchar(4),@intToYear) + '-' + 'Jan' +  '-' + '01') ,20))

      Set @intI = 0
      Set @intDiff = (DateDiff(yy,@dtFromDate,@dtToDate))

      Set @dtDate = @dtFromDate

      While (@intI <= @intDiff)

         Begin
           Set @dtDate= (DateAdd(yy,@intI,@dtFromDate))
           Set @SQL = @SQL + ',(Sum(Case When Get_Date=' + char(39) + CONVERT(VARCHAR(10), @dtDate, 110) + char(39) + ' Then File_Size_MB Else Null End)/1024.0) As ' + char(39) + REPLACE(CONVERT(VARCHAR(11), @dtDate, 106), ' ', '-') + char(39)
           Set @intI=@intI+1
         End

   End

Set @SQL = @SQL + 'From Get_DB_Details_DBA Group By Database_Name Order by Database_Name'
EXECUTE(@SQL)

END

GO

--====================================================================================================

USE [msdb]
GO

/****** Object:  Job [Get_DB_Details_DBA]    Script Date: 10/04/2011 14:40:30 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [Database Maintenance]    Script Date: 10/04/2011 14:40:30 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Get_DB_Details_DBA',
		@enabled=1,
		@notify_level_eventlog=0,
		@notify_level_email=0,
		@notify_level_netsend=0,
		@notify_level_page=0,
		@delete_level=0,
		@description=N'No description available.',
		@category_name=N'Database Maintenance',
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Step1]    Script Date: 10/04/2011 14:40:30 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Step1',
		@step_id=1,
		@cmdexec_success_code=0,
		@on_success_action=1,
		@on_success_step_id=0,
		@on_fail_action=2,
		@on_fail_step_id=0,
		@retry_attempts=0,
		@retry_interval=0,
		@os_run_priority=0, @subsystem=N'TSQL',
		@command=N'DECLARE @DBSizeDetails TABLE
(Server_Name VARCHAR(100),
Database_Name VARCHAR(100),
Logical_File_Name sysname,
Physical_File_Name NVARCHAR(520),
File_Size_MB INT,
Status sysname,
Recovery_Mode sysname,
Free_Space_MB INT,
Free_Space_Per VARCHAR(7),
Get_Date datetime)

DECLARE @command VARCHAR(5000)
SELECT @command = ''Use ['' + ''?'' + ''] SELECT
@@servername As Server_Name,
'' + '''''''' + ''?'' + ''''''''+ '' As Database_Name,
sysfiles.name As Logical_File_Name,
sysfiles.filename AS Physical_File_Name,
CAST(sysfiles.size/128.0 AS int) As File_Size_MB,
CONVERT(sysname,DatabasePropertyEx(''''?'''',''''Status'''')) AS Status,
CONVERT(sysname,DatabasePropertyEx(''''?'''',''''Recovery'''')) AS Recovery_Mode,
CAST(sysfiles.size/128.0 - CAST(FILEPROPERTY(sysfiles.name, '' + '''''''' +
''SpaceUsed'' + '''''''' + '' ) AS int)/128.0 AS int) AS Free_Space_MB,
CAST(100 * (CAST (((sysfiles.size/128.0 - CAST(FILEPROPERTY(sysfiles.name,
'' + '''''''' + ''SpaceUsed'' + '''''''' + '' ) AS int)/128.0)/(sysfiles.size/128.0))
AS decimal(4,2))) AS varchar(8)) + '' + '''''''' + ''%'' + '''''''' + '' AS Free_Space_Per,
GETDATE() as Get_Date FROM dbo.sysfiles''
INSERT INTO @DBSizeDetails
(Server_Name, Database_Name, Logical_File_Name, Physical_File_Name, File_Size_MB, Status,
Recovery_Mode, Free_Space_MB, Free_Space_Per, Get_Date)
EXEC sp_MSForEachDB @command

INSERT INTO Get_DB_Details_DBA (Server_Name, Database_Name, Logical_File_Name, Physical_File_Name, File_Size_MB, Status,Recovery_Mode, Free_Space_MB, Free_Space_Per,Get_Date)
SELECT
Server_Name, Database_Name, Logical_File_Name, Physical_File_Name, File_Size_MB, Status,
Recovery_Mode, Free_Space_MB, Free_Space_Per,CONVERT(CHAR(10), Get_Date, 101) FROM @DBSizeDetails Where Database_Name Not In (''master'',''msdb'',''tempdb'',''model'')
',
		@database_name=N'DBA_Activities',
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Sche1',
		@enabled=1,
		@freq_type=4,
		@freq_interval=1,
		@freq_subday_type=1,
		@freq_subday_interval=0,
		@freq_relative_interval=0,
		@freq_recurrence_factor=0,
		@active_start_date=20101029,
		@active_end_date=99991231,
		@active_start_time=200000,
		@active_end_time=235959,
		@schedule_uid=N'3b2022cd-96b6-42df-8cab-d3be37d711b4'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

--====================================================================================================

USE [DBA_Activities]
GO

Exec Get_Database_Size_Growth
Exec Get_Database_Size_Growth '05-May-2011'
Exec Get_Database_Size_Growth '05-May-2011','05-Jun-2011'
Exec Get_Database_Size_Growth '05-May-2011','05-Oct-2011','MONTH_WISE'
Exec Get_Database_Size_Growth NULL,NULL,'MONTH_WISE'
Exec Get_Database_Size_Growth '05-May-2011','05-May-2012','YEAR_WISE'

--====================================================================================================

Read Full Post »

TORN_PAGE_DETECTION 

Saves a specific bit for each 512-byte sector in the 8-kilobyte (KB) database page and stored in the database page header when the page is written to disk. When the page is read from disk, the torn bits stored in the page header are compared to the actual page sector information. Unmatched values indicate that only part of the page was written to disk. In this situation, error message 824 (indicating a torn page error) is reported to both the SQL Server error log and the Windows event log. Torn pages are typically detected by database recovery if it is truly an incomplete write of a page. However, other I/O path failures can cause a torn page at any time.

CHECKSUM

Calculates a checksum over the contents of the whole page and stores the value in the page header when a page is written to disk. When the page is read from disk, the checksum is recomputed and compared to the checksum value stored in the page header. If the values do not match, error message 824 (indicating a checksum failure) is reported to both the SQL Server error log and the Windows event log. A checksum failure indicates an I/O path problem. To determine the root cause requires investigation of the hardware, firmware drivers, BIOS, filter drivers (such as virus software), and other I/O path components.

Read Full Post »

          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.

Read Full Post »

Older Posts »