суббота, 20 сентября 2008 г.

Truncate Log in SQL Server

to truncate log using command 'backup log .. with truncate_only' is not enough.

to truncate log you need:
1. execute command
backup log YOUR_DB_NAME with TRUNCATE_ONLY

this command removes the inactive part of the log without making a backup copy of it and truncates the log. This option frees space.
NO_LOG and TRUNCATE_ONLY are synonyms.


2. execute command
dbcc shrinkfile (YOUR_DB_NAME, 2, NOTRUNCATE)


where '2' defines that we need log file to have size 2Mb

NOTRUNCATE - Causes the freed file space to be retained in the files.
When NOTRUNCATE is specified along with target_size, the space freed is not released to the operating system. The only effect of the DBCC SHRINKFILE is to relocate used pages from above the target_size line to the front of the file. When NOTRUNCATE is not specified, all freed file space is returned to the operating system.


3. execute command
dbcc shrinkfile (YOUR_DB_NAME, 2, truncateonly)


TRUNCATEONLY - Causes any unused space in the files to be released to the operating system and shrinks the file to the last allocated extent, reducing the file size without moving any data. No attempt is made to relocate rows to unallocated pages. target_size is ignored when TRUNCATEONLY is used.



DBCC commands are perfomed not-immediately and you may not see the changes at once.

if that commands do not help - repeat steps 1,2,3 again.

References:
Books Online SQL Server 2000

Комментариев нет: