June 9, 2009 at 10:30 am
Hi all
I have been running tests on a database to shrink it from 1.5TB down to a more manageable size.
The target size is near 500GB.
As part of this I have migrated a lot of data out of a set of PRIMARY filegroup files and dropped most of them.
I still have 30GB reported in the Disk Usage report for PRIMARY, but I can only account for 7GB in sysindexes (reserved or dbpages for that groupid), even after a full update stats.
Can anyone point out to me where the other 20+ GB would be?
Fragmentation? System tables?
- SQL2008sp1.
Cheers all
Rich
June 9, 2009 at 12:36 pm
Did you run DBCC UPDATEUSAGE already?
A.J.
DBA with an attitude
June 9, 2009 at 12:39 pm
Yeah, update stats, updateusage, dbreindex the works.
Looks like the report uses DBCC FILESTATS.
June 9, 2009 at 2:12 pm
You can try a combination of these two scripts to look at the tables, sizes, and affiliated filegroups.
Don't know if it will necessarily give you a solid answer, but another option to try.
The first will list tables and their associated filegroups.
The second will list tables and their sizes (optional where clause to look at only certain tables, etc)
SELECTo.[name] [object name],
o.[type] [object type],
i.[name] [index name],
i.[index_id] [index id],
f.[name] [filegroup name]
FROM
sys.indexes i
INNER JOIN
sys.filegroups f
ON
i.data_space_id = f.data_space_id
INNER JOIN
sys.all_objects o
ON
i.[object_id] = o.[object_id]
--WHERE i.data_space_id = 2 --* New FileGroup*
where [o].[type] = 'U'
--and [o].[name] = 'tablenamehere'
ORDER BY [filegroup name]
BEGIN try
DECLARE @table_name VARCHAR(500) ;
DECLARE @schema_name VARCHAR(500) ;
DECLARE @tab1 TABLE(
tablename VARCHAR (500) collate database_default
, schemaname VARCHAR(500) collate database_default
);
DECLARE @temp_table TABLE (
tablename sysname
, row_count INT
, reserved VARCHAR(50) collate database_default
, data VARCHAR(50) collate database_default
, index_size VARCHAR(50) collate database_default
, unused VARCHAR(50) collate database_default
);
INSERT INTO @tab1
SELECT t1.name
, t2.name
FROM sys.tables t1
INNER JOIN sys.schemas t2 ON ( t1.schema_id = t2.schema_id );
DECLARE c1 CURSOR FOR
SELECT t2.name + '.' + t1.name
FROM sys.tables t1
INNER JOIN sys.schemas t2 ON ( t1.schema_id = t2.schema_id );
OPEN c1;
FETCH NEXT FROM c1 INTO @table_name;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @table_name = REPLACE(@table_name, '[','');
SET @table_name = REPLACE(@table_name, ']','');
-- make sure the object exists before calling sp_spacedused
IF EXISTS(SELECT OBJECT_ID FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(@table_name))
BEGIN
INSERT INTO @temp_table EXEC sp_spaceused @table_name, false ;
END
FETCH NEXT FROM c1 INTO @table_name;
END;
CLOSE c1;
DEALLOCATE c1;
SELECT t1.*
, t2.schemaname
FROM @temp_table t1
INNER JOIN @tab1 t2 ON (t1.tablename = t2.tablename )
ORDER BYCONVERT(int, LEFT(reserved,(LEN(reserved)-3))) desc,
CONVERT(int, LEFT(data,(LEN(data)-3))) desc,
CONVERT(int, LEFT(index_size,(LEN(index_size)-3))) desc,
row_count desc
END try
BEGIN catch
SELECT -100 AS l1
, ERROR_NUMBER() AS tablename
, ERROR_SEVERITY() AS row_count
, ERROR_STATE() AS reserved
, ERROR_MESSAGE() AS data
, 1 AS index_size, 1 AS unused, 1 AS schemaname
END catch
Regards,
Steve
June 10, 2009 at 3:13 am
Aye, thanks SK, but that still leaves me 20 odd gig out of pocket 🙂
June 10, 2009 at 12:45 pm
Was worth a shot. Thought maybe it would show you a table or something in the primary filegroup that was being overlooked.
Sorry couldn't be of more help.
Steve
June 11, 2009 at 3:37 am
Cheers anyway, it's just rather bugging me.
Rich
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply