教你如何清除SQL日誌

教你如何清除SQL日誌 

1．開啟查詢分析器，輸入命令

DUMP TRANSACTION 資料庫名 WITH NO_LOG

2.再開啟企業管理器--右鍵你要壓縮的資料庫--所有任務--收縮資料庫--收縮檔案--選擇記錄檔--在收縮方式裡選擇收縮至XXM,這裡會給出一個允許收縮到的最小M數,直接輸入這個數,確定就可以了。

清除Log有兩種方法：

　　1.自動清除法

　　開放資料庫選項 Trunc Log on Chkpt，使資料庫系統每隔一段時間自動清除Log。此方法的優點是無須人工幹預，由SQL　Server自動執行，並且一般不會出現Log溢滿的情況；缺點是只清除Log而不做備份。

　　2.手動清除法

　　執行命令“dump transaction”來清除Log。以下兩條命令都可以清除日誌：

dump transaction with truncate_only

dump transaction with no_log 

　　通常刪除交易記錄中不活躍的部分可使用“dump transaction with trancate_only”命令，這條命令寫進交易記錄時，還要做必要的並發性檢查。SYBASE提供“dump transaction with no_log”來處理某些非常緊迫的情況，使用這條命令有很大的危險性，SQL　Server會彈出一條警告資訊。為了盡量確保資料庫的一致性，你應將它作為“最後一招”。

　　以上兩種方法只??清除日誌，而不做記錄備份，若想備份日誌，應執行“dump transaction database_name to dumpdevice”命令。 

PS:附一個更好的方法

先分離資料庫後，直接刪除日誌以後,再在查詢分析器裡用

exec sp_attach_single_file_db '資料庫名', '.mdf檔案路徑' 

命令附加資料庫。 OVER.在別的地方看到的 不錯。

資料庫日誌操作

先提供一種複雜的方法壓縮日誌及資料庫檔案如下：

1.清空日誌

　 DUMP TRANSACTION 庫名 WITH NO_LOG  

2.截斷交易記錄：

　 BACKUP LOG 資料庫名 WITH NO_LOG

3.收縮資料庫檔案(如果不壓縮,資料庫的檔案不會減小

　 企業管理器--右鍵你要壓縮的資料庫--所有任務--收縮資料庫--收縮檔案

　   --選擇記錄檔--在收縮方式裡選擇收縮至XXM,這裡會給出一個允許收縮到的最小M數,直接輸入這個數,確定就可以了

　   --選擇資料檔案--在收縮方式裡選擇收縮至XXM,這裡會給出一個允許收縮到的最小M數,直接輸入這個數,確定就可以了

　 也可以用SQL語句來完成

　 --收縮資料庫

　 DBCC SHRINKDATABASE(客戶資料)

　 --收縮指定資料檔案,1是檔案號,可以通過這個語句查詢到:select * from sysfiles

　 DBCC SHRINKFILE(1)

4.為了最大化的縮小記錄檔(如果是sql 7.0,這步只能在查詢分析器中進行)

　 a.分離資料庫:

　   企業管理器--伺服器--資料庫--右鍵--分離資料庫

　 b.在我的電腦中刪除LOG檔案

　 c.附加資料庫:

　   企業管理器--伺服器--資料庫--右鍵--附加資料庫

　 此法將產生新的LOG，大小隻有500多K

　 或用代碼：

　 下面的樣本分離 pubs，然後將 pubs 中的一個檔案附加到當前伺服器。

　 a.分離

　 E X E C sp_detach_db @dbname = 'pubs'

　 b.刪除記錄檔

　 c.再附加

　 E X E C sp_attach_single_file_db @dbname = 'pubs',

　    @physname = 'c:/Program Files/Microsoft SQL Server/MSSQL/Data/pubs.mdf'

5.為了以後能自動收縮,做如下設定:

　 企業管理器--伺服器--右鍵資料庫--屬性--選項--選擇"自動收縮"

　 --SQL語句設定方式:

　 E X E C sp_dboption '資料庫名', 'autoshrink', 'TRUE'

6.如果想以後不讓它日誌增長得太大

　 企業管理器--伺服器--右鍵資料庫--屬性--交易記錄

　   --將檔案增長限制為xM(x是你允許的最大資料檔案大小)

　 --SQL語句的設定方式:

　 alter database 資料庫名 modify file(name=邏輯檔案名稱,maxsize=20)

特別注意：

　 請按步驟進行,未進行前面的步驟,請不要做後面的步驟

　 否則可能損壞你的資料庫.

　 一般不建議做第4,6兩步

　 第4步不安全,有可能損壞資料庫或遺失資料

　 第6步如果日誌達到上限,則以後的資料庫處理會失敗,在清理日誌後才能恢複.

另外提供一種更簡單的方法，本人屢試不爽，建議大家使用。

更簡單的方法：

   1。右建資料庫屬性視窗--故障還原模型--設為簡單

   2。右建資料庫所有任務--收縮資料庫

   3。右建資料庫屬性視窗--故障還原模型--設為大容量日誌記錄 

可能有不少朋友遇到過這樣的問題：

update或delete語句忘帶了where子句，或where子句精度不夠，執行之後造成了嚴重的後果，

這種情況的資料恢複只能利用交易記錄的備份來進行，所以如果你的SQL沒有進行相應的全庫備份

或不能備份日誌（truncate log on checkpoint選項為1），那麼就無法進行資料的恢複了，或者

只能恢複到最近一次的備份的資料了。

以下簡單說明恢複資料方法：

1，如果誤操作之前存在一個全庫備份（或已有多個差異備份或增量備份），首先要做的事就是進進行一次記錄備份（如果為了不讓記錄檔變大而置trunc. log on chkpt.選項為1那你就死翹了）

     backup log dbName to disk='fileName'

2，恢複一個全庫備份，注意需要使用with norecovery，如果還有其他差異或增量備份，則逐個恢複

     restore database dbName from disk='fileName' with norecovery

3，恢複最後一個記錄備份即剛做的記錄備份，指定恢復點到誤操作之前的時刻

     restore log dbName from disk='fileName'

     with stopat='date_time'

以上這些操作都可以在SQL SERVER企業管理器裡完成，難度不大。。。

記錄檔滿而造成SQL資料庫無法寫入檔案時，可用兩種方法：

一種方法：清空日誌。

1．開啟查詢分析器，輸入命令

DUMP TRANSACTION 資料庫名 WITH NO_LOG

2.再開啟企業管理器--右鍵你要壓縮的資料庫--所有任務--收縮資料庫--收縮檔案--選擇記錄檔--在收縮方式裡選擇收縮至XXM,這裡會給出一個允許收縮到的最小M數,直接輸入這個數,確定就可以了。

另一種方法有一定的風險性，因為SQL SERVER的記錄檔不是即時寫入資料庫主檔案的，如處理不當，會造成資料的損失。

1: 刪除LOG

分離資料庫 企業管理器－＞伺服器－＞資料庫－＞右鍵－＞分離資料庫

2：刪除LOG檔案

附加資料庫 企業管理器－＞伺服器－＞資料庫－＞右鍵－＞附加資料庫

此法產生新的LOG，大小隻有500多K。

注意：建議使用第一種方法。

如果以後,不想要它變大。

SQL2000下使用：

在資料庫上點右鍵->屬性->選項->故障恢複-模型-選擇-簡單模型。

或用SQL語句：

alter database 資料庫名 set recovery simple

另外，Truncate log on checkpoint(此選項用於SQL7.0，SQL 2000中即故障恢複模型選擇為簡單模型)當執行CHECKPOINT 命令時如果交易記錄檔超過其大小的70% 則將其內容清除在開發資料庫時時常將此選項設定為True Auto shrink定期對資料庫進行檢查當資料庫檔案或記錄檔的未用空間超過其大小的25%時，系統將會自動縮減檔案使其未用空間等於25% 當檔案大小沒有超過其建立時的初始大小時不會縮減檔案縮減後的檔案也必須大於或等於其初始大小對交易記錄檔的縮減只有在對其作備份時或將Truncate log on checkpoint 選項設為True 時才能進行。

注意：一般立成建立的資料庫預設屬性已設好，但碰到意外情況使資料庫屬性被更改，請使用者清空日誌後，檢查資料庫的以上屬性，以防交易記錄再次充滿。

當然，如果誤操作是一些不記日誌的操作比如truncate table，select into等操作，那麼是無法利

用上述方法來恢複資料的...

刪除SQL日誌1: 刪除LOG

1：分離資料庫 企業管理器－＞伺服器－＞資料庫－＞右鍵－＞分離資料庫

2：刪除LOG檔案

3：附加資料庫 企業管理器－＞伺服器－＞資料庫－＞右鍵－＞附加資料庫

此法產生新的LOG，大小隻有520多K

再將此資料庫設定自動收縮

或用代碼： 

下面的樣本分離 77169database，然後將 77169database 中的一個檔案附加到當前伺服器。

EXEC sp_detach_db @dbname = '77169database'

EXEC sp_attach_single_file_db @dbname = '77169database', 

@physname = 'c:/Program Files/Microsoft SQL Server/MSSQL/Data/77169database.mdf 

2:清空日誌

DUMP TRANSACTION 庫名 WITH NO_LOG /// DUMP TRANSACTION ulionmis WITH NO_LOG

再：

企業管理器--右鍵你要壓縮的資料庫--所有任務--收縮資料庫--收縮檔案--選擇記錄檔--在收縮方式裡選擇收縮至XXM,這裡會給出一個允許收縮到的最小M數,直接輸入這個數,確定就可以了

3: 如果想以後不讓它增長

企業管理器－＞伺服器－＞資料庫－＞屬性－＞交易記錄－＞將檔案增長限制為2M

自動收縮日誌,也可以用下面這條語句

Alter DATABASE 資料庫名

SET AUTO_SHRINK ON

故障還原模型改為簡單,用語句是

USE MASTER

GO

Alter DATABASE 資料庫名 SET RECOVERY SIMPLE

GO

-------------------------------------------------------------------------------

截斷交易記錄：

BACKUP LOG { database_name | @database_name_var }

{

[ WITH

{ NO_LOG | TRUNCATE_ONLY } ] 

}

--壓縮日誌及資料庫檔案大小

/*--特別注意

請按步驟進行,未進行前面的步驟,請不要做後面的步驟

否則可能損壞你的資料庫.

--*/

1.清空日誌

DUMP TRANSACTION 庫名 WITH NO_LOG 

2.截斷交易記錄：

BACKUP LOG 資料庫名 WITH NO_LOG

3.收縮資料庫檔案(如果不壓縮,資料庫的檔案不會減小

企業管理器--右鍵你要壓縮的資料庫--所有任務--收縮資料庫--收縮檔案

--選擇記錄檔--在收縮方式裡選擇收縮至XXM,這裡會給出一個允許收縮到的最小M數,直接輸入這個數,確定就可以了

--選擇資料檔案--在收縮方式裡選擇收縮至XXM,這裡會給出一個允許收縮到的最小M數,直接輸入這個數,確定就可以了

也可以用SQL語句來完成

--收縮資料庫

DBCC SHRINKDATABASE(客戶資料)

--收縮指定資料檔案,1是檔案號,可以通過這個語句查詢到:select * from sysfiles

DBCC SHRINKFILE(1)

4.為了最大化的縮小記錄檔(如果是sql 7.0,這步只能在查詢分析器中進行)

a.分離資料庫:

企業管理器--伺服器--資料庫--右鍵--分離資料庫

b.在我的電腦中刪除LOG檔案

c.附加資料庫:

企業管理器--伺服器--資料庫--右鍵--附加資料庫

此法將產生新的LOG，大小隻有500多K

或用代碼： 

下面的樣本分離 77169database，然後將 77169database 中的一個檔案附加到當前伺服器。

a.分離

EXEC sp_detach_db @dbname = '77169database'

b.刪除記錄檔

c.再附加

EXEC sp_attach_single_file_db @dbname = '77169database', 

@physname = 'c:/Program Files/Microsoft SQL Server/MSSQL/Data/77169database.mdf'

5.為了以後能自動收縮,做如下設定:

企業管理器--伺服器--右鍵資料庫--屬性--選項--選擇"自動收縮"

--SQL語句設定方式:

EXEC sp_dboption '資料庫名', 'autoshrink', 'TRUE' 

6.如果想以後不讓它日誌增長得太大

企業管理器--伺服器--右鍵資料庫--屬性--交易記錄

--將檔案增長限制為xM(x是你允許的最大資料檔案大小)

--SQL語句的設定方式:

alter database 資料庫名 modify file(name=邏輯檔案名稱,maxsize=20)

-------------------------------------------------------------------------------

/*--壓縮資料庫的通用預存程序

壓縮日誌及資料庫檔案大小

因為要對資料庫進行分離處理

所以預存程序不能建立在被壓縮的資料庫中

--*/

/*--調用樣本

exec p_compdb 'test'

--*/

use master --注意,此預存程序要建在master資料庫中

go

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_compdb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[p_compdb]

GO

create proc p_compdb

@dbname sysname, --要壓縮的資料庫名

@bkdatabase bit=1, --因為分離日誌的步驟中,可能會損壞資料庫,所以你可以選擇是否自動資料庫

@bkfname nvarchar(260)='' --備份的檔案名稱,如果不指定,自動備份到預設備份目錄,備份檔案名為:資料庫名+日期時間

as

--1.清空日誌

exec('DUMP TRANSACTION ['+@dbname+'] WITH NO_LOG')

--2.截斷交易記錄：

exec('BACKUP LOG ['+@dbname+'] WITH NO_LOG')

--3.收縮資料庫檔案(如果不壓縮,資料庫的檔案不會減小

exec('DBCC SHRINKDATABASE(['+@dbname+'])')

--4.設定自動收縮

exec('EXEC sp_dboption '''+@dbname+''',''autoshrink'',''TRUE''')

--後面的步驟有一定危險,你可以可以選擇是否應該這些步驟

--5.分離資料庫

if @bkdatabase=1

begin

if isnull(@bkfname,'')='' 

set @bkfname=@dbname+'_'+convert(varchar,getdate(),112)

+replace(convert(varchar,getdate(),108),':','')

select 提示資訊='備份資料庫到SQL 預設備份目錄,備份檔案名:'+@bkfname

exec('backup database ['+@dbname+'] to disk='''+@bkfname+'''')

end

--進行分離處理

create table #t(fname nvarchar(260),type int)

exec('insert into #t select filename,type=status&0x40 from ['+@dbname+']..sysfiles')

exec('sp_detach_db '''+@dbname+'''')

--刪除記錄檔

declare @fname nvarchar(260),@s varchar(8000)

declare tb cursor local for select fname from #t where type=64

open tb 

fetch next from tb into @fname

while @@fetch_status=0

begin

set @s='del "'+rtrim(@fname)+'"'

exec master..xp_cmdshell @s,no_output

fetch next from tb into @fname

end

close tb

deallocate tb

--附加資料庫

set @s=''

declare tb cursor local for select fname from #t where type=0

open tb 

fetch next from tb into @fname

while @@fetch_status=0

begin

set @s=@s+','''+rtrim(@fname)+''''

fetch next from tb into @fname

end

close tb

deallocate tb

exec('sp_attach_single_file_db '''+@dbname+''''+@s)

go

記錄檔滿而造成SQL資料庫無法寫入檔案時，可用兩種方法：

一種方法：清空日誌。

1．開啟查詢分析器，輸入命令

DUMP TRANSACTION 資料庫名 WITH NO_LOG

2.再開啟企業管理器--右鍵你要壓縮的資料庫--所有任務--收縮資料庫--收縮檔案--選擇記錄檔--在收縮方式裡選擇收縮至XXM,這裡會給出一個允許收縮到的最小M數,直接輸入這個數,確定就可以了。

另一種方法有一定的風險性，因為SQL SERVER的記錄檔不是即時寫入資料庫主檔案的，如處理不當，會造成資料的損失。

1: 刪除LOG

分離資料庫 企業管理器－＞伺服器－＞資料庫－＞右鍵－＞分離資料庫

2：刪除LOG檔案

附加資料庫 企業管理器－＞伺服器－＞資料庫－＞右鍵－＞附加資料庫

此法產生新的LOG，大小隻有500多K。

注意：建議使用第一種方法。

如果以後,不想要它變大。

SQL2000下使用：

在資料庫上點右鍵->屬性->選項->故障恢複-模型-選擇-簡單模型。

或用SQL語句：

alter database 資料庫名 set recovery simple

另外，Truncate log on checkpoint(此選項用於SQL7.0，SQL 2000中即故障恢複模型選擇為簡單模型)當執行CHECKPOINT 命令時如果交易記錄檔超過其大小的70% 則將其內容清除在開發資料庫時時常將此選項設定為True Auto shrink定期對資料庫進行檢查當資料庫檔案或記錄檔的未用空間超過其大小的25%時，系統將會自動縮減檔案使其未用空間等於25% 當檔案大小沒有超過其建立時的初始大小時不會縮減檔案縮減後的檔案也必須大於或等於其初始大小對交易記錄檔的縮減只有在對其作備份時或將Truncate log on checkpoint 選項設為True 時才能進行。

注意：一般立成建立的資料庫預設屬性已設好，但碰到意外情況使資料庫屬性被更改，請使用者清空日誌後，檢查資料庫的以上屬性，以防交易記錄再次充滿。