MSSQL database log purging method

Teach you how to clear SQL logs.

1 Open Query Analyzer and enter commands.

dump transaction database name with no log

2.Then open Enterprise Manager -- right-click the database you want to compress -- All Tasks -- shrink the database -- shrink the file -- select the log file -- select Shrink to xxm in the shrink mode, here will give a minimum number of m that can be shrunk to, enter this number directly, OK.

There are two ways to clear the log:

1.Automatic removal method.

The trunc log on chkpt option is open to the database system, which automatically clears logs at regular intervals. The advantage of this method is that it does not require manual intervention, it is automatically executed by SQL Server, and it is burned and generally does not cause vertical cracks and false log overflow. The disadvantage is that only the logs are cleared and no backups are made.

2.Manual removal method.

Run the mp transaction command to clear the log. The following two commands can clear the logs:

mp transaction with truncate_only

mp transaction with no_log

In general, the "mp transaction with trancate only" command can be used to delete the inactive part of the transaction log, and when this command is written into the transaction log, the necessary concurrency check source difference should be done. Sybase provides "MP transaction with no log" to handle some very urgent cases, and it is very dangerous to use this command, SQL Server will pop up a warning message. To ensure database consistency as much as possible, you should use it as a "last resort".

If you want to back up logs, run the mp transaction database name to mpdevice command.

There is no impact on the database after purging.

SQL Server 2000 is a quick way to clear log files.

SQL Server 2000 database logs are too large!

How do I clean SQL Server 2000 logs?

How do I compress SQL Server 2000 logs?

How do I make SQL Server 2000 logs smaller?

As far as I know, there are many, many ways, and there are 3 types of solutions

1. With a few clicks of the mouse, you can use the enterprise manager in the SQL Server 2000 management tool, which will be explained in detail later;

2. Use SQL statements, which can be realized by the query analyzer in SQL Server 2000 management tools, and senior people like to use this method;

3. Use a third-party tool to implement, the principle, I think it should be implemented with SQL statements, this method is good, simple and easy to use, there is no shortage of high-quality tools on the Internet, and I will introduce one later.

1. Now let's describe in detail how to clean SQL 2000 logs with Enterprise Manager:

2. Right-click on the database to be processed 》All tasks 》Shrink database 》Do not move anything, the default first one is 0%, the other two are not selected, click OK 》If the database log file is greater than 1M before, then take a look now, is it only 1M.

3. After the operation, press the first step, put the "fault restoration", select "completely", the legend SQL Server 2000 has an automatic restore function, saying that if the data loss caused by illegal shutdown and other factors can be automatically rolled back, in addition, you can use the program to achieve the operation rollback, so it is best to make the fault restoration is "complete".

4. In this way, "SQL Server 2000 database logs are too large!" How to manage, clear, make smaller, compress it", become smaller, compressed, as for how to manage, look at it yourself, I usually only use these, and I will write it if I have in-depth research.

2. Now we introduce the use of a third-party SQL Server log cleaning tool to clean logs, which is simple and efficient to use, and this method is recommended.

I like to use sqlserver log cleaner to achieve this feature. The interface is as follows:

The database will generally list the database name of the entire server, you need to choose the one that can be managed by your account, my db iszip account can manage iszip here, after selecting, click the "clear log" button, and then the log of sql server 2000 will be cleaned up, at this time you go to the server to see that your database log occupies only 1m.

--1.Clear Logs:

dump transaction databasename with no_log

2.Truncate the transaction log:

backup log databasename with no_log

3.Shrink the database:

dbcc shrinkdatabase(databasename)

In general, it doesn't have any effect...

Logs are mainly used for differential recovery, if you add a new document today, and then you do clear the log, and then your database is restored to yesterday, then you want to get back the new data, the log is not recoverable... If the log is not lost, it can be retrieved.

Use SQL to delete logs.