SQL Server log Operations related code

Server

Method One:

--Let log no longer grow, but cannot compress log
EXEC sp_dboption ' your_dbname ', ' trunc. Log ', ' TRUE '

Method Two:

--Super strong database file and log file compression,
--After compression may cause the database to not be normal access, restart the database can
DUMP TRANSACTION [database name] with NO_LOG
BACKUP LOG [database name] with NO_LOG
DBCC shrinkdatabase ([database name])

Method Three:

Often on the csdn to see netizens posted that compressed log file processing, resulting in database corruption, or even can not recover data, so wrote a common database log file compression stored procedures to solve this problem:

/**//*--Common stored procedures for compressed databases

Compress log and database file size
Because you want to detach the database
So stored procedures cannot be created in a compressed database

--Jiangjian 2004.3--*/

/**//*--Call Example
EXEC p_compdb ' test '
--*/

Use master-Note that this stored procedure is built in the master database
Go

if exists (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ P_COMPDB] and OBJECTPROPERTY (ID, N ' isprocedure ') = 1)
drop procedure [dbo]. [P_compdb]
Go

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