Re: Huge MSDB because of logshipping

Tech-Archive recommends: Fix windows errors by optimizing your registry

There can be a lot wrong with all 8 procs working on 1 query. What will all the other users do when you have 1 user monopolizing the processors? And unless this is a DW concurrency is often more desirable than hugely parallel queries. And most queries that are properly tuned will not use that many processors efficiently in the first place. You may find you have a lot of CXPacket waits if you look at your wait stats. They are essentially wasted time in parallel operations.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"JeroenSchoen" <JeroenSchoen@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:60288273-3A1F-4908-BC6C-F3AC3CF45A22@xxxxxxxxxxxxxxxx
Thanks Andrew !
ofcourse there is nothing wrong that the 8 procs are going to work on a
query; but I do not think my query is importent enough to disturb normal
production querys. So I this is the solution then I will execute it after
work hours..

thanks again,
Jeroen

"Andrew J. Kelly" wrote:

Set the MAXDOP to les than 8 procs and you won't get any one part of a query
using all the procs. This is true regardless of what you are doing on an
OLTP system.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"JeroenSchoen" <JeroenSchoen@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:78568E64-EC19-4316-AFDF-110330A6E344@xxxxxxxxxxxxxxxx
> Hi Ekrem,
> I agree, the dbcc shrinkfile did not work, my msdbdata.mdf is still 113 > GB
> :(
> There are 3 tables :
> 48230 MB --> log_shipping_monitor_error_detail
> 6106 MB --> log_shipping_monitor_history_detail
>
> Those tables are not effected by sp_cleanup_log_shipping_history or
> sp_purge_jobhistory !
> So, the main question is.. how to get rid of 100 gig of error data...
>
> when I try to run sp_purge_jobhistory to get rid o data in 47349 MB
> -->sysjobhistory
> my 8 procs machine all procs go 100% .. so I cancelled it quick (this > is
> production ..)
>
>
> "Ekrem Önsoy" wrote:
>
>> > Exec SP_SpaceUsed log_shipping_monitor_history_detail
>> > reserved: 5978352 KB
>> > data: 288600 KB
>>
>> I wonder the value of the "index" and "unused". Because ("reserved") =
>> ("data") + ("index") + ("unused").
>>
>> As the "data" in your situation is just "288.600KB" so what's this
>> remaining
>> "5.689.752KB" about? Let's figure out this.
>>
>> I'm somehow in doubt you have successfully shrinked the database.
>>
>> -- >> Ekrem nsoy
>>
>>
>>
>> "Shuwi" <jeroen.schoenmakers@xxxxxxxxx> wrote in message
>> news:be486581-6a1b-496a-a13a-461731e669eb@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
>> > Hi,
>> > we have a 40 gb sql2005 dbase on a sql2005 enterprise server which
>> > must be available 24x7x365;
>> > So I configured logshipping to another server. This works great, >> > every
>> > 5 minutes the logfile ships and we have a warm standby server.
>> >
>> > BUT...
>> > (and this is something you never read when you read about >> > logshipping
>> > configuration):
>> >
>> > Our MSDB dbase is now 110 GB ! I checked which tables are largest:
>> >
>> > Exec SP_SpaceUsed log_shipping_monitor_history_detail
>> > reserved: 5978352 KB
>> > data: 288600 KB
>> > rows: 748340
>> >
>> > I've killed some data using the:
>> > sp_cleanup_log_shipping_history
>> > which requires the agent_id parameter which i found using : select >> > top
>> > 10 * from log_shipping_monitor_history_detail with (nolock)
>> >
>> > the sp_cleanup_log_shipping_history deleted a huge amount of data in
>> > the
>> > [sysmaintplan_log] and [sysmaintplan_logdetail] tables but the MSDB
>> > still is 110 GB ! (even after a dbbc shrink file or dbcc shrink
>> > database)
>> >
>> > Please.. I'm searching for 2 days now, who can help me ?
>> > Thanks in advance!!
>> >
>> > Jeroen
>>



.

Relevant Pages

  • Re: Huge MSDB because of logshipping
    ... query; but I do not think my query is importent enough to disturb normal ... using all the procs. ... I agree, the dbcc shrinkfile did not work, my msdbdata.mdf is still 113 GB ... So I configured logshipping to another server. ...
    (microsoft.public.sqlserver.setup)
  • Re: Roles
    ... grant execute on dbo.sec_test to TEST_ROLE ... -- use the RoleID in the following query in order to return all procs in ... This is the query to get the list of all ... Grant execute rights to the new role. ...
    (microsoft.public.sqlserver.programming)
  • Re: why do I have a table scan???
    ... might try a compound index on MerchName, TranCode and see if that helps. ... Andrew J. Kelly SQL MVP "ChrisR" wrote in message ... > Its quite a long story, but the short version is this> query is going to be used to populate another table. ... >>SELECT COUNTFROM transdtl0 t ...
    (microsoft.public.sqlserver.server)
  • Re: Connection Throttle
    ... If you set MAXDOP to 1 and the query were to use all of the first ... >> Andrew J. Kelly SQL MVP ... The queries are fairly optimized, ...
    (microsoft.public.sqlserver.server)
  • Re: Performance of SQL query for reading from trace file
    ... You didn't say it was a remote query. ... The network and client will obviously have an impact on performance and will depend on what those are. ... It is not the query or SQL Server that is the holdup it must be your client or network. ... Andrew J. Kelly SQL MVP ...
    (microsoft.public.sqlserver.clients)