Be cautious about system downtime caused by self-growth of databases

Fault symptom:

1. Computers are running slowly and cannot be manipulated.

2. The web programs supported by the data source are down, and the Error exposed is: Connection timeout.

3. There is no problem connecting to the database locally, but the local Web program cannot access the data connection.

4. CPU utilization and memory usage are not high

There are many possibilities for the system to become a machine, which makes it difficult to identify the cause. Only blanket search is supported.

1. The system has carried out comprehensive virus detection and Trojan scanning, and no suspicious files are found. The system patch is also repaired in a timely manner, making it unlikely to be infected with Trojans.

Troubleshoot based on the system LOG:

2. The program loaded by remote logon is incorrect. Despite multiple errors, the database connection is not interrupted. Other machines with similar problems did not go down.

3. Automatic Log growth timeout, which is highly probable. This Error is most exposed before the system is shut down twice, and the ErrorLog disappears after the system is normal.

Error message:

Cause analysis:

By default, SQL Server automatically increases the size of the original database file by 10% when the database file is full. There are also log files. If your database file is large and the file size is increased by 10%, it will be a great burden.

After query, the number of log files in one database already exceeded 10% GB. If it increases by 200, it will increase by more than MB, which takes a long time to complete. This operation may time out. Then other insert, update, and query operations generate a chain reaction. The number of threads in the worker process increases significantly.

If the database performs an update operation in a transaction, and the log space is insufficient, you need to increase the log space by 10%, and the database transaction execution timeout time is 30 seconds by default, at this time, if the operation times out, the transaction will be rolled back. At this time, other database operations will generate a chain reaction, forming a blocking.

Small experiment: in order to prove this problem, a simple example is provided to set that the space will be increased by 1 GB After logs are full. As a result, this type of error also occurs, and during this period, you cannot perform any operations on the database.

MSDN introduction:

If the log space required for the transaction you run is greater than the available space and you have enabled the automatic growth option for the transaction log of the database, some of them are the time taken to increase transaction logs by configuration. If the growth is large, or there are other factors that lead to a longer time, the query in which you open the transaction may fail due to a timeout error. The automatic growth of data in the database may cause such problems. Http://support.microsoft.com/kb/315512/zh-cn

Solution: Set the file growth to a lower percentage or directly specify how many megabytes are added.

Event Type: Information

Event Source: MSSQLSERVER

Event Type: (2)

Event ID: 5144

Date: 2009-3-3

Event: 11:57:18

User: N/

Description:

The automatic growth of the file 'databasename _ log' in the database 'databasename' has been canceled by the user, or timed out after 30687 milliseconds. Use alter database to set a smaller FILEGROWTH value for this file, or explicitly set a new file size.

For more information, see Help and Support Center in http://go.microsoft.com/fwlink/events.asp.

I carefully reviewed some documents and obtained the analysis results from several patients on the Internet. The problem has basically been identified.

1. Experience in optimizing SQL Server databases
2. How MySQL offers millions of High-concurrency websites
3. Slow SQL Server Query Solution