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

No comments:

Post a Comment