How to restore master database in Active Passive Cluster environment? or How to restore master database on a cluster instance?

Scenario- Today, One of my DBA friend faced this issue and as a friend he called me to take assistance.

In his production cluster environment. Unable to restart SQL Server Service manually because Master database was corrupted and client asked him to restore the DB without delay. Make everything online. 

He checked in Error log and found same issue.

Solution : 

1.            First you have to take SQL Server Service Offline from Cluster Admin

2.            Login to the active node of the cluster

3.            Go to the node running SQL Server and start it from the command prompt using below command and server name<INHYIZLP10722>:

 

C:\>sqlcmd -W -s , -S INHYIZLP10722

 

1.       Change the SQL Server Mode as Single User mode using below command-

 

1> sqlservr.exe -c -m

 

2.            Rebuild master database using below command( note – change/provide domain\username and password to run the SQL Server instance)

 

2> setup.exe /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADM

INACCOUNTS=Domain\Username /SAPWD=Passw$rdJai2212 /SQLCOLLATION=Latin_General_CI

_AS_KS

Snapshot of the Rebuild command prompt-

  OR  

Snapshot of the Restore command prompt-

  Restore master database using below command line

RESTORE DATABASE master FROM DISK = 'C:\BackupLocation\master.bck' WITH REPLACE;

3.            Once the master database is restored, the SQL Service will be shutdown automatically

4.            Now Start SQL Server Service from Cluster Admin.

5.            Now try to connect your User database from SSMS and ask end user to access the application/Database.

Please let me know if you know other method to perform same activity - Jainendra Verma

SQL Server - How to resolve TempDB get FULL issue.

Please find the complete details to troubleshooting and resolving If the TempDB database get full:-

 Description: The LOG FILE FOR DATABASE 'tempdb' IS FULL.
 Back up the TRANSACTION LOG FOR the DATABASE TO free
 up SOME LOG SPACE

 Reason for temp DB full.
 1. Heavy transaction activity
 2. Due to maintenance job (Index fragmentation etc)
 3. Due to inventory closing or any other such type activity 
 4. Mount Drive\ Volume does not have sufficient space to grow temp db log files.
 5. Auto growth is not enable in tempdb
 6. Bulk Operation

 How to check if TempDB database get FULL :-

  

--Log space usage

dbcc sqlperf(logspace)

 --Open Tran

dbcc opentran(tempdb)

-- VLF (Virtual Log File)

  use tempdb

  go

  dbcc loginfo()

 Status - 2 (active log)
 Status - 0 (Inactive Log)

•  To truncate or shrink the log file there should be continuous inactive log.

 Resolution –
1. Simple and effective solution is to re-start the SQL server but in production environment we do not have privilege to restart the SQL services.
 2. We can use below quires to shrink the log file -
  

    dbcc shrinkfile (templog, 0)

 3. Create new log file in some other volume and cab the existing one to stop the auto growth.
 4. Perform failover if it’s in cluster if shrinking will not resolve the issue and we have necessary approvals from business.

SQL Server -How to move the “system database” files in SQL Server

It’s Very Simple 10 steps to do this.

In this example System database files existing in C: Drive and I will move it in D: Drive.

Step1- Check the D drive is access. Right click on D drive and go to Properties, go to security tab and find the Administrator “group or username” is there on not.

Step2- Next Step to create a folder in D drive. And the folder name should be as SQL server “Instance ID” name, which is “MSSQL10.MSSQLSERVER”. We can get this name from existing SQL server path for example see following path:-   

C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA

We can say this folder is the Data container for all System Database files.

Now we have to check the folder permission of the folder and if anything is missing so we will give that permission to newly created folder “MSSQL10.MSSQLSERVER”.  

Step 3- Open the both old folder and newly create folder properties and click on security tab and match the old folder and newly create folder “group name” and “permission” and click on ADDbutton than search the “group name” and ADD it in new folder.

Step4- Now we will move the Model and MSDB and TEMPDB database.

USE master;

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = modeldev, FILENAME ='D:\MSSQL10.MSSQLSERVER\model.mdf');

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = modellog, FILENAME ='D:\MSSQL10.MSSQLSERVER\modellog.ldf');

GO

USE master;

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = msdbdev, FILENAME ='D:\MSSQL10.MSSQLSERVER\msdb.mdf');

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = msdblog, FILENAME ='D:\MSSQL10.MSSQLSERVER\msdblog.ldf');

GO

USE master;

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = tempdev, FILENAME ='D:\MSSQL10.MSSQLSERVER\tempdb.mdf');

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = templog, FILENAME ='D:\MSSQL10.MSSQLSERVER\templog.ldf');

GO

Step 5- Stop the SQL server instance and COPY the Model and MSDB database file form C: Drive and PASTE it into D Drive- D:\MSSQL10.MSSQLSERVER .  Now we can START the SQL server instance.

Note- No Need to move TEMPDB files. Once the instance will START it will create automatic new TEMPDB log files.

Step 6- Now we have to move Master database. Go to Configuration manager, Right click on SQL server Instance and Properties, Click on Advanced tab. Here we will get the “Startup Parameters” value.

“Startup Parameters” value:-

-dC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

In this value we need to change the Path of each log file. Our current log file path is- D:\MSSQL10.MSSQLSERVER

New “Startup Parameters” value:-

-dD:\ MSSQL10.MSSQLSERVER\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG;-lD:\ MSSQL10.MSSQLSERVER\mastlog.ldf

Step 7- Now we have to STOP the SQL server instance.  Copy the Master database log files and Paste it in D Drive D:\ MSSQL10.MSSQLSERVER.

Step 8- Verify all the file has been moved in New Location,

Step 9- Delete the ALL the System Database files from C drive.

Step 10- Now we must have to START the SQL server instance. 

How to give select permission to account to see the only system databases table and DMVs or How to Grant Server State Permissions to User to query DMVs?

First create an user with Db_datareader permission and grant VIEW SERVER STATE permission

-- Syntax

USE master;

GO

GRANT VIEW SERVER STATE TO UserName;

GO

-- Example

USE master;

GO

GRANT VIEW SERVER STATE TO 'Fareast/TestReaderUser'

OR

GRANT VIEW SERVER STATE TO TestReaderUser

GO

Please comment belo if you know any other step to do the same. - Jainendra Verma