msdb
in [Server Configuration]
|
From: Tibor Karaszi on 15 Jan 2008 11:43 Seems to be service broker. Did you play with Database Mail (which uses service broker)? -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "guest5" <guest5(a)discussions.microsoft.com> wrote in message news:58827192-9F63-4744-B02B-897B94E043C3(a)microsoft.com... > Hi > > It returns. Not sure how to check the size. Any advise? > > queue_messages_407672500 563042 > queue_messages_407672500 563042 > > > "Tibor Karaszi" wrote: > >> Does below give any hints? >> >> USE msdb >> SELECT TOP(30) OBJECT_NAME(object_id), rows >> FROM sys.partitions >> ORDER BY rows DESC >> >> -- >> Tibor Karaszi, SQL Server MVP >> http://www.karaszi.com/sqlserver/default.asp >> http://sqlblog.com/blogs/tibor_karaszi >> >> >> "guest5" <guest5(a)discussions.microsoft.com> wrote in message >> news:876AA104-C30B-451D-9146-69AF9D9EF21E(a)microsoft.com... >> > Hi >> > >> > I have a sql 2005 instance running sp2. The msdb database keeps running out >> > of space and I cant work out what's using all of it. Currently its 1.5 data >> > allocated. The usage report of the trables shows the larges table is only >> > 10Mb! >> > >> > Any advise will be appreciated as to where to start looking. >> > >> > Thanks in advance. >>
From: guest5 on 15 Jan 2008 12:11 Hi Tibor Seems likly it is service broker. I wasnt aware of having set anything up but can see various queues having been setup. How do I check for the sizes of these and is there a quick way of clearing them? Thanks "Tibor Karaszi" wrote: > Seems to be service broker. Did you play with Database Mail (which uses service broker)? > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://sqlblog.com/blogs/tibor_karaszi > > > "guest5" <guest5(a)discussions.microsoft.com> wrote in message > news:58827192-9F63-4744-B02B-897B94E043C3(a)microsoft.com... > > Hi > > > > It returns. Not sure how to check the size. Any advise? > > > > queue_messages_407672500 563042 > > queue_messages_407672500 563042 > > > > > > "Tibor Karaszi" wrote: > > > >> Does below give any hints? > >> > >> USE msdb > >> SELECT TOP(30) OBJECT_NAME(object_id), rows > >> FROM sys.partitions > >> ORDER BY rows DESC > >> > >> -- > >> Tibor Karaszi, SQL Server MVP > >> http://www.karaszi.com/sqlserver/default.asp > >> http://sqlblog.com/blogs/tibor_karaszi > >> > >> > >> "guest5" <guest5(a)discussions.microsoft.com> wrote in message > >> news:876AA104-C30B-451D-9146-69AF9D9EF21E(a)microsoft.com... > >> > Hi > >> > > >> > I have a sql 2005 instance running sp2. The msdb database keeps running out > >> > of space and I cant work out what's using all of it. Currently its 1.5 data > >> > allocated. The usage report of the trables shows the larges table is only > >> > 10Mb! > >> > > >> > Any advise will be appreciated as to where to start looking. > >> > > >> > Thanks in advance. > >> >
From: Tibor Karaszi on 15 Jan 2008 12:27 Again, it is possible that someone has trying out Database Mail. Database Mail uses Service Broker and the queues are in the msdb database. So I'd start by checking of there is some job or something else which tries to send email (and also check the Database Mail config). I'm no Service Broker expert, I'm afraid. I suggest you both post to the Service Broker group and also check out below Books Online section: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlmsg9/html/2c7eb51e-3574-4f15-a9cc-40c92d08c6cc.htm -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "guest5" <guest5(a)discussions.microsoft.com> wrote in message news:3DB20BF7-3863-43ED-ABA8-EA842590274F(a)microsoft.com... > Hi Tibor > > Seems likly it is service broker. I wasnt aware of having set anything up > but can see various queues having been setup. How do I check for the sizes of > these and is there a quick way of clearing them? > > > Thanks > > "Tibor Karaszi" wrote: > >> Seems to be service broker. Did you play with Database Mail (which uses service broker)? >> >> -- >> Tibor Karaszi, SQL Server MVP >> http://www.karaszi.com/sqlserver/default.asp >> http://sqlblog.com/blogs/tibor_karaszi >> >> >> "guest5" <guest5(a)discussions.microsoft.com> wrote in message >> news:58827192-9F63-4744-B02B-897B94E043C3(a)microsoft.com... >> > Hi >> > >> > It returns. Not sure how to check the size. Any advise? >> > >> > queue_messages_407672500 563042 >> > queue_messages_407672500 563042 >> > >> > >> > "Tibor Karaszi" wrote: >> > >> >> Does below give any hints? >> >> >> >> USE msdb >> >> SELECT TOP(30) OBJECT_NAME(object_id), rows >> >> FROM sys.partitions >> >> ORDER BY rows DESC >> >> >> >> -- >> >> Tibor Karaszi, SQL Server MVP >> >> http://www.karaszi.com/sqlserver/default.asp >> >> http://sqlblog.com/blogs/tibor_karaszi >> >> >> >> >> >> "guest5" <guest5(a)discussions.microsoft.com> wrote in message >> >> news:876AA104-C30B-451D-9146-69AF9D9EF21E(a)microsoft.com... >> >> > Hi >> >> > >> >> > I have a sql 2005 instance running sp2. The msdb database keeps running out >> >> > of space and I cant work out what's using all of it. Currently its 1.5 data >> >> > allocated. The usage report of the trables shows the larges table is only >> >> > 10Mb! >> >> > >> >> > Any advise will be appreciated as to where to start looking. >> >> > >> >> > Thanks in advance. >> >> >>
From: a12345 on 15 Jan 2008 15:37 I am having the same problem where the total of the tables amount to about 15 MB in data (reserved) for both user and system tables. The actual file size of the data file for msdb is 700 MB and it won't let me shrink it any lower. I have no idea what is holding on to that amount of data. I use 2 stored procedures to show data usage for user and system tables (see below, data in KB)) and they show the largest table being 5 mb. I cleaned out the database mail log as well as the job history files to no avail. All tables that have to do with service broker are empty or small. I did do the "top 30 from sys.partitions" and the top table is syscolpars which only has 700 KB in it. This issue is not unique to this server. I have 2 other servers with the same issue. I am stuck. Any help would be much appreciated. name rows reserved data index_size unused create_date ---------------------------------------- ----------- ----------- ----------- ----------- ----------- ------------------- dbo.sysmaintplan_logdetail 440 5256 5208 8 40 2005/10/14 01:54:15 dbo.sysmail_attachments 58 776 760 8 8 2005/10/14 01:55:30 dbo.backupfile 1470 592 544 16 32 2005/10/14 01:55:15 dbo.backupset 723 504 408 56 40 2005/10/14 01:55:15 dbo.sysjobhistory 500 336 232 56 48 2005/10/14 01:54:09 dbo.sysmail_mailitems 418 328 256 16 56 2005/10/14 01:55:28 dbo.backupmediafamily 723 248 152 56 40 2005/10/14 01:55:15 dbo.backupfilegroup 735 136 80 16 40 2005/10/14 01:55:15 dbo.backupmediaset 723 120 64 56 0 2005/10/14 01:55:15 dbo.sysjobsteps 33 64 16 48 0 2005/10/14 01:54:11 dbo.sysjobs 23 64 8 56 0 2005/10/14 01:54:10 name rows reserved data index_size unused create_date ---------------------------------------- ----------- ----------- ----------- ----------- ----------- ------------------- sys.sysobjvalues 971 2184 2112 16 56 2005/10/14 01:36:15 sys.syscolpars 2960 784 464 248 72 2005/10/14 01:36:17 sys.sysschobjs 754 448 112 232 104 2005/10/14 01:36:15 sys.sysmultiobjrefs 2930 336 144 136 56 2005/10/14 01:36:15 sys.sysrowsetcolumns 1580 200 120 16 64 2005/10/14 01:36:15 sys.sysobjkeycrypts 618 200 176 16 8 2005/10/14 01:36:25 sys.syshobtcolumns 1580 200 128 16 56 2005/10/14 01:36:15 sys.sysidxstats 469 192 64 72 56 2005/10/14 01:36:17 sys.sysdercv 0 136 64 16 56 2005/10/14 01:36:25 sys.syscerts 2 64 8 56 0 2005/10/14 01:36:25 Total is 15 MB Data file is 700 MB Log file is 21 MB
First
|
Prev
|
Pages: 1 2 3 Prev: Database Maintenance Plan Wizard Next: copying stored procedures in SQL Server 2005? |