How to clear database logs

ClearLogs
Dump transaction database name WITH NO_LOG

Truncation transactionsLogs
BACKUP LOGDatabaseName WITH NO_LOG

ShrinkDatabase
Dbcc shrinkdatabase (DatabaseName)

 

-- CompressionLogsAndDatabaseFile Size

/* -- Pay special attention

Follow these steps. Do not follow these steps.
Otherwise, yourDatabase.

Generally, steps 4 and 6 are not recommended.
Step 1 is insecure and may be damaged.DatabaseOr data loss
Step 2: IfLogsIf the limit is reachedDatabaseProcessing will fail.LogsCan be restored.
--*/

-- All the following database names refer toDatabaseLibrary name

1. ClearLogs
Dump transaction database name WITH NO_LOG

2. truncation transactionsLogs:
Backup log library name WITH NO_LOG

3. ShrinkDatabaseFile (if not compressed,DatabaseThe file will not be reduced.
Enterprise Manager-right-clickDatabase-- All tasks -- contractDatabase-- Shrink a file
-- SelectLogsFile -- select to shrink to XXM in the contraction mode. Here, a minimum number of MB allowed to be shrunk is displayed. Enter this number directly and click OK.
-- Select data file -- select to shrink to XXM in the contraction mode. Here, a minimum number of MB allowed to be shrunk is displayed. Enter this number directly and click OK.

You can also use SQL statements to complete
-- ShrinkDatabase
Dbcc shrinkdatabase (database name)
 
-- Contract the specified data file. 1 is the file number. You can use this statement to query: select * from sysfiles
Dbcc shrinkfile (1)

4. To maximize the reductionLogsFile (for SQL 7.0, this step can only be performed in the query analyzer)
A. SeparationDatabase:
Enterprise Manager -- server --Database-- Right-click -- separateDatabase

B. Delete LOG files in my computer

C. AdditionalDatabase:
Enterprise Manager -- server --Database-- Right-click -- attachDatabase

This method generates a new LOG with a size of more than 500 K.

Or use the code:
The following example separates pubs and attaches a file in pubs to the current server.
 
A. Separation
EXEC sp_detach_db @ dbname = 'database name'

B. DeleteLogsFile
 
C. Attach
EXEC sp_attach_single_file_db @ dbname = 'database name ',
@ Physname = 'C: \ Program Files \ Microsoft SQL Server \ MSSQL \ Data \ database name. mdf'

5. In order to automatically contract in the future, make the following settings:
Enterprise Manager -- server -- Right-clickDatabase-- Property -- option -- select "auto contract"
 
-- SQL statement setting method:
EXEC sp_dboption 'database name', 'autowrite', 'true'

6. If you want to prevent it laterLogsGrowing too much
Enterprise Manager -- server -- Right-clickDatabase-- Attribute -- transactionLogs
-- Limit file growth to xM (x is the maximum data file size you allow)

-- SQL statement settings:
Alter database name modify file (name = logical file name, maxsize = 20)