How to make corrupted DB working

Hi all!
About a month ago I had problems with SQL server, had to stop the sql server
process. After starting it I had to recover master DB. Everything was
looking good. But today I tried to grant DB access and found
sp_grantdbaccess missing.
After some experiments I found out a strange problem: I cannot create ANY
procedure on master DB (I have enough rights :) ).
Example: running script
CREATE PROC xxx AS SELECT 1
returns "There is already an object named 'xxx' in the database.", xxx is
any name.
This happens even on another SQL Server on db, that we backup/restored from
corrupted master db.
Any suggestions are welcomeThe correct way is to restore the last valid backup. If you don't have it,
you can try to rebuild the master database using Rebuildm.Exe command propt
utility (check for the syntax in Books OnLine). Then you will have to
recreate logins (maybe you can still generate scripts, so this task can be
easier?), attach all of the databases (check the sp_attach_db system SP) and
potentialy map logins to database users again (check the
sp_change_users_login for SQL logins and
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q298/8/97.asp&NoWebContent=1
for Windows logins).
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Sergei Almazov" <almazik@.ukr.net> wrote in message
news:e7v2x8teDHA.1820@.TK2MSFTNGP10.phx.gbl...
> Hi all!
> About a month ago I had problems with SQL server, had to stop the sql
server
> process. After starting it I had to recover master DB. Everything was
> looking good. But today I tried to grant DB access and found
> sp_grantdbaccess missing.
> After some experiments I found out a strange problem: I cannot create ANY
> procedure on master DB (I have enough rights :) ).
> Example: running script
> CREATE PROC xxx AS SELECT 1
> returns "There is already an object named 'xxx' in the database.", xxx is
> any name.
> This happens even on another SQL Server on db, that we backup/restored
from
> corrupted master db.
> Any suggestions are welcome
>

how to lock the store procedure and allow one process to acces it at a time

Hello:

I run one process that calls the following the store procedure and
works fine.

create PROCEDURE sp_GetHostSequenceNum
AS
BEGIN

SELECT int_parameter_dbf + 1
FROM system_parameter_dbt
WHERE parameter_name_dbf = 'seqNum'

UPDATE system_parameter_dbt
SET int_parameter_dbf = int_parameter_dbf + 1
WHERE parameter_name_dbf = 'seqNum'

END
GO

If I run two processes that call the above store procedure, I might
occasionally get the dirty data of int_parameter_dbt. I guess that is
caused by two processes accessing to the same resource simultaneously.
Is there any way to lock the store procedure call from MSSQL Server
and allow only one process to access it at a time?

Thanks for help.

Best Jin"Jin" <texlqj@.hotmail.com> wrote in message
news:82b49cd5.0401131104.7c12efc5@.posting.google.c om...
> Hello:
> I run one process that calls the following the store procedure and
> works fine.
> create PROCEDURE sp_GetHostSequenceNum
> AS
> BEGIN
> SELECT int_parameter_dbf + 1
> FROM system_parameter_dbt
> WHERE parameter_name_dbf = 'seqNum'
> UPDATE system_parameter_dbt
> SET int_parameter_dbf = int_parameter_dbf + 1
> WHERE parameter_name_dbf = 'seqNum'
> END
> GO
>
> If I run two processes that call the above store procedure, I might
> occasionally get the dirty data of int_parameter_dbt. I guess that is
> caused by two processes accessing to the same resource simultaneously.
> Is there any way to lock the store procedure call from MSSQL Server
> and allow only one process to access it at a time?
> Thanks for help.
> Best Jin

Here is one possible approach, using an UPDATE syntax specific to MSSQL:

create PROCEDURE sp_GetHostSequenceNum
AS
BEGIN

declare @.val int

UPDATE system_parameter_dbt
SET @.val = int_parameter_dbf = int_parameter_dbf + 1
WHERE parameter_name_dbf = 'seqNum'

select @.val

END
GO

Alternatively, you can use a locking hint:

create PROCEDURE sp_GetHostSequenceNum
AS
BEGIN

begin tran

SELECT int_parameter_dbf + 1
FROM system_parameter_dbt with (UPDLOCK)
WHERE parameter_name_dbf = 'seqNum'

UPDATE system_parameter_dbt
SET int_parameter_dbf = int_parameter_dbf + 1
WHERE parameter_name_dbf = 'seqNum'

commit

END
GO

Simon|||Sure. You could do it transactionally at serializable isolation level...
Joe

Jin wrote:

> Hello:
> I run one process that calls the following the store procedure and
> works fine.
> create PROCEDURE sp_GetHostSequenceNum
> AS
> BEGIN
> SELECT int_parameter_dbf + 1
> FROM system_parameter_dbt
> WHERE parameter_name_dbf = 'seqNum'
> UPDATE system_parameter_dbt
> SET int_parameter_dbf = int_parameter_dbf + 1
> WHERE parameter_name_dbf = 'seqNum'
> END
> GO
>
> If I run two processes that call the above store procedure, I might
> occasionally get the dirty data of int_parameter_dbt. I guess that is
> caused by two processes accessing to the same resource simultaneously.
> Is there any way to lock the store procedure call from MSSQL Server
> and allow only one process to access it at a time?
> Thanks for help.
> Best Jin

How to limit a db owners cpu/memory usage

Hi there,
Is there a possibility that i can limit a user's cpu/memory usage so that if this user (the database) generates a report, this process won't hang up the whole sql server ?
thanks in advance
edgar

Well ...

Maybe the time to optimize you report query has come?

Considering the question, answer is no.

|||

Not exactly. You can prevent a query from running by setting Query governor cost limit, but this will stop all queries with a cost above the specified threshold from running at all.

There is no way to say allow this query to run, but limit it to use x% of y resources.

How to Kill Process - Sleeping, Awaiting Command

Hi,
Any help will me be much apprecaited. Our SQL Server 2000
has many processing as Awaiting Command, Sleeping which
we can't seen to kill even server restart doesn't get rid
of them.
How do I kill them?
Hw do I avoid process going in that state in the future?
All these processes below to a user name which we use to
run some stored procedure.
Please also email your reply to terryb@.tisc.edu.au.
Thank you
TerryTerry
Please refer to KILL command on BOL
"Terry Bhatti" <terryb@.tisc.edu.au> wrote in message
news:01cf01c37120$72997e60$a001280a@.phx.gbl...
> Hi,
> Any help will me be much apprecaited. Our SQL Server 2000
> has many processing as Awaiting Command, Sleeping which
> we can't seen to kill even server restart doesn't get rid
> of them.
> How do I kill them?
> Hw do I avoid process going in that state in the future?
> All these processes below to a user name which we use to
> run some stored procedure.
> Please also email your reply to terryb@.tisc.edu.au.
> Thank you
> Terry
>|||Terry,
When you say you can't seem to kill them, are you referring to the KILL
command failing?
Are these connections autostarting when you start the SQL Server? How is it
that the problem is still there after a server restart? Try using DBCC
INPUTBUFFER(spid) to see if you can figure out what is going on.
Awaiting Command and Sleeping are not pernicious states for a connection to
be in.
Russell Fields
"Terry Bhatti" <terryb@.tisc.edu.au> wrote in message
news:01cf01c37120$72997e60$a001280a@.phx.gbl...
> Hi,
> Any help will me be much apprecaited. Our SQL Server 2000
> has many processing as Awaiting Command, Sleeping which
> we can't seen to kill even server restart doesn't get rid
> of them.
> How do I kill them?
> Hw do I avoid process going in that state in the future?
> All these processes below to a user name which we use to
> run some stored procedure.
> Please also email your reply to terryb@.tisc.edu.au.
> Thank you
> Terry
>

How to KILL a Suspended Sys Process?

I have a suspended process that sits in running processes returned by SP_WHO
for 3 days already.
SPID:10
Status: SUSPENDED
Login Name: sa
DB NAME: master
Command:CHECKPOINT
CPU Time: 2625937
DiskIO: 358622
An attempt to "kill 10 " failed saying that only user processes can be
killed.
Rebooting is not an option since it is a production server.
Is there anything else i can do to kill this process without re-starting the
server?
The job does not affect the performance of the server, but all attempts to
updatestats and backup database fail, and i am 100% sure that this is
because of this process.
Any help please.
Thank you.
low numbered spids are SYSTEM processes. You cannot (for obvious reasons)
KILL them.
What is it that makes you 100% think checkpoint thread is the root of your
problems?
Have you checked the sql error log for messages related to checkpointing?
TheSQLGuru
President
Indicium Resources, Inc.
"Michael Kansky" <mike@.zazasoftware.com> wrote in message
news:u$zA7RFgHHA.284@.TK2MSFTNGP05.phx.gbl...
>I have a suspended process that sits in running processes returned by
>SP_WHO for 3 days already.
> SPID:10
> Status: SUSPENDED
> Login Name: sa
> DB NAME: master
> Command:CHECKPOINT
> CPU Time: 2625937
> DiskIO: 358622
> An attempt to "kill 10 " failed saying that only user processes can be
> killed.
> Rebooting is not an option since it is a production server.
> Is there anything else i can do to kill this process without re-starting
> the server?
> The job does not affect the performance of the server, but all attempts to
> updatestats and backup database fail, and i am 100% sure that this is
> because of this process.
> Any help please.
> Thank you.
>
|||On Apr 16, 2:24 pm, "Michael Kansky" <m...@.zazasoftware.com> wrote:
> I have a suspended process that sits in running processes returned by SP_WHO
> for 3 days already.
> SPID:10
> Status: SUSPENDED
> Login Name: sa
> DB NAME: master
> Command:CHECKPOINT
> CPU Time: 2625937
> DiskIO: 358622
> An attempt to "kill 10 " failed saying that only user processes can be
> killed.
> Rebooting is not an option since it is a production server.
> Is there anything else i can do to kill this process without re-starting the
> server?
> The job does not affect the performance of the server, but all attempts to
> updatestats and backup database fail, and i am 100% sure that this is
> because of this process.
> Any help please.
> Thank you.
the spids between 0 and 50 are system processes not user processes. If
you don't know what is going on, you can always find a down time and
stop/start the MSSQL service. This will kill and restart all system
processes.
Kristina

How to KILL a Suspended Sys Process?

I have a suspended process that sits in running processes returned by SP_WHO
for 3 days already.
SPID:10
Status: SUSPENDED
Login Name: sa
DB NAME: master
Command:CHECKPOINT
CPU Time: 2625937
DiskIO: 358622
An attempt to "kill 10 " failed saying that only user processes can be
killed.
Rebooting is not an option since it is a production server.
Is there anything else i can do to kill this process without re-starting the
server?
The job does not affect the performance of the server, but all attempts to
updatestats and backup database fail, and i am 100% sure that this is
because of this process.
Any help please.
Thank you.low numbered spids are SYSTEM processes. You cannot (for obvious reasons)
KILL them.
What is it that makes you 100% think checkpoint thread is the root of your
problems?
Have you checked the sql error log for messages related to checkpointing?
TheSQLGuru
President
Indicium Resources, Inc.
"Michael Kansky" <mike@.zazasoftware.com> wrote in message
news:u$zA7RFgHHA.284@.TK2MSFTNGP05.phx.gbl...
>I have a suspended process that sits in running processes returned by
>SP_WHO for 3 days already.
> SPID:10
> Status: SUSPENDED
> Login Name: sa
> DB NAME: master
> Command:CHECKPOINT
> CPU Time: 2625937
> DiskIO: 358622
> An attempt to "kill 10 " failed saying that only user processes can be
> killed.
> Rebooting is not an option since it is a production server.
> Is there anything else i can do to kill this process without re-starting
> the server?
> The job does not affect the performance of the server, but all attempts to
> updatestats and backup database fail, and i am 100% sure that this is
> because of this process.
> Any help please.
> Thank you.
>|||On Apr 16, 2:24 pm, "Michael Kansky" <m...@.zazasoftware.com> wrote:
> I have a suspended process that sits in running processes returned by SP_W
HO
> for 3 days already.
> SPID:10
> Status: SUSPENDED
> Login Name: sa
> DB NAME: master
> Command:CHECKPOINT
> CPU Time: 2625937
> DiskIO: 358622
> An attempt to "kill 10 " failed saying that only user processes can be
> killed.
> Rebooting is not an option since it is a production server.
> Is there anything else i can do to kill this process without re-starting t
he
> server?
> The job does not affect the performance of the server, but all attempts to
> updatestats and backup database fail, and i am 100% sure that this is
> because of this process.
> Any help please.
> Thank you.
the spids between 0 and 50 are system processes not user processes. If
you don't know what is going on, you can always find a down time and
stop/start the MSSQL service. This will kill and restart all system
processes.
Kristina

How to KILL a Suspended Sys Process?

I have a suspended process that sits in running processes returned by SP_WHO
for 3 days already.
SPID:10
Status: SUSPENDED
Login Name: sa
DB NAME: master
Command:CHECKPOINT
CPU Time: 2625937
DiskIO: 358622
An attempt to "kill 10 " failed saying that only user processes can be
killed.
Rebooting is not an option since it is a production server.
Is there anything else i can do to kill this process without re-starting the
server?
The job does not affect the performance of the server, but all attempts to
updatestats and backup database fail, and i am 100% sure that this is
because of this process.
Any help please.
Thank you.low numbered spids are SYSTEM processes. You cannot (for obvious reasons)
KILL them.
What is it that makes you 100% think checkpoint thread is the root of your
problems?
Have you checked the sql error log for messages related to checkpointing?
--
TheSQLGuru
President
Indicium Resources, Inc.
"Michael Kansky" <mike@.zazasoftware.com> wrote in message
news:u$zA7RFgHHA.284@.TK2MSFTNGP05.phx.gbl...
>I have a suspended process that sits in running processes returned by
>SP_WHO for 3 days already.
> SPID:10
> Status: SUSPENDED
> Login Name: sa
> DB NAME: master
> Command:CHECKPOINT
> CPU Time: 2625937
> DiskIO: 358622
> An attempt to "kill 10 " failed saying that only user processes can be
> killed.
> Rebooting is not an option since it is a production server.
> Is there anything else i can do to kill this process without re-starting
> the server?
> The job does not affect the performance of the server, but all attempts to
> updatestats and backup database fail, and i am 100% sure that this is
> because of this process.
> Any help please.
> Thank you.
>|||On Apr 16, 2:24 pm, "Michael Kansky" <m...@.zazasoftware.com> wrote:
> I have a suspended process that sits in running processes returned by SP_WHO
> for 3 days already.
> SPID:10
> Status: SUSPENDED
> Login Name: sa
> DB NAME: master
> Command:CHECKPOINT
> CPU Time: 2625937
> DiskIO: 358622
> An attempt to "kill 10 " failed saying that only user processes can be
> killed.
> Rebooting is not an option since it is a production server.
> Is there anything else i can do to kill this process without re-starting the
> server?
> The job does not affect the performance of the server, but all attempts to
> updatestats and backup database fail, and i am 100% sure that this is
> because of this process.
> Any help please.
> Thank you.
the spids between 0 and 50 are system processes not user processes. If
you don't know what is going on, you can always find a down time and
stop/start the MSSQL service. This will kill and restart all system
processes.
Kristina

How to kill a process....

Hi there.
I've got a number of processes that are stuck on my server and are locking
up a table or two and I'd like to kill them. Unfortunately, after trying to
"Kill process" in SQL ENterprise manager, the processes involved are still
there with the following details:
Status: runnable
Command: KILLED/ROLLBACK (see I tried to kill them earlier)
Application: SQL Query analyser (although this instance of Query analyser
has since been closed)
Any ideas how to get rid of them?...len wrote:
> I've got a number of processes that are stuck on my server and are locking
> up a table or two and I'd like to kill them. Unfortunately, after trying t
o
> "Kill process" in SQL ENterprise manager, the processes involved are still
> there with the following details:
When you kill a process in SQL it has to perform a rollback on the
process. This means that if this process was in the middle of updating 1
million records, it would have to roll back all of those records to the
state before the process started.
Aaron Weiker
http://aaronweiker.com/
http://www.sqlprogrammer.org/

How to kill a process

Our application uses SQL 2000 for back end and Access 2003 mdb for front end
and uses ODBC to link together.
One of our users appended data to one table. Because inserted duplicate data
in primary key. The user ignored system warning message and just turned off
her computer. While the user turned on her computer again, the table could
not open it.
We checked SQL 2000 server, the locking process was found in process
information, locks/process ID and lock/object in management. We disconnected
all front end sides application, then tried to kill the process using SQL
server Enterprise Manager console. But it was failed.
Would you help me how to kill the process then unlock this object?
Thanks
When you kill a process that has a transaction open, that transaction has to
be rolled back before the process is completely killed. If it is a large
transaction, it can take a long time for the transaction to roll back. You
can check in the Current Activity pane in Enterprise Manager what the status
is of the process. It should show up as KILLED/ROLLBACK. You can also get
the status of the process with KILL <process id> WITH STATUSONLY in Query
Analyzer. You can find the process id in the Current Activity in Enterprise
Manager.
Jacco Schalkwijk
SQL Server MVP
"NzCharlie" <NzCharlie@.discussions.microsoft.com> wrote in message
news:131B6064-D73D-432C-96C1-CF58C90BD0C8@.microsoft.com...
> Our application uses SQL 2000 for back end and Access 2003 mdb for front
> end
> and uses ODBC to link together.
> One of our users appended data to one table. Because inserted duplicate
> data
> in primary key. The user ignored system warning message and just turned
> off
> her computer. While the user turned on her computer again, the table could
> not open it.
> We checked SQL 2000 server, the locking process was found in process
> information, locks/process ID and lock/object in management. We
> disconnected
> all front end sides application, then tried to kill the process using SQL
> server Enterprise Manager console. But it was failed.
> Would you help me how to kill the process then unlock this object?
> Thanks
>
|||"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:%23AuJx9K3EHA.3820@.TK2MSFTNGP11.phx.gbl...
> When you kill a process that has a transaction open, that transaction has
to
> be rolled back before the process is completely killed. If it is a large
> transaction, it can take a long time for the transaction to roll back. You
> can check in the Current Activity pane in Enterprise Manager what the
status
> is of the process. It should show up as KILLED/ROLLBACK. You can also get
> the status of the process with KILL <process id> WITH STATUSONLY in Query
> Analyzer. You can find the process id in the Current Activity in
Enterprise
> Manager.
>
What he's hitting up against here I bet though is a "bug" I've seen before
where if the client closes improperly, it can be impossible to kill the
connection.
I think SQL SP1 still had this problem but that it's fixed in SP3a.
[vbcol=seagreen]
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "NzCharlie" <NzCharlie@.discussions.microsoft.com> wrote in message
> news:131B6064-D73D-432C-96C1-CF58C90BD0C8@.microsoft.com...
could[vbcol=seagreen]
SQL
>