|
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 |
---|---|
| 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_helpfilename 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 onlyUSE MSDB; EXEC sp_helpfilename 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. |
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 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 model; EXEC sp_helpfile USE tempdb; EXEC sp_helpfilename 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
If you need to move tempdb (to address performance issues due to disk contention), you can:USE tempdb; SELECT * FROM sysobjects WHERE name LIKE '#%';
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