Deadlocks same time Full DB backup

I have a cluster SQL Server 2000 Enterprise with SP3A
that has a full database backup that runs nightly at
12:15 AM.
The database system has functioning properly for about
six months.
Three consective dailys in a row I have received a
deadlock at the same as the full database is running
(12:15 AM). The full database executes for about 2
minutes. Thats about the same length of time as the
deadlock. This deadlock information comes from the DBCC
Traceon (1204) setting.
What could cause this problem?
Thank You,
FrankCan you post what U get in the SQL server errorlog this way we can see the
sequence of events leading to the deadlock issue..I guess that SQL server
service is starting with the -T1204 -T3605
to ensure that this info is sent to the error log.
--
Olu Adedeji
"Frank" <anonymous@.discussions.microsoft.com> wrote in message
news:4fd801c4000e$53839150$a501280a@.phx.gbl...
> I have a cluster SQL Server 2000 Enterprise with SP3A
> that has a full database backup that runs nightly at
> 12:15 AM.
> The database system has functioning properly for about
> six months.
> Three consective dailys in a row I have received a
> deadlock at the same as the full database is running
> (12:15 AM). The full database executes for about 2
> minutes. Thats about the same length of time as the
> deadlock. This deadlock information comes from the DBCC
> Traceon (1204) setting.
>
> What could cause this problem?
> Thank You,
> Frank|||My 1st guess is that you are experiencing IO Saturation.
Is your backup occuring on a separate volume than your data ?
Greg Jackson
PDX, Oregonsql

Deadlocks same time Full DB backup

I have a cluster SQL Server 2000 Enterprise with SP3A
that has a full database backup that runs nightly at
12:15 AM.
The database system has functioning properly for about
six months.
Three consective dailys in a row I have received a
deadlock at the same as the full database is running
(12:15 AM). The full database executes for about 2
minutes. Thats about the same length of time as the
deadlock. This deadlock information comes from the DBCC
Traceon (1204) setting.
What could cause this problem?
Thank You,
FrankCan you post what U get in the SQL server errorlog this way we can see the
sequence of events leading to the deadlock issue..I guess that SQL server
service is starting with the -T1204 -T3605
to ensure that this info is sent to the error log.
Olu Adedeji
"Frank" <anonymous@.discussions.microsoft.com> wrote in message
news:4fd801c4000e$53839150$a501280a@.phx.gbl...
> I have a cluster SQL Server 2000 Enterprise with SP3A
> that has a full database backup that runs nightly at
> 12:15 AM.
> The database system has functioning properly for about
> six months.
> Three consective dailys in a row I have received a
> deadlock at the same as the full database is running
> (12:15 AM). The full database executes for about 2
> minutes. Thats about the same length of time as the
> deadlock. This deadlock information comes from the DBCC
> Traceon (1204) setting.
>
> What could cause this problem?
> Thank You,
> Frank|||My 1st guess is that you are experiencing IO Saturation.
Is your backup occuring on a separate volume than your data ?
Greg Jackson
PDX, Oregon

Deadlock Issue when dropping/creating tables

We are getting deadlock errors (sporadically) on a batch job we've created.

This job runs against a SQL Server 2000 back-end.

The first step of the batch job is to run a DDL script to drop and create 4 tables that are used in the job. The tables are only used during this job and are not accessed by any other process or application.

The second step of the batch job is to make an OSQL call to run the stored procedures associated with the job.

The deadlocks occur during the first step in the job, during the drop/create table statements. A sample follows:

Msg 1205, Level 13, State 54, Server SQL\APP_PROD, Line 7
Transaction (Process ID 78) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction.

I am no DBA but can't understand how we can be getting a deadlock while dropping and creating tables that are used by no other processes or applications.

Any thoughts or help would be greatly appreciated.

Quote:

Originally Posted by DWiggin

We are getting deadlock errors (sporadically) on a batch job we've created.

This job runs against a SQL Server 2000 back-end.

The first step of the batch job is to run a DDL script to drop and create 4 tables that are used in the job. The tables are only used during this job and are not accessed by any other process or application.

The second step of the batch job is to make an OSQL call to run the stored procedures associated with the job.

The deadlocks occur during the first step in the job, during the drop/create table statements. A sample follows:

Msg 1205, Level 13, State 54, Server SQL\APP_PROD, Line 7
Transaction (Process ID 78) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction.

I am no DBA but can't understand how we can be getting a deadlock while dropping and creating tables that are used by no other processes or applications.

Any thoughts or help would be greatly appreciated.

if you ran your stored proc and then run it again, you'll have problem since it's still being used by the first one. possible locks will happen. try to create a tempoary table with randomly-generated table names...

Deadlock

Hi there!
In my database client application (C#) I sometimes get an exception telling
me, that my application (which runs a some kind ob batch job) was selected
as deadlock victim and the active transaction was terminated.
No I want to get mor information about, which other application causes the
deadlock.
I thought about executing "sp_lock" just after I received the
deadlock-exception, but I think that this is too late, because my
transaction will be already terminated at this time, and ther won't be a
deadlock any longer.
Can anyone tell me, what would be the best way to receive information about
the active database-connections and their locks, BEFORE the deadlock
situation is cleared by the database-engine?
Is there some kind of trigger or sth. like that?
As the problem doesn't occur regularly, and it's only on the productive
machine, I absolutely don't want to have the profiler active!
Thanks in advance!
Max
Markus
Take a look at sp_who2 stored procedure on clolumn blkby (If I remember
well) . That tells you who is blocked and by whom.
Regarding to DEADLOCKS please visit at this site
http://www.sql-server-performance.com/deadlocks.asp
"Markus Emayr" <essmayr/at/racon-linz.at> wrote in message
news:%23GGruXUfFHA.1412@.TK2MSFTNGP09.phx.gbl...
> Hi there!
> In my database client application (C#) I sometimes get an exception
telling
> me, that my application (which runs a some kind ob batch job) was selected
> as deadlock victim and the active transaction was terminated.
> No I want to get mor information about, which other application causes the
> deadlock.
> I thought about executing "sp_lock" just after I received the
> deadlock-exception, but I think that this is too late, because my
> transaction will be already terminated at this time, and ther won't be a
> deadlock any longer.
> Can anyone tell me, what would be the best way to receive information
about
> the active database-connections and their locks, BEFORE the deadlock
> situation is cleared by the database-engine?
> Is there some kind of trigger or sth. like that?
> As the problem doesn't occur regularly, and it's only on the productive
> machine, I absolutely don't want to have the profiler active!
> Thanks in advance!
> Max
>
|||Use the trace flag 1024 to receive notifications about deadlocks.
DBCC TRACEON (1024, -1)
[]s
Luciano Caixeta Moreira
Meu blog: http://br.thespoke.net/MyBlog/Luti/MyBlog.aspx
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OMKxokUfFHA.1044@.tk2msftngp13.phx.gbl...
> Markus
> Take a look at sp_who2 stored procedure on clolumn blkby (If I remember
> well) . That tells you who is blocked and by whom.
> Regarding to DEADLOCKS please visit at this site
> http://www.sql-server-performance.com/deadlocks.asp
>
>
> "Markus Emayr" <essmayr/at/racon-linz.at> wrote in message
> news:%23GGruXUfFHA.1412@.TK2MSFTNGP09.phx.gbl...
> telling
> about
>

DBNETLIB Error

Hi All,

I have a problem with a specific query which causes the error below. It runs fine on the server but refuses to work on my Query Analyzer.

select a.cola
from tablea a
where a.cola not in (select b.cola from tableb b)

[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (WrapperRead()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.

Connection Broken

I have MDAC 2.8 installed. SQL Server 2000. There are no entries in the logs, no backups running or deadlocks detected. My Collegues machine runs it fine?

Any Ideas please? All help gratefully received...

Thanks,
JimAre you on windows 2003?
Try to execute other query statements to ensure no issues on your client machine.|||I was encountering similar problems with an application that retrieves data from a remote server (some SQL7, some SQL2K) and inserts into the local server (SQL2K in my case). A particular query ran in T-SQL but not from the ADO object when querying a SQL7 remote server; other queries worked fine.

In DBForums I saw many references to issues with MDAC 2.8, so I rolled MDAC back to 2.6 and discovered that a "Timeout Expired." error was being returned to my app. I then tried the query against another remote server and it worked fine. Looks like:

1 - MDAC 2.8 was shielding me from being able to see the real error, which was a timeout on the SQLServer 7 remote server.
2 - SQL7 or MDAC on the remote server was having trouble handling the query; KB 300519 recommends applying SQL7 SP4.|||I was encountering similar problems with an application that retrieves data from a remote server (some SQL7, some SQL2K) and inserts into the local server (SQL2K in my case). A particular query ran in T-SQL but not from the ADO object when querying a SQL7 remote server; other queries worked fine.

In DBForums I saw many references to issues with MDAC 2.8, so I rolled MDAC back to 2.6 and discovered that a "Timeout Expired." error was being returned to my app. I then tried the query against another remote server and it worked fine. Looks like:

1 - MDAC 2.8 was shielding me from being able to see the real error, which was a timeout on the SQLServer 7 remote server.
2 - SQL7 or MDAC on the remote server was having trouble handling the query; KB 300519 recommends applying SQL7 SP4.|||In any case ensure to maintain similar versions of MDAC if this kind of data compilation is involved.

DBLookup in SQL Server

MS Access has a very udeful fiunction called DBLookup.

It takes 4 parameters: a column, a table, an IDField and an IDValue.

It essentially runs this as a select statement and returns a single value from a database.

Is there an equivalent in SQL Server or .NET 2.0?

Is there a way to write this as a Stored Procedure without granting select permissions on the tables?

Thanks

Take a look at ExecuteScalar in the ADO.NET classes.

dblib support in SQL 2005?

Hi there,

We have a server application that's built on DBLIB. From what I can tell, it runs on SQL2005, but I'm looking for official confirmation that it's supported. I recall seeing a document recently that said it was supported, but that there would be no documentation released for the API set any longer.

Anyone have any links to official information?

Thanks.

The following is copied from an internal email. It should answer your question. I have no official link for this except http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dblibc/dbc_pdc00b_589v.asp

Just to answer any lingering questions.
- ntwdblib.dll never shipped with the OS.
- ntwdblib.dll did ship with SQLServer up through SQLServer 2005. It is now deprecated.
- sqlserver 2005 supports down level to three versions. So you can use a dblib (ntwdblid.dll) based application to connect to sqlserver 2005.
- ntwdblib.dll never shipped a 64bit version.
Please make sure the ISV understands that they should be actively porting this application over to odbc or oledb.

|||

Here is the official doc.

http://msdn2.microsoft.com/en-us/library/ms143729(en-US,SQL.90).aspx

Although the SQL Server 2005 Database Engine still supports connections from existing applications using the DB-Library and Embedded SQL APIs, it does not include the files or documentation needed to do programming work on applications that use these APIs. A future version of the SQL Server Database Engine will drop support for connections from DB-Library or Embedded SQL applications. Do not use DB-Library or Embedded SQL to develop new applications. Remove any dependencies on either DB-Library or Embedded SQL when modifying existing applications. Instead of these APIs, use the SQLClient namespace or an API such as OLE DB or ODBC. SQL Server 2005 does not include the DB-Library DLL required to run these applications. To run DB-Library or Embedded SQL applications you must have available the DB-Library DLL from SQL Server version 6.5, SQL Server 7.0, or SQL Server 2000.