System Databases

We all know that tempDB is the scratchpad for SQL Server and is used for all manner of things from, obviously, housing our temp tables through to being used for memory spills during query execution.

It's not a frequent occurence but times have arisen in which it's necessary to relocate the master database within your SQL Server. With SQL Server 2008 this is an incredibly simple operation and shouldn't cause you any problems.

New, faster disks arrived? Maybe you''ve done a reshuffle and ended up with a clean, unused physical drive? Either way your tempDB needs to be relocated. Doing so is actually incredibly simple and requires just a few short steps.

This came to light when I was working with a company who were struggling with their MSDB database. They had a requirement to maintain huge volumes of historical job data and backup information and didn't want to archive this to another database. Therefore this meant a large MSDB which had filled their drive and they needed to relocate it.

There are 4 main system databases and 1 additional database. These are: