/ * - General stored procedure for compressed databases
Compressed log and database file size
Since the database is to be separated
So the stored procedure cannot be created in a compressed 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 to be compressed
@BKDATABASE BIT = 1, because in the step of separation log, you may damage the database, so you can choose whether to automatically
@BKFNAME NVARCHAR (260) = '' - Backup file name, if not specified, automatically backed up to the default backup directory, backup file name: Database name datetime
AS
--1. Empty log
EXEC ('Dump Transaction [' @ dbname '] with no_log')
--2. Truncate transaction log:
EXEC ('backup log [' @ dbname '] with no_log')
--3. Shrink the database file (if not compressed, the database file will not decrease
EXEC ('DBCC ShrinkDatabase)')
--4. Setting automatic shrinkage
EXEC ('exec sp_dboption' ' @ dbname ' ',' 'autoshrink' ',' 'True' ')
- The following steps are surely dangerous, you can choose whether you should these steps
--5. Separate database
IF @ bkdatabase = 1
Begin
IF isnull (@BKFNAME, '') = ''
Set @ bkfname = @ DBNAME '_' Convert (varchar, getdate (), 112)
Replace (Convert (varchar, getdate (), 108), ':', '')
SELECT prompt = '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 '' ') - Delete Log File
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 @fname
While @@ fetch_status = 0
Begin
Set @ s = 'del "' rtRim (@fname) '" "
EXEC MASTER..XP_CMDSHELL @ S, NO_OUTPUT
Fetch next from tb @fname
end
Close TB
DEAALLOCATE TB
- Additional Database
Set @ s = ''
Declare TB CURSOR LOCAL for SELECT FNAME FROM #t where type = 0
Open TB
Fetch next from tb @fname
While @@ fetch_status = 0
Begin
Set @ s = @ S ',' '' RTRIM (@FNAME) '' ''
Fetch next from tb @fname
end
Close TB
DEAALLOCATE TB
EXEC ('sp_attach_single_file_db' '' @ dbname '' '' @ s)
Go
