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.
