Link Access To SQL Server

I have a problem that I want to solve by linking MS Access to SQL Server 2000 or 2005.

The problem is a 3 party MS Access '97 application that is no longer supported so a new system is currently being built in SQL Server. The Access database has a UI for small amounts of input and viewing but most of the work is done through a WaveLink application. The Wavelink application allows users on the shop floor via wireless devices to pick and count product which then updates Access. There has always been record locking issues that caused some delays at the wireless devices but nothing dramatic. Until now. The wireless network and wireless devices have been upgraded and now record locking (I presume that whats causing the problems) is now causing large productivity delays requiring constant intervention.
The Wavelink application is also being updated from VB6 to VB.Net to work with the new SQL Server application. One of the requests from the client is that the Wavelink upgrade somehow fix the problem with the Access database.

My idea was to link the Access database to a SQL Server so that the wireless devices go through SQL Server to Access hopefully taking the load off Access. Is this possible? Would SQL Server prevent the record locks and provide a better multi user environment or is that something that is inherent in OLEDB Jet and linking is not going to circumvent this problem?

Regards

Linking to Access from SQL will not remove transaction locks from the scanners. There is no magic bean there.

My suggestion would be more along the lines of creating a purgatory (temp) table that replicates with the live data either through a scheduled DTS package or some clever stored procedure.

Whichever option you choose, you must ensure the hardware devices will not intervene with the replication.

If it were me, but it's not, I would create the mirrored purgatory table that would house the live scans, use a stored procedure to find duplicate records from a JOIN of the stored data table...once the scans are complete, and increment/decrement the values as needed.

As a side note, having two tables that replicate will remove the locks from the stored data table so that updates/inserts are controlled by the purgatory table. I'd dabble with an INSERT trigger on the purgatory table to check for dupes and work on the real table

Just my twist on it,

Adamus

|||

Using SQL Server as a backend data server 'could' help.

One of the problems is the way that Access manages locks. When a row of data needs to be updated, Access locks an entire 8k page, which may contain quite a few rows other than the one to be updated. That then blocks other users from the data in adjacent rows. As you've probably experienced, that often leads to corruption in the lock file, and you must get everyone out of the Access application and delete the lock file. A unpleasant and not too quick process...

SQL Server will lock only the single row being updated, allows other users to manage the data in adjacent rows. Also, when using SQL Server, when the 'process' is issuing a single UPDATE command, there is no reason to wrap the UPDATE command in a TRANSACTION. With SQL Server, even a single command is a implicit TRANSACTION.

|||

Arnie Rowland wrote:

Using SQL Server as a backend data server 'could' help.

The scanners will still lock the table.

Arnie Rowland wrote:

SQL Server will lock only the single row being updated, allows other users to manage the data in adjacent rows.

Do you mean it locks the table?

Arnie Rowland wrote:

Also, when using SQL Server, when the 'process' is issuing a single UPDATE command, there is no reason to wrap the UPDATE command in a TRANSACTION.

There is 'always' a reason to use transactions especially with an UPDATE/DELETE unless you enjoy rolling back entire tables. It is good practice and a standard in most analyst position to use transactions to avoid the "oops I've updated every record"

Arnie Rowland wrote:

With SQL Server, even a single command is a implicit TRANSACTION.

This is where the problem occurs with multiple hardware devices attempting to write to the same table and the root of this thread. The implicit transaction is locking the table.

Just my twist on it,

Adamus

|||

Adamus Turner wrote:

Arnie Rowland wrote:

Using SQL Server as a backend data server 'could' help.

The scanners will still lock the table.

Probably Not.

Arnie Rowland wrote:

SQL Server will lock only the single row being updated, allows other users to manage the data in adjacent rows.

Do you mean it locks the table?

No, I think that I wrote 'lock only the single row'.

Arnie Rowland wrote:

Also, when using SQL Server, when the 'process' is issuing a single UPDATE command, there is no reason to wrap the UPDATE command in a TRANSACTION.

There is 'always' a reason to use transactions especially with an UPDATE/DELETE unless you enjoy rolling back entire tables. It is good practice and a standard in most analyst position to use transactions to avoid the "oops I've updated every record"

Access had a problem is that it could fail in the middle of an operation and leave the table in an 'unknown' state. That is not the case with SQL Server. And actually, it is not the 'standard' to use a TRANSACTION with SQL Server for single action queries.

Arnie Rowland wrote:

With SQL Server, even a single command is a implicit TRANSACTION.

This is where the problem occurs with multiple hardware devices attempting to write to the same table and the root of this thread. The implicit transaction is locking the table.

With the current Access implementation, a PAGE LOCK is locking many rows and blocking other users. With SQL Server, even with an Implict Transaction, the minimal amount of data is being locked.

Just my twist on it,

Adamus

|||

Arnie Rowland wrote:

Adamus Turner wrote:

Arnie Rowland wrote:

Using SQL Server as a backend data server 'could' help.

The scanners will still lock the table.

Probably Not.

The implicit transactions will collide and cause errors. It is not a probabitlity, it's a certainty.

Arnie Rowland wrote:

SQL Server will lock only the single row being updated, allows other users to manage the data in adjacent rows.

Do you mean it locks the table?

No, I think that I wrote 'lock only the single row'.

The query optimizer will decide whether a row lock or table lock is necessary. In most cases, on larger queries, the table will be locked.

Arnie Rowland wrote:

Also, when using SQL Server, when the 'process' is issuing a single UPDATE command, there is no reason to wrap the UPDATE command in a TRANSACTION.

There is 'always' a reason to use transactions especially with an UPDATE/DELETE unless you enjoy rolling back entire tables. It is good practice and a standard in most analyst position to use transactions to avoid the "oops I've updated every record"

Access had a problem is that it could fail in the middle of an operation and leave the table in an 'unknown' state. That is not the case with SQL Server. And actually, it is not the 'standard' to use a TRANSACTION with SQL Server for single action queries.

MS is aware of this and has provided Compact and Repair functionality to remedy this issue. The problem, on a broad scale, is that companies tend to stretch Access to mimick SQL Server and try to use it as a large application. This is not the intent of the design. Putting an Access database on a network drive and allowing multiple users to access it concurrently is asking for trouble. The resolution is in application development and SQL Server rollover.

Also, if you're defining a single action query as 1 row affected, I'll agree with you. No transaction required. However, 2+ records affected, transactions are an ethical imperative.

Arnie Rowland wrote:

With SQL Server, even a single command is a implicit TRANSACTION.

This is where the problem occurs with multiple hardware devices attempting to write to the same table and the root of this thread. The implicit transaction is locking the table.

With the current Access implementation, a PAGE LOCK is locking many rows and blocking other users. With SQL Server, even with an Implict Transaction, the minimal amount of data is being locked.

Again, the query optimizer determines this. And yes, on a very very very small scale this is accurate. The point is, the hardware devices used to commit these transactions 'collide' without fail. Changing the backend source will have no affect on the resolution.

Just my twist on it,

Adamus

|||

Adam,

Based upon our history, I'm not going to continue to sustain this 'argument'. This is my last entry.

-Arnie

Adamus Turner wrote:

Arnie Rowland wrote:

Adamus Turner wrote:

Arnie Rowland wrote:

Using SQL Server as a backend data server 'could' help.

The scanners will still lock the table.

Probably Not.

The implicit transactions will collide and cause errors. It is not a probabitlity, it's a certainty.

You still miss some of the finer detials of locking and locking behaviour. The OP indicated that updates are made to Product records, using barcode scanners, and most likely updating a a single row for one product at a time, possibly touching more than one table. With the scenario as presented, I just don't see a lot of transaction 'collisions'.

Arnie Rowland wrote:

SQL Server will lock only the single row being updated, allows other users to manage the data in adjacent rows.

Do you mean it locks the table?

No, I think that I wrote 'lock only the single row'.

The query optimizer will decide whether a row lock or table lock is necessary. In most cases, on larger queries, the table will be locked.

And if it is single row updates, the QP will lock a single row. The entire table will only be locked if the QP determines that the cost of maintaining multiple individual rows locks has a higher cost than a table lock. But it would also issue a page lock, an extent lock, or an Index lock, -before escalating to a table lock.

Arnie Rowland wrote:

Also, when using SQL Server, when the 'process' is issuing a single UPDATE command, there is no reason to wrap the UPDATE command in a TRANSACTION.

There is 'always' a reason to use transactions especially with an UPDATE/DELETE unless you enjoy rolling back entire tables. It is good practice and a standard in most analyst position to use transactions to avoid the "oops I've updated every record"

Access had a problem is that it could fail in the middle of an operation and leave the table in an 'unknown' state. That is not the case with SQL Server. And actually, it is not the 'standard' to use a TRANSACTION with SQL Server for single action queries.

MS is aware of this and has provided Compact and Repair functionality to remedy this issue. The problem, on a broad scale, is that companies tend to stretch Access to mimick SQL Server and try to use it as a large application. This is not the intent of the design. Putting an Access database on a network drive and allowing multiple users to access it concurrently is asking for trouble. The resolution is in application development and SQL Server rollover.

Also, if you're defining a single action query as 1 row affected, I'll agree with you. No transaction required. However, 2+ records affected, transactions are an ethical imperative.

If you have ever had to deal with a situation where an Access query failed 'mid-stream', you will quickly recall that the 'Compact and Repair' tool is of no use to return the data to its previous state, and that you now have an inconsistant data state in the table. Single action Transactions are often a necessary evil with Access. I've had to 'rescue' and migrate Access databases that have exceeded every size/space limit placed on Access. I've had success Access Applications having as many as 50+ users on a Access mdb database. But that took deep understanding of Access and careful planning and execution.

A SQL TRANSACTION (including an implicit transaction) is a 'all or nothing' operation -one row affected, or thousands. It is dependable -unlike Access. IWith SQL Server, i is NOT necessary to issue an EXPLICIT TRANSACTION for a single action.

Arnie Rowland wrote:

With SQL Server, even a single command is a implicit TRANSACTION.

This is where the problem occurs with multiple hardware devices attempting to write to the same table and the root of this thread. The implicit transaction is locking the table.

With the current Access implementation, a PAGE LOCK is locking many rows and blocking other users. With SQL Server, even with an Implict Transaction, the minimal amount of data is being locked.

Again, the query optimizer determines this. And yes, on a very very very small scale this is accurate. The point is, the hardware devices used to commit these transactions 'collide' without fail. Changing the backend source will have no affect on the resolution.

Adam, clearly you have worked with Access a bit, and yet have a lot to learn about SQL Server (as also, do I!)

I don't think that 'the hardware devices' have anything directly to do with transaction collisions -that is a matter of software design, database design, and serenditity. We can work to control the first two to the best of our skills, the last one is beyond our control ...

I fail to understand your need to pick apart and contest my original comments to the OP. Did I step on your toes somewhere?

Just my twist on it,

Adamus

|||

Arnie,

It is not my intention to rival against the masses.

At one of many previous contracts, I had to redesign an Access environment to an ASP.net/SQL Server 2000 environment for a logistics company basically nuking and paving the existing inventory system and designing it from scratch.

Part of their inventory management system used bar code scanners. Most, more pricey, scanners use bulk imports to communicate with the database (not one item at a time). They scan a lot of inventory, hit a button, and the software does the rest.

This works great with 1 person. This works great with 2 people in tandem. This does not work at all concurrently. You do not need any in depth database cognitions to anticipate the potential heartaches. Have 3+ people try to fill a water glass at the same time. Will you argue no collision will occur?

I apologize if you feel attacked, however, your suggestion isn't relavent or effective.

The problem is that more than 1 scanner is attempting to bulk insert concurrently. Changing the back end source will not change anything.

Adamus

|||

One correction:

Moving from an application db (Access) to a server db (SQL Server) does enhance the query time, in effect, reducing the potential of collisions but this is simply coincidental and not a resolution.

Best of luck,

Adamus

|||Thanks Adamus and Arnie, very interesting discussion.

The question was, or should have been, would Sql handle the updates to the access db in the event the table/rows were locked i.e. would Sql keep trying until 'Timeout' was reached or the lock was released on the access table. From your discussions, it seems this would not be the case.

What I've decide to do is suck the data from the access db into similar tables in Sql. The scanners would then connect only to the Sql tables. As each scanner will only access a single row, and the row is exclusive to a scanner, the table locking problem would not be a problem here.
The only record locking that may occur is when the data is pushed back to access but I think this will be more of a timing issue with some processes that seem to run in the access app that I will have to work around.

Cheers gentlemen, thanks for your input.

Limiting Transaction Time?

Please be gentle, I'm an application developer, not a dba. I just
dabble as a DBA for our shop.
Our application is distributed and works through web services to
access information stored in SQL Server 2005. Each request to the web
service is a contained transaction, in other words, transactions do
NOT cross calls web service calls.
Today something unknown and yet unidentified went wrong in the code
and a transaction was open indefinitely (over an hour until I noticed
it by accident and killed the offending process).
Question: Is there a setting that I'm not seeing for the server or
database that will limit how long a transaction can hold locks for?
For example, in our scenario no transaction should be longer than a
few seconds, so if I could set something in sql server that would
rollback and kill any transaction that lasted for more than 1 minute,
I'd be able to limit the impact of this problem.
Thanks in advance for any help you can give . . .
For this case, I don't think its locks you should be concerned about?
Before killing a connection, I would look in the master..sysprocesses
table or run sp_who2 and find out what application. On spid run dbcc
inputbuffer(spid) and see what what's being or what was executed. Is
the connection in a sleeping state? Chances you are not getting a
commit or rollback from the application or service stopped and some
how could not initiate a rollback. You could write a query against the
master..sysprocesses table that checks for connection in an open_tran
> 0 and in a sleeping state and last_batch is greater than a specified
time. Then run a kill statement on it. You can acheive this via cursor
from the query result. If you are using some connection mechanism that
leaves your connetion in open_tran state of 1 (I've seen some java
drivers do this) then try open_tran >1. However, I don't advocate
doing this. Best thing to do let it happen again and troubleshoot the
application. The connection will eventually be closed see See
Orphaned Sessions in BOL.
|||I agree with Ken that you should perform root cause analysis. Consider
adding SET XACT_ABORT ON to stored procedures that begin explicit
transactions. This setting will rollback the transaction immediately and
cancel the batch in the event of most errors, including command timeouts.
If you start transactions from application code, execute SET XACT_ABORT ON
on the connection after opening to help ensure pooled connections
immediately rollback connections in the event of an error. Also check to
ensure a commit or rollback is issued on all code paths.
Problems with performance (e.g. scans) can often lead to performance and
concurrency (blocking) problems, which in turn cause other queries and
transactions to run longer and then lead to even more problems. The best
way to break the cycle is by optimizing queries and adding indexes for
efficient data access. Start by analyzing the execution plan in SSMS for
those queries that are the longest running and most frequently executed.
See http://support.microsoft.com/kb/224453 for some tips. Although the
article was written for SQL 7 and 2000, much applies to SQL 2005 too.
If you need to gather SQL trace data from a busy server, run a server-side
trace with a filter (e.g. RPC and T-SQL batch completed events with duration
greater than 1000000 microseconds) instead of running the Profiler tool
directly against a busy production server. You can develop the desired
trace using Profiler on a dev box and then script that trace so that you can
run on the prod server as a server-side trace.
Hope this helps.
Dan Guzman
SQL Server MVP
"karlag92" <karlag92@.hotmail.com> wrote in message
news:1186013382.486135.138640@.r34g2000hsd.googlegr oups.com...
> Please be gentle, I'm an application developer, not a dba. I just
> dabble as a DBA for our shop.
> Our application is distributed and works through web services to
> access information stored in SQL Server 2005. Each request to the web
> service is a contained transaction, in other words, transactions do
> NOT cross calls web service calls.
> Today something unknown and yet unidentified went wrong in the code
> and a transaction was open indefinitely (over an hour until I noticed
> it by accident and killed the offending process).
> Question: Is there a setting that I'm not seeing for the server or
> database that will limit how long a transaction can hold locks for?
> For example, in our scenario no transaction should be longer than a
> few seconds, so if I could set something in sql server that would
> rollback and kill any transaction that lasted for more than 1 minute,
> I'd be able to limit the impact of this problem.
> Thanks in advance for any help you can give . . .
>
|||On Aug 2, 6:51 am, "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
wrote:
> I agree with Ken that you should perform root cause analysis. Consider
> adding SET XACT_ABORT ON to stored procedures that begin explicit
> transactions. This setting will rollback the transaction immediately and
> cancel the batch in the event of most errors, including command timeouts.
> If you start transactions from application code, execute SET XACT_ABORT ON
> on the connection after opening to help ensure pooled connections
> immediately rollback connections in the event of an error. Also check to
> ensure a commit or rollback is issued on all code paths.
> Problems with performance (e.g. scans) can often lead to performance and
> concurrency (blocking) problems, which in turn cause other queries and
> transactions to run longer and then lead to even more problems. The best
> way to break the cycle is by optimizing queries and adding indexes for
> efficient data access. Start by analyzing the execution plan in SSMS for
> those queries that are the longest running and most frequently executed.
> Seehttp://support.microsoft.com/kb/224453for some tips. Although the
> article was written for SQL 7 and 2000, much applies to SQL 2005 too.
> If you need to gather SQL trace data from a busy server, run a server-side
> trace with a filter (e.g. RPC and T-SQL batch completed events with duration
> greater than 1000000 microseconds) instead of running the Profiler tool
> directly against a busy production server. You can develop the desired
> trace using Profiler on a dev box and then script that trace so that you can
> run on the prod server as a server-side trace.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "karlag92" <karla...@.hotmail.com> wrote in message
> news:1186013382.486135.138640@.r34g2000hsd.googlegr oups.com...
>
>
>
>
> - Show quoted text -
Thanks for the input guys, but I still need a simple way to configure
sql server to protect itself. Maybe what Ken suggests would work, but
it seems like a lot of work to accomplish something so basic.
I do intend to do root cause analysis and find the problem in the code
that causes this. However, I do not have even a part time DBA to
monitor and watch for these things to happen. If I could kill the
transaction automatically form the DB side when it runs too long, this
would help stop it from becoming a major problem but not deprive me of
what I need to fix the problem.
Transactions are managed in the client code in the web service. I
have no doubt that something broke there and will chase that down.
I just need a simple way to have SQL Server not allow a problem to
fester overly long . . . It would reduce the impact and severity of
the problem dramatically.
|||I think your question expressed as a need is valid.
There is probably good reason(s) why Microsoft doesn't allow you to
enforce this policy or rule on sql server.
I invite the SQL MVPs to comment on why that is.
|||> There is probably good reason(so) why Microsoft doesn't allow you to
> enforce this policy or rule on sql server.
IMHO, this is something that should be implemented in the client API (like
CommandTimeout) rather than on the server side. I would be very concerned
about specifying a global transaction timeout on the server. As a server
policy, the timeout would need to be accompanied by more restrictive
criteria, such as host or application name name.
If this feature is important to you or karlag92, consider submitted a
product enhancement request via Connect Feedback
(http://connect.microsoft.com/SQLServer).
Hope this helps.
Dan Guzman
SQL Server MVP
"Ken" <kshapley@.sbcglobal.net> wrote in message
news:1186168209.808571.152240@.d55g2000hsg.googlegr oups.com...
>I think your question expressed as a need is valid.
> There is probably good reason(s) why Microsoft doesn't allow you to
> enforce this policy or rule on sql server.
> I invite the SQL MVPs to comment on why that is.
>

Limiting Transaction Time?

Please be gentle, I'm an application developer, not a dba. I just
dabble as a DBA for our shop.
Our application is distributed and works through web services to
access information stored in SQL Server 2005. Each request to the web
service is a contained transaction, in other words, transactions do
NOT cross calls web service calls.
Today something unknown and yet unidentified went wrong in the code
and a transaction was open indefinitely (over an hour until I noticed
it by accident and killed the offending process).
Question: Is there a setting that I'm not seeing for the server or
database that will limit how long a transaction can hold locks for?
For example, in our scenario no transaction should be longer than a
few seconds, so if I could set something in sql server that would
rollback and kill any transaction that lasted for more than 1 minute,
I'd be able to limit the impact of this problem.
Thanks in advance for any help you can give . . .For this case, I don't think its locks you should be concerned about?
Before killing a connection, I would look in the master..sysprocesses
table or run sp_who2 and find out what application. On spid run dbcc
inputbuffer(spid) and see what what's being or what was executed. Is
the connection in a sleeping state? Chances you are not getting a
commit or rollback from the application or service stopped and some
how could not initiate a rollback. You could write a query against the
master..sysprocesses table that checks for connection in an open_tran
> 0 and in a sleeping state and last_batch is greater than a specified
time. Then run a kill statement on it. You can acheive this via cursor
from the query result. If you are using some connection mechanism that
leaves your connetion in open_tran state of 1 (I've seen some java
drivers do this) then try open_tran >1. However, I don't advocate
doing this. Best thing to do let it happen again and troubleshoot the
application. The connection will eventually be closed see See
Orphaned Sessions in BOL.|||I agree with Ken that you should perform root cause analysis. Consider
adding SET XACT_ABORT ON to stored procedures that begin explicit
transactions. This setting will rollback the transaction immediately and
cancel the batch in the event of most errors, including command timeouts.
If you start transactions from application code, execute SET XACT_ABORT ON
on the connection after opening to help ensure pooled connections
immediately rollback connections in the event of an error. Also check to
ensure a commit or rollback is issued on all code paths.
Problems with performance (e.g. scans) can often lead to performance and
concurrency (blocking) problems, which in turn cause other queries and
transactions to run longer and then lead to even more problems. The best
way to break the cycle is by optimizing queries and adding indexes for
efficient data access. Start by analyzing the execution plan in SSMS for
those queries that are the longest running and most frequently executed.
See http://support.microsoft.com/kb/224453 for some tips. Although the
article was written for SQL 7 and 2000, much applies to SQL 2005 too.
If you need to gather SQL trace data from a busy server, run a server-side
trace with a filter (e.g. RPC and T-SQL batch completed events with duration
greater than 1000000 microseconds) instead of running the Profiler tool
directly against a busy production server. You can develop the desired
trace using Profiler on a dev box and then script that trace so that you can
run on the prod server as a server-side trace.
Hope this helps.
Dan Guzman
SQL Server MVP
"karlag92" <karlag92@.hotmail.com> wrote in message
news:1186013382.486135.138640@.r34g2000hsd.googlegroups.com...
> Please be gentle, I'm an application developer, not a dba. I just
> dabble as a DBA for our shop.
> Our application is distributed and works through web services to
> access information stored in SQL Server 2005. Each request to the web
> service is a contained transaction, in other words, transactions do
> NOT cross calls web service calls.
> Today something unknown and yet unidentified went wrong in the code
> and a transaction was open indefinitely (over an hour until I noticed
> it by accident and killed the offending process).
> Question: Is there a setting that I'm not seeing for the server or
> database that will limit how long a transaction can hold locks for?
> For example, in our scenario no transaction should be longer than a
> few seconds, so if I could set something in sql server that would
> rollback and kill any transaction that lasted for more than 1 minute,
> I'd be able to limit the impact of this problem.
> Thanks in advance for any help you can give . . .
>|||On Aug 2, 6:51 am, "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
wrote:
> I agree with Ken that you should perform root cause analysis. Consider
> adding SET XACT_ABORT ON to stored procedures that begin explicit
> transactions. This setting will rollback the transaction immediately and
> cancel the batch in the event of most errors, including command timeouts.
> If you start transactions from application code, execute SET XACT_ABORT ON
> on the connection after opening to help ensure pooled connections
> immediately rollback connections in the event of an error. Also check to
> ensure a commit or rollback is issued on all code paths.
> Problems with performance (e.g. scans) can often lead to performance and
> concurrency (blocking) problems, which in turn cause other queries and
> transactions to run longer and then lead to even more problems. The best
> way to break the cycle is by optimizing queries and adding indexes for
> efficient data access. Start by analyzing the execution plan in SSMS for
> those queries that are the longest running and most frequently executed.
> Seehttp://support.microsoft.com/kb/224453for some tips. Although the
> article was written for SQL 7 and 2000, much applies to SQL 2005 too.
> If you need to gather SQL trace data from a busy server, run a server-side
> trace with a filter (e.g. RPC and T-SQL batch completed events with durati
on
> greater than 1000000 microseconds) instead of running the Profiler tool
> directly against a busy production server. You can develop the desired
> trace using Profiler on a dev box and then script that trace so that you c
an
> run on the prod server as a server-side trace.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "karlag92" <karla...@.hotmail.com> wrote in message
> news:1186013382.486135.138640@.r34g2000hsd.googlegroups.com...
>
>
>
>
>
>
> - Show quoted text -
Thanks for the input guys, but I still need a simple way to configure
sql server to protect itself. Maybe what Ken suggests would work, but
it seems like a lot of work to accomplish something so basic.
I do intend to do root cause analysis and find the problem in the code
that causes this. However, I do not have even a part time DBA to
monitor and watch for these things to happen. If I could kill the
transaction automatically form the DB side when it runs too long, this
would help stop it from becoming a major problem but not deprive me of
what I need to fix the problem.
Transactions are managed in the client code in the web service. I
have no doubt that something broke there and will chase that down.
I just need a simple way to have SQL Server not allow a problem to
fester overly long . . . It would reduce the impact and severity of
the problem dramatically.|||I think your question expressed as a need is valid.
There is probably good reason(s) why Microsoft doesn't allow you to
enforce this policy or rule on sql server.
I invite the SQL MVPs to comment on why that is.|||> There is probably good reason(so) why Microsoft doesn't allow you to
> enforce this policy or rule on sql server.
IMHO, this is something that should be implemented in the client API (like
CommandTimeout) rather than on the server side. I would be very concerned
about specifying a global transaction timeout on the server. As a server
policy, the timeout would need to be accompanied by more restrictive
criteria, such as host or application name name.
If this feature is important to you or karlag92, consider submitted a
product enhancement request via Connect Feedback
(http://connect.microsoft.com/SQLServer).
Hope this helps.
Dan Guzman
SQL Server MVP
"Ken" <kshapley@.sbcglobal.net> wrote in message
news:1186168209.808571.152240@.d55g2000hsg.googlegroups.com...
>I think your question expressed as a need is valid.
> There is probably good reason(s) why Microsoft doesn't allow you to
> enforce this policy or rule on sql server.
> I invite the SQL MVPs to comment on why that is.
>

Limiting Transaction Time?

Please be gentle, I'm an application developer, not a dba. I just
dabble as a DBA for our shop.
Our application is distributed and works through web services to
access information stored in SQL Server 2005. Each request to the web
service is a contained transaction, in other words, transactions do
NOT cross calls web service calls.
Today something unknown and yet unidentified went wrong in the code
and a transaction was open indefinitely (over an hour until I noticed
it by accident and killed the offending process).
Question: Is there a setting that I'm not seeing for the server or
database that will limit how long a transaction can hold locks for?
For example, in our scenario no transaction should be longer than a
few seconds, so if I could set something in sql server that would
rollback and kill any transaction that lasted for more than 1 minute,
I'd be able to limit the impact of this problem.
Thanks in advance for any help you can give . . .For this case, I don't think its locks you should be concerned about?
Before killing a connection, I would look in the master..sysprocesses
table or run sp_who2 and find out what application. On spid run dbcc
inputbuffer(spid) and see what what's being or what was executed. Is
the connection in a sleeping state? Chances you are not getting a
commit or rollback from the application or service stopped and some
how could not initiate a rollback. You could write a query against the
master..sysprocesses table that checks for connection in an open_tran
> 0 and in a sleeping state and last_batch is greater than a specified
time. Then run a kill statement on it. You can acheive this via cursor
from the query result. If you are using some connection mechanism that
leaves your connetion in open_tran state of 1 (I've seen some java
drivers do this) then try open_tran >1. However, I don't advocate
doing this. Best thing to do let it happen again and troubleshoot the
application. The connection will eventually be closed see See
Orphaned Sessions in BOL.|||I agree with Ken that you should perform root cause analysis. Consider
adding SET XACT_ABORT ON to stored procedures that begin explicit
transactions. This setting will rollback the transaction immediately and
cancel the batch in the event of most errors, including command timeouts.
If you start transactions from application code, execute SET XACT_ABORT ON
on the connection after opening to help ensure pooled connections
immediately rollback connections in the event of an error. Also check to
ensure a commit or rollback is issued on all code paths.
Problems with performance (e.g. scans) can often lead to performance and
concurrency (blocking) problems, which in turn cause other queries and
transactions to run longer and then lead to even more problems. The best
way to break the cycle is by optimizing queries and adding indexes for
efficient data access. Start by analyzing the execution plan in SSMS for
those queries that are the longest running and most frequently executed.
See http://support.microsoft.com/kb/224453 for some tips. Although the
article was written for SQL 7 and 2000, much applies to SQL 2005 too.
If you need to gather SQL trace data from a busy server, run a server-side
trace with a filter (e.g. RPC and T-SQL batch completed events with duration
greater than 1000000 microseconds) instead of running the Profiler tool
directly against a busy production server. You can develop the desired
trace using Profiler on a dev box and then script that trace so that you can
run on the prod server as a server-side trace.
Hope this helps.
Dan Guzman
SQL Server MVP
"karlag92" <karlag92@.hotmail.com> wrote in message
news:1186013382.486135.138640@.r34g2000hsd.googlegroups.com...
> Please be gentle, I'm an application developer, not a dba. I just
> dabble as a DBA for our shop.
> Our application is distributed and works through web services to
> access information stored in SQL Server 2005. Each request to the web
> service is a contained transaction, in other words, transactions do
> NOT cross calls web service calls.
> Today something unknown and yet unidentified went wrong in the code
> and a transaction was open indefinitely (over an hour until I noticed
> it by accident and killed the offending process).
> Question: Is there a setting that I'm not seeing for the server or
> database that will limit how long a transaction can hold locks for?
> For example, in our scenario no transaction should be longer than a
> few seconds, so if I could set something in sql server that would
> rollback and kill any transaction that lasted for more than 1 minute,
> I'd be able to limit the impact of this problem.
> Thanks in advance for any help you can give . . .
>|||On Aug 2, 6:51 am, "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
wrote:
> I agree with Ken that you should perform root cause analysis. Consider
> adding SET XACT_ABORT ON to stored procedures that begin explicit
> transactions. This setting will rollback the transaction immediately and
> cancel the batch in the event of most errors, including command timeouts.
> If you start transactions from application code, execute SET XACT_ABORT ON
> on the connection after opening to help ensure pooled connections
> immediately rollback connections in the event of an error. Also check to
> ensure a commit or rollback is issued on all code paths.
> Problems with performance (e.g. scans) can often lead to performance and
> concurrency (blocking) problems, which in turn cause other queries and
> transactions to run longer and then lead to even more problems. The best
> way to break the cycle is by optimizing queries and adding indexes for
> efficient data access. Start by analyzing the execution plan in SSMS for
> those queries that are the longest running and most frequently executed.
> Seehttp://support.microsoft.com/kb/224453for some tips. Although the
> article was written for SQL 7 and 2000, much applies to SQL 2005 too.
> If you need to gather SQL trace data from a busy server, run a server-side
> trace with a filter (e.g. RPC and T-SQL batch completed events with duration
> greater than 1000000 microseconds) instead of running the Profiler tool
> directly against a busy production server. You can develop the desired
> trace using Profiler on a dev box and then script that trace so that you can
> run on the prod server as a server-side trace.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "karlag92" <karla...@.hotmail.com> wrote in message
> news:1186013382.486135.138640@.r34g2000hsd.googlegroups.com...
>
> > Please be gentle, I'm an application developer, not a dba. I just
> > dabble as a DBA for our shop.
> > Our application is distributed and works through web services to
> > access information stored in SQL Server 2005. Each request to the web
> > service is a contained transaction, in other words, transactions do
> > NOT cross calls web service calls.
> > Today something unknown and yet unidentified went wrong in the code
> > and a transaction was open indefinitely (over an hour until I noticed
> > it by accident and killed the offending process).
> > Question: Is there a setting that I'm not seeing for the server or
> > database that will limit how long a transaction can hold locks for?
> > For example, in our scenario no transaction should be longer than a
> > few seconds, so if I could set something in sql server that would
> > rollback and kill any transaction that lasted for more than 1 minute,
> > I'd be able to limit the impact of this problem.
> > Thanks in advance for any help you can give . . .- Hide quoted text -
> - Show quoted text -
Thanks for the input guys, but I still need a simple way to configure
sql server to protect itself. Maybe what Ken suggests would work, but
it seems like a lot of work to accomplish something so basic.
I do intend to do root cause analysis and find the problem in the code
that causes this. However, I do not have even a part time DBA to
monitor and watch for these things to happen. If I could kill the
transaction automatically form the DB side when it runs too long, this
would help stop it from becoming a major problem but not deprive me of
what I need to fix the problem.
Transactions are managed in the client code in the web service. I
have no doubt that something broke there and will chase that down.
I just need a simple way to have SQL Server not allow a problem to
fester overly long . . . It would reduce the impact and severity of
the problem dramatically.|||I think your question expressed as a need is valid.
There is probably good reason(s) why Microsoft doesn't allow you to
enforce this policy or rule on sql server.
I invite the SQL MVPs to comment on why that is.|||> There is probably good reason(so) why Microsoft doesn't allow you to
> enforce this policy or rule on sql server.
IMHO, this is something that should be implemented in the client API (like
CommandTimeout) rather than on the server side. I would be very concerned
about specifying a global transaction timeout on the server. As a server
policy, the timeout would need to be accompanied by more restrictive
criteria, such as host or application name name.
If this feature is important to you or karlag92, consider submitted a
product enhancement request via Connect Feedback
(http://connect.microsoft.com/SQLServer).
Hope this helps.
Dan Guzman
SQL Server MVP
"Ken" <kshapley@.sbcglobal.net> wrote in message
news:1186168209.808571.152240@.d55g2000hsg.googlegroups.com...
>I think your question expressed as a need is valid.
> There is probably good reason(s) why Microsoft doesn't allow you to
> enforce this policy or rule on sql server.
> I invite the SQL MVPs to comment on why that is.
>

Limiting a connection to an application.

Dear All,
We use NTAuthorisation. The other day we had the auditors in the other who
pointed out a problem where although security has been implelemented data ca
n
still be accessed but using a different application i.e. word excel ect
rather than our VB App.
What is the best way of changing this so that SQL will only accept
connections from a particuar application (i.e. our vb application)
TIAI suggest you check out "application roles" in Books Online.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Patricia" <Patricia@.discussions.microsoft.com> wrote in message
news:68B94CD5-3C40-404A-A236-40E1BF7ACD02@.microsoft.com...
> Dear All,
> We use NTAuthorisation. The other day we had the auditors in the other who
> pointed out a problem where although security has been implelemented data
can
> still be accessed but using a different application i.e. word excel ect
> rather than our VB App.
> What is the best way of changing this so that SQL will only accept
> connections from a particuar application (i.e. our vb application)
> TIA|||Did you think about a application role ?
http://www.sqlteam.com/item.asp?ItemID=864
HTH, Jens Smeyer.
http://www.sqlserver2005.de
--
"Patricia" <Patricia@.discussions.microsoft.com> schrieb im Newsbeitrag
news:68B94CD5-3C40-404A-A236-40E1BF7ACD02@.microsoft.com...
> Dear All,
> We use NTAuthorisation. The other day we had the auditors in the other who
> pointed out a problem where although security has been implelemented data
> can
> still be accessed but using a different application i.e. word excel ect
> rather than our VB App.
> What is the best way of changing this so that SQL will only accept
> connections from a particuar application (i.e. our vb application)
> TIA|||AFAIK, you can limit on that using Application Roles. You can take a look at
the Application roles on BOL under the title "Establishing Application
Security and Application Roles".
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"Patricia" <Patricia@.discussions.microsoft.com> wrote in message
news:68B94CD5-3C40-404A-A236-40E1BF7ACD02@.microsoft.com...
> Dear All,
> We use NTAuthorisation. The other day we had the auditors in the other who
> pointed out a problem where although security has been implelemented data
can
> still be accessed but using a different application i.e. word excel ect
> rather than our VB App.
> What is the best way of changing this so that SQL will only accept
> connections from a particuar application (i.e. our vb application)
> TIA

limited number of connections to SQLMobile?

Using System.Data.SqlClient is there a limit to the number of connections an application can have to a SQLMobile dB?

If you have a look at the url below, it says "A device can only have a small number of connections to an instance of SQL Server at any time"

Does this mean 2, or 8 or what?
Should a app try to use only 1 connection throughout, or can we get away with 2-3?
Is this the same on WM5.0/PPP2003?

Any advice in this area much appreciated!

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlce/htm/_lce_sqlclient_705.asp

p.s. I'm not interested in connection pooling!

Connections to SQL Mobile have little in common with connections to SQL Server. First, the managed provider for SQL Mobile is System.Data.SqlServerCe, which is a different creature than the SqlClient you refer to in your post (which is used to connect to an instance of SQL Server).

Yes, you can have multiple connections to a SQL Mobile database. System.Data.SqlServerCe does not support pooling or encryption of connection strings however.

I am not aware of a limit to the number of SqlCeConnections you can have open against a single SQL Moble database, but if there is one it is more than a dozen as I have done benchmarking with more than this.

Darren

limite the resource a stored procedure can use

Dear sir,
Some times, some unimportant stored procedure on my application are using
too much resource, etc 50% of CUP power. Is there a way to set the priority
of a SP or limite the max resource it can use?
thanks,
Guoqi Zheng
http://www.ureader.comThe only thing you can limit in SQL Server is how many processors a SQL
statement can use on a multi-processor machine. You can do this by adding
the OPTION (MAXDOP n) at the end of the statement, where n is the maximum
number of processors you want the statement to use.
Jacco Schalkwijk
SQL Server MVP
"guoqi zheng" <no@.sorry.com> wrote in message
news:dfc72681649d44efadd824ba066e2126@.ur
eader.com...
> Dear sir,
> Some times, some unimportant stored procedure on my application are using
> too much resource, etc 50% of CUP power. Is there a way to set the
> priority
> of a SP or limite the max resource it can use?
> thanks,
> Guoqi Zheng
> http://www.ureader.com|||To add on to Jacco's response, consider index and query tuning if you
haven't already done so. Excessive CPU utilization can be an indication
that additional indexes are needed or a poorly formulated query. It's best
to address the underlying cause when possible.
Hope this helps.
Dan Guzman
SQL Server MVP
"guoqi zheng" <no@.sorry.com> wrote in message
news:dfc72681649d44efadd824ba066e2126@.ur
eader.com...
> Dear sir,
> Some times, some unimportant stored procedure on my application are using
> too much resource, etc 50% of CUP power. Is there a way to set the
> priority
> of a SP or limite the max resource it can use?
> thanks,
> Guoqi Zheng
> http://www.ureader.com|||Thanks.
The CPU usage is because of some calculation. like using SELECT COUNT(*),
SUM(*) to update another table, and the records to be counted or summed are
a few 100000s.
I haven't know what might be better to do those calculation yet. The
calculation needs to be done a few times per day.
any suggestions?
regards,
Guoqi Zheng
http://www.ureader.com|||You might consider using an indexed view for frequently used aggregations.
Covering indexes may also help.
Hope this helps.
Dan Guzman
SQL Server MVP
"guoqi zheng" <no@.sorry.com> wrote in message
news:b685cc9d335546c0891cf9e7fe284d09@.ur
eader.com...
> Thanks.
> The CPU usage is because of some calculation. like using SELECT COUNT(*),
> SUM(*) to update another table, and the records to be counted or summed
> are
> a few 100000s.
> I haven't know what might be better to do those calculation yet. The
> calculation needs to be done a few times per day.
> any suggestions?
> regards,
> Guoqi Zheng
> http://www.ureader.com

limit the no. of users

I have an application that uses a small SQL Server database.
What I need is to prevent the access of an amount of users to the database.
At the beginning I want to limit the access only for 2 users but I want to be able in the future to grant the access to more.u can limit no: of concurrent users at server level,but U cant limit no: of user at database level from sql server.|||Actually, I want only 2 users to be able to use my application at the same time and I cant figure out which is the best solution to implement this.|||what kind of application is that? web based or windows based?
Is that application is installed on each computer or shared thru network if it is windows based?|||It's a windows client-server application.
I am thinking of giving a session ID when a user log in. Every time the client uses the database I'll check the session ID if is valid.
When the client try to log in the 2nd time I'll give him another Session ID and I'll destroy the other one.
I have also to limit the session ID numbers to 2 for the beginning and I'll ignore users that are not in the top 2.
What do u thinck about this?|||the solution you suggested will work, but the problem with it is - it requires maintenance module to manually disconnect users those who have accidentally or by mistake closed the application/machine without proper logout.

SQL server allows you to create unique application objects and lock them during execution. I think that can be an alternative solution to the problem. check sp_getapplock on BOL. the best thing is - it automatically releases the lock as soon as the connection is lost.

Limit query response time to 5 mins

Right now we have queries running from 20 ms to 200 secs or even more.. We
know that from our application perspective, we do not expect our customers
to be around if the response time is greater than say 5 mins. Is there a way
that we can control this at the db level or server level from the SQL side
vs the application layer ?
Also right now, if I have to profile and say for example run a query that
runs for 5 mins.. If i cancel the query in 2 mins, the profiler reports a
batch completed event for 2 mins. Is there any way to find out that that
query was a cancelled query and did not run to completion ?
Using SQL 2KHassan
Yes, SQL Server Profiler is your friend here, but why would want to cancel
the query? Do you really want the users cancel queries?
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%23VoNa4hfGHA.5104@.TK2MSFTNGP04.phx.gbl...
> Right now we have queries running from 20 ms to 200 secs or even more.. We
> know that from our application perspective, we do not expect our customers
> to be around if the response time is greater than say 5 mins. Is there a
> way that we can control this at the db level or server level from the SQL
> side vs the application layer ?
> Also right now, if I have to profile and say for example run a query that
> runs for 5 mins.. If i cancel the query in 2 mins, the profiler reports a
> batch completed event for 2 mins. Is there any way to find out that that
> query was a cancelled query and did not run to completion ?
> Using SQL 2K
>
>|||I am just looking for a way to do so.. Not really narrowed it down to how we
would use it eventually
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23wjjDoifGHA.4568@.TK2MSFTNGP03.phx.gbl...
> Hassan
> Yes, SQL Server Profiler is your friend here, but why would want to cancel
> the query? Do you really want the users cancel queries?
>
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:%23VoNa4hfGHA.5104@.TK2MSFTNGP04.phx.gbl...
>

Limit query response time to 5 mins

Right now we have queries running from 20 ms to 200 secs or even more.. We
know that from our application perspective, we do not expect our customers
to be around if the response time is greater than say 5 mins. Is there a way
that we can control this at the db level or server level from the SQL side
vs the application layer ?
Also right now, if I have to profile and say for example run a query that
runs for 5 mins.. If i cancel the query in 2 mins, the profiler reports a
batch completed event for 2 mins. Is there any way to find out that that
query was a cancelled query and did not run to completion ?
Using SQL 2KHassan
Yes, SQL Server Profiler is your friend here, but why would want to cancel
the query? Do you really want the users cancel queries?
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%23VoNa4hfGHA.5104@.TK2MSFTNGP04.phx.gbl...
> Right now we have queries running from 20 ms to 200 secs or even more.. We
> know that from our application perspective, we do not expect our customers
> to be around if the response time is greater than say 5 mins. Is there a
> way that we can control this at the db level or server level from the SQL
> side vs the application layer ?
> Also right now, if I have to profile and say for example run a query that
> runs for 5 mins.. If i cancel the query in 2 mins, the profiler reports a
> batch completed event for 2 mins. Is there any way to find out that that
> query was a cancelled query and did not run to completion ?
> Using SQL 2K
>
>|||I am just looking for a way to do so.. Not really narrowed it down to how we
would use it eventually
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23wjjDoifGHA.4568@.TK2MSFTNGP03.phx.gbl...
> Hassan
> Yes, SQL Server Profiler is your friend here, but why would want to cancel
> the query? Do you really want the users cancel queries?
>
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:%23VoNa4hfGHA.5104@.TK2MSFTNGP04.phx.gbl...
>> Right now we have queries running from 20 ms to 200 secs or even more..
>> We know that from our application perspective, we do not expect our
>> customers to be around if the response time is greater than say 5 mins.
>> Is there a way that we can control this at the db level or server level
>> from the SQL side vs the application layer ?
>> Also right now, if I have to profile and say for example run a query that
>> runs for 5 mins.. If i cancel the query in 2 mins, the profiler reports a
>> batch completed event for 2 mins. Is there any way to find out that that
>> query was a cancelled query and did not run to completion ?
>> Using SQL 2K
>>
>