Compressing database log storage process

Create a p_compdb stored procedure in the master.

Create   Proc P_compdb
@ Dbname Sysname, -- Name of the database to be compressed
@ Bkdatabase   Bit = 1 , -- Because the database may be damaged in the log separation step, you can choose whether to automatically Database
@ Bkfname   Nvarchar ( 260 ) = ''   -- The name of the backup file. If not specified, it is automatically backed up to the default backup directory. The backup file name is: database name + date and time.
As
-- 1. Clear logs
Exec ( ' Dump transaction [ ' + @ Dbname + ' ] With no_log ' )

-- 2. transaction Log truncation:
exec ( ' backup log [ ' + @ dbname + ' ] With no_log ' )

-- 3. shrink database files (if not compressed, database files will not be reduced
exec ( ' DBCC shrinkdatabase ([ ' + @ dbname + ' ]) ' )

--4. Set automatic contraction
Exec('Exec sp_dboption'''+@ Dbname+''',''Autoshrink'',''True''')

-- The subsequent steps are dangerous. You can choose whether to perform these steps.
-- 5. Database Separation
If   @ Bkdatabase = 1
Begin
  If   Isnull ( @ Bkfname , '' ) = ''  
Set   @ Bkfname = @ Dbname + ' _ ' + Convert ( Varchar , Getdate (), 112 )
+ Replace ( Convert ( Varchar , Getdate (), 108 ), ' : ' , '' )
  Select Prompt information = ' Back up the database to the default SQL Backup Directory. Backup File Name: ' + @ Bkfname
  Exec ( ' Backup database [ ' + @ Dbname + ' ] To disk = ''' + @ Bkfname + '''' )
End

-- Separate
Create   Table # T (fname Nvarchar ( 260 ), Type Int )
Exec ( ' Insert into # T select filename, type = Status & 0x40 from [ ' + @ Dbname + ' ]. Sysfiles ' )
Exec ( ' Sp_detach_db ''' + @ Dbname + '''' )

-- Delete log files
Declare   @ Fname   Nvarchar ( 260 ), @ S   Varchar ( 8000 )
Declare TB Cursor Local For   Select Fname From # T Where Type = 64
Open TB
Fetch   Next   From TB Into   @ Fname
While   @ Fetch_status = 0
Begin
  Set   @ S = ' Del" ' + Rtrim ( @ Fname ) + ' " '
  Exec Master .. xp_mongoshell @ S , No_output
  Fetch   Next   From TB Into   @ Fname
End
Close TB
Deallocate TB

-- Additional database
Set   @ S = ''
Declare TB Cursor Local For   Select Fname From # T Where Type = 0
Open TB
Fetch   Next   From TB Into   @ Fname
While   @ Fetch_status = 0
Begin
  Set   @ S = @ S + ' , ''' + Rtrim ( @ Fname ) + ''''
  Fetch   Next   From TB Into   @ Fname
End
Close TB
Deallocate TB
Exec ( ' Sp_attach_single_file_db ''' + @ Dbname + '''' + @ S )

Go