📄 ushrinklog.pas
字号:
unit UShrinkLog;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, StdCtrls, DB, ADODB;
type
TfrmConfigure = class(TForm)
GroupBox1: TGroupBox;
rbWindows: TRadioButton;
rbSql: TRadioButton;
btnConnect: TButton;
btnClose: TButton;
GroupBox2: TGroupBox;
GroupBox3: TGroupBox;
Label7: TLabel;
txtSize: TEdit;
Label8: TLabel;
btnShrinkLog: TButton;
GroupBox4: TGroupBox;
mLogFile: TMemo;
GroupBox5: TGroupBox;
mDataFile: TMemo;
GroupBox6: TGroupBox;
Label1: TLabel;
Label2: TLabel;
Label3: TLabel;
Label5: TLabel;
txtServer: TEdit;
txtUser: TEdit;
txtPassword: TEdit;
txtdatabase: TEdit;
Label4: TLabel;
cmbLogName: TComboBox;
procedure btnConnectClick(Sender: TObject);
procedure btnCloseClick(Sender: TObject);
procedure btnShrinkLogClick(Sender: TObject);
procedure txtSizeKeyPress(Sender: TObject; var Key: Char);
procedure txtSizeExit(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
procedure GetDatabaseInfo;
procedure ShrinkLog;
function GetStoredProcSQL:string;
end;
var
frmConfigure: TfrmConfigure;
implementation
uses udm;
{$R *.dfm}
procedure TfrmConfigure.btnConnectClick(Sender: TObject);
begin
dm.WindowsLogin := rbwindows.Checked;
dm.strServer := self.txtServer.Text ;
dm.strUser := self.txtUser.Text ;
dm.strPassword := self.txtPassword.Text ;
dm.strdatabase := self.txtdatabase.Text ;
if(dm.ConnectionDataBase=false) then
begin
ShowMessage('不能按您提供的信息连接到SQL Server数据库！');
exit;
end;
self.btnShrinkLog.Enabled := true;
GetDatabaseInfo;
end;
procedure TfrmConfigure.btnCloseClick(Sender: TObject);
begin
close;
end;
procedure TfrmConfigure.GetDatabaseInfo;
var
i:integer;
qry:TAdoquery;
begin
qry:=dm.GetRecordSet('select groupid,size*8/1024 as size,name,filename from sysfiles');
mDataFile.Lines.Clear ;
mLogFile.Lines.Clear ;
cmbLogName.Clear ;
for i:=1 to qry.RecordCount do
begin
if qry.fieldbyname('groupid').AsInteger >0 then
begin
self.mDataFile.Lines.Add(trim(qry.fieldbyname('name').AsString)+
','+qry.fieldbyname('size').AsString+'m,'+
trim(qry.fieldbyname('filename').AsString));
end
else
begin
self.mLogFile.Lines.Add(trim(qry.fieldbyname('name').AsString)+
','+qry.fieldbyname('size').AsString+'m,'+
trim(qry.fieldbyname('filename').AsString));
cmbLogName.Items.Add(trim(qry.fieldbyname('name').AsString));
end;
qry.Next ;
end;
qry.Close ;
qry.Destroy ;
end;
procedure TfrmConfigure.ShrinkLog;
var
qry:TAdoquery;
proc:TAdoStoredProc;
begin
dm.ExecSQL('if exists (select * from dbo.sysobjects where '+
'id=object_id(N''[dbo].[shrinklog]'') and '+
'OBJECTPROPERTY(id, N''IsProcedure'') = 1) '+
' drop procedure [dbo].[shrinklog];');
dm.ExecSQL('SET QUOTED_IDENTIFIER OFF');
dm.ExecSQL('SET ANSI_NULLS OFF');
qry:=TAdoquery.Create(self);
qry.Connection := dm.Con;
qry.sql.add(GetStoredProcSQL);
qry.ExecSQL ;
qry.Close ;
qry.Destroy ;
dm.ExecSQL('SET QUOTED_IDENTIFIER ON');
dm.ExecSQL('SET ANSI_NULLS ON');
proc:=TAdoStoredProc.Create(self);
proc.Connection := dm.con;
proc.ProcedureName := 'shrinklog';
proc.Prepared := true;
proc.Parameters.CreateParameter('@LogicalFileName',ftString,pdInput,
200,cmbLogName.Text);
proc.Parameters.CreateParameter('@NewSize',ftInteger,pdInput,
8,strtoint(self.txtSize.Text));
proc.ExecProc ;
end;
procedure TfrmConfigure.btnShrinkLogClick(Sender: TObject);
begin
if cmblogname.Text ='' then
begin
showmessage('请选择日志名称！');
exit;
end;
ShrinkLog;
end;
procedure TfrmConfigure.txtSizeKeyPress(Sender: TObject; var Key: Char);
begin
if not (key in['0'..'9', '.', #8, #27, #13]) then
key := #0;
end;
procedure TfrmConfigure.txtSizeExit(Sender: TObject);
begin
if trim(txtsize.Text)='' then txtsize.Text :='1';
end;
function TfrmConfigure.GetStoredProcSQL: string;
var
s:string;
begin
s:=s+'CREATE PROCEDURE shrinklog '+chr(13);
s:=s+'@LogicalFileName varchar(200), '+chr(13);
s:=s+'@NewSize int '+chr(13);
s:=s+' AS '+chr(13);
s:=s+' '+chr(13);
s:=s+'SET NOCOUNT ON '+chr(13);
s:=s+'DECLARE @MaxMinutes INT '+chr(13);
s:=s+' '+chr(13);
s:=s+'SELECT @MaxMinutes = 10 '+chr(13);
s:=s+' '+chr(13);
s:=s+'DECLARE @OriginalSize int '+chr(13);
s:=s+'SELECT @OriginalSize = size '+chr(13);
s:=s+' FROM sysfiles '+chr(13);
s:=s+' WHERE name = @LogicalFileName '+chr(13);
s:=s+'SELECT ''Original Size of '' + db_name() + '' LOG is '' + '+chr(13);
s:=s+' CONVERT(VARCHAR(30),@OriginalSize) + '' 8K pages or '' + '+chr(13);
s:=s+' CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + ''MB'' '+chr(13);
s:=s+' FROM sysfiles '+chr(13);
s:=s+' WHERE name = @LogicalFileName '+chr(13);
s:=s+' '+chr(13);
s:=s+'CREATE TABLE DummyTrans '+chr(13);
s:=s+' (DummyColumn char (8000) not null) '+chr(13);
s:=s+' '+chr(13);
s:=s+' '+chr(13);
s:=s+'DECLARE @Counter INT, '+chr(13);
s:=s+' @StartTime DATETIME, '+chr(13);
s:=s+' @TruncLog VARCHAR(255) '+chr(13);
s:=s+'SELECT @StartTime = GETDATE(), '+chr(13);
s:=s+' @TruncLog = ''BACKUP LOG '' + db_name() + '' WITH TRUNCATE_ONLY'' '+chr(13);
s:=s+' '+chr(13);
s:=s+'DBCC SHRINKFILE (@LogicalFileName, @NewSize) '+chr(13);
s:=s+'EXEC (@TruncLog) '+chr(13);
s:=s+'-- Wrap the log if necessary. '+chr(13);
s:=s+'WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) '+chr(13);
s:=s+' AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName) '+chr(13);
s:=s+' AND (@OriginalSize * 8 /1024) > @NewSize '+chr(13);
s:=s+' BEGIN -- Outer loop. '+chr(13);
s:=s+' SELECT @Counter = 0 '+chr(13);
s:=s+' WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000)) '+chr(13);
s:=s+' BEGIN -- update '+chr(13);
s:=s+' INSERT DummyTrans VALUES (''Fill Log'') '+chr(13);
s:=s+' DELETE DummyTrans '+chr(13);
s:=s+' SELECT @Counter = @Counter + 1 '+chr(13);
s:=s+' END '+chr(13);
s:=s+' EXEC (@TruncLog) '+chr(13);
s:=s+' END '+chr(13);
s:=s+'SELECT ''Final Size of '' + db_name() + '' LOG is '' + '+chr(13);
s:=s+' CONVERT(VARCHAR(30),size) + '' 8K pages or '' + '+chr(13);
s:=s+' CONVERT(VARCHAR(30),(size*8/1024)) + ''MB'' '+chr(13);
s:=s+' FROM sysfiles '+chr(13);
s:=s+' WHERE name = @LogicalFileName '+chr(13);
s:=s+'DROP TABLE DummyTrans '+chr(13);
s:=s+'SET NOCOUNT OFF '+chr(13);
result:=s;
end;
end.
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -