Community Tip - Did you get called away in the middle of writing a post? Don't worry you can find your unfinished post later in the Drafts section of your profile page. X
Have anyone had this issues:
Need someones SQL Server expertise assistance,
Thanks guys,
Patrick
Thanks Steve,
But, all we did all that because that is standard practice for SQL Server database admin. I took the course 3 weeks ago to confirm our installation. My main concers are that even in the oracle to SQL server migration guide there are logical disconnects which leads to the issue of index conflict of 900 bytes/characters. Since we change the database to
and during the upgrade the VARCHAR should change was originally:
http://jahaines.blogspot.com/2010/06/why-are-transactions-be-blocked-all-of.html
It sounds like SQL Server is really meant for serialized transactions and "small" concurrent parallel transactions. Concurrent big Data Manipulation Lanuguage (DML) of selects, updates, inserts with concurrency is required for a mature production Windchill system. Lock escallation may automatically occur. For my database size, we are experiencing this issue at just starting windchill which is due to many factors:
There could be many reasons for this which still needs some investigation.
Thanks for trying to help,
Patrick
The versions of MS SQL Server and MS SQL Server Migration Assistant (SSMA) that was used to perform the migration from Oracle 11G R2 64bit were
PTC currently only has tested and supports Microsoft SQL Server 2005 Enterprise Edition SP2. SQL Server 2005 R2 is not even mentioned in the PTC Software Compatibility Matrix. SQL Server 2005 R2 is not 100% 64bit compliant because it is based on a 32 bit platform with 64 bit additional modules. Like PTC, they would rather you invest you time using a improved platform in SQL Server 2008 R2 than dealing with problematic SQL Server 2005 legacy 32 bit software issues. Thus, failure in migration was inevitable with our large data size with SQL Server 2005.
According to Microsoft, its trainers, and other SQL Server 2005 and 2008 R2 DBAs that I am in communication with, our 300 million records would most likely never be successful unless we used SQL Server 2008 R2. Our migration was even failing with 24 GIGs of ram with 100% CPU and 100% memory utilized. We increased the RAM to 48GIGs which resulted in a successful migration but at 100% memory utilized for last 200 million records. In the past emails, we captured and completely executed the pre and post migration steps with no errors with SQL Server 2008 R2.
Since migrating Oracle 11G R2 to SQL Server is only using MS SSMA which MS advises, recommends and fully 100% supports using SQL Server 2008 R2 and SSMA 5.1 when dealing with large databases, I followed Microsoft support, trainers and other dba recommendations which lead to a successful migration. I don’t have the bandwidth, resources or budget to deal with failure constant SSMA failures due to memory allocation issues on a large database.
Microsoft support service remotely accessed our Windchill server and SQL Server 2008 R2 and instantly recognized the issue. It was not a due to escalation locks. It was due to sleeping transactions which blocks other transactions.
We would like a patch for Windchill 9.1 M050 for this sleeping transaction issues and other potential SQL Server issues. Sleeping transaction mostly occur when the application does not handle all the execptions for SQL Server transactions. The support person from Microsoft confirmed that these exceptions are standard in SQL 2005 and 2008 R2.
The issue occurs when implementing multiple method servers (foregrounds and 3 backgrounds). There are possible causes to errors when reading the database when starting multiple methodservers.
We had to kill spids and constantly go down the cascading blocked due to sleeping transactions:
For example,
select * from master.sys.sysprocesses where spid=64
kill 64