事务日志文件不停增长的原因有很多,比如1,Recovery Mode 不是simple,或者说不处于auto-truncate mode下,2,有一个很大的事务在运行,3,有一个运行很长时间的事务没有提交 都会导致事务日志文件不停增长,此时 dbcc shrinkflie 命令是不能把日志文件shrink的,必须针对特定的情况,采取对应的对策,一般来说,对于1,必须...
DBCCSHRINKDATABASE(database_name|database_id|0[ ,target_percent] [ , {NOTRUNCATE|TRUNCATEONLY} ] ) [WITH{ [WAIT_AT_LOW_PRIORITY[ (<wait_at_low_priority_option_list>)] ] [ ,NO_INFOMSGS] } ]< wait_at_low_priority_option_list >::=<wait_at_low_priority_option>|<wait_at_low_...
运行NOTRUNCATE时指定TRUNCATEONLY选项或DBCC SHRINKDATABASE选项。 如果未指定,则结果与使用DBCC SHRINKDATABASE运行NOTRUNCATE操作然后使用DBCC SHRINKDATABASE运行TRUNCATEONLY操作的结果相同。 收缩数据库不必处于单用户模式。 其他用户可以在数据库收缩时在其中工作,包括系统数据库。
USEAdventureWorks2022; GO-- Truncate the log by changing the database recovery model to SIMPLE.ALTERDATABASEAdventureWorks2022SETRECOVERYSIMPLE; GO-- Shrink the truncated log file to 1 MB.DBCC SHRINKFILE (AdventureWorks2022_Log, 1); GO-- Reset the database recovery model.ALTERDATABASEAdventureWorks...
Shrink a Log file to 8 GiB (8192 MiB):USE MyDatabase; GO DBCC SHRINKFILE(MyDatabase_Log, 8192)BACKUP LOG MyDatabase WITH TRUNCATE_ONLYDBCC SHRINKFILE(MyDatabase_Log, 8192) Afterwords, perform a full backup of the database. To make the file as small as possible you can specify 1 for...
DBCC SHRINKDATABASE。 收缩指定数据库中的数据文件大小。 DBCCSHRINKDATABASE ('database_name'|database_id|0 [,target_percent] [,{NOTRUNCATE|TRUNCATEONLY}] ) [WITHNO_INFOMSGS] DBCC DROPCLEANBUFFERS。 从缓冲池中删除所有清除缓冲区。 DBCC DROPCLEANBUFFERS [ WITH NO_INFOMSGS ] ...
A shrink operation is most effective after an operation that creates lots of unused space, such as a truncate table or a drop table operation. Most databases require some free space to be available for regular day-to-day operations. If you shrink a database repeatedly and notice that the da...
--11DBCC shrinkdatabase --收缩指定数据库的数据文件和日志文件大小 --shrinkdatabase ({dbid | 'dbname'}, [freespace_percentage [, {NOTRUNCATE | TRUNCATEONLY}]]) --- --12DBCC shrinkfile --收缩相关数据库的指定数据文件和日志文件大小 --shrinkfile ({file...
DBCC SHRINKDATABASE with the TRUNCATEONLY option affects the database transaction log file only. To truncate the data file, use DBCC SHRINKFILE instead. For more information, see DBCC SHRINKFILE. WITH NO_INFOMSGS Suppresses all informational messages that have severity levels from 0 through 10. ...
Therefore, if NOTRUNCATE is specified, the file appears not to shrink.NOTRUNCATE is applicable only to data files. The log files aren't affected.This option isn't supported for FILESTREAM filegroup containers.TRUNCATEONLYReleases all free space at the file's end to the operating system but ...