August 11, 2010 at 9:42 am
Try running this and posting the results:
USE [msdb]
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
August 11, 2010 at 9:49 am
sysxmitqueueclst3970848397035739348993102231018
sysssispackagesNULL12031203199
sysmail_attachmentsNULL510509433
sysobjvaluesclst41838832933
syscollector_blobs_internalPK_syscollector_blobs_internal_paremeter_name120120100
August 11, 2010 at 9:54 am
No No it is not an SSIS Server, sorry if I missed it, the issue here is not the drives provision, I just wanted to fix this issue as it is not normal,
Thnaks,
Dev
August 11, 2010 at 9:54 am
Now we're getting somewhere. I goggled "sysmitqueue", and this is the most intelligible response I found in a short amount of time. Feel free to google it yourself, there's a good amount of information that may help you.
sys.transmission_queue will contain
Service Broker messages that can't be delivered. The most likely reason for
them being undeliverable is that either Service Broker is disabled in the
target database or the target queue is disabled. Try this for the
databases:
select Name, is_broker_enabled from sys.databases
and this in the target database:
select name, is_receive_enabled from sys.service_queues
Another possibility is that they are being sent to a service that doesn't
exist. Service Names are case sensitive.
A good question to ask when you figure it out would be how did 7 million
messages fail to get delivered and nobody noticed?
August 11, 2010 at 9:58 am
can you please get me the query to delete it,
August 11, 2010 at 10:00 am
Well, that is much more helpful.
You need to purge the sysxmitqueue. Basically there is an application that is sending messages to your server. Somebody enabled service broker but it is not configured properly.
You can read another thread about the same topic here:
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 11, 2010 at 10:01 am
Another google find:
To completely obliterate the messages without attempting delivery use ALTER DATABASE [msdb] SET NEW_BROKER WITH ROLLBACK IMMEDIATE;. It is very important that you attempt this only if you have SP2 installed. If you do not fix the application that is sending those messages in the first place the database will grow back though.
You really should be doing your own google search, though. I don't know enough about your configuration to be making broad statements about how to maintain your DB. Also, you should be talking with whoever set up service broker. Hopefully someone there knows something about it. You don't want to break something that may be there for a reason.
August 11, 2010 at 10:02 am
OK Thanks a Lot, I will look into it,
August 11, 2010 at 10:02 am
dsohal (8/11/2010)
can you please get me the query to delete it,
It's not a matter of deleting it at this point. The Service Broker needs to be properly configured. If that is not fixed, you will have this problem again in the future.
It is best to fix the issue and let service broker release the messages.
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 11, 2010 at 10:05 am
Since it is NOT a SSIS Server, then why are you storing all those SSIS packages in the MSDB database?
Remove them, that will free up a few Gigs of space as well. You are storing the SSIS pacakges on a box that is not being used as a SSIS Server. There is no need in that. You need to store only the original code some where else.
If memory serves me correctly about the query that I sent you. the SSIS package table was huge.
Andrew SQLDBA
August 11, 2010 at 10:07 am
thanks, I will update the results when I do it,
August 11, 2010 at 10:09 am
AndrewSQLDBA (8/11/2010)
Since it is NOT a SSIS Server, then why are you storing all those SSIS packages in the MSDB database?Remove them, that will free up a few Gigs of space as well. You are storing the SSIS pacakges on a box that is not being used as a SSIS Server. There is no need in that. You need to store only the original code some where else.
If memory serves me correctly about the query that I sent you. the SSIS package table was huge.
Andrew SQLDBA
Andrew, you were looking at Kb and thinking Mb. His SSIS package table was not that large. We've actually found the root of the problem to be sysxmitqueue.
August 11, 2010 at 10:09 am
AndrewSQLDBA (8/11/2010)
Since it is NOT a SSIS Server, then why are you storing all those SSIS packages in the MSDB database?Remove them, that will free up a few Gigs of space as well. You are storing the SSIS pacakges on a box that is not being used as a SSIS Server. There is no need in that. You need to store only the original code some where else.
If memory serves me correctly about the query that I sent you. the SSIS package table was huge.
Andrew SQLDBA
According to the info provided, he is only storing 9mb of SSIS packages (though the results seem a bit off due to the null value in total pages - that could be fixed by using the query I pointed to earlier).
sysssispackagesNULL12031203199
From the query that you sent, the calculation was in KB not in MB.
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 11, 2010 at 10:38 am
I got this result,
Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.
August 11, 2010 at 11:00 am
That is an informational message that is the result of the WITH ROLLBACK IMMEDIATE. If the command can't be run, it rolls back any pending transactions first. Chances are any pending transactions couldn't complete anyways because your database is full.
Do you check your database size? Did it change?
Viewing 15 posts - 31 through 45 (of 71 total)
You must be logged in to reply to this topic. Login to reply