MSDB Database

MSDB database stores all sql server agent related information. It contains all information about Jobs, Alerts, backup details, replication tables, log shipping tables, database maintenance plans and database mail details.

·         The max size of ‘msdb’ log file is 2 TB. (SQL 2k5/2k8).

·         This DB cannot be kept in Emergency mode.

·         This DB cannot be removed directly from SSMS as we shouldn't not detach any of system DB’s directly.   We have to do it from cmd prompt incase needed.

Ref:

http://technet.microsoft.com/en-us/library/ms187112(SQL.100).aspx

To rebuild msdb:

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/06/06/619304.aspx

http://msdn.microsoft.com/en-us/library/ms345408.aspx

Master database:

It is a must run DB. It contains all server related information like server version, user/login details, configuration settings, linked server details, instance information and etc.

In SQL server 2000 all the system databases are stored in master database directly but in SQL Server 2k5/2k8 we have resource database where the actual tables are stored and the sys schema of the system tables are stored in master database. So in SQL Server 2 k 5/2 k 8 master databases will have schemas and stored procedures of the actual system objects.

The maximum size till master database log file can grow is 2 terabytes (SQL Server 2008) and for data file there are no such restriction, it grows till the disk is full. ‘sys.databases’ view can be used to get information of master database.

Complete list of Do’s and Don’ts:

http://msdn.microsoft.com/en-us/library/ms187837.aspx

Rebuilding:

For rebuilding system databases in SQL Server 2005 version we have to use ‘setup.exe’ and to perform this we need to insert the installation DVD.

For rebuilding system databases in SQL Server 2008 version we have to use ‘setup.exe’. This exe can be found in the directory where we installed sql server ‘100\Setup Bootstrap\Release’ location. To perform this no need to insert the installation DVD it automatically takes the databases from BINN/templates folder.

Rebuilding system databases in SQL Server 2008:

http://blogs.msdn.com/b/psssql/archive/2008/08/29/how-to-rebuild-system-databases-in-sql-server-2008.aspx

http://msdn.microsoft.com/en-us/library/ms175535.aspx

For rebuilding master database in SQL Server 2000:

While rebuild master database in this version we can use ‘rebuildm.exe’.   This opens up the ‘Rebuild Master’ window where we can set up the options required.

Reference:

http://dbarecovery.com/backup-and-recovery-exercises/sql-server-2000-disaster-recovery-exercises/sql-server-2000-restore-master-database.html

‘temp’ database growing huge

I am not going to discuss reasons why temp DB grows huge but will make a note how to reduce its size.

Basically, a temp database holds all the temporary data. It is used to perform sort operations, used by cursors to store temporary data, it contains all temporary tables.

As part of an immediate resolution, we generally prefer to create secondary log/data files in other drives where we have enough free space this provides us at least minimal amount of time frame to decide the course of action applicable.


We can use below 3 methods to reduce the size of tempdb:

SQL Server restart

DBCC SHRINKDATABASE
DBCC SHRINKFILE

By performing an sql restart or DBCC SHRINKDATABASE the tempdb size will be reset but it will not reduce below the last configured state. Last configured state is nothing but the file size set by using either the ALTER DATABASE with MODIFY option or the file size changed using DBCC SHRINKFILE. So we have to understand that the file size of tempdb will not come below this size.

By using DBCC SHRINKFILE we can reduce the file size of any database to desired value even we can shrink below the last configured state which is not possible using SHRINKDATABASE command. The only limitation is, we can’t shrink data/log file size smaller than the size of MODEL database size. So here also model database act as a base. After restart temp DB will be recreated by using model DB as a template.

A temp DB is configured by default to truncate log file when check pointer occurs.

Microsoft kb article:
http://support.microsoft.com/kb/307487

Log file of 'Model' database is growing huge.

Hi,

As many of us know the reason why the log file of a Model DB grows even though it won’t store any data in it I’m just sharing this as a recall...

Few times we notice ‘Model’ DB log file grown more than 90%. Why a database which doesn’t holds any data in it causing the log file grow so much. After referring few articles found many reasons that causes this issue and the below reason best suits my condition.

Basically a model database is just a template database for the entire server databases. If we make some changes to model database suppose like if create new user to model database the next user defined databases that are going to be created will have the new user by default, but we won’t find that user in already existing databases.

So if it’s just a template what’s causing the log file grow this much. If we are taking Full backup of Model database by keeping it in Full recovery model it definitely causes the log file to grow every time because full backup is a logged operation. So in order to resolve this issue we can change the recovery model of model database to ‘simple’ and so log will get truncated automatically and also there is no need of taking full backup of model DB daily unless we make some changes to it daily.