Blocking And READ_COMMITTED_SNAPSHOT Isolation level

I was troubleshooting sustained blocking in sql server 2005 that lead to deadlock from java application: As I tried to capture the inputbuffer of the processes involved in blocking found out that there is one process which is constantly changing its status from sleeping to runnable and vice versa. This process is the culprit (chain blocker). As I mentioned often times the blocker process was in sleep mode and yet blocking and keeping other process hostage preventing them from completing. As there are multiple application servers and web servers involved in sending batch processes to sql server it is reasonable to think that the batches could be running in transactions and dependent on some middle tier process as well. I thought of writing up simple test I did in a separate environment to prove about blocking:

Steps Performed:

1. Create test database in sql 2005 with default setting

— Create database test

2. Create test table with two columns as below:

USE

 [test]

 GO

/****** Object: Table [dbo].[test1] Script Date: 12/12/2012 4:42:41 PM ******/

GO

CREATE TABLE[dbo].[test1]

 ([col1][nchar](10NULL,[col2][nchar](10NULL)

3. Insert three sample rows

INSERT INTO[dbo].[test1] ([col1],[col2]VALUES (‘test1’,‘test2’)

INSERT INTO[dbo].[test1] ([col1],[col2]) VALUES (‘test2′,’test3’)

INSERT INTO[dbo].[test1] ([col1],[col2]) VALUES (‘test3′,’test4’)

4. Open query window and issue the below update statement with holdlock hint and with begin transaction without commit

USE

 [test]

 GO

begin tran

 UPDATE [dbo].[test1]with (holdlockSET[col1]=‘test_new’ WHERE col1=‘test2’

 GO

5. Then open another query window and issue the below simple select statement

select * from dbo.test1

 The above select query will never finish because it is blocked by the update statement from step 4

If you run the below query you see the blocked and blocking process similar to the output shown below

select spid,blocked,waitresource,open_tran,status from sys.sysprocesses where blocked<>0 or open_tran<>0

spid blocked Waitresource Open_tran Status
56 0   1 sleeping
57 56 TAB: 5:2073058421:0 0 suspended

However if the database isolation level was set to READ_COMMITTED_SNAPSHOT step 5 will return values with old copy of the test table as the update in step 4 is not yet committed.

To run the above steps with the database isolation level set to READ_COMMITTED_SNAPSHOT close all connections to test database, run the below statement and repeat step 1 to step 5

ALTER DATABASE[test]SET READ_COMMITTED_SNAPSHOTON WITH NO_WAIT

Troubleshoot_Failed_Setup_SQl2012

Issue: After installing SQL 2012 Database engine, Reporting service may fail with below error:

  Feature:                       Database Engine Services

  Status:                        Failed: see logs for details

  Reason for failure:            An error occurred during the setup process of the feature.

  Next Step:                     Use the following information to resolve the error, uninstall this feature, and then run the setup process again.

  Component name:                SQL Server Database Engine Services Instance Features

  Component error code:          -2147467259

  Error description:             Access is denied

OS is Windows 7 Enterprise

Troubleshooting Steps:

Actually the setup finishes with installing the binaries and system databases. It failed with adding and provisioning the account windows account and Sa account with Sysadmin permission

Here below is how to troubleshoot it:

  1. Make sure the account used to run setup is local admin
  2. Change the SQL server and agent service account to be the account you run the setup. ( in my case change to domain account that I logged in to perform setup
  3. Open SQL server configuration manager using “Run Elevated”:
  4. Change SQL server service startup account ( account used to run the setup)
  5. Then add –m to start SQL server in single user mode
  6. Restart SQL server service
  7. Connect to SQL server using SQLCMD from command line (example : sqlcmd -S SQLINSTANCENAME –E)
  8. Once connected add the account you logged in to sysadmin server role by running the below t-sql 

Use master

GO

sp_addsrvrolemember ‘doamin\useraccount’, ‘sysadmin’

GO

     9.  After disconnecting from command line go back to SQL configuration manager  and remove –m that you added at step 5 and restart SQL server so that

     SQL server can run in multi user mode

    10. Then you can connect using windows/SQL authentication to the SQL server instance

 

 


Categories

July 2017
M T W T F S S
« Dec    
 12
3456789
10111213141516
17181920212223
24252627282930
31