System Databases in SQL SERVER

System Databases OF SQL SERVER.

Working with SQL server, Everyone might have observed couple of databases  listed under Databases> System Databases in SSMS.

System Databases are needed for the working of SQL Server efficiently. Many times developers or rookies are advised not to alter or mess with System Databases.

The importance and use of system databases will help us understand the internal workings of SQL server.

Master: Records all the system level Information for SQL Server Instances,

 Msdb: Information related to SQL Server Agent working (Jobs, Schedules, Job histroy).

 Model: This Database will be used as a Model when we create a new database.

Scenario: If you ever need all your database’s created with pre defined settings for Primary and Log file. You can configure them here, all the new databases created thereafter will get the settings from model. Need for a customized function, stored procedures to be available for all databases can be created here in model to get that replicated across all newly created databases. 

Tempdb: It’s a workspace for holding temporary objects or intermediate result sets. Many times you see long running queries will fill the tempb and clear once they are done. You can observe that when SQL queries which uses temp tables . The temp tables created with # and ## (representing temp and Global temp tables) can be found under tables section of TempDB.

Sometimes we also observe Reportserver & Reportservertempdb  when Reporting services is configured and distribution database when distribution of Replication is enabled on the server under system databases.