.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Post New Web Links

SQL Server locking, compared to DB2 question;

Posted By:      Posted Date: December 04, 2010    Points: 0   Category :Sql Server

Hi We have a sql 2008 Enterprise Edition x64 on a win 2008 cluster. The application is a 3rd party app that normally uses Oracle or DB2. We have been having deadlock issues in our environment. The vendor mentioned they don't have deadlocks in Oracle or DB2 and said they use DB2_EVALUNCOMMITTED (From DB2 manual, "which improve concurrency of scans, it may be possible to defer row locking until after determining that a row qualifies for a query) to improve concurrency, is there a similar feature in SQL Server?


View Complete Post

More Related Resource Links

Educational question: How server objects are accessed from multiple client threads?

I will appreciate it if someone can help me to undestand how the following scenario works:   There is remoting server that is registered as WellKnownObjectMode.Singleton. Server implementation: It instantiates a class TestServer that implements interface IFoo interface IFoo { IHelper GetHelper() }   interface IHelper { void StoreString(); }   Implementation of TestServer: internal sealed class TestServer: System.MarshalByRefObject, IFoo { private IHelper> helper = new Helper(); public IHelper GetHelper(string helper){return helper;} }   Implementation of Helper: internal sealed class Helper : System.MarshalByRefObject, IHelper { public void StoreString (string val) {}; }   Implementation of a client (simplified; shown to illustrate the question only): static class Program { public static IFoo TestService; public static IHelper Helper; public TestThreads testmultiplecalls; [STAThread] static void Main() { TestService = (IFoo)Activator.GetObject(typeof(IFoo),"ipc://Channel"); Helper = TestService.GetHelper(); testmultiplecalls.Start(); } }   internal class TestThreads { public Start() { for (int i = 0; i < 20; i++) ThreadPool.QueueUserWorkItem((new TestCall(i.ToString())).DoWork); } }     internal class TestCall { private string m_str; public TestCall (string str) {m_str = str;} public void DoWork() { Program.Helper.StoreString(m_str); } }     Que

clr assembly in sql server - working directory? and exception question

Hello, I have a small assembly in sql server 2008... it's pretty simple, but in the very rare circumstance that it may throw an exception I am catching it and writting to a log file. something like: using (StreamWriter sr = new StreamWriter(@".\ErrorLog.txt", true)) { sr.WriteLine("{0:G} ===> {1}", DateTime.Now, ex.Message); } I believe that should write the file to the working directory... 1. what would the working directory be for sql server 2008? the binn directory? 2. what would happen if a clr assembly added to sql server throws an exception that is not handled by your clr code?

SQL server 2008 r2 Data sync question???

Sql 2008 r2 data sync, does it involve a db hosted by microsoft or does that involve us hosting the sync functionality locally?  I know we have db's local that need to be sync'd across a wan but does that happen in a db hosted in the ms cloud or does that have to do with a sync functionality that runs locally to keep my db's sync'd?   Thanks, SL

MDX query using linked server - performance question

Hi, I'm having a strange performance behavior. When I’m running a mdx query on management studio, it runs for 10 minutes (its mainly calc members on a large cube) When I run the same query via linked server and openquery, it runs for 20 minutes. Is the SQL from some reason does a parsing or running the query twice? The server is sql2008 UP1 on 64bit. and the "allow in process" is checked. its the default in sql2008 Thanks in advance, Yoav  

Server Time out settings question

I’ve followed the instructions for the login time out but we are still getting time outs prompting login within less than the stated time (currently set to 30 minutes). I’ve evenn tried setting it to never time out, but still we have to re-login after about 20 minutes…. Do you know if there is a further setting somewhere that would over ride this one? Configuring login expire time for sharepoint site   You need to set the "security validation time-out":   Central Administration > Application Management > Web Application General Settings (be sure to do this for the correct web application)   Ensure that Security Validation is On and set the timeout to 20 min. (default 30 min).   ThanksSharepoint Customiser

SQL Server 2008 R2 Installation and Service Pack 1 Question

I am migrating a SQL 2000 server to a SQL 2008 R2 server (on different physical servers) . When I ordered SQL Server 2008 the volume licensing allowed me to download sql server web 2008, sql server web 2008 R2 and sql server 2008 service pack 1. I don't know why I have three options to download. Don't I just need to just install the sql server 2008 Web R2? Are there any sql 2008 R2 service packs available?   

Cross server Selects by application are locking the Server

.Net Application runs cross server queries and show up on the SSMS Monitor as Locks.  The seem to be select statements, and when the application is restarted, the locks are released. What can be locking the system and how do we go about troubleshooting it? The application runs queries across sql2k8 and sql2k5. Compatiblity level on 2008 is set to 2008 (80).  Here are the details from the Activity Monitor. SessionID:51 login:Application  Wait time(ms): 183414  Wiat type :LCK_M_S Blocked By : 102  HostName: VHP-SQL01  Task State : Suspended Command: Select  application: MSSQL  Wait Time    Wait Resource  KEylock hotid =  dbid = 16 lockb2f64580 mode X associatedObjectID= 

report server config question


On the left pane of the configuration window, there is an item called "Windows Service Identity".

Here I entererd a windows account and its password. Next, I hit apply and get a red x on "Assigning Reporting Services Rights to User". It goes on to say, "The Reporting Services rights were not applied properly. The user may still not have appropriate access to Reporting Services resources."

The windows account happens to be a sysadmin on the SQL SERVER and also an administrator on the machine.

SQL Server 2005 Locking


We have a SQL Server 2005 stored procedure that updates the "batch_status" field of a specific row. This procedure is the one that is most frequently called in our entire system. It is typically called from within some third party software that moves information from queue to queue to queue. The last thing the software does is to update the status.

One of our custom applications shells out to this third party software, gets the process handle, and waits for the handle to close. Then, it finds out what the batch_status is to deterrmine what to do next.

As volume increases in our system it becomes more and more likely for us to get a dirty read and the affected data gets "stuck" rather than moved on to where it should go. I am extremely puzzled as to how the stored procedure is supposedly done, yet the change is not visible.

I don't know how to prevent these dirty reads. Should I use some locking hint in the update statement? If so, should I use it within a transaction block? I am very concerned about the effect either of these would be on overall performance. If I could get just a rowlock that would be great. I'm concerned that page locks and tablelocks will create a "gridlock".

Any ideas?

Sharepoint Server 2007 Document Library permission question


Is there a way to set permissions on document library where HR can see all documents but employees can only see the documents that they uploaded?

I am trying to configure permission level but can't find anything that will solve the purpose.

HR is hoping to do online enrollment and remove paper format.

SQL Server audit question

I've set up a SQL server audit to capture select statements against a table in my database.   That's working great with no problems my question is when I view the selected row details of my audit entry in the audit log I see the statement has been parameterized.  Here's an example of what I mean, in a query window I execute: 

select * from table where name = 'Smith'.  

The audit entry displays the statement as 

select * from table where name = @0
  My question is whether or not there is a way we can capture the where conditions in the audit event.

SQL Server 2008 Management Studio locking up while not running anything



Restart Server that DB is running on

Restart workstation that SQL Server 2008 Management Studio is running on

Start SQL Server 2008 and open file I'm editing

Edit the SQL file while while monitoring memory and processor usage on the workstation

Processor starts to spike, intellisense stops working, memory usage begins to climb to an absurd amount ~7G, management studio becomes almost nonresponsive (i.e. I can type and the text takes 30 seconds to 5 minutes to show up)

After about 30 mintues, memory usage drops to around 1.5G and management studio becomes semi-usable again.  Intellisense sometimes goes back to working, sometimes everything has to be restarted again.

I've been working on this exact setup for 7 months now and this problem is new.  No, I am not executing any statements - I am editing text only.

SQL Server 2000 to 2008 R2 db restore question


Hi guys

I've just restored a db (MS SQL 2000) unto MS SQL 2008. All went well and the users for that db were also created. But they were created on the db level.

On the server level, these logins were not there. I tried to create them manually at the server level. When I try to assign the login to the db it understandably says that the user with the same name already exists in the db.

I tried dropping the login created in the db level but I can't do so as there is a schema in the database with the username.

How should I go about getting the same login unto 2008 without dropping any schemas as I am trying to keep everything the same as it was in 2000 for testing purposes in 2008.


Thanks in advance.

SQL Server 2008 Licensing Question?


I know the same question is being asked many time from you, but I am curious to know the answer to my question.


SQL Server licensing says you don't have to purchase any new license for (Mirroring, Clustering, Log Shipping) as long as it is not up and running beyond 30 days and less or equal processors then the active sql server.

I want to check how the licensing works when the DR site is setup with SQL Transactional Replication, where the target server is just a stand-by but active.

Please Advise. Thanks in Advance


SQL Server 2008 instance question



Is it possible to install a SQL Server 2008 without giving it an instance name?  So that my dotnet applications' connection string won't need to change to "data source:dbServer\Instance;...", and still can use the "data source:dbServer;..."
Thanks for help.


Question about SQL Server Database sizes


I have SQL Server 2008, when I go into Server Management Studio and I right click on my database and choose properities.  I see two things, I see Size of 200.13 MB and Space Available of .44MB.  Does this mean my database can only grow by .44MB more of data?  If so, is there a way to change the max size?  When I was looking at documents on SQL Server Express 2008, I saw a 10GB limit on each Database instance, so I was assuming SQL Server 2008 would provide me more than 200.5 MB? 

I'd really appreciate any info/clearing up for me.



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

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend