SQL Server 2008 R2 issues

[patrick.chin1]

Have anyone had this issues:

• Blocking SQL statements which freezes Method Server start up.


• Having issues with more than one methodserver


• All 48 GIG of RAM is allocated from the SQL Server due at startup of Windchill. Will now move to 96GIG for production.
  
  
  
  • could be due to the number of queues
  
  
  • could be many things of index issues.
  
  


• Indexes limited to 900 bytes, but ORACLE has columns that are VARCAR 2000 which is already beyond the limit when combining more than 1 column for an index for a table.
  
  
  
  • How does one increase that defalt limit to 5000 bytes
  
  
  • 900 bytes =0.87890625 Kb and a SQL page limit is 8 Kb
  
  

Need someones SQL Server expertise assistance,

Thanks guys,

Patrick

[patrick.chin1]

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:

• 
  
• starting up all the services
• 
  
• starting up all the queues and executing queues
• 
  
• quering the entire system of users, products, etc.
• 
  

There could be many reasons for this which still needs some investigation.

Thanks for trying to help,

Patrick

[patrick.chin1]

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

• 
  
• Microsoft SQL Server 2008 R2 (fully 100% based on a 64 bit platform) Enterprise and
• 
  
• SSMA 5.1 with extension pack respectfully.
• 
  

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.

• 
  
• With Multiple Method Servers there will always be errors when other methodservers try to start adapters that have already been started by another methodserver.
• 
  
• Deleted schedules that are not cleaned up in the queue.
• 
  
• Etc.
• 
  

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