msdb database size

My SQL Server 2005 SP2 msdb database is 230 GB. I tried shrinking it several
times and nothing changes. The log file is 1 mb. How can I tell what is
using up this space?USE msdb;
GO
SELECT TOP 10 OBJECT_NAME(id), *
FROM sys.sysindexes
WHERE indid IN (0,1)
ORDER BY used DESC;
--or
SELECT TOP 10 OBJECT_NAME([object_id]), *
FROM sys.dm_db_partition_stats
WHERE index_id IN (0,1)
ORDER BY in_row_reserved_page_count DESC;
"SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
news:771E4963-D5B2-4CEB-9209-00FF3B5F75A4@.microsoft.com...
> My SQL Server 2005 SP2 msdb database is 230 GB. I tried shrinking it
> several
> times and nothing changes. The log file is 1 mb. How can I tell what is
> using up this space?|||Aaron,
Thank you. I ran the second query and got an object with this name:
queue_messages_391672443
I cannot find this object in the tables...
"Aaron Bertrand [SQL Server MVP]" wrote:
> USE msdb;
> GO
> SELECT TOP 10 OBJECT_NAME(id), *
> FROM sys.sysindexes
> WHERE indid IN (0,1)
> ORDER BY used DESC;
> --or
> SELECT TOP 10 OBJECT_NAME([object_id]), *
> FROM sys.dm_db_partition_stats
> WHERE index_id IN (0,1)
> ORDER BY in_row_reserved_page_count DESC;
>
> "SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
> news:771E4963-D5B2-4CEB-9209-00FF3B5F75A4@.microsoft.com...
> > My SQL Server 2005 SP2 msdb database is 230 GB. I tried shrinking it
> > several
> > times and nothing changes. The log file is 1 mb. How can I tell what is
> > using up this space?
>
>|||Probably marked as a system table. This will generate a query that will
show you 10 sample rows from the table.
SELECT 'SELECT TOP 10 * FROM '
+ OBJECT_SCHEMA_NAME(object_id)
+ '.[' + name + ']'
FROM sys.all_objects
WHERE name = 'queue_messages_391672443';
Is it possible you are using service broker or event/query notifications and
messages are being placed on the queue but not sent out?
"SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
news:6F5B5B70-F929-4D5D-8A28-34E3BF84D098@.microsoft.com...
> Aaron,
> Thank you. I ran the second query and got an object with this name:
> queue_messages_391672443
> I cannot find this object in the tables...
> "Aaron Bertrand [SQL Server MVP]" wrote:
>> USE msdb;
>> GO
>> SELECT TOP 10 OBJECT_NAME(id), *
>> FROM sys.sysindexes
>> WHERE indid IN (0,1)
>> ORDER BY used DESC;
>> --or
>> SELECT TOP 10 OBJECT_NAME([object_id]), *
>> FROM sys.dm_db_partition_stats
>> WHERE index_id IN (0,1)
>> ORDER BY in_row_reserved_page_count DESC;
>>
>> "SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
>> news:771E4963-D5B2-4CEB-9209-00FF3B5F75A4@.microsoft.com...
>> > My SQL Server 2005 SP2 msdb database is 230 GB. I tried shrinking it
>> > several
>> > times and nothing changes. The log file is 1 mb. How can I tell what
>> > is
>> > using up this space?
>>|||I cannot find this table. The script returned a SQL statement with table
name that does not exist. We're not using service broker. What can we do to
stop the message and clear up them up?
"Aaron Bertrand [SQL Server MVP]" wrote:
> Probably marked as a system table. This will generate a query that will
> show you 10 sample rows from the table.
>
> SELECT 'SELECT TOP 10 * FROM '
> + OBJECT_SCHEMA_NAME(object_id)
> + '.[' + name + ']'
> FROM sys.all_objects
> WHERE name = 'queue_messages_391672443';
>
> Is it possible you are using service broker or event/query notifications and
> messages are being placed on the queue but not sent out?
>
>
>
> "SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
> news:6F5B5B70-F929-4D5D-8A28-34E3BF84D098@.microsoft.com...
> > Aaron,
> > Thank you. I ran the second query and got an object with this name:
> > queue_messages_391672443
> >
> > I cannot find this object in the tables...
> >
> > "Aaron Bertrand [SQL Server MVP]" wrote:
> >
> >> USE msdb;
> >> GO
> >>
> >> SELECT TOP 10 OBJECT_NAME(id), *
> >> FROM sys.sysindexes
> >> WHERE indid IN (0,1)
> >> ORDER BY used DESC;
> >>
> >> --or
> >>
> >> SELECT TOP 10 OBJECT_NAME([object_id]), *
> >> FROM sys.dm_db_partition_stats
> >> WHERE index_id IN (0,1)
> >> ORDER BY in_row_reserved_page_count DESC;
> >>
> >>
> >>
> >> "SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
> >> news:771E4963-D5B2-4CEB-9209-00FF3B5F75A4@.microsoft.com...
> >> > My SQL Server 2005 SP2 msdb database is 230 GB. I tried shrinking it
> >> > several
> >> > times and nothing changes. The log file is 1 mb. How can I tell what
> >> > is
> >> > using up this space?
> >>
> >>
> >>
>
>|||>I cannot find this table. The script returned a SQL statement with table
> name that does not exist. We're not using service broker. What can we do
> to
> stop the message and clear up them up?
Well if you "can't find the table" it's going to be hard for any of us to
suggest a way to "clear up" anything... maybe you should turn on Profiler
and watch for any T-SQL events that involve an object name like that... then
that might point you to some application you have running that is doing this
to you.
A|||Aaron,
Thank you for your assistance. I seem to be getting nowhere in getting to
the bottom of what is causing this. I don't know how Service Broker works
and whether it is responsible.
I'm running Profiler now and I am not getting anything. How do I view the
sys tables?
"Aaron Bertrand [SQL Server MVP]" wrote:
> >I cannot find this table. The script returned a SQL statement with table
> > name that does not exist. We're not using service broker. What can we do
> > to
> > stop the message and clear up them up?
> Well if you "can't find the table" it's going to be hard for any of us to
> suggest a way to "clear up" anything... maybe you should turn on Profiler
> and watch for any T-SQL events that involve an object name like that... then
> that might point you to some application you have running that is doing this
> to you.
> A
>
>|||> I'm running Profiler now and I am not getting anything. How do I view the
> sys tables?
There are tons of sys tables. Can you be more specific? What *EXACTLY* did
the query I sent earlier return? And what happened when you ran the output?
SELECT 'SELECT TOP 10 * FROM '
+ OBJECT_SCHEMA_NAME(object_id)
+ '.[' + name + ']'
FROM sys.all_objects
WHERE name = 'queue_messages_391672443';|||It generates this sql statement as output:
SELECT TOP 10 * FROM sys.[queue_messages_391672443]
"Aaron Bertrand [SQL Server MVP]" wrote:
> > I'm running Profiler now and I am not getting anything. How do I view the
> > sys tables?
> There are tons of sys tables. Can you be more specific? What *EXACTLY* did
> the query I sent earlier return? And what happened when you ran the output?
> SELECT 'SELECT TOP 10 * FROM '
> + OBJECT_SCHEMA_NAME(object_id)
> + '.[' + name + ']'
> FROM sys.all_objects
> WHERE name = 'queue_messages_391672443';
>
>|||And I'll ask again, what happens when you run *THAT* query?
"SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
news:EFEE4A62-F1A4-4171-81CC-D804033AD2E3@.microsoft.com...
> It generates this sql statement as output:
> SELECT TOP 10 * FROM sys.[queue_messages_391672443]|||Thanks Aaron,
I get an error:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.queue_messages_391672443'.
"Aaron Bertrand [SQL Server MVP]" wrote:
> And I'll ask again, what happens when you run *THAT* query?
>
> "SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
> news:EFEE4A62-F1A4-4171-81CC-D804033AD2E3@.microsoft.com...
> > It generates this sql statement as output:
> > SELECT TOP 10 * FROM sys.[queue_messages_391672443]
>
>|||I don't know, gremlins? Can you try running that query as sa or another
sysadmin? Maybe you can't select from it because you don't have privileges.
"SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
news:9FAB03FA-9A5D-455B-BC15-DDB1A7036A73@.microsoft.com...
> Thanks Aaron,
> I get an error:
> Msg 208, Level 16, State 1, Line 1
> Invalid object name 'sys.queue_messages_391672443'.
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>> And I'll ask again, what happens when you run *THAT* query?
>>
>> "SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
>> news:EFEE4A62-F1A4-4171-81CC-D804033AD2E3@.microsoft.com...
>> > It generates this sql statement as output:
>> > SELECT TOP 10 * FROM sys.[queue_messages_391672443]
>>|||With the help from Micrsoft, the culprit turned out to be a login
notification service that had gotten turned on (still a mysterie). The
service broker type service usese an internal table called LoginQueue that
was expanding by leaps and bound as result of login events. Since there was
no place for the notifications to be sent or received, the queue continued to
grow. We had to write a program to "receive messages" for 86 million
messages. Once the program received all the message we could then delete the
service and the associated internal table. Shrinking the database reclaimed
all the space.
Aaron, I really apreciate your involvement. Becaue of your query, we're
able to get Microsoft focused on how to identify the source and deal with it.
"Aaron Bertrand [SQL Server MVP]" wrote:
> I don't know, gremlins? Can you try running that query as sa or another
> sysadmin? Maybe you can't select from it because you don't have privileges.
>
> "SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
> news:9FAB03FA-9A5D-455B-BC15-DDB1A7036A73@.microsoft.com...
> > Thanks Aaron,
> > I get an error:
> > Msg 208, Level 16, State 1, Line 1
> > Invalid object name 'sys.queue_messages_391672443'.
> >
> >
> > "Aaron Bertrand [SQL Server MVP]" wrote:
> >
> >> And I'll ask again, what happens when you run *THAT* query?
> >>
> >>
> >>
> >> "SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
> >> news:EFEE4A62-F1A4-4171-81CC-D804033AD2E3@.microsoft.com...
> >> > It generates this sql statement as output:
> >> > SELECT TOP 10 * FROM sys.[queue_messages_391672443]
> >>
> >>
> >>
>
>