×

Loading...
Ad by
  • 推荐 OXIO 加拿大高速网络,最低月费仅$40. 使用推荐码 RCR37MB 可获得一个月的免费服务
Ad by
  • 推荐 OXIO 加拿大高速网络,最低月费仅$40. 使用推荐码 RCR37MB 可获得一个月的免费服务

here.

本文发表在 rolia.net 枫下论坛INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
View products that this article applies to.
This article was previously published under Q272318
SUMMARY
Shrinking the log in SQL Server 2000 is no longer a deferred operation. A shrink operation attempts to shrink the file immediately. However, in some circumstances it may be necessary to perform additional actions before the log file is shrunk to the desired size.
MORE INFORMATION
When DBCC SHRINKFILE is run, SQL Server 2000 shrinks the log file by removing as many virtual log files as it can to attempt to reach the target size. If the target file size is not reached, SQL Server places dummy log entries in the last virtual log file until the virtual log is filled and moves the head of the log to the beginning of the file. The following actions are then required to complete the shrinking of the transaction log:
You must run a BACKUP LOG statement to free up space by removing the inactive portion of the log.
You must run DBCC SHRINKFILE again with the desired target size until the log file shrinks to the target size.
The following example demonstrates this with the pubs database and attempts to shrink the pubs_log file to 2 MB:
Run this code:

DBCC SHRINKFILE(pubs_log, 2)

NOTE: If the target size is not reached, proceed to the next step.
Run this code if you want to truncate the transaction log and not keep a backup of the transaction log. Truncate_only invalidates your transaction log backup sequence. Take a full backup of your database after you perform backup log with truncate_only:

BACKUP LOG pubs WITH TRUNCATE_ONLY
-or-

Run this code if you want to keep a backup of your transaction log and keep your transaction log backup sequence intact. See SQL Server Books Online topic "BACKUP" for more information:
BACKUP LOG pubs TO pubslogbackup
Run this code:
DBCC SHRINKFILE(pubs_log,2)

The transaction log has now been shrunk to the target size.
REFERENCES
For more information, see the Shrinking the Transaction Log and DBCC SHRINKFILE topics in SQL Server 2000 Books Online.

For additional information about shrinking the transaction log in SQL Server 7.0, click the article number below to view the article in the Microsoft Knowledge Base:
256650 INF: How to Shrink the SQL Server Transaction Log

The information in this article applies to:
Microsoft SQL Server 2000 (all editions)
Last Reviewed: 11/6/2003 (3.0)
Keywords: kbinfo KB272318 kbAudDeveloper更多精彩文章及讨论,请光临枫下论坛 rolia.net
Report

Replies, comments and Discussions:

  • 工作学习 / 专业技术讨论 / MS sql server in my laptop, one database data file around 2G, but log file 4G, how to shrink log file.
    • here.
      本文发表在 rolia.net 枫下论坛INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
      View products that this article applies to.
      This article was previously published under Q272318
      SUMMARY
      Shrinking the log in SQL Server 2000 is no longer a deferred operation. A shrink operation attempts to shrink the file immediately. However, in some circumstances it may be necessary to perform additional actions before the log file is shrunk to the desired size.
      MORE INFORMATION
      When DBCC SHRINKFILE is run, SQL Server 2000 shrinks the log file by removing as many virtual log files as it can to attempt to reach the target size. If the target file size is not reached, SQL Server places dummy log entries in the last virtual log file until the virtual log is filled and moves the head of the log to the beginning of the file. The following actions are then required to complete the shrinking of the transaction log:
      You must run a BACKUP LOG statement to free up space by removing the inactive portion of the log.
      You must run DBCC SHRINKFILE again with the desired target size until the log file shrinks to the target size.
      The following example demonstrates this with the pubs database and attempts to shrink the pubs_log file to 2 MB:
      Run this code:

      DBCC SHRINKFILE(pubs_log, 2)

      NOTE: If the target size is not reached, proceed to the next step.
      Run this code if you want to truncate the transaction log and not keep a backup of the transaction log. Truncate_only invalidates your transaction log backup sequence. Take a full backup of your database after you perform backup log with truncate_only:

      BACKUP LOG pubs WITH TRUNCATE_ONLY
      -or-

      Run this code if you want to keep a backup of your transaction log and keep your transaction log backup sequence intact. See SQL Server Books Online topic "BACKUP" for more information:
      BACKUP LOG pubs TO pubslogbackup
      Run this code:
      DBCC SHRINKFILE(pubs_log,2)

      The transaction log has now been shrunk to the target size.
      REFERENCES
      For more information, see the Shrinking the Transaction Log and DBCC SHRINKFILE topics in SQL Server 2000 Books Online.

      For additional information about shrinking the transaction log in SQL Server 7.0, click the article number below to view the article in the Microsoft Knowledge Base:
      256650 INF: How to Shrink the SQL Server Transaction Log

      The information in this article applies to:
      Microsoft SQL Server 2000 (all editions)
      Last Reviewed: 11/6/2003 (3.0)
      Keywords: kbinfo KB272318 kbAudDeveloper更多精彩文章及讨论,请光临枫下论坛 rolia.net
    • detach the database, move the log file somewhere else or remove it, then attach the data file , u will get a new log file...
      • I detach, rename log file, re-attach. DONE, Thank you