SQL Server: Database types

Two types of databases:

• (a) System databases: Used by SQL Server and SQL Server services.
• Four system databases: master, MSDB, model and tempdb
  contain configuration information about the SQL Server services and user databases.
• (b) User databases: Used to store user data.

System databases

Master database	MSDB database
Primary configuration database. Usually small. Data in: master.mdf Logs in: masterlog.ldf Used by SQL Server service. Systems table in the master database: keep information about users and databases within the instance. Needs to be available (not corrupted) for database startup.	Used by the SQL Server Agent service. Data here include: scheduled jobs, job parameters, alerts, operators. Data in: MSDBData.mdf Logs in: MSDBlog.ldf

USE MASTER;
EXEC sp_helpfile
name    fileid  filename                                               filegroup size    maxsize   growth usage
------  ------  -----------------------------------------------------  --------- ------- --------- ------ --------- 
master  1       C:\Program Files\...\MSSQL10...\MSSQL\DATA\master.mdf  PRIMARY   4096 KB Unlimited 10%    data only
mastlog 2       C:\Program Files\...\MSSQL10...\MSSQL\DATA\mastlog.ldf NULL      1024 KB Unlimited 10%    log only

USE MSDB;
EXEC sp_helpfile
name     fileid  filename                                                filegroup size     maxsize      growth  usage
------   ------  ------------------------------------------------------  --------- -------  ---------    ------- --------- 
MSDBData 1       C:\Program Files\...\MSSQL10...\MSSQL\DATA\MSDBData.mdf PRIMARY   15104 KB Unlimited   10%    data only
MSDBLog  2       C:\Program Files\...\MSSQL10...\MSSQL\DATA\MSDBLog.ldf NULL      5184 KB  2147483648 KB 10%    log only

Model database

Tempdb database

Model for new databases. Properties set in the model database will be automatically configured on new databases.

Used by SQL Server database engine tempdb is recreated every time SQL Server service starts. Therefore, any data in tempdb is LOST when the database shutdown. If you need an object to always exist in tempdb, it HAS to be recreated after instance startup. For this you can use a startup stored procedure, which are automatically executed when the SQL Server service starts up. Users may also create temporary objects in the tempd: i.e. USE tempdb; SELECT * INTO #tmp_orders   FROM InsideTSQL2008.Sales.Orders   WHERE custid = 10; -- copy all data from sales table into a temporary table #temp_table. The # sign directs the server to create a LOCAL temporary table in the tempdbdatabase. Local temporary tables: visible only in the current session. The ## sign directs the server to create a GLOBAL temporary table. Global temporary tables: visible to all sessions.

USE model;
EXEC sp_helpfile

USE tempdb;
EXEC sp_helpfile

name       fileid  filename                                                filegroup size     maxsize    growth  usage
--------   ------  ------------------------------------------------------  --------- -------  ---------  ------- --------- 
modeldev   1  C:\Program Files\...\MSSQL10...\MSSQL\DATA\model.mdf      PRIMARY   1280 KB  Unlimited  1024 KB data only
modellog   2  C:\Program Files\...\MSSQL10...\MSSQL\DATA\modellog.ldf  NULL      512  KB  Unlimited  10%     log only

name       fileid  filename                                                filegroup size     maxsize    growth  usage
--------   ------  ------------------------------------------------------  --------- -------  ---------  ------- --------- 
tempdev    1  C:\Program Files\...\MSSQL10...\MSSQL\DATA\tempdb.mdf     PRIMARY   8192 KB  Unlimited  10%     data only
templog    2  C:\Program Files\...\MSSQL10...\MSSQL\DATA\templog.ldf   NULL      512  KB  Unlimited  10%     log only

The full name of a temporary table as stored in the sysobjects table in tempdb. The full name is: table name specified in the CREATE TABLE statement + a system-generated numeric suffix.

USE tempdb;
  SELECT * FROM sysobjects
  WHERE name LIKE '#%';

If you need to move tempdb (to address performance issues due to disk contention), you can:

USE MASTER;
GO

ALTER DATABASE tempdb modify FILE
  (name = tempdev,
   filename = 'F:\tempdata\tempdb.mdf');
GO
ALTER DATABASE tempdb modify FILE
  (name = templog,
   filename = 'F:\tempdata\templog.ldf');
GO