In this article, we'll explore system databases, their uses, benefits, and what to do and not do with system databases. So let's get started.

Microsoft SQL Server has the following 4 system databases:

Microsoft SQL Server uses these databases to perform its own internal operations. Every time when you start your computer (If your SQL Server start type is Automatic) or when you start/restart your SQL Server Services, SQL Server starts all the system databases with other databases. If you want to check this, you can run sp_readerrorlog 0,1,'starting'. This will show you the starting time of your databases.

Starting time
Figure 2: Starting Time

Just to show this, I'm restarting the SQL Server Service and when starting, it also starts all the databases as shown below.

Restarting Services
Figure 3: Restarting Services

Now you can clearly see that, first I started my computer at 08:37 AM, hence SQL Service started and with that all the databases were also started. Then at 10:40 AM I restarted SQL Services and again it starts all the databases when starting the service.

The following is the table that tells you the default size of these system databases (as per technet.microsoft.com )

Database file Physical file name Default size, typical setup
master primary data Master.mdf 11.0 MB
master log Mastlog.ldf 1.25 MB
tempdb primary data Tempdb.mdf 8.0 MB
tempdb log Templog.ldf 0.5 MB
model primary data Model.mdf 0.75 MB
model log Modellog.ldf 0.75 MB
msdb primary data Msdbdata.mdf 12.0 MB
msdb log Msdblog.ldf 2.25 MB

Now let's explore these databases one-by-one.

Master Databases

The following is what to do for the master database:

Don't do the following for the Master database:

Model Database

When you create a database with the "CREATE DATABASE" command, your database is created within a minute. But have you ever thought:

So the answer is all these features, functionalities, everything are defined under the model database. And this is what the main purpose of the model database is, to act as the template for all the user-defined databases. If I say this in programmatically terms, the model database acts as "Parent" for all other databases, it's a kind of "Inheritance" in SQL Server where all the user databases inherits the functionality of the model database.

Let's see this in an example.

We'll create a table in the model database, then we'll create a new database and let's check whether it inherits that table or not.

  1. USE model
  2. GO
  3. CREATE TABLE tblDemo
  4. (
  5. Id int,
  6. Name char(5)
  7. )
  8. GO
This'll create a new table named "tblDemo" in the model database.

tbldemo
Figure 4: tbldemo

Now let's create a new database.

  1. USE master
  2. GO
  3. CREATE DATABASE FunDatabase
  4. GO
Now, when you expand your table section in your database you'll see "tblDemo" already available in that.

Fun Database
Figure 5: Fun Database

So this is what the purpose of the model database is.

Whenever you create a new database its recovery type set to FULL by default, this is because your model database's recovery type is set to FULL. If you change the model's recovery type to simple, then the new databases follows that rule only.

The following is what to do for the model database:

MSDB Database

TEMPDB database

The following is what to do for a tempdb database:

With this I'm winding up this article on System databases.

In my next article we'll explain recovery models available in SQL Server, until then keep learning and sharing.

If there's any mistake in this article then please let me know. Please provide your valuable feedback and comments that enable me to provide a better article the next time.