Describes how to delete SQL Server logs.

Each database of SQL Server, whether it is a system database master, model, sybsystemprocs, tempdb) or a user database, has its own transaction log, and each database has a syslogs table. Logs record user operations on the database, so if you do not need to clear the Log, the log will continue to grow until the occupied space. You can run the dump transaction command to clear logs, or use the trunc log on chkpt option to clear logs automatically at intervals. Managing database logs is a must for users to operate databases.

1. delete a LOG

1: Detach Database Enterprise Manager-> server-> database-> right-click-> detach Database

2: delete LOG files

3: attach the Database Enterprise Manager-> server-> database-> right-click-> attach Database

This method generates a new LOG with a size of more than 520 KB.

Then set the database to automatically contract

Or use the code:

The following example separates the 77169 database and then attaches a file in the 77169 database 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'

Ii. Clear logs

Dump transaction database name WITH NO_LOG

Next:

Enterprise Manager -- Right-click the database you want to compress -- all tasks -- contract database -- contract file -- Select log file -- select to contract to XXM in the contract mode, here we will provide a minimum number of M that can be reduced. Enter this number directly and click OK.

3. If you want to avoid growth in the future

Enterprise Manager-> server-> database-> properties-> transaction log-> limit file growth to 2 MB

You can also use the following statement to automatically contract logs:

Alter database name SET AUTO_SHRINK ON

The fault recovery model is changed to simple. The statement is as follows:

USE MASTER GO Alter database name SET RECOVERY SIMPLE GO ------------------------------------------ Transaction Log truncation: Backup log {database_name | @ database_name_var} { [ {NO_LOG | TRUNCATE_ONLY}] } -- Compressed log and database file size /* -- Pay special attention Follow these steps. Do not follow these steps. Otherwise, your database may be damaged. --*/

1. Clear logs

Dump transaction database name WITH NO_LOG

2. truncate transaction logs:

Backup log database name WITH NO_LOG

3. Shrink database files(If not compressed, the database file will not be reduced

Enterprise Manager -- Right-click the database you want to compress -- all tasks -- contract database -- contract file

-- Select log file -- select to shrink to XXM in the contraction mode. Here, a minimum number of MB allowed to be shrunk is displayed. Enter this number directly and click OK.

-- Select data file -- select to shrink to XXM in the contraction mode. Here, a minimum number of MB allowed to be shrunk is displayed. Enter this number directly and click OK.

You can also use SQL statements to complete

-- Shrink Database

Dbcc shrinkdatabase (customer profile)

-- Contract the specified data file. 1 indicates the file number. You can use this statement to query:

select * from sysfiles DBCC SHRINKFILE(1)

4. To minimize log files(If SQL 7.0 is used, this step can only be performed in the query analyzer)

A. Separate the database:

Enterprise Manager -- server -- database -- Right-click -- detach Database

B. Delete LOG files in my computer

C. Additional database:

Enterprise Manager -- server -- database -- Right-click -- attach database this method will generate a new LOG with a size of more than 500 KB

Or use the code:

The following example separates the 77169 database and then attaches a file in the 77169 database to the current server.

A. Separation

EXEC sp_detach_db @ dbname = '77169database'

B. Delete log files

C. Attach

EXEC sp_attach_single_file_db @dbname = '77169database', @physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\77169database.mdf'

5. In order to automatically contract in the future, make the following settings:

Enterprise Manager -- server -- Right-click Database -- Property -- option -- select "auto contract"

-- SQL statement setting method:

EXEC sp_dboption 'database name', 'autowrite', 'true'

6. If you want to prevent the log from increasing too much in the future

Enterprise Manager -- server -- Right-click Database -- properties -- transaction log

-- Limit file growth to xM (x is the maximum data file size you allow)

-- SQL statement settings:

Alter database name modify file (name = logical file name, maxsize = 20) -------------------------------------------------------------- /* -- Compress the General stored procedure of the database The size of the compressed logs and database files. Because the database must be separated, the stored procedure cannot be created in the compressed database. /* -- Call example Exec p_compdb 'test' --*/ Use master -- note that this stored procedure should be created 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, -- Name of the database to be compressed @ Bkdatabase bit = 1, -- because the log separation step may damage the database, you can choose whether to automatically Database @ Bkfname nvarchar (260) = ''-- Name of the backup file. If this parameter is not specified, it is automatically backed up to the default backup directory, Backup File Name: database name + Date and Time As

-- 1. Clear logs

exec('DUMP TRANSACTION ['+@dbname+'] WITH NO_LOG')

-- 2. truncate transaction logs:

exec('BACKUP LOG ['+@dbname+'] WITH NO_LOG')

-- 3. compress the database file (if it is not compressed, the database file will not be reduced

exec('DBCC SHRINKDATABASE(['+@dbname+'])')

-- 4. Set automatic contraction

exec('EXEC sp_dboption '''+@dbname+''',''autoshrink'',''TRUE''')

-- The subsequent steps are dangerous. You can choose whether to perform these steps.

-- 5. Database Separation

If @ bkdatabase = 1
Begin
If isnull (@ bkfname, '') =''
Set @ bkfname = @ dbname + '_' + convert (varchar, getdate (), 112)
+ Replace (convert (varchar, getdate (), 108 ),':','')
Select prompt = 'Back Up the database to the default SQL Backup Directory. Backup File Name: '+ @ bkfname
Exec ('backup database ['+ @ dbname +'] to disk = ''' + @ bkfname + '''')
End

-- Separate
Create table # t (fname nvarchiar (260), type int)
Exec ('insert into # t select filename, type = status & 0x40 from ['+ @ dbname +'] .. sysfiles ')
Exec ('SP _ detach_db ''' + @ dbname + '''')

-- Delete a log file
Declare @ fname nvarchar (260), @ s varchar (8000)
Declare tb cursor local for select fname from # t where type = 64
Pen tb
Fetch next from tb into @ fname
While @ fetch_status = 0
Begin
Set @ s = 'del "'+ rtrim (@ fname) + '"'
Exec master .. xp_mongoshell @ s, no_output
Fetch next from tb into @ fname
End
Close tb
Deallocate tb

-- Attach a database
Set @ s =''
Declare tb cursor local for 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

1. SQL Server abnormally deletes the log file ldf) Restoration Method
2. Several Key Techniques for SQL Server Cluster
3. Detailed steps for upgrading to SQL Server 2008