SQL Server tempdb principle-startup process parsing practice

We know that the database will be restored (Redo,undo) during SQL Server instance startup and then open to provide services, but we know that tempdb does not provide a redo mechanism (Redo) How is tempdb restored? If tempdb is damaged what do we do, here I will show you through an example.

Sometimes tempdb can become very large for some reason, and some DBA solution is to restart the DB instance, and after reboot, tempdb will revert to its initial set size for the simple reason that tempdb is recreated.

The creation process for tempdb.

1 tempdb is created after the Model library is opened (you can see in the log that the model is always open before tempdb)

2 copy extent (s) from the model library to the tempdb master file and create the tempdb master data file (MDF) based on the Meta data information in the Master Library

3 populate the primary file with the primary file size for tempdb defined in master

4 Create a log file based on the primary log file size defined in master

5 creating, attaching other files

Note: If tempdb cannot be created, the instance shutdown

Here we do a test. Simply create a table in the Model library and restart the instance

Code

 Use Model Create Table int)

After restarting the instance, we can see that table T1 1-1 is also present in tempdb, which is actually the main file to copy the expansion area (extent) from model when Tempdb is started. We can even analyze the corresponding data page, You can see the corresponding data page tempdb is exactly the same as the model. Interested friends can try it on their own.

Figure 1-1

The problem comes, we can see that tempdb is first determined by the file location, size, if the boot process from the master library to obtain the location of the disk corruption or does not exist, then tempdb will not be created, the instance will not start, this will be an instance-level problem, the impact is serious.

At this point, we can use trace flag 3608 to start the instance with only the master restored, adjust it accordingly, and then restart the database.

Note: tf3608 is a very special case when starting a database, it is best to use minimal configuration startup (/F) to avoid other unexpected operations (corruption) on the database

Minimum configuration start/F

A. Single-user mode

B. No checkpoint (checkpoint)

C. Remote access and pre-reading is not supported

D. Prohibit start of execution proc

Analog failure

1 Modifying the tempdb file to a different drive letter

2 Disable the corresponding drive letter when restarting, SQL Server cannot start 1-2

Code

Alter Databasetempdb ModifyFile(Name= 'Tempdev', FileName= 'E:\tempdb.mdf')Alter Databasetempdb ModifyFile(Name= 'Templog', FileName= 'E:\templog.ldf')

Figure 1-2

In this case, the DB instance does not start properly. We can use the previously mentioned tf3608 to minimize the configuration of/F startup in the case of restoring master only, and then make the appropriate modifications.

Note: Minimal configuration startup because it is single-user mode, we should close the process associated with the instance before starting. (such as SQLAgent) otherwise the user will not be able to access the instance

Process steps

1 Code (Dos), or configure startup parameters in Configuration Manager

/ /F

2 using SQLCMD to access instances of an administrator link to perform relevant adjustments, specific information 1-3

sqlcmd-A-S-localhost-QAlter Databasetempdb ModifyFile(Name= 'Tempdev', FileName= 'D:\Program Files\Microsoft SQL Server\mssql10_50.mssqlserver\mssql\data\tempdb.mdf');" sqlcmd-A-S-localhost-QAlter Databasetempdb ModifyFile(Name= 'Templog', FileName= 'D:\Program Files\Microsoft SQL Server\mssql10_50.mssqlserver\mssql\data\templog.ldf',);"

3 Restart the database after the adjustment is complete

Figure 1-3

This allows us to complete the adjustment of tempdb in a specific scenario.

About the tempdb log

We all know that log files cannot be initialized in a timely manner. So it's best to pre-set its size to avoid the tempdb performance bottleneck caused by file growth.

Conclusion

In reality, because of the specific role of tempdb, which is likely to be a performance bottleneck, some applications put tempdb on a specific disk, such as SSDs, based on performance considerations, due to the working characteristics of tempdb, the replication rate can be very high, so that the SSD-based erase characteristics resulting in short life, This causes the library corruption problem, such problems need to be able to guard, but if the passive response, we can still use the relevant means of processing.