Shrink SQL Log

--Shrink SQL log

--exec p_compdb ' test '

Create proc P_compdb
@dbname sysname--The name of the database to compress
@bkdatabase bit=1, because the database may be corrupted in the steps of separating the logs, so you can choose whether to automatically database
@bkfname nvarchar (260) = '--backup file name, if not specified, automatically back up to the default backup directory, backup file name: Database name + Date Time
As
--1. Empty log
EXEC (' DUMP TRANSACTION [' + @dbname + '] with no_log ')

--2. Truncate the transaction log:
EXEC (' BACKUP LOG [' + @dbname + '] with no_log ')

--3. Shrink the database file (if not compressed, the database file will not be reduced
EXEC (' DBCC shrinkdatabase ([' + @dbname + ']) ')

--4. Set Auto Shrink
EXEC (' exec sp_dboption ' + @dbname + ', ' autoshrink ', ' TRUE ')

--there is a certain danger behind the steps, and you can choose whether these steps should
--5. Detach the database
If @bkdatabase =1
Begin
If IsNull (@bkfname, ') = '
Set @bkfname = @dbname + ' _ ' +convert (Varchar,getdate (), 112)
+replace (CONVERT (Varchar,getdate (), 108), ': ', '
Select message = ' Back up database to SQL default backup directory, Backup filename: ' + @bkfname
EXEC (' backup database [' + @dbname + '] to disk= ' + @bkfname + ' ")
End

--Separate processing
CREATE table #t (fname nvarchar), 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 (@s), varchar (8000)
Declare TB cursor Local to 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_cmdshell @s,no_output
FETCH NEXT from TB into @fname
End
Close TB
Deallocate TB

--Additional databases
Set @s= '
Declare TB cursor Local to 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