Methods and steps for compressing database log files

How to compress database log files

There are three specific methods.

method one:

first step:

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.

Step 2:

1. Shrink all data and log files of a specific database, execute:

dbcc shrinkdatabase (database_name,[,target_percent])

-- database_name is the name of the database to be shrunk; target_percent is the percentage of remaining free space required in the database file after the database is shrunk.

2. Shrink the data or log files in a specific database at a time, execute

dbcc shrinkfile(file_id,[,target_size])

-- file_id is the identification (ID) number 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 is the desired file size in megabytes (using expressed as an integer). If not specified, dbcc shrinkfile reduces the file size to the default file size. Both dbcc can have the parameter notruncate or truncateonly. For details, see the online help.

Method Two:

first step:

Back up the entire database first to prepare for emergencies.

Step 2:

After the backup is completed, execute the following statement in Query Analyzer:

exec sp_detach_db yourDBName,true

--Uninstall the registration information of this DB in MSSQL

third step:

Go to the directory where the physical log file is located to delete the log file or move the log file out of the directory

the fourth step:

Execute the following statement in Query Analyzer:

exec sp_attach_single_file_db yourDBName,'

d:\mssql\data\yourDBName_data.mdf '

--Register the DB as a single file. If successful, MSSQL will automatically generate a 500K log file for this DB.

Method 3:

1. Enter the Enterprise Manager and select the database, such as demo

2. All tasks->Separate database

3. Go to the directory where the database file is stored and delete the MuOnline_log.LDF file. Just in case, you can copy it out

4. Enterprise Manager -> Attach database, select muonline. At this time, you will see that the log file item is a cross. It does not matter, continue. At this time, the database will prompt you whether to create a new one if the database has no logs. That’s it for sure.

5. Remember that the user needs to reset it after the database is reattached.

If you don’t want it to get bigger in the future:

Used under SQL2000:

Right-click on the database->Properties->Options->Failure Recovery-Model-Select-Simple Model.

Or use SQL statement:

alter database database name set recovery simple

What to do if the SQL database is too large

--1. Daily database compression

--Compress log and database file size

/*--pay attention

Please follow the steps. If you have not completed the previous steps, please do not do the following steps

Otherwise it may damage your database.

--*/

1. Clear the log

DUMP TRANSACTION library name WITH NO_LOG

2. Truncate transaction log:

BACKUP LOG database name WITH NO_LOG

3. Shrink the database file (if not compressed, the database file will not be reduced

Enterprise Manager--right-click the database you want to compress--All tasks--Shrink database--Shrink files

--Select the log file--In the shrink mode, select shrink to XXM. A minimum number of M allowed to be shrunk will be given here. Enter this number directly and confirm.

--Select the data file--In the shrink mode, select shrink to XXM. There will be a minimum number of M allowed to be shrunk. Enter this number directly and confirm.

You can also use SQL statements to complete

--Shrink database

DBCC SHRINKDATABASE(Customer Data)

--Shrink the specified data file, 1 is the file number, which can be queried through this statement: select * from sysfiles

DBCC SHRINKFILE(1)

4. In order to minimize the log file size (if it is SQL 7.0, this step can only be performed in the query analyzer)

a. Separate database:

Enterprise Manager--Server--Database--Right-click--Detach Database

b. Delete LOG files on my computer

c. Additional database:

Enterprise Manager--Server--Database--Right-click--Attach Database

This method will generate a new LOG, the size is only more than 500K

or use code:

The following example detaches pubs and then appends a file in pubs to the current server.

a.Separation

EXEC sp_detach_db @dbname = 'pubs'

b. Delete log files

c.Add

EXEC sp_attach_single_file_db @dbname = 'pubs',

@physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'

5. In order to automatically shrink in the future, make the following settings:

Enterprise Manager--Server--right-click the database--Properties--Options--Select "Auto Shrink"

--SQL statement setting method:

EXEC sp_dboption 'database name', 'autoshrink', 'TRUE'

6. If you want to prevent the log from growing too large in the future

Enterprise Manager--Server--Right-click Database--Properties--Transaction Log

--Limit file growth to xM (x is the maximum data file size you allow)

--SQL statement setting method:

alter database database name modify file(name=logical file name, maxsize=20)

The above is the detailed content of Methods and steps for compressing database log files. For more information, please follow other related articles on the PHP Chinese website!