How log files affect the startup of my database

Original article: http://blogs.msdn.com/ B /apgcdsd/archive/2011/12/30/10251946.aspx

Intercept a paragraph for future reference
How does the log file affect the startup of my database?

Whether your SQL Server is started, you can recover a database from a backup, or in other cases, before your database can be used properly, your database will enter the recovery status. Http://msdn.microsoft.com/en-us/library/ms190442.aspx if this step fails, your database will enter the suspect State and cannot be used normally. In some special cases, this rediscovery takes a long time. Before the database enters the online status, we cannot think that SQL Server can be used normally.

So what has been done in recovery?

We can run the following statement in your SSMs. Before running, you can press Ctrl + T to convert the result to plain text format.

 

Sp_readerrorlog returns the error log information of the SQL Server since the last time the SQL Server service was started or the error log was recycled. There are two words that may confuse you.FirstLogAnd the log file we mentioned above (Transaction Log).LogNot one thing. Second, the so-calledError LogIt does not mean thatError.SQL Server uses this error log to record a lot of diagnostic information. Therefore, you can think that this is just a common log file that records a lot of SQL Server Information.

 

You may find the above information in the returned result. In quotation marks, the names of each database under your current database instance.

 

Take the database 'caselync' as an example. Because the error log records the information of all databases in the current SQL server instance, you may see that the logs related to this database are sequentially distributed in different places of the log. In the first and second rows, two words are mentioned: rolled forward and rolled back. These two words are closely related to the transaction log mentioned in this article.

Previously, we mentioned the data modification process: the data is synchronized to the transaction log, and then synchronized to the data file when the checkpoint occurs. So, if my transaction is not committed or rolled back, whether the data modification is also in the log file (transaction log) or in a data file )?

The answer is: they are all there!

When the data changes, SQL server does not distinguish whether the statement displays transaction operations (begin transaction, commit/rollback transaction) or whether the transaction has been completed. SQL Server faithfully records all modification operations. Begin transaction and commit/rollback transaction are also operations to be recorded in log files.

Because the modification to the transaction log is prior to the modification to the data file, when your database is in the recovery status, then the transaction logFrom the latestCheckpointClick to start the following operations:

1. If the operation is already in the transaction log, but not in the data file, and if it uses an explicit transaction operator and is in the commit state, a rolled forward operation will occur, synchronize the operation to the data file.
2. If the operation is already in the transaction log, but not in the data file, and if it uses an explicit transaction operator and is in the rollback transaction state, A rolled back operation will take place, and the relevant data in the data file will be rolled back before the transaction occurs.
3. If the operation is already in the transaction log, but not in the data file, and if the explicit transaction is used in the log file, there is no commit operation or rollback operation, A rolled back operation will take place, and the relevant data in the data file will be rolled back before the transaction occurs.
4. If the operation is already in the transaction log, but not in the data file, and if it does not use the explicit transaction operator (begin transaction ), it is considered as a rolled forward operation.
5. after the above steps are completed, SQL Server will mark the database as a checkpoint and write the transaction log, indicating that the data file and transaction log have been synchronized. This indicates that the database recovery is complete. The database enters the online status and is in normal use. If your database has enough modification operations from the last checkpoint to the current one, the error log also shows the progress of the SQL Server recovery covery.