December 3, 2014 at 10:20 am
If I count index size and data size for all of the tables in db, it comes close to 100GB. But DB size shows 215 GB. Is the extra size coming through fragmentation? What could be causing it? What can we do reduce the size of the mdf file? We run CDC on this DB.
December 3, 2014 at 10:21 am
Notice that a database is not just tables and indexes.
December 3, 2014 at 10:23 am
what else should I look for? Need to know what is other 100GB comprise of?
December 3, 2014 at 10:44 am
SQL also creates internal tables for certain types of data, including: primary XML indexes,
service broker, change tracking and a few other things. You'll need to include the size of those tables as well.
This should give you a rough idea of the total internal space used, so you can see if that accounts for most of the "missing" space:
SELECT SUM(au.total_pages) AS total_internal_pages
FROM sys.internal_tables AS itab
INNER JOIN sys.indexes AS idx ON
itab.object_id = idx.object_id
--AND idx.index_id IN (0,1)
INNER JOIN sys.partitions AS p ON
p.object_id = idx.object_id AND
p.index_id = idx.index_id
INNER JOIN sys.allocation_units AS au ON
au.container_id = CASE au.type WHEN 2 THEN p.partition_id ELSE p.hobt_id END
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 3, 2014 at 10:55 am
About 40 internal objects that spans on 417 pages. How do I go about getting their sizes?
December 3, 2014 at 10:59 am
417 pages is a trivial size, you can ignore it if that's the total.
Does "DB size shows 215 GB" include log file? If so, what size is the log file?
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 3, 2014 at 11:04 am
No it just mdf file. Log file is about .14 GB
December 3, 2014 at 11:28 am
Ok, let's see what this shows:
IF OBJECT_ID('tempdb.dbo.#showfilestats') IS NOT NULL
DROP TABLE #showfilestats
CREATE TABLE #showfilestats (
Fileid smallint NOT NULL,
FileGroup smallint NULL,
TotalExtents bigint NOT NULL,
UsedExtents bigint NOT NULL,
Unused_GB AS CAST((TotalExtents - UsedExtents) / 16.0 / 1024.0 AS decimal(9, 1)),
Name nvarchar(255) NULL,
FileName nvarchar(255) NOT NULL
)
INSERT INTO #showfilestats ( Fileid, FileGroup, TotalExtents, UsedExtents, Name, FileName)
EXEC('DBCC SHOWFILESTATS')
SELECT *
FROM #showfilestats
ORDER BY Fileid
COMPUTE SUM(TotalExtents), SUM(UsedExtents), SUM(Unused_GB)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 3, 2014 at 12:53 pm
It shows Unused_GB of 13.4
December 3, 2014 at 2:38 pm
I ran the following on the db. And It shows 88 GB is reserved by sysxmitqueue. Do I need to worry about this? If so, how do I get rid of it? We used to use service broker in the past but not anymore.
SELECT Cast(Sum(i.reserved) / 128.0 AS DECIMAL(12,2)) AS MB, o.name
FROM dbo.sysindexes AS i
JOIN dbo.sysobjects AS o
ON (o.id = i.id)
GROUP BY o.name
ORDER BY Sum(i.reserved) DESC
December 3, 2014 at 3:43 pm
If you're not using service broker any more, then it's 100% safe to run this:
ALTER DATABASE msdb SET NEW_BROKER
which should clear that table and allow you to release the space.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 3, 2014 at 3:46 pm
The database in question is different db. Not MSDB. So I need to still run that for MSDB?
December 3, 2014 at 6:40 pm
Also, I ran ALTER DATABASE msdb SET NEW_BROKER...its been running for 30 mins now.....Any idea why is so?
December 3, 2014 at 6:54 pm
also ran this command. It got executed in few secs but the size of the tables doesn't seem to go away.
ALTER DATABASE db_name SET ENABLE_BROKER with rollback immediate
December 4, 2014 at 8:28 am
Anybody?
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply