April 22, 2010 at 8:07 am
Hello,
Is anyone knows<
I was ask to find out what of 10G in the database, is there any code that I can run to see this information?
Thank you
April 22, 2010 at 10:12 am
if you refer to the size of DB, the comand is sp_helpdb or sp_helpdb 'DBName', I tell you will put more information about your question
Angel Miranda Nieto
DBA Specialist, BI, MCSA SQL Server
April 22, 2010 at 11:13 am
I'm not sure what your question is. Can you please rephrase?
This shows logical files for a given database, space used, and freespace:
SELECT name AS NameOfFile,
size/128.0 as TotalSizeInMB,
CAST(FILEPROPERTY(name, 'SpaceUsed' )AS int)/128.0 AS SpacesUsedInMB,
size/128.0 -CAST(FILEPROPERTY(name, 'SpaceUsed' )AS int)/128.0 AS AvailableSpaceInMB
FROM dbo.SYSFILES
us can use sp_msforeachdb to get this information for all databases:
sp_msforeachdb 'use ?
SELECT name AS NameOfFile,
size/128.0 as TotalSizeInMB,
CAST(FILEPROPERTY(name, ''SpaceUsed'' )AS int)/128.0 AS SpacesUsedInMB,
size/128.0 -CAST(FILEPROPERTY(name, ''SpaceUsed'' )AS int)/128.0 AS AvailableSpaceInMB
FROM dbo.SYSFILES'
April 22, 2010 at 11:41 am
Krasavita (4/22/2010)
Hello,Is anyone knows<
I was ask to find out what of 10G in the database, is there any code that I can run to see this information?
Thank you
What's 10G ?
Are u taking about Oracle ....
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
April 22, 2010 at 12:10 pm
I have for my database 10,303 MB, I want to know what includes in this data for each table.
April 22, 2010 at 12:17 pm
some will be data, some will be free space, and some will be indexes. Following script will find 25 biggest tables... you can of course change the "select top 25" to be a larger number.
/**************************************************************************************
*
* BigTables.sql
* Bill Graziano (SQLTeam.com)
* v1.1
*
**************************************************************************************/
declare @idint
declare @typecharacter(2)
declare@pagesint
declare @dbname sysname
declare @dbsize dec(15,0)
declare @bytesperpagedec(15,0)
declare @pagesperMBdec(15,0)
create table #spt_space
(
objidint null,
rowsint null,
reserveddec(15) null,
datadec(15) null,
indexpdec(15) null,
unuseddec(15) null
)
set nocount on
-- Create a cursor to loop through the user tables
declare c_tables cursor for
selectid
fromsysobjects
wherextype = 'U'
open c_tables
fetch next from c_tables
into @id
while @@fetch_status = 0
begin
/* Code from sp_spaceused */
insert into #spt_space (objid, reserved)
select 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 #spt_space
set data = @pages
where objid = @id
/* index: sum(used) where indid in (0, 1, 255) - data */
update #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 #spt_space
set unused = reserved
- (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
where objid = @id
update #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
select top 25
Table_Name = (select left(name,25) from sysobjects where id = objid),
rows = convert(char(11), rows),
reserved_KB = ltrim(str(reserved * d.low / 1024.,15,0) + ' ' + 'KB'),
data_KB = ltrim(str(data * d.low / 1024.,15,0) + ' ' + 'KB'),
index_size_KB = ltrim(str(indexp * d.low / 1024.,15,0) + ' ' + 'KB'),
unused_KB = ltrim(str(unused * d.low / 1024.,15,0) + ' ' + 'KB')
from #spt_space, master.dbo.spt_values d
where d.number = 1
and d.type = 'E'
order by reserved desc
drop table #spt_space
close c_tables
deallocate c_tables
April 28, 2010 at 6:41 am
I found a table which takes the most spave in my db:
rowsreserved_KBdata_KBindex_size_KBunused_KB
821656 361200 KB264088 KB84472 KB12640 KB
I am deleting data unused, would this eliminate size of 10G of db?
April 28, 2010 at 6:49 am
Looking at your result indicates that this particular table only accounts for 352 MB of space in your database (reserved space = total space the table takes in your db).
Can you please post the result of the following:
SELECT name AS NameOfFile,
size/128.0 as TotalSizeInMB,
CAST(FILEPROPERTY(name, 'SpaceUsed' )AS int)/128.0 AS SpacesUsedInMB,
size/128.0 -CAST(FILEPROPERTY(name, 'SpaceUsed' )AS int)/128.0 AS AvailableSpaceInMB
FROM dbo.SYSFILES
The result from this will show how much space is truely used in your database and any free space that might be there.
April 28, 2010 at 7:15 am
NameOfFileTotalSizeInMBSpacesUsedInMBAvailableSpaceInMB
cs_Dot11330.1875008325.4375003004.750000
cs_Dot_log672.250000119.398437552.851563
April 28, 2010 at 7:22 am
So, your data file is about 11GB- 8GB is actual data and 3GB is free space. That's about appropriate- you want that free space in there for performance reasons.
Can you post the results of the other query that shows table sizes- I'd like to see if the largest table really < 400MB, or could another table be where most of that data is?
April 28, 2010 at 7:29 am
Table_Namerowsreserved_KBdata_KBindex_size_KBunused_KB
table 1 725757 352368 KB264088 KB75248 KB13032 KB
table 242157 1816 KB1488 KB32 KB296 KB
table 316268 1200 KB720 KB344 KB136 KB
table 44476 472 KB168 KB176 KB128 KB
table 51014 368 KB168 KB120 KB80 KB
table 61226 312 KB40 KB216 KB56 KB
table 7361 304 KB216 KB56 KB32 KB
table 81574 248 KB128 KB64 KB56 KB
table 9890 216 KB32 KB184 KB0 KB
table 10798 200 KB96 KB72 KB32 KB
table 11340 152 KB88 KB32 KB32 KB
table 12119 112 KB16 KB48 KB48 KB
table 1371 112 KB24 KB48 KB40 KB
table 14204 96 KB8 KB88 KB0 KB
table 1576 96 KB40 KB56 KB0 KB
table 420 96 KB32 KB16 KB48 KB
HtmlText 16314 88 KB64 KB24 KB0 KB
ModuleControls199 88 KB32 KB56 KB0 KB
Lists339 80 KB32 KB16 KB32 KB
Roles20 72 KB16 KB56 KB0 KB
ScheduleHistory159 72 KB24 KB48 KB0 KB
UserProfile106 72 KB32 KB40 KB0 KB
CoremetricsSettings157 64 KB56 KB8 KB0 KB
aspnet_Applications1 64 KB8 KB56 KB0 KB
aspnet_Users24 48 KB8 KB40 KB0 KB
aspnet_Membership24 48 KB16 KB32 KB0 KB
Announcements2 48 KB8 KB40 KB0 KB
Blog_Entries4 48 KB8 KB40 KB0 KB
Blog_Blogs2 48 KB8 KB40 KB0 KB
UserPortals21 48 KB8 KB40 KB0 KB
Links22 48 KB8 KB40 KB0 KB
UserRoles58 48 KB8 KB40 KB0 KB
Documents1 48 KB8 KB40 KB0 KB
Packages2 32 KB8 KB24 KB0 KB
Portals5 32 KB8 KB24 KB0 KB
Permission6 32 KB8 KB24 KB0 KB
Users24 32 KB8 KB24 KB0 KB
SearchTerms261 32 KB16 KB16 KB0 KB
Version22 32 KB8 KB24 KB0 KB
EventLogTypes47 32 KB16 KB16 KB0 KB
Blog_Comments0 32 KB8 KB24 KB0 KB
EventLogConfig11 32 KB8 KB24 KB0 KB
FAQs20 32 KB8 KB24 KB0 KB
UrlTracking2 32 KB8 KB24 KB0 KB
PortalAlias20 32 KB8 KB24 KB0 KB
Folders85 32 KB8 KB24 KB0 KB
SearchCommonWords369 32 KB16 KB16 KB0 KB
Urls1 32 KB8 KB24 KB0 KB
Profile22 32 KB8 KB24 KB0 KB
DefaultSearchResults11 24 KB16 KB8 KB0 KB
Redirection5 16 KB8 KB8 KB0 KB
HostSettings53 16 KB8 KB8 KB0 KB
WebServers5 16 KB8 KB8 KB0 KB
DocumentsSettings1 16 KB8 KB8 KB0 KB
Wiki_Topic25 16 KB8 KB8 KB0 KB
Wiki_TopicHistory44 16 KB8 KB8 KB0 KB
PackageTypes6 16 KB8 KB8 KB0 KB
Skins2 16 KB8 KB8 KB0 KB
Assemblies3 16 KB8 KB8 KB0 KB
aspnet_SchemaVersions4 16 KB8 KB8 KB0 KB
TopNewProductsSettings5 16 KB8 KB8 KB0 KB
Dashboard_Controls6 16 KB8 KB8 KB0 KB
TabModuleSettings9 16 KB8 KB8 KB0 KB
Authentication3 16 KB8 KB8 KB0 KB
FAQsCategory1 16 KB8 KB8 KB0 KB
ProductCategorySearchTerm69 16 KB8 KB8 KB0 KB
ProductCategoryModules10 16 KB8 KB8 KB0 KB
Blog_Settings2 16 KB8 KB8 KB0 KB
PopularProductsSettings10 16 KB8 KB8 KB0 KB
SearchIndexer1 16 KB8 KB8 KB0 KB
schema_info1 16 KB8 KB8 KB0 KB
Schedule7 16 KB8 KB8 KB0 KB
EventQueue15 16 KB8 KB8 KB0 KB
Blog_MetaWeblogData1 16 KB8 KB8 KB0 KB
Address5 16 KB8 KB8 KB0 KB
SearchPageSettings9 16 KB8 KB8 KB0 KB
FinancialInstitution5 16 KB8 KB8 KB0 KB
Categories28 16 KB8 KB8 KB0 KB
Sales1 16 KB8 KB8 KB0 KB
AnonymousUsers0 0 KB0 KB0 KB0 KB
FI_Catalog0 0 KB0 KB0 KB0 KB
PortalDesktopModules0 0 KB0 KB0 KB0 KB
RoleGroups0 0 KB0 KB0 KB0 KB
UrlLog0 0 KB0 KB0 KB0 KB
SiteLog0 0 KB0 KB0 KB0 KB
Classification0 0 KB0 KB0 KB0 KB
IFrame_Parameters0 0 KB0 KB0 KB0 KB
UsersOnline0 0 KB0 KB0 KB0 KB
FIHTMLText0 0 KB0 KB0 KB0 KB
ScheduleItemSettings0 0 KB0 KB0 KB0 KB
Vendors0 0 KB0 KB0 KB0 KB
Affiliates0 0 KB0 KB0 KB0 KB
UserAuthentication0 0 KB0 KB0 KB0 KB
Banners0 0 KB0 KB0 KB0 KB
aspnet_UsersInRoles0 0 KB0 KB0 KB0 KB
Wiki_CommentParents0 0 KB0 KB0 KB0 KB
Wiki_Comments0 0 KB0 KB0 KB0 KB
aspnet_Roles0 0 KB0 KB0 KB0 KB
SystemMessages0 0 KB0 KB0 KB0 KB
aspnet_Profile0 0 KB0 KB0 KB0 KB
Wiki_Settings0 0 KB0 KB0 KB0 KB
VendorClassification0 0 KB0 KB0 KB0 KB
April 28, 2010 at 8:00 am
Hmm- this is indeed strange. According to these results, tables only take up <400MB. What is taking so much space in your database? Do you have many- I mean LOTS- of indexed views? hmm- lets try this. Compile the following stored procedure (from http://searchsqlserver.techtarget.com/tip/Find-size-of-SQL-Server-tables-and-other-objects-with-stored-procedure), then execute it using
sp_SOS @dbname= 'DATABASENAME',@orderby='T'
STORED procedure definition follows:
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
April 28, 2010 at 8:55 am
Object NameTypeRowsTotal(MB)-Unused(MB)==Used(MB)=Index(MB)+Data(MB)
dbo.AddressU 50.016-0.000==0.016=0.008+0.008
dbo.AffiliatesU 00.000-0.000==0.000=0.000+0.000
dbo.AnnouncementsU 20.070-0.000==0.070=0.039+0.031
dbo.AnonymousUsersU 00.000-0.000==0.000=0.000+0.000
dbo.aspnet_ApplicationsU 10.063-0.000==0.063=0.055+0.008
dbo.aspnet_MembershipU 240.047-0.000==0.047=0.031+0.016
dbo.aspnet_ProfileU 00.000-0.000==0.000=0.000+0.000
dbo.aspnet_RolesU 00.000-0.000==0.000=0.000+0.000
dbo.aspnet_SchemaVersionsU 40.016-0.000==0.016=0.008+0.008
dbo.aspnet_UsersU 240.047-0.000==0.047=0.039+0.008
dbo.aspnet_UsersInRolesU 00.000-0.000==0.000=0.000+0.000
dbo.AssembliesU 30.016-0.000==0.016=0.008+0.008
dbo.AuthenticationU 30.016-0.000==0.016=0.008+0.008
dbo.BannersU 00.000-0.000==0.000=0.000+0.000
dbo.Blog_BlogsU 20.047-0.000==0.047=0.039+0.008
dbo.Blog_CommentsU 00.039-0.000==0.039=0.023+0.016
dbo.Blog_EntriesU 40.125-0.047==0.078=0.039+0.039
dbo.Blog_MetaWeblogDataU 10.016-0.000==0.016=0.008+0.008
dbo.Blog_SettingsU 20.016-0.000==0.016=0.008+0.008
dbo.CategoriesU 280.016-0.000==0.016=0.008+0.008
dbo.ClassificationU 00.000-0.000==0.000=0.000+0.000
dbo.CoremetricsSettingsU 1570.063-0.000==0.063=0.008+0.055
dbo.Dashboard_ControlsU 60.016-0.000==0.016=0.008+0.008
dbo.DefaultSearchResultsU 110.023-0.000==0.023=0.008+0.016
dbo.DesktopModulesU 710.109-0.039==0.070=0.047+0.023
dbo.DocumentsU 10.047-0.000==0.047=0.039+0.008
dbo.DocumentsSettingsU 10.016-0.000==0.016=0.008+0.008
dbo.EventLogU 5616815726.102-994.203==4731.898=56.813+4675.086
dbo.EventLogConfigU 110.031-0.000==0.031=0.023+0.008
dbo.EventLogTypesU 470.031-0.000==0.031=0.016+0.016
dbo.EventQueueU 150.078-0.000==0.078=0.008+0.070
dbo.FAQsU 200.070-0.000==0.070=0.023+0.047
dbo.FAQsCategoryU 10.016-0.000==0.016=0.008+0.008
dbo.FI_CatalogU 00.000-0.000==0.000=0.000+0.000
dbo.FIHTMLTextU 00.000-0.000==0.000=0.000+0.000
dbo.FilesU 10140.359-0.078==0.281=0.117+0.164
dbo.FinancialInstitutionU 50.016-0.000==0.016=0.008+0.008
dbo.FolderPermissionU 2040.094-0.000==0.094=0.086+0.008
dbo.FoldersU 850.031-0.000==0.031=0.023+0.008
dbo.HostSettingsU 530.016-0.000==0.016=0.008+0.008
dbo.HtmlTextU 3143.672-0.219==3.453=0.023+3.430
dbo.IFrame_ParametersU 00.000-0.000==0.000=0.000+0.000
dbo.LinksU 220.047-0.000==0.047=0.039+0.008
dbo.ListsU 3390.078-0.031==0.047=0.016+0.031
dbo.ModuleControlsU 1990.086-0.000==0.086=0.055+0.031
dbo.ModuleDefinitionsU 760.094-0.000==0.094=0.055+0.039
dbo.ModulePermissionU 8900.211-0.000==0.211=0.180+0.031
dbo.ModulesU 7980.328-0.078==0.250=0.070+0.180
dbo.ModuleSettingsU 4200.094-0.047==0.047=0.016+0.031
dbo.PackagesU 20.063-0.000==0.063=0.023+0.039
dbo.PackageTypesU 60.016-0.000==0.016=0.008+0.008
dbo.PermissionU 60.031-0.000==0.031=0.023+0.008
dbo.PopularProductsSettingsU 100.016-0.000==0.016=0.008+0.008
dbo.PortalAliasU 200.031-0.000==0.031=0.023+0.008
dbo.PortalDesktopModulesU 00.000-0.000==0.000=0.000+0.000
dbo.PortalsU 50.031-0.000==0.031=0.023+0.008
dbo.ProductCategoryModulesU 100.016-0.000==0.016=0.008+0.008
dbo.ProductCategorySearchTermsU 690.016-0.000==0.016=0.008+0.008
dbo.ProfileU 220.063-0.000==0.063=0.023+0.039
dbo.ProfilePropertyDefinitionU 1190.133-0.047==0.086=0.047+0.039
dbo.RedirectionU 50.016-0.000==0.016=0.008+0.008
dbo.RoleGroupsU 00.000-0.000==0.000=0.000+0.000
dbo.RolesU 200.070-0.000==0.070=0.055+0.016
dbo.SalesU 10.016-0.000==0.016=0.008+0.008
dbo.ScheduleU 70.016-0.000==0.016=0.008+0.008
dbo.ScheduleHistoryU 1620.125-0.000==0.125=0.047+0.078
dbo.ScheduleItemSettingsU 00.000-0.000==0.000=0.000+0.000
dbo.schema_infoU 10.016-0.000==0.016=0.008+0.008
dbo.SearchCommonWordsU 3690.031-0.000==0.031=0.016+0.016
dbo.SearchIndexerU 10.016-0.000==0.016=0.008+0.008
dbo.SearchItemU 3440.148-0.031==0.117=0.031+0.086
dbo.SearchItemWordU 163451.172-0.133==1.039=0.336+0.703
dbo.SearchItemWordPositionU 423421.773-0.297==1.477=0.031+1.445
dbo.SearchPageSettingsU 90.031-0.000==0.031=0.008+0.023
dbo.SearchTermsU 2610.031-0.000==0.031=0.016+0.016
dbo.SearchWordU 44760.461-0.125==0.336=0.172+0.164
dbo.SiteLogU 00.000-0.000==0.000=0.000+0.000
dbo.SkinsU 20.016-0.000==0.016=0.008+0.008
dbo.SystemMessagesU 00.000-0.000==0.000=0.000+0.000
dbo.TabModulesU 15740.242-0.055==0.188=0.063+0.125
dbo.TabModuleSettingsU 90.016-0.000==0.016=0.008+0.008
dbo.TabPermissionU 12260.305-0.055==0.250=0.211+0.039
dbo.TabsU 3610.297-0.031==0.266=0.055+0.211
dbo.TopNewProductsSettingsU 50.016-0.000==0.016=0.008+0.008
dbo.UrlLogU 00.000-0.000==0.000=0.000+0.000
dbo.UrlsU 10.031-0.000==0.031=0.023+0.008
dbo.UrlTrackingU 20.031-0.000==0.031=0.023+0.008
dbo.UserAuthenticationU 00.000-0.000==0.000=0.000+0.000
dbo.UserPortalsU 210.047-0.000==0.047=0.039+0.008
dbo.UserProfileU 1060.109-0.000==0.109=0.039+0.070
dbo.UserRolesU 580.047-0.000==0.047=0.039+0.008
dbo.UsersU 240.031-0.000==0.031=0.023+0.008
dbo.UsersOnlineU 00.000-0.000==0.000=0.000+0.000
dbo.VendorClassificationU 00.000-0.000==0.000=0.000+0.000
dbo.VendorsU 00.000-0.000==0.000=0.000+0.000
dbo.VersionU 220.031-0.000==0.031=0.023+0.008
dbo.WebServersU 50.016-0.000==0.016=0.008+0.008
dbo.Wiki_CommentParentsU 00.000-0.000==0.000=0.000+0.000
dbo.Wiki_CommentsU 00.000-0.000==0.000=0.000+0.000
dbo.Wiki_SettingsU 00.000-0.000==0.000=0.000+0.000
dbo.Wiki_TopicU 250.398-0.016==0.383=0.008+0.375
dbo.Wiki_TopicHistoryU 440.523-0.047==0.477=0.008+0.469
sys.queue_messages_1977058079 (dbo.QueryNotificationErrorsQueue)IT00.000-0.000==0.000=0.000+0.000
sys.queue_messages_2009058193 (dbo.EventNotificationErrorsQueue)IT00.000-0.000==0.000=0.000+0.000
sys.queue_messages_2041058307 (dbo.ServiceBrokerQueue)IT00.000-0.000==0.000=0.000+0.000
sys.sysallocunitsS 3040.125-0.055==0.070=0.016+0.055
sys.sysasymkeysS 00.000-0.000==0.000=0.000+0.000
sys.sysbinobjsS 230.031-0.000==0.031=0.023+0.008
sys.sysbinsubobjsS 00.000-0.000==0.000=0.000+0.000
sys.syscertsS 00.000-0.000==0.000=0.000+0.000
sys.sysclsobjsS 230.031-0.000==0.031=0.023+0.008
sys.syscolparsS 31020.781-0.102==0.680=0.234+0.445
sys.sysconvgroupS 00.000-0.000==0.000=0.000+0.000
sys.sysdbfilesS 20.016-0.000==0.016=0.008+0.008
sys.sysdercvS 00.000-0.000==0.000=0.000+0.000
sys.sysdesendS 00.000-0.000==0.000=0.000+0.000
sys.sysfiles1S 20.016-0.000==0.016=0.008+0.008
sys.sysftindsS 00.000-0.000==0.000=0.000+0.000
sys.sysguidrefsS 00.000-0.000==0.000=0.000+0.000
sys.syshobtcolumnsS 15500.258-0.102==0.156=0.016+0.141
sys.syshobtsS 2700.031-0.000==0.031=0.016+0.016
sys.sysidxstatsS 5440.266-0.094==0.172=0.094+0.078
sys.sysiscolsS 6970.063-0.000==0.063=0.016+0.047
sys.sysmultiobjrefsS 39150.461-0.086==0.375=0.164+0.211
sys.sysnsobjsS 10.031-0.000==0.031=0.023+0.008
sys.sysobjkeycryptsS 00.000-0.000==0.000=0.000+0.000
sys.sysobjvaluesS 11562.156-0.164==1.992=0.023+1.969
sys.sysownersS 270.047-0.000==0.047=0.039+0.008
sys.sysprivsS 1960.016-0.000==0.016=0.008+0.008
sys.sysqnamesS 910.031-0.000==0.031=0.023+0.008
sys.sysremsvcbindsS 00.000-0.000==0.000=0.000+0.000
sys.sysrowsetcolumnsS 15500.195-0.055==0.141=0.016+0.125
sys.sysrowsetrefsS 00.000-0.000==0.000=0.000+0.000
sys.sysrowsetsS 2700.039-0.000==0.039=0.016+0.023
sys.sysrtsS 10.047-0.000==0.047=0.039+0.008
sys.sysscalartypesS 270.047-0.000==0.047=0.039+0.008
sys.sysschobjsS 9820.492-0.109==0.383=0.234+0.148
sys.sysserefsS 3040.016-0.000==0.016=0.008+0.008
sys.syssingleobjrefsS 4030.078-0.000==0.078=0.047+0.031
sys.syssqlguidesS 00.000-0.000==0.000=0.000+0.000
sys.systypedsubobjsS 00.000-0.000==0.000=0.000+0.000
sys.sysxmitqueueS 00.000-0.000==0.000=0.000+0.000
sys.sysxmlcomponentS 930.031-0.000==0.031=0.023+0.008
sys.sysxmlfacetS 970.016-0.000==0.016=0.008+0.008
sys.sysxmlplacementS 170.031-0.000==0.031=0.023+0.008
sys.sysxpropsS 20.016-0.000==0.016=0.008+0.008
April 28, 2010 at 8:56 am
sumsumsumsumsum
5744.140996.3464747.79960.7534687.050
April 28, 2010 at 9:03 am
Is this supposte to give performance issues? I want to go to the properties of he db after I ran that code and it is timing out on me?
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply