How to delete SQLServer large-capacity logs

Editor: Like our server, we usually use tools to program logs by 1 MB. If you are not bothered, see the manual method: 1: Delete LOG1: detach Database Enterprise Manager-server-database-right-click-Detach database 2: delete LOG file 3: attach Database Enterprise Manager-server-database-right-click-Attach database this method generates a new LOG, only 520 KB in size

Editor: Like our server, the tool is generally used to program log 1 M. If you are not too troublesome, you can look at the manual method: 1: Delete LOG 1: detach Database Enterprise Manager-server-database-right-click-Detach database 2: delete LOG file 3: attach Database Enterprise Manager-server-database-right-click-Attach database this method generates a new LOG, only 520 KB in size

Editor: Like our server, the tool is usually used to program logs for 1 MB. If you are not bothered, you can look at the manual method:

1: Delete 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 K

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 = 77169 database

EXEC sp_attach_single_file_db @ dbname = 77169 database,

@ Physname = c: Program FilesMicrosoft SQL ServerMSSQLData77169database. mdf

2: Clear logs

Dump transaction database name WITH NO_LOG

Again:

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: do not let it grow

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

Automatically contract the log. You can also use the following statement.

Alter database name

SET AUTO_SHRINK ON

The fault recovery model is changed to simple. The statement is

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. Compress database files (if not compressed, the database files 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 is the file number. You can use this statement to query: select * from sysfiles

Dbcc shrinkfile (1)

4. To minimize log files (for SQL 7.0, 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 generates a new LOG with a size of more than 500 K.

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 = 77169 database

B. Delete log files

C. Attach

EXEC sp_attach_single_file_db @ dbname = 77169 database,

@ Physname = c: Program FilesMicrosoft SQL ServerMSSQLData77169database. 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, autoshrink, 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)

Bytes -------------------------------------------------------------------------------------------

/* -- Compress the General stored procedure of the database

Compressed log and database file size

Because the database needs to be separated

Therefore, 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, NIsProcedure) = 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) = -- backup file name. If this parameter is not specified, it is automatically backed up to the default backup directory. The backup file name is 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 information = back up the database to the SQL default 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

Open 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