Tag Archives: moving system databases

Recovering from out-of-disk space conditions for tempdb

Another potential problem that you need to tackle as a DBA is running out of disk space on the hard disk that contains tempdb.This is because SQL Server makes extensive use of tempdb when:

  • Tracking versions for row-versioning concurrency.
  • Performing bulk load operations on tables with triggers enabled.
  • Running DBCC CHECKDB.
  • Rebuilding an index with SORT_IN_TEMPDB option.
  • Variables of LOB data types.
  • Storing intermediate query results, for example, during joins, aggregates, or sorts.
  • Service broker dialog information.
  • Caching temporary objects and tables.
  • Storing inserted and deleted tables in triggers.
  • Running sp_xml_preparedocument.

Continue reading →

Advertisement

How to move master and resource system databases?

I received an phone call from a friend today asking how to move master and resource system databases in Microsoft SQL Server 2012.

Well, the process is very simple and is explained in this blog post:

Moving “master” database

The following are the steps to move master database:

  1. Launch SQL Server Configuration Manager and display the Properties for the SQL Server service.

  2. Activate Startup Parameters and modify the datafile (-d) and log file (-l) startup parameters to reference the new location (see below):

  3. Click Apply and then stop the SQL Server service (see below):

  4. Move the master database files to the new location and then start the SQL Server service (see below):

Moving “resource” database (only applies to SQL Server 2005)

The following are the steps to move “resource” database:

  1. Start in master-only recovery mode by running:

    NET START MSSQLSERVER /f /T3608

  2. Launch a command prompt and run sqlcmd.

  3. Use the ALTER DATABASE statement with the MODIFY FILE option to specify the new location for the resource database data and log files.

  4. Use the ALTER DATABASE statement to make the Resource database read-only.

  5. Stop the SQL Server service.

  6. Move the database files for the “resource” database to new location.

  7. Start the SQL Server service.

I hope you will find this information useful 😀