- Reduce SQL Log
- EXEC P_COMPDB 'TEST'
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 be these steps - 5. Separate the database if @ bkdatabase = 1beginif 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:' @ BKFNAMEEXEC ('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 the log files declare @fname nvarchar (260), @ s varchar (8000) declare tb cursor local for select fname from #t where type = 64open tb fetch next from tb into @fnamewhile @@ fetch_status = 0beginset @ s = ' del " ' rtrim (@fname) '" 'exec master..xp_cmdshell @ s, no_outputfetch next from tb into @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 = 0beginset @ s = @ s ',' '' rtrim (@fname) '' '' fetch next from tb into @fnameendclose tbdeallocate tbexec ( 'sp_attach_single_file_db '' ' @ dbname ' '' @ s)
Go
