SQL (Master & MSDB Database)

The Server system catalog and all the environmental information stored in Master Database. The master database is the brain of the server. Database administrator has to take special care of Master Database whenever they change any values of master database.Administrator should have the habit of backing up the master database whenever make any changes. The following items should trigger a backup of Master Database.

1) Disk Statements
2) Altering a Transaction Log
3) Adding & Removing Mirror Device
4) Adding & Removing remote servers
5) Adding & Dropping Login Id
6) Any change in server configuration

Tables such as SysLock,Sysdatabase,SysProcess and Sysusage are a part of Master Database. The server will allow user to change the values via Raw SQL of these tables and it is recommended also not to change values.

Other than this Master Database includes Logon Accounts, End Points, Linked Server and System Configuration Settings. Master database store information of all other database also.

Below is the Physical Properties of Master Database

File	Logical name	Physical name	File growth
Primary data	master	master.mdf	Autogrow by 10 percent until the disk is full.
Log	mastlog	mastlog.ldf	Autogrow by 10 percent to a maximum of 2 terabytes.

Below are some basic queries which Database Administrator uses normally

-- To get the Login Count
SELECT COUNT(*) FROM master..syslogins
GO

-- Login Details
SELECT name, createdate  FROM master..syslogins
GO

-- Database Size

SELECT d.name,ROUND(SUM(mf.size) * 8 / 1024, 0) Size_MBs FROM sys.master_files mf INNER JOIN sys.databases d ON d.database_id = mf.database_id WHERE d.database_id > 4 GROUP BY d.name ORDER BY d.name


***********************************************************************************

Msdb database is used by SQL Server Agent to execute Job, Executing Alerts. Sql Server automatically maintain a history of Backup and the devices where backup has been taken.All these events will be stored in Msdb database.Msdb stores multiple useful information so it is recommended that you consider placing the Msdb transaction log on fault tolerant storage.

Below is the Physical Properties of Msdb Database

File	Logical name	Physical name	File growth
Primary data	MSDBData	MSDBData.mdf	Autogrow by 256 KB until the disk is full.
Log	MSDBLog	MSDBLog.ldf	Autogrow by 256 KB to a maximum of 2 terabytes.

**************************************************************************************

For freshers below question is very common in an interview where interviewer is looking for basic of Master Database

Ques : Define Master Database
Ans : Database which store system level information such as user accounts,configuration settings.

Ques : Define Msdb Database
Ans : Database used by the SQL Server Agent for configuring alert and schedule Job

Ques : Can you move system databases?
Ans : Yes We can move the database and moving database will be useful in below circumstances
1) Failure Recovery
2) Planned Relocation
3) Relocation Schedule Disk Maintenance
All above scenario is applicable moving files within same instance. 

This has been a match of epic proportions getting 190 in 14.3 was an improbable ask. 

So there's a will there's a way 

That's all for Today, see you tomorrow with new learning and Mumbai have qualified.

Sanjay Bakshi
mesanjaybakshi@gmailcom

(Keep Calm & Keep Learning)