SQL SERVER database log clearing text tutorial

How can I clear database logs without stopping? Here, we take a database SQL SERVER 2005 as an example. When the log file reaches more than 100 GB, it cannot be cleaned up and many methods have been taken:

For exampleSqlServer log clearing expertUsed to clear database logs of SQL Server 7, SQL Server 2000, and SQL Server 2005.

This tool can be cleaned once it is cleared. So that you no longer have to worry about the database log files exceeding several hundred megabytes or GB level.

The second method is to use a relatively dead method. Use separation, delete the log file, and then append it to generate a new log file.

Remember to back up the SQL before performing the operation.

The first step is to open the Enterprise Manager.

Before separation, it is best to shut down the programs that are connecting to the database such as IIS. Otherwise, they will not be able to be separated for a long time.

You can also choose to delete the link so that the separation may be faster.

Locate the hard disk where the database is located.

We can back up qq2.mdf for the sake of security, and then re-name the file qq2_log.ldf (there is no place to back up files of GB ).

Then we can append the database.

Append to the mdf file. Note: The following message indicates that the. ldf file cannot be found.

Ignore him. Select. ldf and delete it.

Then confirm that the restoration is successful. The system automatically generates a new ldf file 504KB

We recommend that you first use the tool above to clear the log. If it cannot be clear, you can use this method to delete the log.

Finally, I would like to remind you to pay attention to backup again !!

SQL2008 contraction log
Because SQL2008 optimizes file and log management, the following statements can be run in SQL2005 but have been canceled in SQL2008:
(SQL2005)
Backup Log DNName with no_log
Go
Dump transaction DNName with no_log
Go
USE DNName
Dbcc shrinkfile (2)
Go
--------------------------------------------------------------
(SQL2008 ):
Log cleanup in SQL2008 must be performed in simple mode. After the cleanup is completed, the log is called back to full mode.
USE [master]
GO
Alter database DNName set recovery simple with NO_WAIT
GO
Alter database DNName set recovery simple -- SIMPLE mode
GO
USE DNName
GO
Dbcc shrinkfile (n'dnname _ log', 11, TRUNCATEONLY)
GO
USE [master]
GO

Alter database DNName set recovery full with NO_WAIT

GO

Alter database DNName set recovery full -- restore to FULL Mode

GO

Advantage: it takes a short time to clear logs. A 90 GB log can be cleared in about minutes. After the log is cleared, make a full backup within minutes.
.
Disadvantage: however, it is best not to use this action frequently because its operation will cause system fragmentation. In normal state, logs and DIFF backups can be truncated.
The appropriate environment used by this statement: when the system LOG file is abnormally increased or the backup LOG time is too long, it may affect the production.