SQL Server transaction log full solution

This is a case from the Microsoft community's elite project. I gave the solution.

 

Problem description:

Environment Description:
Operating System win2003
Database SQL SERVER 2000 SP4
About GB database data size
 
Specific fault description:
Connect to the portal system
The error message "unable to connect to the configuration server" is displayed.
Check locally on the server
Prompt in lower right corner
The disk of the database is full.
So I stopped the SQL service.
The disk is immediately released with more than 10 GB Space
Restart the SQL Service
Connect to the portal system
Still prompt unable to connect to the configuration database
You cannot connect to the database on the SQL console.
 
The portal system has three servers:
10.205.1.6 Application System server SharePoint
10.205.1.7 portal DB Server Database Server SQL 2000
10.205.1.5 DC Server
This error occurs on the 10.205.1.7 database server.

 

Error screenshot:

 

 

 

 

 

Solution:

 

This problem initially seems to be because the log files of the SharePoint_Config and tempdb databases occupy too much space, so that the disk space is full.
It is a little troublesome to solve this problem. Because the disk space is full, the SqlServer service may fail to start properly. Do not allow applications to connect to the database or SharePoint to connect to the database. Try to start the SqlServer service. See if it can be started. If not, you need to create a space. Delete unnecessary software. In short, you must start the SQL Server service. If the SQL Server service is available, do the following.
Open SQL Analyzer and execute the following statement:
Backup log tempdb with no_log -- Clear transaction logs
Go
Backup log transaction point_config with no_log -- Clear transaction logs
Go
 
Use tempdb
Go

Dbcc shrinkfile (tempdev, 10240) -- adjust the size of the master data file of tempdb to 10240 MB, which can be adjusted as needed. This command is not required.
Go

Dbcc shrinkfile (templog, 10240) -- Adjust the transaction log file size of tempdb to 10240 MB, which can be adjusted as needed
Go

-- For the SharePoint_Config database, the logic name of its primary data file should be SharePoint_Config, and its transaction log data file name may be SharePoint_Config_log.
-- You can use the following command to check the logic name of its data file,
Use SharePoint_Config
Go
Select name from sysfiles;
Go

If you know the logic name of the transaction log file, write the following command:
 
Use SharePoint_Config
Go
 
Dbcc shrinkfile (SharePoint_Config_log, 10240) -- Adjust the transaction log file size of the SharePoint_Config database to 10240 MB. you can adjust it as needed. The SharePoint_Config_log should be the name found in the previous select name from sysfiles. SharePoint_Config_log is used for the time being.
Go

The above can solve the current problem.

 

Deeper problems

Why is full space occupied by transaction logs?

 

The transaction log file is usually named <Database Name> _ log. ldf. It has an initial size. For example, 500 MB. We will change the data in the database when adding, deleting, and modifying the database. All the changes have been recorded in the transaction log by SqlServer. Over time, the transaction log file <Database Name> _ log. ldf will gradually become full of transaction logs, when the transaction log file <Database Name> _ log. when ldf is full, SqlServer will process it according to some specific policies. A common practice is to add transaction log files <Database Name> _ log. ldf 10% space. This avoids the database transaction failure because the transaction log file <Database Name> _ log. ldf is full. The disk space is not infinite. One day, the transaction log file <Database Name> _ log. ldf will no longer be able to increase the volume. The above situation occurs.

 

What is the right approach?

 

1. assign a fixed size to the transaction log file <Database Name> _ log. ldf, which cannot grow automatically. In fact, this is also true for the master database file <Database Name>. mdf.

 

2. Create a database monitoring task. When the transaction log is full, the transaction log is automatically backed up to reduce the space occupied by the transaction log.