1: Delete LOG1: Separate Database Enterprise Manager -> Server -> Database -> Right /> Separate Database 2: Delete Log File 3: Additional Database Enterprise Manager -> Server -> Database -> Right -> Additional Database This method Generate new logs, only 520 kg, then set this database to automatically shrink or use code: 77169Database, then attach one of 77169Database to the current server.
EXEC sp_detach_db @dbname = '77169database'EXEC sp_attach_single_file_db @dbname =' 77169database ', @physname =' c: / Program Files / Microsoft SQL Server / MSSQL / Data / 77169database.mdf '
2: Empty Log Dump Transaction Database name with no_log
Again: Enterprise Manager - Right click on the database you want to compress - All Tasks - Shrink Database - Shrink File - Select Log File - Select to shrink to XXM in the shrink mode, here will give an allowable shrinkage to The minimum number, enter this number directly, it is ok.
3: If you want to make it grow Enterprise Manager -> Server -> Database -> Properties -> Transaction Log -> Transaction Log -> Transaction Log -
Automatically shrink the log, you can also use the following statement alter database database name set auto_shrink on
The fault reduction model is changed to simply, use the statement is the use mastergoalter Database Database name set recovery SimpleGo ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ -------------------------------------------------
Truncate transaction log:
Backup log {database_name | @Database_name_var} {[with {no_log | Truncate_only}]}
- Compressed log and database file size
/ * - Special pay attention Please follow the steps, not in front of the steps, please do not do the following steps may not damage your database. - * /
1. Clear Log Dump Transaction Database name with no_log
2. Truncate the transaction log: Backup log database name with no_log
3. Shrink the database file (if not compressed, the database does not reduce the enterprise manager - Right click on the database you want to compress - All Tasks - Shrink Database - Shrink File - Select Log File - In Shrink Mode The choice to shrink to XXM, here will give a minimum M number that is allowed to shrink, enter this number directly, determine it - select data file - Select shrinkage to XXM in the shrink mode, here will give a permission The minimum number of shrinks is contracted, enter this number directly, it is ok.
You can also use SQL statements to complete - shrink database DBCC ShrinkDatabase (customer information)
- Spencer specified data file, 1 is the file number, you can query this statement: select * from sysfilesdbcc shrinkfile (1)
4. In order to maximize the log file (if it is SQL 7.0, this step can only be performed in the query analyzer) a. Separate Database: Enterprise Manager - Server - Database - Right - Separate Database
b. Delete log files in my computer
c. Additional Database: Enterprise Manager - Server - Database - Right - Right - Additional Database
This method will generate new logs, only 500 k or with code: 77169Database, then separate 77169Database, and then attach one of 77169Database to the current server.
a. Separate EXEC SP_DETACH_DB @dbname = '77169Database'
b. Delete log files
c. Additional EXEC SP_ATTACH_SINGLE_FILE_DB @dbname = '77169Database', @physName = 'C: / Program Files / Microsoft SQL Server / MSSQL / DATA / 77169DATABASE.MDF'
5. In order to automatically shrink in the future, do the following settings: Enterprise Manager - Server - Right-click Database - Properties - Option - Select "Auto Shrink"
--SQL statement setting mode: exec sp_dboption 'Database name,' autoshrink ',' True '
6. If you want to get the log growth too large, you don't want to grow too large enterprise manager - Server - Right-click Database - Property - Transaction Log - Limit the file growth to XM (x is your maximum data file size allowed)
--SQL statement setting mode: Alter Database Database name modify file (name = logical file name, maxsize = 20) ------------------------- -------------------------------------------------- --------------- / * - General stored procedures for compressed databases
Compressed log and database file size Because the stored procedure is to be separated, 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 @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 =' '') end - Separation processing 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 @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 @fnamendclose tbdeallocate tbexec ('sp_attach_single_file_db' '' @ DBNAME '' '' @ s) Go
