Deadlock Manager

In what appears to be a deadlock scenario, why doesn’t the deadlock manage
r engage and determine a victim?
Here is an example of what I am talking about:
Query 1
Select statement on table "A" with a suitable where clause to define only th
e records desired.
Query 2
Multiple insert statements within a single transaction to several different
tables including table "A."
The resulting scenario is that query 2 has crossed the magic threshold of 12
50 locks and therefore escalates its lock to a table lock. Query 1, which is
now blocked, also escalates to a table lock. The two queries then sit aroun
d waiting for each other to
finish, but they never do.
Here are my questions:
The lock manager will begin escalation if a single resource is using more th
an 1250 locks on table resources, or 765 locks on index resources. If the ma
chine in question has 2GB of physical memory, how big is the memory pool for
locks?
Even though this isn't the classic deadlock scenario, why doesn't the deadlo
ck manager recognize this as a deadlock and choose a victim to help free up
the resources?
Thanks.> The lock manager will begin escalation if a single resource is using more
than 1250 locks on table resources, or 765 locks on index resources. If the
machine in question has 2GB of physical memory, how big is the memory pool
for locks?
>
If default not changed, it is 40% of memory allocated.

> Even though this isn't the classic deadlock scenario, why doesn't the
deadlock manager recognize this as a deadlock and choose a victim to help
free up the resources?
>
Since your situation is not a deadlock (it is a block), it will try to
finish the first (blocking) process. Deadlock does not have any hope of
finishing, and therefore sql server has the mechanism of getting itself out
of it.
Quentin

Deadlock Manager

In what appears to be a deadlock scenario, why doesn’t the deadlock manager engage and determine a victim?
Here is an example of what I am talking about:
Query 1
Select statement on table "A" with a suitable where clause to define only the records desired.
Query 2
Multiple insert statements within a single transaction to several different tables including table "A."
The resulting scenario is that query 2 has crossed the magic threshold of 1250 locks and therefore escalates its lock to a table lock. Query 1, which is now blocked, also escalates to a table lock. The two queries then sit around waiting for each other to
finish, but they never do.
Here are my questions:
The lock manager will begin escalation if a single resource is using more than 1250 locks on table resources, or 765 locks on index resources. If the machine in question has 2GB of physical memory, how big is the memory pool for locks?
Even though this isn't the classic deadlock scenario, why doesn't the deadlock manager recognize this as a deadlock and choose a victim to help free up the resources?
Thanks.
> The lock manager will begin escalation if a single resource is using more
than 1250 locks on table resources, or 765 locks on index resources. If the
machine in question has 2GB of physical memory, how big is the memory pool
for locks?
>
If default not changed, it is 40% of memory allocated.

> Even though this isn't the classic deadlock scenario, why doesn't the
deadlock manager recognize this as a deadlock and choose a victim to help
free up the resources?
>
Since your situation is not a deadlock (it is a block), it will try to
finish the first (blocking) process. Deadlock does not have any hope of
finishing, and therefore sql server has the mechanism of getting itself out
of it.
Quentin

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 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

Dead links

Hi.
In report manager, links to Home, My subscription, Site settings, Help,
Contents, Properties works fine.
But links to New Folder, New Data source and Upload files are completly
dead - what could be wrong?
I'v installed evalution edition incl. SP1.
Best
LABProblem solved, it was a matter of browser-sequrity.
/LAB
"LAB" <nomail@.nomail.dk> skrev i en meddelelse
news:#NLIMzXnEHA.2680@.TK2MSFTNGP15.phx.gbl...
> Hi.
> In report manager, links to Home, My subscription, Site settings, Help,
> Contents, Properties works fine.
> But links to New Folder, New Data source and Upload files are completly
> dead - what could be wrong?
> I'v installed evalution edition incl. SP1.
> Best
> LAB
>

DDL Primer?

Ent Manager is nice, but I need to make some DDL changes to a database
(convert a column from char to varchar, add a new column, etc) with
SQL. Is there a good primer for this? I'm sure it is not too hard, I
just have not had to do this. Thanks.
-JohnElementary cases are well presented in Books Online.
E.g. (to alter a table and/or columns):
http://msdn.microsoft.com/library/d...br />
3ied.asp
ML
http://milambda.blogspot.com/|||...and use Query Analyzer. I forgot to mention.
ML
http://milambda.blogspot.com/|||"John Baima" <john@.nospam.com> wrote in message
news:f17gq1l0e6cted806ef0ub0ab3tflqmlt4@.
4ax.com...
> Ent Manager is nice, but I need to make some DDL changes to a database
> (convert a column from char to varchar, add a new column, etc) with
> SQL. Is there a good primer for this? I'm sure it is not too hard, I
> just have not had to do this. Thanks.
> -John
EM is "nice" but does a lot of "unneccessary" stuff behind the scenes.
When you change a column from Char to Varchar, it actually:
-creates a new table with the new DDL
-copies the data from the original to the new table
-drops the old table
-renames the new table
-drops and recreates constaints
So do you your charges in Query Analyser, like ML suggests.|||Plus - QA won't "assist" you in "clicking up" a potential disaster. :)
ML
http://milambda.blogspot.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