USE [TEST]
GO
/****** Object: StoredProcedure [dbo].[DBTools_SYS_SHRINKDATABASE] Script Date: 2012/7/12 16:17:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*------------------------------------
-- 用途:对目标数据库的逐个文件进行收缩
-- 项目名称: 数据库优化二期
-- 说明:
-- 时间: 2012-06-14
-- 编写者:
--------------------------------------
-- 修改记录:
-- 编号 修改时间 修改人 修改原因 修改标注
test
declare @IsSuccess int
exec DBTools_SYS_SHRINKDATABASE @Databasename='NDAS',@IsSuccess=@IsSuccess output
print @IsSuccess
------------------------------------*/
ALTER PROCEDURE [dbo].[DBTools_SYS_SHRINKDATABASE]
(
@Databasename nvarchar(20),@IsSuccess int output
)
AS
BEGIN
DECLARE @SQL NVARCHAR(MAX),
@Filename NVARCHAR(20)
CREATE TABLE #Data_Files
(
DataFilename NVARCHAR(100)
)
INSERT INTO #Data_Files
( DataFilename )
SELECT name
FROM SYS.master_files
WHERE database_id=DB_ID(@Databasename)
SET @SQL=N'ALTER DATABASE '+@Databasename+' SET RECOVERY SIMPLE WITH NO_WAIT --设置数据库备份模式为简单备份
'
DECLARE File_Cursor CURSOR
FOR
SELECT
DataFilename
FROM #Data_Files
OPEN File_Cursor
FETCH NEXT FROM File_Cursor INTO @Filename
WHILE @@FETCH_STATUS=0
BEGIN
SET @SQL=@SQL+'DBCC SHRINKFILE('+@Filename+',0,TRUNCATEONLY) --按文件收缩库
'
FETCH NEXT FROM File_Cursor INTO @Filename
END
CLOSE File_Cursor
DEALLOCATE File_Cursor
--SET @SQL=@SQL+'ALTER DATABASE '+@Databasename+' SET AUTO_SHRINK ON WITH NO_WAIT --设置数据库自动收缩
--'
IF @@ERROR<>0
SET @IsSuccess=-1
ELSE
SET @IsSuccess=1
EXEC(@SQL)
--PRINT @SQL
END
对目标数据库的逐个文件进行收缩
最新推荐文章于 2024-04-24 15:44:04 发布