[cisco-voip] CDR flatfiles readonly, CDRinsert not filling database

Wed Jul 12 23:50:00 EDT 2006

Arjen,

Interesting.  If you execute 'sp_who2' do you actually see CDRInsert  
logged in with all of those SPID's?  If so, can you restart CDRInsert  
to see if they clear?  If not, you will need to use 'kill <spid>' to  
kill the logins or restart SQL server.

If you do review 'sp_who2' are there any waiting processes?  And if  
so, what are they waiting on?

Troubleshooting prepare and unprepare SQL statements is covered here:
http://support.microsoft.com/Default.aspx?id=224587

/Wes

On Jul 11, 2006, at 7:20 AM, Arjen de Vries wrote:

L.S,

In short: We are having trouble with the CDR data in flatfiles not  
being inserted into the database. The flatfiles are saved in a  
readonly manner and when the CDR Insert process starts it’s getting a  
lot of threads in SQL Server and they seem to lockup.

We have made some changes with Wes his tips but didn’t see any  
improvement that is why I am reporting back to this mailing list in  
hope to find some answers.

Most solutions to our problems are related to user rights and setting  
them the right way. On both our servers we have synched the user  
settings and the services under which they are running. Now we still  
have the readonly problem with the flatfiles. And it causes many  
lockups in our SQL Server.

I have included a screenshot of the information we get from SQL  
Server. In little time it produces over a thousand threads that are  
stuck with the message in last executed SQL command: “sp_unprepare;1”.

If I can provide more information in order for anyone to help me in  
the right directions with this problem please do ask. I will try my  
best to give as many information needed to get some answers.

Maybe I have missed some detail with which I could have solved this  
problem from Wes his mail. If so I apologize.

Thanks for your time!

Arjen F. de Vries
Consultant

M.06 18397581

STS Automatisering BV
Fivelpoort 10B 9902 SR Appingedam
Postbus 117 9930 AC Delfzijl
Telefoon 0596-856550
Telefax 0596-634818
E-mail: arjen.devries at sts-automatisering.nl
Web: http://www.sts-automatisering.nl

From: Wes Sisk [mailto:wsisk at cisco.com]
Sent: dinsdag 4 juli 2006 18:36
To: Arjen de Vries
Cc: cisco-voip at puck.nether.net
Subject: Re: [cisco-voip] CDR flatfiles readonly, CDRinsert not  
filling database

Hello Arjen,

Nice problem description.

the cdr flat files are generated locally by ccm.exe running on the  
server.  This process creates the files and the files should inherit  
the directory permissions - can you check what userid ccm.exe runs  
under on your publisher and subscriber?  Then check the NTFS file  
permissions on the CallDetail,CDR, and CMR directories?

Once files are written the Aupair.exe (Cisco Database Layer Monitor)  
process on the subscriber moves the files up to the publisher server  
via the windows share \\<publisher>\cdr.  Can you verify what user  
Aupair.exe is running under on the subscriber?  Can you verify  
permissions on the cdr share on the publisher?

Then CDRInsert starts up and does its job reading files from the  
directory on the publisher.

When you say the files are readonly, do you mean by file permissions  
or by the readonly attribute (attrib <filename> from cmd prompt)?

I have found the cdr files marked as readonly just a few times on CM  
systems.  I have never been able to find the process that marked them  
as readonly.  Can you use filemon from www.sysinternals.com to  
monitor the cdr files, clear the readonly attribute and capture the  
filemon log showing what process sets the readonly attribute?

CSCsd88223 is another issue in recent versions of CM that prevent  
files from properly inserting into the database, but this is because  
of locks inside the SQL database.  Typically one of the steps in the  
'IPT Optimizations' SQL job grabs and holds locks causing this problem.

/Wes

On Jul 4, 2006, at 7:25 AM, Arjen de Vries wrote:

L.s,

Recentely we have started using Cisco Call Manager. The CDR data is  
stored in flat files. This data can be set to be updated to a  
database. And it should insert the history (old flatfiles) to this  
database too. Now we have the next problem. The CDRInsert process  
knows where the flatfiles are located. These flatfiles seem to be  
read only, so the CDRinsert can’t process them. If we manually set  
the property of the flatfiles to be writeable, the CDRinsert process  
changes it after the first processing of files and doesn’t seem to  
insert anything in the datatable CallDetailRecord.

We have 2 CallManagers, a publisher and a subscriber. On both  
CallManagers there is a SQL Server running. On both servers there is  
the database CCM0300. On the first (publisher) server there is a CDR  
database. In witch some of the CallDetailRecords are available.

Is this a known issue? And is there some setting I have missed? I  
cant seem to find information about this anywhere. If there is more  
information needed on the configuration please report back to me. I  
am not really comfortable with the call manager settings yet. So I  
might need some direction on the information needed to properly  
analyze this problem.

Thanks in advance.

Kindest regards,

Arjen F. de Vries
Consultant

STS Automatisering BV
E-mail: arjen.devries at sts-automatisering.nl
Web: http://www.sts-automatisering.nl

_______________________________________________

cisco-voip mailing list

cisco-voip at puck.nether.net

https://puck.nether.net/mailman/listinfo/cisco-voip

<SQL-server-CCM1_bw.gif>

-------------- next part --------------
An HTML attachment was scrubbed...
URL: https://puck.nether.net/pipermail/cisco-voip/attachments/20060712/599f29c9/attachment-0001.html 