MSRepl_Commands question

SORRY FOR THE LONG POST.
I am using transactional replication. I have an update process that is
run by users (not dbas). The update process among many other things
will delete replication, perform updates, and then reinstall
replication. This process has been working great for us (in testing).
However, there are times when the distribution agent hasn't finished
processing all the rows in msrepl_commands and the replication gets
dropped which causes lost (not replicated) commands.
We started by just running sp_browereplcmds with the proper
publication_database_id value to see if any commands were outstanding.
The thought was to check the recordset, if empty, then proceed, else
wait, until specified time then cancel the update with a nice message
about waiting until replication was complete. Great, right? Wrong.
Even after replication is finished and EVEN after the cleanup agent is
run (we are considering running it manually and rechecking the list),
there are commands STILL in the table. They all have command type
values of -2147xxxx and they all appear to be snapshot commands.
Sorry for the long post, but here's the question: Does anyone know
where I can find a key to these values? I would like to just ignore any
-2147xxxx type command in the table and proceed with dropping
replication.
Any help would be appreciated!
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
this is as near as I can figure
-2147483641 directory check
-2147483598 file check
-2147483597 unknown
-2147483646 apply schema
-2147483646 apply index
-2147483645 sync command
-2147483596 unknown
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"PBarbin" <pbarbin@.nospam.com> wrote in message
news:%23lDmE10aEHA.2516@.TK2MSFTNGP10.phx.gbl...
> SORRY FOR THE LONG POST.
> I am using transactional replication. I have an update process that is
> run by users (not dbas). The update process among many other things
> will delete replication, perform updates, and then reinstall
> replication. This process has been working great for us (in testing).
> However, there are times when the distribution agent hasn't finished
> processing all the rows in msrepl_commands and the replication gets
> dropped which causes lost (not replicated) commands.
> We started by just running sp_browereplcmds with the proper
> publication_database_id value to see if any commands were outstanding.
> The thought was to check the recordset, if empty, then proceed, else
> wait, until specified time then cancel the update with a nice message
> about waiting until replication was complete. Great, right? Wrong.
> Even after replication is finished and EVEN after the cleanup agent is
> run (we are considering running it manually and rechecking the list),
> there are commands STILL in the table. They all have command type
> values of -2147xxxx and they all appear to be snapshot commands.
> Sorry for the long post, but here's the question: Does anyone know
> where I can find a key to these values? I would like to just ignore any
> -2147xxxx type command in the table and proceed with dropping
> replication.
> Any help would be appreciated!
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Thanks for the reply. As far as I can tell, transactional replication
commands are of type 30.
As an alternate question, can anyone think of a way that I can be SURE
that transactional replication is finished before deleting it and
readding?
Paul
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Paul,
you could compare the xact_seqno in MSDistribution_history (last processed
transaction sequence number) to the values in msrepl_commands. Presumably,
if the last row in each table has the same xact_seqno (after reformatting
slightly), then you're ok.
HTH,
Paul Ibison
|||Paul, that may help tremendously! I'll look into it. Have you noticed
that sp_browsereplcmds shows commands even when there are none to be
processed. I was hoping it was just because we were in a test
environment and we weren't always playing by the rules, but I didn't
want to ship this to all customers without being pretty confident that
my solution would work. I'll try this get back...
Thanks.
Paul
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Background
When my program is updated in the future, the first step will be to
delete the transactional replication, make all database changes to the
publisher and the subscriber and then reinitialize replication. We want
to make sure that the distribution agent has finished processing all
outstanding transactions before dropping it.
Problem
We considered checking the status of the distribution agent but realized
that we couldn't. One reason is that it never "finishes". This agent
runs continuously. Also, its possible to have the status of the agent
as "Idle" even when there are commands left to be processed (this
process is asynchronous).
So, we decided to query the system table, MSRepl_Commands, directly.
This table has a few interesting problems. One is that commands are not
dropped immediately after being processed by replication. Another is
that rows exist in this table for both snapshot and transactional
replication. As usual, the only identifier to distinguish them was not
documented and we were not completely sure which rows to filter. The
stored procedure that Microsoft recommends using doesn't take these
items into consideration and will return rows when transactional
replication is "finished".
Solution
Profiling the agent gave us all the information we needed. We found
that the subscription database contains a table that tracks the last
transaction number processed for each distribution agent that hits the
database (we only have 1). Now we can filter out all the old commands
by checking their transaction number against this value. Also, we saw
that one of the stored procedures defines a snapshot bit (0x80000000)
and uses a bitwise AND on this undocumented "Type" column to see which
commands are snapshot and which are not. Using both of these pieces of
info, we are able to query the table to accurately determine the status
of transactional replication.
Hope this helps somebody. I find it amazing how little documentation
exists regarding replication. If it weren't for the great posters (Paul
I. and Hilary C) on this group, a LOT of us would be in trouble.
Thanks!
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

MSOLAP Error. Please help!

I have an Openrowset query, which connects to a cube and obtains the data in a table. This is run every day. This process has been running for over a year but now all of a sudden doesn't work and gives the erros mentioned below. When I reduce the records to be returned to about 1/4, then it runs fine else not. We are using Enterprise Version. Any help will be really appreciated.

Error when tried once -

OLE DB provider 'MSOLAP' reported an error. The provider ran out of memory.
[OLE/DB provider returned message: An error occurred during attempt to get a cell value.]
[OLE/DB provider returned message: The operation has failed because of an error in the COM component - unknown error ]
OLE DB error trace [OLE/DB Provider 'MSOLAP' IRowset::GetNextRows returned 0x8007000e: The provider ran out of memory.].

The second time I received -

ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 77 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
[OLE/DB provider returned message: An unexpected internal error has occurred.] [Microsoft][ODBC SQL Server Driver]Function sequence error

Thanks,
SauravRevisiting archived thread here: http://www.dbforums.com/archive/index.php/t-534601.html

msmdsrv.exe takes more than 95% cpu

Hi,
The process msmdsrv.exe (ver 8.00.2039) takes more than 95% cpu. The PC is
very very slow. What can I do? I have SP4 for Analysis Services installed.
I thought SP4 could correct the problem but it did not.
When I looked at the Analysis Services in MMC, the PC icon had a small red
square, which indicates the Analysis Services was not running. I did not
connect to it, since the PC was nearly halted.
Please help me!
Thanks.
Actually msmdsrv *IS* Analysis Services. It is the service.
So it is running -- regardless of what the icon is saying. This just means
that the service APIs cannot detect that it is in a running state. Typically
msmdsrv going into a high CPU loop when the lazy aggregator is running (i.e.
someone kicked off a changing dimension incremental process and the flex
aggs are being reprocessed), or a full processing is being done for a
specific cube, etc.
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Chrissi" <anubisofthydeath@.hotmail.com> wrote in message
news:uuLYlX6cFHA.2420@.TK2MSFTNGP12.phx.gbl...
> Hi,
> The process msmdsrv.exe (ver 8.00.2039) takes more than 95% cpu. The PC
> is very very slow. What can I do? I have SP4 for Analysis Services
> installed. I thought SP4 could correct the problem but it did not.
> When I looked at the Analysis Services in MMC, the PC icon had a small red
> square, which indicates the Analysis Services was not running. I did not
> connect to it, since the PC was nearly halted.
> Please help me!
> Thanks.
>
|||Do you have a solution to this problem? My OS is windows 2000 server. I
have another PC with Analysis Services. The msmdsrv.exe does not take cpu.
"Dave Wickert [MSFT]" <dwickert@.online.microsoft.com> wrote in message
news:%23qC1fGfdFHA.2696@.TK2MSFTNGP09.phx.gbl...
> Actually msmdsrv *IS* Analysis Services. It is the service.
> So it is running -- regardless of what the icon is saying. This just means
> that the service APIs cannot detect that it is in a running state.
> Typically msmdsrv going into a high CPU loop when the lazy aggregator is
> running (i.e. someone kicked off a changing dimension incremental process
> and the flex aggs are being reprocessed), or a full processing is being
> done for a specific cube, etc.
> --
> Dave Wickert [MSFT]
> dwickert@.online.microsoft.com
> Program Manager
> BI SystemsTeam
> SQL BI Product Unit (Analysis Services)
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Chrissi" <anubisofthydeath@.hotmail.com> wrote in message
> news:uuLYlX6cFHA.2420@.TK2MSFTNGP12.phx.gbl...
>
|||I had the same problem albiet under Windows XP. I resolved it by:
1: Go to Control Panel/Admin Tools/Services. Find the
MSSQLServerOLAPService, choose properties and check the 'Allow service to
interact with desktop' checkbox.
2: Allow the msmdsrv.exe file through your firewall (file typically found in
the \program files\Microsoft Analyst Service\bin\ folder.
I'm not sure if 2: is required but it seemed to help.
This resolved the problem - I'm not technically competent enough to know why
but it worked!
Good luck.
"§Chrissi§" wrote:

> Do you have a solution to this problem? My OS is windows 2000 server. I
> have another PC with Analysis Services. The msmdsrv.exe does not take cpu.
> "Dave Wickert [MSFT]" <dwickert@.online.microsoft.com> wrote in message
> news:%23qC1fGfdFHA.2696@.TK2MSFTNGP09.phx.gbl...
>
>

msmdsrv.exe takes more than 95% cpu

Hi,
The process msmdsrv.exe (ver 8.00.2039) takes more than 95% cpu. The PC is
very very slow. What can I do? I have SP4 for Analysis Services installed.
I thought SP4 could correct the problem but it did not.
When I looked at the Analysis Services in MMC, the PC icon had a small red
square, which indicates the Analysis Services was not running. I did not
connect to it, since the PC was nearly halted.
Please help me!
Thanks.Actually msmdsrv *IS* Analysis Services. It is the service.
So it is running -- regardless of what the icon is saying. This just means
that the service APIs cannot detect that it is in a running state. Typically
msmdsrv going into a high CPU loop when the lazy aggregator is running (i.e.
someone kicked off a changing dimension incremental process and the flex
aggs are being reprocessed), or a full processing is being done for a
specific cube, etc.
--
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Chrissi" <anubisofthydeath@.hotmail.com> wrote in message
news:uuLYlX6cFHA.2420@.TK2MSFTNGP12.phx.gbl...
> Hi,
> The process msmdsrv.exe (ver 8.00.2039) takes more than 95% cpu. The PC
> is very very slow. What can I do? I have SP4 for Analysis Services
> installed. I thought SP4 could correct the problem but it did not.
> When I looked at the Analysis Services in MMC, the PC icon had a small red
> square, which indicates the Analysis Services was not running. I did not
> connect to it, since the PC was nearly halted.
> Please help me!
> Thanks.
>|||Do you have a solution to this problem? My OS is Windows 2000 server. I
have another PC with Analysis Services. The msmdsrv.exe does not take cpu.
"Dave Wickert [MSFT]" <dwickert@.online.microsoft.com> wrote in message
news:%23qC1fGfdFHA.2696@.TK2MSFTNGP09.phx.gbl...
> Actually msmdsrv *IS* Analysis Services. It is the service.
> So it is running -- regardless of what the icon is saying. This just means
> that the service APIs cannot detect that it is in a running state.
> Typically msmdsrv going into a high CPU loop when the lazy aggregator is
> running (i.e. someone kicked off a changing dimension incremental process
> and the flex aggs are being reprocessed), or a full processing is being
> done for a specific cube, etc.
> --
> Dave Wickert [MSFT]
> dwickert@.online.microsoft.com
> Program Manager
> BI SystemsTeam
> SQL BI Product Unit (Analysis Services)
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Chrissi" <anubisofthydeath@.hotmail.com> wrote in message
> news:uuLYlX6cFHA.2420@.TK2MSFTNGP12.phx.gbl...
>|||I had the same problem albiet under Windows XP. I resolved it by:
1: Go to Control Panel/Admin Tools/Services. Find the
MSSQLServerOLAPService, choose properties and check the 'Allow service to
interact with desktop' checkbox.
2: Allow the msmdsrv.exe file through your firewall (file typically found in
the \program files\Microsoft Analyst Service\bin\ folder.
I'm not sure if 2: is required but it seemed to help.
This resolved the problem - I'm not technically competent enough to know why
but it worked!
Good luck.
"§Chrissi§" wrote:

> Do you have a solution to this problem? My OS is Windows 2000 server. I
> have another PC with Analysis Services. The msmdsrv.exe does not take cp
u.
> "Dave Wickert [MSFT]" <dwickert@.online.microsoft.com> wrote in message
> news:%23qC1fGfdFHA.2696@.TK2MSFTNGP09.phx.gbl...
>
>sql

msftesql memory usage

We have been testing SQL 2005 full-text search, and noticed that the
msftesql process isn't using much memory (only around 6 megs), when the
single table we're indexing has about 3.8 millions rows, with 4 varchar
columns indexed ranging between varchar(255) and varchar(750). Is this
normal, i.e., is the full text search service using sqlservr.exe's memory?
Our server is a 2-way 2.0 GHZ opteron with 4GB RAM using WIN2K server with
/3GB switch.
We've looked around for a way to increase the memory available to msfteql
without success.
Thanks,
John
John,
Yes, its normal, but you can control the "aggressiveness" of the SQL Server
2005 MSFTESQL service via several advance sp_configure settings. Run the
below SQL code and review the new fulltext search options:
use master
go
sp_configure 'show advanced options', 1
reconfigure with override
go
sp_configure
There should be several new option to allow you to control the memory
allocation of the MSFTESQL service. No, the MSFTESQL uses its own memory
separate from the SQL Server memory.
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"John" <jglass67@.msn.com> wrote in message
news:uocvvK2lFHA.764@.TK2MSFTNGP14.phx.gbl...
> We have been testing SQL 2005 full-text search, and noticed that the
> msftesql process isn't using much memory (only around 6 megs), when the
> single table we're indexing has about 3.8 millions rows, with 4 varchar
> columns indexed ranging between varchar(255) and varchar(750). Is this
> normal, i.e., is the full text search service using sqlservr.exe's memory?
> Our server is a 2-way 2.0 GHZ opteron with 4GB RAM using WIN2K server with
> /3GB switch.
> We've looked around for a way to increase the memory available to msfteql
> without success.
> Thanks,
> John
>

MsDtsSrvr.exe memory leak?

In our current environment, we are running about 10,000 packages per day on a given 64 bit SSIS server.

The MSDtsSrvr.exe (SSIS service) process' memory working set size continues to grow until it consumes the memory on the machine.

Some of the packages do use a custom component. Could this be responsible for leaking memory in the MSDtsSrvr.exe process? Has a memory leak been confirmed in this process for the "first-party" components anyway?

More information about the possible memory leak.

The packages are run in in a CLR process with calls Package.Execute(), rather than being executed via dtexec or dtexecui or the designer. The reason for this approach is the ability to write a custom event handler with access to the package pointer, which other log handlers cannot do. The process runs Package.Execute() and exits, so there are 10,000 short-lived processes, each of which call Package.Execute() once.

To be slightly more specific about what the packages are doing, each of the 10,000 packages generally correlates to 1 source file, loading in dummy (aka inferred) dimension records, and then populating a fact table via a surrogate key pipeline.

MSDTC / W2K3 / SQL 2000 Clustering

I'm in the process of setting up my first W2K3 / SQL 2000 cluster and I have a few questions with regards to MSDTC.
Documentation I have says that MSDTC must be clustered in order for SQL clustering to work. It also says that you cannot use comclust.exe anymore and that MSDTC must be setup in its own group with the following ( VIP, Network Name, and dedicated shared di
sk). I currently have Windows and SQL 2000 clustering up and running without MSDTC clustered.
My questions are :
1) Do I need MSDTC at all? If so, why?
2) If I do need MSDTC clustered, can I use a disk that has other applications installed on it? (I only have two LUNS to work with on the SAN, my quorum device and a device I'm using for SQL data / logs)
I know that I haven't given a lot of background information, but I was hoping I could get a discussion started.
Thanks.
Comments inline.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"tech_arch_guy" <tech_arch_guy@.discussions.microsoft.com> wrote in message
news:2B8AD70E-877C-4F02-A0F7-7A51B072075C@.microsoft.com...
> I'm in the process of setting up my first W2K3 / SQL 2000 cluster and I
have a few questions with regards to MSDTC.
> Documentation I have says that MSDTC must be clustered in order for SQL
clustering to work. It also says that you cannot use comclust.exe anymore
and that MSDTC must be setup in its own group with the following ( VIP,
Network Name, and dedicated shared disk). I currently have Windows and SQL
2000 clustering up and running without MSDTC clustered.
> My questions are :
> 1) Do I need MSDTC at all? If so, why?
Yes. Setup will fail without MSDTC.
> 2) If I do need MSDTC clustered, can I use a disk that has other
applications installed on it? (I only have two LUNS to work with on the SAN,
my quorum device and a device I'm using for SQL data / logs)
>
W2000 used the quorum device as the MSDTC physical disk by default. W2003
recommends using another disk, but in your case the qourum drive is probably
OK. I have a cluster upgraded from W2000 to W2003 with MSDTC on the quorum
device and it works just fine.

> I know that I haven't given a lot of background information, but I was
hoping I could get a discussion started.
> Thanks.
|||You do NOT need MSDTC clustered for installing SQL Server 2000 virtual instance on a Windows 2003 Microsoft Cluster Server. You need MSDTC only if you doing distributed transactions. you can cluster MSDTC
even after installing SQL Server 2000 virtual instance.
Refer the foll. KB for Win2K3
How to configure Microsoft Distributed Transaction Coordinator (MSDTC)on a Windows Server 2003
http://support.microsoft.com/?kbid=301600
How to enable network DTC access in Windows Server 2003
http://support.microsoft.com/default.aspx?kbid=817064
It is recommended to use a seperate shared disk (500MB works) for MSDTC. In your case you just have the qourum disk and sql disk. I have seen many clusters that have MSDTC using the Quorum disk. Usually if
you are not doing a lot of distributed transactions then leave that way if everything is working fine. But if you are building your cluster and want to build as per best practices then the recommendation is to use a
seperate disk for MSDTC.
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx
|||I agree with Uttam but if you like to create DTC resource if you have
distributed transactions or even linked servers, you will be creating it just
like you create a disk resource. Make sure you have a disk, name and ip
resource created for MSDTC.
"Uttam Parui[MS]" wrote:

> You do NOT need MSDTC clustered for installing SQL Server 2000 virtual instance on a Windows 2003 Microsoft Cluster Server. You need MSDTC only if you doing distributed transactions. you can cluster MSDTC
> even after installing SQL Server 2000 virtual instance.
> Refer the foll. KB for Win2K3
> How to configure Microsoft Distributed Transaction Coordinator (MSDTC)on a Windows Server 2003
> http://support.microsoft.com/?kbid=301600
> How to enable network DTC access in Windows Server 2003
> http://support.microsoft.com/default.aspx?kbid=817064
> It is recommended to use a seperate shared disk (500MB works) for MSDTC. In your case you just have the qourum disk and sql disk. I have seen many clusters that have MSDTC using the Quorum disk. Usually if
> you are not doing a lot of distributed transactions then leave that way if everything is working fine. But if you are building your cluster and want to build as per best practices then the recommendation is to use a
> seperate disk for MSDTC.
> Best Regards,
> Uttam Parui
> Microsoft Corporation
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
> Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
> http://www.microsoft.com/protect
> http://www.microsoft.com/security/guidance/default.mspx
>
>