March 3, 2011 at 6:29 pm
I am trying to make a test database for developers and for that purpose made a database with 100 person records whereas the production database has about 40,000 such records. I deleted tens of thousands of records from about 40 other tables, left the support tables intact. None of the support tables have over 2000 records.
WHen I finished and backed up, the new DB was only 25% smaller than the production DB! I then tried SHRINKDatabase and gained perhaps another 5 to 10% shrinkage. I was expecting the new DB to be about 10% the size of production. What is the cause for the apparent bloat?
Thanks so much,
Sam
March 3, 2011 at 10:41 pm
Before bringing it over to your test environment. when you did your back up of your Production db, did you also do a log backup? Or was the actualy .MDF file still near the same size after you modified the data from the various tables? Something sounds off to me.
I'd try running something like sp_sos to get a handle on what your objects are doing in the prod db first, and then run it against your test copy afterwards. It'll give you a tally of the space being taken by both data and indexes for all of your tables. I don't think it's in the script library out here, but here's a copy of the code, and a site to give you some particulars on it from the Author, Richard Ding.
sp_sos
USE master;
GO
IF OBJECT_ID(N'dbo.sp_SOS', N'P') IS NOT NULL
DROP PROCEDURE dbo.sp_SOS;
GO
CREATE PROCEDURE dbo.sp_SOS
@DbName sysname = NULL,
@SchemaName sysname = NULL,
@ObjectName sysname = N'%',
@TopClause nvarchar(20) = NULL,
@ObjectType nvarchar(50) = NULL,
@ShowInternalTable nvarchar(3) = NULL,
@OrderBy nvarchar(100) = NULL,
@UpdateUsage bit = 0
AS
/*=================================================================================================
Author: Richard Ding
Created: Mar. 03, 2008
Modified: Mar. 17, 2008
Purpose: Manipulate object size calculation and display for SS 2000/2005/2008
Parameters:
@DbName: default is the current database
@SchemaName: default is null showing all schemas
@ObjectName: default is "%" including all objects in "LIKE" clause
@TopClause: default is null showing all objects. Can be "TOP N" or "TOP N PERCENT"
@ObjectType: default is "S", "U", "V", "SQ" and "IT". All objects that can be sized
@ShowInternalTable: default is "Yes", when listing IT, the Parent excludes it in size
@OrderBy: default is by object name, can be any size related column
@UpdateUsage: default is 0, meaning "do not run DBCC UPDATEUSAGE"
Note: SS 2000/2005/2008 portable using dynamic SQL to bypass validation error;
Use ISNULL to allow prefilled default parameter values;
Use "DBCC UPDATEUSAGE" with caution as it can hold up large databases;
Unicode compatible and case insensitive;
Sample codes:
EXEC dbo.sp_SOS;
EXEC dbo.sp_SOS 'AdventureWorks', NULL, '%', NULL, 'U', 'No', 'T', 1;
sp_SOS 'TRACE', NULL, NULL, Null, ' ,,, ,;SQ,; u ;;;,, v ,,;iT , ;', 'No', N'N', 0;
sp_SOS NULL, NULL, NULL, NULL, 'U', 'Yes', N'U', 1;
sp_SOS 'AdventureWorks', 'Person%', 'Contact%', NULL, 'U', 'no', 'N', 0;
sp_SOS 'AdventureWorks', NULL, NULL, N'Top 100 Percent', 'S', 'yes', N'N', 1;
sp_SOS 'AdventureWorks', NULL, 'xml_index_nodes_309576141_32000', NULL, 'IT', 'yes', 'N', 1;
sp_SOS 'TRACE', NULL, 'Vw_DARS_217_overnight_activity_11142007', ' top 10 ', 'v', 'yes', 'N', 0;
sp_SOS 'AdventureWorks', NULL, 'xml%', ' top 10 ', null, 'yes', 'N', 1;
sp_SOS 'AdventureWorks2008', NULL, 'sales%', NULL, ' ,,; u ;;;,, v ', 'No', N'N', 1;
sp_SOS NULL, NULL, NULL, N'Top 100 Percent', ' ;;Q, U;V,', N'Y', 1;
=================================================================================================*/
SET NOCOUNT ON;
-- Input parameter validity checking
DECLARE @SELECT nvarchar(2500),
@WHERE_Schema nvarchar(200),
@WHERE_Object nvarchar(200),
@WHERE_Type nvarchar(200),
@WHERE_Final nvarchar(1000),
@ID int,
@Version nchar(2),
@String nvarchar(4000),
@Count bigint,
@GroupBy nvarchar(450);
IF ISNULL(@OrderBy, N'N') NOT IN (N'', N'N', N'R', N'T', N'U', N'I', N'D', N'F', N'Y')
BEGIN
RAISERROR (N'Incorrect value for @OrderBy. Valid parameters are:
''N'' --> Listing by object name
''R'' --> Listing by number of records
''T'' --> Listing by total size
''U'' --> Listing by used portion (excluding free space)
''I'' --> Listing by index size
''D'' --> Listing by data size
''F'' --> Listing by unused (free) space
''Y'' --> Listing by object type ', 16, 1)
RETURN (-1)
END;
-- Object Type Validation and Clean up
DECLARE @OTV nvarchar(10), @OTC nvarchar(10);
SELECT @OTV = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(ISNULL(@ObjectType,
N'S, U, V, SQ, IT'), N' ', N''), N',', N''), N';', N''), N'SQ', N''), N'U', N''),
N'V', N''), N'IT', N''), N'S', N'');
IF LEN(@OTV) <> 0 -- only allow comma, semi colon and space around S,U,V,SQ,IT
BEGIN
RAISERROR (N'Parameter error. Choose ''S'', ''U'', ''V'', ''SQ'', ''IT'' or any combination of them,
separated by space, comma or semicolon.
S -> System table;
U -> User table;
V -> Indexed view;
SQ -> Service Queue;
IT -> Internal Table', 16, 1)
RETURN (-1)
END
ELSE -- passed validation
BEGIN
SET @OTC = UPPER(REPLACE(REPLACE(REPLACE(ISNULL(@ObjectType,N'S,U,V,SQ,IT'),N' ',N''),N',',N''),N';',N''))
SELECT @ObjectType = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(ISNULL
(@ObjectType, N'S,U,V,SQ,IT'),N',',N''),N';',N''),N'SQ',N'''QQ'''),N'IT',N'''IT'''),N'S',
N'''S'''),N'U',N'''U'''),N'V',N'''V'''),N'QQ',N'SQ'),N' ',N''),N'''''',N''',''')
END
---- common ----
SELECT @DbName = ISNULL(@DbName, DB_NAME()),
@Version = SUBSTRING(CONVERT(nchar(20), SERVERPROPERTY (N'ProductVersion')), 1,
CHARINDEX(N'.', CONVERT(nchar(20), SERVERPROPERTY (N'ProductVersion')))-1),
@OrderBy = N'ORDER BY [' +
CASE ISNULL(@OrderBy, N'N')
WHEN N'N' THEN N'Object Name] ASC '
WHEN N'R' THEN N'Rows] DESC, [Object Name] ASC '
WHEN N'T' THEN N'Total(MB)] DESC, [Object Name] ASC '
WHEN N'U' THEN N'Used(MB)] DESC, [Object Name] ASC '
WHEN N'I' THEN N'Index(MB)] DESC, [Object Name] ASC '
WHEN N'D' THEN N'Data(MB)] DESC, [Object Name] ASC '
WHEN N'F' THEN N'Unused(MB)] DESC, [Object Name] ASC '
WHEN N'Y' THEN N'Type] ASC, [Object Name] ASC '
END;
---------------------- SS 2000 -----------------------------------
IF @Version = N'8'
BEGIN
SELECT @SELECT = N'USE ' + @DbName + N' SELECT ' + ISNULL(@TopClause, N' ') +
N''''' + USER_NAME(o.uid) + ''.'' + OBJECT_NAME(i.id) + '''' AS ''Object Name'',
o.type AS ''Type'',
MAX(i.[rows]) AS ''Rows'',
CONVERT(dec(10,3), SUM(i.reserved * 8.000/1024)) AS ''Total(MB)'',
CONVERT(dec(10,3), SUM((i.reserved - i.used) * 8.000/1024)) AS ''Unused(MB)'',
CONVERT(dec(10,3), SUM(i.used * 8.000/1024)) AS ''Used(MB)'',
CONVERT(dec(10,3), SUM((i.used - CASE WHEN indid <> 255 THEN i.dpages ELSE i.used END)
* 8.000/1024)) AS ''Index(MB)'',
CONVERT(dec(10,3), SUM(CASE WHEN indid <> 255 THEN i.dpages ELSE i.used END
* 8.000/1024)) AS ''Data(MB)''
FROM dbo.sysindexes i WITH (NOLOCK)
JOIN dbo.sysobjects o WITH (NOLOCK)
ON i.id = o.id
WHERE i.name NOT LIKE ''_WA_Sys_%''
AND i.indid IN (0, 1, 255) AND USER_NAME(o.uid) LIKE ''' + ISNULL(@SchemaName, N'%') + N''' ',
-- SS 2000 calculation as below:
-- "reserved" = total size;
-- "dpages" = data used;
-- "used" = used portion (contains data and index);
-- text or image column: use "used" for data size
-- Nonclustered index take tiny space, somehow it is not counted (see sp_spaceused).
@WHERE_Final = N' AND OBJECT_NAME(i.id) LIKE ''' + ISNULL(@ObjectName, N'%')
+ N''' AND o.type IN (' + @ObjectType + N') ',
@GroupBy = N' GROUP BY '''' + USER_NAME(o.uid) + ''.'' + OBJECT_NAME(i.id) + '''', o.type ',
@String = @SELECT + @WHERE_Final + @GroupBy + @OrderBy
END
------------------- ss 2k5 ------------------------------------------------------
IF @Version IN (N'9', N'10')
BEGIN
SELECT @String = N'
IF OBJECT_ID (''tempdb.dbo.##BO'', ''U'') IS NOT NULL
DROP TABLE dbo.##BO
CREATE TABLE dbo.##BO (
ID int identity,
DOI bigint null, -- Daughter Object Id
DON sysname null, -- Daughter Object Name
DSI int null, -- Daughter Schema Id
DSN sysname null, -- Daughter Schema Name
DOT varchar(10) null, -- Daughter Object Type
DFN sysname null, -- Daughter Full Name
POI bigint null, -- Parent Object Id
PON sysname null, -- Parent Object Name
PSI bigint null, -- Parent Schema Id
PSN sysname null, -- Parent Schema Name
POT varchar(10) null, -- Parent Object Type
PFN sysname null -- Parent Full Name
)
INSERT INTO dbo.##BO (DOI, DSI, DOT, POI)
SELECT object_id, schema_id, type, Parent_object_id
FROM ' + @DbName + N'.sys.objects o WHERE type IN (''S'',''U'',''V'',''SQ'',''IT'')
USE ' + @DbName + N'
UPDATE dbo.##BO SET DON = object_name(DOI), DSN = schema_name(DSI), POI = CASE POI WHEN 0 THEN DOI ELSE POI END
UPDATE dbo.##BO SET PSI = o.schema_id, POT = o.type FROM sys.objects o JOIN dbo.##BO t ON o.object_id = t.POI
UPDATE dbo.##BO SET PON = object_name(POI), PSN = schema_name(PSI), DFN = DSN + ''.'' + DON,
PFN = schema_name(PSI)+ ''.'' + object_name(POI)
'
EXEC (@String)
SELECT
@WHERE_Type = CASE WHEN ISNULL(@ShowInternalTable, N'Yes') = N'Yes' THEN N't.DOT ' ELSE N't.POT ' END,
@SELECT = N'USE ' + @DbName + N'
SELECT ' + ISNULL(@TopClause, N'TOP 100 PERCENT ') +
N' CASE WHEN ''' + isnull(@ShowInternalTable, N'Yes') + N''' = ''Yes'' THEN CASE t.DFN WHEN t.PFN THEN t.PFN
ELSE t.DFN + '' (''+ t.PFN + '')'' END ELSE t.PFN END AS ''Object Name'',
' + @WHERE_Type + N' AS ''Type'',
SUM (CASE WHEN ''' + isnull(@ShowInternalTable, N'Yes') + N''' = ''Yes'' THEN
CASE WHEN (ps.index_id < 2 ) THEN ps.row_count ELSE 0 END
ELSE CASE WHEN (ps.index_id < 2 and t.DON = t.PON) THEN ps.row_count ELSE 0 END END) AS ''Rows'',
SUM (CASE WHEN t.DON NOT LIKE ''fulltext%'' OR t.DON LIKE ''fulltext_index_map%''
THEN ps.reserved_page_count ELSE 0 END)* 8.000/1024 AS ''Total(MB)'',
SUM (CASE WHEN t.DON NOT LIKE ''fulltext%'' OR t.DON LIKE ''fulltext_index_map%''
THEN ps.reserved_page_count ELSE 0 END
- CASE WHEN t.DON NOT LIKE ''fulltext%'' OR t.DON LIKE ''fulltext_index_map%'' THEN
ps.used_page_count ELSE 0 END)* 8.000/1024 AS ''Unused(MB)'',
SUM (CASE WHEN t.DON NOT LIKE ''fulltext%'' OR t.DON LIKE ''fulltext_index_map%''
THEN ps.used_page_count ELSE 0 END)* 8.000/1024 AS ''Used(MB)'',
SUM (CASE WHEN t.DON NOT LIKE ''fulltext%'' OR t.DON LIKE ''fulltext_index_map%''
THEN ps.used_page_count ELSE 0 END
- CASE WHEN t.POT NOT IN (''SQ'',''IT'') AND t.DOT IN (''IT'') and ''' + isnull(@ShowInternalTable, N'Yes')
+ N''' = ''No'' THEN 0 ELSE CASE WHEN (ps.index_id<2)
THEN (ps.in_row_data_page_count+ps.lob_used_page_count+ps.row_overflow_used_page_count)
ELSE ps.lob_used_page_count + ps.row_overflow_used_page_count END END) * 8.000/1024 AS ''Index(MB)'',
SUM (CASE WHEN t.POT NOT IN (''SQ'',''IT'') AND t.DOT IN (''IT'') and ''' + isnull(@ShowInternalTable, N'Yes')
+ N''' = ''No'' THEN 0 ELSE CASE WHEN (ps.index_id<2)
THEN (ps.in_row_data_page_count+ps.lob_used_page_count+ps.row_overflow_used_page_count)
ELSE ps.lob_used_page_count + ps.row_overflow_used_page_count END END) * 8.000/1024 AS ''Data(MB)''
FROM sys.dm_db_partition_stats ps INNER JOIN dbo.##BO t
ON ps.object_id = t.DOI
',
@ObjectType = CASE WHEN ISNULL(@ShowInternalTable, N'Yes') = N'Yes' THEN N'''IT'',' + ISNULL(@ObjectType, N'''S'',''U'',
''V'', ''SQ'', ''IT''') ELSE ISNULL(@ObjectType, N'''S'', ''U'', ''V'', ''SQ'', ''IT''') END,
@WHERE_Schema = CASE WHEN ISNULL(@ShowInternalTable, N'Yes') = N'Yes' THEN N' t.DSN ' ELSE N' t.PSN ' END, -- DSN or PSN
@WHERE_Object = CASE WHEN ISNULL(@ShowInternalTable, N'Yes') = N'Yes' THEN N' t.DON LIKE ''' + ISNULL(@ObjectName, N'%')
+ ''' OR t.PON LIKE ''' + ISNULL(@ObjectName, N'%') + N''' '
ELSE N' t.pon LIKE ''' + ISNULL(@ObjectName, N'%') + N''' ' END, -- DON or PON
@WHERE_Final = N' WHERE (' + @WHERE_Schema + N' LIKE ''' + ISNULL(@SchemaName, N'%') + N''' OR ' + @WHERE_Schema +
N' = ''sys'') AND (' + @WHERE_Object + N' ) AND ' + @WHERE_Type + N' IN (' + @ObjectType + N') ',
@GroupBy = N'GROUP BY CASE WHEN ''' + ISNULL(@ShowInternalTable, N'Yes') + N''' = ''Yes'' THEN CASE t.DFN WHEN t.PFN
THEN t.PFN ELSE t.DFN + '' (''+ t.PFN + '')'' END ELSE t.PFN END, ' + @WHERE_Type + N''
SELECT @String = @SELECT + @WHERE_Final + @GroupBy + @OrderBy
-- SELECT @String AS 'STRING'
END
----- common ------
IF OBJECT_ID(N'tempdb.dbo.##FO', N'U') IS NOT NULL
DROP TABLE dbo.##FO;
CREATE TABLE dbo.##FO (
ID int identity,
[Object Name] sysname,
[Type] varchar(2),
[Rows] bigint,
[Total(MB)] dec(10,3),
[-] nchar(1),
[Unused(MB)] dec(10,3),
[==] nchar(2),
[Used(MB)] dec(10,3),
[=] nchar(1),
[Index(MB)] dec(10,3),
[+] nchar(1),
[Data(MB)] dec(10,3) );
INSERT INTO dbo.##FO ([Object Name], [Type], [Rows], [Total(MB)],[Unused(MB)],[Used(MB)],[Index(MB)],[Data(MB)])
EXEC (@String);
SELECT @Count = COUNT(*) FROM dbo.##FO;
IF @Count = 0
BEGIN
RAISERROR (N'No records were found macthcing your criteria.', 16, 1)
RETURN (-1)
END
ELSE -- There're at least one records
BEGIN
-- Run DBCC UPDATEUSAGE to correct wrong values
IF ISNULL(@UpdateUsage, 0) = 1
BEGIN
SELECT @ObjectName = N'', @ID = 0
WHILE 1 = 1
BEGIN
SELECT TOP 1 @ObjectName = CASE WHEN [Object Name] LIKE N'%(%' THEN
SUBSTRING([Object Name], 1, CHARINDEX(N'(', [Object Name])-2) ELSE [Object Name] END
, @ID = ID FROM dbo.##FO WHERE ID > @ID ORDER BY ID ASC
IF @@ROWCOUNT = 0
BREAK
PRINT N'==> DBCC UPDATEUSAGE (' + @DbName + N', ''' + @ObjectName + N''') WITH COUNT_ROWS'
DBCC UPDATEUSAGE (@DbName, @ObjectName) WITH COUNT_ROWS
PRINT N''
END
PRINT N''
TRUNCATE TABLE dbo.##FO
INSERT INTO dbo.##FO ([Object Name], [Type], [Rows], [Total(MB)],[Unused(MB)],
[Used(MB)],[Index(MB)],[Data(MB)]) EXEC (@String)
END
ELSE
PRINT N'(Warning: Run "DBCC UPDATEUSAGE" on suspicious objects. It may incur overhead on big databases.)'
PRINT N''
UPDATE dbo.##FO SET [-] = N'-', [==] = N'==', [=] = N'=', [+] = N'+'
IF @Count = 1 -- when only 1 row, no need to sum up total
SELECT [Object Name], [Type], [Rows], [Total(MB)],[-], [Unused(MB)],[==], [Used(MB)],[=],
[Index(MB)],[+],[Data(MB)]
FROM dbo.##FO ORDER BY ID ASC
ELSE
BEGIN
SELECT [Object Name], [Type], [Rows], [Total(MB)],[-], [Unused(MB)],[==], [Used(MB)],[=],
[Index(MB)],[+],[Data(MB)]
FROM dbo.##FO ORDER BY ID ASC
COMPUTE SUM([Total(MB)]), SUM([Unused(MB)]), SUM([Used(MB)]), SUM([Index(MB)]), SUM([Data(MB)])
END
END
RETURN (0)
GO
Here is the website with the particulars from the author, Richard Ding.
March 5, 2011 at 6:20 pm
Thanks, This helps.
I also copied the log so that is likely the problem.
Sam
March 8, 2011 at 8:03 am
smknox (3/3/2011)
I am trying to make a test database for developers and for that purpose made a database with 100 person records whereas the production database has about 40,000 such records. I deleted tens of thousands of records from about 40 other tables, left the support tables intact. None of the support tables have over 2000 records.WHen I finished and backed up, the new DB was only 25% smaller than the production DB! I then tried SHRINKDatabase and gained perhaps another 5 to 10% shrinkage. I was expecting the new DB to be about 10% the size of production. What is the cause for the apparent bloat?
Delete statement does not lower High Watermark on affected tables.
I would truncate affected tables then insert desired data - if original Production population is about 40,000 people records and desired Development population is about 100 people records this truncate and insert approach will be much faster and will actually release allocated space.
Hope this helps.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply