Truncate and shrink the transaction log and establish a simple maintenance plan with full backup and transaction log backup
Author: Yihong Gong child (lucky@dev-club.com) 2002
Foreword
One of the most common problems in the database is, "My log file grows to a number of GBs, how to reduce it?" There are several solutions in the essence, but this is the method of governance. How to specimalism? Here is a shortening log file and establish a simple backup plan for your reference.
Description of the code
Because the code has included detailed annotation and use, we give the source code directly, and please read the comments carefully before use, and understand the principles of the procedure, the author is not responsible for the consequences of the error. .
Source code
/ ************************************************** *****************************
* Uses: Truncate and shrink the transaction log, and establish a simple maintenance plan with full backup and transaction log backup
* Author: Yihong Gong child @ Dev-Club (developer Club) http://202.101.18.235
* Use the environment: Microsoft SQL Server 2000, and the library to be processed only contains a log file
* Note: 1. Please read this precaution carefully before use, and pay attention to the modification of data on the 17th and 25th lines.
* Library name, the 18th line of transaction logs are expected to shrink the size
* 2, you can copy this code, but please pay attention to replication, copy content
* All content must be included
* 3, this code can be used for commercial and other purposes, but must not be put into this code for the purpose of profit
* Broadcast, especially in traditional media
* 4, I don't have any damage to the system or the loss of the data of the system.
* Responsibility, implementing this code means you are willing to bear any risk
* 5, I don't provide any technical support for this code, read the comment and check the book online,
* You can get all the information you need.
* 6. Any suggestions or revised comments on this code, please go to the developer club
* (Http://202.101.18.235) database discussion layout
*********************************************************** *********************************** /
Declare @dbname sysname, @logfileid smallint, @taglogsize smallint
Declare @PLANID UNIQUEIDENTIFIER, @iResult Int
Declare @PlanName Varchar (128)
Declare @ Jobid1 binary (16), @ Jobid2 binary (16)
Declare @jobname sysname, @jobcommand nvarchar (3200)
SET @dbname = 'devclub'
Set @taglogsize = 256 - Unit as megabytes
/ ************************************************** *****************************
* Truncate and shrink the log ****************************************************** ******************************* /
- Please pay attention to modify the database name of the next line
Use devclub
- Truncate log
Backup log @dbname with no_log
- get FileID from log files
IF (Select Count (*) from sysfiles where status & 0x40 = 0x40) <> 1
Begin
The number of Print 'log files is not right
Return
End
Else
SELECT @logfileid = fileid from sysfiles where status & 0x40 = 0x40
- Shrink log file to the specified size
Execute ('DBCC ShrinkFile (' @logfileid ')') ')
USE MSDB
Set @PlanName = @dbname 'Maintenance Plan'
/ ************************************************** *****************************
* Establish a maintenance plan
*********************************************************** *************************** /
- Establish a maintenance plan
Execute @iResult = sp_add_maintenance_plan @PLANNAME, @PLANID OUTPUT
IF (@@ Error <> 0 or @iResult <> 0)
Begin
Print 'cannot establish a maintenance plan'
Return
End
- Associate the database to the maintenance plan
Execute @iResult = sp_add_maintenance_plan_db @PLANID, @dbname
IF (@@ Error <> 0 or @iResult <> 0)
Begin
Print 'does not associate a database to the maintenance plan'
Return
End
/ ************************************************** *****************************
* Establish a complete backup job
*********************************************************** *************************** /
Set @JobName = @PLANNAME N 'full backup job'
/ * Significance of Command
* Back up the log to the default backup directory of the disk and verify, remove the backup file earlier than 1 week, extension is Bak
* If you want to modify, please read the grammatical description of the SQLMAINT utility. * /
Set @Jobcommand = N'Execute Master.dbo.xp_sqlmaint n '' - Planid ' Convert (varchar (40), @PLANID)
'-Vrfybackup -bkupmedia disk -bkupdb -usededefdir -delbkups 1Weeks -Bkext "Bak"' '- Add job
Execute @iResult = sp_add_job @Job_ID = @ Jobid1 Output,
@Job_name = @JobName,
@Owner_login_name = n'sa ',
@Description = N'NO Description Available. ',
@category_name = NULL,
@enabled = 1,
@Notify_level_email = 0,
@Notify_level_page = 0,
@notify_level_netsend = 0,
@Notify_level_eventlog = 2,
@ delete_level = 0
IF (@@ Error <> 0 or @iResult <> 0)
Begin
Print 'cannot establish a full backup job'
Return
End
- Add job steps
Execute @iResult = sp_add_jobstep @Job_ID = @ Jobid1,
@step_id = 1,
@Step_name = n 'step 1',
@command = @Jobcommand,
@Database_name = n'master ',
@server = n '',
@Database_user_name = n '',
@subsystem = n'tsql ',
@cmdexec_success_code = 0,
@flags = 4,
@retry_attempts = 0,
@Retry_Interval = 0,
@output_file_name = n '',
@on_success_step_id = 0,
@on_success_action = 1,
@On_fail_step_id = 0,
@on_fail_action = 2
IF (@@ Error <> 0 or @iResult <> 0)
Begin
Print 'cannot establish a full backup job step'
Return
End
Execute @iResult = sp_update_job @Job_ID = @ Jobid1, @start_step_id = 1
IF (@@ Error <> 0 or @iResult <> 0)
Begin
Print 'does not add a full backup job step'
Return
End
- Add job scheduling, once a day, 2: 17 minutes, too lazy to write a variable, if you want to change your own change @Active_start_time
- If you want to change the cycle, please read the instructions of sp_add_jobschedule carefully after booking Books online, modify the number of parameters of @Freq at the beginning of @Freq Execute @iResult @Job_ID = @ Jobid1,
@Name = n 'First Dispatch',
@enabled = 1, @freq_type = 4,
@Active_start_date = 20000101,
@Active_start_time = 21700, @freq_interval = 1,
@freq_subday_type = 1, @freq_subday_interval = 0,
@freq_relative_interval = 0, @freq_recurrence_factor = 0,
@Active_end_date = 99991231, @Active_end_time = 235959
IF (@@ Error <> 0 or @iResult <> 0)
Begin
Print 'does not add full backup job scheduling'
Return
End
- Add a target server
Execute @iResult = sp_add_jobserver @Job_ID = @ Jobid1, @server_name = n '(local)'
IF (@@ Error <> 0 or @iResult <> 0)
Begin
Print 'does not add a fully backed up target server'
Return
End
- Associate the job to the maintenance plan
Execute @iResult = sp_add_maintenance_plan_job @PLANID, @ Jobid1
IF (@@ Error <> 0 or @iResult <> 0)
Begin
Print 'does not associate a full backup job to the maintenance plan'
Return
End
/ ************************************************** ***********************************
* Establish a transaction log backup job
*********************************************************** *********************************** /
Set @JobName = @PLANNAME N 'transaction log backup job'
/ * Significance of Command
* Back up the log to the default backup directory of the disk and the directory of the library and check, remove the backup file earlier than 4 days, extension is TRN
* If you want to modify, please read the grammatical description of the SQLMAINT utility. * /
Set @Jobcommand = N'Execute Master.dbo.xp_sqlmaint n '' - Planid ' Convert (varchar (40), @PLANID)
'-Vrfybackup -bkupmedia disk -bkuplog -usededefdir -delbkups 4days -crbksubdir -bkext "trn"' ''
- Add Jobs Execute @iResult = SP_ADD_JOB @Job_ID = @ Jobid2 Output,
@Job_name = @JobName,
@Owner_login_name = n'sa ',
@Description = N'NO Description Available. ',
@category_name = NULL,
@enabled = 1,
@Notify_level_email = 0,
@Notify_level_page = 0,
@notify_level_netsend = 0,
@Notify_level_eventlog = 2,
@ delete_level = 0
IF (@@ Error <> 0 or @iResult <> 0)
Begin
Print 'cannot establish a job of transaction log backup'
Return
End
- Add job steps
Execute @iResult = sp_add_jobstep @Job_ID = @ Jobid2,
@step_id = 1,
@Step_name = n 'step 1',
@command = @Jobcommand,
@Database_name = n'master ',
@server = n '',
@Database_user_name = n '',
@subsystem = n'tsql ',
@cmdexec_success_code = 0,
@flags = 4,
@retry_attempts = 0,
@Retry_Interval = 0,
@output_file_name = n '',
@on_success_step_id = 0,
@on_success_action = 1,
@On_fail_step_id = 0,
@on_fail_action = 2
IF (@@ Error <> 0 or @iResult <> 0)
Begin
Print 'cannot establish a job step by a transaction log backup?
Return
End
Execute @iResult = sp_update_job @Job_ID = @ Jobid2, @start_step_id = 1
IF (@@ Error <> 0 or @iResult <> 0)
Begin
Print 'does not add job steps for transaction log backup
Return
End
- Add job scheduling, once every hour, 32 points, lazy to write a variable, if you want to change your own change @Active_start_time
- If you want to change the cycle, please read the instructions of sp_add_jobschedule in books online after modifying several parameters starting with @freq.
Execute @iResult = SP_ADD_JOBSCHEDULE @Job_ID = @ Jobid2, @ Name = n 'First Dispatch', @enabled = 1, @Freq_Type = 4,
@Active_start_date = 20000101, @Active_start_time = 3200,
@freq_interval = 1, @freq_subday_type = 8,
@freq_subday_interval = 1, @Freq_RELATIVE_INTERVAL = 0,
@freq_recurrence_factor = 0, @Active_end_date = 99991231, @Active_end_time = 235959
IF (@@ Error <> 0 or @iResult <> 0)
Begin
Print 'does not add job scheduling in transaction log backup
Return
End
- Add a target server
Execute @iResult = SP_ADD_JOBSERVER @Job_ID = @ Jobid2, @server_name = n '(local)'
IF (@@ Error <> 0 or @iResult <> 0)
Begin
Print 'cannot add a target server that is backed up by transaction log'
Return
End
- Associate the job to the maintenance plan
Execute @iResult = sp_add_maintenance_plan_job @PLANID, @ JobID2
IF (@@ Error <> 0 or @iResult <> 0)
Begin
Print 'does not associate the job log backup to the maintenance plan'
Return
End
Print 'code has been successfully executed, thank you for using this code'
Reference resource
SQL Server Book Online.
About author
Yihong Mono, one of the founders of Chinaasp. MSSQL experts developed the largest Microsoft SQL Server application in China. You can contact him through lucky@dev-club.com.
