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

--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的话可能数据并不准确.
--需要看哪种信息再选择适应的方法.

相关推荐
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页