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


Top 5 Contributors of the Month
MarieAdela
Imran Ghani
Post New Web Links

SSAS2008 - server wide locking?

Posted By:      Posted Date: October 25, 2010    Points: 0   Category :Sql Server
 

Hi -

 

I have about 15 databases running on a single instance of 2008 R2. It's a beefy box - 16 way with 64GB of RAM.

We process several databases at a time. Very often we'll see where a processing in one database will delay the processing in another database. The CPU is under 15%, RAM isn't nearly utilized, and there's no slowness on the disk.

My question is - why would one processing in a database delay the processing in another. No objects are shared between the databases.

 

It appears there is a server wide lock going on. Is this true? Why?




View Complete Post


More Related Resource Links

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= 

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?


SQL Server 2008 Management Studio locking up while not running anything

  

Scenerio:

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 2008 Locking Problem

  

Hi guys,

 

I wanna ask something,

In SSMS, when we do SELECT or UPDATE, which one is locked? Table or Record?

 

and,

In SSMS, when we change indexes in one table, why does it lock the entire database? 

 

Best regards and thanks,

Roland Andreas


SQL Server locking, compared to DB2 question;

  

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?

Thanks


call JavaScript - jQuery code from ASP.NET Server-Side

  
jQuery got so close to me lately that I can see myself adding the scripts to my project almost unconsciously. The thing is, jQuery is very useful for me, in almost all situations and it has been a do or die enhancement for all my project since I first put my hands on it.

Of course while using it, you encounter few situations which need a bit of research to solve, mostly when you are trying to combine it with some other technologies like: UpdatePanels and ASP.NET Ajax. For instance there are many situations when I would like to run some jQuery magic based on some decision that I make on the server side.

Making Ajax Callbacks to the Server Using jQuery with ASP.NET

  
In the first part of this article series I introduced jQuery's functionality and how it provides a rich client side programming model. This time around I'll expand on these concepts and show you how you can use jQuery in combination with ASP.NET using it as an AJAX backend to retrieve data. I'll also discuss how you can create ASP.NET controls and otherwise interact with jQuery content from ASP.NET pages in WebForms.


jQuery is just a JavaScript library so it will work seamlessly with ASP.NET both from page code as well as through backend driven code using the Page.ClientScript object or ScriptManager. You can use jQuery on its own as a client side and Ajax library that communicates with ASP.NET or you can use jQuery in combination with ASP.NET AJAX. The two actually complement each other quite well as jQuery provides functionality that the ASP.NET AJAX library does not and vice versa. For the most part the interaction between the two libraries is trouble free except for a few very rare edge cases.

MS SQL Server: Disconnect Users From Database - Kill User Session

  
If you ever wanted to restore your database from a SQL backup file (.bak), but there are still users connected to your database, the backup operation will fail causing the error: Exclusive access could not be obtained because the database is in use.

MS SQL Server: Search All Tables, Columns & Rows For Data or Keyword Query

  
If you need to search your entire database for specific data, this query will come in handy.

So when a client needs a custom report or some sort of custom development using Great Plains, most of the time I will have to track down the data in the system by running this query and find the table(s) it is in.

call JavaScript - jQuery code from ASP.NET Server-Side

  
jQuery got so close to me lately that I can see myself adding the scripts to my project almost unconsciously. The thing is, jQuery is very useful for me, in almost all situations and it has been a do or die enhancement for all my project since I first put my hands on it.

Of course while using it, you encounter few situations which need a bit of research to solve, mostly when you are trying to combine it with some other technologies like: UpdatePanels and ASP.NET Ajax. For instance there are many situations when I would like to run some jQuery magic based on some decision that I make on the server side

Publishing SQL Server Database using Publishing Wizard : Tips & Tricks

  
We can use SQL Server Publishing Wizard to deploy our local Database to remote hosting server/production server.This feature is available in SQL Server 2008/VWD 2008.

Below is the complete step by step guide of this process.

CLR Integration With Microsoft SQL Server

  
Transact-SQL provides as much flexibility as possible but, because it is not a real programming language, it has some shortcomings about many issues (memory management, exception handling, debugging, file processing (or streaming), object-orientation (classes), or thread management). Transact-SQL also lacks features of normal libraries (arrays, collections, serialization, expressions, or string manipulations, etc).

IP Address Blocking or Restriction in SQL Server

  
"is it possible to block connections to SQL Server based on IP address". As far as now there is no official way in SQL Server to block the connections in SQL Server based on IP address. However this can be done from the OS end, we have the following three options available, refer HERE for more.


.Firewall
.IPSec
.RRAS IP Filter

Determine whether Forms Server is installed on a server

  
Check whether the Microsoft.Office.InfoPath.Server.dll and FormServer.aspx files are installed on the server.

Connestion string create any database server

  
This links important to how to create connection string to any data base sever.

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:

SQL Server Backup and Restore (video)

  
Restore your full backup, then run a few queries before you keep restoring transaction logs. Brent shows how to use the Standby option for restores.

In this five minute video, Brent demonstrates how to do it by creating a database, populating data, and then showing what disaster recovery is like by doing RESTORE WITH STANDBY.
Categories: 
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