subject_46687.htm

一些关于vc的问答

<p>
序号：46687 发表者：随心所欲 发表日期：2003-07-12 20:43:14
<br>主题：数据库大小的控制
<br>内容：SQL Server的日志文件的增长太快了，我有一数据库，里面的数据一个月清除一次，但是数据库日志文件现在却有1.5G左右了，请教各位有什么好的方法可以让它变小一点？
<br><a href="javascript:history.go(-1)">返回上页</a><br><a href=http://www.copathway.com/cndevforum/>访问论坛</a></p>
<hr size=1>
<blockquote><p>
回复者：roy 回复日期：2003-07-15 08:43:36
<br>内容：有人这样说：先备份数据库日志，再收缩数据库。
<br>
<a href="javascript:history.go(-1)">返回上页</a><br><a href=http://www.copathway.com/cndevforum/>访问论坛</a></p></blockquote>
<hr size=1>
<blockquote><p>
<font color=red>答案被接受</font><br>回复者：开心玩够 回复日期：2003-07-15 10:01:58
<br>内容：把代码COPY到查询分析器里，，然后修改其中的3个参数(数据库名，日志文件名，和目标日志文件的大小)，运行即可(我已经用过多次了)&nbsp;&nbsp;&nbsp;&nbsp;<BR>-----&nbsp;&nbsp;&nbsp;&nbsp;<BR>SET NOCOUNT ON&nbsp;&nbsp;&nbsp;&nbsp;<BR>DECLARE @LogicalFileName sysname，&nbsp;&nbsp;&nbsp;&nbsp;<BR>@MaxMinutes INT，&nbsp;&nbsp;&nbsp;&nbsp;<BR>@NewSize INT&nbsp;&nbsp;&nbsp;&nbsp;<BR>&nbsp;&nbsp;&nbsp;&nbsp;<BR>&nbsp;&nbsp;&nbsp;&nbsp;<BR>USE Marias -- 要操作的数据库名&nbsp;&nbsp;&nbsp;&nbsp;<BR>SELECT @LogicalFileName = ‘Marias_log‘， -- 日志文件名&nbsp;&nbsp;&nbsp;&nbsp;<BR>@MaxMinutes = 10， -- Limit on time allowed to wrap log.&nbsp;&nbsp;&nbsp;&nbsp;<BR>@NewSize = 100 -- 你想设定的日志文件的大小(M)&nbsp;&nbsp;&nbsp;&nbsp;<BR>&nbsp;&nbsp;&nbsp;&nbsp;<BR>-- Setup / initialize&nbsp;&nbsp;&nbsp;&nbsp;<BR>DECLARE @OriginalSize int&nbsp;&nbsp;&nbsp;&nbsp;<BR>SELECT @OriginalSize = size&nbsp;&nbsp;&nbsp;&nbsp; <BR>FROM sysfiles&nbsp;&nbsp;&nbsp;&nbsp;<BR>WHERE name = @LogicalFileName&nbsp;&nbsp;&nbsp;&nbsp;<BR>SELECT ‘Original Size of ‘ + db_name() + ‘ LOG is ‘ +&nbsp;&nbsp;&nbsp;&nbsp; <BR>CONVERT(VARCHAR(30)，@OriginalSize) + ‘ 8K pages or ‘ +&nbsp;&nbsp;&nbsp;&nbsp; <BR>CONVERT(VARCHAR(30)，(@OriginalSize*8/1024)) + ‘MB‘&nbsp;&nbsp;&nbsp;&nbsp;<BR>FROM sysfiles&nbsp;&nbsp;&nbsp;&nbsp;<BR>WHERE name = @LogicalFileName&nbsp;&nbsp;&nbsp;&nbsp;<BR>CREATE TABLE DummyTrans&nbsp;&nbsp;&nbsp;&nbsp;<BR>(DummyColumn char (8000) not null)&nbsp;&nbsp;&nbsp;&nbsp;<BR>&nbsp;&nbsp;&nbsp;&nbsp;<BR>&nbsp;&nbsp;&nbsp;&nbsp;<BR>DECLARE @Counter INT，&nbsp;&nbsp;&nbsp;&nbsp;<BR>@StartTime DATETIME，&nbsp;&nbsp;&nbsp;&nbsp;<BR>@TruncLog VARCHAR(255)&nbsp;&nbsp;&nbsp;&nbsp;<BR>SELECT @StartTime = GETDATE()，&nbsp;&nbsp;&nbsp;&nbsp;<BR>@TruncLog = ‘BACKUP LOG ‘ + db_name() + ‘ WITH TRUNCATE_ONLY‘&nbsp;&nbsp;&nbsp;&nbsp;<BR>&nbsp;&nbsp;&nbsp;&nbsp;<BR>DBCC SHRINKFILE (@LogicalFileName， @NewSize)&nbsp;&nbsp;&nbsp;&nbsp;<BR>EXEC (@TruncLog)&nbsp;&nbsp;&nbsp;&nbsp;<BR>-- Wrap the log if necessary.&nbsp;&nbsp;&nbsp;&nbsp;<BR>WHILE @MaxMinutes " DATEDIFF (mi， @StartTime， GETDATE()) -- time has not expired&nbsp;&nbsp;&nbsp;&nbsp;<BR>AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)&nbsp;&nbsp;&nbsp;&nbsp; <BR>AND (@OriginalSize * 8 /1024) " @NewSize&nbsp;&nbsp;&nbsp;&nbsp; <BR>BEGIN -- Outer loop.&nbsp;&nbsp;&nbsp;&nbsp;<BR>SELECT @Counter = 0&nbsp;&nbsp;&nbsp;&nbsp;<BR>WHILE ((@Counter lt; @OriginalSize / 16) AND (@Counter lt; 50000))&nbsp;&nbsp;&nbsp;&nbsp;<BR>BEGIN -- update&nbsp;&nbsp;&nbsp;&nbsp;<BR>INSERT DummyTrans VALUES (‘Fill Log‘)&nbsp;&nbsp;&nbsp;&nbsp; <BR>DELETE DummyTrans&nbsp;&nbsp;&nbsp;&nbsp;<BR>SELECT @Counter = @Counter + 1&nbsp;&nbsp;&nbsp;&nbsp;<BR>END&nbsp;&nbsp;&nbsp;&nbsp; <BR>EXEC (@TruncLog)&nbsp;&nbsp;&nbsp;&nbsp; <BR>END&nbsp;&nbsp;&nbsp;&nbsp; <BR>SELECT ‘Final Size of ‘ + db_name() + ‘ LOG is ‘ +&nbsp;&nbsp;&nbsp;&nbsp;<BR>CONVERT(VARCHAR(30)，size) + ‘ 8K pages or ‘ +&nbsp;&nbsp;&nbsp;&nbsp; <BR>CONVERT(VARCHAR(30)，(size*8/1024)) + ‘MB‘&nbsp;&nbsp;&nbsp;&nbsp;<BR>FROM sysfiles&nbsp;&nbsp;&nbsp;&nbsp; <BR>WHERE name = @LogicalFileName&nbsp;&nbsp;&nbsp;&nbsp;<BR>DROP TABLE DummyTrans&nbsp;&nbsp;&nbsp;&nbsp;<BR>
<br>
<a href="javascript:history.go(-1)">返回上页</a><br><a href=http://www.copathway.com/cndevforum/>访问论坛</a></p></blockquote>
<hr size=1>
<blockquote><p>
回复者：金枪鱼 回复日期：2003-07-15 11:06:44
<br>内容：TO：2楼<BR>怎么COPY进入，修改名称等后运行通不过！
<br>
<a href="javascript:history.go(-1)">返回上页</a><br><a href=http://www.copathway.com/cndevforum/>访问论坛</a></p></blockquote>
<hr size=1>
<blockquote><p>
回复者：金枪鱼 回复日期：2003-07-17 14:33:59
<br>内容：Up
<br>
<a href="javascript:history.go(-1)">返回上页</a><br><a href=http://www.copathway.com/cndevforum/>访问论坛</a></p></blockquote>
<hr size=1>
<blockquote><p>
回复者：qiusb 回复日期：2003-07-17 16:40:15
<br>内容：感谢各位的支持，我也试用了各种方法，还可以。谢谢!!!
<br>
<a href="javascript:history.go(-1)">返回上页</a><br><a href=http://www.copathway.com/cndevforum/>访问论坛</a></p></blockquote>