Prev: Cannot open database error
Next: "Send Report to an Email Recipient" checkbox after SQL 2008 SP1
From: Russell Fields on 22 Oct 2009 14:51
Reread one of your earlier statements about "the mdf file is still big".
Yes, freeing space in a database will not cause the database file to shrink.
It just opens up space for reuse by other data. So, if your database now
says something like 25% usage, instead of 98% usage, that is what happened
to you.

If you need to make the mdf smaller for some reason, you can do it. But you
should not unless you expect the database to stay small. If it will just
grow again, this is a bit of wasted effort. Read this and decide what you
want to do:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp

RLF


"Yitzak" <terryshamir(a)yahoo.co.uk> wrote in message
news:2e392429-e6a3-4a38-a77f-f0e225c85828(a)p23g2000vbl.googlegroups.com...
> 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..

From: Yitzak on 22 Oct 2009 15:13
On 22 Oct, 19:51, "Russell Fields" <russellfie...(a)nomail.com> wrote:
> Reread one of your earlier statements about "the mdf file is still big".
> Yes, freeing space in a database will not cause the database file to shrink.
> It just opens up space for reuse by other data.   So, if your database now
> says something like 25% usage, instead of 98% usage, that is what happened
> to you.
>
> If you need to make the mdf smaller for some reason, you can do it.  But you
> should not unless you expect the database to stay small.  If it will just
> grow again, this is a bit of wasted effort.   Read this and decide what you
> want to do:http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>
> RLF
>
> "Yitzak" <terrysha...(a)yahoo.co.uk> wrote in message
>
> news:2e392429-e6a3-4a38-a77f-f0e225c85828(a)p23g2000vbl.googlegroups.com...
>
> > 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..

Take your point.

But even with no rows in the offending table, shrink file shows only
8% is free - after having removed the job logs running dbcc,
updatestats and a Shrink.

Want to reduce the file size as the huge logging job that made it grow
to this size has been removed - and space is at a premium - after all
its just logs.

Am tempted to do a Truncate table - which I've read sorts this problem
out, but its a system table



From: David Hay on 22 Oct 2009 16:24
ok, how huge is huge? Other than being huge, is it causing problems?
I have a few large msdb databases, but they are larger for a reason,
and I use the data that's in them. What do you have the jobhistory
set to?

Right click on sql agent, go to properties, then job system or
history. Set some of those properties, give it a few days and see if
things don't get pruned.

Are you doing any regularly scheduled maintenece on MSDB? Indexes
etc?

Also take a read at this:

http://www.brentozar.com/archive/2009/09/checking-your-msdb-cleanup-jobs/

http://www.brentozar.com/archive/2009/05/brents-backup-bottleneck-msdb/

Again, good luck!

David Hay
From: Yitzak on 22 Oct 2009 17:13
On 22 Oct, 21:24, David Hay <david....(a)gmail.com> wrote:
> ok, how huge is huge?  Other than being huge, is it causing problems?
> I have a few large msdb databases, but they are larger for a reason,
> and I use the data that's in them.  What do you have the jobhistory
> set to?
>
> Right click on sql agent, go to properties, then job system or
> history.  Set some of those properties, give it a few days and see if
> things don't get pruned.
>
> Are you doing any regularly scheduled maintenece on MSDB?  Indexes
> etc?
>
> Also take a read at this:
>
> http://www.brentozar.com/archive/2009/09/checking-your-msdb-cleanup-j...
>
> http://www.brentozar.com/archive/2009/05/brents-backup-bottleneck-msdb/
>
> Again, good luck!
>
> David Hay

David

We don't really use the logs, they are there to diagnose problems for
us.

We have clean ups etc working, scheduled reindexes. Yet its
frustrating as I can see no rows in the table, yet it is basically
99% of disk spaced used by msdb.

I used the ms sp_delete_## log sps to delete them and then shrink msdb
- no effect.

At least found one job that was logging 20 mb to this table at a
time!! put a stop to it and msdb is growing slower now but still
growing.



From: David Hay on 23 Oct 2009 08:14

Still don't know how huge is huge, but if it is really a problem..
Here is a sledge hammer to fix it.

Script out all your jobs...

Script out or save all your dts packages elsewhere. here is a good
tool to do just that, http://www.sqldts.com/default.aspx

Then rebuild MSDB from scratch, reimport your DTS packages, and run
the job scripts to recreate the jobs.

http://www.tek-tips.com/viewthread.cfm?qid=1102001&page=2

http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/06/619304.aspx

Good Luck!

David Hay
First  |  Prev  | 
Pages: 1 2
Prev: Cannot open database error
Next: "Send Report to an Email Recipient" checkbox after SQL 2008 SP1