收缩mssql数据库日志文件

--执行前请修改第一句 的 数据库名

-------使用在收缩qpkfsj帐套执行

-------执行前先将【qpkfsj】做全部替换为收缩数据库名，并选择其对应的qpkfsj

USE 需执行的数据库名
GO

DECLARE @dbname VARCHAR(50)
SET @dbname=DB_NAME()  
print(N'use '+@dbname)
exec(N'use '+@dbname)

--重建索引
DECLARE B CURSOR FOR 

    SELECT  A.NAME FROM (SELECT * FROM SYSOBJECTS)A,(SELECT * FROM SYSINDEXES) B WHERE A.ID=B.ID AND XTYPE='U' GROUP BY A.NAME

OPEN B
    DECLARE @BM VARCHAR(50)
    DECLARE @SQL VARCHAR(8000)

        FETCH NEXT FROM B INTO @BM

        WHILE(@@FETCH_STATUS<>-1)
    BEGIN

        SET @SQL = 'DBCC DBREINDEX ( ['+@BM+'],'''', 0)'

        EXEC(@SQL)

        PRINT(@SQL)

        FETCH NEXT FROM B INTO @BM
    END
CLOSE B
DEALLOCATE B
GO

--游标修复表自增量记录值
declare curtablename cursor local for
select name from sys.objects where type='u'
open curtablename 
declare @Table_name varchar(100),@idcol_name varchar(100)
DECLARE @idnumber BIGINT
begin
    fetch next from curtablename into @Table_name
    while @@FETCH_STATUS=0
    begin
    fetch next from curtablename into @Table_name
    --select * from syscolumns where id=object_id('salebill') and status=0x80
        IF EXISTS (select * from syscolumns where id=object_id(@Table_name) and status=0x80)
        BEGIN
            SELECT @idcol_name=b.name FROM syscolumns b where id=object_id(@Table_name) and COLUMNPROPERTY(id,name,'IsIdentity')=1
              print '表:'+@Table_name+' 存在标识列:'+@idcol_name+'!'
              DBCC   CHECKIDENT   (@Table_name, RESEED)    ---如果表的当前标识值小于列中存储的最大标识值，则使用标识列中的最大值对其进行重置。
        -- select @idnumber = ident_current(@Table_name)
        -- IF ISNULL(@idnumber,0)=0 SET @idnumber=1
        -- DBCC   CHECKIDENT   (@Table_name,   RESEED,   @idnumber)  ---当前值设置为 new_reseed_value。如果自创建表后没有将行插入该表，则在执行 DBCC CHECKIDENT 后插入的第一行将使用new_reseed_value 作为标识
        END

    end
end
close curtablename
deallocate curtablename

GO

--进行索引碎片整理
SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @execstr   VARCHAR (255)
DECLARE @objectid  INT
DECLARE @indexid   INT
DECLARE @frag      DECIMAL
DECLARE @maxfrag   DECIMAL

--设置最大允许的碎片数量,超过则对索引进行碎片整理
SET @maxfrag = 30.0

CREATE TABLE #fraglist (
   ObjectName CHAR (255),
   ObjectId INT,
   IndexName CHAR (255),
   IndexId INT,
   Lvl INT,
   CountPages INT,
   CountRows INT,
   MinRecSize INT,
   MaxRecSize INT,
   AvgRecSize INT,
   ForRecCount INT,
   Extents INT,
   ExtentSwitches INT,
   AvgFreeBytes INT,
   AvgPageDensity INT,
   ScanDensity DECIMAL,
   BestCount INT,
   ActualCount INT,
   LogicalFrag DECIMAL,
   ExtentFrag DECIMAL)

--获取当前数据库中所有数据表的索引碎片信息,并保存到临时表
DECLARE tables CURSOR FOR SELECT TABLE_NAME   FROM INFORMATION_SCHEMA.TABLES   WHERE TABLE_TYPE = 'BASE TABLE'
OPEN tables
FETCH NEXT FROM tables INTO @tablename
WHILE @@FETCH_STATUS = 0 BEGIN
   INSERT INTO #fraglist 
   EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''') 
      WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
   FETCH NEXT FROM tables INTO @tablename
END
CLOSE tables
DEALLOCATE tables

/*
ObjectName:数据表名称
IndexId:索引ID
LogicalFrag:逻辑碎片值
*/
DECLARE indexes CURSOR FOR SELECT ObjectName, ObjectId, IndexId, LogicalFrag  FROM #fraglist
      WHERE LogicalFrag >= @maxfrag
         AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
OPEN indexes
FETCH NEXT FROM indexes INTO @tablename, @objectid, @indexid, @frag
WHILE @@FETCH_STATUS = 0 BEGIN

  
   PRINT '正在执行 DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
      ' + RTRIM(@indexid) + ') - fragmentation currently '
       + RTRIM(CONVERT(varchar(15),@frag)) + '%'

   --执行索引碎片整理
   SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ', ' + RTRIM(@indexid) + ')'

   EXEC (@execstr)

   FETCH NEXT FROM indexes INTO @tablename, @objectid, @indexid, @frag
END
CLOSE indexes
DEALLOCATE indexes

DROP TABLE #fraglist
GO

--1、收缩zx2014文件

DBCC   SHRINKFILE(1) 

go
DBCC   SHRINKFILE(2) 

go

DECLARE @dbname VARCHAR(50)
SET @dbname=DB_NAME() 
DBCC   SHRINKDATABASE(@dbname,0) 
--2、设置zx2014为自动收缩
go
DECLARE @dbname VARCHAR(50)
SET @dbname=DB_NAME()
--2008后就没有该存储过程了 
EXEC   sp_dboption @dbname,'autoshrink',   'TRUE' 
--2008后使用该方法设置自动收缩数据库
DECLARE @Sql VARCHAR(MAX) 
SET @Sql='' 
select @Sql=@Sql+' alter database '+'['+ name + ']'+' set auto_shrink on'+CHAR(10)+CHAR(13) from sys.databases 
where is_distributor <> 1 and is_auto_shrink_on = 0 and database_id not in (1,2,3,4) and name=@dbname 
exec (@Sql)
PRINT (@Sql)
go

--5、更改增长模式
DECLARE @dbname VARCHAR(50)
SET @dbname=DB_NAME() 
declare @databasefile varchar(50)
declare @SQL nvarchar(4000)
set @databasefile=(select LTRIM(RTRIM(name)) from sysfiles where fileid=1)
set @SQL='
ALTER DATABASE '+@dbname+'

MODIFY FILE 

(

    NAME = '+@databasefile+', --mdf 数据文件逻辑文件名

    FILEGROWTH = 100MB

) 
'
print (@sql)
exec (@sql)

set @databasefile=(select LTRIM(RTRIM(name)) from sysfiles where fileid=2)
set @SQL='
ALTER DATABASE '+@dbname+'

MODIFY FILE 

(

    NAME = '+@databasefile+',-- ldf 日志文件逻辑文件名

    FILEGROWTH = 100MB

) '
print (@sql)
exec (@sql)

--收缩日志
set @databasefile=(select LTRIM(RTRIM(name)) from sysfiles where fileid=2)
set @SQL='
USE '+@dbname+' 
ALTER DATABASE '+@dbname+' SET RECOVERY SIMPLE WITH NO_WAIT
ALTER DATABASE '+@dbname+' SET RECOVERY SIMPLE

DBCC SHRINKFILE (N'''+@databasefile+''' , 0,TRUNCATEONLY)
ALTER DATABASE '+@dbname+' SET RECOVERY FULL WITH NO_WAIT
ALTER DATABASE '+@dbname+' SET RECOVERY FULL
'
print (@sql)
exec (@sql)

GO

---------------处理优化结束--------------------

--清除日志文件
DELETE from dbo.webLog where datediff(dd,datetime,getdate())>30  
DELETE from dbo.sqlLog where datediff(dd,datetime,getdate())>30 
GO