Home » SQL Server

Category Archives: SQL Server

Window Server 2008 Failover Clustering Logs


In Windows 2003 failover clustering, Cluster nodes continuously putting details in log file on each node. This log is in readable plain text format & located in %SystemRoot%\Cluster folder on each node in the cluster and the name of the file is CLUSTER.LOG.

In Windows 2008, One of the major change is cluster logs logging. Microsoft change the logging mechanism & now cluster logs logging also work as per rest of Windows handles event logging. The legacy cluster.log file no more exists. In Windows 2008 the cluster log is handled by the Windows Event Tracing (ETW) process. This is the same as the System or Application Event logs you view in Event Viewer.

è Points :-

1. Cluster logs located in %windir%\Cluster\Reports directory on each node of the cluster

2. The log files are stored in an *.etl format.

3. Naming Format in Windows Server 2008

ClusterLog.etl.001
ClusterLog.etl.002
ClusterLog.etl.003

4. Naming Format in Windows Server 2008 R2

Microsoft-Windows-FailoverClustering Diagnostic.etl.001
Microsoft-Windows-FailoverClustering Diagnostic.etl.002
Microsoft-Windows-FailoverClustering Diagnostic.etl.003

5. The default size of these logs is 100MB each. You can change it as per your requirements.

6. We can have maximum 3 *.etl files.

7. The ETL.001 file is the active file being used by the live cluster service to write debug entries.

8. File rollover happens only on reboot. On reboot, new ETL.001 will be created & last file will be deleted.

9. In case, Latest ETL.001 file reach to MAX size, Data inside the file will start truncating on basis of FIFO (First In First Out). But file rollover happen only on reboot.

èHow to check cluster properties ?

Open an elevated command prompt and type the following:

C:\>cluster /prop

èHow to change the MAX size of cluster logs ?

Powershell

C:\PS>Set-ClusterLog –Size X

Command Line

c:\>cluster log /Size:X

èHow to Generate a Cluster Log in readable format ?

Command Line

Switch Effect
c:\>cluster log /gen /COPY:”directory Dumps the logs on all nodes in the entire cluster to a single directory
c:\>cluster log /gen /SPAN:min Just dump the last X minutes of the log
c:\>cluster log /gen /NODE:”node-name Useful when the ClusSvc is down to dump a specific node’s logs

Powershell

Switch Effect
C:\PS> Get-ClusterLog –Destination Dumps the logs on all nodes in the entire cluster to a single directory
C:\PS> Get-ClusterLog –TimeSpan Just dump the last X minutes of the log
C:\PS> Get-ClusterLog –Node Useful when the ClusSvc is down to dump a specific node’s logs

èHow to Change the level of the Cluster Log

Default cluster log level (ClusterLogLevel) is 3. Which is same as windows 2003 logging. You can have max level 5 to have more detailed information for analysis. Using higher level of level may impact system performance as well.

Level Error Warning Info Verbose Debug
0 (Disabled)
1 X
2 X X
3
(Default)
X X X
4 X X X X
5 X X X X X

Powershell

C:\PS>Set-ClusterLog –Level X

Command Line

c:\>cluster log /Level:X

Reference : Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN :-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx

How to restore Master database – SQL Server System Database?


Steps to restore SQL Server Master Database :-

1) Stop SQL Server Services

2) Add [-m] startup parameter in SQL Services from configuration manager

Parameter [-m] Starts SQL Server instance in single-user mode. SQL Server in single-user mode can connect with single user only and did not start CHECKPOINT process. Starting SQL Server in single-user mode enables any member of the computer’s local Administrators group to connect to the instance of SQL Server as a member of the sysadmin fixed server role.

From SQL Server 2005 to 2008 R2

From SQL Server 2012

3) Start SQL Services & SQL server will come online in single user mode. Connect using SQLCMD

4) Execute Restore command

RESTORE DATABASE MASTER FROM DISK = ‘C:MASTER.BAK';

Message :

Processed 352 pages for database ‘MASTER’, file ‘master’ on file 1.

Processed 2 pages for database ‘MASTER’, file ‘mastlog’ on file 1.

The master database has been successfully restored. Shutting down SQL Server.

SQL Server is terminating this process.

5) After Master DB restore, You SQL services will be shutdown. Start SQL Services in normal mode by removing [-m] parameter.

6) You are done !

Reference : Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN :-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx

How to move Resource DB in SQL Server ?


The Resource database is a read-only database that contains all the system objects. This DB included with SQL Server 2005 to increase security of system metadata. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database does not contain user data or user metadata. You cannot access Resource database directly, it’s a hidden database & accessible through system catalog only.

Note :

1) Resource & Master Database location must be same.

2) From SQL Server 2008 onwards Resource DB resides in BIN folder & cannot be moved.

Steps to Move Resource Database :-

1) Start in master-only recovery mode by running:

NET START MSSQLSERVER /f /T3608

You can also do it from configuration manager, Like we start SQL server in single user mode. (http://mssqlfun.com/2014/09/04/how-to-start-sql-server-in-single-user-mode/)

2) Start a command prompt and run SQLCMD.

3) Use the ALTER DATABASE statement with the MODIFY FILE option to specify the new location for the resource database data and log files.

ALTER DATABASE mssqlsystemresource

MODIFY FILE (NAME=data, FILENAME= ‘E:SQL2K5_1Mastermssqlsystemresource.mdf’);

GO

ALTER DATABASE mssqlsystemresource

MODIFY FILE (NAME=log, FILENAME= ‘E:SQL2K5_1Mastermssqlsystemresource.ldf’);

GO

4) Use the ALTER DATABASE statement to make the Resource database read-only.

Alter Database mssqlsystemresource set Read_only;

5) Stop the SQL Server service.

6) Move the database files for the mssqlsystemresource database to new location.

7) Start the SQL Server service.

Reference : Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN :-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx

How to Rebuild MSDB database?


I suggest to have regular full backup of your system database along with user database. But if you do not have system database & your MSDB got corrupted then you have to rebuild it. You can do it by instmsdb.sql script comes with installation. It will result in loss of SQL Agent jobs, backup & restore history, database mail configuration etc. Let’s have a demo for that :-

1. Stop all services connecting to the Database Engine, including SQL Server Agent, SSRS, SSIS, and all applications using SQL Server

2. Start SQL Server from the command prompt using the command: NET START MSSQLSERVER /T3608

3. Detach the msdb database using SQLCMD : SQLCMD -E -S<servername> -dmaster -Q"EXEC sp_detach_db msdb"

4. Using SQL Server Configuration Manager, stop and restart the Database Engine service normally.

5. Now you need to execute instmsdb.sql. You can locate it in <Installation Directory><Instance Name>MSSQLInstallinstmsdb.sql

You can execute it from SSMS as well as from SQLCMD.

For SQLCMD from command prompt use command :

SQLCMD -E -S<servername> -i"<Installation Directory><Instance Name>MSSQLInstallinstmsdb.sql" -o" <Installation Directory><Instance Name>MSSQLInstallinstmsdb.out"

6. Re-apply any service packs or hotfix installed on the instance.

7. Recreate the user content stored in the msdb database, such as jobs, alert, etc.

8. Backup the msdb database.

Reference : Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN :-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx

How to install standalone SQL Server 2012 instance ?


Steps to install standalone SQL Server 2012 instance

1) Start the setup > Go to Installation from Left > Click on 1st option of New SQL Server standalone edition from right

2) Click Ok to cont., In case of any failure, you need to fix that first

3) Enter Product Key & click Next

4) Accept EULA & Click Next

5) New Feature of SQL Server 2012 installation : Automatically check product update & include them in single installation if available

6) Click Next, In case of any failure, you need to fix that first

7) Select the role of SQL Server instance, you are going to install

8) Select Features, you want to install & Click Next

9) Click Next to continue

10) Specify SQL Server instance name & Click Next

11) Check Disk space requirement & Click Next

12) Specify Service accounts & Click Next

13) Add User to work as SYSADMIN & Click Next

14) Set error reporting options & click Next

15) Click Next (In case of any failure, you need to fix that)

16) Check all configurations & Click Next

17) Installation progress will appear to complete the installation. Installation Completed successfully

Reference : Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN :-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx

A Very Happy New 2015


Wishing you a very happy new year to you.

May this year bring success & happiness in your life.

Reference : Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN :-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx

2014 – One more Year with you


The WordPress.com stats helper monkeys prepared a 2014 annual report for this blog.

Here’s an excerpt:

The Louvre Museum has 8.5 million visitors per year. This blog was viewed about 73,000 times in 2014. If it were an exhibit at the Louvre Museum, it would take about 3 days for that many people to see it.

Click here to see the complete report.

Follow

Get every new post delivered to your Inbox.

Join 562 other followers