Andrew Drake wrote:
> Dear all,
> A developer intended to alter a view in a production database. They issued
> an ALTER VIEW command and, because of some reason, the process hung up.
> In the EM, if I go to Management --> Current Activity --> Process Info I c
an
> see that process as spid 160, the statement is "ALTER VIEW..."
> Any help / ideas / suggestions would be greatly appreciated.
>
Does the view refer to any linked servers? Can you post the query that
makes up the view?Dear all,
From time to time an SQL process ('spid') hangs up in our production SQL
server.
Then we try to kill it using KILL command.
And quite often the KILL never ends.
If I call (spid to kill is 160):
KILL 160 WITH STATUSONLY
the result is:
SPID 160: transaction rollback in progress. Estimated rollback completion:
100%. Estimated time remaining: 0 seconds.
and that result lasts forever (i.e. a couple of days until we restart the
server).
That is quite troublesome as the server is quite heavily used and restarting
it always interferes with normal work of users.
Is there any way to REALLY kill the process?
I guess my problems are related to locking issues, I looked up the
newsgroups but couldn't figure out any feasible solution but restart the
server.
To be more specific, my current problem is as follows:
A developer intended to alter a view in a production database. They issued
an ALTER VIEW command and, because of some reason, the process hung up.
In the EM, if I go to Management --> Current Activity --> Process Info I can
see that process as spid 160, the statement is "ALTER VIEW..."
I killed the process, as described above, but it is still alive.
If I call
select * from sysprocesses where spid = 160
the result is:
spid 160
kpid 1320
blocked 0
waittype 0x0000
waittime 0
lastwaittype OLEDB
waitresource
dbid 17
uid 0
cpu 21265
physical_io 2299
memusage 476
login_time 2006-06-23 08:34:36.450
last_batch 2006-06-23 09:39:55.577
ecid 0
open_tran 2
status runnable
sid 0x010500000000000515000000AA562D4A3E3CC3
6A4B1A1304ED030...0
hostname XXX
program_name MS SQLEM
hostprocess 3900
cmd AWAITING COMMAND
nt_domain XXX
nt_username XXX
net_address XXX
net_library TCP/IP
loginame XXX\XXX
context_info 0x0...0
sql_handle 0x0...0
stmt_start 0
stmt_end 0
Now, if I go to Locks / Process ID I can see that process 160 is marked as
'Blocking'.
Moreover, there are many processes that are marked as 'Blocked by 160'.
The result of
sp_lock '160'
is:
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
160 17 0 0 DB S GRANT
160 17 0 0 DB S GRANT
160 8 0 0 DB S GRANT
160 8 0 0 DB S GRANT
160 52 0 0 DB S GRANT
160 47 0 0 DB S GRANT
160 17 804197915 0 TAB Sch-S GRANT
160 17 804197915 0 TAB [COMPILE] X GRANT
160 47 1990402260 0 TAB Sch-S GRANT
160 8 985106600 0 TAB Sch-S GRANT
160 17 1767677345 0 TAB Sch-S GRANT
160 52 1893581784 0 TAB Sch-S GRANT
160 8 793105916 0 TAB [COMPILE] X GRANT
160 8 793105916 0 TAB Sch-S GRANT
160 52 1285579618 0 TAB Sch-S GRANT
And that is all what I was able to check.
What can I do in that situation? Is the server restart really necessary?
Probably, I will end up restarting the SQL this time, but I would like to
learn what to do if that happens again.
Any help / ideas / suggestions would be greatly appreciated.
Thank you in advance!
Best regards,
Andrew|||Andrew Drake wrote:
> Dear all,
> A developer intended to alter a view in a production database. They issued
> an ALTER VIEW command and, because of some reason, the process hung up.
> In the EM, if I go to Management --> Current Activity --> Process Info I c
an
> see that process as spid 160, the statement is "ALTER VIEW..."
> Any help / ideas / suggestions would be greatly appreciated.
>
Does the view refer to any linked servers? Can you post the query that
makes up the view?|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:O89ADPUmGHA.1252@.TK2MSFTNGP02.phx.gbl...
> Andrew Drake wrote:
> Does the view refer to any linked servers? Can you post the query that
> makes up the view?
Yes, it does. Actually it is a bit more complicated, because the view refers
to another view in some other database, that in turn refers to a linked
server:
ALTER VIEW DB1.dbo.MyView
AS
SELECT T1.Field1, V2.Field2
FROM DB1.dbo.Table1 T1
JOIN DB2.dbo.View2 V2
ON T1.Field1 = V2.FieldX
where DB2.dbo.View2 refers to a linked server.
Does it matter that a linked server is involved?
Best regards,
Andrew|||Andrew Drake wrote:
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:O89ADPUmGHA.1252@.TK2MSFTNGP02.phx.gbl...
> Yes, it does. Actually it is a bit more complicated, because the view refe
rs
> to another view in some other database, that in turn refers to a linked
> server:
> ALTER VIEW DB1.dbo.MyView
> AS
> SELECT T1.Field1, V2.Field2
> FROM DB1.dbo.Table1 T1
> JOIN DB2.dbo.View2 V2
> ON T1.Field1 = V2.FieldX
> where DB2.dbo.View2 refers to a linked server.
> Does it matter that a linked server is involved?
> Best regards,
> Andrew
>
>
Check sysprocesses on the linked server - the process that you killed is
probably waiting on something to finish over on the linked server. In
that case, you'll have to go over to that machine and kill whatever spid
is running over there. Once it dies, then the original spid should die
as well.|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:O89ADPUmGHA.1252@.TK2MSFTNGP02.phx.gbl...
> Andrew Drake wrote:
> Does the view refer to any linked servers? Can you post the query that
> makes up the view?
Yes, it does. Actually it is a bit more complicated, because the view refers
to another view in some other database, that in turn refers to a linked
server:
ALTER VIEW DB1.dbo.MyView
AS
SELECT T1.Field1, V2.Field2
FROM DB1.dbo.Table1 T1
JOIN DB2.dbo.View2 V2
ON T1.Field1 = V2.FieldX
where DB2.dbo.View2 refers to a linked server.
Does it matter that a linked server is involved?
Best regards,
Andrew|||Andrew Drake wrote:
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:O89ADPUmGHA.1252@.TK2MSFTNGP02.phx.gbl...
> Yes, it does. Actually it is a bit more complicated, because the view refe
rs
> to another view in some other database, that in turn refers to a linked
> server:
> ALTER VIEW DB1.dbo.MyView
> AS
> SELECT T1.Field1, V2.Field2
> FROM DB1.dbo.Table1 T1
> JOIN DB2.dbo.View2 V2
> ON T1.Field1 = V2.FieldX
> where DB2.dbo.View2 refers to a linked server.
> Does it matter that a linked server is involved?
> Best regards,
> Andrew
>
>
Check sysprocesses on the linked server - the process that you killed is
probably waiting on something to finish over on the linked server. In
that case, you'll have to go over to that machine and kill whatever spid
is running over there. Once it dies, then the original spid should die
as well.|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:ubgQROemGHA.4436@.TK2MSFTNGP05.phx.gbl...
> Andrew Drake wrote:
> Check sysprocesses on the linked server - the process that you killed is
> probably waiting on something to finish over on the linked server. In
> that case, you'll have to go over to that machine and kill whatever spid
> is running over there. Once it dies, then the original spid should die as
> well.
Thank you very much for your help!
Best regards,
Andrew|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:ubgQROemGHA.4436@.TK2MSFTNGP05.phx.gbl...
> Andrew Drake wrote:
> Check sysprocesses on the linked server - the process that you killed is
> probably waiting on something to finish over on the linked server. In
> that case, you'll have to go over to that machine and kill whatever spid
> is running over there. Once it dies, then the original spid should die as
> well.
Thank you very much for your help!
Best regards,
Andrew|||Andrew Drake wrote:
> Thank you very much for your help!
> Best regards,
> Andrew
>
No problem... FYI, the same thing can happen to a process that uses
xp_cmdshell to run an OS command, like a COPY command to copy a large
backup file. Killing the spid will exhibit the same behavior that you
saw, to completely kill it you have to open the Windows Task Manager and
kill the CMD.EXE process that was spawned by xp_cmdshell.
Showing posts with label production. Show all posts
Showing posts with label production. Show all posts
Friday, March 23, 2012
KILL spid never ends (SQL 2000)
Dear all,
From time to time an SQL process ('spid') hangs up in our production SQL
server.
Then we try to kill it using KILL command.
And quite often the KILL never ends.
If I call (spid to kill is 160):
KILL 160 WITH STATUSONLY
the result is:
SPID 160: transaction rollback in progress. Estimated rollback completion:
100%. Estimated time remaining: 0 seconds.
and that result lasts forever (i.e. a couple of days until we restart the
server).
That is quite troublesome as the server is quite heavily used and restarting
it always interferes with normal work of users.
Is there any way to REALLY kill the process?
I guess my problems are related to locking issues, I looked up the
newsgroups but couldn't figure out any feasible solution but restart the
server.
To be more specific, my current problem is as follows:
A developer intended to alter a view in a production database. They issued
an ALTER VIEW command and, because of some reason, the process hung up.
In the EM, if I go to Management --> Current Activity --> Process Info I can
see that process as spid 160, the statement is "ALTER VIEW..."
I killed the process, as described above, but it is still alive.
If I call
select * from sysprocesses where spid = 160
the result is:
spid 160
kpid 1320
blocked 0
waittype 0x0000
waittime 0
lastwaittype OLEDB
waitresource
dbid 17
uid 0
cpu 21265
physical_io 2299
memusage 476
login_time 2006-06-23 08:34:36.450
last_batch 2006-06-23 09:39:55.577
ecid 0
open_tran 2
status runnable
sid 0x010500000000000515000000AA562D4A3E3CC36A4B1A1304ED030...0
hostname XXX
program_name MS SQLEM
hostprocess 3900
cmd AWAITING COMMAND
nt_domain XXX
nt_username XXX
net_address XXX
net_library TCP/IP
loginame XXX\XXX
context_info 0x0...0
sql_handle 0x0...0
stmt_start 0
stmt_end 0
Now, if I go to Locks / Process ID I can see that process 160 is marked as
'Blocking'.
Moreover, there are many processes that are marked as 'Blocked by 160'.
The result of
sp_lock '160'
is:
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
160 17 0 0 DB S GRANT
160 17 0 0 DB S GRANT
160 8 0 0 DB S GRANT
160 8 0 0 DB S GRANT
160 52 0 0 DB S GRANT
160 47 0 0 DB S GRANT
160 17 804197915 0 TAB Sch-S GRANT
160 17 804197915 0 TAB [COMPILE] X GRANT
160 47 1990402260 0 TAB Sch-S GRANT
160 8 985106600 0 TAB Sch-S GRANT
160 17 1767677345 0 TAB Sch-S GRANT
160 52 1893581784 0 TAB Sch-S GRANT
160 8 793105916 0 TAB [COMPILE] X GRANT
160 8 793105916 0 TAB Sch-S GRANT
160 52 1285579618 0 TAB Sch-S GRANT
And that is all what I was able to check.
What can I do in that situation? Is the server restart really necessary?
Probably, I will end up restarting the SQL this time, but I would like to
learn what to do if that happens again.
Any help / ideas / suggestions would be greatly appreciated.
Thank you in advance!
Best regards,
AndrewAndrew Drake wrote:
> Dear all,
> A developer intended to alter a view in a production database. They issued
> an ALTER VIEW command and, because of some reason, the process hung up.
> In the EM, if I go to Management --> Current Activity --> Process Info I can
> see that process as spid 160, the statement is "ALTER VIEW..."
> Any help / ideas / suggestions would be greatly appreciated.
>
Does the view refer to any linked servers? Can you post the query that
makes up the view?|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:O89ADPUmGHA.1252@.TK2MSFTNGP02.phx.gbl...
> Andrew Drake wrote:
>> Dear all,
>> A developer intended to alter a view in a production database. They
>> issued
>> an ALTER VIEW command and, because of some reason, the process hung up.
>> In the EM, if I go to Management --> Current Activity --> Process Info I
>> can
>> see that process as spid 160, the statement is "ALTER VIEW..."
>> Any help / ideas / suggestions would be greatly appreciated.
> Does the view refer to any linked servers? Can you post the query that
> makes up the view?
Yes, it does. Actually it is a bit more complicated, because the view refers
to another view in some other database, that in turn refers to a linked
server:
ALTER VIEW DB1.dbo.MyView
AS
SELECT T1.Field1, V2.Field2
FROM DB1.dbo.Table1 T1
JOIN DB2.dbo.View2 V2
ON T1.Field1 = V2.FieldX
where DB2.dbo.View2 refers to a linked server.
Does it matter that a linked server is involved?
Best regards,
Andrew|||Andrew Drake wrote:
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:O89ADPUmGHA.1252@.TK2MSFTNGP02.phx.gbl...
>> Andrew Drake wrote:
>> Dear all,
>> A developer intended to alter a view in a production database. They
>> issued
>> an ALTER VIEW command and, because of some reason, the process hung up.
>> In the EM, if I go to Management --> Current Activity --> Process Info I
>> can
>> see that process as spid 160, the statement is "ALTER VIEW..."
>> Any help / ideas / suggestions would be greatly appreciated.
>> Does the view refer to any linked servers? Can you post the query that
>> makes up the view?
> Yes, it does. Actually it is a bit more complicated, because the view refers
> to another view in some other database, that in turn refers to a linked
> server:
> ALTER VIEW DB1.dbo.MyView
> AS
> SELECT T1.Field1, V2.Field2
> FROM DB1.dbo.Table1 T1
> JOIN DB2.dbo.View2 V2
> ON T1.Field1 = V2.FieldX
> where DB2.dbo.View2 refers to a linked server.
> Does it matter that a linked server is involved?
> Best regards,
> Andrew
>
>
Check sysprocesses on the linked server - the process that you killed is
probably waiting on something to finish over on the linked server. In
that case, you'll have to go over to that machine and kill whatever spid
is running over there. Once it dies, then the original spid should die
as well.|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:ubgQROemGHA.4436@.TK2MSFTNGP05.phx.gbl...
> Andrew Drake wrote:
>> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
>> news:O89ADPUmGHA.1252@.TK2MSFTNGP02.phx.gbl...
>> Andrew Drake wrote:
>> Dear all,
>> A developer intended to alter a view in a production database. They
>> issued
>> an ALTER VIEW command and, because of some reason, the process hung up.
>> In the EM, if I go to Management --> Current Activity --> Process Info
>> I can
>> see that process as spid 160, the statement is "ALTER VIEW..."
>> Any help / ideas / suggestions would be greatly appreciated.
>> Does the view refer to any linked servers? Can you post the query that
>> makes up the view?
>> Yes, it does. Actually it is a bit more complicated, because the view
>> refers to another view in some other database, that in turn refers to a
>> linked server:
>> ALTER VIEW DB1.dbo.MyView
>> AS
>> SELECT T1.Field1, V2.Field2
>> FROM DB1.dbo.Table1 T1
>> JOIN DB2.dbo.View2 V2
>> ON T1.Field1 = V2.FieldX
>> where DB2.dbo.View2 refers to a linked server.
>> Does it matter that a linked server is involved?
>> Best regards,
>> Andrew
> Check sysprocesses on the linked server - the process that you killed is
> probably waiting on something to finish over on the linked server. In
> that case, you'll have to go over to that machine and kill whatever spid
> is running over there. Once it dies, then the original spid should die as
> well.
Thank you very much for your help!
Best regards,
Andrew|||Andrew Drake wrote:
> Thank you very much for your help!
> Best regards,
> Andrew
>
No problem... FYI, the same thing can happen to a process that uses
xp_cmdshell to run an OS command, like a COPY command to copy a large
backup file. Killing the spid will exhibit the same behavior that you
saw, to completely kill it you have to open the Windows Task Manager and
kill the CMD.EXE process that was spawned by xp_cmdshell.|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:eHoNt7qmGHA.2372@.TK2MSFTNGP04.phx.gbl...
> Andrew Drake wrote:
>> Thank you very much for your help!
>> Best regards,
>> Andrew
>>
> No problem... FYI, the same thing can happen to a process that uses
> xp_cmdshell to run an OS command, like a COPY command to copy a large
> backup file. Killing the spid will exhibit the same behavior that you
> saw, to completely kill it you have to open the Windows Task Manager and
> kill the CMD.EXE process that was spawned by xp_cmdshell.
Yes, you are right, we have experienced a similar behaviour with jobs that
contained an EXEC master.dbo.xp_cmdshell 'some.exe' command.
Stopping the job was not enough, we had to explicitly kill the exe process
at the OS level.
Thanks a lot again!
Best regards,
Andrew|||Hi, try to restart the MSDTC services..
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:ubgQROemGHA.4436@.TK2MSFTNGP05.phx.gbl...
> Andrew Drake wrote:
> > "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> > news:O89ADPUmGHA.1252@.TK2MSFTNGP02.phx.gbl...
> >> Andrew Drake wrote:
> >> Dear all,
> >>
> >> A developer intended to alter a view in a production database. They
> >> issued
> >> an ALTER VIEW command and, because of some reason, the process hung
up.
> >> In the EM, if I go to Management --> Current Activity --> Process Info
I
> >> can
> >> see that process as spid 160, the statement is "ALTER VIEW..."
> >>
> >> Any help / ideas / suggestions would be greatly appreciated.
> >>
> >> Does the view refer to any linked servers? Can you post the query that
> >> makes up the view?
> >
> > Yes, it does. Actually it is a bit more complicated, because the view
refers
> > to another view in some other database, that in turn refers to a linked
> > server:
> >
> > ALTER VIEW DB1.dbo.MyView
> > AS
> > SELECT T1.Field1, V2.Field2
> > FROM DB1.dbo.Table1 T1
> > JOIN DB2.dbo.View2 V2
> > ON T1.Field1 = V2.FieldX
> >
> > where DB2.dbo.View2 refers to a linked server.
> >
> > Does it matter that a linked server is involved?
> >
> > Best regards,
> > Andrew
> >
> >
> >
> Check sysprocesses on the linked server - the process that you killed is
> probably waiting on something to finish over on the linked server. In
> that case, you'll have to go over to that machine and kill whatever spid
> is running over there. Once it dies, then the original spid should die
> as well.
From time to time an SQL process ('spid') hangs up in our production SQL
server.
Then we try to kill it using KILL command.
And quite often the KILL never ends.
If I call (spid to kill is 160):
KILL 160 WITH STATUSONLY
the result is:
SPID 160: transaction rollback in progress. Estimated rollback completion:
100%. Estimated time remaining: 0 seconds.
and that result lasts forever (i.e. a couple of days until we restart the
server).
That is quite troublesome as the server is quite heavily used and restarting
it always interferes with normal work of users.
Is there any way to REALLY kill the process?
I guess my problems are related to locking issues, I looked up the
newsgroups but couldn't figure out any feasible solution but restart the
server.
To be more specific, my current problem is as follows:
A developer intended to alter a view in a production database. They issued
an ALTER VIEW command and, because of some reason, the process hung up.
In the EM, if I go to Management --> Current Activity --> Process Info I can
see that process as spid 160, the statement is "ALTER VIEW..."
I killed the process, as described above, but it is still alive.
If I call
select * from sysprocesses where spid = 160
the result is:
spid 160
kpid 1320
blocked 0
waittype 0x0000
waittime 0
lastwaittype OLEDB
waitresource
dbid 17
uid 0
cpu 21265
physical_io 2299
memusage 476
login_time 2006-06-23 08:34:36.450
last_batch 2006-06-23 09:39:55.577
ecid 0
open_tran 2
status runnable
sid 0x010500000000000515000000AA562D4A3E3CC36A4B1A1304ED030...0
hostname XXX
program_name MS SQLEM
hostprocess 3900
cmd AWAITING COMMAND
nt_domain XXX
nt_username XXX
net_address XXX
net_library TCP/IP
loginame XXX\XXX
context_info 0x0...0
sql_handle 0x0...0
stmt_start 0
stmt_end 0
Now, if I go to Locks / Process ID I can see that process 160 is marked as
'Blocking'.
Moreover, there are many processes that are marked as 'Blocked by 160'.
The result of
sp_lock '160'
is:
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
160 17 0 0 DB S GRANT
160 17 0 0 DB S GRANT
160 8 0 0 DB S GRANT
160 8 0 0 DB S GRANT
160 52 0 0 DB S GRANT
160 47 0 0 DB S GRANT
160 17 804197915 0 TAB Sch-S GRANT
160 17 804197915 0 TAB [COMPILE] X GRANT
160 47 1990402260 0 TAB Sch-S GRANT
160 8 985106600 0 TAB Sch-S GRANT
160 17 1767677345 0 TAB Sch-S GRANT
160 52 1893581784 0 TAB Sch-S GRANT
160 8 793105916 0 TAB [COMPILE] X GRANT
160 8 793105916 0 TAB Sch-S GRANT
160 52 1285579618 0 TAB Sch-S GRANT
And that is all what I was able to check.
What can I do in that situation? Is the server restart really necessary?
Probably, I will end up restarting the SQL this time, but I would like to
learn what to do if that happens again.
Any help / ideas / suggestions would be greatly appreciated.
Thank you in advance!
Best regards,
AndrewAndrew Drake wrote:
> Dear all,
> A developer intended to alter a view in a production database. They issued
> an ALTER VIEW command and, because of some reason, the process hung up.
> In the EM, if I go to Management --> Current Activity --> Process Info I can
> see that process as spid 160, the statement is "ALTER VIEW..."
> Any help / ideas / suggestions would be greatly appreciated.
>
Does the view refer to any linked servers? Can you post the query that
makes up the view?|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:O89ADPUmGHA.1252@.TK2MSFTNGP02.phx.gbl...
> Andrew Drake wrote:
>> Dear all,
>> A developer intended to alter a view in a production database. They
>> issued
>> an ALTER VIEW command and, because of some reason, the process hung up.
>> In the EM, if I go to Management --> Current Activity --> Process Info I
>> can
>> see that process as spid 160, the statement is "ALTER VIEW..."
>> Any help / ideas / suggestions would be greatly appreciated.
> Does the view refer to any linked servers? Can you post the query that
> makes up the view?
Yes, it does. Actually it is a bit more complicated, because the view refers
to another view in some other database, that in turn refers to a linked
server:
ALTER VIEW DB1.dbo.MyView
AS
SELECT T1.Field1, V2.Field2
FROM DB1.dbo.Table1 T1
JOIN DB2.dbo.View2 V2
ON T1.Field1 = V2.FieldX
where DB2.dbo.View2 refers to a linked server.
Does it matter that a linked server is involved?
Best regards,
Andrew|||Andrew Drake wrote:
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:O89ADPUmGHA.1252@.TK2MSFTNGP02.phx.gbl...
>> Andrew Drake wrote:
>> Dear all,
>> A developer intended to alter a view in a production database. They
>> issued
>> an ALTER VIEW command and, because of some reason, the process hung up.
>> In the EM, if I go to Management --> Current Activity --> Process Info I
>> can
>> see that process as spid 160, the statement is "ALTER VIEW..."
>> Any help / ideas / suggestions would be greatly appreciated.
>> Does the view refer to any linked servers? Can you post the query that
>> makes up the view?
> Yes, it does. Actually it is a bit more complicated, because the view refers
> to another view in some other database, that in turn refers to a linked
> server:
> ALTER VIEW DB1.dbo.MyView
> AS
> SELECT T1.Field1, V2.Field2
> FROM DB1.dbo.Table1 T1
> JOIN DB2.dbo.View2 V2
> ON T1.Field1 = V2.FieldX
> where DB2.dbo.View2 refers to a linked server.
> Does it matter that a linked server is involved?
> Best regards,
> Andrew
>
>
Check sysprocesses on the linked server - the process that you killed is
probably waiting on something to finish over on the linked server. In
that case, you'll have to go over to that machine and kill whatever spid
is running over there. Once it dies, then the original spid should die
as well.|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:ubgQROemGHA.4436@.TK2MSFTNGP05.phx.gbl...
> Andrew Drake wrote:
>> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
>> news:O89ADPUmGHA.1252@.TK2MSFTNGP02.phx.gbl...
>> Andrew Drake wrote:
>> Dear all,
>> A developer intended to alter a view in a production database. They
>> issued
>> an ALTER VIEW command and, because of some reason, the process hung up.
>> In the EM, if I go to Management --> Current Activity --> Process Info
>> I can
>> see that process as spid 160, the statement is "ALTER VIEW..."
>> Any help / ideas / suggestions would be greatly appreciated.
>> Does the view refer to any linked servers? Can you post the query that
>> makes up the view?
>> Yes, it does. Actually it is a bit more complicated, because the view
>> refers to another view in some other database, that in turn refers to a
>> linked server:
>> ALTER VIEW DB1.dbo.MyView
>> AS
>> SELECT T1.Field1, V2.Field2
>> FROM DB1.dbo.Table1 T1
>> JOIN DB2.dbo.View2 V2
>> ON T1.Field1 = V2.FieldX
>> where DB2.dbo.View2 refers to a linked server.
>> Does it matter that a linked server is involved?
>> Best regards,
>> Andrew
> Check sysprocesses on the linked server - the process that you killed is
> probably waiting on something to finish over on the linked server. In
> that case, you'll have to go over to that machine and kill whatever spid
> is running over there. Once it dies, then the original spid should die as
> well.
Thank you very much for your help!
Best regards,
Andrew|||Andrew Drake wrote:
> Thank you very much for your help!
> Best regards,
> Andrew
>
No problem... FYI, the same thing can happen to a process that uses
xp_cmdshell to run an OS command, like a COPY command to copy a large
backup file. Killing the spid will exhibit the same behavior that you
saw, to completely kill it you have to open the Windows Task Manager and
kill the CMD.EXE process that was spawned by xp_cmdshell.|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:eHoNt7qmGHA.2372@.TK2MSFTNGP04.phx.gbl...
> Andrew Drake wrote:
>> Thank you very much for your help!
>> Best regards,
>> Andrew
>>
> No problem... FYI, the same thing can happen to a process that uses
> xp_cmdshell to run an OS command, like a COPY command to copy a large
> backup file. Killing the spid will exhibit the same behavior that you
> saw, to completely kill it you have to open the Windows Task Manager and
> kill the CMD.EXE process that was spawned by xp_cmdshell.
Yes, you are right, we have experienced a similar behaviour with jobs that
contained an EXEC master.dbo.xp_cmdshell 'some.exe' command.
Stopping the job was not enough, we had to explicitly kill the exe process
at the OS level.
Thanks a lot again!
Best regards,
Andrew|||Hi, try to restart the MSDTC services..
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:ubgQROemGHA.4436@.TK2MSFTNGP05.phx.gbl...
> Andrew Drake wrote:
> > "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> > news:O89ADPUmGHA.1252@.TK2MSFTNGP02.phx.gbl...
> >> Andrew Drake wrote:
> >> Dear all,
> >>
> >> A developer intended to alter a view in a production database. They
> >> issued
> >> an ALTER VIEW command and, because of some reason, the process hung
up.
> >> In the EM, if I go to Management --> Current Activity --> Process Info
I
> >> can
> >> see that process as spid 160, the statement is "ALTER VIEW..."
> >>
> >> Any help / ideas / suggestions would be greatly appreciated.
> >>
> >> Does the view refer to any linked servers? Can you post the query that
> >> makes up the view?
> >
> > Yes, it does. Actually it is a bit more complicated, because the view
refers
> > to another view in some other database, that in turn refers to a linked
> > server:
> >
> > ALTER VIEW DB1.dbo.MyView
> > AS
> > SELECT T1.Field1, V2.Field2
> > FROM DB1.dbo.Table1 T1
> > JOIN DB2.dbo.View2 V2
> > ON T1.Field1 = V2.FieldX
> >
> > where DB2.dbo.View2 refers to a linked server.
> >
> > Does it matter that a linked server is involved?
> >
> > Best regards,
> > Andrew
> >
> >
> >
> Check sysprocesses on the linked server - the process that you killed is
> probably waiting on something to finish over on the linked server. In
> that case, you'll have to go over to that machine and kill whatever spid
> is running over there. Once it dies, then the original spid should die
> as well.
Monday, March 12, 2012
Key locks and Deadlocks
I don't know if this is the best place to post this, if not, please
advise...
I have a SQL 2000 production db that the users of a VB 6.0 app occasionally
see deadlock errors. Like so many times, it's not reproducible, but it
happens more when they are really busy (Thanks, Murf!).
In running the performance Monitor, I see that a significant number (over
10,000) key locks get generated during some queries. I have run the Sql
Analyyzer and cannot find any specific transaction or sequence of events
that results in the deadlock.
My theory is the deadlocking is coming from page locks, and each user
contends just a little too much for some pages.
The other possibility is that the key locks, being so numerous could be
blocking and causing the deadlock victims transaction to roll back.
So, my question is two-fold:
1) can Key lock contention cause deadlocks? If so, how do I reduce keylock
use?
2) Is there a way to control or force row locking when using ADO, VB6 style;
specifically when using the .Update method on an ADO recordset (I realize I
could re-write all the updates to be via striaght SQL, so I could insert my
own hints regarding rowlocking, and yes, I already have a post in
public.data.ado message group on this).
Thanks!
Steve
Steve Byrne wrote:
> I don't know if this is the best place to post this, if not, please
> advise...
> I have a SQL 2000 production db that the users of a VB 6.0 app
> occasionally see deadlock errors. Like so many times, it's not
> reproducible, but it happens more when they are really busy (Thanks,
> Murf!).
> In running the performance Monitor, I see that a significant number
> (over 10,000) key locks get generated during some queries. I have run
> the Sql Analyyzer and cannot find any specific transaction or
> sequence of events that results in the deadlock.
> My theory is the deadlocking is coming from page locks, and each user
> contends just a little too much for some pages.
> The other possibility is that the key locks, being so numerous could
> be blocking and causing the deadlock victims transaction to roll back.
> So, my question is two-fold:
> 1) can Key lock contention cause deadlocks? If so, how do I reduce
> keylock use?
> 2) Is there a way to control or force row locking when using ADO, VB6
> style; specifically when using the .Update method on an ADO recordset
> (I realize I could re-write all the updates to be via striaght SQL,
> so I could insert my own hints regarding rowlocking, and yes, I
> already have a post in public.data.ado message group on this).
> Thanks!
> Steve
See this page about identifying and resolving deadlocks:
http://support.microsoft.com/?kbid=832524
The first step is to see what transactions are responsible for the
deadlocks. To resolve the deadlock, you should:
- Make sure all transactions involved are fully optimized
- Access objects in the same order in all transactions
- Keep the transactions short - (fetch all result set data immediately
and avoid leaving locks on the server)
- Use the lowest level isolation level possible (READ COMMITTED, READ
UNCOMMITTED, REPEATABLE READ, and then SERIALIZABLE)
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Also try with (nolock) for dirty reads.
Helps greatly on a heavy OLTP server and you don't need "to the second"
accuracy.
Mike
"Steve Byrne" <steveb@.ssninc.com> wrote in message
news:OJ9SZn9sFHA.2348@.tk2msftngp13.phx.gbl...
I don't know if this is the best place to post this, if not, please
advise...
I have a SQL 2000 production db that the users of a VB 6.0 app occasionally
see deadlock errors. Like so many times, it's not reproducible, but it
happens more when they are really busy (Thanks, Murf!).
In running the performance Monitor, I see that a significant number (over
10,000) key locks get generated during some queries. I have run the Sql
Analyyzer and cannot find any specific transaction or sequence of events
that results in the deadlock.
My theory is the deadlocking is coming from page locks, and each user
contends just a little too much for some pages.
The other possibility is that the key locks, being so numerous could be
blocking and causing the deadlock victims transaction to roll back.
So, my question is two-fold:
1) can Key lock contention cause deadlocks? If so, how do I reduce keylock
use?
2) Is there a way to control or force row locking when using ADO, VB6 style;
specifically when using the .Update method on an ADO recordset (I realize I
could re-write all the updates to be via striaght SQL, so I could insert my
own hints regarding rowlocking, and yes, I already have a post in
public.data.ado message group on this).
Thanks!
Steve
|||Mike Perino wrote:
> Also try with (nolock) for dirty reads.
> Helps greatly on a heavy OLTP server and you don't need "to the
> second" accuracy.
Or you can handle reading data that exists now, but is rolled back
afterwards. Essentially, data that never existed. I agree, though, that
any application that does not require accurate data be returned for a
query should consider using this locking hint. For example, a query that
returns estimated totals sales for the month.
David Gugick
Quest Software
www.imceda.com
www.quest.com
advise...
I have a SQL 2000 production db that the users of a VB 6.0 app occasionally
see deadlock errors. Like so many times, it's not reproducible, but it
happens more when they are really busy (Thanks, Murf!).
In running the performance Monitor, I see that a significant number (over
10,000) key locks get generated during some queries. I have run the Sql
Analyyzer and cannot find any specific transaction or sequence of events
that results in the deadlock.
My theory is the deadlocking is coming from page locks, and each user
contends just a little too much for some pages.
The other possibility is that the key locks, being so numerous could be
blocking and causing the deadlock victims transaction to roll back.
So, my question is two-fold:
1) can Key lock contention cause deadlocks? If so, how do I reduce keylock
use?
2) Is there a way to control or force row locking when using ADO, VB6 style;
specifically when using the .Update method on an ADO recordset (I realize I
could re-write all the updates to be via striaght SQL, so I could insert my
own hints regarding rowlocking, and yes, I already have a post in
public.data.ado message group on this).
Thanks!
Steve
Steve Byrne wrote:
> I don't know if this is the best place to post this, if not, please
> advise...
> I have a SQL 2000 production db that the users of a VB 6.0 app
> occasionally see deadlock errors. Like so many times, it's not
> reproducible, but it happens more when they are really busy (Thanks,
> Murf!).
> In running the performance Monitor, I see that a significant number
> (over 10,000) key locks get generated during some queries. I have run
> the Sql Analyyzer and cannot find any specific transaction or
> sequence of events that results in the deadlock.
> My theory is the deadlocking is coming from page locks, and each user
> contends just a little too much for some pages.
> The other possibility is that the key locks, being so numerous could
> be blocking and causing the deadlock victims transaction to roll back.
> So, my question is two-fold:
> 1) can Key lock contention cause deadlocks? If so, how do I reduce
> keylock use?
> 2) Is there a way to control or force row locking when using ADO, VB6
> style; specifically when using the .Update method on an ADO recordset
> (I realize I could re-write all the updates to be via striaght SQL,
> so I could insert my own hints regarding rowlocking, and yes, I
> already have a post in public.data.ado message group on this).
> Thanks!
> Steve
See this page about identifying and resolving deadlocks:
http://support.microsoft.com/?kbid=832524
The first step is to see what transactions are responsible for the
deadlocks. To resolve the deadlock, you should:
- Make sure all transactions involved are fully optimized
- Access objects in the same order in all transactions
- Keep the transactions short - (fetch all result set data immediately
and avoid leaving locks on the server)
- Use the lowest level isolation level possible (READ COMMITTED, READ
UNCOMMITTED, REPEATABLE READ, and then SERIALIZABLE)
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Also try with (nolock) for dirty reads.
Helps greatly on a heavy OLTP server and you don't need "to the second"
accuracy.
Mike
"Steve Byrne" <steveb@.ssninc.com> wrote in message
news:OJ9SZn9sFHA.2348@.tk2msftngp13.phx.gbl...
I don't know if this is the best place to post this, if not, please
advise...
I have a SQL 2000 production db that the users of a VB 6.0 app occasionally
see deadlock errors. Like so many times, it's not reproducible, but it
happens more when they are really busy (Thanks, Murf!).
In running the performance Monitor, I see that a significant number (over
10,000) key locks get generated during some queries. I have run the Sql
Analyyzer and cannot find any specific transaction or sequence of events
that results in the deadlock.
My theory is the deadlocking is coming from page locks, and each user
contends just a little too much for some pages.
The other possibility is that the key locks, being so numerous could be
blocking and causing the deadlock victims transaction to roll back.
So, my question is two-fold:
1) can Key lock contention cause deadlocks? If so, how do I reduce keylock
use?
2) Is there a way to control or force row locking when using ADO, VB6 style;
specifically when using the .Update method on an ADO recordset (I realize I
could re-write all the updates to be via striaght SQL, so I could insert my
own hints regarding rowlocking, and yes, I already have a post in
public.data.ado message group on this).
Thanks!
Steve
|||Mike Perino wrote:
> Also try with (nolock) for dirty reads.
> Helps greatly on a heavy OLTP server and you don't need "to the
> second" accuracy.
Or you can handle reading data that exists now, but is rolled back
afterwards. Essentially, data that never existed. I agree, though, that
any application that does not require accurate data be returned for a
query should consider using this locking hint. For example, a query that
returns estimated totals sales for the month.
David Gugick
Quest Software
www.imceda.com
www.quest.com
Friday, March 9, 2012
Keeping SQL up 24/7
Hi all,
I have been asked to propose a production system whereby a SQL server
machine can be up 7/24.
My question is, how do you keep a production box up 7/24 when you need to
add the OS or SQL patches to it?
Do you need to have 2 boxes, and somehow switch between the to for this
maint. Any advice would be helpful.
Thanks in advance.
TomIf you have no time for maintenance, you may need to look
into Clustering or at least Replication.
>--Original Message--
>Hi all,
>I have been asked to propose a production system whereby
a SQL server
>machine can be up 7/24.
>My question is, how do you keep a production box up 7/24
when you need to
>add the OS or SQL patches to it?
>Do you need to have 2 boxes, and somehow switch between
the to for this
>maint. Any advice would be helpful.
>Thanks in advance.
>Tom
>
>.
>|||I don't think replication, in general, would help reducing the potential
down time caused by applying OS/SQL patches.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"chris" <chris@.NoEmail.com> wrote in message
news:0d1d01c38855$c6b404b0$a101280a@.phx.gbl...
> If you have no time for maintenance, you may need to look
> into Clustering or at least Replication.
> >--Original Message--
> >Hi all,
> >
> >I have been asked to propose a production system whereby
> a SQL server
> >machine can be up 7/24.
> >My question is, how do you keep a production box up 7/24
> when you need to
> >add the OS or SQL patches to it?
> >Do you need to have 2 boxes, and somehow switch between
> the to for this
> >maint. Any advice would be helpful.
> >
> >Thanks in advance.
> >Tom
> >
> >
> >
> >.
> >
I have been asked to propose a production system whereby a SQL server
machine can be up 7/24.
My question is, how do you keep a production box up 7/24 when you need to
add the OS or SQL patches to it?
Do you need to have 2 boxes, and somehow switch between the to for this
maint. Any advice would be helpful.
Thanks in advance.
TomIf you have no time for maintenance, you may need to look
into Clustering or at least Replication.
>--Original Message--
>Hi all,
>I have been asked to propose a production system whereby
a SQL server
>machine can be up 7/24.
>My question is, how do you keep a production box up 7/24
when you need to
>add the OS or SQL patches to it?
>Do you need to have 2 boxes, and somehow switch between
the to for this
>maint. Any advice would be helpful.
>Thanks in advance.
>Tom
>
>.
>|||I don't think replication, in general, would help reducing the potential
down time caused by applying OS/SQL patches.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"chris" <chris@.NoEmail.com> wrote in message
news:0d1d01c38855$c6b404b0$a101280a@.phx.gbl...
> If you have no time for maintenance, you may need to look
> into Clustering or at least Replication.
> >--Original Message--
> >Hi all,
> >
> >I have been asked to propose a production system whereby
> a SQL server
> >machine can be up 7/24.
> >My question is, how do you keep a production box up 7/24
> when you need to
> >add the OS or SQL patches to it?
> >Do you need to have 2 boxes, and somehow switch between
> the to for this
> >maint. Any advice would be helpful.
> >
> >Thanks in advance.
> >Tom
> >
> >
> >
> >.
> >
Keeping same DateTime format and others in production server
Hi,
I noticed that some of my Stored Procedure is not working well on the production server cause it seems that it has not the same DateTime format...(amongst other things ).
Example of differences between DateTime format :
----------------------
My station (SQL Express 2005) : 26/06/2007 3:17:20 PM
Production server (SQL 2005 Enteprise) : 2007-06-26 15:17:20
How can i make the format of the database unchangeable so it keep the same format on the production server?
I use SQL Express 2005 for coding and SQL 2005 Enteprise for production.
Thanks
check the region settings in the production server's OS and make them the same
|||Ok nice it works.
But if i had no access to the production server (if i host my web site on godaddy for example), what else can we do?
|||That's a good question that I can't answer - you might contact them and see if they have any servers running on the same format as yours, so that they could transfer your domain there...other than that - I have no idea.
|||http://msdn2.microsoft.com/en-us/library/ms142797.aspx
You can set the language in your connect string. Alternatively, you can issue the SET LANGUAGE / SET DATEFORMAT explicitly yourself before your SQL commands.
Although ultimately, that is telling me that you are treating your datetime's as strings (and/or using string concatenation instead of parameterized queries). Both of those are bad practices, should be changed, and then you wouldn't care about the server's language either.
|||Hi,
Ok i understand.
I use :
.....@.DateFinale varchar(30), ...
select @.stmt = 'SELECT *
FROM Categories
WHERE CategoryID =' + @.CategoryID + ' AND DateOnCreate >=''' + @.DateFinale + ''' ORDER BY DateOnCreate DESC'
What will be the code above if i change the type of @.DateFinale for @.DateFinale DateTime?
Thanks for your help.
Wednesday, March 7, 2012
Keeping multiple SSRS instances syncrhonized?
Hi all,
I have a problem to solve for synchronizing report objects between multiple instances of reporting services. Our production data is distributed via replication from our head office to database servers at our remote offices. Likewise, we want to have an instance of reporting services co-located with each of the databases and therefore will be installing reporting services instances at the remote offices.
I would like to optimize for efficiency in keeping the report objects in sync, minimizing data transfer and as little manual intervention as possible so as to avoid mistakes. I have come up with several approaches to acheiving the SSRS replication I seek:
a) Use a scale-out deployment. This would mean creating multiple instances of the reports to account for the multiple DataSources I would need to configure, since I can't parameterize the DataSource in a linked report.
b) Manually replicate reports to each of my deployments using BIDS/SSMS. This would involve a ton of admin as all objects should be kept in sync.
c) Write RSS scripts to handle the synchronization of the servers. This shouldn't be too hard given that the webservices should play nicely with such a solution. It might take some effort to write the object comparison routine.
d) Have the replication engine take care of the replication of report objects between the servers. If I sync up the encryption keys, could I set up replication on all the tables I need synchronized and just be done with it? This would be my favored solution but I'm not sure it is possible.
Any ideas and suggestions would be appreciated.
Thanks,
Calvin
HTH, Jens SUessmeyer.
http://www.sqlserver2005.de|||I know this is already marked answered, but I figured I would put this up here since I ran across it today, probably would do what you needed
http://www.sqldbatips.com/showarticle.asp?ID=62
it worked for me at least 
Keeping multiple SSRS instances syncrhonized?
Hi all,
I have a problem to solve for synchronizing report objects between multiple instances of reporting services. Our production data is distributed via replication from our head office to database servers at our remote offices. Likewise, we want to have an instance of reporting services co-located with each of the databases and therefore will be installing reporting services instances at the remote offices.
I would like to optimize for efficiency in keeping the report objects in sync, minimizing data transfer and as little manual intervention as possible so as to avoid mistakes. I have come up with several approaches to acheiving the SSRS replication I seek:
a) Use a scale-out deployment. This would mean creating multiple instances of the reports to account for the multiple DataSources I would need to configure, since I can't parameterize the DataSource in a linked report.
b) Manually replicate reports to each of my deployments using BIDS/SSMS. This would involve a ton of admin as all objects should be kept in sync.
c) Write RSS scripts to handle the synchronization of the servers. This shouldn't be too hard given that the webservices should play nicely with such a solution. It might take some effort to write the object comparison routine.
d) Have the replication engine take care of the replication of report objects between the servers. If I sync up the encryption keys, could I set up replication on all the tables I need synchronized and just be done with it? This would be my favored solution but I'm not sure it is possible.
Any ideas and suggestions would be appreciated.
Thanks,
Calvin
HTH, Jens SUessmeyer.
http://www.sqlserver2005.de
|||I know this is already marked answered, but I figured I would put this up here since I ran across it today, probably would do what you needed
http://www.sqldbatips.com/showarticle.asp?ID=62
it worked for me at least
Keeping a Production and Test db insync
I have just finished upsizing an Access database to SQL Server 2k. Now the SQL Server need to be run on a test basis to determine if i need to make more changes to the front-end (Access). The problem I am facing is how to keep the two databases in sync while I am testing. Any suggestions?
Also any suggestion or comments on how to run a test setup like this (in parrallel) are also welcome since this is my first time attempting a project like this.
Let me know if anyone needs more info.
Thanks in advance.How often?
I do a dump of p[roduction nightly and restore to dev every day...
Also any suggestion or comments on how to run a test setup like this (in parrallel) are also welcome since this is my first time attempting a project like this.
Let me know if anyone needs more info.
Thanks in advance.How often?
I do a dump of p[roduction nightly and restore to dev every day...
Monday, February 20, 2012
Keep backup files ?
We are running SQL Server 2000.
We would like to know:
Is it possible to make ad hoc backup of the production
database in the same directory where daily database backup
is generated by the Database Maintenance Plan ? We have
selected "Daily backup is 'Remove files older than 1 week'
option is chosen for the production database" in the
Database Maintenance Plan.
This is because if the "Database Maintenance Plan" only
remove backups generated by itself, it is perfect for us
to put ad hoc backup there as we don't want to create
another directory for holding ad hoc backup.
ThanksYes you can place manually run backup files in the same directory as the
maintenance plan stores them. In fact, some use this method to run restore
jobs to a secondary database as you don't have to write a script to
determine the latest backup generated from the maintenance plan.
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Roger Lee" <anonymous@.discussions.microsoft.com> wrote in message
news:02b701c3a195$11bdb430$a501280a@.phx.gbl...
> We are running SQL Server 2000.
> We would like to know:
> Is it possible to make ad hoc backup of the production
> database in the same directory where daily database backup
> is generated by the Database Maintenance Plan ? We have
> selected "Daily backup is 'Remove files older than 1 week'
> option is chosen for the production database" in the
> Database Maintenance Plan.
> This is because if the "Database Maintenance Plan" only
> remove backups generated by itself, it is perfect for us
> to put ad hoc backup there as we don't want to create
> another directory for holding ad hoc backup.
> Thanks
We would like to know:
Is it possible to make ad hoc backup of the production
database in the same directory where daily database backup
is generated by the Database Maintenance Plan ? We have
selected "Daily backup is 'Remove files older than 1 week'
option is chosen for the production database" in the
Database Maintenance Plan.
This is because if the "Database Maintenance Plan" only
remove backups generated by itself, it is perfect for us
to put ad hoc backup there as we don't want to create
another directory for holding ad hoc backup.
ThanksYes you can place manually run backup files in the same directory as the
maintenance plan stores them. In fact, some use this method to run restore
jobs to a secondary database as you don't have to write a script to
determine the latest backup generated from the maintenance plan.
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Roger Lee" <anonymous@.discussions.microsoft.com> wrote in message
news:02b701c3a195$11bdb430$a501280a@.phx.gbl...
> We are running SQL Server 2000.
> We would like to know:
> Is it possible to make ad hoc backup of the production
> database in the same directory where daily database backup
> is generated by the Database Maintenance Plan ? We have
> selected "Daily backup is 'Remove files older than 1 week'
> option is chosen for the production database" in the
> Database Maintenance Plan.
> This is because if the "Database Maintenance Plan" only
> remove backups generated by itself, it is perfect for us
> to put ad hoc backup there as we don't want to create
> another directory for holding ad hoc backup.
> Thanks
Keep all servers registered and connected
I have 5 servers in my Server Mgmt Studio divided into 4 groups,
Local
Production
Test
Development
In Sql 2000 Enterprise Manager would put the little green arrow next to your db if it was connected, I noticed that Sql 2005 does do that but it wont allow me to keep all 5 servers connected and I need them all to stay connected.
If I look under Registered Servers it shows all 5 servers there but again only 2 show as connected even if I connect the other ones they dont stay connected. I have deleted the registerd ones and re added them and still no difference.
How can I keep all servers registered and connected and then have them show up on start-up?
Thanks!
Stokh
Launch Mgmt Studio
In the Registered Servers tool window right-click on the server and select Properties
On the Properties dialog select the Connection Properties tab
Set Connection time-out to 0 (the default is 15 seconds; 0 indicates no time-out).
|||I already did that and it still does the same thing, only shows 2 servers registered and doesnt keep the other 3 connected|||I suggest you file a bug using the Product Feedback Center: http://lab.msdn.microsoft.com/productfeedback/Default.aspx.|||I would also like to complain about this new 2005 behavior. As a DBA managing dozens of servers, it is ridiculous to keep jumping through the connection hoops in 2005 whenever I switch server context. It's hard to believe MS could screw up something so basic... and something that worked perfectly well in 2000.|||The best thing to do is search the Product Feedback Center for the issue and vote on it. In SP2 we are targeting many of the annoyances of the Mgmt Studio but we need your votes. The voting system let's us know what issues are causing pain for people and those tend to float to the top part of the list.
Subscribe to:
Posts (Atom)