Database Log Files increase with time. If they are not cleared for a long time, the files become very large. Therefore, they need to be cleared regularly. However, daily files are an important basis for restoring the database, it is unwise not to use log files.
Database Log Files increase with time. If they are not cleared for a long time, the files become very large. Therefore, they need to be cleared regularly. However, daily files are an important basis for restoring the database, it is unwise not to use log files.
1. Daily automatic backup
We strongly recommend that users with conditions perform this operation!
1. Open Enterprise Manager, open Microsoft SQL Server -- SQL Server group in the root directory of the console, and double-click to open your Server.
2. Click the tool in the menu above -- select the Database Maintenance Scheduler
3. Select the data to be automatically backed up in the next step-Update Data Optimization information in the next step. You do not need to select here-check data integrity in the next step, or choose not
4. Next, specify the database maintenance plan. The default one-week backup is performed. Click "change" and select "backup every day". Click "OK ".
5. Next, specify the backup disk directory and select the specified directory. For example, you can create a directory on disk D, such as ddatabak, and then select to use this directory here, if you have a large number of databases, it is best to create a subdirectory for each database, and then select the number of days before the deletion of the backup, generally set to 4-7 days, depending on your specific backup requirements, the backup file extension is generally bak and the default is used.
6. Specify the transaction log backup plan in the next step. Check whether you need to select the report to be generated in the next step. Generally, do not select the report to be generated in the next step. It is best to use the default option to complete the next step.
7. At this time, the database plan has been successfully run and will be automatically backed up according to the above settings.
Modify plan:
Open the Enterprise Manager, in the root directory of the console, open Microsoft SQL Server -- SQL Server group -- double-click your Server -- manage -- database maintenance plan -- and you will see the plan you set, can be modified or deleted
Regular automatic cleaning of Database Log Files
Database Log Files increase with time. If they are not cleared for a long time, the files become very large. Therefore, they need to be cleared regularly. However, daily files are an important basis for restoring the database, it is unwise not to use log files. Manual cleanup of a single database is good to say, but there are too many databases, or temporarily there is no time to clean up, the hard disk space may be full, affecting access. Therefore, it is more practical to set Automatic cleanup of database log files.
Manual cleaning method: Right-click the database to be cleaned up, select "properties", set the fault recovery model to simple on the "options" card, click OK, and close. Then, right-click the database, "All tasks"-"shrink Database". After confirmation, you can clear the log file. Remember to re-select "properties" and set the fault recovery model to complete.
Automatic cleanup method: the SQL server proxy service is used to execute automatic jobs.
Open the Enterprise Manager, choose "manage"> "SQL server proxy service"> "job", right-click the window on the right, and select "new job ". 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, type as script, and database as the database for which logs need to be cleared. 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 in the database name above, and enter the corresponding log file name in the Database Log File Name. Note that it is not the name with a Suffix in the resource manager, but in the Enterprise Manager, the name of the log in the Log tab in the database properties (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 operation steps, pay attention to the actions after each step succeeds or fails, and finally select the start step.
On the "scheduling" tab, enter the scheduling cycle, that is, the cycle of regular cleaning, similar to the backup maintenance plan. If necessary, you can set the notification item after the job is completed on the "Notification" tab at the end. You need to set the operator and set the corresponding service. This is not detailed here. Generally, you do not need ......
Previously, all the operations above were manually executed, which was troublesome. I searched online and added my own practices to sum up the above experience. It was indeed much easier to implement for a period of time than before. Archive one.
Sometimes our server cannot be executed. We recommend a backup software.
