August 12, 2010 at 11:09 am
My apologies, I thought you would complete the name of that table. The table is sysxmitqueue.
Run this query and show us the full results please.
/*similar to sp_spaceused */
declare @dbsize decimal(19,2)
,@logsize decimal(19,2)
set nocount on
/*
** Summary data.
*/
begin
select @dbsize = sum(convert(decimal(19,2),case when type = 0 then size else 0 end)) * 8/1024
, @logsize = sum(convert(decimal(19,2),case when type = 1 then size else 0 end)) * 8/1024
from sys.database_files
end
/*
** We want all objects.
*/
Begin
With FirstPass as (
SELECT OBJECT_ID,
ReservedPage = convert(decimal(19,2),SUM(reserved_page_count)) * 8/1024,
UsedPage = convert(decimal(19,2),SUM(used_page_count)) *8/1024,
PageCnt = SUM(
convert(decimal(19,2),CASE
WHEN (index_id < 2)
THEN (used_page_count)
ELSE lob_used_page_count + row_overflow_used_page_count
END
)) * 8/1024,
RowCnt = SUM(
CASE
WHEN (index_id < 2)
THEN row_count
ELSE 0
END
)
FROM sys.dm_db_partition_stats
--Where OBJECTPROPERTY(OBJECT_ID,'IsMSShipped') = 0
Group By OBJECT_ID
)
,InternalTables as (
Select ps.OBJECT_ID,
ReservedPage = convert(decimal(19,2),SUM(reserved_page_count)) * 8/1024,
UsedPage = convert(decimal(19,2),SUM(used_page_count)) *8/1024
From sys.dm_db_partition_stats ps
Inner Join sys.internal_tables it
On it.OBJECT_ID = ps.OBJECT_ID
And it.internal_type IN (202,204,211,212,213,214,215,216)
Where it.parent_id = ps.OBJECT_ID
--And OBJECTPROPERTY(ps.OBJECT_ID,'IsMSShipped') = 0
Group By ps.OBJECT_ID
)
,Summary as (
SELECT
ObjName = OBJECT_NAME (f.OBJECT_ID),
NumRows = MAX(f.rowcnt),
ReservedPageMB = SUM(IsNull(f.reservedpage,0) + IsNull(i.ReservedPage,0)),
DataSizeMB = Sum(f.PageCnt),
IndexSizeMB = Sum(CASE WHEN (f.UsedPage + IsNull(i.UsedPage,0)) > f.PageCnt
THEN ((f.UsedPage + IsNull(i.UsedPage,0)) - f.PageCnt) ELSE 0 END) ,-- Equivalent of max_record_size from sys.dm_db_index_physical_stats
UnusedSpace = Sum(CASE WHEN (f.ReservedPage + IsNull(i.ReservedPage,0)) > (f.UsedPage + IsNull(i.UsedPage,0))
THEN ((f.ReservedPage + IsNull(i.ReservedPage,0)) - (f.UsedPage + IsNull(i.UsedPage,0))) ELSE 0 END),
DBSizeMB = @Dbsize,
LogSizeMB = @logsize
From FirstPass F
Left Outer Join InternalTables i
On i.OBJECT_ID = f.OBJECT_ID
Group By f.OBJECT_ID
)
Select ObjName,NumRows, ReservedPageMB, DataSizeMB, IndexSizeMB, UnusedSpace, DBSizeMB, LogSizeMB,
PercentofDB = ((IndexSizeMb + DataSizeMB) / @DBsize) * 100
From Summary
Order By PercentofDB desc
End
THat is a script available in my series on the subject at http://jasonbrimhall.info/2010/05/05/sql-2005-tablespace/
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 12, 2010 at 11:53 am
sysssispackages219.41406259.3984370.015626031035.5623.810.0303
sysmail_attachments183.9843753.9765620.0000010.007812531035.5623.810.0128
sysobjvalues11233.2656253.0312500.23437531035.5623.810.0097
syscollector_blobs_internal10.93750.93750031035.5623.810.003
syscolpars35670.9531250.6171870.2734380.062531035.5623.810.0028
sysschobjs9000.49218750.1718750.2421880.07812531035.5623.810.0013
syssoftobjrefs13190.5156250.2578120.1484380.10937531035.5623.810.0013
sysmultiobjrefs36550.3906250.2421870.1250010.023437531035.5623.810.0011
sysobjkeycrypts3770.25781250.2500.007812531035.5623.810.0008
sysjobactivity14880.2656250.187500.07812531035.5623.810.0006
sysjobsteps1070.18750.156250.03125031035.5623.810.0006
sysrscols17880.19531250.1562500.039062531035.5623.810.0005
sysidxstats4960.19531250.0859370.0625010.04687531035.5623.810.0004
sysjobhistory760.1406250.1171870.023438031035.5623.810.0004
sysmail_mailitems2770.13281250.12500.007812531035.5623.810.0004
sysiscols7090.10156250.06250.039063031035.5623.810.0003
syscerts20.06250.0156250.046875031035.5623.810.0002
sysallocunits3330.06250.0468750.015625031035.5623.810.0002
syssingleobjrefs4620.0781250.0468750.03125031035.5623.810.0002
sysjobs640.08593750.0390620.046876031035.5623.810.0002
sysmanagement_shared_server_groups_internal50.06250.0156250.046875031035.5623.810.0002
syspolicy_facet_events840.08593750.0546870.031251031035.5623.810.0002
syscollector_collection_items_internal50.07031250.0546870.015626031035.5623.810.0002
MSdbms_map3740.05468750.0546870.000001031035.5623.810.0001
sysssispackagefolders50.0468750.0156250.03125031035.5623.810.0001
sysjobschedules580.031250.0156250.015625031035.5623.810.0001
sysdtscategories30.031250.0156250.015625031035.5623.810.0001
sysschedules650.031250.031250031035.5623.810.0001
syscollector_collector_types_internal40.031250.0156250.015625031035.5623.810.0001
sysmail_profile10.031250.0156250.015625031035.5623.810.0001
sysoperators30.031250.0156250.015625031035.5623.810.0001
syscollector_collection_sets_internal30.031250.0156250.015625031035.5623.810.0001
syssubsystems120.031250.0156250.015625031035.5623.810.0001
sysmail_account10.031250.0156250.015625031035.5623.810.0001
MSdbms_datatype1800.031250.031250031035.5623.810.0001
sysjobservers640.0468750.031250.015625031035.5623.810.0001
MSdbms_datatype_mapping4930.0468750.0468750031035.5623.810.0001
backupset00.05468750.0234370.031251031035.5623.810.0001
syssessions280.031250.0156250.015625031035.5623.810.0001
sysmail_log1030.03906250.0390620.000001031035.5623.810.0001
restorehistory00.031250.0156250.015625031035.5623.810.0001
sysxmlplacement850.031250.0156250.015625031035.5623.810.0001
sysbinsubobjs30.031250.0156250.015625031035.5623.810.0001
sysqnames1270.031250.0156250.015625031035.5623.810.0001
sysxmlcomponent1490.031250.0156250.015625031035.5623.810.0001
backupmediaset00.031250.0156250.015625031035.5623.810.0001
backupmediafamily00.03906250.0234370.015626031035.5623.810.0001
sysscalartypes350.0468750.0156250.03125031035.5623.810.0001
sysclsobjs260.031250.0156250.015625031035.5623.810.0001
sysrts10.0468750.0156250.03125031035.5623.810.0001
sysrowsets2720.03906250.0390620.000001031035.5623.810.0001
sysowners340.0468750.0156250.03125031035.5623.810.0001
sysprivs4820.03906250.0390620.000001031035.5623.810.0001
sysnsobjs40.031250.0156250.015625031035.5623.810.0001
sysbinobjs290.031250.0156250.015625031035.5623.810.0001
sysaudacts0000031035.5623.810
sysxprops0000031035.5623.810
sysfiles120.0156250.0156250031035.5623.810
syspriorities0000031035.5623.810
sysfgfrag20.0156250.0156250031035.5623.810
sysphfg10.0156250.0156250031035.5623.810
sysprufiles20.0156250.0156250031035.5623.810
sysftinds0000031035.5623.810
sysconvgroup0000031035.5623.810
sysdesend0000031035.5623.810
sysdercv0000031035.5623.810
sysrowsetrefs0000031035.5623.810
sysremsvcbinds0000031035.5623.810
sysxmitqueue0000031035.5623.810
systypedsubobjs0000031035.5623.810
sysguidrefs0000031035.5623.810
syscompfragments0000031035.5623.810
sysftstops0000031035.5623.810
syspolicy_target_sets_internal0000031035.5623.810
sqlagent_info0000031035.5623.810
sysdownloadlist0000031035.5623.810
sysproxysubsystem0000031035.5623.810
sysproxylogin0000031035.5623.810
queue_messages_542912530000031035.5623.810
queue_messages_862913670000031035.5623.810
sysxmlfacet1120.0156250.0156250031035.5623.810
sysasymkeys0000031035.5623.810
syssqlguides0000031035.5623.810
sysproxies0000031035.5623.810
sysdbmaintplan_history0000031035.5623.810
syscollector_execution_stats_internal0000031035.5623.810
sysoriginatingservers0000031035.5623.810
syspolicy_target_set_levels_internal0000031035.5623.810
backupfilegroup00.02343750.0234370.000001031035.5623.810
sysmail_send_retries60.0156250.0156250031035.5623.810
log_shipping_primaries0000031035.5623.810
backupfile00.02343750.0234370.000001031035.5623.810
log_shipping_secondaries0000031035.5623.810
syspolicy_policy_category_subscriptions_internal0000031035.5623.810
filestream_tombstone_20730584210000031035.5623.810
syscommittab0000031035.5623.810
sysdbmaintplan_databases0000031035.5623.810
syscollector_execution_log_internal0000031035.5623.810
log_shipping_monitor_secondary0000031035.5623.810
sysmail_profileaccount10.0156250.0156250031035.5623.810
log_shipping_monitor_alert0000031035.5623.810
sysmail_server10.0156250.0156250031035.5623.810
queue_messages_19770580790000031035.5623.810
sysdbmaintplan_jobs0000031035.5623.810
queue_messages_20090581930000031035.5623.810
MSdbms80.0156250.0156250031035.5623.810
queue_messages_20410583070000031035.5623.810
sysmail_configuration70.0156250.0156250031035.5623.810
log_shipping_primary_databases0000031035.5623.810
syspolicy_policy_categories_internal0000031035.5623.810
log_shipping_primary_secondaries0000031035.5623.810
sysnotifications0000031035.5623.810
sysdtspackagelog0000031035.5623.810
sysmaintplan_subplans30.0156250.0156250031035.5623.810
syspolicy_management_facets740.0156250.0156250031035.5623.810
syspolicy_configuration_internal40.0156250.0156250031035.5623.810
sysdtssteplog0000031035.5623.810
log_shipping_monitor_primary0000031035.5623.810
syspolicy_object_sets_internal0000031035.5623.810
log_shipping_monitor_history_detail0000031035.5623.810
sysmail_principalprofile10.0156250.0156250031035.5623.810
log_shipping_monitor_error_detail0000031035.5623.810
log_shipping_secondary0000031035.5623.810
log_shipping_secondary_databases0000031035.5623.810
syspolicy_policies_internal0000031035.5623.810
sysmanagement_shared_registered_servers_internal0000031035.5623.810
syspolicy_policy_execution_history_details_internal0000031035.5623.810
sysdtspackages0000031035.5623.810
restorefile00.00781250.0078120.000001031035.5623.810
syspolicy_system_health_state_internal0000031035.5623.810
syscollector_tsql_query_collector0000031035.5623.810
sysmail_query_transfer00.0156250.0156250031035.5623.810
restorefilegroup00.00781250.0078120.000001031035.5623.810
sysjobstepslogs0000031035.5623.810
logmarkhistory0000031035.5623.810
suspect_pages0000031035.5623.810
sysmail_attachments_transfer00.02343750.0234370.000001031035.5623.810
syspolicy_policy_execution_history_internal0000031035.5623.810
sysssislog0000031035.5623.810
syscategories210.0156250.0156250031035.5623.810
systargetservers0000031035.5623.810
systargetservergroups0000031035.5623.810
systargetservergroupmembers0000031035.5623.810
sysalerts0000031035.5623.810
syspolicy_execution_internal0000031035.5623.810
syscollector_config_store_internal50.0156250.0156250031035.5623.810
sysmaintplan_log10.0156250.0156250031035.5623.810
syspolicy_conditions_internal0000031035.5623.810
sysdtstasklog0000031035.5623.810
queue_messages_11868192900000031035.5623.810
sysmaintplan_logdetail00.00781250.0078120.000001031035.5623.810
systaskids0000031035.5623.810
syscachedcredentials10.0156250.0156250031035.5623.810
sysmail_servertype10.0156250.0156250031035.5623.810
sysdbmaintplans10.0156250.0156250031035.5623.810
August 12, 2010 at 11:56 am
Try this:
sp_updatestats
;
Once that is done, do this:
dbcc shrinkfile (1,100)
;
dbcc shrinkfile (2,100)
;
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 12, 2010 at 12:00 pm
I hope these statements will not screw up anything, just checking and making sure please,
August 12, 2010 at 12:01 pm
Do you mean sp_updatestats or DBCC UPDATEUSAGE(0)?
August 12, 2010 at 12:03 pm
both of them please
August 12, 2010 at 12:11 pm
Shrinkfile is similar to the shrinkdatabase that you already ran - difference is in scope - one is to shrink files and the other is for shrinking the database.
update stats is to update the statistics related to the database tables.
Updateusage would also be advisable to run - and it is another maintenance task that will not screw up your database.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 12, 2010 at 12:17 pm
Hello,
I just want to thank big time for your patiance and working with me, the problem is resolved, I am attaching hereby the results, it is in few MB. now, I really learnt a lot,
Thanks a Lot Again,
If you come in Toronto buzz me, I owe you a lunch,
Thanks Again,
Dev
msdb151.19 MB76.46 MB
August 12, 2010 at 12:25 pm
Congrats - good to know that it is resolved.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 12, 2010 at 12:29 pm
Thanks once Again Jason, I made my notes in case I am in such situations,
August 12, 2010 at 12:38 pm
You're welcome.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 30, 2010 at 3:28 am
Hi dsohal
Can you send me the exact steps on how you resolved this as we are having the same issue.
Regards
Kurt Walters
Viewing 12 posts - 61 through 71 (of 71 total)
You must be logged in to reply to this topic. Login to reply