12592.htm

一本很基础的SQL讲解

<link href="./dzs_cs.css" rel="stylesheet" type="text/css" />
<table width="96%" border="0" align="center" cellpadding="0" cellspacing="0">
      <tr>
        <td>&nbsp;</td>
      </tr>
      <tr>
        <td height="24" align="center" valign="bottom" class="d_font3">SQL Server日志清除的两种方法</td>
      </tr>
      <tr>
        <td height="3" bgcolor="#E3E3E3"></td>
      </tr>
      <tr>
        <td>&nbsp;</td>
      </tr>
      <tr>
        <td class="d_font4"> <P><STRONG>方法一</STRONG></P>
<P>一般情况下，SQL数据库的收缩并不能很大程度上减小数据库大小，其主要作用是收缩日志大小，应当定期进行此操作以免数据库日志过大。</P>
<P>1、设置数据库模式为简单模式：打开SQL企业管理器，在控制台根目录中依次点开Microsoft SQL Server-->SQL Server组-->双击打开你的服务器-->双击打开数据库目录-->选择你的数据库名称（如论坛数据库Forum）-->然后点击右键选择属性-->选择选项-->在故障还原的模式中选择“简单”，然后按确定保存；</P>
<P>2、在当前数据库上点右键，看所有任务中的收缩数据库，一般里面的默认设置不用调整，直接点确定；</P>
<P>3、收缩数据库完成后，建议将您的数据库属性重新设置为标准模式，操作方法同第一点，因为日志在一些异常情况下往往是恢复数据库的重要依据。</P>
<P><STRONG>方法二</STRONG></P>
<P>SET NOCOUNT ON<BR>DECLARE @LogicalFileName sysname,<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; @MaxMinutes INT,<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; @NewSize INT</P>
<P><BR>USE&nbsp;&nbsp;&nbsp;&nbsp; tablename&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- 要操作的数据库名<BR>SELECT&nbsp; @LogicalFileName = 'tablename_log',&nbsp; -- 日志文件名<BR>@MaxMinutes = 10,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- Limit on time allowed to wrap log.<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; @NewSize = 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- 你想设定的日志文件的大小(M)</P>
<P>-- Setup / initialize<BR>DECLARE @OriginalSize int<BR>SELECT @OriginalSize = size <BR>&nbsp; FROM sysfiles<BR>&nbsp; WHERE name = @LogicalFileName<BR>SELECT 'Original Size of ' + db_name() + ' LOG is ' + <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'<BR>&nbsp; FROM sysfiles<BR>&nbsp; WHERE name = @LogicalFileName<BR>CREATE TABLE DummyTrans<BR>&nbsp; (DummyColumn char (8000) not null)</P>
<P><BR>DECLARE @Counter&nbsp;&nbsp; INT,<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; @StartTime DATETIME,<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; @TruncLog&nbsp; VARCHAR(255)<BR>SELECT&nbsp; @StartTime = GETDATE(),<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; @TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'</P>
<P>DBCC SHRINKFILE (@LogicalFileName, @NewSize)<BR>EXEC (@TruncLog)<BR>-- Wrap the log if necessary.<BR>WHILE&nbsp;&nbsp;&nbsp;&nbsp; @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)&nbsp; <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AND (@OriginalSize * 8 /1024) > @NewSize&nbsp; <BR>&nbsp; BEGIN -- Outer loop.<BR>&nbsp;&nbsp;&nbsp; SELECT @Counter = 0<BR>&nbsp;&nbsp;&nbsp; WHILE&nbsp; ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; BEGIN -- update<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; INSERT DummyTrans VALUES ('Fill Log')&nbsp; <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; DELETE DummyTrans<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT @Counter = @Counter + 1<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; END&nbsp;&nbsp; <BR>&nbsp;&nbsp;&nbsp; EXEC (@TruncLog)&nbsp; <BR>&nbsp; END&nbsp;&nbsp; <BR>SELECT 'Final Size of ' + db_name() + ' LOG is ' +<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CONVERT(VARCHAR(30),size) + ' 8K pages or ' + <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'<BR>&nbsp; FROM sysfiles <BR>&nbsp; WHERE name = @LogicalFileName<BR>DROP TABLE DummyTrans<BR>SET NOCOUNT OFF </P> </td>
      </tr>
      <tr>
        <td class="d_font4">&nbsp;</td>
      </tr>
    </table>