How to clear logs in SQL Server 2000

How to manually clear logs

I. Here we will describe in detail how to use the Enterprise Manager to clear SQL 2000 logs:

1. Open the Enterprise Manager, right-click the database to be processed, choose Properties> Options> fault recovery, and choose simple> OK. As shown in the following figure:

2. Right-click the database to be processed -- all tasks -- contract the database -- and there will be no changes. The first task is 0% by default, and the other two are not selected, click "OK". If your database log file is larger than 1 MB, check whether it is only 1 MB.

3. After the operation, take the first step and select "completely" for "fault recovery". It is said that SQL Server 2000 has the automatic restoration function, it is said that if data is lost due to factors such as illegal shutdown, data can be automatically rolled back. In addition, you can use a program to perform operation rollback, so it is best to make fault recovery "completely ".

How to automatically clear logs

Open the Enterprise Manager, choose "manage"> "SQL server proxy service"> "job", right-click the window on the right, and select "New Job ". On the "General" tab, enter the job name and description. It is recommended that you use sa or the default management account as the owner.

Go to the "steps" tab, create a job step, enter the step name, type as script, and database as the database for which logs need to be cleared. In the following command, enter the following command:

SQL code

The code is as follows:	Copy code
1. Dump transaction database name WITH NO_LOG Dbcc shrinkfile (database log file name, 1) Or use 2. Dump transaction DBName WITH NO_LOG Backup log DBName WITH NO_LOG Dbcc shrinkdatabase (DBName) Dbcc shrinkfile (1)

Shrink logs using SQL

Copy the code to the query analyzer, modify the three parameters (database name, log file name, and target log file size), and run

The code is as follows:

Copy code

SET NOCOUNT ON DECLARE @ LogicalFileName sysname, @ MaxMinutes INT, @ NewSize INT USE tablename -- Name of the database to be operated SELECT @ LogicalFileName = 'tablename _ log', -- log file name @ MaxMinutes = 10, -- Limit on time allowed to wrap log. @ NewSize = 1 -- the size of the log file you want to set (M) -- Setup/initialize DECLARE @ OriginalSize int SELECT @ OriginalSize = size FROM sysfiles WHERE name = @ LogicalFileName SELECT 'original Size of '+ db_name () + 'log is' + CONVERT (VARCHAR (30), @ OriginalSize) + '8 K pages or '+ CONVERT (VARCHAR (30), (@ OriginalSize * 8/1024) + 'mb' FROM sysfiles WHERE name = @ LogicalFileName Create table DummyTrans (DummyColumn char (8000) not null) DECLARE @ Counter INT, @ StartTime DATETIME, @ TruncLog VARCHAR (255) SELECT @ StartTime = GETDATE (), @ TruncLog = 'backup log' + db_name () + 'WITH TRUNCATE_ONLY' Dbcc shrinkfile (@ LogicalFileName, @ NewSize) EXEC (@ TruncLog) -- Wrap the log if necessary. WHILE @ MaxMinutes> DATEDIFF (mi, @ StartTime, GETDATE () -- time has not expired AND @ OriginalSize = (SELECT size FROM sysfiles WHERE name = @ LogicalFileName) AND (@ OriginalSize * 8/1024)> @ NewSize BEGIN -- Outer loop. SELECT @ Counter = 0 WHILE (@ Counter <@ OriginalSize/16) AND (@ counter< 50000 )) BEGIN -- update INSERT DummyTrans VALUES ('fill log ') DELETE DummyTrans SELECT @ Counter = @ Counter + 1 END EXEC (@ TruncLog) END SELECT 'final Size of '+ db_name () + 'log is' + CONVERT (VARCHAR (30), size) + '8 K pages or '+ CONVERT (VARCHAR (30), (size * 8/1024) + 'mb' FROM sysfiles WHERE name = @ LogicalFileName Drop table DummyTrans SET NOCOUNT OFF