none
一个批量收缩数据库日志的脚本 调试了很久,不知道哪里有错误 RRS feed

  • 问题

  • SET NOCOUNT ON 
    USE master
    GO

    DECLARE @DBNAME NVARCHAR(MAX)
    DECLARE @SQL NVARCHAR(MAX)



    --临时表保存数据
    CREATE TABLE #DataBaseServerData
    (
      ID INT IDENTITY(1, 1) ,
      DBNAME NVARCHAR(MAX) ,
      Log_Total_MB DECIMAL(18, 1) NOT NULL ,
      Log_FREE_SPACE_MB DECIMAL(18, 1) NOT NULL 
    )



    --游标
    DECLARE @itemCur CURSOR
    SET 
    @itemCur = CURSOR FOR 
    SELECT name from   SYS.[sysdatabases] WHERE [name] NOT IN ('MASTER','MODEL','TEMPDB','MSDB','ReportServer','ReportServerTempDB','distribution'))


    OPEN @itemCur
    FETCH NEXT FROM @itemCur INTO @DBNAME
    WHILE @@FETCH_STATUS = 0
        BEGIN
        SET @SQL=N'USE ['+@DBNAME+'];'+CHAR(10)
        +'
         DECLARE @TotalLogSpace DECIMAL(18, 1)
         DECLARE @FreeLogSpace DECIMAL(18, 1)
    DECLARE @filename NVARCHAR(MAX)
    DECLARE @CanshrinkSize BIGINT

    SELECT  @TotalLogSpace=(SUM(CONVERT(dec(17, 2), sysfiles.size)) / 128) 
         FROM    dbo.sysfiles AS sysfiles  WHERE [groupid]=0

    SELECT  @FreeLogSpace = ( SUM(( size - FILEPROPERTY(name, ''SpaceUsed'') )) )/ 128.0
         FROM    sys.database_files
         WHERE   [type] = 1

    SELECT @filename=name  FROM sys.database_files WHERE [type]=1
    SET @CanshrinkSize=CAST((@TotalLogSpace-@FreeLogSpace) AS BIGINT)

     ''USE ['+@DBNAME+']''
     ''DBCC SHRINKFILE (['' + @filename + ''],'' + CAST(@CanshrinkSize+1 AS NVARCHAR(MAX)) + '')'''
       EXEC (@SQL)
            FETCH NEXT FROM @itemCur INTO @DBNAME
        END 

    CLOSE @itemCur
    DEALLOCATE @itemCur

    SELECT  *  FROM    [#DataBaseServerData]
    DROP TABLE [#DataBaseServerData]

    执行的时候报错

    “USE [tt2]”附近有语法错误。
    消息 102,级别 15,状态 1,第 18 行
    “USE [tt1]”附近有语法错误。
    消息 102,级别 15,状态 1,第 18 行

      

    Love SQL

    2015年10月14日 2:48

答案

  •  ''USE ['+@DBNAME+']''
     ''DBCC SHRINKFILE (['' + @filename + ''],'' + CAST(@CanshrinkSize+1 AS NVARCHAR(MAX)) + '')'''

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

    DECLARE @sql nvarchar(max);
     SET @sql = ''USE ['+@DBNAME+']''
    SET @sql = @sql+
     ''DBCC SHRINKFILE (['' + @filename + ''],'' + CAST(@CanshrinkSize+1 AS NVARCHAR(MAX)) + '')''

    2015年10月14日 3:27

全部回复

  •  ''USE ['+@DBNAME+']''
     ''DBCC SHRINKFILE (['' + @filename + ''],'' + CAST(@CanshrinkSize+1 AS NVARCHAR(MAX)) + '')'''

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

    DECLARE @sql nvarchar(max);
     SET @sql = ''USE ['+@DBNAME+']''
    SET @sql = @sql+
     ''DBCC SHRINKFILE (['' + @filename + ''],'' + CAST(@CanshrinkSize+1 AS NVARCHAR(MAX)) + '')''

    2015年10月14日 3:27
  • 改了之后,可以了

    USE master
    GO

    DECLARE @DBNAME NVARCHAR(MAX)
    DECLARE @SQL NVARCHAR(MAX)



    --临时表保存数据
    CREATE TABLE #DataBaseServerData
    (
      ID INT IDENTITY(1, 1) ,
      DBNAME NVARCHAR(MAX) ,
      Log_Total_MB DECIMAL(18, 1) NOT NULL ,
      Log_FREE_SPACE_MB DECIMAL(18, 1) NOT NULL 
    )



    --游标
    DECLARE @itemCur CURSOR
    SET 
    @itemCur = CURSOR FOR 
    SELECT name from   SYS.[sysdatabases] WHERE [name] NOT IN ('MASTER','MODEL','TEMPDB','MSDB','ReportServer','ReportServerTempDB','distribution')


    OPEN @itemCur
    FETCH NEXT FROM @itemCur INTO @DBNAME
    WHILE @@FETCH_STATUS = 0
        BEGIN
        SET @SQL=N'USE ['+@DBNAME+'];'+CHAR(10)
        +'
         DECLARE @TotalLogSpace DECIMAL(18, 1)
         DECLARE @FreeLogSpace DECIMAL(18, 1)
    DECLARE @filename NVARCHAR(MAX)
    DECLARE @CanshrinkSize BIGINT
    DECLARE @sql1 nvarchar(MAX)

    SELECT  @TotalLogSpace=(SUM(CONVERT(dec(17, 2), sysfiles.size)) / 128) 
         FROM    dbo.sysfiles AS sysfiles  WHERE [groupid]=0

    SELECT  @FreeLogSpace = ( SUM(( size - FILEPROPERTY(name, ''SpaceUsed'') )) )/ 128.0
         FROM    sys.database_files
         WHERE   [type] = 1

    SELECT @filename=name  FROM sys.database_files WHERE [type]=1
    SET @CanshrinkSize=CAST((@TotalLogSpace-@FreeLogSpace) AS BIGINT)



     SET @sql1 = ''USE ['+@DBNAME+']''
    SET @sql1 = @sql1+
     ''DBCC SHRINKFILE (['' + @filename + ''],'' + CAST(@CanshrinkSize+1 AS NVARCHAR(MAX)) + '')''
      EXEC (@sql1)'
       EXEC (@SQL)
            FETCH NEXT FROM @itemCur INTO @DBNAME
        END 

    CLOSE @itemCur
    DEALLOCATE @itemCur

    SELECT  *  FROM    [#DataBaseServerData]
    DROP TABLE [#DataBaseServerData]


    Love SQL

    2015年10月14日 7:04