Code Alter proc [DBO]. [cleantranlog]
As
Begin
Declare @ num tinyint -- number of executions
Declare @ backlogname varchar (100); -- backup log file name
Declare @ backlogpath varchar (100); -- path of the backup log file
Set @ num = 0;
Set @ backlogpath = n'c: \ sqlbackup '; -- set the path of the backup log.
-- Backup the Image Log File three times and delete it at the same time
While (@ num <3)
Begin
Declare @ logpath varchar (100)
Set @ backlogname = cast (@ num as varchar (2) + '. trn ';
Set @ logpath = @ backlogpath + '\' + @ backlogname
Backup log mirror to disk = @ logpath with noformat, noinit,
Name = @ backlogname, Skip, rewind, nounload, stats = 10
Set @ num = @ num + 1
-- Delete the backup log file after the trn log file is backed up.
Execute master. DBO. xp_delete_file 0, @ logpath;
End
-- Shrink the log file to 200 MB
DBCC shrinkfile (mongo_log, 2 );
End
