Hello friends, I hope you enjoyed my earlier blogs. I am quite new to this blogging fraternity but trying my level best to share my knowledge that I accumulated in my mind during my tenure in IT industry.
So without wasting the time let me share few interesting things about system databases..
Before I move ahead and write this blog, let me ask one question..If you are a database developer(specially SQL database developer) how many of you have really care about system databases and really tried to know about system databases? I am pretty sure that only few of you would have really thought about system databases..
Let me ask 2 questions about system databases and you will find my article followed by these questions.
1)What are 4 default databases those gets installed while installing SQL Server?
2)What are the roles of these system databases in SQL Server operations?
Four system databases those gets installed while installing SQL Server.
1)Master database
2)Model database
3)Tempdb database
4)Msdb database
Role of Master database :- Master database is the heart of SQL server that contains all the server level configurations in it. It also contains the information about all the user databases as well as system databases.
All of the extended stored procedures(Xp_Cmdshell) resides in the master database. Apart from these objects it also contains login information, linked server information.
Model database:- Model database is a standard template of database that is used by SQL Server while creating a user database.When a user database is created SQL Server just copy the schema of model database and paste into the user database.All of the objects SP's, Functions, Triggers and all the objects of model database are copied into the user database.If we want an object to be a part of all the user database we just create the object into model database.Once we have the intended object in the model database, all the future databases will have the intended object in it.
Tempdb database:- Tempdb also plays an important role in SQL Server operations. One interesting fact about tempdb is that whenever SQL server is rebooted, it re-creates the tempdb. All the temporary objects resides in tempdb.SQL Server uses tempdb very frequently, whenever a sort operation is made or any other operations like distinct, group by or any calculation that requires intermediary data at that point of time, SQL Severs uses tempdb.
MSDB database:- Msdb contains all the information about jobs,SQL Server agent, database mail, service broker and schedule tasks. All the database maintenance activities are controlled by Msdb database.Apart from these activities, Msdb also contains data of DTS packages(for 2000 or earlier versions) and SSIS packages(for 2005 or later versions).
I hope this article will help you to increase your knowledge about system databases for those people who all were not aware of system databases and help to refresh the knowledge of those people who already know about system databases.
So without wasting the time let me share few interesting things about system databases..
Before I move ahead and write this blog, let me ask one question..If you are a database developer(specially SQL database developer) how many of you have really care about system databases and really tried to know about system databases? I am pretty sure that only few of you would have really thought about system databases..
Let me ask 2 questions about system databases and you will find my article followed by these questions.
1)What are 4 default databases those gets installed while installing SQL Server?
2)What are the roles of these system databases in SQL Server operations?
Four system databases those gets installed while installing SQL Server.
1)Master database
2)Model database
3)Tempdb database
4)Msdb database
Role of Master database :- Master database is the heart of SQL server that contains all the server level configurations in it. It also contains the information about all the user databases as well as system databases.
All of the extended stored procedures(Xp_Cmdshell) resides in the master database. Apart from these objects it also contains login information, linked server information.
Model database:- Model database is a standard template of database that is used by SQL Server while creating a user database.When a user database is created SQL Server just copy the schema of model database and paste into the user database.All of the objects SP's, Functions, Triggers and all the objects of model database are copied into the user database.If we want an object to be a part of all the user database we just create the object into model database.Once we have the intended object in the model database, all the future databases will have the intended object in it.
Tempdb database:- Tempdb also plays an important role in SQL Server operations. One interesting fact about tempdb is that whenever SQL server is rebooted, it re-creates the tempdb. All the temporary objects resides in tempdb.SQL Server uses tempdb very frequently, whenever a sort operation is made or any other operations like distinct, group by or any calculation that requires intermediary data at that point of time, SQL Severs uses tempdb.
MSDB database:- Msdb contains all the information about jobs,SQL Server agent, database mail, service broker and schedule tasks. All the database maintenance activities are controlled by Msdb database.Apart from these activities, Msdb also contains data of DTS packages(for 2000 or earlier versions) and SSIS packages(for 2005 or later versions).
I hope this article will help you to increase your knowledge about system databases for those people who all were not aware of system databases and help to refresh the knowledge of those people who already know about system databases.
2 comments:
Apart from the 4 systems databases you have mentioned, there is another system database exists, which is read only. It is known as Resource Database.
Since resource database is not visible to us in management studio, that is the reason,I did not explain the role of resource database.Moreover, I wrote this article in context of SQL Server 2000 and in that version resource database was not there. Please correct me if i am wrong.
Post a Comment