查看数据库空间页。表空间页。索引空间页。索引碎片。日志空间的使用情况

SQL77 2012-07-10 09:32:03

加精

--111------------------------------------------------------------------------------------------------------------------------------------

--查看数据库所申请的空间以及空间在数据和索引的分布

--1.修改SP_SPACEUSED过程--必须以SP_开头.建立在MASTER底下

--EXEC SP_DBSPACEUSED 'MASTER','FALSE'

ALTER PROCEDURE SP_DBSPACEUSED

@DBNAME SYSNAME	,

@UPDATEUSAGE VARCHAR(5) = FALSE

AS

DECLARE @SQL NVARCHAR(4000)

SET @SQL=

'USE ['+@DBNAME+'];

DECLARE @ID	INT

DECLARE @TYPE	CHARACTER(2)

DECLARE	@PAGES	INT

DECLARE @DBNAME SYSNAME

DECLARE @DBSIZE DEC(15,0)

DECLARE @LOGSIZE DEC(15)

DECLARE @BYTESPERPAGE	DEC(15,0)

DECLARE @PAGESPERMB		DEC(15,0)

DECLARE @UPDATEUSAGE_TEMP VARCHAR(5)





CREATE TABLE #SPT_SPACE

(

	ROWS		INT NULL,

	RESERVED	DEC(15) NULL,

	DATA		DEC(15) NULL,

	INDEXP		DEC(15) NULL,

	UNUSED		DEC(15) NULL

)



IF '''+@UPDATEUSAGE+''' IS NOT NULL

	BEGIN

		SELECT @UPDATEUSAGE_TEMP=LOWER('''+@UPDATEUSAGE+''')



		IF  @UPDATEUSAGE_TEMP NOT IN (''TRUE'',''FALSE'')

			BEGIN

				RAISERROR(15143,-1,-1,@UPDATEUSAGE_TEMP)

			END

	END





IF @UPDATEUSAGE_TEMP = ''TRUE''

BEGIN

	DBCC UPDATEUSAGE(0) WITH NO_INFOMSGS

END



SET NOCOUNT ON

BEGIN

SELECT @DBSIZE = SUM(CONVERT(DEC(15),SIZE)) FROM DBO.SYSFILES WHERE (STATUS & 64 = 0)

SELECT @LOGSIZE = SUM(CONVERT(DEC(15),SIZE)) FROM DBO.SYSFILES WHERE (STATUS & 64 <> 0)

SELECT @BYTESPERPAGE = LOW FROM MASTER.DBO.SPT_VALUES WHERE NUMBER = 1 AND TYPE = ''E''

SELECT @PAGESPERMB = 1048576 / @BYTESPERPAGE

INSERT INTO #SPT_SPACE (RESERVED) SELECT SUM(CONVERT(DEC(15),RESERVED)) FROM SYSINDEXES WHERE INDID IN (0, 1, 255)

SELECT @PAGES = SUM(CONVERT(DEC(15),DPAGES)) FROM SYSINDEXES WHERE INDID < 2

SELECT @PAGES = @PAGES + ISNULL(SUM(CONVERT(DEC(15),USED)), 0) FROM SYSINDEXES WHERE INDID = 255

UPDATE #SPT_SPACE SET DATA = @PAGES

UPDATE #SPT_SPACE SET INDEXP = (SELECT SUM(CONVERT(DEC(15),USED)) FROM SYSINDEXES WHERE INDID IN (0, 1, 255))- DATA 

UPDATE #SPT_SPACE SET UNUSED = RESERVED-(SELECT SUM(CONVERT(DEC(15),USED)) FROM SYSINDEXES WHERE INDID IN (0, 1, 255))

END

SELECT 

	DATABASE_NAME = DB_NAME(),

	DATABASE_SIZE =LTRIM(STR((@DBSIZE + @LOGSIZE) / @PAGESPERMB,15,2) + '' MB''),

	''UNALLOCATED SPACE'' =LTRIM(STR((@DBSIZE -(SELECT SUM(CONVERT(DEC(15),RESERVED))FROM SYSINDEXES WHERE INDID IN (0, 1, 255))) / @PAGESPERMB,15,2)+ '' MB''),

	RESERVED = LTRIM(STR(RESERVED * D.LOW / 1024.,15,0) +'' '' + ''KB''),

	DATA = LTRIM(STR(DATA * D.LOW / 1024.,15,0) +'' '' + ''KB''),

	INDEX_SIZE = LTRIM(STR(INDEXP * D.LOW / 1024.,15,0) +'' '' + ''KB''),

	UNUSED = LTRIM(STR(UNUSED * D.LOW / 1024.,15,0) +'' '' + ''KB'') INTO ##'+@DBNAME+'_DBSPACE

FROM 

	#SPT_SPACE, MASTER.DBO.SPT_VALUES D

WHERE 

	D.NUMBER = 1 AND D.TYPE =''E''

'

EXEC (@SQL)

GO

--上面本来不想插入全局临时表.在外面库里调用插入另一个临时表时会死锁.所以才替换成全局临时.

--本来以为直接改SP_SPACEUSED 加个列就可以了.结果却发现在外面调用时DATABASESIZE是一个定值.所以得全面改造了,哈哈

--DROP TABLE #SP_DBUSED

IF OBJECT_ID('TEMPDB..[#SP_DBUSED]')IS NOT NULL

DROP TABLE #SP_DBUSED

GO

CREATE TABLE #SP_DBUSED

(

ID INT IDENTITY(1,1),

DATABASE_NAME VARCHAR(18),

DATABASE_SIZE VARCHAR(18),

UNALLOCATED_SPACE VARCHAR(18),

RESERVED  VARCHAR(18),

DATA  VARCHAR(18),

INDEX_SIZE  VARCHAR(18),

UNUSED  VARCHAR(18)

)



EXEC SP_MSFOREACHDB 'USE [?]; EXEC  SP_DBSPACEUSED "?", @UPDATEUSAGE = ''TRUE'''

INSERT INTO #SP_DBUSED EXEC SP_MSFOREACHDB 'SELECT * FROM ##?_DBSPACE'

SELECT * FROM #SP_DBUSED



--222-----------------------------------------------------------------------------------------------------------------------------------

GO

--查看数据库用户表所申请的空间以及空间在数据和索引的分布

IF OBJECT_ID('TEMPDB..#SP_TBUSED') IS NOT NULL

DROP TABLE #SP_TBUSED

GO

CREATE TABLE #SP_TBUSED

(

NAME NVARCHAR(20),

ROWS CHAR(11),

RESERVED VARCHAR(18),

DATA VARCHAR(18),

INDEX_SIZE VARCHAR(18),

UNUSED VARCHAR(18)

)

INSERT INTO #SP_TBUSED (NAME,ROWS,RESERVED,DATA,INDEX_SIZE,UNUSED) EXEC SP_MSFOREACHTABLE 'SP_SPACEUSED "?",''TRUE'''

SELECT * FROM #SP_TBUSED

--333-----------------------------------------------------------------------------------------------------------------------------------

--查看所有库的总扩展分区以及所使用的扩展分区.

--建立临时表省略....

EXEC SP_MSFOREACHDB 'USE [?];DBCC SHOWFILESTATS'

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

--查看所有库的日志空间,可以与第一个数据空间通过名字关联.合成一个表.

--建立临时表省略....

DBCC SQLPERF ( LOGSPACE )

--444-----------------------------------------------------------------------------------------------------------------------------------

--查看数据库中所有的表和表索引的页面数,索引的分区数以及索引的碎片信息等.

--具体参考DBCC SHOWCONTIG 相关的帮助--MSDN

--SP_MSFOREACHDB 'USE [?];EXEC SP_MSFOREACHTABLE ''DBCC SHOWCONTIG([?]) WITH TABLERESULTS'''

--因SP_MSFOREACHTABLE过程中游标是局部游标.所以不行.

--建立临时表省略

--EXEC PRINT_DBCCSHOWCONTIG NULL

ALTER PROC PRINT_DBCCSHOWCONTIG(@DBNAME SYSNAME)

AS

IF @DBNAME IS NULL

BEGIN

	DECLARE CUR_DBNAME CURSOR

	FOR

	SELECT NAME FROM MASTER..SYSDATABASES

	

	DECLARE @DB SYSNAME,@SQL NVARCHAR(4000)

	OPEN CUR_DBNAME

	FETCH NEXT FROM CUR_DBNAME INTO @DB

	WHILE @@FETCH_STATUS=0

	BEGIN

		 SET @SQL='USE ['+@DB+'];EXEC SP_MSFOREACHTABLE ''DBCC SHOWCONTIG([?]) WITH TABLERESULTS,ALL_INDEXES'''

		 --PRINT @SQL

		 EXEC(@SQL)

		 SET @SQL=''

		 FETCH NEXT FROM CUR_DBNAME INTO @DB

	END

	CLOSE CUR_DBNAME

	DEALLOCATE CUR_DBNAME

	

END

ELSE

BEGIN

	SET @SQL='USE ['+@DBNAME+'];EXEC SP_MSFOREACHTABLE ''DBCC SHOWCONTIG([?]) WITH TABLERESULTS,ALL_INDEXES'''

	--PRINT @SQL

	EXEC(@SQL)

	SET @SQL=''

END

---555---------------------------------------------------------------------------------------------------------------

--SQL2K5以上可利用动态管理视图SYS.DM_DB_PARTITION_STATS进行统计,方便啊(参考MSDN)

--写成过程就可以整服务器调用.

USE <数据库>

GO

SELECT 

	O.NAME ,

	 SUM (P.RESERVED_PAGE_COUNT) AS RESERVED_PAGE_COUNT,

	

	 SUM (P.USED_PAGE_COUNT) AS USED_PAGE_COUNT,

	

	 SUM ( CASE WHEN (P.INDEX_ID < 2) THEN (P.IN_ROW_DATA_PAGE_COUNT + P.LOB_USED_PAGE_COUNT + P.ROW_OVERFLOW_USED_PAGE_COUNT)

			 ELSE P.LOB_USED_PAGE_COUNT + P.ROW_OVERFLOW_USED_PAGE_COUNT END ) AS DATAPAGES,

	

	 SUM (  CASE WHEN (P.INDEX_ID < 2) THEN ROW_COUNT ELSE 0  END  ) AS ROWCOUNTS



FROM 

	SYS.DM_DB_PARTITION_STATS P 

	INNER JOIN SYS.OBJECTS O ON P.OBJECT_ID = O.OBJECT_ID

GROUP 

	BY O.NAME

 

GO

-----------

--以上是各种查看数据库空间.表空间.索引空间.日志空间,索引碎片等信息的一些方法.

--欢迎各位大虾指正

--查看碎片可利用DBCC SHOWCONTIG 但有性能上的一些影响.

--如果用SP_SPACEUSED第二个参数不为TRUE的话可能数据并不准确.

--需要看哪种信息再选择适应的方法.