SQL Server: How to delete log files and empty logs

• 1. Delete Log
• 2. Clear the Log
• 3. Shrinking database files
• 4. truncating the transaction log

The performance of the database is the DBA need to focus on, the increase of log files seriously affect the performance of the database, this article will introduce you to the SQL Server delete log files for your reference, I hope to help you.

In the process of using the database, the log files are increased, which degrades the performance of the database and consumes a lot of disk space. The SQL Server database has a log file, and the log file records the user's actions on the database modifications. You can clear the database log by directly deleting the log file and emptying the logs.

1. Delete LOG1.1 separate database

Before separating the database, make sure to do a full backup of the database, select the database-right--task--separate.

Tick Delete connection

The detached database will not be visible to the database list after it is detached.

1.2 Delete log file 1.3 additional database

The log file will not be found when attached.

Remove the LDF file for database information information:

After attaching the database, a new log file is generated and the new log file size is 504K.

1.4 You can also complete the above operation by command

Use master;exec sp_detach_db @dbname = ' database name '; exec sp_attach_single_file_db @dbname = ' database name ', @physname = ' D:\Program Files \microsoft SQL Server\mssql10. Sql2008\mssql\data\testdb.mdf '

sp_detach_db: Separating the database;

sp_attach_single_file_db: Attaching a single database file, attaching only the data file in the above code, without attaching the log file, meaning to delete the log file.

This command is supported in SQL Server 2005 and 2000, and SQL Server 2008 does not support this command.

2. Clear the Log

DUMP TRANSACTION database name with NO_LOG

3. Shrinking database files

DBCC shrinkfile (' Testdb_log ', 1)

"Data shrinkage" in SQL Server

4. truncating the transaction log

BACKUP LOG TestDB with no_log

The command is also not supported in SQL Server 2008, which can be used in SQL Server 2005 and 2000.

SQL Server: How to delete log files and empty logs