SQL Server database compression and Database Log (LDF) compression methods

Access

The operation is very simple. The specific steps are as follows: Open your mdb database, tools> Database Utilities> compress and restore the database (c )...

SQL Server

In general, the contraction of the SQL database does not greatly reduce the size of the database. Its main function is to shrink the log size. This operation should be performed regularly to avoid excessive database logs.
1. Set database mode to simple mode: Open the SQL Enterprise Manager, in the root directory of the console, choose Microsoft sqlserver --> sqlserver group --> double-click to open your server --> double-click to open the database directory --> select your database name (such as forum database Forum) --> then right-click and select Properties --> Select Options --> select "simple" in the fault recovery mode, and then click OK to save
2. Right-click the current database to view the shrinking database in all tasks. Generally, the default settings in the database do not need to be adjusted. Click OK directly.
3. After shrinking the database, we recommend that you set your database attributes to the standard mode. The operation method is the same as the first one, because logs are often an important basis for restoring the database in case of exceptions.
However, the following statement can be executed on the server in the query Analyzer:CopyCodeThe Code is as follows: Dump transaction [jc8] With no_log
Backup log [jc8] With no_log
DBCC shrinkdatabase ([jc8])

Jc8 is the database name to clean up jc8.ldf files.

SQL Server transaction log compression and Deletion

Q: How can I compress and delete transaction logs in sqlserver?
A: There are three methods.
Method 1:
Step 1:
Backup log database_name with no_log
Or backup log database_name with truncate_only
-- No_log and truncate_only are synonymous here. You can execute any sentence at will.
Step 2:
1. compress all data and log files of a specific database and execute:
DBCC shrinkdatabase (database_name, [, target_percent])
-- Database_name is the name of the database to be shrunk; target_percent is the percentage of available space in the database file after the database is shrunk.
2. compress the data or log files in a specific database and execute
DBCC shrinkfile (file_id, [, target_size])
-- File_id is the ID of the file to be shrunk. To obtain the file ID, use the file_id function or search sysfiles in the current database; target_size indicates the size of the desired file in megabytes (expressed in integers ). If not specified, DBCC shrinkfile reduces the file size to the default file size. Both DBCC can contain the notruncate or truncateonly parameter. For more information, see online help.
Method 2:
Step 1:
Back up the entire database for emergency purposes.
Step 2:
After the backup is complete, run the following statement in query Analyzer:
Exec sp_detach_db yourdbname, true
-- Remove the registration information of this dB in MSSQL
Step 3:
Delete the log file or remove the log file from the directory where the log physical file is located.
Step 4:
Execute the following statement in query Analyzer:
Exec sp_attach_single_file_db yourdbname ,'
D: \ MSSQL \ data \ yourdbname_data.mdf'
-- Register the database as a single file. If yes, MSSQL automatically generates a K log file for the database.
Method 3:
1. Go to the Enterprise Manager and select the database, such as demo.
2. All tasks-> database Separation
3. Go to the database file storage directory and delete the muonline_log.ldf file. Just in case, you can copy it out.
4. enterprise Manager-> attach a database and select muonline. At this time, you will see that the log file is a cross. It doesn't matter. Continue, at this time, the database will prompt you whether to create a new database without logs. OK.
5. Remember to reset the settings after the database is reappended.
If you do not want it to become larger later:
Use in SQL2000:
Right-click the database and choose Properties> Options> fault recovery> model> select simple model.
Or use an SQL statement:
Alter database name set recovery simple