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"
discussions.microsoft.com> wrote in message
news:60288273-3A1F-4908-BC6C-F3AC3CF45A22@microsoft.com...> 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"
discussions.microsoft.com> wrote in message
>> news:78568E64-EC19-4316-AFDF-110330A6E344@microsoft.com...>>> 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"
gmail.com> wrote in message
>>>> news:be486581-6a1b-496a-a13a-461731e669eb@e39g2000hsf.googlegroups.com...>>>>> 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
>>>>
>>
>>
