none
关于设置JOB压缩数据库日志文件 RRS feed

  • 问题

  • 同样的JOB,为什么在其他服务器上执行都能成功,但有一台服务器每次执行都失败呢?

    这是失败的日志:

    日期  2012/1/18 5:00:03
    日志  作业历史记录 (update status)

    步骤 ID  1
    服务器  ICS-SQL16
    作业名称  update status
    步骤名称  update
    持续时间  00:00:07
    SQL 严重性  16
    SQL 消息 ID  15635
    已通过电子邮件通知的操作员  
    已通过网络发送通知的操作员  
    已通过寻呼通知的操作员  
    重试次数  0

    消息
    Executed as user: ICS-SQL16\Administrator. ...date is not necessary... [SQLSTATE 01000] (Message 15653)  , update is not necessary... [SQLSTATE 01000] (Message 15653)  , update is not necessary... [SQLSTATE 01000] (Message 15653)  0 index(es)/statistic(s) have been updated, 3 did not require update. [SQLSTATE 01000] (Message 15651)  Updating [dbo].[ConfigurationInfo] [SQLSTATE 01000] (Message 15650)  , update is not necessary... [SQLSTATE 01000] (Message 15653)  , update is not necessary... [SQLSTATE 01000] (Message 15653)  , update is not necessary... [SQLSTATE 01000] (Message 15653)  0 index(es)/statistic(s) have been updated, 3 did not require update. [SQLSTATE 01000] (Message 15651)  Updating [dbo].[Catalog] [SQLSTATE 01000] (Message 15650)  , update is not necessary... [SQLSTATE 01000] (Message 15653)  , update is not necessary... [SQLSTATE 01000] (Message 15653)  , update is not necessary... [SQLSTATE 01000] (Message 15653)  , update is not necessary... [SQLSTATE 01000] (Message 15653)  , update is not necessary... [SQLSTATE 01000] (Message 15653)  , update is not necessary... [SQLSTATE 01000] (Message 15653)  , update is not necessary... [SQLSTATE 01000] (Message 15653)  0 index(es)/statistic(s) have been updated, 7 did not require update. [SQLSTATE 01000] (Message 15651)  Updating [dbo].[UpgradeInfo] [SQLSTATE 01000] (Message 15650)  , update is not necessary... [SQLSTATE 01000] (Message 15653)  0 index(es)/statistic(s) have been updated, 1 did not require update. [SQLSTATE 01000] (Message 15651)  Updating [dbo].[SubscriptionsBeingDeleted] [SQLSTATE 01000] (Message 15650)  , update is not necessary... [SQLSTATE 01000] (Message 15653)  0 index(es)/statistic(s) have been updated, 1 did not require update. [SQLSTATE 01000] (Message 15651)  Updating [dbo].[ModelDrill] [SQLSTATE 01000] (Message 15650)  , update is not necessary... [SQLSTATE 01000] (Message 15653)  , update is not necessary... [SQLSTATE 01000] (Message 15653)  0 index(es)/statistic(s) have been updated, 2 did not require update. [SQLSTATE 01000] (Message 15651)  Updating [dbo].[Segment] [SQLSTATE 01000] (Message 15650)  , update is not necessary... [SQLSTATE 01000] (Message 15653)  , update is not necessary... [SQLSTATE 01000] (Message 15653)  0 index(es)/statistic(s) have been updated, 2 did not require update. [SQLSTATE 01000] (Message 15651)  Updating [dbo].[ChunkSegmentMapping] [SQLSTATE 01000] (Message 15650)  , update is not necessary... [SQLSTATE 01000] (Message 15653)  , update is not necessary... [SQLSTATE 01000] (Message 15653)  , update is not necessary... [SQLSTATE 01000] (Message 15653)  0 index(es)/statistic(s) have been updated, 3 did not require update. [SQLSTATE 01000] (Message 15651)  Updating [dbo].[ModelPerspective] [SQLSTATE 01000] (Message 15650)  , update is not necessary... [SQLSTATE 01000] (Message 15653)  0 index(es)/statistic(s) have been updated, 1 did not require update. [SQLSTATE 01000] (Message 15651)  Updating [dbo].[CachePolicy] [SQLSTATE 01000] (Message 15650)  , update is not necessary... [SQLSTATE 01000] (Message 15653)  , update is not necessary... [SQLSTATE 01000] (Message 15653)  0 index(es)/statistic(s) have been updated, 2 did not require update. [SQLSTATE 01000] (Message 15651)  Updating [dbo].[SegmentedChunk] [SQLSTATE 01000] (Message 15650)  , update is not necessary... [SQLSTATE 01000] (Message 15653)  , update is not necessary... [SQLSTATE 01000] (Message 15653)  , update is not necessary... [SQLSTATE 01000] (Message 15653)  0 index(es)/statistic(s) have been updated, 3 did not require update. [SQLSTATE 01000] (Message 15651)  Updating [dbo].[Users] [SQLSTATE 01000] (Message 15650)  , update is not necessary... [SQLSTATE 01000] (Message 15653)  , update is not necessary... [SQLSTATE 01000] (Message 15653)  , update is not necessary... [SQLSTATE 01000] (Message 15653)  , update is not necessary... [SQLSTATE 01000] (Message 15653)  , update is not necessary... [SQLSTATE 01000] (Message 15653)...  The step failed.

     

    看得有点晕,求高手指导啊!!!

    这是JOB脚本

    DECLARE  @nvrchrDBName   nvarchar(100)
    DECLARE  @nvrchrSQL    nvarchar(4000)
    
    
    IF DATEPART(hh, GETDATE()) < 8
    BEGIN
    
     DECLARE curMyBackup CURSOR FAST_FORWARD 
     FOR 
     select name from master.dbo.sysdatabases where [dbid]>4 order by [dbid]
     
     open curMyBackup
     fetch curMyBackup into @nvrchrDBName
     
     while (@@fetch_status <> -1)
      begin
      
       --压缩数据库所有文件
       -- dbcc shrinkfile (ics_Log)
       select @nvrchrSQL = 'use ' + @nvrchrDBName +' 
        EXEC sp_updatestats 
        
        DBCC SHRINKDATABASE(N''' + @nvrchrDBName +''', 0) 
         '
         
       exec sp_executesql @nvrchrSQL
       --print @nvrchrSQL
       
       fetch curMyBackup into @nvrchrDBName
      end
     close curMyBackup
     deallocate curMyBackup
    END
    
    

     这是JOB的第一步,每次都失败啊- -

    2012年1月18日 2:07

答案

  • Backup log every 15 minutes or so, that's another way to control log size and you can recover db to point of failure. By the way, sql2k8 supports backup compression so you need much less disk space for backups.
    • 已标记为答案 Corda_杰 2012年1月19日 4:26
    2012年1月18日 13:59

全部回复

  • Run it in ssms query window, you'll get more details. But why you shrink every db in scheduled job? It generates db fragmentation, causes performance issues, and db will grow again. Shouldn't do that on prod server at all.
    2012年1月18日 2:58
  • Run it in ssms query window, you'll get more details. But why you shrink every db in scheduled job? It generates db fragmentation, causes performance issues, and db will grow again. Shouldn't do that on prod server at all.


    白天这些数据库操作很频繁,只能等晚上再手动执行下看是什么原因了。

    因为大部分的数据库我们晚上都会设置JOB进行一些操作,在这些操作之后会产生大量的日志文件,所以才会在这些操作全部完成之后进行日志压缩,不然的话可能会影响第二天的数据操作。

    对于你说的会产生数据碎片影响性能这个问题,在其他服务器上的运行结果来看,这个问题影响不大。

    谢谢!

    2012年1月18日 3:26
  • You are better to do log backup in scheduled intervals instead of shrinking it.
    2012年1月18日 3:57
  • 有些数据库多的话一天能产生80G+的日志文件,这备份确实没办法做。我先尝试下换个用户执行这个JOB吧,谢谢了!
    2012年1月18日 7:25
  • Backup log every 15 minutes or so, that's another way to control log size and you can recover db to point of failure. By the way, sql2k8 supports backup compression so you need much less disk space for backups.
    • 已标记为答案 Corda_杰 2012年1月19日 4:26
    2012年1月18日 13:59
  • 上面的就为压缩日志的脚本

    找到失败的原因了,这台服务器上的有个数据库权限设置有问题。

    谢谢了!

    2012年1月19日 4:28