October 19, 2011 at 8:12 am
What can I do about a 35g sys.sysobjvalues table? My msdb database is 45g and I have cleared backup tables and mail tables. I ran a query to find which table was causing the issue (below) and it shows the system base table sys.sysobjvalues but I find nothing on what to do about this or why it would be so large:
SELECT object_name(i.object_id) as objectName,
i.[name] as indexName,
sum(a.total_pages) as totalPages,
sum(a.used_pages) as usedPages,
sum(a.data_pages) as dataPages,
(sum(a.total_pages) * 8) / 1024 as totalSpaceMB,
(sum(a.used_pages) * 8) / 1024 as usedSpaceMB,
(sum(a.data_pages) * 8) / 1024 as dataSpaceMB
FROM sys.indexes i
INNER JOIN sys.partitions p
ON i.object_id = p.object_id
AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a
ON p.partition_id = a.container_id
GROUP BY i.object_id, i.index_id, i.[name]
ORDER BY sum(a.total_pages) DESC, object_name(i.object_id)
GO
Results:
objectName indexName TotalPages usedPagesdataPagestotalSpaceMBusedSpaceMBdataSpaceMB
sysobjvaluesclst4566986456631533835679356742
October 19, 2011 at 8:17 am
All I can find is this incredibally useful info on msdn :
Exists in every database. Contains a row for each general value property of an entity.
Can you show us some sample data?
October 19, 2011 at 8:24 am
unfortunately you cannot access the system base tables without being in single user mode and our database server is up 24/7 so switching to single user mode has to be scheduled long in advance.
October 19, 2011 at 8:26 am
Backup / restore to local/test server.
I'm sure that is alraedy coded somewhere to test the backups 😉
October 19, 2011 at 1:03 pm
I restored the database to a local machine and connected using DAC so I could query the sys.sysobjvalues table but the data is very cryptic. There are 1052 rows but I'm not sure how to determine what the imageval column is referencing
October 19, 2011 at 7:36 pm
Don't know either... Hard to say without seeing some of the data.
February 12, 2012 at 3:57 pm
As posted here: http://social.technet.microsoft.com/Forums/en-US/sqldatabaseengine/thread/68bd166f-c9b1-4f21-b997-8b44a8cffedb
My guess is that you have created large modules in msdb. See what this yields (you may need to connect using a dedicated administrator connection, e.g. use "admin:server\instance" instead of just "server\instance":
SELECT TOP (10)
name = OBJECT_SCHEMA_NAME([object_id]) + '.' + OBJECT_NAME([object_id]),
size_in_sysobjvalues = DATALENGTH(o.imageval),
size_in_allsqlmodules = DATALENGTH(m.[definition]),
m.[definition]
FROM msdb.sys.sysobjvalues AS o
INNER JOIN sys.all_sql_modules AS m
ON o.[objid] = m.[object_id]
ORDER BY DATALENGTH(o.imageval) DESC;
You should notice that the size in all_sql_modules is double the size in sysobjvalues.
Also note that this join isn't fully compatible - there are some items in sysobjvalues that aren't in all_sql_modules and vice versa. For example on my system:
SELECT COUNT(*), SUM(DATALENGTH(imageval))
FROM msdb.sys.sysobjvalues;
SELECT COUNT(*), SUM(DATALENGTH([definition]))
FROM sys.all_sql_modules;
SELECT COUNT(*),
SUM(DATALENGTH(m.[definition])),
SUM(DATALENGTH(o.imageval))
FROM sys.all_sql_modules AS m
INNER JOIN msdb.sys.sysobjvalues AS o
ON m.[object_id] = o.[objid];
My answers are:
1368 1793742
2431 16939628
617 3160420 1522649
But hopefully that might help you track down where the size is being used. You may want to add the following where clause if you find the top is being consumed by system procedures which you can't do anything about (you may also consider that you have many, many objects that aren't large at all):
WHERE OBJECTPROPERTY(m.[object_id], 'IsMsShipped') = 0
February 12, 2012 at 4:25 pm
Also you may have some interesting observations about other objects that are contributing to the size of sysobjvalues. Again connect as DAC:
CREATE TABLE #x(name NVARCHAR(511), c INT, l INT);
DECLARE @sql NVARCHAR(MAX) = N'SELECT N'''', 0, 0';
SELECT @sql += '
UNION ALL SELECT ''sys.' + OBJECT_NAME(object_id)
+ ''', COUNT(*), SUM(DATALENGTH(o.imageval)) FROM sys.['
+ OBJECT_NAME(object_id)
+ '] AS s INNER JOIN msdb.sys.sysobjvalues AS o
ON s.[object_id] = o.[objid]'
FROM sys.all_columns WHERE object_id IN (SELECT object_id FROM sys.all_objects)
AND name = N'object_id'
AND OBJECT_NAME([object_id]) IS NOT NULL
AND (OBJECTPROPERTY(object_id, 'IsTable') = 1 OR
OBJECTPROPERTY(object_id, 'IsView') = 1)
AND OBJECT_SCHEMA_NAME([object_id]) = N'sys';
PRINT @sql;
INSERT #x EXEC sp_executesql @sql;
SELECT * FROM #x
WHERE c > 0
ORDER BY l DESC;
DROP TABLE #x;
February 13, 2012 at 8:55 am
What can I do about a 35g sys.sysobjvalues table? My msdb database is 45g and I have cleared backup tables and mail tables. I ran a query to find which table was causing the issue (below) and it shows the system base table sys.sysobjvalues but I find nothing on what to do about this or why it would be so large:
Damn, that's weird. The sys.sysobjvalues is just some system table for containing information about properties and text of objects. Is it possible that there is some job or process that continuously dropping/recreating objects?
Try querying sys.dm_exec_* or running a trace to determine what may be hitting this table.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 13, 2012 at 12:21 pm
These are my results:
10561750908
199814922790
39921097681039186
name c l
sys.sql_dependencies 2888 13467191
sys.all_columns 7673 13108160
sys.columns 7673 13108160
sys.stats_columns 4666 8296763
sys.all_parameters 1499 8114854
sys.parameters 1499 8114854
sys.stats 3932 7213388
sys.index_columns 1771 2400001
sys.all_objects 1054 1750908
sys.objects 1054 1750908
sys.dm_db_index_usage_stats 599 1441111
sys.system_internals_partitions 1062 1328875
sys.dm_db_partition_stats 1062 1328875
sys.partitions 1062 1328875
sys.indexes 1062 1328875
sys.sql_modules 399 1039186
sys.all_sql_modules 399 1039186
sys.procedures 335 1017877
sys.tables 369 439553
sys.identity_columns 195 260438
sys.dm_db_missing_index_details 56 161144
sys.trigger_events 52 15698
sys.events 52 15698
sys.triggers 40 11348
sys.views 14 7503
sys.all_views 14 7503
sys.default_constraints 106 962
sys.check_constraints 8 652
sys.computed_columns 3 41
sys.internal_tables 18 0
sys.service_queues 2 NULL
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply