Locked out of Report Manager on my own machine!

I must have inadvertently "touched" something in my configuration, but I
don't know what, or how to fix it. Here's what's happening:
1) Running Windows XP Professional, ISS, SQL Server 2000, Reporting Services
2000 SP2. All are installed on my laptop and have been running normally for
over a year.
2) I am logged in to my machine as Administrator.
3) When I try to open Report Manager via http://localhost/Reports, I get the
prompt
"Enter username and password for "" at http://localhost".
I enter "Administrator", then the password as usual.
4) Normally, Report Manager then opens up. However, now, I can't get past
this dialog! It immediately gives me the same prompts again, and again, and
again...
5) I have tried every user and password on this machine. I have reset the
Admin password and tried again. I have rebooted many times.
6) The IIS Security settings are the same everywhere: Integrated Windows
Authentication, NO anonymous access. This is set:
* At the Web Sites level
* At the Default Web Site level
* At the Reports virtual directory
* At the ReportServer virtual directory
===================
Hope you can help! I am completely locked out. (How can IIS not allow THE
ADMINISTRATOR access?)
Thanks,
C17Problem solved -- it's something to do with the HTTP Keep-Alive property.
In IIS, if you go to the Default Web Site, and un-check the "HTTP
Keep-Alive" property, you will experience the bizarre problem of being
locked out of your own machine, even as Administrator.
If you check the property, all is well.
--C17

Locked out of Enterprise Manager

Hi all

First post - please be gentle :)

I have had a search of the forum and couldn't find a solution to this one.

On a clean install of SQL Server 2000 on a W2003 server we setup our database as normal, selected appropriate priveldges for everyone and all was well.

Until an adminsitraor accidently turned builtin\administors account to DENY access. We are now locked out of EM and I can't think of a way back in.

We can't access the database via the sa account as it is set to Windows Autentication, and we can't re-register the database for the same reason.

No other user has sufficent priveldges to update the master db or change authenticaion, or even log into EM for that matter.

I think we are snookered, but any thoughts are welcomed.

Cheers

K2Interesting.
There's also one more user that has full admin rights in SQL instance, that is local admin account. i.e., machinename\administrator
Since this user also is part of admin group, try changing it's member group to POWER USERS.
Start-->Run--> control userpasswords2
Select the ADMINISTRATOR user , choose properties and under the Group Membership change it to Power Users.
Now, it won't inherit the BUILTIN/ADMINSTRATOR group , but would still be existing in sqlserver logins as admin user.
Try connecting to sql under this local admin account!

Hopefully, it would work.
AND, please don't forget to create one more ADMIN user on the machine, before changing localadmin user to POWERUSERS :)

If you couldn't connect, you might need to rebuild the master db using rebuild utility, that will leave ur instance intact but rebuild the master db and you will need to reattach ur user dbs after wards.
c:\Program Files\Microsoft SQL Server\80\Tools\Binn\rebuildm.exe|||My first thought would be to restore the master database from before the incident. There is a small chance you can reset the logon mode to mixed authentication, though. Did you ever set a password for sa?|||Interesting.
There's also one more user that has full admin rights in SQL instance, that is local admin account. i.e., machinename\administrator
Since this user also is part of admin group, try changing it's member group to POWER USERS.
Start-->Run--> control userpasswords2
Select the ADMINISTRATOR user , choose properties and under the Group Membership change it to Power Users.
Now, it won't inherit the BUILTIN/ADMINSTRATOR group , but would still be existing in sqlserver logins as admin user.

Hi

I think this would have been a better angle to take than what we did in the end! We didn't think of that one.

In the end we did a rebuild of the master database and this worked just fine.

We did try to change the authentication mode (we had set the sa password) but it wouldn't have it.

Thanks for your help guys!

K

Lock out 1 user within Enterprise Manager

I have one rogue user who constantly creates, deletes,
and just does things in the system that are not
authorized causing tons of administrative work on my
end. Is there a way to allow this user ONLY to READ
everything from within SQL-EM, vs. writing or creating
anything. All this person does is Crystal reports, so I
might just remove the software altogether. But I'd like
to restrict rights because they'll definitely come tell
me that they couldn't do something due to restriction, at
which point I can bust them. Thanks!Don't give the user sysadmin privileges on the database.
Make this person a member of the datareader role in the database they need
access to and no other role membership other than public and no permissions
explicitly.
Bill
"CPS" <anonymous@.discussions.microsoft.com> wrote in message
news:14e2f01c3fa26$40d04c80$a001280a@.phx
.gbl...
> I have one rogue user who constantly creates, deletes,
> and just does things in the system that are not
> authorized causing tons of administrative work on my
> end. Is there a way to allow this user ONLY to READ
> everything from within SQL-EM, vs. writing or creating
> anything. All this person does is Crystal reports, so I
> might just remove the software altogether. But I'd like
> to restrict rights because they'll definitely come tell
> me that they couldn't do something due to restriction, at
> which point I can bust them. Thanks!|||Thanks!! How do I make sure they don't go into EM and
give themselves the rights back again?
>--Original Message--
>Don't give the user sysadmin privileges on the database.
>Make this person a member of the datareader role in the
database they need
>access to and no other role membership other than public
and no permissions
>explicitly.
>Bill
>"CPS" <anonymous@.discussions.microsoft.com> wrote in
message
> news:14e2f01c3fa26$40d04c80$a001280a@.phx
.gbl...
I
like
at
>
>.
>|||If they don't have sa or dbo rights they wont be able to change permissions.
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:1567901c3fa5b$c5789d90$a501280a@.phx
.gbl...
> Thanks!! How do I make sure they don't go into EM and
> give themselves the rights back again?
> database they need
> and no permissions
> message
> I
> like
> atsql

lock manager question

i have a query that deletes a large range of rows but the where clause
is not indexes
delete from instructor where lastteachdate < '2005-01-01'
lastteachdate does not have an index and the instructor table has 100,000
records.
sp_lock shows a table lock. I understand why. Since there is no index on
the
lastteachdate, it must lock everything to search and delete the correct
rows.
there is an index on the table called instructorid. it is a non-clustered
index. it is
the only index on the table (don't yell at me, i inherited this). I was
told not to
add an index on lastteachdate (i can't go into the details why right now).
my question is, if i write code that (a) select instructorid from
instructor where lastteachdate < '2005-01-01'
(b) deletes each instructor by instructorid in a loop, I think I can have
the lock manager
just use row locks and not a table lock. That would allow inserts and
updates of existing rows
without blocking.
Is this a bad idea? I don't know the locking well enough to decipher all
the X locks that I see
in sp_lock. I see locks for the rows and then I see locks on indid = 2
which I think is the
non clustered index.
Do you know how many rows you are likely to delete?
If not too many you could insert the instructorid into a table variable (or
'TABLE) and delete using it
declare @.RowsToDelete TABLE (instructorid int)
insert into @.RowsToDelete
select instructorid
from instructor
where lastteachdate < '2005-01-01'
delete from instructor
where instructorid in (select instructorid from @.RowsToDelete)
or
delete from instructor
from @.RowsToDelete RowsToDelete
where instructor.instructorid = RowToDelete.instructorid
or using a derived table may help
delete from instructor
from (
select instructorid
from instructor
where lastteachdate < '2005-01-01'
) as RowsToDelete
where instructor.instructorid = RowToDelete.instructorid
You could also do it in batches by setting ROWCOUNT and using a while loop.
Break out of the loop when @.@.ROWCOUNT after the delete is zero
eg
declare @.RowC
set ROWCOUNT 50
while (1=1) begin
BEGIN TRANSACTION
delete from instructor
from @.RowsToDelete RowsToDelete
where instructor.instructorid = RowToDelete.instructorid
select @.RowC = @.@.ROWCOUNT
COMMIT TRANSACTION
if @.RowC = 0 BREAK
end
set ROWCOUNT 0
Be aware that SET ROWCOUNT will affect any triggers you have on intructors
Paul
"Dodo Lurker" <none@.noemailplease> wrote in message
news:AIGdnacqdfhqm4DYnZ2dnUVZ_tqdnZ2d@.comcast.com. ..
>i have a query that deletes a large range of rows but the where clause
> is not indexes
> delete from instructor where lastteachdate < '2005-01-01'
> lastteachdate does not have an index and the instructor table has 100,000
> records.
> sp_lock shows a table lock. I understand why. Since there is no index on
> the
> lastteachdate, it must lock everything to search and delete the correct
> rows.
> there is an index on the table called instructorid. it is a non-clustered
> index. it is
> the only index on the table (don't yell at me, i inherited this). I was
> told not to
> add an index on lastteachdate (i can't go into the details why right now).
> my question is, if i write code that (a) select instructorid from
> instructor where lastteachdate < '2005-01-01'
> (b) deletes each instructor by instructorid in a loop, I think I can have
> the lock manager
> just use row locks and not a table lock. That would allow inserts and
> updates of existing rows
> without blocking.
> Is this a bad idea? I don't know the locking well enough to decipher all
> the X locks that I see
> in sp_lock. I see locks for the rows and then I see locks on indid = 2
> which I think is the
> non clustered index.
>
|||On Fri, 29 Sep 2006 07:43:40 -0700, "Dodo Lurker" <none@.noemailplease>
wrote:

>i have a query that deletes a large range of rows but the where clause
>is not indexes
>delete from instructor where lastteachdate < '2005-01-01'
>lastteachdate does not have an index and the instructor table has 100,000
>records.
Why do anything? How long does this take to run? I would expect this
only runs a few times a day and deletes less than one percent of
records, shouldn't take more than a couple of seconds at worst.
Using a cursor is almost always a mistake, but you could do a prequery
and get the unique instructorid and delete by that, but I can't see
that running any faster. With the cursor, yes, you might avoid the
table lock, but I'll bet the total execution would be 10x or 100x
worse.
J.
sql

lock manager question

i have a query that deletes a large range of rows but the where clause
is not indexes
delete from instructor where lastteachdate < '2005-01-01'
lastteachdate does not have an index and the instructor table has 100,000
records.
sp_lock shows a table lock. I understand why. Since there is no index on
the
lastteachdate, it must lock everything to search and delete the correct
rows.
there is an index on the table called instructorid. it is a non-clustered
index. it is
the only index on the table (don't yell at me, i inherited this). I was
told not to
add an index on lastteachdate (i can't go into the details why right now).
my question is, if i write code that (a) select instructorid from
instructor where lastteachdate < '2005-01-01'
(b) deletes each instructor by instructorid in a loop, I think I can have
the lock manager
just use row locks and not a table lock. That would allow inserts and
updates of existing rows
without blocking.
Is this a bad idea? I don't know the locking well enough to decipher all
the X locks that I see
in sp_lock. I see locks for the rows and then I see locks on indid = 2
which I think is the
non clustered index.Do you know how many rows you are likely to delete?
If not too many you could insert the instructorid into a table variable (or
'TABLE) and delete using it
declare @.RowsToDelete TABLE (instructorid int)
insert into @.RowsToDelete
select instructorid
from instructor
where lastteachdate < '2005-01-01'
delete from instructor
where instructorid in (select instructorid from @.RowsToDelete)
or
delete from instructor
from @.RowsToDelete RowsToDelete
where instructor.instructorid = RowToDelete.instructorid
or using a derived table may help
delete from instructor
from (
select instructorid
from instructor
where lastteachdate < '2005-01-01'
) as RowsToDelete
where instructor.instructorid = RowToDelete.instructorid
You could also do it in batches by setting ROWCOUNT and using a while loop.
Break out of the loop when @.@.ROWCOUNT after the delete is zero
eg
declare @.RowC
set ROWCOUNT 50
while (1=1) begin
BEGIN TRANSACTION
delete from instructor
from @.RowsToDelete RowsToDelete
where instructor.instructorid = RowToDelete.instructorid
select @.RowC = @.@.ROWCOUNT
COMMIT TRANSACTION
if @.RowC = 0 BREAK
end
set ROWCOUNT 0
Be aware that SET ROWCOUNT will affect any triggers you have on intructors
Paul
"Dodo Lurker" <none@.noemailplease> wrote in message
news:AIGdnacqdfhqm4DYnZ2dnUVZ_tqdnZ2d@.co
mcast.com...
>i have a query that deletes a large range of rows but the where clause
> is not indexes
> delete from instructor where lastteachdate < '2005-01-01'
> lastteachdate does not have an index and the instructor table has 100,000
> records.
> sp_lock shows a table lock. I understand why. Since there is no index on
> the
> lastteachdate, it must lock everything to search and delete the correct
> rows.
> there is an index on the table called instructorid. it is a non-clustered
> index. it is
> the only index on the table (don't yell at me, i inherited this). I was
> told not to
> add an index on lastteachdate (i can't go into the details why right now).
> my question is, if i write code that (a) select instructorid from
> instructor where lastteachdate < '2005-01-01'
> (b) deletes each instructor by instructorid in a loop, I think I can have
> the lock manager
> just use row locks and not a table lock. That would allow inserts and
> updates of existing rows
> without blocking.
> Is this a bad idea? I don't know the locking well enough to decipher all
> the X locks that I see
> in sp_lock. I see locks for the rows and then I see locks on indid = 2
> which I think is the
> non clustered index.
>|||On Fri, 29 Sep 2006 07:43:40 -0700, "Dodo Lurker" <none@.noemailplease>
wrote:

>i have a query that deletes a large range of rows but the where clause
>is not indexes
>delete from instructor where lastteachdate < '2005-01-01'
>lastteachdate does not have an index and the instructor table has 100,000
>records.
Why do anything? How long does this take to run? I would expect this
only runs a few times a day and deletes less than one percent of
records, shouldn't take more than a couple of seconds at worst.
Using a cursor is almost always a mistake, but you could do a prequery
and get the unique instructorid and delete by that, but I can't see
that running any faster. With the cursor, yes, you might avoid the
table lock, but I'll bet the total execution would be 10x or 100x
worse.
J.

lock manager question

i have a query that deletes a large range of rows but the where clause
is not indexes
delete from instructor where lastteachdate < '2005-01-01'
lastteachdate does not have an index and the instructor table has 100,000
records.
sp_lock shows a table lock. I understand why. Since there is no index on
the
lastteachdate, it must lock everything to search and delete the correct
rows.
there is an index on the table called instructorid. it is a non-clustered
index. it is
the only index on the table (don't yell at me, i inherited this). I was
told not to
add an index on lastteachdate (i can't go into the details why right now).
my question is, if i write code that (a) select instructorid from
instructor where lastteachdate < '2005-01-01'
(b) deletes each instructor by instructorid in a loop, I think I can have
the lock manager
just use row locks and not a table lock. That would allow inserts and
updates of existing rows
without blocking.
Is this a bad idea? I don't know the locking well enough to decipher all
the X locks that I see
in sp_lock. I see locks for the rows and then I see locks on indid = 2
which I think is the
non clustered index.Do you know how many rows you are likely to delete?
If not too many you could insert the instructorid into a table variable (or
'TABLE) and delete using it
declare @.RowsToDelete TABLE (instructorid int)
insert into @.RowsToDelete
select instructorid
from instructor
where lastteachdate < '2005-01-01'
delete from instructor
where instructorid in (select instructorid from @.RowsToDelete)
or
delete from instructor
from @.RowsToDelete RowsToDelete
where instructor.instructorid = RowToDelete.instructorid
or using a derived table may help
delete from instructor
from (
select instructorid
from instructor
where lastteachdate < '2005-01-01'
) as RowsToDelete
where instructor.instructorid = RowToDelete.instructorid
You could also do it in batches by setting ROWCOUNT and using a while loop.
Break out of the loop when @.@.ROWCOUNT after the delete is zero
eg
declare @.RowC
set ROWCOUNT 50
while (1=1) begin
BEGIN TRANSACTION
delete from instructor
from @.RowsToDelete RowsToDelete
where instructor.instructorid = RowToDelete.instructorid
select @.RowC = @.@.ROWCOUNT
COMMIT TRANSACTION
if @.RowC = 0 BREAK
end
set ROWCOUNT 0
Be aware that SET ROWCOUNT will affect any triggers you have on intructors
Paul
"Dodo Lurker" <none@.noemailplease> wrote in message
news:AIGdnacqdfhqm4DYnZ2dnUVZ_tqdnZ2d@.comcast.com...
>i have a query that deletes a large range of rows but the where clause
> is not indexes
> delete from instructor where lastteachdate < '2005-01-01'
> lastteachdate does not have an index and the instructor table has 100,000
> records.
> sp_lock shows a table lock. I understand why. Since there is no index on
> the
> lastteachdate, it must lock everything to search and delete the correct
> rows.
> there is an index on the table called instructorid. it is a non-clustered
> index. it is
> the only index on the table (don't yell at me, i inherited this). I was
> told not to
> add an index on lastteachdate (i can't go into the details why right now).
> my question is, if i write code that (a) select instructorid from
> instructor where lastteachdate < '2005-01-01'
> (b) deletes each instructor by instructorid in a loop, I think I can have
> the lock manager
> just use row locks and not a table lock. That would allow inserts and
> updates of existing rows
> without blocking.
> Is this a bad idea? I don't know the locking well enough to decipher all
> the X locks that I see
> in sp_lock. I see locks for the rows and then I see locks on indid = 2
> which I think is the
> non clustered index.
>|||On Fri, 29 Sep 2006 07:43:40 -0700, "Dodo Lurker" <none@.noemailplease>
wrote:
>i have a query that deletes a large range of rows but the where clause
>is not indexes
>delete from instructor where lastteachdate < '2005-01-01'
>lastteachdate does not have an index and the instructor table has 100,000
>records.
Why do anything? How long does this take to run? I would expect this
only runs a few times a day and deletes less than one percent of
records, shouldn't take more than a couple of seconds at worst.
Using a cursor is almost always a mistake, but you could do a prequery
and get the unique instructorid and delete by that, but I can't see
that running any faster. With the cursor, yes, you might avoid the
table lock, but I'll bet the total execution would be 10x or 100x
worse.
J.

Lock manager out of space

Hi,

I'm getting an error which I'm pretty sure I can avoid for now, but I'd like to understand the underlying issue.

I'm have SQL CE 3.1 on Win XP and am replicating a reasonably large set of data with SQL Server 2005 using merge replication. The initial replication works fine, and brings the SQL CE database to around 800MB. Subsequent delta syncs are also fine. However, if I re-initialise the subscription, it chugs away for a while, grows the local database to just over 1Gb then errors with the following message:

The lock manager has run out of space for additional locks. This can be caused by large transactions, by large sort operations, or by operations where SQL Server Compact Edition creates temporary tables. You cannot increase the lock space.

I can avoid this issue by either deleteing the .sdf file and recreating the subscription from scratch, or by splitting the publication into smaller sets, and re-intialising each one seperately. Obviously SQL CE uses some sort of temporary or "lock" space to manage the re-initialisation. Is, as the error message suggests, there no way to increase this? How much space is there - ie what is the threshold overwhich I need to split up the update operations into multiple steps.

I'm assuming this is a SQL CE limitation, hence posting this here rather than under the replication forum.

Cheers

Looking at http://msdn2.microsoft.com/en-us/library/system.data.sqlserverce.sqlceconnection.connectionstring.aspx

I can see the following setting:

default lock escalation-or-ssceefault lock escalation

The number of locks a transaction will acquire before attempting escalation from row to page, or from page to table. If not specified, the default value is 100.

It's a far shot, but maybe decreasing this number to 10 or lower will help you.

What does your connection string look like, anyway?

|||

Thanks for the suggestion. From our application we don't actually set this value, so I expect it's using the default 100. Our connection string is pretty minimal, and looks like this:

Data Source='filename.sdf';Max Database Size = 2048; Max Buffer Size = 1024;

I've tried testing different values in the default lock escalation setting using the subscription wizard in sql management studio which seems to enforce a minimum value of 50. It doesn't appear to make any difference to the result - I still get the lock manager running out of space.

|||

Received this information through a Device MVP and thought it might be useful for others:

"You hit this issue:

1) If a single transaction is dealing with more than 1 GB of pages.

2) If you are using v3.0 or v3.1

We have extended the limit in v3.5 (available in v3.5 Beta2), and you can now have a one bulk transaction which is updating more than 1 GB of pages."

Also this:

" The old limit is 2^18-1 lock references, now it is 2^32-1 lock references"

Lock manager out of space

Hi,

I'm getting an error which I'm pretty sure I can avoid for now, but I'd like to understand the underlying issue.

I'm have SQL CE 3.1 on Win XP and am replicating a reasonably large set of data with SQL Server 2005 using merge replication. The initial replication works fine, and brings the SQL CE database to around 800MB. Subsequent delta syncs are also fine. However, if I re-initialise the subscription, it chugs away for a while, grows the local database to just over 1Gb then errors with the following message:

The lock manager has run out of space for additional locks. This can be caused by large transactions, by large sort operations, or by operations where SQL Server Compact Edition creates temporary tables. You cannot increase the lock space.

I can avoid this issue by either deleteing the .sdf file and recreating the subscription from scratch, or by splitting the publication into smaller sets, and re-intialising each one seperately. Obviously SQL CE uses some sort of temporary or "lock" space to manage the re-initialisation. Is, as the error message suggests, there no way to increase this? How much space is there - ie what is the threshold overwhich I need to split up the update operations into multiple steps.

I'm assuming this is a SQL CE limitation, hence posting this here rather than under the replication forum.

Cheers

Looking at http://msdn2.microsoft.com/en-us/library/system.data.sqlserverce.sqlceconnection.connectionstring.aspx

I can see the following setting:

default lock escalation-or-ssceefault lock escalation

The number of locks a transaction will acquire before attempting escalation from row to page, or from page to table. If not specified, the default value is 100.

It's a far shot, but maybe decreasing this number to 10 or lower will help you.

What does your connection string look like, anyway?

|||

Thanks for the suggestion. From our application we don't actually set this value, so I expect it's using the default 100. Our connection string is pretty minimal, and looks like this:

Data Source='filename.sdf';Max Database Size = 2048; Max Buffer Size = 1024;

I've tried testing different values in the default lock escalation setting using the subscription wizard in sql management studio which seems to enforce a minimum value of 50. It doesn't appear to make any difference to the result - I still get the lock manager running out of space.

|||

Received this information through a Device MVP and thought it might be useful for others:

"You hit this issue:

1) If a single transaction is dealing with more than 1 GB of pages.

2) If you are using v3.0 or v3.1

We have extended the limit in v3.5 (available in v3.5 Beta2), and you can now have a one bulk transaction which is updating more than 1 GB of pages."

Also this:

" The old limit is 2^18-1 lock references, now it is 2^32-1 lock references"

Lock manager out of space

Hi,

I'm getting an error which I'm pretty sure I can avoid for now, but I'd like to understand the underlying issue.

I'm have SQL CE 3.1 on Win XP and am replicating a reasonably large set of data with SQL Server 2005 using merge replication. The initial replication works fine, and brings the SQL CE database to around 800MB. Subsequent delta syncs are also fine. However, if I re-initialise the subscription, it chugs away for a while, grows the local database to just over 1Gb then errors with the following message:

The lock manager has run out of space for additional locks. This can be caused by large transactions, by large sort operations, or by operations where SQL Server Compact Edition creates temporary tables. You cannot increase the lock space.

I can avoid this issue by either deleteing the .sdf file and recreating the subscription from scratch, or by splitting the publication into smaller sets, and re-intialising each one seperately. Obviously SQL CE uses some sort of temporary or "lock" space to manage the re-initialisation. Is, as the error message suggests, there no way to increase this? How much space is there - ie what is the threshold overwhich I need to split up the update operations into multiple steps.

I'm assuming this is a SQL CE limitation, hence posting this here rather than under the replication forum.

Cheers

Looking at http://msdn2.microsoft.com/en-us/library/system.data.sqlserverce.sqlceconnection.connectionstring.aspx

I can see the following setting:

default lock escalation-or-ssceefault lock escalation

The number of locks a transaction will acquire before attempting escalation from row to page, or from page to table. If not specified, the default value is 100.

It's a far shot, but maybe decreasing this number to 10 or lower will help you.

What does your connection string look like, anyway?

|||

Thanks for the suggestion. From our application we don't actually set this value, so I expect it's using the default 100. Our connection string is pretty minimal, and looks like this:

Data Source='filename.sdf';Max Database Size = 2048; Max Buffer Size = 1024;

I've tried testing different values in the default lock escalation setting using the subscription wizard in sql management studio which seems to enforce a minimum value of 50. It doesn't appear to make any difference to the result - I still get the lock manager running out of space.

|||

Received this information through a Device MVP and thought it might be useful for others:

"You hit this issue:

1) If a single transaction is dealing with more than 1 GB of pages.

2) If you are using v3.0 or v3.1

We have extended the limit in v3.5 (available in v3.5 Beta2), and you can now have a one bulk transaction which is updating more than 1 GB of pages."

Also this:

" The old limit is 2^18-1 lock references, now it is 2^32-1 lock references"

sql

Lock Manager does a dead lock search?

I see the following in the SQL (error) log and am curious as to why it shows
up (repeatedly). Server OS is Windows 2003 x64 Enterprise, SQL Server 2000
SP4. This server uses the Intel EMT processor. We have two other similarly
configured servers and I don't see these mesages on those servers (same
databases, etc.).
Starting deadlock search 34450
0
2005-10-12 09:49:51.31 spid4 Target Resource Owner:
0
2005-10-12 09:49:51.31 spid4 ResType:ExchangeId Stype:'AND' SPID:73
ECID:0 Ec0xBBA39370) Value:0x800ee67c
0
2005-10-12 09:49:51.31 spid4 Node:1 ResType:ExchangeId Stype:'AND'
SPID:73 ECID:0 Ec0xBBA39370) Value:0x800ee67c
0
2005-10-12 09:49:51.31 spid4
0
2005-10-12 09:49:51.31 spid4 End deadlock search 34450 ... a deadlock
was not found.
jl
Sounds like a trace flag has been enabled for that SQL instance.
Perhaps -T1205. Look in the startup parameters for SQL Server or use DBCC
TRACESTATUS(-1) to see all currently enabled trace flags.
"John L" <JohnL@.discussions.microsoft.com> wrote in message
news:A460B54F-CE69-4601-82EC-4EB2806FDF2C@.microsoft.com...
>I see the following in the SQL (error) log and am curious as to why it
>shows
> up (repeatedly). Server OS is Windows 2003 x64 Enterprise, SQL Server
> 2000
> SP4. This server uses the Intel EMT processor. We have two other
> similarly
> configured servers and I don't see these mesages on those servers (same
> databases, etc.).
>
> Starting deadlock search 34450
> 0
> 2005-10-12 09:49:51.31 spid4 Target Resource Owner:
>
> 0
> 2005-10-12 09:49:51.31 spid4 ResType:ExchangeId Stype:'AND' SPID:73
> ECID:0 Ec0xBBA39370) Value:0x800ee67c
> 0
> 2005-10-12 09:49:51.31 spid4 Node:1 ResType:ExchangeId Stype:'AND'
> SPID:73 ECID:0 Ec0xBBA39370) Value:0x800ee67c
> 0
> 2005-10-12 09:49:51.31 spid4
>
> 0
> 2005-10-12 09:49:51.31 spid4 End deadlock search 34450 ... a deadlock
> was not found.
> --
> jl
|||Lori,
We don't have the 1205 flag enabled.
jl
"Lori Clark" wrote:

> Sounds like a trace flag has been enabled for that SQL instance.
> Perhaps -T1205. Look in the startup parameters for SQL Server or use DBCC
> TRACESTATUS(-1) to see all currently enabled trace flags.
> "John L" <JohnL@.discussions.microsoft.com> wrote in message
> news:A460B54F-CE69-4601-82EC-4EB2806FDF2C@.microsoft.com...
>
>

Lock Manager does a dead lock search?

I see the following in the SQL (error) log and am curious as to why it shows
up (repeatedly). Server OS is Windows 2003 x64 Enterprise, SQL Server 2000
SP4. This server uses the Intel EMT processor. We have two other similarly
configured servers and I don't see these mesages on those servers (same
databases, etc.).
Starting deadlock search 34450
0
2005-10-12 09:49:51.31 spid4 Target Resource Owner:
0
2005-10-12 09:49:51.31 spid4 ResType:ExchangeId Stype:'AND' SPID:73
ECID:0 Ec:(0xBBA39370) Value:0x800ee67c
0
2005-10-12 09:49:51.31 spid4 Node:1 ResType:ExchangeId Stype:'AND'
SPID:73 ECID:0 Ec:(0xBBA39370) Value:0x800ee67c
0
2005-10-12 09:49:51.31 spid4
0
2005-10-12 09:49:51.31 spid4 End deadlock search 34450 ... a deadlock
was not found.
--
jlSounds like a trace flag has been enabled for that SQL instance.
Perhaps -T1205. Look in the startup parameters for SQL Server or use DBCC
TRACESTATUS(-1) to see all currently enabled trace flags.
"John L" <JohnL@.discussions.microsoft.com> wrote in message
news:A460B54F-CE69-4601-82EC-4EB2806FDF2C@.microsoft.com...
>I see the following in the SQL (error) log and am curious as to why it
>shows
> up (repeatedly). Server OS is Windows 2003 x64 Enterprise, SQL Server
> 2000
> SP4. This server uses the Intel EMT processor. We have two other
> similarly
> configured servers and I don't see these mesages on those servers (same
> databases, etc.).
>
> Starting deadlock search 34450
> 0
> 2005-10-12 09:49:51.31 spid4 Target Resource Owner:
>
> 0
> 2005-10-12 09:49:51.31 spid4 ResType:ExchangeId Stype:'AND' SPID:73
> ECID:0 Ec:(0xBBA39370) Value:0x800ee67c
> 0
> 2005-10-12 09:49:51.31 spid4 Node:1 ResType:ExchangeId Stype:'AND'
> SPID:73 ECID:0 Ec:(0xBBA39370) Value:0x800ee67c
> 0
> 2005-10-12 09:49:51.31 spid4
>
> 0
> 2005-10-12 09:49:51.31 spid4 End deadlock search 34450 ... a deadlock
> was not found.
> --
> jl|||Lori,
We don't have the 1205 flag enabled.
--
jl
"Lori Clark" wrote:
> Sounds like a trace flag has been enabled for that SQL instance.
> Perhaps -T1205. Look in the startup parameters for SQL Server or use DBCC
> TRACESTATUS(-1) to see all currently enabled trace flags.
> "John L" <JohnL@.discussions.microsoft.com> wrote in message
> news:A460B54F-CE69-4601-82EC-4EB2806FDF2C@.microsoft.com...
> >I see the following in the SQL (error) log and am curious as to why it
> >shows
> > up (repeatedly). Server OS is Windows 2003 x64 Enterprise, SQL Server
> > 2000
> > SP4. This server uses the Intel EMT processor. We have two other
> > similarly
> > configured servers and I don't see these mesages on those servers (same
> > databases, etc.).
> >
> >
> > Starting deadlock search 34450
> >
> > 0
> > 2005-10-12 09:49:51.31 spid4 Target Resource Owner:
> >
> >
> > 0
> > 2005-10-12 09:49:51.31 spid4 ResType:ExchangeId Stype:'AND' SPID:73
> > ECID:0 Ec:(0xBBA39370) Value:0x800ee67c
> >
> > 0
> > 2005-10-12 09:49:51.31 spid4 Node:1 ResType:ExchangeId Stype:'AND'
> > SPID:73 ECID:0 Ec:(0xBBA39370) Value:0x800ee67c
> >
> > 0
> > 2005-10-12 09:49:51.31 spid4
> >
> >
> > 0
> > 2005-10-12 09:49:51.31 spid4 End deadlock search 34450 ... a deadlock
> > was not found.
> >
> > --
> > jl
>
>

Lock Manager does a dead lock search?

I see the following in the SQL (error) log and am curious as to why it shows
up (repeatedly). Server OS is Windows 2003 x64 Enterprise, SQL Server 2000
SP4. This server uses the Intel EMT processor. We have two other similarly
configured servers and I don't see these mesages on those servers (same
databases, etc.).
Starting deadlock search 34450
0
2005-10-12 09:49:51.31 spid4 Target Resource Owner:
0
2005-10-12 09:49:51.31 spid4 ResType:ExchangeId Stype:'AND' SPID:73
ECID:0 Ec0xBBA39370) Value:0x800ee67c
0
2005-10-12 09:49:51.31 spid4 Node:1 ResType:ExchangeId Stype:'AND'
SPID:73 ECID:0 Ec0xBBA39370) Value:0x800ee67c
0
2005-10-12 09:49:51.31 spid4
0
2005-10-12 09:49:51.31 spid4 End deadlock search 34450 ... a deadlock
was not found.
jlSounds like a trace flag has been enabled for that SQL instance.
Perhaps -T1205. Look in the startup parameters for SQL Server or use DBCC
TRACESTATUS(-1) to see all currently enabled trace flags.
"John L" <JohnL@.discussions.microsoft.com> wrote in message
news:A460B54F-CE69-4601-82EC-4EB2806FDF2C@.microsoft.com...
>I see the following in the SQL (error) log and am curious as to why it
>shows
> up (repeatedly). Server OS is Windows 2003 x64 Enterprise, SQL Server
> 2000
> SP4. This server uses the Intel EMT processor. We have two other
> similarly
> configured servers and I don't see these mesages on those servers (same
> databases, etc.).
>
> Starting deadlock search 34450
> 0
> 2005-10-12 09:49:51.31 spid4 Target Resource Owner:
>
> 0
> 2005-10-12 09:49:51.31 spid4 ResType:ExchangeId Stype:'AND' SPID:73
> ECID:0 Ec0xBBA39370) Value:0x800ee67c
> 0
> 2005-10-12 09:49:51.31 spid4 Node:1 ResType:ExchangeId Stype:'AND'
> SPID:73 ECID:0 Ec0xBBA39370) Value:0x800ee67c
> 0
> 2005-10-12 09:49:51.31 spid4
>
> 0
> 2005-10-12 09:49:51.31 spid4 End deadlock search 34450 ... a deadlock
> was not found.
> --
> jl|||Lori,
We don't have the 1205 flag enabled.
jl
"Lori Clark" wrote:

> Sounds like a trace flag has been enabled for that SQL instance.
> Perhaps -T1205. Look in the startup parameters for SQL Server or use DBC
C
> TRACESTATUS(-1) to see all currently enabled trace flags.
> "John L" <JohnL@.discussions.microsoft.com> wrote in message
> news:A460B54F-CE69-4601-82EC-4EB2806FDF2C@.microsoft.com...
>
>

Lock Information

I need some help in interpreting the information displayed under
Management -> Current Activity -> Locks / Process ID in Enterprise
Manager.
There seems to be a number of locks against the "Water.dbo.sched_lock"
object. The values in the Index column are different for the different
rows: "Gazetteer", "Employees" and "pk_workbank...".
The strange thing is that these are not indexes of the Sched_Lock
table. Gazetteer and Employees refer to 2 different tables within the
database.
Why are they being displayed against a lock which is supposed to be
held against the "sched_lock" table?No ideas, anyone?|||I never use that to monitor locks. A few years ago there
were posts like yours so I played around with viewing locks
from Enterprise Manager and found that it's pretty easy to
get bogus, useless info. I specifically remember seeing the
wrong table references. And then if you look at the stored
procedure used by Enterprise Manager,
sp_MSset_currency_activity, you can see how the results
wouldn't be too reliable.
Use sp_lock, sp_who2 to monitor things. Or even better is
the stored procedure Erland wrote:
http://www.sommarskog.se/sqlutil/aba_lockinfo.html
-Sue
On 7 Sep 2006 09:39:26 -0700, "Paul H"
<del.monte@.ntlworld.com> wrote:
>I need some help in interpreting the information displayed under
>Management -> Current Activity -> Locks / Process ID in Enterprise
>Manager.
>There seems to be a number of locks against the "Water.dbo.sched_lock"
>object. The values in the Index column are different for the different
>rows: "Gazetteer", "Employees" and "pk_workbank...".
>The strange thing is that these are not indexes of the Sched_Lock
>table. Gazetteer and Employees refer to 2 different tables within the
>database.
>Why are they being displayed against a lock which is supposed to be
>held against the "sched_lock" table?|||Thanks very much Sue - it's a pain that a part of Enterprise Manager
itself would be unreliable.
I've had a look at sp_MSset_current_activity, and I've seen where the
Index column is likely to get mixed up. I can't see why the Table
column would be unreliable though - it seems like a fair bet to me that
the data in the table column is correct, and the locks are being held
against the "sched_lock" table. Would you agree?|||In your case, yes.
In terms of the other areas of unreliability in that stored
procedure, look at the isolation levels being used.
Many GUI tools for databases are have issues - not just
Microsoft's. You're often better off just knowing the SQL
commands to execute. It generally gives you more flexibility
in writing some of your own scripts to retrieve the data you
want, with the information you need or care about.
-Sue
On 11 Sep 2006 08:21:22 -0700, "Paul H"
<del.monte@.ntlworld.com> wrote:
>Thanks very much Sue - it's a pain that a part of Enterprise Manager
>itself would be unreliable.
>I've had a look at sp_MSset_current_activity, and I've seen where the
>Index column is likely to get mixed up. I can't see why the Table
>column would be unreliable though - it seems like a fair bet to me that
>the data in the table column is correct, and the locks are being held
>against the "sched_lock" table. Would you agree?|||Indeed.
Thanks very much.sql

Lock Down Enterprise Manager...

I am looking for any information on locking down EM, such as not allowing
access to certain areas (Security Folder, Management, etc), as well as
securing it so that they cannot even see certain DB's in the Databases
Folder. The security would ideally be tied into their SQL acct info, but NT
perms would work as well.
Can anyone provide some good primer info on this topic? Any hints or advice
would be greatly appreciated.
You can't really. There is no real metadata security in SQL2000 so the
obvious example is that a login with access to only 1 database can see all
databases in EM. Just because they can see stuff in EM doesn't mean they can
do anything. What you can do in EM is all controlled by what permissions you
have in SQL but just because they can see an object in EM doesn't mean they
can do anything to it. If a user has access to only one database they can't
do anything in EM other than in that one database.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Rathael1" <Rathael1@.discussions.microsoft.com> wrote in message
news:FA39C12F-ACF6-4465-B464-3C66A53A65E3@.microsoft.com...
>I am looking for any information on locking down EM, such as not allowing
> access to certain areas (Security Folder, Management, etc), as well as
> securing it so that they cannot even see certain DB's in the Databases
> Folder. The security would ideally be tied into their SQL acct info, but
> NT
> perms would work as well.
> Can anyone provide some good primer info on this topic? Any hints or
> advice
> would be greatly appreciated.
|||That is what I was afraid of, and was pretty much what I have discovered
while researching this on my own. The example you just gave is exactly what
we're trying to prevent.
I am looking at some 3rd party solutions (AppSense, etc) that may help, just
in case anyone is running into anything similar. Also, I thought of creating
a web interface to EM, where we could control and filter the content
programatially, but that would be a fairly more involved solution than
getting something off the shelf.
Thanks for your response...if anyone else has any ideas, I'm all ears!
JD, MCSE, MCDBA
"Jasper Smith" wrote:

> You can't really. There is no real metadata security in SQL2000 so the
> obvious example is that a login with access to only 1 database can see all
> databases in EM. Just because they can see stuff in EM doesn't mean they can
> do anything. What you can do in EM is all controlled by what permissions you
> have in SQL but just because they can see an object in EM doesn't mean they
> can do anything to it. If a user has access to only one database they can't
> do anything in EM other than in that one database.
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Rathael1" <Rathael1@.discussions.microsoft.com> wrote in message
> news:FA39C12F-ACF6-4465-B464-3C66A53A65E3@.microsoft.com...
>
>
|||What exactly is your concern with the behaviour of EM ? Is this for some
sort of hosting scenario ?
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"rathael1" <rathael1@.discussions.microsoft.com> wrote in message
news:04AFBDCC-4D63-4931-973E-2422E4396A30@.microsoft.com...[vbcol=seagreen]
> That is what I was afraid of, and was pretty much what I have discovered
> while researching this on my own. The example you just gave is exactly
> what
> we're trying to prevent.
> I am looking at some 3rd party solutions (AppSense, etc) that may help,
> just
> in case anyone is running into anything similar. Also, I thought of
> creating
> a web interface to EM, where we could control and filter the content
> programatially, but that would be a fairly more involved solution than
> getting something off the shelf.
> Thanks for your response...if anyone else has any ideas, I'm all ears!
> JD, MCSE, MCDBA
> "Jasper Smith" wrote:
|||Have a look at SQL Server Web Data Administrator
http://www.microsoft.com/downloads/d...displaylang=en
This seems to only show databases users have access to. It is extensible and
has documentation on the SqlAdmin class which you can use
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"rathael1" <rathael1@.discussions.microsoft.com> wrote in message
news:04AFBDCC-4D63-4931-973E-2422E4396A30@.microsoft.com...[vbcol=seagreen]
> That is what I was afraid of, and was pretty much what I have discovered
> while researching this on my own. The example you just gave is exactly
> what
> we're trying to prevent.
> I am looking at some 3rd party solutions (AppSense, etc) that may help,
> just
> in case anyone is running into anything similar. Also, I thought of
> creating
> a web interface to EM, where we could control and filter the content
> programatially, but that would be a fairly more involved solution than
> getting something off the shelf.
> Thanks for your response...if anyone else has any ideas, I'm all ears!
> JD, MCSE, MCDBA
> "Jasper Smith" wrote:
|||rathael1 typed:
> Also, I
> thought of creating a web interface to EM, where we could control and
> filter the content programatially, but that would be a fairly more
> involved solution than getting something off the shelf.
You should have a look at myLittleAdmin on
http://www.mylittletools.net/mla_sql
Live demo on http://www.mylittletools.net/livedemo/mla_sql
You can also download a lite edition
Hope this helps
Best regards
Elian Chrebor
// myLittleTools.net : web-based applications for ASP developers
// myLittleAdmin spotlight is available on
// http://www.mylittletools.net/spotlight
// webmaster@.mylittletools.net
[vbcol=seagreen]
> Thanks for your response...if anyone else has any ideas, I'm all ears!
> JD, MCSE, MCDBA
> "Jasper Smith" wrote:

lock blocks under SQLServer.Memory Manager

I'm trying to use the performance monitor on Win2K Adv Server to
troubleshoot performance issues I have on a MSSQL box.
I was trying to find out if there was a lot of blocking on my databases
creating the performance problems.
I starting tracing the counter called "lock Blocks" under "SQLServer.Memory
Manager" and found out that the average value was 4000 with picks at 8000.
I'm not too sure if I understand the exact meaning of this counter. Can
someone explain it to me and tell me if the values I'm getting are bad or
normal?
Thank youHow many rows do you get when you execute this:
select * from master..sysprocesses
where blocked > 0
I am a bit puzzled as to what the figure in lock blocks
actually means. I created a lock block and in perfmon I
got a figure of 1013. I will investigate further what this
figure means.
If you are concerned about blocking locks, then use the
above query and sp_lock to find out what is blocking. You
can also use DBCC INPUTBUFFER (SPID) to find out what was
submitted that caused the block.
Mark Allison
SQL Server MVP
>--Original Message--
>I'm trying to use the performance monitor on Win2K Adv
Server to
>troubleshoot performance issues I have on a MSSQL box.
>I was trying to find out if there was a lot of blocking
on my databases
>creating the performance problems.
>I starting tracing the counter called "lock Blocks"
under "SQLServer.Memory
>Manager" and found out that the average value was 4000
with picks at 8000.
>I'm not too sure if I understand the exact meaning of
this counter. Can
>someone explain it to me and tell me if the values I'm
getting are bad or
>normal?
>Thank you
>
>.
>|||i ran your query and I didn't get any rows back.. I guess blocking is not my
problem then...
thank you Mark
"Mark Allison" <mark@.allisonmitchell.c0m> wrote in message
news:080c01c3b427$576ab100$a001280a@.phx.gbl...
> How many rows do you get when you execute this:
> select * from master..sysprocesses
> where blocked > 0
> I am a bit puzzled as to what the figure in lock blocks
> actually means. I created a lock block and in perfmon I
> got a figure of 1013. I will investigate further what this
> figure means.
> If you are concerned about blocking locks, then use the
> above query and sp_lock to find out what is blocking. You
> can also use DBCC INPUTBUFFER (SPID) to find out what was
> submitted that caused the block.
> Mark Allison
> SQL Server MVP
> >--Original Message--
> >I'm trying to use the performance monitor on Win2K Adv
> Server to
> >troubleshoot performance issues I have on a MSSQL box.
> >I was trying to find out if there was a lot of blocking
> on my databases
> >creating the performance problems.
> >I starting tracing the counter called "lock Blocks"
> under "SQLServer.Memory
> >Manager" and found out that the average value was 4000
> with picks at 8000.
> >I'm not too sure if I understand the exact meaning of
> this counter. Can
> >someone explain it to me and tell me if the values I'm
> getting are bad or
> >normal?
> >
> >Thank you
> >
> >
> >.
> >

Lock /objects in Enterprise manager**

Hi
when I come to lock/objects branch in enterprise manager I find out so many
tables and views name there ,does it mean that all these are locked? or does
it mean all these are using by some users,but we can use it too?
any help would be thankfulYou can get more information with sp_who and sp_who2 system stored procs
Dishan Fernando
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||http://msdn.microsoft.com/library/d...y/en-us/adminsq
l/ad_perfmon_6ofn.asp
DishanF
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!

Local SQL Service Not Starting but SQL Server is running?

Hello there,

This is a bit of strange one.
My local version of SQL Server fails to start when I boot yet if I
start Enterprise Manager its started and working fine. ISQL or Query
Analyzer is also not working.

If I try and register the server by another SQL Server is not there. Me
thinks that the named pipes might be a bit broken but would appreciate
any advise from people.

Thanks

GintersCouple of thoughts. One the service is not starting until you start EM and
click on the server? By default when a server is registered in EM it sets
the checkbox for automatically start. Right click the server in EM, choose
Edit server registration and look at the bottom checkbox.

Another is only shared memory is working and you are trying to connect from
isql (osql) and QA remotely? In this case check the SQL server error log
for what protocols SQL started and verify that on the client that these
protocols are activated in the client network utility.

If this is a default installation then shared memory, named pipes, and tcp
are all active. Is the MSSQLServer service set to start automatically?

"Ginters" <jpmcginty@.talk21.com> wrote in message
news:1108124975.337210.24500@.f14g2000cwb.googlegro ups.com...
> Hello there,
> This is a bit of strange one.
> My local version of SQL Server fails to start when I boot yet if I
> start Enterprise Manager its started and working fine. ISQL or Query
> Analyzer is also not working.
> If I try and register the server by another SQL Server is not there. Me
> thinks that the named pipes might be a bit broken but would appreciate
> any advise from people.
> Thanks
> Ginters|||Ginters (jpmcginty@.talk21.com) writes:
> This is a bit of strange one.
> My local version of SQL Server fails to start when I boot yet

Do you get any error message?

Do you have the service set to start automatically?

> if I start Enterprise Manager its started and working fine. ISQL or Query
> Analyzer is also not working.

Even if SQL Server is running?

Which operating system and which version of SQL Server are you using?

> If I try and register the server by another SQL Server is not there. Me
> thinks that the named pipes might be a bit broken but would appreciate
> any advise from people.

Which protcols have you enabled in the Client Network Utility? Is
shared memory on?

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

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