SQL-BOX.COM

It is a good practice to find and monitor your large SQL server tables, we may surprise in some cases where our large audit or logging tables that are not being cleaned up frequently and fill into multiple GB’s of data and may result in higher backup and restore times. Working with your development team on these large tables to see if they are serving any business else can be archived and reduces huge maintenance over head with backup/restores and also saves disk space available.

Here is the script that finds top 10 large tables that are ordered by reserved disk space.

--CREATING STAGING TABLES S
CREATE TABLE MASTER.DBO.SPT_SPACE
(
DBNAME VARCHAR(50) NOT NULL,
OBJID VARCHAR(300) NULL,
ROWS INT NULL,
RESERVED DEC(15) NULL,
DATA DEC(15) NULL,
INDEXP DEC(15) NULL,
UNUSED DEC(15) NULL
)
CREATE TABLE [DBO].[TOP10_LARGE](
[DBNAME] [VARCHAR](50) NOT NULL,
[TABLE_NAME] [SYSNAME] NULL,
[ROWS] [CHAR](11) NULL,
[RESERVED_KB] [VARCHAR](18) NULL,
[DATA_KB] [VARCHAR](18) NULL,
[INDEX_SIZE_KB] [VARCHAR](18) NULL,
[UNUSED_KB] [VARCHAR](18) NULL
) ON [PRIMARY]
EXEC SP_MSFOREACHDB 'USE ?

DECLARE @ID INT
DECLARE @TYPE CHARACTER(2)
DECLARE @PAGES INT
DECLARE @DBNAME SYSNAME
DECLARE @DBSIZE DEC(15,0)
DECLARE @BYTESPERPAGE DEC(15,0)
DECLARE @PAGESPERMB DEC(15,0)

SET NOCOUNT ON

-- CREATE A CURSOR TO LOOP THROUGH THE USER TABLES
DECLARE C_TABLES CURSOR FOR
SELECT ID
FROM SYSOBJECTS
WHERE XTYPE = ''U''

OPEN C_TABLES

FETCH NEXT FROM C_TABLES
INTO @ID

WHILE @@FETCH_STATUS = 0
BEGIN

/* CODE FROM SP_SPACEUSED */
INSERT INTO MASTER.DBO.SPT_SPACE (DBNAME, OBJID, RESERVED)

SELECT DB_NAME(), OBJID = @ID, SUM(RESERVED)
FROM SYSINDEXES
WHERE INDID IN (0, 1, 255)
AND ID = @ID

SELECT @PAGES = SUM(DPAGES)
FROM SYSINDEXES
WHERE INDID < 2
AND ID = @ID
SELECT @PAGES = @PAGES + ISNULL(SUM(USED), 0)
FROM SYSINDEXES
WHERE INDID = 255
AND ID = @ID
UPDATE MASTER.DBO.SPT_SPACE
SET DATA = @PAGES
WHERE OBJID = @ID
/* INDEX: SUM(USED) WHERE INDID IN (0, 1, 255) - DATA */
UPDATE MASTER.DBO.SPT_SPACE
SET INDEXP = (SELECT SUM(USED)
FROM SYSINDEXES
WHERE INDID IN (0, 1, 255)
AND ID = @ID)
- DATA
WHERE OBJID = @ID

/* UNUSED: SUM(RESERVED) - SUM(USED) WHERE INDID IN (0, 1, 255) */
UPDATE MASTER.DBO.SPT_SPACE
SET UNUSED = RESERVED
- (SELECT SUM(USED)
FROM SYSINDEXES
WHERE INDID IN (0, 1, 255)
AND ID = @ID)
WHERE OBJID = @ID

UPDATE MASTER.DBO.SPT_SPACE
SET ROWS = I.ROWS
FROM SYSINDEXES I
WHERE I.INDID < 2
AND I.ID = @ID
AND OBJID = @ID

FETCH NEXT FROM C_TABLES
INTO @ID
END

CLOSE C_TABLES
DEALLOCATE C_TABLES'

EXEC SP_MSFOREACHDB' USE ?
INSERT INTO MASTER.DBO.TOP10_LARGE
SELECT TOP 10
DBNAME,
TABLE_NAME = (SELECT NAME FROM SYS.SYSOBJECTS WHERE ID = OBJID),
ROWS = CONVERT(CHAR(11), ROWS),
RESERVED_KB = LTRIM(STR(RESERVED * D.LOW / 1024.,15,0) ),
DATA_KB = LTRIM(STR(DATA * D.LOW / 1024.,15,0) ),
INDEX_SIZE_KB = LTRIM(STR(INDEXP * D.LOW / 1024.,15,0)),
UNUSED_KB = LTRIM(STR(UNUSED * D.LOW / 1024.,15,0) )

FROM MASTER.DBO.SPT_SPACE A, MASTER.DBO.SPT_VALUES D
WHERE D.NUMBER = 1 AND D.TYPE = ''E'' AND DBNAME = ''?''ORDER BY RESERVED DESC'
GO

SELECT * FROM MASTER.DBO.TOP10_LARGE WHERE DBNAME NOT IN ('MASTER', 'TEMPDB','MSDB', 'MODEL')

--CLEANING UP ALL TABLES.

-- DROP TABLE MASTER.DBO.TOP10_LARGE

-- DROP TABLE MASTER.DBO.SPT_SPACE