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

SQLServer how to empty the log statement, 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.

Sql2000 log emptying

You can make the jb51.ldf file very small, make it easy to back up the database, etc., and execute it in the sqlserver query analyzer. The copy code is as follows: DUMP TRANSACTION [jb51] WITH NO_LOGBACKUP LOG [jb51] WITH NO_LOGDBCC SHRINKDATABASE ([jb51])

1. Clear the log: DUMP TRANSACTION [databasename] WITH NO_LOG 2. Truncate the transaction log: BACKUPLOG [databasename] WITH NO_LOG 3. Shrink database: DBCC SHRINKDATABASE ([databasename]) Note: database name is best added []

SQLServer database log cleanup clears sqlserver2005 log

Sometimes when the system runs for a long time, when we restore the backup database, we find that the data files and log files in the database become very large, especially the log files. Now let's show you how to clean up the SQLServer database logs; there are two ways to do this:

Method 1: manually clear the sqlserver2005 log

1. Right-click the database that clears the log, such as "TestDB", and click [New query (Q)] 2. Enter the following SQL statement, where "TestDB" is the database name DUMP TRANSACTION TestDB WITH NO_LOG3. Execute the SQL and continue with the following 4. Right-click the database node and click [Task (T)]-> [shrink (S)]-> [File (F)] 5. In the pop-up shrink Files dialog box, select Files of Type (T) as Log, and shrink Operations select reorganize pages before releasing unused space 6. Enter the value of the minimum size prompted later in the "shrink the file to (K)" text box, and click OK.

Method 2: use the tool software SqlServer log clearance expert 3.0, which can clear the database logs of various versions from SqlServer 6.5 to SqlServer 2005; the use method is very simple; SqlServer log clearance expert green version V3.0 download address:

Download address / / www.xuebuyuan.com/softs/21840.html

Method 1 is relatively troublesome to operate, but the size of the log can be customized. after cleaning the log, the corresponding database data files will become smaller and the data will not be lost; method 2 is more convenient to operate. You can clean up the log files in the database to 1m size

SQLServer database log cleanup clears sqlserver2008 log

SQL2008's shrinking log because SQL2008 optimizes file and log management, the following statement can be run in SQL2005 but has been cancelled in SQL2008: (SQL2005)

The copy code is as follows: BackupLog DNName with no_loggodumptransaction DNName with no_loggoUSE DNName DBCC SHRINKFILE (2) Go

(SQL2008): clearing logs in SQL2008 must be done in simple mode, and then return to full mode after the cleanup action is completed.

Option 1: full command mode

The copy code is as follows: USE [master] GO ALTERDATABASE DNName SET RECOVERY SIMPLE WITH NO_WAIT GO ALTERDATABASE DNName SET RECOVERY SIMPLE-- simple mode GO USE DNName GO DBCC SHRINKFILE (NumberNameLog', 11, TRUNCATEONLY) GO USE [master] GO ALTERDATABASE DNName SET RECOVERY FULLWITH NO_WAIT GO ALTERDATABASE DNName SET RECOVERY FULL-- restore to full mode GO

Scheme 2: partial command mode + task-shrink-file (single database)

ALTERDATABASE DNName SET RECOVERY SIMPLE-simple mode GO

Right-click-Task-shrink-File-make sure that only 1m is retained in the log of the database

ALTERDATABASE DNName SET RECOVERY FULL-revert to full mode

GO

Advantages: this clean log takes a short time to run. The 90GB log can be cleared in about minutes, and a full backup can be completed in minutes after completion. Disadvantages: however, it is best not to use this action often, because its operation will bring system fragments. In normal state, the backup of LOG and DIFF can truncate the log. The appropriate environment for this statement: when the log file of the system is abnormally large or if the backup time of the LOG is too long, it may affect production.

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.