SQL Server 2008 (and 2005) contain five System databases:
Following picture dipicts how server looks like with the sysytem databases:
Note: Size of master primary data & master log are 11 MB and 1.25MB respectively.
model:
Model is essentially a template database usedin the creation of any new user database created in the instance. User defined tables, stored procedures, user defined data types, etc can be created in the Model database and will exist in all future user defined databases .The database configurations such as the recovery model for the Model database are applied to future user defined databases. Model Database template is applied across all the databases in the server.
Note: Size of model primary data & model log are 0.75 MB each.
msdb:The msdb database storesinformation regarding database backups, SQL Agent information, DTS packages,SQL Server jobs, and some replication information such as for log shipping.
Here are some of the msdb system tables:
One important item is that msdb holds backup history. Using the msdb tables, it’s possible to determine when each database and filegroup was last backed up. backupset is the table which stores this information.
sp_delete_backuphistory will remove the backup history from msdb.
Note: Size of msdb primary data and msdb log are 12.0 MB and 2.25 MB respectively.
tempdb: This database stores temporary tables (#temptable or ##temptale), table variables, cursors, work tables, row versioning, create or rebuild indexes sorted in TempDB, etc. Each time the SQL Server instance is restarted all objects in this database are destroyed, so permanent objects cannot be created in this database. This database is recreated every time SQL Server starts, andthe objects contained in it will be based upon the objects defined in the modeldatabase. In SQL Server 2005, the tempdbdatabase has taken on an additional workload; it is used as the version storefor features such as the new snapshot isolation levels and online indexingoperations.A properly sized and configured tempdb is vital for effective SQL Server performance.
Note: Default size of tempdb primary data and tempdb log are 8.0 MB and 0.5MB respectively.
Resource: This is a Read-only database that is not accessible via the SQL Server 2005 tool set .The Resource database is responsible for physically storing all of the SQL Server 2005 system objects. This database has been created to improve the upgrade and rollback of SQL Server system objects with the ability to overwrite only this database. The database ID for the Resource database is 32767. The Resource database does not have an entry in master.sys.databases
Using the following query you can see what are all the databases present in SQL Server:
The best way to learn SQL Server is to explore how things work in the systemdatabases. As a general rule, it is not recommended to directly query thesystem tables in SQL Server; however, you can learn a lot about how SQL Serverworks by exploring the tables in these system databases.
- master
- model
- msdb
- tempdb
- mssqlsystemresource (Resource)
Following picture dipicts how server looks like with the sysytem databases:
Resource is a hidden, system database that contains all system objects included with SQL Server 2005/2008 Express.
master:
The master database contains all of the system level information for SQL Server – all of the logins, linked servers, endpoints, and other system-wide configuration settings. The master database is also where SQL Server stores information about the other databases on this instance and the location of their files. If the master database is not present, SQL Server cannot start. master databse contains the following tables.These tables hold the login, linked server , endpoints and other system information.Note: Size of master primary data & master log are 11 MB and 1.25MB respectively.
model:
Model is essentially a template database usedin the creation of any new user database created in the instance. User defined tables, stored procedures, user defined data types, etc can be created in the Model database and will exist in all future user defined databases .The database configurations such as the recovery model for the Model database are applied to future user defined databases. Model Database template is applied across all the databases in the server.
Note: Size of model primary data & model log are 0.75 MB each.
msdb:The msdb database storesinformation regarding database backups, SQL Agent information, DTS packages,SQL Server jobs, and some replication information such as for log shipping.
Here are some of the msdb system tables:
One important item is that msdb holds backup history. Using the msdb tables, it’s possible to determine when each database and filegroup was last backed up. backupset is the table which stores this information.
sp_delete_backuphistory will remove the backup history from msdb.
Note: Size of msdb primary data and msdb log are 12.0 MB and 2.25 MB respectively.
tempdb: This database stores temporary tables (#temptable or ##temptale), table variables, cursors, work tables, row versioning, create or rebuild indexes sorted in TempDB, etc. Each time the SQL Server instance is restarted all objects in this database are destroyed, so permanent objects cannot be created in this database. This database is recreated every time SQL Server starts, andthe objects contained in it will be based upon the objects defined in the modeldatabase. In SQL Server 2005, the tempdbdatabase has taken on an additional workload; it is used as the version storefor features such as the new snapshot isolation levels and online indexingoperations.A properly sized and configured tempdb is vital for effective SQL Server performance.
Note: Default size of tempdb primary data and tempdb log are 8.0 MB and 0.5MB respectively.
Resource: This is a Read-only database that is not accessible via the SQL Server 2005 tool set .The Resource database is responsible for physically storing all of the SQL Server 2005 system objects. This database has been created to improve the upgrade and rollback of SQL Server system objects with the ability to overwrite only this database. The database ID for the Resource database is 32767. The Resource database does not have an entry in master.sys.databases
Using the following query you can see what are all the databases present in SQL Server:
The best way to learn SQL Server is to explore how things work in the systemdatabases. As a general rule, it is not recommended to directly query thesystem tables in SQL Server; however, you can learn a lot about how SQL Serverworks by exploring the tables in these system databases.
