This question many people have received the puzzle, the diary frequently several g

Questioning: How in should the SQLServer business diary compress and delete?

Answer: The concrete method has 3 kinds.

Method one:

The first step:

backup log database_name with no_log or backup log database_name with truncate_only

-- no_log and truncate_are only in here are the synonymy, which one carries out to be possible casually.

The second step:

1. contraction specific database all data and journal file, execution:

dbcc shrinkdatabase (database_name,[, target_percent])

-- database_name is the database name which must contract; after target_percent is the surplus usable space percentage which in the database contraction's database file wants.

2. contracts in a time specific database the data or the journal file, the execution

dbcc shrinkfile(file_id,[, target_size])

-- file_id is the document marking (ID) number which must contract, if obtains document ID, please use the FILE_ID function or searches sysfiles in the current database; target_size is the document size which wants with a megabyte expression (with integer expression). If has not assigned, dbcc shrinkfile reduces the document size to the default document size. Two dbcc both may take to bring with parameter notruncate or truncateonly, concrete meaning examination online help.

Method two:

The first step:

The backup entire database prepares the mishap first.

The second step:

After the backup had finished, carries out the following sentence in Query Analyzer:

exec sp_detach_db yourDBName, true

--Dismantle this DB in MSSQL log-on message

The third step:

In the table of contents which is at to the diary physical file deletes this journal file or emigrates this table of contents this journal file

The fourth step:

Carries out the following sentence in Query Analyzer:

exec sp_attach_single_file_db yourDBName, 'd:\mssql\data\yourDBName _data.mdf'

--Registers this DB by single document's way, if will succeed MSSQL to be automatic for this DB produces 500K the journal file.

Method three:

1. Enters the enterprise supervisor, selects the database, for instance demo

2. All duties - > separate the database

3. To database file's depositing table of contents, the MuOnline_log.LDF file delete, provides against contingencies, you may beat

4. The enterprise supervisor - > attaches the database, chooses muonline, at this time you will see journal file this item will be a fork, will be unimportant, continues, this time the database will prompt your this database not to have the diary whether to found one newly, determination and that's the end.

5. Remembered after the database attaches, the user must reset.

If, does not want it to increase:

Under SQL2000 uses:

In the database a right key - > the attribute - > the option - > the breakdown restores - the model - choice - simple model.

Or uses the SQL sentence:

alter database database set recovery simple