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

Deadlocks problem when database files growing

We are running a very busy SQL Server 2000 Enterprise in the cluster "passive active" environment. Hundreds of transactions are going through and all of them are
logged in to a special database we've created. For an each real transaction we are getting around 10 records inserted in to this database.
I found that whenever the database grows its files, especially the log file, we're getting a lot of deadlocks which we are able to resolve only by failing over to another node.

Any suggestions would be appreciated.

Thanks,

DanHowdy

Is this a recent problem of a long term issue?
Sounds like an application design issue....I doubt he growing logfiles would cause the problem - they would just be a symptom of how busy the system is. Also, if the logs grow really quickly, its possible the app is holding open tables etc too long and then causing the deadlocks. Shorter tansactuions may help. I have used locking hint TABLOCKX to get around a lot of problems, but it MAY NOT be the best solution for you. Sounds very application specific.........

I assume you have plenty of disk space for the TEMPDB and the database files?

More info / background would be useful.

Cheers,

SG

deadlocks involving parallelism

We're experiencing a large number of deadlocks since we began running
SQL Server 2000 Enterprise Edition SP3 on a Dell 6650 with hyper
threading intel processors. We don't have the same problem on Dell
6650's w/o the hyper threading. If I turn off the parallel query
processing option the deadlocks stop. I've tried all of the suggestions
from the Microsoft Knowledge Base under the following link -

http://support.microsoft.com/?kbid=837983

The only suggestion that actually yielded results was turning off
parallel query processing but I don't want to give up what should be a
performance advantage if it wasn't for the deadlocks. Query tuning and
index tuning hasn't helped. Any suggestions? I haven't applied SP4
yet. I'm wondering if anyone has seen the same problem resolved with
SP4.

*** Sent via Developersdex http://www.developersdex.com ***T Dubya (timber_toes@.bigfoot.com) writes:
> We're experiencing a large number of deadlocks since we began running
> SQL Server 2000 Enterprise Edition SP3 on a Dell 6650 with hyper
> threading intel processors. We don't have the same problem on Dell
> 6650's w/o the hyper threading. If I turn off the parallel query
> processing option the deadlocks stop. I've tried all of the suggestions
> from the Microsoft Knowledge Base under the following link -

A general recommendation is to change "max degree of parallelism" to
the number of physical processors. Whether this will help your parallelism
deadlocks, I don't know, but you should make that configuration anyway.

As it was explained to me, HT processors creates that extra CPU by
giving it idle cycles from the first processor. But if you have a
parallel query, those idle cycles are not really there, and you get
a serialization of the processing.

If that does not, try tracking down the query/ies that have this
problem, and add "OPTION (MAXDOP 1)" to these queries, to turn off
parallelism for these queries.

> I haven't applied SP4 yet. I'm wondering if anyone has seen the same
> problem resolved with SP4.

I have no idea if that will help, but some general notes on SP4:

SP4 is here: http://www.microsoft.com/sql/downloads/2000/sp4.mspx.
Please observe the note about AWE. The note is out of date, since
there actually is a fix for the AWE problem; just follow the link
in the note.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for the suggestion. I'll give it a try.
I found a "Best Practices" note in my Microsoft SQL Server 2000
Administrators Pocket Consultant on page 38 that recommends not
assigning the higher numbered processors (5,6,7, and 8) to the SQL
Server. It goes on to explain that Windows assigns deferred process
calls associated with network interface cards to the highest numbered
processors. If the system has two NICs, for example, the calls would be
directed to CPUs 7 and 8. Even though the default installation made
processors 0 through 7 available to the SQL Server it sounds like the
recommendation is to only make 0 through 3 available. What do you
think? Perhaps this would have the same effect as only assigning 4
processors for parallel execution of queries.

*** Sent via Developersdex http://www.developersdex.com ***|||T Dubya (timber_toes@.bigfoot.com) writes:
> Thanks for the suggestion. I'll give it a try.
> I found a "Best Practices" note in my Microsoft SQL Server 2000
> Administrators Pocket Consultant on page 38 that recommends not
> assigning the higher numbered processors (5,6,7, and 8) to the SQL
> Server. It goes on to explain that Windows assigns deferred process
> calls associated with network interface cards to the highest numbered
> processors. If the system has two NICs, for example, the calls would be
> directed to CPUs 7 and 8. Even though the default installation made
> processors 0 through 7 available to the SQL Server it sounds like the
> recommendation is to only make 0 through 3 available. What do you
> think? Perhaps this would have the same effect as only assigning 4
> processors for parallel execution of queries.

I will have to admit that the discussion went over my head here. If CPU:s
0-3 are the "default CPU" of each physical processor, this seems like
a good choice. I will have to admit that I don't know how processors
are numbered in a multi-processor HT box.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland, Dubaya,

your problem is SQL Server 2000 SP3 - SP3 is not hiperthread aware
which means that if your query is parallelized into several worker
threads, these threads might end-up running concurrently on the
same physical processor, which means 2 threads running on 1 physical
processor due to Hyperthreading. I know that there have been made some
changes in build 818, and SP4, especially regarding HT and NUMA -
what you basically sohuld do is test your situation with build 818 or
SP4,
or turn off hyperthreading. Test, but be aware that Hyperthreading
is only giving you maybe 10% extra performance if you're lucky,
whereas
your parallisme within SQL Server can give you enormous amounts of
performance gains. Its no secret that Intel made hyperthreading since
the extra thread could run Antivirus software while the CPU was more
a less idle in some of their components. Running SQL Server 2000 with
hyperthreading can give you some headaches, try running on the latest
build
or turn of hyperthreading.sql

Deadlocking Limitations of SQL Server... tell me it isn't so.

Hi,
I have a client-server .NET system that uses an Enterprise Services
Serviced Component (COM+ component) for data access. Under high load,
I am getting deadlocking errors, they seem to be related to one table.
These situations are hard to debug, but I am guessing it is because
an update on a delete may be occurring on DIFFERENT ROWS in the same
table at the same time. This can't be right, can it?
I read something about problems when using indexes, but this table is
not indexed other than the primary key. The table definition is shown
below. Any suggestions would be appreciated.
Thanks!
*** TABLE DEFINITION (FIELDS HAVE BEEN RENAMED, BUT STRUCTURE IS
ACCURATE) ***
CREATE TABLE [Boo_Record_Foo] (
[Boo_Id] [int] NOT NULL ,
[Fooed_By_User_Id] [int] NULL ,
[Fooed_By_User_Name] [varchar] (30) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_Boo_Record_Foo] PRIMARY KEY CLUSTERED
(
[Boo_Id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO
*** ERROR MESSAGE ***
Transaction (Process ID 53) was deadlocked on {lock} resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction.COM+ tends to use the SERIALIZED isolation level which is never good for
multi-user apps. I would check to see what the isolation level is on all
the connections. You say your table has no index other than the PK
constraint. Is it ever accessed by anything other than the PK? Can you
show the 2 statements that are being used when it deadlocks?
--
Andrew J. Kelly
SQL Server MVP
"Don MacKenzie" <cd_mackenzie@.hotmail.com> wrote in message
news:2544f4a.0402131647.7bbd58cf@.posting.google.com...
> Hi,
> I have a client-server .NET system that uses an Enterprise Services
> Serviced Component (COM+ component) for data access. Under high load,
> I am getting deadlocking errors, they seem to be related to one table.
> These situations are hard to debug, but I am guessing it is because
> an update on a delete may be occurring on DIFFERENT ROWS in the same
> table at the same time. This can't be right, can it?
> I read something about problems when using indexes, but this table is
> not indexed other than the primary key. The table definition is shown
> below. Any suggestions would be appreciated.
> Thanks!
> *** TABLE DEFINITION (FIELDS HAVE BEEN RENAMED, BUT STRUCTURE IS
> ACCURATE) ***
> CREATE TABLE [Boo_Record_Foo] (
> [Boo_Id] [int] NOT NULL ,
> [Fooed_By_User_Id] [int] NULL ,
> [Fooed_By_User_Name] [varchar] (30) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> CONSTRAINT [PK_Boo_Record_Foo] PRIMARY KEY CLUSTERED
> (
> [Boo_Id]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> ) ON [PRIMARY]
> GO
>
> *** ERROR MESSAGE ***
> Transaction (Process ID 53) was deadlocked on {lock} resources with
> another process and has been chosen as the deadlock victim. Rerun the
> transaction.|||Hi Don.
You can get the precise reason for the deadlock by writing it's detailed
deadlock report to the SQL error log & inspecting that report. It's complex
to analyse, but if you post it back perhaps we could help you analyse it.
To write the detailed deadlock report to the error log, issue the following
command:
dbcc traceon (1204, 3605, -1)
1204 is the trace flag for detailed deadlock reports
3605 is the instruction to write that report to the sqwl error log
-1 is the instruction that the trace should apply to all connections, not
just the current connection that is issuing the dbcc traceon command.
Regards,
Greg Linwood
SQL Server MVP
"Don MacKenzie" <cd_mackenzie@.hotmail.com> wrote in message
news:2544f4a.0402131647.7bbd58cf@.posting.google.com...
> Hi,
> I have a client-server .NET system that uses an Enterprise Services
> Serviced Component (COM+ component) for data access. Under high load,
> I am getting deadlocking errors, they seem to be related to one table.
> These situations are hard to debug, but I am guessing it is because
> an update on a delete may be occurring on DIFFERENT ROWS in the same
> table at the same time. This can't be right, can it?
> I read something about problems when using indexes, but this table is
> not indexed other than the primary key. The table definition is shown
> below. Any suggestions would be appreciated.
> Thanks!
> *** TABLE DEFINITION (FIELDS HAVE BEEN RENAMED, BUT STRUCTURE IS
> ACCURATE) ***
> CREATE TABLE [Boo_Record_Foo] (
> [Boo_Id] [int] NOT NULL ,
> [Fooed_By_User_Id] [int] NULL ,
> [Fooed_By_User_Name] [varchar] (30) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> CONSTRAINT [PK_Boo_Record_Foo] PRIMARY KEY CLUSTERED
> (
> [Boo_Id]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> ) ON [PRIMARY]
> GO
>
> *** ERROR MESSAGE ***
> Transaction (Process ID 53) was deadlocked on {lock} resources with
> another process and has been chosen as the deadlock victim. Rerun the
> transaction.

Deadlocking Limitations of SQL Server... tell me it isn't so.

Hi,
I have a client-server .NET system that uses an Enterprise Services
Serviced Component (COM+ component) for data access. Under high load,
I am getting deadlocking errors, they seem to be related to one table.
These situations are hard to debug, but I am guessing it is because
an update on a delete may be occurring on DIFFERENT ROWS in the same
table at the same time. This can't be right, can it?
I read something about problems when using indexes, but this table is
not indexed other than the primary key. The table definition is shown
below. Any suggestions would be appreciated.
Thanks!
*** TABLE DEFINITION (FIELDS HAVE BEEN RENAMED, BUT STRUCTURE IS
ACCURATE) ***
CREATE TABLE [Boo_Record_Foo] (
[Boo_Id] [int] NOT NULL ,
[Fooed_By_User_Id] [int] NULL ,
[Fooed_By_User_Name] [varchar] (30) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_Boo_Record_Foo] PRIMARY KEY CLUSTERED
(
[Boo_Id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO
*** ERROR MESSAGE ***
Transaction (Process ID 53) was deadlocked on {lock} resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction.COM+ tends to use the SERIALIZED isolation level which is never good for
multi-user apps. I would check to see what the isolation level is on all
the connections. You say your table has no index other than the PK
constraint. Is it ever accessed by anything other than the PK? Can you
show the 2 statements that are being used when it deadlocks?
Andrew J. Kelly
SQL Server MVP
"Don MacKenzie" <cd_mackenzie@.hotmail.com> wrote in message
news:2544f4a.0402131647.7bbd58cf@.posting.google.com...
> Hi,
> I have a client-server .NET system that uses an Enterprise Services
> Serviced Component (COM+ component) for data access. Under high load,
> I am getting deadlocking errors, they seem to be related to one table.
> These situations are hard to debug, but I am guessing it is because
> an update on a delete may be occurring on DIFFERENT ROWS in the same
> table at the same time. This can't be right, can it?
> I read something about problems when using indexes, but this table is
> not indexed other than the primary key. The table definition is shown
> below. Any suggestions would be appreciated.
> Thanks!
> *** TABLE DEFINITION (FIELDS HAVE BEEN RENAMED, BUT STRUCTURE IS
> ACCURATE) ***
> CREATE TABLE [Boo_Record_Foo] (
> [Boo_Id] [int] NOT NULL ,
> [Fooed_By_User_Id] [int] NULL ,
> [Fooed_By_User_Name] [varchar] (30) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> CONSTRAINT [PK_Boo_Record_Foo] PRIMARY KEY CLUSTERED
> (
> [Boo_Id]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> ) ON [PRIMARY]
> GO
>
> *** ERROR MESSAGE ***
> Transaction (Process ID 53) was deadlocked on {lock} resources with
> another process and has been chosen as the deadlock victim. Rerun the
> transaction.|||Hi Don.
You can get the precise reason for the deadlock by writing it's detailed
deadlock report to the SQL error log & inspecting that report. It's complex
to analyse, but if you post it back perhaps we could help you analyse it.
To write the detailed deadlock report to the error log, issue the following
command:
dbcc traceon (1204, 3605, -1)
1204 is the trace flag for detailed deadlock reports
3605 is the instruction to write that report to the sqwl error log
-1 is the instruction that the trace should apply to all connections, not
just the current connection that is issuing the dbcc traceon command.
Regards,
Greg Linwood
SQL Server MVP
"Don MacKenzie" <cd_mackenzie@.hotmail.com> wrote in message
news:2544f4a.0402131647.7bbd58cf@.posting.google.com...
> Hi,
> I have a client-server .NET system that uses an Enterprise Services
> Serviced Component (COM+ component) for data access. Under high load,
> I am getting deadlocking errors, they seem to be related to one table.
> These situations are hard to debug, but I am guessing it is because
> an update on a delete may be occurring on DIFFERENT ROWS in the same
> table at the same time. This can't be right, can it?
> I read something about problems when using indexes, but this table is
> not indexed other than the primary key. The table definition is shown
> below. Any suggestions would be appreciated.
> Thanks!
> *** TABLE DEFINITION (FIELDS HAVE BEEN RENAMED, BUT STRUCTURE IS
> ACCURATE) ***
> CREATE TABLE [Boo_Record_Foo] (
> [Boo_Id] [int] NOT NULL ,
> [Fooed_By_User_Id] [int] NULL ,
> [Fooed_By_User_Name] [varchar] (30) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> CONSTRAINT [PK_Boo_Record_Foo] PRIMARY KEY CLUSTERED
> (
> [Boo_Id]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> ) ON [PRIMARY]
> GO
>
> *** ERROR MESSAGE ***
> Transaction (Process ID 53) was deadlocked on {lock} resources with
> another process and has been chosen as the deadlock victim. Rerun the
> transaction.

deadlock issue in sql server 2000 enterprise edition version 8.00.

we have installed sql server 2000 enterprise edition on our erp server.
We are facing frequent deadlock problem ie one process blocks the other
process frequently.
The compatibility of the databases has been set to 80.
First of all whether the version is that of enterprise edition ?
secondly any particular setting to resolve the deadlock issues ?To see what version you're on issue the following :-
SELECT SERVERPROPERTY('Edition')
This article may provide help with your deadlocking :-
http://support.microsoft.com/kb/271509/
--
HTH. Ryan
"Rajeev Rivankar" <Rajeev Rivankar@.discussions.microsoft.com> wrote in
message news:EEFEE7EC-A947-41F8-A92F-A3626B7A7BA6@.microsoft.com...
> we have installed sql server 2000 enterprise edition on our erp server.
> We are facing frequent deadlock problem ie one process blocks the other
> process frequently.
> The compatibility of the databases has been set to 80.
> First of all whether the version is that of enterprise edition ?
> secondly any particular setting to resolve the deadlock issues ?
>|||thanks for your feedback.
I have seen the article on deadlock but any simpler way to handle it.
like a sp_configure statement
"Ryan" wrote:
> To see what version you're on issue the following :-
> SELECT SERVERPROPERTY('Edition')
> This article may provide help with your deadlocking :-
> http://support.microsoft.com/kb/271509/
> --
> HTH. Ryan
>
> "Rajeev Rivankar" <Rajeev Rivankar@.discussions.microsoft.com> wrote in
> message news:EEFEE7EC-A947-41F8-A92F-A3626B7A7BA6@.microsoft.com...
> > we have installed sql server 2000 enterprise edition on our erp server.
> > We are facing frequent deadlock problem ie one process blocks the other
> > process frequently.
> > The compatibility of the databases has been set to 80.
> > First of all whether the version is that of enterprise edition ?
> > secondly any particular setting to resolve the deadlock issues ?
> >
> >
>
>|||I'm afriad there is no quick fix for deadlocking, there are some traceflags
you can turn on to give you detailed information about the nature of your
deadlock :-
DBCC TRACEON (1204,3605,-1)
This will write deadlock information to the SQL Server Errorlog, which can
be read using sp_ReadErrorLog.
Here's a good article about Anti-Blocking strategies :-
http://vyaskn.tripod.com/anti_blocking_strategies.htm
HTH. Ryan
"Rajeev Rivankar" <RajeevRivankar@.discussions.microsoft.com> wrote in
message news:E5E165E2-E8CB-43D4-8F78-4F1CF3908B8A@.microsoft.com...
> thanks for your feedback.
> I have seen the article on deadlock but any simpler way to handle it.
> like a sp_configure statement
> "Ryan" wrote:
>> To see what version you're on issue the following :-
>> SELECT SERVERPROPERTY('Edition')
>> This article may provide help with your deadlocking :-
>> http://support.microsoft.com/kb/271509/
>> --
>> HTH. Ryan
>>
>> "Rajeev Rivankar" <Rajeev Rivankar@.discussions.microsoft.com> wrote in
>> message news:EEFEE7EC-A947-41F8-A92F-A3626B7A7BA6@.microsoft.com...
>> > we have installed sql server 2000 enterprise edition on our erp server.
>> > We are facing frequent deadlock problem ie one process blocks the other
>> > process frequently.
>> > The compatibility of the databases has been set to 80.
>> > First of all whether the version is that of enterprise edition ?
>> > secondly any particular setting to resolve the deadlock issues ?
>> >
>> >
>>|||thanks
"Ryan" wrote:
> I'm afriad there is no quick fix for deadlocking, there are some traceflags
> you can turn on to give you detailed information about the nature of your
> deadlock :-
> DBCC TRACEON (1204,3605,-1)
> This will write deadlock information to the SQL Server Errorlog, which can
> be read using sp_ReadErrorLog.
> Here's a good article about Anti-Blocking strategies :-
> http://vyaskn.tripod.com/anti_blocking_strategies.htm
>
> --
> HTH. Ryan
>
> "Rajeev Rivankar" <RajeevRivankar@.discussions.microsoft.com> wrote in
> message news:E5E165E2-E8CB-43D4-8F78-4F1CF3908B8A@.microsoft.com...
> > thanks for your feedback.
> >
> > I have seen the article on deadlock but any simpler way to handle it.
> > like a sp_configure statement
> >
> > "Ryan" wrote:
> >
> >> To see what version you're on issue the following :-
> >>
> >> SELECT SERVERPROPERTY('Edition')
> >>
> >> This article may provide help with your deadlocking :-
> >>
> >> http://support.microsoft.com/kb/271509/
> >>
> >> --
> >> HTH. Ryan
> >>
> >>
> >> "Rajeev Rivankar" <Rajeev Rivankar@.discussions.microsoft.com> wrote in
> >> message news:EEFEE7EC-A947-41F8-A92F-A3626B7A7BA6@.microsoft.com...
> >> > we have installed sql server 2000 enterprise edition on our erp server.
> >> > We are facing frequent deadlock problem ie one process blocks the other
> >> > process frequently.
> >> > The compatibility of the databases has been set to 80.
> >> > First of all whether the version is that of enterprise edition ?
> >> > secondly any particular setting to resolve the deadlock issues ?
> >> >
> >> >
> >>
> >>
> >>
>
>sql

deadlock issue in sql server 2000 enterprise edition version 8.00.

we have installed sql server 2000 enterprise edition on our erp server.
We are facing frequent deadlock problem ie one process blocks the other
process frequently.
The compatibility of the databases has been set to 80.
First of all whether the version is that of enterprise edition ?
secondly any particular setting to resolve the deadlock issues ?To see what version you're on issue the following :-
SELECT SERVERPROPERTY('Edition')
This article may provide help with your deadlocking :-
http://support.microsoft.com/kb/271509/
HTH. Ryan
"Rajeev Rivankar" <Rajeev Rivankar@.discussions.microsoft.com> wrote in
message news:EEFEE7EC-A947-41F8-A92F-A3626B7A7BA6@.microsoft.com...
> we have installed sql server 2000 enterprise edition on our erp server.
> We are facing frequent deadlock problem ie one process blocks the other
> process frequently.
> The compatibility of the databases has been set to 80.
> First of all whether the version is that of enterprise edition ?
> secondly any particular setting to resolve the deadlock issues ?
>

Dead lock operation query

If I saw there's a dead lock through the Enterprise Manager's current
activity section, how can I see those processes which have dead lock
(blocking and being blocked) is running what SQL statement? So that I
can know which SQL statement cause the dead lock there.
Usually when I open the deadlock process, I can see something like
'sp_xxxxx' ... it seems to be the store procedure.But the dead lock
should be cause the the SQL statement in the application scripting
portion.
I can kill the process from there, but before doing that, I wish to see
the SQL statement causing that.
Thanks.
Peter CCHHi
sp_who2 will give you information about the processes involved. You may want
to read
http://support.microsoft.com/default.aspx?scid=kb;en-us;224587 and try
sp_blocker_pss80
http://support.microsoft.com/kb/271509/EN-US/
John
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1122191600.869516.130600@.z14g2000cwz.googlegroups.com...
> If I saw there's a dead lock through the Enterprise Manager's current
> activity section, how can I see those processes which have dead lock
> (blocking and being blocked) is running what SQL statement? So that I
> can know which SQL statement cause the dead lock there.
> Usually when I open the deadlock process, I can see something like
> 'sp_xxxxx' ... it seems to be the store procedure.But the dead lock
> should be cause the the SQL statement in the application scripting
> portion.
> I can kill the process from there, but before doing that, I wish to see
> the SQL statement causing that.
>
> Thanks.
>
> Peter CCH
>|||Hi,
To add on to John, Enable trace flag (1204); which will record the deadlock
chain into the error log.
Execute the below command from query analyzer to enable trace flag 1204.
DBCC TRACEON(1204,-1)
Also see the below url to reduce deadlock.
http://www.sql-server-performance.com/deadlocks.asp
Thanks
Hari
SQL Server MVP
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1122191600.869516.130600@.z14g2000cwz.googlegroups.com...
> If I saw there's a dead lock through the Enterprise Manager's current
> activity section, how can I see those processes which have dead lock
> (blocking and being blocked) is running what SQL statement? So that I
> can know which SQL statement cause the dead lock there.
> Usually when I open the deadlock process, I can see something like
> 'sp_xxxxx' ... it seems to be the store procedure.But the dead lock
> should be cause the the SQL statement in the application scripting
> portion.
> I can kill the process from there, but before doing that, I wish to see
> the SQL statement causing that.
>
> Thanks.
>
> Peter CCH
>|||Hari,
Would you know what the "-1" does in "DBCC TRACEON(1204,-1)" ?
Thanks,
Gopi
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23V4GOXFkFHA.3300@.TK2MSFTNGP15.phx.gbl...
> Hi,
> To add on to John, Enable trace flag (1204); which will record the
> deadlock chain into the error log.
> Execute the below command from query analyzer to enable trace flag 1204.
> DBCC TRACEON(1204,-1)
> Also see the below url to reduce deadlock.
> http://www.sql-server-performance.com/deadlocks.asp
> Thanks
> Hari
> SQL Server MVP
>
> "Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
> news:1122191600.869516.130600@.z14g2000cwz.googlegroups.com...
>> If I saw there's a dead lock through the Enterprise Manager's current
>> activity section, how can I see those processes which have dead lock
>> (blocking and being blocked) is running what SQL statement? So that I
>> can know which SQL statement cause the dead lock there.
>> Usually when I open the deadlock process, I can see something like
>> 'sp_xxxxx' ... it seems to be the store procedure.But the dead lock
>> should be cause the the SQL statement in the application scripting
>> portion.
>> I can kill the process from there, but before doing that, I wish to see
>> the SQL statement causing that.
>>
>> Thanks.
>>
>> Peter CCH
>|||The -1 means to enable the flag for ALL connections. Without the -1, it only
enables the flag for the current connection, and connections that have other
trace flags already enabled.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"gopi" <rgopinath@.hotmail.com> wrote in message
news:%23Gne01IkFHA.3144@.TK2MSFTNGP12.phx.gbl...
> Hari,
> Would you know what the "-1" does in "DBCC TRACEON(1204,-1)" ?
> Thanks,
> Gopi
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23V4GOXFkFHA.3300@.TK2MSFTNGP15.phx.gbl...
>> Hi,
>> To add on to John, Enable trace flag (1204); which will record the
>> deadlock chain into the error log.
>> Execute the below command from query analyzer to enable trace flag 1204.
>> DBCC TRACEON(1204,-1)
>> Also see the below url to reduce deadlock.
>> http://www.sql-server-performance.com/deadlocks.asp
>> Thanks
>> Hari
>> SQL Server MVP
>>
>> "Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
>> news:1122191600.869516.130600@.z14g2000cwz.googlegroups.com...
>> If I saw there's a dead lock through the Enterprise Manager's current
>> activity section, how can I see those processes which have dead lock
>> (blocking and being blocked) is running what SQL statement? So that I
>> can know which SQL statement cause the dead lock there.
>> Usually when I open the deadlock process, I can see something like
>> 'sp_xxxxx' ... it seems to be the store procedure.But the dead lock
>> should be cause the the SQL statement in the application scripting
>> portion.
>> I can kill the process from there, but before doing that, I wish to see
>> the SQL statement causing that.
>>
>> Thanks.
>>
>> Peter CCH
>>
>|||We've been using this trace flag with lots of success. You can see in the
SQL error log what DB and the object ids involved (tables & indexes) in the
chain. This makes it "much" easier to find the cause(s) of the deadlock.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23V4GOXFkFHA.3300@.TK2MSFTNGP15.phx.gbl...
> Hi,
> To add on to John, Enable trace flag (1204); which will record the
deadlock
> chain into the error log.
> Execute the below command from query analyzer to enable trace flag 1204.
> DBCC TRACEON(1204,-1)
> Also see the below url to reduce deadlock.
> http://www.sql-server-performance.com/deadlocks.asp
> Thanks
> Hari
> SQL Server MVP
>
> "Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
> news:1122191600.869516.130600@.z14g2000cwz.googlegroups.com...
> > If I saw there's a dead lock through the Enterprise Manager's current
> > activity section, how can I see those processes which have dead lock
> > (blocking and being blocked) is running what SQL statement? So that I
> > can know which SQL statement cause the dead lock there.
> >
> > Usually when I open the deadlock process, I can see something like
> > 'sp_xxxxx' ... it seems to be the store procedure.But the dead lock
> > should be cause the the SQL statement in the application scripting
> > portion.
> >
> > I can kill the process from there, but before doing that, I wish to see
> > the SQL statement causing that.
> >
> >
> > Thanks.
> >
> >
> >
> > Peter CCH
> >
>|||Peter CCH wrote:
> If I saw there's a dead lock through the Enterprise Manager's current
> activity section, how can I see those processes which have dead lock
> (blocking and being blocked) is running what SQL statement? So that I
> can know which SQL statement cause the dead lock there.
> Usually when I open the deadlock process, I can see something like
> 'sp_xxxxx' ... it seems to be the store procedure.But the dead lock
> should be cause the the SQL statement in the application scripting
> portion.
> I can kill the process from there, but before doing that, I wish to
> see the SQL statement causing that.
>
> Thanks.
>
> Peter CCH
Deadlocks and blocking problems are two different things, although they
do share the "blocking" aspect. It sounds like what you are seeing is
blocking problems, not deadlock issues. Deadlocks are quickly resolved
by SQL Server and don't really show up in system tables. They are
normally identified through application error reporting and through
running traces on a server (from Profiler or using the SQL Trace API).
If you are having blocking problems, you need to identify the long
running transactions and figure out why they are taking so long to
complete. It's possible the application is not fetching result sets
quickly enough or not fetching all results to the end of the result set.
It's also possible there are performance problems in the queries that
require tuning.
The best built-in tool to determine these problems is Profiler or,
better yet, using SQL Trace directly from the sp_trace*() functions. You
can have Profiler script a trace for you using the File - Script Trace
menu option.
Look at the SQL:BatchStarting/Completed and RPC:Starting/Completed
events to start with. Examine the CPU, Duration, and Reads columns and
try to get a handle on the high CPU and long running batches.
David Gugick
Quest Software
www.imceda.com
www.quest.com

Dead lock operation query

If I saw there's a dead lock through the Enterprise Manager's current
activity section, how can I see those processes which have dead lock
(blocking and being blocked) is running what SQL statement? So that I
can know which SQL statement cause the dead lock there.
Usually when I open the deadlock process, I can see something like
'sp_xxxxx' ... it seems to be the store procedure.But the dead lock
should be cause the the SQL statement in the application scripting
portion.
I can kill the process from there, but before doing that, I wish to see
the SQL statement causing that.
Thanks.
Peter CCH
Hi
sp_who2 will give you information about the processes involved. You may want
to read
http://support.microsoft.com/default...b;en-us;224587 and try
sp_blocker_pss80
http://support.microsoft.com/kb/271509/EN-US/
John
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1122191600.869516.130600@.z14g2000cwz.googlegr oups.com...
> If I saw there's a dead lock through the Enterprise Manager's current
> activity section, how can I see those processes which have dead lock
> (blocking and being blocked) is running what SQL statement? So that I
> can know which SQL statement cause the dead lock there.
> Usually when I open the deadlock process, I can see something like
> 'sp_xxxxx' ... it seems to be the store procedure.But the dead lock
> should be cause the the SQL statement in the application scripting
> portion.
> I can kill the process from there, but before doing that, I wish to see
> the SQL statement causing that.
>
> Thanks.
>
> Peter CCH
>
|||Hi,
To add on to John, Enable trace flag (1204); which will record the deadlock
chain into the error log.
Execute the below command from query analyzer to enable trace flag 1204.
DBCC TRACEON(1204,-1)
Also see the below url to reduce deadlock.
http://www.sql-server-performance.com/deadlocks.asp
Thanks
Hari
SQL Server MVP
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1122191600.869516.130600@.z14g2000cwz.googlegr oups.com...
> If I saw there's a dead lock through the Enterprise Manager's current
> activity section, how can I see those processes which have dead lock
> (blocking and being blocked) is running what SQL statement? So that I
> can know which SQL statement cause the dead lock there.
> Usually when I open the deadlock process, I can see something like
> 'sp_xxxxx' ... it seems to be the store procedure.But the dead lock
> should be cause the the SQL statement in the application scripting
> portion.
> I can kill the process from there, but before doing that, I wish to see
> the SQL statement causing that.
>
> Thanks.
>
> Peter CCH
>
|||Hari,
Would you know what the "-1" does in "DBCC TRACEON(1204,-1)" ?
Thanks,
Gopi
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23V4GOXFkFHA.3300@.TK2MSFTNGP15.phx.gbl...
> Hi,
> To add on to John, Enable trace flag (1204); which will record the
> deadlock chain into the error log.
> Execute the below command from query analyzer to enable trace flag 1204.
> DBCC TRACEON(1204,-1)
> Also see the below url to reduce deadlock.
> http://www.sql-server-performance.com/deadlocks.asp
> Thanks
> Hari
> SQL Server MVP
>
> "Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
> news:1122191600.869516.130600@.z14g2000cwz.googlegr oups.com...
>
|||The -1 means to enable the flag for ALL connections. Without the -1, it only
enables the flag for the current connection, and connections that have other
trace flags already enabled.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"gopi" <rgopinath@.hotmail.com> wrote in message
news:%23Gne01IkFHA.3144@.TK2MSFTNGP12.phx.gbl...
> Hari,
> Would you know what the "-1" does in "DBCC TRACEON(1204,-1)" ?
> Thanks,
> Gopi
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23V4GOXFkFHA.3300@.TK2MSFTNGP15.phx.gbl...
>
|||We've been using this trace flag with lots of success. You can see in the
SQL error log what DB and the object ids involved (tables & indexes) in the
chain. This makes it "much" easier to find the cause(s) of the deadlock.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23V4GOXFkFHA.3300@.TK2MSFTNGP15.phx.gbl...
> Hi,
> To add on to John, Enable trace flag (1204); which will record the
deadlock
> chain into the error log.
> Execute the below command from query analyzer to enable trace flag 1204.
> DBCC TRACEON(1204,-1)
> Also see the below url to reduce deadlock.
> http://www.sql-server-performance.com/deadlocks.asp
> Thanks
> Hari
> SQL Server MVP
>
> "Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
> news:1122191600.869516.130600@.z14g2000cwz.googlegr oups.com...
>
|||Peter CCH wrote:
> If I saw there's a dead lock through the Enterprise Manager's current
> activity section, how can I see those processes which have dead lock
> (blocking and being blocked) is running what SQL statement? So that I
> can know which SQL statement cause the dead lock there.
> Usually when I open the deadlock process, I can see something like
> 'sp_xxxxx' ... it seems to be the store procedure.But the dead lock
> should be cause the the SQL statement in the application scripting
> portion.
> I can kill the process from there, but before doing that, I wish to
> see the SQL statement causing that.
>
> Thanks.
>
> Peter CCH
Deadlocks and blocking problems are two different things, although they
do share the "blocking" aspect. It sounds like what you are seeing is
blocking problems, not deadlock issues. Deadlocks are quickly resolved
by SQL Server and don't really show up in system tables. They are
normally identified through application error reporting and through
running traces on a server (from Profiler or using the SQL Trace API).
If you are having blocking problems, you need to identify the long
running transactions and figure out why they are taking so long to
complete. It's possible the application is not fetching result sets
quickly enough or not fetching all results to the end of the result set.
It's also possible there are performance problems in the queries that
require tuning.
The best built-in tool to determine these problems is Profiler or,
better yet, using SQL Trace directly from the sp_trace*() functions. You
can have Profiler script a trace for you using the File - Script Trace
menu option.
Look at the SQL:BatchStarting/Completed and RPC:Starting/Completed
events to start with. Examine the CPU, Duration, and Reads columns and
try to get a handle on the high CPU and long running batches.
David Gugick
Quest Software
www.imceda.com
www.quest.com

Dead lock operation query

If I saw there's a dead lock through the Enterprise Manager's current
activity section, how can I see those processes which have dead lock
(blocking and being blocked) is running what SQL statement? So that I
can know which SQL statement cause the dead lock there.
Usually when I open the deadlock process, I can see something like
'sp_xxxxx' ... it seems to be the store procedure.But the dead lock
should be cause the the SQL statement in the application scripting
portion.
I can kill the process from there, but before doing that, I wish to see
the SQL statement causing that.
Thanks.
Peter CCHHi
sp_who2 will give you information about the processes involved. You may want
to read
http://support.microsoft.com/defaul...kb;en-us;224587 and try
sp_blocker_pss80
http://support.microsoft.com/kb/271509/EN-US/
John
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1122191600.869516.130600@.z14g2000cwz.googlegroups.com...
> If I saw there's a dead lock through the Enterprise Manager's current
> activity section, how can I see those processes which have dead lock
> (blocking and being blocked) is running what SQL statement? So that I
> can know which SQL statement cause the dead lock there.
> Usually when I open the deadlock process, I can see something like
> 'sp_xxxxx' ... it seems to be the store procedure.But the dead lock
> should be cause the the SQL statement in the application scripting
> portion.
> I can kill the process from there, but before doing that, I wish to see
> the SQL statement causing that.
>
> Thanks.
>
> Peter CCH
>|||Hi,
To add on to John, Enable trace flag (1204); which will record the deadlock
chain into the error log.
Execute the below command from query analyzer to enable trace flag 1204.
DBCC TRACEON(1204,-1)
Also see the below url to reduce deadlock.
http://www.sql-server-performance.com/deadlocks.asp
Thanks
Hari
SQL Server MVP
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1122191600.869516.130600@.z14g2000cwz.googlegroups.com...
> If I saw there's a dead lock through the Enterprise Manager's current
> activity section, how can I see those processes which have dead lock
> (blocking and being blocked) is running what SQL statement? So that I
> can know which SQL statement cause the dead lock there.
> Usually when I open the deadlock process, I can see something like
> 'sp_xxxxx' ... it seems to be the store procedure.But the dead lock
> should be cause the the SQL statement in the application scripting
> portion.
> I can kill the process from there, but before doing that, I wish to see
> the SQL statement causing that.
>
> Thanks.
>
> Peter CCH
>|||Hari,
Would you know what the "-1" does in "DBCC TRACEON(1204,-1)" ?
Thanks,
Gopi
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23V4GOXFkFHA.3300@.TK2MSFTNGP15.phx.gbl...
> Hi,
> To add on to John, Enable trace flag (1204); which will record the
> deadlock chain into the error log.
> Execute the below command from query analyzer to enable trace flag 1204.
> DBCC TRACEON(1204,-1)
> Also see the below url to reduce deadlock.
> http://www.sql-server-performance.com/deadlocks.asp
> Thanks
> Hari
> SQL Server MVP
>
> "Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
> news:1122191600.869516.130600@.z14g2000cwz.googlegroups.com...
>|||The -1 means to enable the flag for ALL connections. Without the -1, it only
enables the flag for the current connection, and connections that have other
trace flags already enabled.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"gopi" <rgopinath@.hotmail.com> wrote in message
news:%23Gne01IkFHA.3144@.TK2MSFTNGP12.phx.gbl...
> Hari,
> Would you know what the "-1" does in "DBCC TRACEON(1204,-1)" ?
> Thanks,
> Gopi
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23V4GOXFkFHA.3300@.TK2MSFTNGP15.phx.gbl...
>|||We've been using this trace flag with lots of success. You can see in the
SQL error log what DB and the object ids involved (tables & indexes) in the
chain. This makes it "much" easier to find the cause(s) of the deadlock.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23V4GOXFkFHA.3300@.TK2MSFTNGP15.phx.gbl...
> Hi,
> To add on to John, Enable trace flag (1204); which will record the
deadlock
> chain into the error log.
> Execute the below command from query analyzer to enable trace flag 1204.
> DBCC TRACEON(1204,-1)
> Also see the below url to reduce deadlock.
> http://www.sql-server-performance.com/deadlocks.asp
> Thanks
> Hari
> SQL Server MVP
>
> "Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
> news:1122191600.869516.130600@.z14g2000cwz.googlegroups.com...
>|||Peter CCH wrote:
> If I saw there's a dead lock through the Enterprise Manager's current
> activity section, how can I see those processes which have dead lock
> (blocking and being blocked) is running what SQL statement? So that I
> can know which SQL statement cause the dead lock there.
> Usually when I open the deadlock process, I can see something like
> 'sp_xxxxx' ... it seems to be the store procedure.But the dead lock
> should be cause the the SQL statement in the application scripting
> portion.
> I can kill the process from there, but before doing that, I wish to
> see the SQL statement causing that.
>
> Thanks.
>
> Peter CCH
Deadlocks and blocking problems are two different things, although they
do share the "blocking" aspect. It sounds like what you are seeing is
blocking problems, not deadlock issues. Deadlocks are quickly resolved
by SQL Server and don't really show up in system tables. They are
normally identified through application error reporting and through
running traces on a server (from Profiler or using the SQL Trace API).
If you are having blocking problems, you need to identify the long
running transactions and figure out why they are taking so long to
complete. It's possible the application is not fetching result sets
quickly enough or not fetching all results to the end of the result set.
It's also possible there are performance problems in the queries that
require tuning.
The best built-in tool to determine these problems is Profiler or,
better yet, using SQL Trace directly from the sp_trace*() functions. You
can have Profiler script a trace for you using the File - Script Trace
menu option.
Look at the SQL:BatchStarting/Completed and RPC:Starting/Completed
events to start with. Examine the CPU, Duration, and Reads columns and
try to get a handle on the high CPU and long running batches.
David Gugick
Quest Software
www.imceda.com
www.quest.com

dbo's Login Name is blank and can't be edited from Enterprise Manager

We are using SQL Server 2000 SP4. I just created a new database from the EM
but found that in the Users folder under this new db name the Login Name for
dbo was blank. I double-clicked the dbo line and it showed <None> in the
properities dialog box which could not be edited. Is it okay to exe
sp_changedbowner 'sa' sepcially for this new database? Or any better idea?
Thanks,
Eli
> Is it okay to exe sp_changedbowner 'sa' sepcially for this new database?
Yes, sp_changedbowner will fix the database owner. I think it's odd that a
new database would have a NULL owner, though. I usually see that only when
the Windows account that was the database owner is deleted.
Hope this helps.
Dan Guzman
SQL Server MVP
"Eli" <efeng@.kerisys.com> wrote in message
news:%233Pa1LmIIHA.4196@.TK2MSFTNGP04.phx.gbl...
> We are using SQL Server 2000 SP4. I just created a new database from the
> EM
> but found that in the Users folder under this new db name the Login Name
> for
> dbo was blank. I double-clicked the dbo line and it showed <None> in the
> properities dialog box which could not be edited. Is it okay to exe
> sp_changedbowner 'sa' sepcially for this new database? Or any better idea?
> Thanks,
> Eli
>
|||Thanks Dan. It works. Appreciate your meesage.
Regards,
Eli
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:8849D3CB-78AA-42C8-8AA5-9E646991CDFC@.microsoft.com...
> Yes, sp_changedbowner will fix the database owner. I think it's odd that
a
> new database would have a NULL owner, though. I usually see that only
when[vbcol=seagreen]
> the Windows account that was the database owner is deleted.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Eli" <efeng@.kerisys.com> wrote in message
> news:%233Pa1LmIIHA.4196@.TK2MSFTNGP04.phx.gbl...
idea?
>
|||I'm glad I was able to help. Thanks for taking the time to confirm.
Dan Guzman
SQL Server MVP
"Eli" <efeng@.kerisys.com> wrote in message
news:uZ80fLvIIHA.1212@.TK2MSFTNGP05.phx.gbl...
> Thanks Dan. It works. Appreciate your meesage.
> Regards,
> Eli
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:8849D3CB-78AA-42C8-8AA5-9E646991CDFC@.microsoft.com...
> a
> when
> idea?
>

dbo's Login Name is blank and can't be edited from Enterprise Manager

We are using SQL Server 2000 SP4. I just created a new database from the EM
but found that in the Users folder under this new db name the Login Name for
dbo was blank. I double-clicked the dbo line and it showed <None> in the
properities dialog box which could not be edited. Is it okay to exe
sp_changedbowner 'sa' sepcially for this new database? Or any better idea?
Thanks,
Eli> Is it okay to exe sp_changedbowner 'sa' sepcially for this new database?
Yes, sp_changedbowner will fix the database owner. I think it's odd that a
new database would have a NULL owner, though. I usually see that only when
the Windows account that was the database owner is deleted.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Eli" <efeng@.kerisys.com> wrote in message
news:%233Pa1LmIIHA.4196@.TK2MSFTNGP04.phx.gbl...
> We are using SQL Server 2000 SP4. I just created a new database from the
> EM
> but found that in the Users folder under this new db name the Login Name
> for
> dbo was blank. I double-clicked the dbo line and it showed <None> in the
> properities dialog box which could not be edited. Is it okay to exe
> sp_changedbowner 'sa' sepcially for this new database? Or any better idea?
> Thanks,
> Eli
>|||Thanks Dan. It works. Appreciate your meesage.
Regards,
Eli
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:8849D3CB-78AA-42C8-8AA5-9E646991CDFC@.microsoft.com...
> > Is it okay to exe sp_changedbowner 'sa' sepcially for this new database?
> Yes, sp_changedbowner will fix the database owner. I think it's odd that
a
> new database would have a NULL owner, though. I usually see that only
when
> the Windows account that was the database owner is deleted.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Eli" <efeng@.kerisys.com> wrote in message
> news:%233Pa1LmIIHA.4196@.TK2MSFTNGP04.phx.gbl...
> > We are using SQL Server 2000 SP4. I just created a new database from the
> > EM
> > but found that in the Users folder under this new db name the Login Name
> > for
> > dbo was blank. I double-clicked the dbo line and it showed <None> in the
> > properities dialog box which could not be edited. Is it okay to exe
> > sp_changedbowner 'sa' sepcially for this new database? Or any better
idea?
> > Thanks,
> > Eli
> >
> >
>|||I'm glad I was able to help. Thanks for taking the time to confirm.
--
Dan Guzman
SQL Server MVP
"Eli" <efeng@.kerisys.com> wrote in message
news:uZ80fLvIIHA.1212@.TK2MSFTNGP05.phx.gbl...
> Thanks Dan. It works. Appreciate your meesage.
> Regards,
> Eli
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:8849D3CB-78AA-42C8-8AA5-9E646991CDFC@.microsoft.com...
>> > Is it okay to exe sp_changedbowner 'sa' sepcially for this new
>> > database?
>> Yes, sp_changedbowner will fix the database owner. I think it's odd that
> a
>> new database would have a NULL owner, though. I usually see that only
> when
>> the Windows account that was the database owner is deleted.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Eli" <efeng@.kerisys.com> wrote in message
>> news:%233Pa1LmIIHA.4196@.TK2MSFTNGP04.phx.gbl...
>> > We are using SQL Server 2000 SP4. I just created a new database from
>> > the
>> > EM
>> > but found that in the Users folder under this new db name the Login
>> > Name
>> > for
>> > dbo was blank. I double-clicked the dbo line and it showed <None> in
>> > the
>> > properities dialog box which could not be edited. Is it okay to exe
>> > sp_changedbowner 'sa' sepcially for this new database? Or any better
> idea?
>> > Thanks,
>> > Eli
>> >
>> >
>

dbo's Login Name is blank and can't be edited from Enterprise Manager

We are using SQL Server 2000 SP4. I just created a new database from the EM
but found that in the Users folder under this new db name the Login Name for
dbo was blank. I double-clicked the dbo line and it showed <None> in the
properities dialog box which could not be edited. Is it okay to exe
sp_changedbowner 'sa' sepcially for this new database? Or any better idea?
Thanks,
Eli> Is it okay to exe sp_changedbowner 'sa' sepcially for this new database?
Yes, sp_changedbowner will fix the database owner. I think it's odd that a
new database would have a NULL owner, though. I usually see that only when
the Windows account that was the database owner is deleted.
Hope this helps.
Dan Guzman
SQL Server MVP
"Eli" <efeng@.kerisys.com> wrote in message
news:%233Pa1LmIIHA.4196@.TK2MSFTNGP04.phx.gbl...
> We are using SQL Server 2000 SP4. I just created a new database from the
> EM
> but found that in the Users folder under this new db name the Login Name
> for
> dbo was blank. I double-clicked the dbo line and it showed <None> in the
> properities dialog box which could not be edited. Is it okay to exe
> sp_changedbowner 'sa' sepcially for this new database? Or any better idea?
> Thanks,
> Eli
>|||Thanks Dan. It works. Appreciate your meesage.
Regards,
Eli
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:8849D3CB-78AA-42C8-8AA5-9E646991CDFC@.microsoft.com...
> Yes, sp_changedbowner will fix the database owner. I think it's odd that
a
> new database would have a NULL owner, though. I usually see that only
when
> the Windows account that was the database owner is deleted.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Eli" <efeng@.kerisys.com> wrote in message
> news:%233Pa1LmIIHA.4196@.TK2MSFTNGP04.phx.gbl...
idea?[vbcol=seagreen]
>|||I'm glad I was able to help. Thanks for taking the time to confirm.
Dan Guzman
SQL Server MVP
"Eli" <efeng@.kerisys.com> wrote in message
news:uZ80fLvIIHA.1212@.TK2MSFTNGP05.phx.gbl...
> Thanks Dan. It works. Appreciate your meesage.
> Regards,
> Eli
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:8849D3CB-78AA-42C8-8AA5-9E646991CDFC@.microsoft.com...
> a
> when
> idea?
>

dbo prefix on table names in Object Explorer

Hi there,

Is there any way that I can remove the dbo prefix on the table names in the object explorer? In the SQL 2K Enterprise manager, you could click on any table name, type the first few letters of the table you're looking for, and you'd be taken to the general area.

With dbo on the front of the name, it makes it a bit more cumbersome.

Can the dbo bit be reomved somehow?

No, AFAIK it can′t be removed, what for my opinion is missing in here (or I didn′t see it yet) is a grouping for schemas. SQL Server 2k didn′t know about schemas. If you would group on schemas like the fuzzy Oracle Explorer also does you could leave out the schema at the subnode of the schema.

Perhaps you might raise a feature request at connect.microsoft.com , send us the link of the request and we will vote on that :-)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Dbo access does not work.

I apologize for leaving of this important bit of information:
Windows Server 2003 Enterprise Edition SP1. Sql Server 2000 SP 4.
Raymond Lewallen
http://www.codebetter.com/blogs/raymond.lewallen

> I have a domain user account, DOM\User1, who I have granted dbo rights
> to
> DatabaseA, which is on a server who is a member of the domain DOM as
> well.
> User1 can add, remove, alter tables and stored procedures etc, but
> when
> User1 attempts to update, select, insert or delete a row from any
> table in
> DatabaseA, even if it is a table User1 just created, the user is given
> a Select/Update/Insert/Delete Permission Denied error, depending on
> the task.
> The only way to get past the problem is to give DOM\User1 system admin
> rights on the server.
> If I create a Sql Server user, UserSql1, and give that user dbo access
> to DatabaseA and attempt to Select/Update/Insert/Delete, then it works
> just fine for UserSql1. Its only the domain accounts that do not work
> correctly.
> Any ideas on this?
> Raymond Lewallen
> http://www.codebetter.com/blogs/raymond.lewallenHi Raymond
There really isn't anything called 'dbo rights'. DBO is a user name in a
database. You can put another user in the db_owner role, but this doesn't
give them the user name dbo. Can you elaborate on exactly what you granted
to DOM\User1?
Is it possible the Windows user belongs to a Windows group that was given
different access to the server and the database?
What is the value of user_name() when DOM\User1 connects to DatabaseA?
HTH
Kalen Delaney, SQL Server MVP
"Raymond Lewallen" <rlewallen@.gmail.com> wrote in message
news:fffd68f614ddba8c861dae82718bc@.news.microsoft.com...
>I have a domain user account, DOM\User1, who I have granted dbo rights to
>DatabaseA, which is on a server who is a member of the domain DOM as well.
>User1 can add, remove, alter tables and stored procedures etc, but when
>User1 attempts to update, select, insert or delete a row from any table in
>DatabaseA, even if it is a table User1 just created, the user is given a
>Select/Update/Insert/Delete Permission Denied error, depending on the task.
> The only way to get past the problem is to give DOM\User1 system admin
> rights on the server.
> If I create a Sql Server user, UserSql1, and give that user dbo access to
> DatabaseA and attempt to Select/Update/Insert/Delete, then it works just
> fine for UserSql1. Its only the domain accounts that do not work
> correctly.
> Any ideas on this?
> Raymond Lewallen
> http://www.codebetter.com/blogs/raymond.lewallen
>|||I have a domain user account, DOM\User1, who I have granted dbo rights to
DatabaseA, which is on a server who is a member of the domain DOM as well.
User1 can add, remove, alter tables and stored procedures etc, but when
User1 attempts to update, select, insert or delete a row from any table in
DatabaseA, even if it is a table User1 just created, the user is given a
Select/Update/Insert/Delete Permission Denied error, depending on the task.
The only way to get past the problem is to give DOM\User1 system admin right
s
on the server.
If I create a Sql Server user, UserSql1, and give that user dbo access to
DatabaseA and attempt to Select/Update/Insert/Delete, then it works just
fine for UserSql1. Its only the domain accounts that do not work correctly.
Any ideas on this?
Raymond Lewallen
http://www.codebetter.com/blogs/raymond.lewallen|||I apologize for leaving of this important bit of information:
Windows Server 2003 Enterprise Edition SP1. Sql Server 2000 SP 4.
Raymond Lewallen
http://www.codebetter.com/blogs/raymond.lewallen

> I have a domain user account, DOM\User1, who I have granted dbo rights
> to
> DatabaseA, which is on a server who is a member of the domain DOM as
> well.
> User1 can add, remove, alter tables and stored procedures etc, but
> when
> User1 attempts to update, select, insert or delete a row from any
> table in
> DatabaseA, even if it is a table User1 just created, the user is given
> a Select/Update/Insert/Delete Permission Denied error, depending on
> the task.
> The only way to get past the problem is to give DOM\User1 system admin
> rights on the server.
> If I create a Sql Server user, UserSql1, and give that user dbo access
> to DatabaseA and attempt to Select/Update/Insert/Delete, then it works
> just fine for UserSql1. Its only the domain accounts that do not work
> correctly.
> Any ideas on this?
> Raymond Lewallen
> http://www.codebetter.com/blogs/raymond.lewallen|||Hi Raymond
There really isn't anything called 'dbo rights'. DBO is a user name in a
database. You can put another user in the db_owner role, but this doesn't
give them the user name dbo. Can you elaborate on exactly what you granted
to DOM\User1?
Is it possible the Windows user belongs to a Windows group that was given
different access to the server and the database?
What is the value of user_name() when DOM\User1 connects to DatabaseA?
HTH
Kalen Delaney, SQL Server MVP
"Raymond Lewallen" <rlewallen@.gmail.com> wrote in message
news:fffd68f614ddba8c861dae82718bc@.news.microsoft.com...
>I have a domain user account, DOM\User1, who I have granted dbo rights to
>DatabaseA, which is on a server who is a member of the domain DOM as well.
>User1 can add, remove, alter tables and stored procedures etc, but when
>User1 attempts to update, select, insert or delete a row from any table in
>DatabaseA, even if it is a table User1 just created, the user is given a
>Select/Update/Insert/Delete Permission Denied error, depending on the task.
> The only way to get past the problem is to give DOM\User1 system admin
> rights on the server.
> If I create a Sql Server user, UserSql1, and give that user dbo access to
> DatabaseA and attempt to Select/Update/Insert/Delete, then it works just
> fine for UserSql1. Its only the domain accounts that do not work
> correctly.
> Any ideas on this?
> Raymond Lewallen
> http://www.codebetter.com/blogs/raymond.lewallen
>|||Raymond Lewallen wrote:
> I have a domain user account, DOM\User1, who I have granted dbo rights
> to DatabaseA, which is on a server who is a member of the domain DOM as
> well. User1 can add, remove, alter tables and stored procedures etc, but
> when User1 attempts to update, select, insert or delete a row from any
> table in DatabaseA, even if it is a table User1 just created, the user
> is given a Select/Update/Insert/Delete Permission Denied error,
> depending on the task.
> The only way to get past the problem is to give DOM\User1 system admin
> rights on the server.
> If I create a Sql Server user, UserSql1, and give that user dbo access
> to DatabaseA and attempt to Select/Update/Insert/Delete, then it works
> just fine for UserSql1. Its only the domain accounts that do not work
> correctly.
> Any ideas on this?
> Raymond Lewallen
> http://www.codebetter.com/blogs/raymond.lewallen
>
Have you explicitly DENIED access to any particular domain groups? Does
DOM\User1 belong to one of those groups?|||Raymond Lewallen wrote:
> I have a domain user account, DOM\User1, who I have granted dbo rights
> to DatabaseA, which is on a server who is a member of the domain DOM as
> well. User1 can add, remove, alter tables and stored procedures etc, but
> when User1 attempts to update, select, insert or delete a row from any
> table in DatabaseA, even if it is a table User1 just created, the user
> is given a Select/Update/Insert/Delete Permission Denied error,
> depending on the task.
> The only way to get past the problem is to give DOM\User1 system admin
> rights on the server.
> If I create a Sql Server user, UserSql1, and give that user dbo access
> to DatabaseA and attempt to Select/Update/Insert/Delete, then it works
> just fine for UserSql1. Its only the domain accounts that do not work
> correctly.
> Any ideas on this?
> Raymond Lewallen
> http://www.codebetter.com/blogs/raymond.lewallen
>
Have you explicitly DENIED access to any particular domain groups? Does
DOM\User1 belong to one of those groups?|||Hello Kalen,
db_owner role is the group the domain account has been assigned access to.
Sorry for the confusion there, in the sql circles I've been in over the
last 10 years, 'dbo rights' have always been understood as the db_owner grou
p.
No windows groups other than BUILTIN\Administrators have been given any expl
icit
rights, and the admins have sa rights.
The value of user_name is DOM\User1 when the user connects.
Raymond Lewallen
http://www.codebetter.com/blogs/raymond.lewallen
[vbcol=seagreen]
> Hi Raymond
> There really isn't anything called 'dbo rights'. DBO is a user name in
> a database. You can put another user in the db_owner role, but this
> doesn't give them the user name dbo. Can you elaborate on exactly what
> you granted to DOM\User1?
> Is it possible the Windows user belongs to a Windows group that was
> given different access to the server and the database?
> What is the value of user_name() when DOM\User1 connects to DatabaseA?
> "Raymond Lewallen" <rlewallen@.gmail.com> wrote in message
> news:fffd68f614ddba8c861dae82718bc@.news.microsoft.com...
>|||Hello Tracy,
No windows groups have been given any rights, whether access or deny, to
the sql server or any of its databases. The only windows group on the entir
e
server is BUILTIN\Administrators, which has sa rights.
Raymond Lewallen
http://www.codebetter.com/blogs/raymond.lewallen

> Raymond Lewallen wrote:
>
> Have you explicitly DENIED access to any particular domain groups?
> Does DOM\User1 belong to one of those groups?
>|||Hello Tracy,
No windows groups have been given any rights, whether access or deny, to
the sql server or any of its databases. The only windows group on the entir
e
server is BUILTIN\Administrators, which has sa rights.
Raymond Lewallen
http://www.codebetter.com/blogs/raymond.lewallen

> Raymond Lewallen wrote:
>
> Have you explicitly DENIED access to any particular domain groups?
> Does DOM\User1 belong to one of those groups?
>