Compressing Database Log Files

Compressed logsAnd DatabaseFile Size

/* -- Pay special attention

Follow these steps. Do not follow these steps.
Otherwise, your Database.


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

-- All the following database names refer to DatabaseLibrary name

1. Clear Logs
Dump transaction database name WITH NO_LOG

2. truncation transactions Logs:
Backup log library name WITH NO_LOG

3. Shrink DatabaseFile (if not Compression, DatabaseThe file will not be reduced.
Enterprise Manager -- Right-click CompressionOf Database-- All tasks -- contract Database-- Shrink a file
-- Select LogsFile -- 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
-- Shrink Database
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 reduction LogsFile (for SQL 7.0, this step can only be performed in the query analyzer)
A. Separation Database:
Enterprise Manager -- server -- Database-- Right-click -- separate Database

B. Delete LOG files in my computer

C. Additional Database:
Enterprise Manager -- server -- Database-- Right-click -- attach Database

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. Delete LogsFile

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-click Database-- Property -- option -- select "auto contract"

-- SQL statement setting method:
EXEC sp_dboption 'database name', 'autowrite', 'true'

6. If you want to prevent it later LogsGrowing too much
Enterprise Manager -- server -- Right-click Database-- Attribute -- transaction Logs
-- Limit file growth to xM (x is the maximum data file size you allow)