September 29, 2010 at 9:15 am
msdb database is currently occupying 77 GB disk space. I checked tables sizes (using Jeff's query) and max table size is around 41 MB. also number of rows in each tables do not exceed 10000.
Free space in the database is few MB.
where is the space gone?
September 29, 2010 at 9:19 am
Do you have a lot of SSIS/DTS packages in there? They are stored in those tables.
September 29, 2010 at 9:24 am
job history. backup history (I think). they add up
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 29, 2010 at 9:27 am
Steve Jones - Editor (9/29/2010)
Do you have a lot of SSIS/DTS packages in there? They are stored in those tables.
Nope. unfortunately not.
Wayne - Those history tables have around 4-5k records which should be fine i think. max table size is 41 MB..
September 29, 2010 at 9:31 am
backupfile table - 10k records
5 other history tables with appx 5k records each
other have few hundred records.
September 29, 2010 at 10:15 am
Run this against your msdb and it will give you an idea what tables are the big ones. If the sum of the ReservedKB column is not adding up close to the 77GB, look at the free space on the DATA and LOG with the query at the bottom of this post
USE msdb
GO
DBCC UPDATEUSAGE(0)
SELECT DBName = DB_NAME(),
SchemaName = SCHEMA_NAME(so.UID), --Comment out if for SQL Server 2000
--SchemaName = USER_NAME(so.UID), --Comment out if for SQL Server 2005
TableName = so.Name,
TableID = so.ID,
MinRowSize = MIN(si.MinLen),
MaxRowSize = MAX(si.XMaxLen),
ReservedKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Reserved ELSE 0 END) * pkb.PageKB,
DataKB = SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.DPages ELSE 0 END) * pkb.PageKB
+ SUM(CASE WHEN si.IndID IN ( 255) THEN ISNULL(si.Used,0) ELSE 0 END) * pkb.PageKB,
IndexKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Used ELSE 0 END) * pkb.PageKB
- SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.DPages ELSE 0 END) * pkb.PageKB
- SUM(CASE WHEN si.IndID IN ( 255) THEN ISNULL(si.Used,0) ELSE 0 END) * pkb.PageKB,
UnusedKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Reserved ELSE 0 END) * pkb.PageKB
- SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Used ELSE 0 END) * pkb.PageKB,
Rows = SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.Rows ELSE 0 END),
RowModCtr = MIN(si.RowModCtr),
HasTextImage = MAX(CASE WHEN si.IndID IN ( 255) THEN 1 ELSE 0 END),
HasClustered = MAX(CASE WHEN si.IndID IN ( 1 ) THEN 1 ELSE 0 END)
FROM dbo.SysObjects so,
dbo.SysIndexes si,
(--Derived table finds page size in KB according to system type
SELECT Low/1024 AS PageKB --1024 is a binary Kilo-byte
FROM Master.dbo.spt_Values
WHERE Number = 1 --Identifies the primary row for the given type
AND Type = 'E' --Identifies row for system type
) pkb
WHERE si.ID = so.ID
AND si.IndID IN (0, --Table w/o Text or Image Data
1, --Table with clustered index
255) --Table w/ Text or Image Data
AND so.XType = 'U' --User Tables
AND PERMISSIONS(so.ID) <> 0
GROUP BY so.Name,
so.UID,
so.ID,
pkb.PageKB
ORDER BY ReservedKB DESC
And, to see the size of the DB and LOG, you can use
USE msdb
GO
SELECT
SUM(ROUND((CAST ( AS NUMERIC)*8/1024),2)) [FileSizeMb]
,SUM(
CASE
WHEN FILEPROPERTY([name], 'SpaceUsed') IS NULL THEN 0
ELSE
ROUND(CAST (FILEPROPERTY([name], 'SpaceUsed')AS NUMERIC)*8/1024,2)
END) [UsedMb]
,CASE
WHEN TYPE = 0 THEN 'ROWS'
WHEN TYPE = 1 THEN 'LOG'
WHEN TYPE = 4 THEN 'FULLTEXT'
END [FileType]
FROM
[sys].[database_files]
WHERE
TYPE>=0
GROUP BY TYPE
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply