Post New Web Links

SQL Server + .Net | Transaction | Can Transaction level be esclated

Posted By: Posted Date: September 10, 2010 Points: 0 Category :Sql Server

Hi We are facing a unique problem which ends up causing deadlocks. We have found that We have a very heavy load OLTP Sql server 2008. In .net code we set the Transaction isolation level to Read commited while executing a SP. We find deadlocks and in the dead lock details it is explictly called out that the SP in question was executing in transaction level serializable. Lets call this SP1 The only other thing we can say is that there are some other unrelated stored procedures which access the same tables as SP1 and are executing in isolation level serializable. Is it possible for one SP to escalate isolation level like locks are escalated? We found the following links which hint this, by saying that isolation level is just a hint. http://msdn.microsoft.com/en-us/library/ms229978(v=VS.90).aspx http://technet.microsoft.com/en-us/magazine/2008.04.blocking.aspx?pr=blog http://msdn.microsoft.com/en-us/library/ms189857.aspx http://aartemiou.blogspot.com/2009/01/table-level-locking-hints-in-sql-server.html Any help in this regard will be great.

View Complete Post

More Related Resource Links

SQL Server Database Transaction

A transaction is a group of SQL commands executed together as a single component of work to be accomplished. If all of these commands succeed, then a transaction is committed - the changes to the data are made final. If any of the commands within a transaction fail, then the entire transaction is cancelled, or in SQL Server terms, the transaction will be rolled back.

Transactions are one of the things that keep your data safe, but they're not necessarily easy to understand. Here's some help:

multiple executions of MERGE statement: Help with suitable TRANSACTION ISOLATION LEVEL

Folks, I am reasonably new to SQL Server. I am using SQL Server 2008 (no SP) on Windows XP. I am using the MERGE statement within a TSQL procedure to update a master/detail table pair (Master/Child), in which the MERGE inserts into the MASTER if a record based on the primary key doesn't exist and does, effectively nothing, if it does (well, it does an UPDATE set PK=PK so the record is passed on to the OUTPUT statement for insertion into the CHILD). Regardless as to the situation, the CHILD record has a record created when the MASTER exists or doesn't exist. Now, this code works fine with the standard TRANSACTION LEVELS. But I don't know what to do when I am running two instances of the same MERGE statement at the same time. One execution could create a record in the MASTER which the other process might try and create 5 minutes later. I really don't know what SET TRANSACTION ISOLATION LEVEL to use to allow both processes to run at the same time. I have looked at: ALTER DATABASE $(usedbname) SET READ_COMMITTED_SNAPSHOT ON; ALTER DATABASE $(usedbname) SET ALLOW_SNAPSHOT_ISOLATION ON; But these don't seem to work with their associated TSQL calls. I know this is a complex issue, but as a new SQL Server user, I didn't know where else to go. regards Simon

Transaction Handling in SQL Server 2008

Hi there, I am using .NET (C#) and sql server 2008 for my application, I am fetching records from a table and using gridview to show them. Now saving selected records (using checkbox to select records) into another table row by row. before saving the records if records with same ID is present need to delete those records. could anyone tell me how to use transaction in SQL server 2008. any blog or website............... anything Thanks, Sadaf

Doesn't restart sql server rollback uncommitted transaction?

hi, i executed a long-running sql in the management studio. The sql updates many rows and all updates are within a transaction. After runing 30 mins, the hard disk is full. That's because of the uncommitted updates. Then, i restarted the sql server because I thought after restarting, the uncommitted transaction is rollback and the occupied hard disk space is also released. But i found that the transaction is rollbacked but the hard disk space is not released. Why the hard disk space is not released? how can i release the disk space now? Thank you for your help.

Very high transaction log file growth in SQL server

Hi, I am using with below version of SQL server. Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2). One of my database transaction log file is getting full very frequently on PROD database,which i have truncated ones and shrunk ones after that i have taken full backup immediately.Again the Db tran log file grown to very big size. We are taking the transaction log backup for evry one hour. and The column shows log_reuse_wait=2 & log_reuse_wait_desc=LOG_BACKUP from sys.databases. Can you please suggest me free the transaction log space without truncating the log file. Thanks, Gangadhar

Serve to server Transaction Replication error

I have windows 2003 sp2 servers each hosts SQL Server 2008 SP1 standard edition. I am trying to replicate both servers, using the tutorial available in the online documentation <Tutorial: Replicating Data Between Continuously Connected Servers> which can be found at: ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10rp_1devconc/html/9c55aa3c-4664-41fc-943f-e817c31aad5e.htm

I have created the users (repl_logreader, repl_distibution, repl_snapshot, and repl_merge and setting their security. Then I have created the distibutor, setting database security as mentioned in the document, and last creating the publication with a snapshot to be run immediately, distributor and publisher are on the same server and the publication will be pushed to the subscriber. Once the publication is created succesfully, I am checking the agents to see the errors. I am always stuck with the following two error messages:

1- snapshot agent error:

Error messages:

Message: Failed to create AppDomain "mssqlsystemresource.dbo[runtime].1090".
The domain manager specified by the host could not be instantiated.
Failed to create AppDomain "mssqlsystemresource.dbo[runtime].1091".
The domain manager sp

The server failed to resume the transaction. Desc:4800000002

Hi friends !

I have a problem with SQL Server 2008. When I insert / update into DB then exception is thrown:

The server failed to resume the transaction. Desc:4800000002

Pls help me solve this problem.

Thank u

Distributed transaction using linked server not working in SQL Server 2008 64 bit

Hi. I have had an issue trying to get distributed transactions to work in SQL Server 2008 using a linked server. The error message I get is

OLE DB provider "SQLNCLI10" for linked server "pod1" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Line 3
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "pod1" was unable to begin a distributed transaction.

My Environment:

Windows Server 2008 Enterprise 64 bit, SQL Server 2008 Enterprise 64 bit

Problem occurs with multiple different builds of SQL Server 2008 - I have been able to reproduce the problem with 10.0.1600.0, 10.0.1779.0 (CU 2), as well as 10.0.2531 (SP1)

I am aware that other people have had this issue and have reviewed all existing posts. I have verified that the MSDTC is configured correctly on both machines. I have also used DTCTester to verify that DTC is working correctly on all machines in question. None of the mentioned resolutions has solved this problem for me.

I am not seeing this problem occur on my 32 bit test machines - it is only occurring if at least one of the two machines is 64 bit.

Cannot use Serializable Transaction Level with Linked Servers

I am testing an upgrade from SQL Server 2005 to SQL Server 2008 R2. I have a stored procedure which attempts to use the serializable transaction isolation level on a distributed transaction (linked server).

The query fails on SQL Server 2008 R2 with

---------

OLE DB provider "SQLNCLI10" for linked server "SERVER1" returned message "No transaction is active.".

Msg 7391, Level 16, State 2

The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "SERVER1" was unable to begin a distributed transaction.

-----------

If I change the isolation level to read committed, the transaction succeeds. This same procedure worked on SQL Server 2005 but now fails on SQL Server 2008 R2 using the same isolation level.

Is there a way to get this isolation level to work with linked servers on SQL Server 2008 R2?

advantages of transaction log backup in sql server 2008?

i want to know what are the advantages of transaction log backup in sql server 2008?

Transaction Problems In SQL Server 2008 R2

Hello Forum.

Recently I've been involved in an upgrade project migrating databases from 2000 into 2005; the database then replicates to another company into a 2008 Database. The processes involved extract data from an Oracle database into the 2005 Database and then transferring the data to the 2008 Database. a Stored procedeure is then fired on both Servers to integrate the extracted/migrated data into the existing data.

The problem is that now we are getting duplicate data in the 2008 database; essentially the Stored Procedure is supposed to delete existing data if it has been migrated and then replace it with the existing data but it appears as though this isn't happening. Is anybody aware of any issues surrounding 2008 R2 that could explain this?

Please click "Mark As Answer" if my post helped. Tony C.

Run Distrubited Transaction from Linked Server

Hi All,

I guess you all know that famous error when trying to run DT using linked server ,

Server: Msg 7391, Level 16, State 1, Line 3
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].

I followed all instruction listed mentioned at MS support and nothing change still have the same error

http://support.microsoft.com/kb/839279

http://support.microsoft.com/kb/306212

my enviroment is run distrubited transaction using linked server from sql server 2000 Ent Sp4 on win 2003 SP2 to SQL Server 2008 Sp1 x64 For IA on win server 2008(cluster enviroment active\active) IA .

appricaite you suuport, thanks in advance

SQL Server 2008 Dump Transaction

I used SQL Server 2005 before and used "DUMP TRANSACTION ... WITH NO_LOG" statement, i tried to do sameÃÂ thing in SQL Server 2008 and it doesn't work. How can i use it in SQL Server 2008?

Transaction across local and Linked Server

Hello,

I have some requirement where my procedure will do several operations (Add, Update etc) in my Local SQL Server + Add some records on a remote server (Linked Server).

I have one transaction around my local server tasks. But writing to Linked Server is out of this transaction block. And there is chance that writing to Linked server may fail.

So what are the best way I can make both task (Local task + writing to LinkedServer) with in a single transaction? Is that suggested?

Thanks.

Regarding "Cannot access disposed Object name:Transaction" DTC error in SQL server 2008

Hi all,

I have upgraded my Sql server 2005 to Sql server 2008 in window 2003 server (SP 2 standard edition) . After upgrading iam getting above mentioned MSDTC issue when try to make an distributed transaction with Sql server 2008 in my .Net application .

Note: No issues with SQL server 2005...

Please give me an solution

Reducing the SQL Server transaction log on the fly for production DB ?

Hi All,

I'd like to know which is the best solution possible to reduce the SQL Server transaction log in the live production server without downtime ?

1. Database full backup - which should commit transaction log ? (like in Exchange Server ?)

2. executing the following T-SQL Script from SSMS manually during the production working hours ?

------------------------------------------------------------------------------
-- Otto R. Radke - http://ottoradke.com
-- Info: T-SQL script to shrink a database's transaction log. Just set the
-- database name below and run the script and it will shrink the
-- transaction log.
------------------------------------------------------------------------------
------------------------------------------------------------------------------
-- Update the line below with the name of the database who's transaction
-- log you want to shrink.
----------------------------

Latest Best Practices in Transaction Auditing in ASP.NET and SQL Server?

Hello!

What is the most recent best practice on how transaction auditing should be done for transactional systems using ASP.NET 3.5 and SQL Server 2008?

My requirement is to track changes done by a user and allow them to view the audit trail history in the front-end.

Here’s the different ways I found so far:

Create triggers and separate table(s). There are 2 options on this approach:
1. Create an audit table for each table. This will be exact copy of the table, but will have additional field such as transactionID and datetime fields, etc.
2. Create one audit table to keep track of all tables. This will be a more generic table to keep track of changes to the table's data.
I read about the new auditing feature on SQL Server 2008 (http://msdn.microsoft.com/en-us/library/dd392015%28v=sql.100%29.aspx). Can this be leverage for in ASP.NET? Can I access the file directly and allow users to view it? Or is this really more of a DBA Admin usage?

Thanks for your help in advance!


Categories:

ASP.Net	Windows Application	.NET Framework	C#	VB.Net	ADO.Net
Sql Server	SharePoint	Silverlight	Others	All