There are five main system databases that are created automatically when you install SQL server. They are master, model, tempdb, msdb, and resource. There is a sixth system database, distribution, that is only created when you configure replication on the server.
master - The master database contains all the information the system needs. I contains things login information, linked servers, and system settings. It also contains information on all the other databases on your server and the location of those databases. If the master database is deleted SQL Server cannot start.
model - The model database is a template for the user databases that you create. If you modify the model db it won't affect any existing databases but it will affect any newly created databases. For example, if you have a stored procedure that you like to have in all databases, you can create it in the model database and it will automatically be in any database that you create
tempdb - As I heard SQLSoldier say in a recent webinar. "tempdb is the junk drawer of SQL Server". Tempdb is a shared database used to store temporary tables. Everytime you restart SQL server tempdb is recreated. The proper or improper use of this database can have a huge impact on your overall server performance. Here is a link to the Slide deck and scripts SQLSoldier used in his presentation
msdb - This is the database that is used by SQL Server Agent to jobs and alerts. If the SQL Server Agent service is not running none of the jobs stored in the db will run. The msdb database also stores the history information for the jobs.
resource - This Database is where all the system objects physically live. They show up logically in every database but actually live here. These are all your system tables and system Catalog Views.
That's a quick rundown on the five main system databases. Tune in next time for Databases and Filegroups.
I hope to get a more regular schedule from here on out. I plan on posting fortnightly on Thursdays. Let's hope I can stick to that schedule.
Hasta luego,
RutherfordTX
