We use SQL Server 2000 SP4 with merge replication enabled. The master site
replicates all changes to at multiple subscribers, but we encounter some
problems due to deadlocking issues.
Some actions produce a lot of new data that must be replicated. The table,
in which the data is inserted, has a trigger defined that must be enabled for
replication. Inserting a record into this table takes 70-350ms during normal
operation. It goes through several complex calculations that have been
optimized pretty well.
When the merge agent starts replicating it often receives a deadlock when
inserting data in these tables. After this deadlock it becomes very slow. It
enlists all further records for retrying (Unable to synchronize row due to
unknown reason) and inserts that do get through have durations of over
35.000ms! Of course, this severly hurts replication performance.
I think the triggers for replication are the real probleme due to locking
issues. These calculations need to be performed even when data is replicated
and I don't know another method then using a trigger. Does anyone have a
suggestion?
Greetings,
Ramon de Klein
The replication triggers do cause increased latency of operations on
replicated tables.
Do you have real time requirements for the data that these triggers
calculate? You may want to evaluate having this calculation being performed
in a batch.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ramon de Klein" <RamondeKlein@.discussions.microsoft.com> wrote in message
news:2820F1E2-B4B2-4140-BABC-26DC753D69EB@.microsoft.com...
> We use SQL Server 2000 SP4 with merge replication enabled. The master site
> replicates all changes to at multiple subscribers, but we encounter some
> problems due to deadlocking issues.
> Some actions produce a lot of new data that must be replicated. The table,
> in which the data is inserted, has a trigger defined that must be enabled
> for
> replication. Inserting a record into this table takes 70-350ms during
> normal
> operation. It goes through several complex calculations that have been
> optimized pretty well.
> When the merge agent starts replicating it often receives a deadlock when
> inserting data in these tables. After this deadlock it becomes very slow.
> It
> enlists all further records for retrying (Unable to synchronize row due to
> unknown reason) and inserts that do get through have durations of over
> 35.000ms! Of course, this severly hurts replication performance.
> I think the triggers for replication are the real probleme due to locking
> issues. These calculations need to be performed even when data is
> replicated
> and I don't know another method then using a trigger. Does anyone have a
> suggestion?
> --
> Greetings,
> Ramon de Klein
sql
Showing posts with label sp4. Show all posts
Showing posts with label sp4. Show all posts
Thursday, March 29, 2012
Tuesday, March 27, 2012
Deadlocks -- sql server 2000
Any help with deadlocks ?
I keep getting deadlocks..and can't figure out why...
version of sql server: 8.00.2040 (SP4).
[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction (Process ID 73) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
You might find this previous thread on deadlocks to be helpful:http://forums.asp.net/982968/ShowPost.aspx
Sunday, March 25, 2012
deadlock with replication's update/insert/delete process
We are using SQL 2K with sp4. We are using Push method and both publisher
and distribution db are on the same server.
Recently we have quite a few deadlocks involving a replication's sp such as
sp_MSupd_xxxxxxx and some select statement from our application. We have
deadlocks from time to time but I haven't seen a deadlock caused by sp_MSupd
until now. Is this an indication of something that I need to look into?
Any help is very much appreciated.
Wingman
Sounds like a transaction on the publisher is coming over as a transaction
on the subscriber which you're deadlocking with. Some ideas: you could look
at the order of the transaction on the publisher and check that your
application accesses tables in the same order. You could allow your
application to do dirty reads (not generally recommended but sometimes
useful). You could use SQL Server 2005 and the snapshot isolation level
where this problem goes away. Finally you could control when the
distribution agent runs to avoid the lcash.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
and distribution db are on the same server.
Recently we have quite a few deadlocks involving a replication's sp such as
sp_MSupd_xxxxxxx and some select statement from our application. We have
deadlocks from time to time but I haven't seen a deadlock caused by sp_MSupd
until now. Is this an indication of something that I need to look into?
Any help is very much appreciated.
Wingman
Sounds like a transaction on the publisher is coming over as a transaction
on the subscriber which you're deadlocking with. Some ideas: you could look
at the order of the transaction on the publisher and check that your
application accesses tables in the same order. You could allow your
application to do dirty reads (not generally recommended but sometimes
useful). You could use SQL Server 2005 and the snapshot isolation level
where this problem goes away. Finally you could control when the
distribution agent runs to avoid the lcash.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
Deadlock using 'INSERT BULK'
Using the new System.Data.SqlClient.SqlBulkCopy class in .Net Framework
2.0 I'm inserting some 100 rows into a SQL Server 2000 table (sp4
applied). The SqlBulkCopy class is used within a
System.Transactions.TransactionScope, so MSDTC is implicitely used as a
transactionmanager.
The problem is that a a deadlock occurs the command is issued, and I'm
having problems understanding why this deadlock happens. The -T1204
output is:
Deadlock encountered ... Printing deadlock information
2006-11-22 15:23:56.78 spid3
2006-11-22 15:23:56.78 spid3 Wait-for graph
2006-11-22 15:23:56.78 spid3
2006-11-22 15:23:56.78 spid3 Node:1
2006-11-22 15:23:56.78 spid3 TAB: 8:565577053 []
CleanCnt:2 Mode: Schema Flags: 0x0
2006-11-22 15:23:56.78 spid3 Grant List 0::
2006-11-22 15:23:56.78 spid3 Owner:0x1ef77980 Mode: Schema
Flg:0x0 Ref:0 Life:02000000 SPID:77 ECID:0
2006-11-22 15:23:56.78 spid3 SPID: 77 ECID: 0 Statement Type:
UNKNOWN TOKEN Line #: 1
2006-11-22 15:23:56.78 spid3 Input Buf: Language Event: insert
bulk TableName ([Field1] Int, [Field2] DateTime, [Field3] DateTime,
[Field4] Decimal(28,13), [Field4] Bit)
2006-11-22 15:23:56.78 spid3 Requested By:
2006-11-22 15:23:56.78 spid3 ResType:LockOwner Stype:'OR' Mode:
Schema-Mod SPID:77 ECID:0 Ec:(0x3660BA30) Value:0x1ef77880 Cost:(0/0)
2006-11-22 15:23:56.78 spid3 Victim Resource Owner:
2006-11-22 15:23:56.78 spid3 ResType:LockOwner Stype:'OR' Mode:
Schema-Mod SPID:77 ECID:0 Ec:(0x3660BA30) Value:0x1ef77880 Cost:(0/0)
The "TAB: 8:565577053" refers to the TableName specified in the Input
Buf.
This problem is reproducable (it happens with multiple tables).
Does anyone have a hint on how to tackle this problem?
Regards,
Tjibbe Chris<tjibbechris@.gmail.com> wrote in message
news:1164206482.527864.15760@.e3g2000cwe.googlegroups.com...
> Using the new System.Data.SqlClient.SqlBulkCopy class in .Net Framework
> 2.0 I'm inserting some 100 rows into a SQL Server 2000 table (sp4
> applied). The SqlBulkCopy class is used within a
> System.Transactions.TransactionScope, so MSDTC is implicitely used as a
> transactionmanager.
> The problem is that a a deadlock occurs the command is issued, and I'm
> having problems understanding why this deadlock happens. The -T1204
> output is:
> Deadlock encountered ... Printing deadlock information
> 2006-11-22 15:23:56.78 spid3
> 2006-11-22 15:23:56.78 spid3 Wait-for graph
> 2006-11-22 15:23:56.78 spid3
> 2006-11-22 15:23:56.78 spid3 Node:1
> 2006-11-22 15:23:56.78 spid3 TAB: 8:565577053 []
> CleanCnt:2 Mode: Schema Flags: 0x0
> 2006-11-22 15:23:56.78 spid3 Grant List 0::
> 2006-11-22 15:23:56.78 spid3 Owner:0x1ef77980 Mode: Schema
> Flg:0x0 Ref:0 Life:02000000 SPID:77 ECID:0
> 2006-11-22 15:23:56.78 spid3 SPID: 77 ECID: 0 Statement Type:
> UNKNOWN TOKEN Line #: 1
> 2006-11-22 15:23:56.78 spid3 Input Buf: Language Event: insert
> bulk TableName ([Field1] Int, [Field2] DateTime, [Field3] DateTime,
> [Field4] Decimal(28,13), [Field4] Bit)
> 2006-11-22 15:23:56.78 spid3 Requested By:
> 2006-11-22 15:23:56.78 spid3 ResType:LockOwner Stype:'OR' Mode:
> Schema-Mod SPID:77 ECID:0 Ec:(0x3660BA30) Value:0x1ef77880 Cost:(0/0)
> 2006-11-22 15:23:56.78 spid3 Victim Resource Owner:
> 2006-11-22 15:23:56.78 spid3 ResType:LockOwner Stype:'OR' Mode:
> Schema-Mod SPID:77 ECID:0 Ec:(0x3660BA30) Value:0x1ef77880 Cost:(0/0)
> The "TAB: 8:565577053" refers to the TableName specified in the Input
> Buf.
> This problem is reproducable (it happens with multiple tables).
> Does anyone have a hint on how to tackle this problem?
>
Are you specifying SqlBulkCopyOptions.TableLock in the SqlBulkCopy
constructor?
Do you have any other work on other connection to SQL Server enlisted in the
transaction? If so try to do that work on the _same_ connection that
SqlBulkCopy is using.
SQL 2000 doesn't play as nice with System.Transactions as SQL 2005. see
http://blogs.msdn.com/florinlazar/archive/2005/09/29/475546.aspx
for a discussion and some possible workarounds.
David|||Thanks for your quick reply.
I've tried using the tablelock option, but it didn't make any
difference.
There are more statements issued prior to the bulkcopy, all regular
inserts on other tables (using stored procs). Whe're using the
DataAccessApplication block, so I've got no influence on using the same
connection although it does use the same connection for all commands
within the transaction (as seen with SQL Profiler).
I'll look into the link you've presented.
tjibbe chris
On 22 nov, 17:03, "David Browne" <davidbaxterbrowne no potted
m...@.hotmail.com> wrote:
> <tjibbech...@.gmail.com> wrote in messagenews:1164206482.527864.15760@.e3g2000cwe.googlegroups.com...
>
>
> > Using the new System.Data.SqlClient.SqlBulkCopy class in .Net Framework
> > 2.0 I'm inserting some 100 rows into a SQL Server 2000 table (sp4
> > applied). The SqlBulkCopy class is used within a
> > System.Transactions.TransactionScope, so MSDTC is implicitely used as a
> > transactionmanager.
> > The problem is that a a deadlock occurs the command is issued, and I'm
> > having problems understanding why this deadlock happens. The -T1204
> > output is:
> > Deadlock encountered ... Printing deadlock information
> > 2006-11-22 15:23:56.78 spid3
> > 2006-11-22 15:23:56.78 spid3 Wait-for graph
> > 2006-11-22 15:23:56.78 spid3
> > 2006-11-22 15:23:56.78 spid3 Node:1
> > 2006-11-22 15:23:56.78 spid3 TAB: 8:565577053 []
> > CleanCnt:2 Mode: Schema Flags: 0x0
> > 2006-11-22 15:23:56.78 spid3 Grant List 0::
> > 2006-11-22 15:23:56.78 spid3 Owner:0x1ef77980 Mode: Schema
> > Flg:0x0 Ref:0 Life:02000000 SPID:77 ECID:0
> > 2006-11-22 15:23:56.78 spid3 SPID: 77 ECID: 0 Statement Type:
> > UNKNOWN TOKEN Line #: 1
> > 2006-11-22 15:23:56.78 spid3 Input Buf: Language Event: insert
> > bulk TableName ([Field1] Int, [Field2] DateTime, [Field3] DateTime,
> > [Field4] Decimal(28,13), [Field4] Bit)
> > 2006-11-22 15:23:56.78 spid3 Requested By:
> > 2006-11-22 15:23:56.78 spid3 ResType:LockOwner Stype:'OR' Mode:
> > Schema-Mod SPID:77 ECID:0 Ec:(0x3660BA30) Value:0x1ef77880 Cost:(0/0)
> > 2006-11-22 15:23:56.78 spid3 Victim Resource Owner:
> > 2006-11-22 15:23:56.78 spid3 ResType:LockOwner Stype:'OR' Mode:
> > Schema-Mod SPID:77 ECID:0 Ec:(0x3660BA30) Value:0x1ef77880 Cost:(0/0)
> > The "TAB: 8:565577053" refers to the TableName specified in the Input
> > Buf.
> > This problem is reproducable (it happens with multiple tables).
> > Does anyone have a hint on how to tackle this problem?Are you specifying SqlBulkCopyOptions.TableLock in the SqlBulkCopy
> constructor?
> Do you have any other work on other connection to SQL Server enlisted in the
> transaction? If so try to do that work on the _same_ connection that
> SqlBulkCopy is using.
> SQL 2000 doesn't play as nice with System.Transactions as SQL 2005. seehttp://blogs.msdn.com/florinlazar/archive/2005/09/29/475546.aspx
> for a discussion and some possible workarounds.
> David- Tekst uit oorspronkelijk bericht niet weergeven -- Tekst uit oorspronkelijk bericht weergeven -
2.0 I'm inserting some 100 rows into a SQL Server 2000 table (sp4
applied). The SqlBulkCopy class is used within a
System.Transactions.TransactionScope, so MSDTC is implicitely used as a
transactionmanager.
The problem is that a a deadlock occurs the command is issued, and I'm
having problems understanding why this deadlock happens. The -T1204
output is:
Deadlock encountered ... Printing deadlock information
2006-11-22 15:23:56.78 spid3
2006-11-22 15:23:56.78 spid3 Wait-for graph
2006-11-22 15:23:56.78 spid3
2006-11-22 15:23:56.78 spid3 Node:1
2006-11-22 15:23:56.78 spid3 TAB: 8:565577053 []
CleanCnt:2 Mode: Schema Flags: 0x0
2006-11-22 15:23:56.78 spid3 Grant List 0::
2006-11-22 15:23:56.78 spid3 Owner:0x1ef77980 Mode: Schema
Flg:0x0 Ref:0 Life:02000000 SPID:77 ECID:0
2006-11-22 15:23:56.78 spid3 SPID: 77 ECID: 0 Statement Type:
UNKNOWN TOKEN Line #: 1
2006-11-22 15:23:56.78 spid3 Input Buf: Language Event: insert
bulk TableName ([Field1] Int, [Field2] DateTime, [Field3] DateTime,
[Field4] Decimal(28,13), [Field4] Bit)
2006-11-22 15:23:56.78 spid3 Requested By:
2006-11-22 15:23:56.78 spid3 ResType:LockOwner Stype:'OR' Mode:
Schema-Mod SPID:77 ECID:0 Ec:(0x3660BA30) Value:0x1ef77880 Cost:(0/0)
2006-11-22 15:23:56.78 spid3 Victim Resource Owner:
2006-11-22 15:23:56.78 spid3 ResType:LockOwner Stype:'OR' Mode:
Schema-Mod SPID:77 ECID:0 Ec:(0x3660BA30) Value:0x1ef77880 Cost:(0/0)
The "TAB: 8:565577053" refers to the TableName specified in the Input
Buf.
This problem is reproducable (it happens with multiple tables).
Does anyone have a hint on how to tackle this problem?
Regards,
Tjibbe Chris<tjibbechris@.gmail.com> wrote in message
news:1164206482.527864.15760@.e3g2000cwe.googlegroups.com...
> Using the new System.Data.SqlClient.SqlBulkCopy class in .Net Framework
> 2.0 I'm inserting some 100 rows into a SQL Server 2000 table (sp4
> applied). The SqlBulkCopy class is used within a
> System.Transactions.TransactionScope, so MSDTC is implicitely used as a
> transactionmanager.
> The problem is that a a deadlock occurs the command is issued, and I'm
> having problems understanding why this deadlock happens. The -T1204
> output is:
> Deadlock encountered ... Printing deadlock information
> 2006-11-22 15:23:56.78 spid3
> 2006-11-22 15:23:56.78 spid3 Wait-for graph
> 2006-11-22 15:23:56.78 spid3
> 2006-11-22 15:23:56.78 spid3 Node:1
> 2006-11-22 15:23:56.78 spid3 TAB: 8:565577053 []
> CleanCnt:2 Mode: Schema Flags: 0x0
> 2006-11-22 15:23:56.78 spid3 Grant List 0::
> 2006-11-22 15:23:56.78 spid3 Owner:0x1ef77980 Mode: Schema
> Flg:0x0 Ref:0 Life:02000000 SPID:77 ECID:0
> 2006-11-22 15:23:56.78 spid3 SPID: 77 ECID: 0 Statement Type:
> UNKNOWN TOKEN Line #: 1
> 2006-11-22 15:23:56.78 spid3 Input Buf: Language Event: insert
> bulk TableName ([Field1] Int, [Field2] DateTime, [Field3] DateTime,
> [Field4] Decimal(28,13), [Field4] Bit)
> 2006-11-22 15:23:56.78 spid3 Requested By:
> 2006-11-22 15:23:56.78 spid3 ResType:LockOwner Stype:'OR' Mode:
> Schema-Mod SPID:77 ECID:0 Ec:(0x3660BA30) Value:0x1ef77880 Cost:(0/0)
> 2006-11-22 15:23:56.78 spid3 Victim Resource Owner:
> 2006-11-22 15:23:56.78 spid3 ResType:LockOwner Stype:'OR' Mode:
> Schema-Mod SPID:77 ECID:0 Ec:(0x3660BA30) Value:0x1ef77880 Cost:(0/0)
> The "TAB: 8:565577053" refers to the TableName specified in the Input
> Buf.
> This problem is reproducable (it happens with multiple tables).
> Does anyone have a hint on how to tackle this problem?
>
Are you specifying SqlBulkCopyOptions.TableLock in the SqlBulkCopy
constructor?
Do you have any other work on other connection to SQL Server enlisted in the
transaction? If so try to do that work on the _same_ connection that
SqlBulkCopy is using.
SQL 2000 doesn't play as nice with System.Transactions as SQL 2005. see
http://blogs.msdn.com/florinlazar/archive/2005/09/29/475546.aspx
for a discussion and some possible workarounds.
David|||Thanks for your quick reply.
I've tried using the tablelock option, but it didn't make any
difference.
There are more statements issued prior to the bulkcopy, all regular
inserts on other tables (using stored procs). Whe're using the
DataAccessApplication block, so I've got no influence on using the same
connection although it does use the same connection for all commands
within the transaction (as seen with SQL Profiler).
I'll look into the link you've presented.
tjibbe chris
On 22 nov, 17:03, "David Browne" <davidbaxterbrowne no potted
m...@.hotmail.com> wrote:
> <tjibbech...@.gmail.com> wrote in messagenews:1164206482.527864.15760@.e3g2000cwe.googlegroups.com...
>
>
> > Using the new System.Data.SqlClient.SqlBulkCopy class in .Net Framework
> > 2.0 I'm inserting some 100 rows into a SQL Server 2000 table (sp4
> > applied). The SqlBulkCopy class is used within a
> > System.Transactions.TransactionScope, so MSDTC is implicitely used as a
> > transactionmanager.
> > The problem is that a a deadlock occurs the command is issued, and I'm
> > having problems understanding why this deadlock happens. The -T1204
> > output is:
> > Deadlock encountered ... Printing deadlock information
> > 2006-11-22 15:23:56.78 spid3
> > 2006-11-22 15:23:56.78 spid3 Wait-for graph
> > 2006-11-22 15:23:56.78 spid3
> > 2006-11-22 15:23:56.78 spid3 Node:1
> > 2006-11-22 15:23:56.78 spid3 TAB: 8:565577053 []
> > CleanCnt:2 Mode: Schema Flags: 0x0
> > 2006-11-22 15:23:56.78 spid3 Grant List 0::
> > 2006-11-22 15:23:56.78 spid3 Owner:0x1ef77980 Mode: Schema
> > Flg:0x0 Ref:0 Life:02000000 SPID:77 ECID:0
> > 2006-11-22 15:23:56.78 spid3 SPID: 77 ECID: 0 Statement Type:
> > UNKNOWN TOKEN Line #: 1
> > 2006-11-22 15:23:56.78 spid3 Input Buf: Language Event: insert
> > bulk TableName ([Field1] Int, [Field2] DateTime, [Field3] DateTime,
> > [Field4] Decimal(28,13), [Field4] Bit)
> > 2006-11-22 15:23:56.78 spid3 Requested By:
> > 2006-11-22 15:23:56.78 spid3 ResType:LockOwner Stype:'OR' Mode:
> > Schema-Mod SPID:77 ECID:0 Ec:(0x3660BA30) Value:0x1ef77880 Cost:(0/0)
> > 2006-11-22 15:23:56.78 spid3 Victim Resource Owner:
> > 2006-11-22 15:23:56.78 spid3 ResType:LockOwner Stype:'OR' Mode:
> > Schema-Mod SPID:77 ECID:0 Ec:(0x3660BA30) Value:0x1ef77880 Cost:(0/0)
> > The "TAB: 8:565577053" refers to the TableName specified in the Input
> > Buf.
> > This problem is reproducable (it happens with multiple tables).
> > Does anyone have a hint on how to tackle this problem?Are you specifying SqlBulkCopyOptions.TableLock in the SqlBulkCopy
> constructor?
> Do you have any other work on other connection to SQL Server enlisted in the
> transaction? If so try to do that work on the _same_ connection that
> SqlBulkCopy is using.
> SQL 2000 doesn't play as nice with System.Transactions as SQL 2005. seehttp://blogs.msdn.com/florinlazar/archive/2005/09/29/475546.aspx
> for a discussion and some possible workarounds.
> David- Tekst uit oorspronkelijk bericht niet weergeven -- Tekst uit oorspronkelijk bericht weergeven -
Saturday, February 25, 2012
DBREINDEX takes database offline?
HI All,
Environment: SQL server2000, SP4, on win2003.
I have few quetions regarding DBCC DBREINDEX..
1. Reindex is offline operation means..takes the db offline?
we have optimzation job through maintenace plan. When it started, will
it takes the database offline by issuing the below command? I heard one DBA
saying this.
ALTER DATABASE <dbname> SET OFFLINE WITH ROLLBACK IMMEDIATE.
What is my understanding till now is, it will take the table offline by
issuing xcluve lock.. is that correct?
2. Database is avaiable for use when reondex is happening?
3. When users are already connected, this optimation jobs starts, it will
kill the existing users?If no, if the user is having a lock on table, reindex
is also for the same table, reindex will wait for that user to release the
lock on the same object or it will kill the user?
4. It will allow new users while it is running?
Any one can throw some light on above points.
Thanks,
Suchi
Thanks a lot Tibor..I am clear on REINDEX now..
"Tibor Karaszi" wrote:
> First, note that the command being executed (DBCC DBREINDEX) is one thing, and the environment
> (Maint Plan, your own scripts etc) from where you execute it is another thing. The environment might
> add some commands etc. For instance, in 2000 you have an option to "repair minor problems" for the
> DBCC CHECKDB, which makes the environment (Maint Plan) to set the database in single user mode.
> See inline below for comments on your questions:
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Suchi" <Suchi@.discussions.microsoft.com> wrote in message
> news:BF8AF05A-B75E-4FF2-9D7A-40013A5D7F82@.microsoft.com...
> No, it isn't offline in that sense.
>
>
> Your dba is incorrect (unless some incredibly funky environment is used). Actually, if that ALTER
> DATABASE would be executed, then there's no way you can access the database and execute the DBCC
> DBREINDEx commands.
>
> Correct. SQL Server will acquire an exclusive lock if the table has a clustered index, else a shared
> lock (from memory).
>
> Yes, except for the table currently being reindexes and having the lock.
>
> No
>
> Wait. Just as any type lf locking/blocking.
>
> Having alock on a resrource will not prohibit new connections to the database. It will not prohibit
> connections execute queries. It will only cause blocking of there is a clonflicts for locks on the
> same resource.
>
>
Environment: SQL server2000, SP4, on win2003.
I have few quetions regarding DBCC DBREINDEX..
1. Reindex is offline operation means..takes the db offline?
we have optimzation job through maintenace plan. When it started, will
it takes the database offline by issuing the below command? I heard one DBA
saying this.
ALTER DATABASE <dbname> SET OFFLINE WITH ROLLBACK IMMEDIATE.
What is my understanding till now is, it will take the table offline by
issuing xcluve lock.. is that correct?
2. Database is avaiable for use when reondex is happening?
3. When users are already connected, this optimation jobs starts, it will
kill the existing users?If no, if the user is having a lock on table, reindex
is also for the same table, reindex will wait for that user to release the
lock on the same object or it will kill the user?
4. It will allow new users while it is running?
Any one can throw some light on above points.
Thanks,
Suchi
Thanks a lot Tibor..I am clear on REINDEX now..
"Tibor Karaszi" wrote:
> First, note that the command being executed (DBCC DBREINDEX) is one thing, and the environment
> (Maint Plan, your own scripts etc) from where you execute it is another thing. The environment might
> add some commands etc. For instance, in 2000 you have an option to "repair minor problems" for the
> DBCC CHECKDB, which makes the environment (Maint Plan) to set the database in single user mode.
> See inline below for comments on your questions:
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Suchi" <Suchi@.discussions.microsoft.com> wrote in message
> news:BF8AF05A-B75E-4FF2-9D7A-40013A5D7F82@.microsoft.com...
> No, it isn't offline in that sense.
>
>
> Your dba is incorrect (unless some incredibly funky environment is used). Actually, if that ALTER
> DATABASE would be executed, then there's no way you can access the database and execute the DBCC
> DBREINDEx commands.
>
> Correct. SQL Server will acquire an exclusive lock if the table has a clustered index, else a shared
> lock (from memory).
>
> Yes, except for the table currently being reindexes and having the lock.
>
> No
>
> Wait. Just as any type lf locking/blocking.
>
> Having alock on a resrource will not prohibit new connections to the database. It will not prohibit
> connections execute queries. It will only cause blocking of there is a clonflicts for locks on the
> same resource.
>
>
DBREINDEX takes database offline?
HI All,
Environment: SQL server2000, SP4, on win2003.
I have few quetions regarding DBCC DBREINDEX..
1. Reindex is offline operation means..takes the db offline?
we have optimzation job through maintenace plan. When it started, will
it takes the database offline by issuing the below command? I heard one DBA
saying this.
ALTER DATABASE <dbname> SET OFFLINE WITH ROLLBACK IMMEDIATE.
What is my understanding till now is, it will take the table offline by
issuing xcluve lock.. is that correct?
2. Database is avaiable for use when reondex is happening?
3. When users are already connected, this optimation jobs starts, it will
kill the existing users?If no, if the user is having a lock on table, reindex
is also for the same table, reindex will wait for that user to release the
lock on the same object or it will kill the user?
4. It will allow new users while it is running?
Any one can throw some light on above points.
Thanks,
SuchiFirst, note that the command being executed (DBCC DBREINDEX) is one thing, and the environment
(Maint Plan, your own scripts etc) from where you execute it is another thing. The environment might
add some commands etc. For instance, in 2000 you have an option to "repair minor problems" for the
DBCC CHECKDB, which makes the environment (Maint Plan) to set the database in single user mode.
See inline below for comments on your questions:
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Suchi" <Suchi@.discussions.microsoft.com> wrote in message
news:BF8AF05A-B75E-4FF2-9D7A-40013A5D7F82@.microsoft.com...
> HI All,
>
> Environment: SQL server2000, SP4, on win2003.
> I have few quetions regarding DBCC DBREINDEX..
> 1. Reindex is offline operation means..takes the db offline?
No, it isn't offline in that sense.
> we have optimzation job through maintenace plan. When it started, will
> it takes the database offline by issuing the below command? I heard one DBA
> saying this.
> ALTER DATABASE <dbname> SET OFFLINE WITH ROLLBACK IMMEDIATE.
Your dba is incorrect (unless some incredibly funky environment is used). Actually, if that ALTER
DATABASE would be executed, then there's no way you can access the database and execute the DBCC
DBREINDEx commands.
> What is my understanding till now is, it will take the table offline by
> issuing xcluve lock.. is that correct?
Correct. SQL Server will acquire an exclusive lock if the table has a clustered index, else a shared
lock (from memory).
> 2. Database is avaiable for use when reondex is happening?
Yes, except for the table currently being reindexes and having the lock.
>
> 3. When users are already connected, this optimation jobs starts, it will
> kill the existing users?
No
> If no, if the user is having a lock on table, reindex
> is also for the same table, reindex will wait for that user to release the
> lock on the same object or it will kill the user?
Wait. Just as any type lf locking/blocking.
>
> 4. It will allow new users while it is running?
Having alock on a resrource will not prohibit new connections to the database. It will not prohibit
connections execute queries. It will only cause blocking of there is a clonflicts for locks on the
same resource.
>
> Any one can throw some light on above points.
>
> Thanks,
> Suchi|||Thanks a lot Tibor..I am clear on REINDEX now..
"Tibor Karaszi" wrote:
> First, note that the command being executed (DBCC DBREINDEX) is one thing, and the environment
> (Maint Plan, your own scripts etc) from where you execute it is another thing. The environment might
> add some commands etc. For instance, in 2000 you have an option to "repair minor problems" for the
> DBCC CHECKDB, which makes the environment (Maint Plan) to set the database in single user mode.
> See inline below for comments on your questions:
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Suchi" <Suchi@.discussions.microsoft.com> wrote in message
> news:BF8AF05A-B75E-4FF2-9D7A-40013A5D7F82@.microsoft.com...
> > HI All,
> >
> >
> > Environment: SQL server2000, SP4, on win2003.
> >
> > I have few quetions regarding DBCC DBREINDEX..
> >
> > 1. Reindex is offline operation means..takes the db offline?
> No, it isn't offline in that sense.
>
> > we have optimzation job through maintenace plan. When it started, will
> > it takes the database offline by issuing the below command? I heard one DBA
> > saying this.
> >
> > ALTER DATABASE <dbname> SET OFFLINE WITH ROLLBACK IMMEDIATE.
> Your dba is incorrect (unless some incredibly funky environment is used). Actually, if that ALTER
> DATABASE would be executed, then there's no way you can access the database and execute the DBCC
> DBREINDEx commands.
>
> >
> > What is my understanding till now is, it will take the table offline by
> > issuing xcluve lock.. is that correct?
> Correct. SQL Server will acquire an exclusive lock if the table has a clustered index, else a shared
> lock (from memory).
>
> >
> > 2. Database is avaiable for use when reondex is happening?
> Yes, except for the table currently being reindexes and having the lock.
>
> >
> >
> > 3. When users are already connected, this optimation jobs starts, it will
> > kill the existing users?
> No
>
> > If no, if the user is having a lock on table, reindex
> > is also for the same table, reindex will wait for that user to release the
> > lock on the same object or it will kill the user?
> Wait. Just as any type lf locking/blocking.
>
> >
> >
> > 4. It will allow new users while it is running?
> Having alock on a resrource will not prohibit new connections to the database. It will not prohibit
> connections execute queries. It will only cause blocking of there is a clonflicts for locks on the
> same resource.
>
> >
> >
> > Any one can throw some light on above points.
> >
> >
> > Thanks,
> > Suchi
>
Environment: SQL server2000, SP4, on win2003.
I have few quetions regarding DBCC DBREINDEX..
1. Reindex is offline operation means..takes the db offline?
we have optimzation job through maintenace plan. When it started, will
it takes the database offline by issuing the below command? I heard one DBA
saying this.
ALTER DATABASE <dbname> SET OFFLINE WITH ROLLBACK IMMEDIATE.
What is my understanding till now is, it will take the table offline by
issuing xcluve lock.. is that correct?
2. Database is avaiable for use when reondex is happening?
3. When users are already connected, this optimation jobs starts, it will
kill the existing users?If no, if the user is having a lock on table, reindex
is also for the same table, reindex will wait for that user to release the
lock on the same object or it will kill the user?
4. It will allow new users while it is running?
Any one can throw some light on above points.
Thanks,
SuchiFirst, note that the command being executed (DBCC DBREINDEX) is one thing, and the environment
(Maint Plan, your own scripts etc) from where you execute it is another thing. The environment might
add some commands etc. For instance, in 2000 you have an option to "repair minor problems" for the
DBCC CHECKDB, which makes the environment (Maint Plan) to set the database in single user mode.
See inline below for comments on your questions:
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Suchi" <Suchi@.discussions.microsoft.com> wrote in message
news:BF8AF05A-B75E-4FF2-9D7A-40013A5D7F82@.microsoft.com...
> HI All,
>
> Environment: SQL server2000, SP4, on win2003.
> I have few quetions regarding DBCC DBREINDEX..
> 1. Reindex is offline operation means..takes the db offline?
No, it isn't offline in that sense.
> we have optimzation job through maintenace plan. When it started, will
> it takes the database offline by issuing the below command? I heard one DBA
> saying this.
> ALTER DATABASE <dbname> SET OFFLINE WITH ROLLBACK IMMEDIATE.
Your dba is incorrect (unless some incredibly funky environment is used). Actually, if that ALTER
DATABASE would be executed, then there's no way you can access the database and execute the DBCC
DBREINDEx commands.
> What is my understanding till now is, it will take the table offline by
> issuing xcluve lock.. is that correct?
Correct. SQL Server will acquire an exclusive lock if the table has a clustered index, else a shared
lock (from memory).
> 2. Database is avaiable for use when reondex is happening?
Yes, except for the table currently being reindexes and having the lock.
>
> 3. When users are already connected, this optimation jobs starts, it will
> kill the existing users?
No
> If no, if the user is having a lock on table, reindex
> is also for the same table, reindex will wait for that user to release the
> lock on the same object or it will kill the user?
Wait. Just as any type lf locking/blocking.
>
> 4. It will allow new users while it is running?
Having alock on a resrource will not prohibit new connections to the database. It will not prohibit
connections execute queries. It will only cause blocking of there is a clonflicts for locks on the
same resource.
>
> Any one can throw some light on above points.
>
> Thanks,
> Suchi|||Thanks a lot Tibor..I am clear on REINDEX now..
"Tibor Karaszi" wrote:
> First, note that the command being executed (DBCC DBREINDEX) is one thing, and the environment
> (Maint Plan, your own scripts etc) from where you execute it is another thing. The environment might
> add some commands etc. For instance, in 2000 you have an option to "repair minor problems" for the
> DBCC CHECKDB, which makes the environment (Maint Plan) to set the database in single user mode.
> See inline below for comments on your questions:
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Suchi" <Suchi@.discussions.microsoft.com> wrote in message
> news:BF8AF05A-B75E-4FF2-9D7A-40013A5D7F82@.microsoft.com...
> > HI All,
> >
> >
> > Environment: SQL server2000, SP4, on win2003.
> >
> > I have few quetions regarding DBCC DBREINDEX..
> >
> > 1. Reindex is offline operation means..takes the db offline?
> No, it isn't offline in that sense.
>
> > we have optimzation job through maintenace plan. When it started, will
> > it takes the database offline by issuing the below command? I heard one DBA
> > saying this.
> >
> > ALTER DATABASE <dbname> SET OFFLINE WITH ROLLBACK IMMEDIATE.
> Your dba is incorrect (unless some incredibly funky environment is used). Actually, if that ALTER
> DATABASE would be executed, then there's no way you can access the database and execute the DBCC
> DBREINDEx commands.
>
> >
> > What is my understanding till now is, it will take the table offline by
> > issuing xcluve lock.. is that correct?
> Correct. SQL Server will acquire an exclusive lock if the table has a clustered index, else a shared
> lock (from memory).
>
> >
> > 2. Database is avaiable for use when reondex is happening?
> Yes, except for the table currently being reindexes and having the lock.
>
> >
> >
> > 3. When users are already connected, this optimation jobs starts, it will
> > kill the existing users?
> No
>
> > If no, if the user is having a lock on table, reindex
> > is also for the same table, reindex will wait for that user to release the
> > lock on the same object or it will kill the user?
> Wait. Just as any type lf locking/blocking.
>
> >
> >
> > 4. It will allow new users while it is running?
> Having alock on a resrource will not prohibit new connections to the database. It will not prohibit
> connections execute queries. It will only cause blocking of there is a clonflicts for locks on the
> same resource.
>
> >
> >
> > Any one can throw some light on above points.
> >
> >
> > Thanks,
> > Suchi
>
Friday, February 24, 2012
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?
>
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
>> >
>> >
>
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?
>
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 with user name in "users"
Using SS2000 SP4. Under "Users" in EM, I see "dbo" in the name column and in
the login name column a name like "webapp". In other databases, I see "dbo"
in the name column and nothing in the login name column.
What is the significance of having another user like "webapp" in the login
name column? And how could can I assign another user login to dbo?
Thanks,
--
Dan D.Dan
DBO is a priviliged user. There are difference between LOGIN and USER.
BOL has pertty good article about it
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:C8FC2930-1CC2-4BEF-BACA-C292396E498E@.microsoft.com...
> Using SS2000 SP4. Under "Users" in EM, I see "dbo" in the name column and
> in
> the login name column a name like "webapp". In other databases, I see
> "dbo"
> in the name column and nothing in the login name column.
> What is the significance of having another user like "webapp" in the login
> name column? And how could can I assign another user login to dbo?
> Thanks,
> --
> Dan D.|||I've read BOL but I don't see anything that explains how a user ends up in
the "login name" column for "dbo" in the "users" part of EM. Sometimes "sa"
is in the "login name" column for "dbo", sometimes the "login name" column
for "dbo" is empty and sometimes, there is another user name in the "login
name" column for "dbo".
--
Dan D.
"Uri Dimant" wrote:
> Dan
> DBO is a priviliged user. There are difference between LOGIN and USER.
> BOL has pertty good article about it
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:C8FC2930-1CC2-4BEF-BACA-C292396E498E@.microsoft.com...
>
>|||Dan
You can add user to db_owner database fixed role and all objects will be
created as dbo.tablename ( If he/she is a member of sysadmin server role)
If he/she isn't you needs to specify
create table dbo.table (col1 int)
Waht does that mean "sometimes"? Can you reproduce the problem?
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:913FEB8F-7EE7-4161-8EBA-54D71DBA6314@.microsoft.com...[vbcol=seagreen]
> I've read BOL but I don't see anything that explains how a user ends up in
> the "login name" column for "dbo" in the "users" part of EM. Sometimes
> "sa"
> is in the "login name" column for "dbo", sometimes the "login name" column
> for "dbo" is empty and sometimes, there is another user name in the "login
> name" column for "dbo".
> --
> Dan D.
>
> "Uri Dimant" wrote:
>|||"Uri Dimant" wrote:
> Dan
> You can add user to db_owner database fixed role and all objects will be
> created as dbo.tablename ( If he/she is a member of sysadmin server role)
> If he/she isn't you needs to specify
> create table dbo.table (col1 int)
This I understand.
> Waht does that mean "sometimes"? Can you reproduce the problem?
If I create a new database, my name will show up in the "login name" column
for "dbo". That would explain some of what I see. But as I look through
different databases I see a lot of different users in the "login name" colum
n
for "dbo" in the "login" column. In one database, the "login name" column fo
r
"dbo" will be empty. In another database, a user called "jbanner" will be in
the "login name" column for "dbo". In another database, "sa" will be in the
"login name" column for "dbo". In another database "domain\tbrown" will be i
n
the "login name" column for "dbo".
Some of these users are used for applications only, and it's highly unlikely
that someone logged on as the user and created a database so there must be
some other way that it happens.
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:913FEB8F-7EE7-4161-8EBA-54D71DBA6314@.microsoft.com...
>
>|||Hi Dan
The login name column should show the login name who is considered the owner
of the database, whose user name will be 'dbo' when they use the database. I
don't know why EM is sometimes showing a blank here. There should always be
a mapped login name. One guess is that the login of the owner is a domain
account, and the domain is not available to validate the name. You can try
to verify that by run sp_helpdb in a query window, and seeing what gets
listed for the owner of the database.
You can change the login name that owns a database by using a query window.
Use the database, and run the following:
EXEC sp_changedbowner '<new_owner_login_name>'
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:C8FC2930-1CC2-4BEF-BACA-C292396E498E@.microsoft.com...
> Using SS2000 SP4. Under "Users" in EM, I see "dbo" in the name column and
> in
> the login name column a name like "webapp". In other databases, I see
> "dbo"
> in the name column and nothing in the login name column.
> What is the significance of having another user like "webapp" in the login
> name column? And how could can I assign another user login to dbo?
> Thanks,
> --
> Dan D.|||When I run sp_helpdb on the database with a blank in the "login name" column
for dbo, I get an error: "cannot insert the value NULL into column '', table
''; column does not allow nulls. INSERT fails. I changed the dbowner to 'sa'
and it then worked correctly.
The other databases respond appropriately.
I do have some databases with a domain user as owner so I don't think the
domain is the problem.
In a database for an application, could I assign the database owner as a
user with limited (read/write) rights? Or would making the user the database
owner override the read/write permissions?
Thanks,
--
Dan D.
"Kalen Delaney" wrote:
> Hi Dan
> The login name column should show the login name who is considered the own
er
> of the database, whose user name will be 'dbo' when they use the database.
I
> don't know why EM is sometimes showing a blank here. There should always b
e
> a mapped login name. One guess is that the login of the owner is a domain
> account, and the domain is not available to validate the name. You can try
> to verify that by run sp_helpdb in a query window, and seeing what gets
> listed for the owner of the database.
> You can change the login name that owns a database by using a query window
.
> Use the database, and run the following:
> EXEC sp_changedbowner '<new_owner_login_name>'
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:C8FC2930-1CC2-4BEF-BACA-C292396E498E@.microsoft.com...
>
>|||Dan
I have seen that problem with the NULL error message, and I still believe it
has something to do with not being able to validate the login. Other domain
logins might be able to be validated, or might be using cached credentials,
or something. But as you found, changing the owner to sa is usually a great
solution.
The owner of a database always has the user name dbo in the database, which
always give her full permissions. If you don't want that login to have full
permission, give ownership of the database to someone else.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:841A0E2F-8DBE-437F-8661-2CFF7998404E@.microsoft.com...[vbcol=seagreen]
> When I run sp_helpdb on the database with a blank in the "login name"
> column
> for dbo, I get an error: "cannot insert the value NULL into column '',
> table
> ''; column does not allow nulls. INSERT fails. I changed the dbowner to
> 'sa'
> and it then worked correctly.
> The other databases respond appropriately.
> I do have some databases with a domain user as owner so I don't think the
> domain is the problem.
> In a database for an application, could I assign the database owner as a
> user with limited (read/write) rights? Or would making the user the
> database
> owner override the read/write permissions?
> Thanks,
> --
> Dan D.
>
> "Kalen Delaney" wrote:
>|||We've had some developers leave recently and I removed their logins. It migh
t
be that one of them owned the database and because the login/user has been
removed the system can't validate them anymore. I thought the system always
asked to reassign the object in that case but maybe not.
Thanks Kalen,
--
Dan D.
"Kalen Delaney" wrote:
> Dan
> I have seen that problem with the NULL error message, and I still believe
it
> has something to do with not being able to validate the login. Other domai
n
> logins might be able to be validated, or might be using cached credentials
,
> or something. But as you found, changing the owner to sa is usually a grea
t
> solution.
> The owner of a database always has the user name dbo in the database, whic
h
> always give her full permissions. If you don't want that login to have ful
l
> permission, give ownership of the database to someone else.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:841A0E2F-8DBE-437F-8661-2CFF7998404E@.microsoft.com...
>
>|||You can identify databases invalid owners with the following query:
SELECT name
FROM master..sysdatabases
WHERE SUSER_SNAME(sid) IS NULL
Execute sp_changedbowner for the problem databases.
> We've had some developers leave recently and I removed their logins. It
> might
> be that one of them owned the database and because the login/user has been
> removed the system can't validate them anymore. I thought the system
> always
> asked to reassign the object in that case but maybe not.
The check for database ownership is done when you drop a standard login
(sp_droplogin) but not when you remove Windows logins (sp_revokelogin). In
fact, you can have a Windows login own a database even when no corresponding
login exists in SQL Server. This is often the case when an administrator
connects to SQL Server via BUILTIJN\Administrators group membership and then
creates, restores or attaches a database. The database is then owned by the
individual's Windows account. However, the database owner becomes invalid
if the Windows account is later deleted.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:9B68CC18-5C0F-47E0-BB0E-276A7C92569A@.microsoft.com...[vbcol=seagreen]
> We've had some developers leave recently and I removed their logins. It
> might
> be that one of them owned the database and because the login/user has been
> removed the system can't validate them anymore. I thought the system
> always
> asked to reassign the object in that case but maybe not.
> Thanks Kalen,
> --
> Dan D.
>
> "Kalen Delaney" wrote:
>
the login name column a name like "webapp". In other databases, I see "dbo"
in the name column and nothing in the login name column.
What is the significance of having another user like "webapp" in the login
name column? And how could can I assign another user login to dbo?
Thanks,
--
Dan D.Dan
DBO is a priviliged user. There are difference between LOGIN and USER.
BOL has pertty good article about it
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:C8FC2930-1CC2-4BEF-BACA-C292396E498E@.microsoft.com...
> Using SS2000 SP4. Under "Users" in EM, I see "dbo" in the name column and
> in
> the login name column a name like "webapp". In other databases, I see
> "dbo"
> in the name column and nothing in the login name column.
> What is the significance of having another user like "webapp" in the login
> name column? And how could can I assign another user login to dbo?
> Thanks,
> --
> Dan D.|||I've read BOL but I don't see anything that explains how a user ends up in
the "login name" column for "dbo" in the "users" part of EM. Sometimes "sa"
is in the "login name" column for "dbo", sometimes the "login name" column
for "dbo" is empty and sometimes, there is another user name in the "login
name" column for "dbo".
--
Dan D.
"Uri Dimant" wrote:
> Dan
> DBO is a priviliged user. There are difference between LOGIN and USER.
> BOL has pertty good article about it
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:C8FC2930-1CC2-4BEF-BACA-C292396E498E@.microsoft.com...
>
>|||Dan
You can add user to db_owner database fixed role and all objects will be
created as dbo.tablename ( If he/she is a member of sysadmin server role)
If he/she isn't you needs to specify
create table dbo.table (col1 int)
Waht does that mean "sometimes"? Can you reproduce the problem?
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:913FEB8F-7EE7-4161-8EBA-54D71DBA6314@.microsoft.com...[vbcol=seagreen]
> I've read BOL but I don't see anything that explains how a user ends up in
> the "login name" column for "dbo" in the "users" part of EM. Sometimes
> "sa"
> is in the "login name" column for "dbo", sometimes the "login name" column
> for "dbo" is empty and sometimes, there is another user name in the "login
> name" column for "dbo".
> --
> Dan D.
>
> "Uri Dimant" wrote:
>|||"Uri Dimant" wrote:
> Dan
> You can add user to db_owner database fixed role and all objects will be
> created as dbo.tablename ( If he/she is a member of sysadmin server role)
> If he/she isn't you needs to specify
> create table dbo.table (col1 int)
This I understand.
> Waht does that mean "sometimes"? Can you reproduce the problem?
If I create a new database, my name will show up in the "login name" column
for "dbo". That would explain some of what I see. But as I look through
different databases I see a lot of different users in the "login name" colum
n
for "dbo" in the "login" column. In one database, the "login name" column fo
r
"dbo" will be empty. In another database, a user called "jbanner" will be in
the "login name" column for "dbo". In another database, "sa" will be in the
"login name" column for "dbo". In another database "domain\tbrown" will be i
n
the "login name" column for "dbo".
Some of these users are used for applications only, and it's highly unlikely
that someone logged on as the user and created a database so there must be
some other way that it happens.
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:913FEB8F-7EE7-4161-8EBA-54D71DBA6314@.microsoft.com...
>
>|||Hi Dan
The login name column should show the login name who is considered the owner
of the database, whose user name will be 'dbo' when they use the database. I
don't know why EM is sometimes showing a blank here. There should always be
a mapped login name. One guess is that the login of the owner is a domain
account, and the domain is not available to validate the name. You can try
to verify that by run sp_helpdb in a query window, and seeing what gets
listed for the owner of the database.
You can change the login name that owns a database by using a query window.
Use the database, and run the following:
EXEC sp_changedbowner '<new_owner_login_name>'
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:C8FC2930-1CC2-4BEF-BACA-C292396E498E@.microsoft.com...
> Using SS2000 SP4. Under "Users" in EM, I see "dbo" in the name column and
> in
> the login name column a name like "webapp". In other databases, I see
> "dbo"
> in the name column and nothing in the login name column.
> What is the significance of having another user like "webapp" in the login
> name column? And how could can I assign another user login to dbo?
> Thanks,
> --
> Dan D.|||When I run sp_helpdb on the database with a blank in the "login name" column
for dbo, I get an error: "cannot insert the value NULL into column '', table
''; column does not allow nulls. INSERT fails. I changed the dbowner to 'sa'
and it then worked correctly.
The other databases respond appropriately.
I do have some databases with a domain user as owner so I don't think the
domain is the problem.
In a database for an application, could I assign the database owner as a
user with limited (read/write) rights? Or would making the user the database
owner override the read/write permissions?
Thanks,
--
Dan D.
"Kalen Delaney" wrote:
> Hi Dan
> The login name column should show the login name who is considered the own
er
> of the database, whose user name will be 'dbo' when they use the database.
I
> don't know why EM is sometimes showing a blank here. There should always b
e
> a mapped login name. One guess is that the login of the owner is a domain
> account, and the domain is not available to validate the name. You can try
> to verify that by run sp_helpdb in a query window, and seeing what gets
> listed for the owner of the database.
> You can change the login name that owns a database by using a query window
.
> Use the database, and run the following:
> EXEC sp_changedbowner '<new_owner_login_name>'
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:C8FC2930-1CC2-4BEF-BACA-C292396E498E@.microsoft.com...
>
>|||Dan
I have seen that problem with the NULL error message, and I still believe it
has something to do with not being able to validate the login. Other domain
logins might be able to be validated, or might be using cached credentials,
or something. But as you found, changing the owner to sa is usually a great
solution.
The owner of a database always has the user name dbo in the database, which
always give her full permissions. If you don't want that login to have full
permission, give ownership of the database to someone else.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:841A0E2F-8DBE-437F-8661-2CFF7998404E@.microsoft.com...[vbcol=seagreen]
> When I run sp_helpdb on the database with a blank in the "login name"
> column
> for dbo, I get an error: "cannot insert the value NULL into column '',
> table
> ''; column does not allow nulls. INSERT fails. I changed the dbowner to
> 'sa'
> and it then worked correctly.
> The other databases respond appropriately.
> I do have some databases with a domain user as owner so I don't think the
> domain is the problem.
> In a database for an application, could I assign the database owner as a
> user with limited (read/write) rights? Or would making the user the
> database
> owner override the read/write permissions?
> Thanks,
> --
> Dan D.
>
> "Kalen Delaney" wrote:
>|||We've had some developers leave recently and I removed their logins. It migh
t
be that one of them owned the database and because the login/user has been
removed the system can't validate them anymore. I thought the system always
asked to reassign the object in that case but maybe not.
Thanks Kalen,
--
Dan D.
"Kalen Delaney" wrote:
> Dan
> I have seen that problem with the NULL error message, and I still believe
it
> has something to do with not being able to validate the login. Other domai
n
> logins might be able to be validated, or might be using cached credentials
,
> or something. But as you found, changing the owner to sa is usually a grea
t
> solution.
> The owner of a database always has the user name dbo in the database, whic
h
> always give her full permissions. If you don't want that login to have ful
l
> permission, give ownership of the database to someone else.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:841A0E2F-8DBE-437F-8661-2CFF7998404E@.microsoft.com...
>
>|||You can identify databases invalid owners with the following query:
SELECT name
FROM master..sysdatabases
WHERE SUSER_SNAME(sid) IS NULL
Execute sp_changedbowner for the problem databases.
> We've had some developers leave recently and I removed their logins. It
> might
> be that one of them owned the database and because the login/user has been
> removed the system can't validate them anymore. I thought the system
> always
> asked to reassign the object in that case but maybe not.
The check for database ownership is done when you drop a standard login
(sp_droplogin) but not when you remove Windows logins (sp_revokelogin). In
fact, you can have a Windows login own a database even when no corresponding
login exists in SQL Server. This is often the case when an administrator
connects to SQL Server via BUILTIJN\Administrators group membership and then
creates, restores or attaches a database. The database is then owned by the
individual's Windows account. However, the database owner becomes invalid
if the Windows account is later deleted.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:9B68CC18-5C0F-47E0-BB0E-276A7C92569A@.microsoft.com...[vbcol=seagreen]
> We've had some developers leave recently and I removed their logins. It
> might
> be that one of them owned the database and because the login/user has been
> removed the system can't validate them anymore. I thought the system
> always
> asked to reassign the object in that case but maybe not.
> Thanks Kalen,
> --
> Dan D.
>
> "Kalen Delaney" wrote:
>
Sunday, February 19, 2012
dbo > login name, it's not sa
I have a few databases on SQL2000 SP4 that are NOT owned by sa, and this
bothers me as I am trying to resolve many security issues. I've used
SP_Changedbowner and the database properties now show the owner as sa, but
when I drill into the user list under the database, the columns displayed
(Name, Login Name, Database Access) show the Name as dbo, but the Login Name
is still NOT sa. <puzzling> Does someone out there have an answer to this
for me? Can you tell me how to reset the DBO to sa, like the other 50
databases on this server?
Thank You!!!
Ok, naivigate to Users (under specific database) and click refresh
"WANNABE" <breichenbach AT istate DOT com> wrote in message
news:uKHH1xlDHHA.3396@.TK2MSFTNGP02.phx.gbl...
>I have a few databases on SQL2000 SP4 that are NOT owned by sa, and this
>bothers me as I am trying to resolve many security issues. I've used
>SP_Changedbowner and the database properties now show the owner as sa, but
>when I drill into the user list under the database, the columns displayed
>(Name, Login Name, Database Access) show the Name as dbo, but the Login
>Name is still NOT sa. <puzzling> Does someone out there have an answer to
>this for me? Can you tell me how to reset the DBO to sa, like the other
>50 databases on this server?
> Thank You!!!
>
|||Since you used TSQL code to change the owner, can you try looking at the
owner name using TSQL?
This code will give you the name of all your databases, and login names of
the database owners:
select name, suser_sname(sid)
from master..sysdatabases
If the code shows you the correct owner name, you are good to go.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"WANNABE" <breichenbach AT istate DOT com> wrote in message
news:uKHH1xlDHHA.3396@.TK2MSFTNGP02.phx.gbl...
>I have a few databases on SQL2000 SP4 that are NOT owned by sa, and this
>bothers me as I am trying to resolve many security issues. I've used
>SP_Changedbowner and the database properties now show the owner as sa, but
>when I drill into the user list under the database, the columns displayed
>(Name, Login Name, Database Access) show the Name as dbo, but the Login
>Name is still NOT sa. <puzzling> Does someone out there have an answer to
>this for me? Can you tell me how to reset the DBO to sa, like the other
>50 databases on this server?
> Thank You!!!
>
|||Thank you, I did find that refreshing EM displayed the current owner, but
it's also nice to know the TSQL command for this... :-)
==================
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OkLR45zDHHA.1220@.TK2MSFTNGP04.phx.gbl...
> Since you used TSQL code to change the owner, can you try looking at the
> owner name using TSQL?
> This code will give you the name of all your databases, and login names of
> the database owners:
> select name, suser_sname(sid)
> from master..sysdatabases
> If the code shows you the correct owner name, you are good to go.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "WANNABE" <breichenbach AT istate DOT com> wrote in message
> news:uKHH1xlDHHA.3396@.TK2MSFTNGP02.phx.gbl...
>
bothers me as I am trying to resolve many security issues. I've used
SP_Changedbowner and the database properties now show the owner as sa, but
when I drill into the user list under the database, the columns displayed
(Name, Login Name, Database Access) show the Name as dbo, but the Login Name
is still NOT sa. <puzzling> Does someone out there have an answer to this
for me? Can you tell me how to reset the DBO to sa, like the other 50
databases on this server?
Thank You!!!
Ok, naivigate to Users (under specific database) and click refresh
"WANNABE" <breichenbach AT istate DOT com> wrote in message
news:uKHH1xlDHHA.3396@.TK2MSFTNGP02.phx.gbl...
>I have a few databases on SQL2000 SP4 that are NOT owned by sa, and this
>bothers me as I am trying to resolve many security issues. I've used
>SP_Changedbowner and the database properties now show the owner as sa, but
>when I drill into the user list under the database, the columns displayed
>(Name, Login Name, Database Access) show the Name as dbo, but the Login
>Name is still NOT sa. <puzzling> Does someone out there have an answer to
>this for me? Can you tell me how to reset the DBO to sa, like the other
>50 databases on this server?
> Thank You!!!
>
|||Since you used TSQL code to change the owner, can you try looking at the
owner name using TSQL?
This code will give you the name of all your databases, and login names of
the database owners:
select name, suser_sname(sid)
from master..sysdatabases
If the code shows you the correct owner name, you are good to go.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"WANNABE" <breichenbach AT istate DOT com> wrote in message
news:uKHH1xlDHHA.3396@.TK2MSFTNGP02.phx.gbl...
>I have a few databases on SQL2000 SP4 that are NOT owned by sa, and this
>bothers me as I am trying to resolve many security issues. I've used
>SP_Changedbowner and the database properties now show the owner as sa, but
>when I drill into the user list under the database, the columns displayed
>(Name, Login Name, Database Access) show the Name as dbo, but the Login
>Name is still NOT sa. <puzzling> Does someone out there have an answer to
>this for me? Can you tell me how to reset the DBO to sa, like the other
>50 databases on this server?
> Thank You!!!
>
|||Thank you, I did find that refreshing EM displayed the current owner, but
it's also nice to know the TSQL command for this... :-)
==================
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OkLR45zDHHA.1220@.TK2MSFTNGP04.phx.gbl...
> Since you used TSQL code to change the owner, can you try looking at the
> owner name using TSQL?
> This code will give you the name of all your databases, and login names of
> the database owners:
> select name, suser_sname(sid)
> from master..sysdatabases
> If the code shows you the correct owner name, you are good to go.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "WANNABE" <breichenbach AT istate DOT com> wrote in message
> news:uKHH1xlDHHA.3396@.TK2MSFTNGP02.phx.gbl...
>
dbo > login name, it's not sa
I have a few databases on SQL2000 SP4 that are NOT owned by sa, and this
bothers me as I am trying to resolve many security issues. I've used
SP_Changedbowner and the database properties now show the owner as sa, but
when I drill into the user list under the database, the columns displayed
(Name, Login Name, Database Access) show the Name as dbo, but the Login Name
is still NOT sa. <puzzling> Does someone out there have an answer to this
for me? Can you tell me how to reset the DBO to sa, like the other 50
databases on this server'
Thank You!!!Ok, naivigate to Users (under specific database) and click refresh
"WANNABE" <breichenbach AT istate DOT com> wrote in message
news:uKHH1xlDHHA.3396@.TK2MSFTNGP02.phx.gbl...
>I have a few databases on SQL2000 SP4 that are NOT owned by sa, and this
>bothers me as I am trying to resolve many security issues. I've used
>SP_Changedbowner and the database properties now show the owner as sa, but
>when I drill into the user list under the database, the columns displayed
>(Name, Login Name, Database Access) show the Name as dbo, but the Login
>Name is still NOT sa. <puzzling> Does someone out there have an answer to
>this for me? Can you tell me how to reset the DBO to sa, like the other
>50 databases on this server'
> Thank You!!!
>|||Since you used TSQL code to change the owner, can you try looking at the
owner name using TSQL?
This code will give you the name of all your databases, and login names of
the database owners:
select name, suser_sname(sid)
from master..sysdatabases
If the code shows you the correct owner name, you are good to go.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"WANNABE" <breichenbach AT istate DOT com> wrote in message
news:uKHH1xlDHHA.3396@.TK2MSFTNGP02.phx.gbl...
>I have a few databases on SQL2000 SP4 that are NOT owned by sa, and this
>bothers me as I am trying to resolve many security issues. I've used
>SP_Changedbowner and the database properties now show the owner as sa, but
>when I drill into the user list under the database, the columns displayed
>(Name, Login Name, Database Access) show the Name as dbo, but the Login
>Name is still NOT sa. <puzzling> Does someone out there have an answer to
>this for me? Can you tell me how to reset the DBO to sa, like the other
>50 databases on this server'
> Thank You!!!
>|||Thank you, I did find that refreshing EM displayed the current owner, but
it's also nice to know the TSQL command for this... :-)
=================="Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OkLR45zDHHA.1220@.TK2MSFTNGP04.phx.gbl...
> Since you used TSQL code to change the owner, can you try looking at the
> owner name using TSQL?
> This code will give you the name of all your databases, and login names of
> the database owners:
> select name, suser_sname(sid)
> from master..sysdatabases
> If the code shows you the correct owner name, you are good to go.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "WANNABE" <breichenbach AT istate DOT com> wrote in message
> news:uKHH1xlDHHA.3396@.TK2MSFTNGP02.phx.gbl...
>>I have a few databases on SQL2000 SP4 that are NOT owned by sa, and this
>>bothers me as I am trying to resolve many security issues. I've used
>>SP_Changedbowner and the database properties now show the owner as sa, but
>>when I drill into the user list under the database, the columns displayed
>>(Name, Login Name, Database Access) show the Name as dbo, but the Login
>>Name is still NOT sa. <puzzling> Does someone out there have an answer
>>to this for me? Can you tell me how to reset the DBO to sa, like the
>>other 50 databases on this server'
>> Thank You!!!
>
bothers me as I am trying to resolve many security issues. I've used
SP_Changedbowner and the database properties now show the owner as sa, but
when I drill into the user list under the database, the columns displayed
(Name, Login Name, Database Access) show the Name as dbo, but the Login Name
is still NOT sa. <puzzling> Does someone out there have an answer to this
for me? Can you tell me how to reset the DBO to sa, like the other 50
databases on this server'
Thank You!!!Ok, naivigate to Users (under specific database) and click refresh
"WANNABE" <breichenbach AT istate DOT com> wrote in message
news:uKHH1xlDHHA.3396@.TK2MSFTNGP02.phx.gbl...
>I have a few databases on SQL2000 SP4 that are NOT owned by sa, and this
>bothers me as I am trying to resolve many security issues. I've used
>SP_Changedbowner and the database properties now show the owner as sa, but
>when I drill into the user list under the database, the columns displayed
>(Name, Login Name, Database Access) show the Name as dbo, but the Login
>Name is still NOT sa. <puzzling> Does someone out there have an answer to
>this for me? Can you tell me how to reset the DBO to sa, like the other
>50 databases on this server'
> Thank You!!!
>|||Since you used TSQL code to change the owner, can you try looking at the
owner name using TSQL?
This code will give you the name of all your databases, and login names of
the database owners:
select name, suser_sname(sid)
from master..sysdatabases
If the code shows you the correct owner name, you are good to go.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"WANNABE" <breichenbach AT istate DOT com> wrote in message
news:uKHH1xlDHHA.3396@.TK2MSFTNGP02.phx.gbl...
>I have a few databases on SQL2000 SP4 that are NOT owned by sa, and this
>bothers me as I am trying to resolve many security issues. I've used
>SP_Changedbowner and the database properties now show the owner as sa, but
>when I drill into the user list under the database, the columns displayed
>(Name, Login Name, Database Access) show the Name as dbo, but the Login
>Name is still NOT sa. <puzzling> Does someone out there have an answer to
>this for me? Can you tell me how to reset the DBO to sa, like the other
>50 databases on this server'
> Thank You!!!
>|||Thank you, I did find that refreshing EM displayed the current owner, but
it's also nice to know the TSQL command for this... :-)
=================="Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OkLR45zDHHA.1220@.TK2MSFTNGP04.phx.gbl...
> Since you used TSQL code to change the owner, can you try looking at the
> owner name using TSQL?
> This code will give you the name of all your databases, and login names of
> the database owners:
> select name, suser_sname(sid)
> from master..sysdatabases
> If the code shows you the correct owner name, you are good to go.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "WANNABE" <breichenbach AT istate DOT com> wrote in message
> news:uKHH1xlDHHA.3396@.TK2MSFTNGP02.phx.gbl...
>>I have a few databases on SQL2000 SP4 that are NOT owned by sa, and this
>>bothers me as I am trying to resolve many security issues. I've used
>>SP_Changedbowner and the database properties now show the owner as sa, but
>>when I drill into the user list under the database, the columns displayed
>>(Name, Login Name, Database Access) show the Name as dbo, but the Login
>>Name is still NOT sa. <puzzling> Does someone out there have an answer
>>to this for me? Can you tell me how to reset the DBO to sa, like the
>>other 50 databases on this server'
>> Thank You!!!
>
dbo > login name, it's not sa
I have a few databases on SQL2000 SP4 that are NOT owned by sa, and this
bothers me as I am trying to resolve many security issues. I've used
SP_Changedbowner and the database properties now show the owner as sa, but
when I drill into the user list under the database, the columns displayed
(Name, Login Name, Database Access) show the Name as dbo, but the Login Name
is still NOT sa. <puzzling> Does someone out there have an answer to this
for me? Can you tell me how to reset the DBO to sa, like the other 50
databases on this server'
Thank You!!!Ok, naivigate to Users (under specific database) and click refresh
"WANNABE" <breichenbach AT istate DOT com> wrote in message
news:uKHH1xlDHHA.3396@.TK2MSFTNGP02.phx.gbl...
>I have a few databases on SQL2000 SP4 that are NOT owned by sa, and this
>bothers me as I am trying to resolve many security issues. I've used
>SP_Changedbowner and the database properties now show the owner as sa, but
>when I drill into the user list under the database, the columns displayed
>(Name, Login Name, Database Access) show the Name as dbo, but the Login
>Name is still NOT sa. <puzzling> Does someone out there have an answer to
>this for me? Can you tell me how to reset the DBO to sa, like the other
>50 databases on this server'
> Thank You!!!
>|||Since you used TSQL code to change the owner, can you try looking at the
owner name using TSQL?
This code will give you the name of all your databases, and login names of
the database owners:
select name, suser_sname(sid)
from master..sysdatabases
If the code shows you the correct owner name, you are good to go.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"WANNABE" <breichenbach AT istate DOT com> wrote in message
news:uKHH1xlDHHA.3396@.TK2MSFTNGP02.phx.gbl...
>I have a few databases on SQL2000 SP4 that are NOT owned by sa, and this
>bothers me as I am trying to resolve many security issues. I've used
>SP_Changedbowner and the database properties now show the owner as sa, but
>when I drill into the user list under the database, the columns displayed
>(Name, Login Name, Database Access) show the Name as dbo, but the Login
>Name is still NOT sa. <puzzling> Does someone out there have an answer to
>this for me? Can you tell me how to reset the DBO to sa, like the other
>50 databases on this server'
> Thank You!!!
>|||Thank you, I did find that refreshing EM displayed the current owner, but
it's also nice to know the TSQL command for this... :-)
==================
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OkLR45zDHHA.1220@.TK2MSFTNGP04.phx.gbl...
> Since you used TSQL code to change the owner, can you try looking at the
> owner name using TSQL?
> This code will give you the name of all your databases, and login names of
> the database owners:
> select name, suser_sname(sid)
> from master..sysdatabases
> If the code shows you the correct owner name, you are good to go.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "WANNABE" <breichenbach AT istate DOT com> wrote in message
> news:uKHH1xlDHHA.3396@.TK2MSFTNGP02.phx.gbl...
>
bothers me as I am trying to resolve many security issues. I've used
SP_Changedbowner and the database properties now show the owner as sa, but
when I drill into the user list under the database, the columns displayed
(Name, Login Name, Database Access) show the Name as dbo, but the Login Name
is still NOT sa. <puzzling> Does someone out there have an answer to this
for me? Can you tell me how to reset the DBO to sa, like the other 50
databases on this server'
Thank You!!!Ok, naivigate to Users (under specific database) and click refresh
"WANNABE" <breichenbach AT istate DOT com> wrote in message
news:uKHH1xlDHHA.3396@.TK2MSFTNGP02.phx.gbl...
>I have a few databases on SQL2000 SP4 that are NOT owned by sa, and this
>bothers me as I am trying to resolve many security issues. I've used
>SP_Changedbowner and the database properties now show the owner as sa, but
>when I drill into the user list under the database, the columns displayed
>(Name, Login Name, Database Access) show the Name as dbo, but the Login
>Name is still NOT sa. <puzzling> Does someone out there have an answer to
>this for me? Can you tell me how to reset the DBO to sa, like the other
>50 databases on this server'
> Thank You!!!
>|||Since you used TSQL code to change the owner, can you try looking at the
owner name using TSQL?
This code will give you the name of all your databases, and login names of
the database owners:
select name, suser_sname(sid)
from master..sysdatabases
If the code shows you the correct owner name, you are good to go.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"WANNABE" <breichenbach AT istate DOT com> wrote in message
news:uKHH1xlDHHA.3396@.TK2MSFTNGP02.phx.gbl...
>I have a few databases on SQL2000 SP4 that are NOT owned by sa, and this
>bothers me as I am trying to resolve many security issues. I've used
>SP_Changedbowner and the database properties now show the owner as sa, but
>when I drill into the user list under the database, the columns displayed
>(Name, Login Name, Database Access) show the Name as dbo, but the Login
>Name is still NOT sa. <puzzling> Does someone out there have an answer to
>this for me? Can you tell me how to reset the DBO to sa, like the other
>50 databases on this server'
> Thank You!!!
>|||Thank you, I did find that refreshing EM displayed the current owner, but
it's also nice to know the TSQL command for this... :-)
==================
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OkLR45zDHHA.1220@.TK2MSFTNGP04.phx.gbl...
> Since you used TSQL code to change the owner, can you try looking at the
> owner name using TSQL?
> This code will give you the name of all your databases, and login names of
> the database owners:
> select name, suser_sname(sid)
> from master..sysdatabases
> If the code shows you the correct owner name, you are good to go.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "WANNABE" <breichenbach AT istate DOT com> wrote in message
> news:uKHH1xlDHHA.3396@.TK2MSFTNGP02.phx.gbl...
>
Friday, February 17, 2012
Dbnetlib connectionopen(preloginhandshake()) error
SQL 2000/SQL 2005(see below) running on Windows 2003 sp1, web server running
IIS 5.0 on Windows 2000 sp4.
Sometime in the first week of every month since at least Nov 2005, I get an
Dbnetlib connectionopen(preloginhandshake()) error. Connections to the
databases from the Web pages will be slow and intermittent. Rebooting both
web server and sql machine does not fix the error.
Upgraded to SQL 2005 in Jan 06. The connection issue will last 24 to 36
hours then fix itself. Any suggestions on how to fix the error would be
appreciated.
ThanksDuring that time the system is acting as described, I would run a SQL
profiler trace to rule out SQL Server jobs/users. If not found, then run
Windows performance monitor looking at memory and network traffic.
Let me know where I can help.
Adam
"Heather" wrote:
> SQL 2000/SQL 2005(see below) running on Windows 2003 sp1, web server runni
ng
> IIS 5.0 on Windows 2000 sp4.
> Sometime in the first week of every month since at least Nov 2005, I get a
n
> Dbnetlib connectionopen(preloginhandshake()) error. Connections to the
> databases from the Web pages will be slow and intermittent. Rebooting both
> web server and sql machine does not fix the error.
> Upgraded to SQL 2005 in Jan 06. The connection issue will last 24 to 36
> hours then fix itself. Any suggestions on how to fix the error would be
> appreciated.
> Thanks
>
IIS 5.0 on Windows 2000 sp4.
Sometime in the first week of every month since at least Nov 2005, I get an
Dbnetlib connectionopen(preloginhandshake()) error. Connections to the
databases from the Web pages will be slow and intermittent. Rebooting both
web server and sql machine does not fix the error.
Upgraded to SQL 2005 in Jan 06. The connection issue will last 24 to 36
hours then fix itself. Any suggestions on how to fix the error would be
appreciated.
ThanksDuring that time the system is acting as described, I would run a SQL
profiler trace to rule out SQL Server jobs/users. If not found, then run
Windows performance monitor looking at memory and network traffic.
Let me know where I can help.
Adam
"Heather" wrote:
> SQL 2000/SQL 2005(see below) running on Windows 2003 sp1, web server runni
ng
> IIS 5.0 on Windows 2000 sp4.
> Sometime in the first week of every month since at least Nov 2005, I get a
n
> Dbnetlib connectionopen(preloginhandshake()) error. Connections to the
> databases from the Web pages will be slow and intermittent. Rebooting both
> web server and sql machine does not fix the error.
> Upgraded to SQL 2005 in Jan 06. The connection issue will last 24 to 36
> hours then fix itself. Any suggestions on how to fix the error would be
> appreciated.
> Thanks
>
Dbnetlib connectionopen(preloginhandshake()) error
SQL 2000/SQL 2005(see below) running on Windows 2003 sp1, web server running
IIS 5.0 on Windows 2000 sp4.
Sometime in the first week of every month since at least Nov 2005, I get an
Dbnetlib connectionopen(preloginhandshake()) error. Connections to the
databases from the Web pages will be slow and intermittent. Rebooting both
web server and sql machine does not fix the error.
Upgraded to SQL 2005 in Jan 06. The connection issue will last 24 to 36
hours then fix itself. Any suggestions on how to fix the error would be
appreciated.
Thanks
During that time the system is acting as described, I would run a SQL
profiler trace to rule out SQL Server jobs/users. If not found, then run
Windows performance monitor looking at memory and network traffic.
Let me know where I can help.
Adam
"Heather" wrote:
> SQL 2000/SQL 2005(see below) running on Windows 2003 sp1, web server running
> IIS 5.0 on Windows 2000 sp4.
> Sometime in the first week of every month since at least Nov 2005, I get an
> Dbnetlib connectionopen(preloginhandshake()) error. Connections to the
> databases from the Web pages will be slow and intermittent. Rebooting both
> web server and sql machine does not fix the error.
> Upgraded to SQL 2005 in Jan 06. The connection issue will last 24 to 36
> hours then fix itself. Any suggestions on how to fix the error would be
> appreciated.
> Thanks
>
IIS 5.0 on Windows 2000 sp4.
Sometime in the first week of every month since at least Nov 2005, I get an
Dbnetlib connectionopen(preloginhandshake()) error. Connections to the
databases from the Web pages will be slow and intermittent. Rebooting both
web server and sql machine does not fix the error.
Upgraded to SQL 2005 in Jan 06. The connection issue will last 24 to 36
hours then fix itself. Any suggestions on how to fix the error would be
appreciated.
Thanks
During that time the system is acting as described, I would run a SQL
profiler trace to rule out SQL Server jobs/users. If not found, then run
Windows performance monitor looking at memory and network traffic.
Let me know where I can help.
Adam
"Heather" wrote:
> SQL 2000/SQL 2005(see below) running on Windows 2003 sp1, web server running
> IIS 5.0 on Windows 2000 sp4.
> Sometime in the first week of every month since at least Nov 2005, I get an
> Dbnetlib connectionopen(preloginhandshake()) error. Connections to the
> databases from the Web pages will be slow and intermittent. Rebooting both
> web server and sql machine does not fix the error.
> Upgraded to SQL 2005 in Jan 06. The connection issue will last 24 to 36
> hours then fix itself. Any suggestions on how to fix the error would be
> appreciated.
> Thanks
>
Subscribe to:
Posts (Atom)