http://blog.9cbs.net/zjcxc/archive/2004/03/08/20097.aspx
It is often seen on 9CBS to see that the compressed log file is improperly handled, causing the database damage, and cannot recover the data, so a universal database log file compressed stored procedure to resolve this issue:
/ * - The general stored procedure compression log and database file size of the compressed database are not created in the compressed database because the stored procedures are separated by the database.
- Zou Jian 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, - Database name @BKDATABASE bit = 1, because of the separation log, you can damage the database, so you can choose whether the automatic 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. Clear log EXEC ('dump Transaction [' @ dbname "with no_log ' )
--2. Truncated transaction log: EXEC ('backup log [' @ dbname '] with no_log')
--3. Shrinking database files (if not compressed, database files do not reduce EXEC ('DBCC ShrinkDatabase ([' @ DBNAME '])')
--4. Setting automatic shrinking EXEC ('exec sp_dboption' '' @ dbname '', '' autoshrink '', '' True '')
- Back steps are certainly dangerous, you can choose whether you should these steps - 5. Separate the database if @ bkdatabase = 1begin if isnull (@BKFNAME, '') = '' set @ bkfname = @ dbname '_' Convert (varchar, getdate (), 112) replace (varchar, getdate (), 108), ':', '') SELECT prompt information = 'backup database to SQL default backup directory, backup file name:' @BKFNAME EXEC ('BACKUP DATABASE [' @ dbname '] to disk =' '' @ bkfname '' '') End
- Separate treatment 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 '' ') - Deleting Log File Declare @fname Nvarchar (260), @ s varchar (8000) Declare TB Cursor Local for SELECT FROME from #t where type = 64open TB Fetch next from tb @ @ 飞 f @ FNameWhile @@ fetch_status = 0begin set @ s = 'del "' rtrim (@fname) '"' exec master..xp_cmdshell @ s, no_output fetch next from tb @fnameendclose TBDEAllocate TB
- Additional Database Set @S = '' DECLARE TB CURSOR LOCAL for SELECT FNAME FROM #t where type = 0open TB Fetch Next from Tb Into @fnameWHile @@ fetch_status = 0begin Set @ s = @ S ',' '' RTRIM (@fname) '' 'Fetch next from tb @fnameendclose tbdeallocate tbexec (' sp_attach_single_file_db '' @ dbname '' '' @ s) Go
