Prev: Cannot open database error
Next: "Send Report to an Email Recipient" checkbox after SQL 2008 SP1
From: Yitzak on 22 Oct 2009 11:31
1. Have a SQL Agent Job that uses the option "log to table" - logs a
fair bit.

MSDB is getting huge, even though there are timed jobs to clear
history and logs.

A table called msdb.sysjobstepslogs takes up about 98% of space. There
was only one row

So I deleted it by running: EXEC msdb.dbo.sp_delete_jobsteplog
@job_name =####

Now the table has no rows - but is still as large and sysjobstepslogs
still 98% of spaceused.

There is a suggestion of truncate table sysjobstepslogs - but bit
fearfull. Tried Shrinking dbcc etc etc no joy

2. How does one actually look at what is logged in this table
From: David Hay on 22 Oct 2009 12:52

It is probably relased, but the internal tables did not get updated
properly, so the 98% usage is incorrect.

Try running sp_updatestats, and sp_updateusage in msdb.

use msdb
go

exec sp_updateastats
go

dbcc updateusage('msdb') WITH COUNT_ROWS
go

Good luck!

David Hay
From: Yitzak on 22 Oct 2009 13:04
On 22 Oct, 17:52, David Hay <david....(a)gmail.com> wrote:
> It is probably relased, but the internal tables did not get updated
> properly, so the 98% usage is incorrect.
>
Tried all sorts - including this

The mdf file is still large.


> Try running sp_updatestats, and sp_updateusage in msdb.
>
> use msdb
> go
>
> exec sp_updateastats
> go
>
> dbcc updateusage('msdb') WITH COUNT_ROWS
> go
>
> Good luck!
>
> David Hay

From: Russell Fields on 22 Oct 2009 13:25
I don't know what version of SQL Server you are running, but you might try:

DBCC CLEANTABLE ('msdb', 'sysjobstepslogs')

RLF

"Yitzak" <terryshamir(a)yahoo.co.uk> wrote in message
news:5f805253-be89-4660-8a62-9c613b77d6b5(a)l35g2000vba.googlegroups.com...
> On 22 Oct, 17:52, David Hay <david....(a)gmail.com> wrote:
>> It is probably relased, but the internal tables did not get updated
>> properly, so the 98% usage is incorrect.
>>
> Tried all sorts - including this
>
> The mdf file is still large.
>
>
>> Try running sp_updatestats, and sp_updateusage in msdb.
>>
>> use msdb
>> go
>>
>> exec sp_updateastats
>> go
>>
>> dbcc updateusage('msdb') WITH COUNT_ROWS
>> go
>>
>> Good luck!
>>
>> David Hay
>

From: Yitzak on 22 Oct 2009 14:37
On 22 Oct, 18:25, "Russell Fields" <russellfie...(a)nomail.com> wrote:
> I don't know what version of SQL Server you are running, but you might try:
>
> DBCC CLEANTABLE ('msdb', 'sysjobstepslogs')
>
> RLF
>
> "Yitzak" <terrysha...(a)yahoo.co.uk> wrote in message
>
> news:5f805253-be89-4660-8a62-9c613b77d6b5(a)l35g2000vba.googlegroups.com...
>
> > On 22 Oct, 17:52, David Hay <david....(a)gmail.com> wrote:
> >> It is probably relased, but the internal tables did not get updated
> >> properly, so the 98% usage is incorrect.
>
> > Tried all sorts - including this
>
> > The mdf file is still large.
>
> >> Try running sp_updatestats, and sp_updateusage in msdb.
>
> >> use msdb
> >> go
>
> >> exec sp_updateastats
> >> go
>
> >> dbcc updateusage('msdb') WITH COUNT_ROWS
> >> go
>
> >> Good luck!
>
> >> David Hay
Thanks Guys

Will try it - but I think clean table works if you have altered a
table and want space back straight away..
 |  Next  |  Last
Pages: 1 2
Prev: Cannot open database error
Next: "Send Report to an Email Recipient" checkbox after SQL 2008 SP1