Shulou(Shulou.com)05/31 Report--

SQL Server how to delete large-capacity logs, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

1: delete LOG

1: detach Enterprise Manager-> Server-> Database-> right-click-> detach Database

2: delete the LOG file

3: attach Database Enterprise Manager-> Server-> Database-> right-click-> attach Database

This method generates a new LOG with a size of only 520K.

Then set this database to shrink automatically

Or use the code:

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

EXEC sp_detach_db @ dbname = 77169database

EXEC sp_attach_single_file_db @ dbname = 77169database

@ physname = c:Program FilesMicrosoft SQL ServerMSSQLData77169database.mdf

2: clear the log

DUMP TRANSACTION library name WITH NO_LOG

Again:

Enterprise Manager-- right-click the database you want to compress-- all tasks-- shrink the database-- shrink the file-- Select the log file-- choose to shrink to XXM in the shrink mode. Here is a minimum number of M allowed to shrink. Enter this number directly and confirm it.

3: don't let it grow.

Enterprise Manager-> Server-> Database-> Properties-> transaction Log-> limit file growth to 2m

To automatically shrink the log, you can also use the following statement

ALTER DATABASE database name

SET AUTO_SHRINK ON

The fault recovery model is changed to simple, and the statement is

USE MASTER

GO

ALTER DATABASE database name SET RECOVERY SIMPLE

GO

Truncate the transaction log:

BACKUP LOG {database_name | @ database_name_var}

{

[WITH

{NO_LOG | TRUNCATE_ONLY}]

}

-- compress log and database file size

/ *-pay special attention

Please follow the steps, do not do the previous steps, please do not do the following steps

Otherwise, it may damage your database.

-- * /

1. Clear the log

DUMP TRANSACTION library name WITH NO_LOG

two。 Truncate the transaction log:

BACKUP LOG database name WITH NO_LOG

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

Enterprise Manager-right-click the database you want to compress-all tasks-shrink database-shrink files

-- Select the log file-- choose to shrink to XXM in the contraction mode. Here, you will give a minimum number of M allowed to shrink to. Enter this number directly and confirm it.

-- Select the data file-- choose to shrink to XXM in the contraction mode. Here, you will give a minimum M number that is allowed to shrink. Enter this number directly and confirm it.

You can also use SQL statements to do this.

-- shrink the database

DBCC SHRINKDATABASE (customer profile)

-- shrink the specified data file. 1 is the file number. You can query it through this statement: select * from sysfiles

DBCC SHRINKFILE (1)

4. To maximize the reduction of log files (in the case of sql 7. 0, this can only be done in the query Analyzer)

a. Detach the database:

Enterprise manager-- server-- database-- right-- detach database

b. Delete the LOG file from my computer

c. Attach database:

Enterprise Manager-Server-Database-right-attach Database

This method will generate a new LOG with a size of only more than 500K.

Or use the code:

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

a. Separate

EXEC sp_detach_db @ dbname = 77169database

b. Delete log files

c. Re-attach

EXEC sp_attach_single_file_db @ dbname = 77169database

@ physname = c:Program FilesMicrosoft SQL ServerMSSQLData77169database.mdf

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

Enterprise Manager-Server-right database-Properties-options-Select "Auto shrink"

-- SQL statement setting method:

EXEC sp_dboption database name, autoshrink, TRUE

6. If you want to keep its log from growing too big in the future,

Enterprise Manager-Server-right Database-Properties-transaction Log

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

-- how to set the SQL statement:

Alter database database name modify file (name= logical file name, maxsize=20)

-

/ *-General stored procedure for compressing database

Compress log and database file size

Because you have to separate the database.

So stored procedures cannot be created in a compressed database / *

-- 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, 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 database may be damaged during the step of detaching the log, you can choose whether or not to automate the database

@ bkfname nvarchar =-- the file name of the backup. If not specified, the backup is automatically backed up to the default backup directory. The backup file name is: database name + date and time.

As

-1. Clear the log

Exec (DUMP TRANSACTION [+ @ dbname+] WITH NO_LOG)

-- 2. Truncate the transaction log:

Exec (BACKUP LOG [+ @ dbname+] WITH NO_LOG)

-3. Shrink 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)

The following steps are dangerous, and you can choose whether they should be taken or not.

-5. Detach database

If @ bkdatabase=1

Begin

If isnull (@ bkfname,) =

Set @ bkfname=@dbname+_+convert (varchar,getdate ())

+ replace (convert (varchar,getdate (), 108),:,

Select prompt = backup database to SQL default backup directory, backup file name: + @ bkfname

Exec (backup database [+ @ dbname+] to)

End

-- carry out separation treatment

Create table # t (fname nvarchar, type int)

Exec (insert into # t select filename,type=status&0x40 from [+ @ dbname+].. sysfiles)

Exec (sp_detach_db)

-- Delete log files

Declare @ fname nvarchar, @ 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_cmdshell @ s recoverable 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 @ fname (@ fname) +

Fetch next from tb into @ fname

End

Close tb

Deallocate tb

Exec (sp_attach_single_file_db)

Go

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.