清理日志代码,MSSQL代码库,德仔网

代码语言
代码分类
【MSSQL】清理日志
作者:Dezai.CN / 发布于2011/8/9/ 751
<div>
<div><span style="color: rgb(0,0,255)">use</span><span style="color: rgb(0,0,0)"> CTC315
</span><span style="color: rgb(0,0,255)">select</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,128,128)">*</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">from</span><span style="color: rgb(0,0,0)"> sysfiles

</span><span style="color: rgb(0,0,255)">dump</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">transaction</span><span style="color: rgb(0,0,0)"> CTC315 </span><span style="color: rgb(0,0,255)">with</span><span style="color: rgb(0,0,0)"> no_log 
</span><span style="color: rgb(0,0,255)">DBCC</span><span style="color: rgb(0,0,0)"> SHRINKFILE (</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">CTC315_Log</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">)

</span><span style="color: rgb(0,0,255)">set</span><span style="color: rgb(0,0,0)"> ANSI_NULLS </span><span style="color: rgb(0,0,255)">ON</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,0,255)">set</span><span style="color: rgb(0,0,0)"> QUOTED_IDENTIFIER </span><span style="color: rgb(0,0,255)">ON</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,0,255)">go</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,0,255)">create</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">procedure</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,0)">[</span><span style="color: rgb(255,0,0)">dbo</span><span style="color: rgb(255,0,0)">]</span><span style="color: rgb(0,0,0)">.</span><span style="color: rgb(255,0,0)">[</span><span style="color: rgb(255,0,0)">p清除日志</span><span style="color: rgb(255,0,0)">]</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,128,0)">@databasename</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">varchar</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(128,0,0); font-weight: bold">100</span><span style="color: rgb(0,0,0)">) </span><span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)"> 数据库名称</span><span style="color: rgb(0,128,128)">
</span><span style="color: rgb(0,0,255)">as</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,0,255)">begin</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,0,255)">declare</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,128,0)">@execsql</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">nvarchar</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(255,0,255)">max</span><span style="color: rgb(0,0,0)">), </span><span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)"> 执行语句</span><span style="color: rgb(0,128,128)">
</span><span style="color: rgb(0,128,0)">@logfilename</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">varchar</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(128,0,0); font-weight: bold">100</span><span style="color: rgb(0,0,0)">) </span><span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)"> 日志逻辑文件</span><span style="color: rgb(0,128,128)">
</span><span style="color: rgb(0,0,255)">select</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,128,0)">@execsql</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">select @filename=name from </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(0,128,0)">@databasename</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">.dbo.[sysfiles] where fileid=2</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)"> 查询文件名</span><span style="color: rgb(0,128,128)">
--</span><span style="color: rgb(0,128,128)"> select 1,@execsql,@logfilename </span><span style="color: rgb(0,128,128)">
</span><span style="color: rgb(0,0,255)">exec</span><span style="color: rgb(0,0,0)"> sp_executesql </span><span style="color: rgb(0,128,0)">@execsql</span><span style="color: rgb(0,0,0)">,N</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">@filename varchar(100) output </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(0,128,0)">@logfilename</span><span style="color: rgb(0,0,0)"> output 
</span><span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)"> select 1,@logfilename</span><span style="color: rgb(0,128,128)">
</span><span style="color: rgb(0,0,255)">select</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,128,0)">@execsql</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">use </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(0,128,0)">@databasename</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)"> dump transaction </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(0,128,0)">@databasename</span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)"> with no_log </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)"> DBCC SHRINKFILE (</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(0,128,0)">@logfilename</span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">) </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)"> 清除脚本</span><span style="color: rgb(0,128,128)">
--</span><span style="color: rgb(0,128,128)"> select @execsql</span><span style="color: rgb(0,128,128)">
</span><span style="color: rgb(0,0,255)">exec</span><span style="color: rgb(0,0,0)"> sp_executesql </span><span style="color: rgb(0,128,0)">@execsql</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)"> 清除</span><span style="color: rgb(0,128,128)">
</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,0,255)">end</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,128,128)">/*</span><span style="color: rgb(0,128,128)">
exec [p清除日志] 'master'
</span><span style="color: rgb(0,128,128)">*/</span></div>

<div class="cnblogs_code">
<div><span style="color: rgb(0,0,0)">SQL </span><span style="color: rgb(128,0,0); font-weight: bold">7.0</span><span style="color: rgb(128,128,128)">/</span><span style="color: rgb(128,0,0); font-weight: bold">2000</span><span style="color: rgb(0,0,0)">:
</span><span style="color: rgb(0,0,255)">BACKUP</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">TRANSACTION</span><span style="color: rgb(0,0,0)"> DBName </span><span style="color: rgb(0,0,255)">WITH</span><span style="color: rgb(0,0,0)"> TRUNCATE_ONLY </span><span style="color: rgb(0,0,255)">DBCC</span><span style="color: rgb(0,0,0)"> SHRINKFILE(</span><span style="color: rgb(128,0,0); font-weight: bold">2</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(128,0,0); font-weight: bold">200</span><span style="color: rgb(0,0,0)">)

SQL </span><span style="color: rgb(128,0,0); font-weight: bold">2005</span><span style="color: rgb(0,0,0)">:
在 SQL </span><span style="color: rgb(128,0,0); font-weight: bold">2005</span><span style="color: rgb(0,0,0)"> 中備份 </span><span style="color: rgb(0,0,255)">Transaction</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,255)">Log</span><span style="color: rgb(0,0,0)"> 語法改為 </span><span style="color: rgb(0,0,255)">Backup</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,255)">LOG</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,0,255)">BACKUP</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,255)">LOG</span><span style="color: rgb(0,0,0)"> DBName </span><span style="color: rgb(0,0,255)">WITH</span><span style="color: rgb(0,0,0)"> NO_LOG
</span><span style="color: rgb(0,0,255)">DBCC</span><span style="color: rgb(0,0,0)"> SHRINKFILE(</span><span style="color: rgb(128,0,0); font-weight: bold">2</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(128,0,0); font-weight: bold">200</span><span style="color: rgb(0,0,0)">)

SQL </span><span style="color: rgb(128,0,0); font-weight: bold">2008</span><span style="color: rgb(0,0,0)">:
必須先將復原模式改為 "簡單" 才能清除，完成之後再將模式改回 "完整"
</span><span style="color: rgb(0,0,255)">USE</span><span style="color: rgb(0,0,0)"> DBName
</span><span style="color: rgb(0,0,255)">Alter</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">Database</span><span style="color: rgb(0,0,0)"> DBName </span><span style="color: rgb(0,0,255)">Set</span><span style="color: rgb(0,0,0)"> Recovery Simple </span><span style="color: rgb(0,0,255)">DBCC</span><span style="color: rgb(0,0,0)"> SHRINKFILE(</span><span style="color: rgb(128,0,0); font-weight: bold">2</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(128,0,0); font-weight: bold">100</span><span style="color: rgb(0,0,0)">) </span><span style="color: rgb(0,0,255)">Alter</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">Database</span><span style="color: rgb(0,0,0)"> DBName </span><span style="color: rgb(0,0,255)">Set</span><span style="color: rgb(0,0,0)"> Recovery </span><span style="color: rgb(0,0,255)">Full
</span><span style="color: rgb(0,0,0)">
SQL </span><span style="color: rgb(128,0,0); font-weight: bold">2008</span><span style="color: rgb(0,0,0)"> R2:
</span><span style="color: rgb(0,0,255)">USE</span><span style="color: rgb(0,0,0)"> DBName;
</span><span style="color: rgb(0,0,255)">GO</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)"> Truncate the log by changing the database recovery model to SIMPLE.</span><span style="color: rgb(0,128,128)">
</span><span style="color: rgb(0,0,255)">ALTER</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">DATABASE</span><span style="color: rgb(0,0,0)"> DBName
</span><span style="color: rgb(0,0,255)">SET</span><span style="color: rgb(0,0,0)"> RECOVERY SIMPLE;
</span><span style="color: rgb(0,0,255)">GO</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)"> Shrink the truncated log file to 10 MB.</span><span style="color: rgb(0,128,128)">
</span><span style="color: rgb(0,0,255)">DBCC</span><span style="color: rgb(0,0,0)"> SHRINKFILE (</span><span style="color: rgb(128,0,0); font-weight: bold">2</span><span style="color: rgb(0,0,0)">, </span><span style="color: rgb(128,0,0); font-weight: bold">10</span><span style="color: rgb(0,0,0)">);
</span><span style="color: rgb(0,0,255)">GO</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)"> Reset the database recovery model.</span><span style="color: rgb(0,128,128)">
</span><span style="color: rgb(0,0,255)">ALTER</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">DATABASE</span><span style="color: rgb(0,0,0)"> DBName
</span><span style="color: rgb(0,0,255)">SET</span><span style="color: rgb(0,0,0)"> RECOVERY </span><span style="color: rgb(0,0,255)">FULL</span><span style="color: rgb(0,0,0)">;
</span><span style="color: rgb(0,0,255)">GO</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(128,128,128)">*</span><span style="color: rgb(0,0,0)">上述 </span><span style="color: rgb(0,0,255)">DBCC</span><span style="color: rgb(0,0,0)"> SHRINKFILE (</span><span style="color: rgb(128,0,0); font-weight: bold">2</span><span style="color: rgb(0,0,0)">, </span><span style="color: rgb(128,0,0); font-weight: bold">10</span><span style="color: rgb(0,0,0)">)，2指的是LOG </span><span style="color: rgb(0,0,255)">File</span><span style="color: rgb(0,0,0)">, 10指的是壓到10MB哦!</span></div>
</div>
</div>
试试其它关键字
　清理日志　
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)