SQL server 日志太大的处理方法

 

--
最好备份日志，以后可通过日志恢复数据。。。



以下为日志处理方法

一般不建议做第4,6两步

第4步不安全,有可能损坏数据库或丢失数据

第6步如果日志达到上限,则以后的数据库处理会失败,在清理日志后才能恢复.


--
*/






--
下面的所有库名都指你要处理的数据库的库名






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.分离


EXEC
 sp_detach_db 
@dbname
 
=
 
'
库名
'




b.删除日志文件



c.再附加


EXEC
 sp_attach_single_file_db 
@dbname
 
=
 
'
库名
'
, 


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





5
.为了以后能自动收缩,做如下设置:

企业管理器
--
服务器--右键数据库--属性--选项--选择"自动收缩"






--
SQL语句设置方式:



EXEC
 sp_dboption 
'
库名
'
, 
'
autoshrink
'
, 
'
TRUE
'





6
.如果想以后不让它日志增长得太大

企业管理器
--
服务器--右键数据库--属性--事务日志


--
将文件增长限制为xM(x是你允许的最大数据文件大小)






--
SQL语句的设置方式:



alter
 
database
 库名 modify 
file
(name
=
逻辑文件名,maxsize
=
20
)








--
首先,用下面的语句,检查/修复数据库/并整理索引碎片(需要比较长的时间,请在系统空闲时进行此工作)   



    

  
USE
   MASTER   

  
GO
   

    

  sp_dboption   
'
你的数据库名
'
,   
'
single   user
'
,   
'
true
'
   

  
Go
   

    

  
DBCC
   CHECKDB(
'
你的数据库名
'
,   REPAIR_REBUILD)     

  
Go
   

    

  
USE
   你的数据库名   

  
go
   

    

  
exec
   sp_msforeachtable   
'
DBCC   CHECKTABLE(
''
?
''
,REPAIR_REBUILD)
'
   

  
exec
   sp_msforeachtable   
'
DBCC   DBREINDEX(
''
?
''
)
'
   

  
go
   

    

  sp_dboption   
'
你的数据库名
'
,   
'
single   user
'
,   
'
false
'
   

  
Go
  






--
然后用这个压缩数据库日志文件.



SET
 NOCOUNT 
ON



DECLARE
 
@LogicalFileName
 sysname,


@MaxMinutes
 
INT
,


@NewSize
 
INT





--
Select * From Sysfiles



USE
 YSERP 
--
 要操作的数据库名



SELECT
 
@LogicalFileName
 
=
  
'
YSERP_Log
'
 , 
--
 日志文件名



@MaxMinutes
 
=
 
10
, 
--
 Limit on time allowed to wrap log.



@NewSize
 
=
 
20
 
--
 你想设定的日志文件的大小(M)






--
 Setup / initialize



DECLARE
 
@OriginalSize
 
int



SELECT
 
@OriginalSize
 
=
 size


FROM
 sysfiles


WHERE
 name 
=
 
@LogicalFileName



SELECT
  
'
 Original Size of  
'
  
+
 
db_name
() 
+
  
'
  LOG is  
'
  
+



CONVERT
(
VARCHAR
(
30
),
@OriginalSize
) 
+
  
'
  8K pages or  
'
  
+



CONVERT
(
VARCHAR
(
30
),(
@OriginalSize
*
8
/
1024
)) 
+
  
'
 MB 
'
 


FROM
 sysfiles


WHERE
 name 
=
 
@LogicalFileName



CREATE
 
TABLE
 DummyTrans

(DummyColumn 
char
 (
8000
) 
not
 
null
)






DECLARE
 
@Counter
 
INT
,


@StartTime
 
DATETIME
,


@TruncLog
 
VARCHAR
(
255
)


SELECT
 
@StartTime
 
=
 
GETDATE
(),


@TruncLog
 
=
  
'
 BACKUP LOG  
'
  
+
 
db_name
() 
+
  
'
  WITH TRUNCATE_ONLY 
'
 




DBCC
 SHRINKFILE (
@LogicalFileName
, 
@NewSize
)


EXEC
 (
@TruncLog
)


--
 Wrap the log if necessary.



WHILE
 
@MaxMinutes
 
>
 
DATEDIFF
 (mi, 
@StartTime
, 
GETDATE
()) 
--
 time has not


/*
expired
*/
 
AND
 
@OriginalSize
 
=
 (
SELECT
 size 
FROM
 sysfiles 
WHERE
 name 
=
@LogicalFileName
) 
AND
 (
@OriginalSize
 
*
 
8
 
/
1024
) 
>
 
@NewSize



BEGIN
 
--
 Outer loop.



SELECT
 
@Counter
 
=
 
0



WHILE
 ((
@Counter
 
<
 
@OriginalSize
 
/
 
16
) 
AND
 (
@Counter
 
<
 
50000
))


BEGIN
 
--
 update



INSERT
 DummyTrans 
VALUES
 ( 
'
 Fill Log 
'
 )


DELETE
 DummyTrans


SELECT
 
@Counter
 
=
 
@Counter
 
+
 
1



END



EXEC
 (
@TruncLog
)


END



SELECT
  
'
 Final Size of  
'
  
+
 
db_name
() 
+
  
'
  LOG is  
'
  
+



CONVERT
(
VARCHAR
(
30
),size) 
+
  
'
  8K pages or  
'
  
+



CONVERT
(
VARCHAR
(
30
),(size
*
8
/
1024
)) 
+
  
'
 MB 
'
 


FROM
 sysfiles


WHERE
 name 
=
 
@LogicalFileName



DROP
 
TABLE
 DummyTrans


SET
 NOCOUNT 
OFF





--
其次是压缩主数据文件





企业管理器
->
所有任务
->
压缩数据库
->
压缩文件
->
文件
->
压缩到最终大小
->
输入最小值即可