SQL Server log emptying statement (sql2000,sql2005,sql2008) _mssql

SQL Server Log Vacuuming method

Perform the following three steps sequentially in Query Analyzer, where databasename is your database filename

sql2000 Log Empty

You can make jb51.ldf files very small, make it easy to back up databases, and so on, and execute them in SQL Server Query Analyzer.

Copy Code code as follows:

DUMP TRANSACTION [jb51] with no_log
BACKUP LOG [jb51] with no_log
DBCC shrinkdatabase ([jb51])

1. Empty log:

DUMP TRANSACTION [DatabaseName] with no_log

2. Truncate the transaction log:

Backuplog [DatabaseName] with no_log

3. Shrink the database:

DBCC shrinkdatabase ([databasename])

Note: Database name Chen best Add []

SQL Server database log cleanup purge sqlserver2005 log

Sometimes when the system is running for a long time, we restore the backup database when we found that the database data files and log files become very large, especially log files. Now I'm going to show you how to clean up SQL Server database logs, and there are two ways to do this:

Method One: Manually clear the sqlserver2005 log

1. Right-click in the database to clear the log, such as "TestDB", clicks [New Query (Q)]
2. Enter the following SQL statement, where "TestDB" is the name of the database
DUMP TRANSACTION TestDB with no_log
3. Execute the SQL, and continue with the following operations successfully
4. Right key to the database node, click [Task (T)]-> [Shrink (S)]-> [File (F)]
5. In the Shrink file dialog box that pops up, select file Type (T) as log, and the Shrink action to select the Rearrange page (O) before freeing unused space
6. In the "Shrink file to (K)" text box, enter the minimum size of the following prompts, click [OK].

Method Two: Use the tool software SQL Server log to clear Expert 3.0, the various versions of SQL Server 6.5 to SQL Server 2005 of the database log cleanup, its use is very simple; SQL Server log cleanup expert green version V3.0 download address:

Download Address http://www.jb51.net/softs/21840.html

Method One is relatively troublesome, but can be customized to the size of the log, after cleaning up the log of its corresponding database data files will become smaller, the data will not be lost; The method is convenient, the log files in the database can be cleaned to 1M size;

SQL Server database log cleanup purge sqlserver2008 log

SQL2008 Shrink Log
Because SQL2008 is optimized for file and log management, the following statements can be run in SQL2005 but have been canceled in SQL2008:
(SQL2005)

Copy Code code as follows:

Backuplog Dnname with No_log
Go
Dumptransaction Dnname with No_log
Go
Use Dnname
DBCC Shrinkfile (2)
Go

(SQL2008):
Clearing the log in SQL2008 must be done in simple mode, until the purge action is completed and then back to full mode.

Scenario One: Full command mode

Copy Code code as follows:

Use[master]
Go
Alterdatabase dnname SET RECOVERY simple with no_wait
Go
Alterdatabase dnname SET RECOVERY Simplicity--Simple mode
Go
Use Dnname
Go
DBCC shrinkfile (N ' Dnname_log ', one, truncateonly)
Go
Use[master]
Go
Alterdatabase dnname SET RECOVERY fullwith no_wait
Go
Alterdatabase dnname SET RECOVERY Full--Revert to complete mode
Go

Scenario Two: Partial command mode + task-shrink-file (single database)

Alterdatabase dnname SET RECOVERY Simplicity--Simple mode
Go

Right-task-shrink-file-OK log down database only retains 1M

Alterdatabase dnname SET RECOVERY Full--Revert to complete mode

Go

Advantages: This purge log is running for a short period of time, 90GB of log in minutes or so can be cleared, done after a full backup in minutes
can be completed.
Disadvantage: This action is best not to be used frequently, because its operation will bring system fragmentation. Log and diff backups can be truncated in normal state.
The appropriate environment used by this statement: use when the system's log files are abnormally large or if the backup log time is too long to be affected by production.