關(guān)于 SQL Server ErrorLog 錯(cuò)誤日志說(shuō)明

更新時(shí)間：2011年04月30日 11:28:33 作者：

關(guān)于 SQL Server ErrorLog 錯(cuò)誤日志說(shuō)明學(xué)習(xí)sqlserver的朋友可以參考下。

默認(rèn)情況下，SQL Server 保存 7 個(gè) ErrorLog 文件，名為：
ErrorLog
ErrorLog.1
ErrorLog.2
ErrorLog.3
ErrorLog.4
ErrorLog.5
ErrorLog.6
在 APACS OS 版本 6.1 中，ErrorLog 文件保存在 c:\Program Files\Microsoft SQL Server\MSSQL$WINCC\LOG 文件夾中。在 APACS OS 版本 7.0 中，ErrorLog 文件保存在 c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG 文件夾中。ErrorLog 文件包含最新信息；ErrorLog.6 文件包含最老的信息。
每次重啟動(dòng) SQL Server 時(shí)，這些日志文件都如下循環(huán)：
刪除 ErrorLog.6 文件中的所有數(shù)據(jù)，并創(chuàng)建一個(gè)新的 ErrorLog 文件。
上個(gè) ErrorLog 文件中的所有數(shù)據(jù)被寫(xiě)入到 ErrorLog.1 文件中。
上個(gè) ErrorLog.1 文件中的所有數(shù)據(jù)被寫(xiě)入到 ErrorLog.2 文件中。
上個(gè) ErrorLog.2 文件中的所有數(shù)據(jù)被寫(xiě)入到 ErrorLog.3 文件中。
上個(gè) ErrorLog.3 文件中的所有數(shù)據(jù)被寫(xiě)入到 ErrorLog.4 文件中。
上個(gè) ErrorLog.4 文件中的所有數(shù)據(jù)被寫(xiě)入到 ErrorLog.5 文件中。
上個(gè) ErrorLog.5 文件中的所有數(shù)據(jù)被寫(xiě)入到 ErrorLog.6 文件中。
如果其中一個(gè) ErrorLog 文件已很大，則可通過(guò)運(yùn)行 sp_cycle_errorlog 存儲(chǔ)過(guò)程手動(dòng)循環(huán)這些 ErrorLog 文件。注意事項(xiàng)：舊的 ErrorLog 文件中的數(shù)據(jù)將被覆蓋！如果必須保存舊的 ErrorLog 文件中的數(shù)據(jù)，則可將這些舊的 ErrorLog 文件復(fù)制到某個(gè)外部存儲(chǔ)介質(zhì)中。
在 APACS OS 版本 6.1 中，可利用 SQL Qry Analyzer Tool 從 SQL Server Enterprise Manager 運(yùn)行 sp_cycle_errorlog 存儲(chǔ)過(guò)程。在 APACS OS 版本 7.0 中，可從 SQL Server Management St io 運(yùn)行 sp_cycle_errorlog 存儲(chǔ)過(guò)程。

所以需要定期能切換寫(xiě)入error log . 一般可以使用DBCC errorlog命令來(lái)操作。
Exec('DBCC ErrorLog')
或者可以通過(guò)以下命令，將sp 放在Job中定期執(zhí)行。

復(fù)制代碼代碼如下:

 
create procedure sp_cycle_errorlog --- 1997/06/24 
as 
if (not (is_srvrolemember('sysadmin') = 1)) -- Make sure that it is the SA executing this. 
begin 
raiserror(15247,-1,-1) 
return(1) 
end 
dbcc errorlog 
return (0) 
GO 

復(fù)制代碼代碼如下:

 
獲取數(shù)據(jù)庫(kù)中所有的表 
SELECT SysObjects.name AS Tablename FROM sysobjects WHERE xtype = 'U' and sysstat<200 
獲取數(shù)據(jù)庫(kù)中所有表的列名 
SELECT SysColumns.name AS Columnsname, SysObjects.name AS Tablename FROM SysObjects, SysColumns WHERE Sysobjects.Xtype='u' AND Sysobjects.Id=Syscolumns.Id 
獲取SQL所有數(shù)據(jù)庫(kù)名、所有表名、所有字段名、表字段長(zhǎng)度 
1.獲取MSSQL中的所有數(shù)據(jù)庫(kù)名: 
SELECT name FROM MASter..SysDatabASes ORDER BY name 
2.獲取MSSQL中的所有用戶(hù)表名: 
SELECT name FROM DatabASename..SysObjects WHERE XType='U' ORDER BY name 
XType='U':表示所有用戶(hù)表; 
XType='S':表示所有系統(tǒng)表; 
3.獲取指定表[tb_phone]的所有字段名: 
SELECT name FROM SysColumns WHERE id=Object_Id('tb_phone') 
4.SQL所有表的表名、所有字段名、表字段長(zhǎng)度 
SELECT table_name AS 數(shù)據(jù)表名, 
column_name AS 字段名, 
ISNULL(column_default,'') AS 默認(rèn)值, 
is_nullable AS 是否允許為NULL, 
data_type AS 數(shù)據(jù)類(lèi)型, 
ISNULL(ISNULL(ISNULL(character_maximum_length,numeric_precision),datetime_precision),1) AS 類(lèi)型長(zhǎng)度 
FROM information_schema.columns 
WHERE NOT table_name IN('sysdiagrams','dtproperties') 
5.獲取指定表[tb_phone]的表名，表字段名，字段類(lèi)型和類(lèi)型長(zhǎng)度 
SELECT SysObjects.name AS Tablename, 
Syscolumns.name AS Columnsname, 
Systypes.name AS DateType, 
Syscolumns.length AS DateLength 
FROM Sysproperties RIGHT OUTER JOIN 
Sysobjects INNER JOIN 
Syscolumns ON Sysobjects.id = Syscolumns.id INNER JOIN 
Systypes ON Syscolumns.xtype = Systypes.xtype ON 
Sysproperties.id = Syscolumns.id AND 
Sysproperties.smallid = Syscolumns.colid 
WHERE (Sysobjects.xtype = 'u' OR 
Sysobjects.xtype = 'v') AND (Systypes.name <> 'Sysname') AND 
(Sysobjects.name = 'tb_phone') 
ORDER BY Columnsname 

SQL Server 的每一個(gè)數(shù)據(jù)庫(kù)，無(wú)論是系統(tǒng)數(shù)據(jù)庫(kù)（master,model, sybsystemprocs, tempdb），還是用戶(hù)數(shù)據(jù)庫(kù)，都有自己的transaction log，每個(gè)庫(kù)都有syslogs表。Log記錄用戶(hù)對(duì)數(shù)據(jù)庫(kù)修改的操作，所以如果不用命令清除， log會(huì)一直增長(zhǎng)直至占滿(mǎn)空間。清除log可用dump transaction 命令；或者開(kāi)放數(shù)據(jù)庫(kù)選項(xiàng)trunc log on chkpt，數(shù)據(jù)庫(kù)會(huì)每隔一段間隔自動(dòng)清除log。管理好數(shù)據(jù)庫(kù)log是用戶(hù)操作數(shù)據(jù)庫(kù)必須考慮的一面。
一、刪除LOG
1：分離數(shù)據(jù)庫(kù) 企業(yè)管理器－＞服務(wù)器－＞數(shù)據(jù)庫(kù)－＞右鍵－＞分離數(shù)據(jù)庫(kù)
2：刪除LOG文件
3：附加數(shù)據(jù)庫(kù) 企業(yè)管理器－＞服務(wù)器－＞數(shù)據(jù)庫(kù)－＞右鍵－＞附加數(shù)據(jù)庫(kù)
此法生成新的LOG，大小只有520多K
再將此數(shù)據(jù)庫(kù)設(shè)置自動(dòng)收縮
或用代碼：
下面的示例分離 77169database，然后將 77169database 中的一個(gè)文件附加到當(dāng)前服務(wù)器。

復(fù)制代碼代碼如下:

 
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' 

二、清空日志

復(fù)制代碼代碼如下:

DUMP TRANSACTION 庫(kù)名 WITH NO_LOG

再操作：
企業(yè)管理器--右鍵你要壓縮的數(shù)據(jù)庫(kù)--所有任務(wù)--收縮數(shù)據(jù)庫(kù)--收縮文件--選擇日志文件--在收縮方式里選擇收縮至XXM,這里會(huì)給出一個(gè)允許收縮到的最小M數(shù),直接輸入這個(gè)數(shù),確定就可以了
三、如果想以后不讓它增長(zhǎng)
企業(yè)管理器－＞服務(wù)器－＞數(shù)據(jù)庫(kù)－＞屬性－＞事務(wù)日志－＞將文件增長(zhǎng)限制為2M
自動(dòng)收縮日志,也可以用下面這條語(yǔ)句:
ALTER DATABASE 數(shù)據(jù)庫(kù)名 SET AUTO_SHRINK ON
故障還原模型改為簡(jiǎn)單,用語(yǔ)句是:

復(fù)制代碼代碼如下:

 
USE MASTER 
GO 
ALTER DATABASE 數(shù)據(jù)庫(kù)名 SET RECOVERY SIMPLE 
GO 
------------------------------------------ 
截?cái)嗍聞?wù)日志： 
BACKUP LOG { database_name | @database_name_var } 
{ 
[ WITH 
{ NO_LOG | TRUNCATE_ONLY } ] 
} 
--壓縮日志及數(shù)據(jù)庫(kù)文件大小 
/*--特別注意 
請(qǐng)按步驟進(jìn)行,未進(jìn)行前面的步驟,請(qǐng)不要做后面的步驟，否則可能損壞你的數(shù)據(jù)庫(kù). 
--*/ 

1.清空日志
DUMP TRANSACTION 庫(kù)名 WITH NO_LOG
2.截?cái)嗍聞?wù)日志：
BACKUP LOG 數(shù)據(jù)庫(kù)名 WITH NO_LOG
3.收縮數(shù)據(jù)庫(kù)文件(如果不壓縮,數(shù)據(jù)庫(kù)的文件不會(huì)減小
企業(yè)管理器--右鍵你要壓縮的數(shù)據(jù)庫(kù)--所有任務(wù)--收縮數(shù)據(jù)庫(kù)--收縮文件
--選擇日志文件--在收縮方式里選擇收縮至XXM,這里會(huì)給出一個(gè)允許收縮到的最小M數(shù),直接輸入這個(gè)數(shù),確定就可以了
--選擇數(shù)據(jù)文件--在收縮方式里選擇收縮至XXM,這里會(huì)給出一個(gè)允許收縮到的最小M數(shù),直接輸入這個(gè)數(shù),確定就可以了
也可以用SQL語(yǔ)句來(lái)完成
--收縮數(shù)據(jù)庫(kù)
DBCC SHRINKDATABASE(客戶(hù)資料)
--收縮指定數(shù)據(jù)文件,1是文件號(hào),可以通過(guò)這個(gè)語(yǔ)句查詢(xún)到:
select * from sysfiles DBCC SHRINKFILE(1)
4.為了最大化的縮小日志文件(如果是sql 7.0,這步只能在查詢(xún)分析器中進(jìn)行)
a.分離數(shù)據(jù)庫(kù):
企業(yè)管理器--服務(wù)器--數(shù)據(jù)庫(kù)--右鍵--分離數(shù)據(jù)庫(kù)
b.在我的電腦中刪除LOG文件
c.附加數(shù)據(jù)庫(kù):
企業(yè)管理器--服務(wù)器--數(shù)據(jù)庫(kù)--右鍵--附加數(shù)據(jù)庫(kù) 此法將生成新的LOG，大小只有500多K
或用代碼：
下面的示例分離 77169database，然后將 77169database 中的一個(gè)文件附加到當(dāng)前服務(wù)器。
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.為了以后能自動(dòng)收縮,做如下設(shè)置:
企業(yè)管理器--服務(wù)器--右鍵數(shù)據(jù)庫(kù)--屬性--選項(xiàng)--選擇"自動(dòng)收縮"
--SQL語(yǔ)句設(shè)置方式:
EXEC sp_dboption '數(shù)據(jù)庫(kù)名', 'autoshrink', 'TR'
6.如果想以后不讓它日志增長(zhǎng)得太大
企業(yè)管理器--服務(wù)器--右鍵數(shù)據(jù)庫(kù)--屬性--事務(wù)日志
--將文件增長(zhǎng)限制為xM(x是你允許的最大數(shù)據(jù)文件大小)
--SQL語(yǔ)句的設(shè)置方式:

復(fù)制代碼代碼如下:

 
alter database 數(shù)據(jù)庫(kù)名 modify file(name=邏輯文件名,maxsize=20) 
-------------------------------------------------------------- 
/*--壓縮數(shù)據(jù)庫(kù)的通用存儲(chǔ)過(guò)程 
壓縮日志及數(shù)據(jù)庫(kù)文件大小，因?yàn)橐獙?duì)數(shù)據(jù)庫(kù)進(jìn)行分離處理，所以存儲(chǔ)過(guò)程不能創(chuàng)建在被壓縮的數(shù)據(jù)庫(kù)中。 
/*--調(diào)用示例 
exec p_compdb 'test' 
--*/ 
use master --注意,此存儲(chǔ)過(guò)程要建在master數(shù)據(jù)庫(kù)中 
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, --要壓縮的數(shù)據(jù)庫(kù)名 
@bkdatabase bit=1, --因?yàn)榉蛛x日志的步驟中,可能會(huì)損壞數(shù)據(jù)庫(kù),所以你可以選擇是否自動(dòng)數(shù)據(jù)庫(kù) 
@bkfname nvarchar(260)='' --備份的文件名,如果不指定,自動(dòng)備份到默認(rèn)備份目錄, 
備份文件名為:數(shù)據(jù)庫(kù)名+日期時(shí)間 
as 

--1.清空日志
exec('DUMP TRANSACTION ['+@dbname+'] WITH NO_LOG')
--2.截?cái)嗍聞?wù)日志：
exec('BACKUP LOG ['+@dbname+'] WITH NO_LOG')
--3.收縮數(shù)據(jù)庫(kù)文件(如果不壓縮,數(shù)據(jù)庫(kù)的文件不會(huì)減小
exec('DBCC SHRINKDATABASE(['+@dbname+'])')
--4.設(shè)置自動(dòng)收縮
exec('EXEC sp_dboption '''+@dbname+''',''autoshrink'',''TR''')
--后面的步驟有一定危險(xiǎn),你可以可以選擇是否應(yīng)該這些步驟
--5.分離數(shù)據(jù)庫(kù)

復(fù)制代碼代碼如下:

 
if @bkdatabase=1 
begin 
if isnull(@bkfname,'')='' 
set @bkfname=@dbname+'_'+convert(varchar,getdate(),112) 
+replace(convert(varchar,getdate(),108),':','') 
select 提示信息='備份數(shù)據(jù)庫(kù)到SQL 默認(rèn)備份目錄,備份文件名:'+@bkfname 
exec('backup database ['+@dbname+'] to disk='''+@bkfname+'''') 
end 
--進(jìn)行分離處理 
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 
pen 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 
--附加數(shù)據(jù)庫(kù) 
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 