MSSQL database log cleanup instance details

When the log file is full and the SQL database cannot write the file, you can use the following methods:

Open SQL query analyzer => connect to the server where the database is located
You can use the following statement to clear database logs:
-Clear logs when mssql transaction logs become larger
Dump transaction database name WITH NO_LOG

-Truncate transaction logs
Backup log database name WITH NO_LOG

-Shrink database
Dbcc shrinkdatabase (database name)

Another method is risky because the log files of SQL SERVER are not immediately written to the master database file, such as improper processing,

This may cause data loss.
1. Back up the database (just in case)
2. Choose "detach Database Enterprise Manager"> "server"> "database"> "right-click"> "detach Database".
3. Delete LOG files
4. Attach Database Enterprise Manager> server> right-click> attach database
This method generates a new LOG with a size of more than 500 K.

If you do not want it to become larger later.
Use in SQL2000:
Right-click the database and choose Properties> Options> fault recovery> Model> select simple model.
Or use an SQL statement:
Alter database name set recovery simple

V if we manually clear it, we may sometimes forget it. I will give it an automatically cleared instance.

Automatic cleanup method: the SQL server proxy service is used to execute automatic jobs.

Open Enterprise Manager, go to "management"-"SQL server proxy service"-"job", right-click in the right-side window, and select "create

Industry ". On the "General" tab, enter the job name and description. It is recommended that you use sa or the default management account as the owner.

Go to the "steps" tab, create a job step, enter the step name, the type is script, and the database is the database that needs to clear logs

In the following command, enter the following command:

Dump transaction database name WITH NO_LOG

Dbcc shrinkfile (database log file name, 1)

Enter the name of the database to be maintained for the database name above, and enter the corresponding log file name for the database log file name. Note that

It is not the name with the suffix seen in the resource manager, but the log in the log tab in the database properties in the enterprise manager.

Name (usually only a suffix ......), Click OK to add a job step. If you need to maintain multiple databases, use the preceding

Method to repeat the add job step, pay attention to the action after each step is successful or failed, and finally select the start step.

On the "schedule" tab, enter the scheduling cycle, that is, the cycle of regular cleaning, similar to the backup maintenance plan.