INF: Starting with Minimal Configuration Removes Tempdb Fragment (170124)

SUMMARY

If you expanded tempdb from the default device (master device) on additional devices, only the 2 MB tempdb on the master device exists if you start SQL Server in minimal configuration (using the -f option). The additional fragments are removed.

Because the additional physical device files for tempdb still exist in SQL Server, you only have to expand tempdb again on these devices to get the former size.

MORE INFORMATION

The following illustrates the behavior. After the installation of SQL Server, you have a default tempdb with a size of 2 MB on the master device.

If you create an additional device of 10 MB and alter the size of the tempdb (dbid 2) on this device, you have the following entries in the sysusages system table:

   dbid       segmap       lstart       size       vstart
   --------------------------------------------------------
     2          7            0          1024       2564
     2          7            1024       5120       83886080

The first row represents the default on the master device, and the second row results from the alteration on the additional device.

Now open a command prompt session and start SQL Server in minimal configuration mode with the following command:

sqlservr -c -f

Shut Down SQLServer and restart without minimal configuration.

In the sysusages system table, the change is reflected. There is only one entry for the tempdb, indicating a size of 2 MB on the master device. This is exactly the default entry that also exists after a new installation of SQL Server.

dbid       segmap       lstart       size       vstart
------------------------------------------------------
2          7            0            1024       2564

Now you only have to alter the tempdb again on the additional device to get the former size of the tempdb.