Can I safely remove the model, msdbdata and/or the master
databases from SQL2000 (they may actually have been from
SQL7) without affecting the program operation and
population? I am incredibly low on storage (that is whole
other subject) and have already removed the northwind
database. Thanks...
hi carl,
These are system database, do not touch them.
Delete the pubs and northwind databases, they are sample databases provided by SQL Server.
Dont touch model, master, tempdb or msdb.
Vishal Parkar
vgparkar@.yahoo.co.in
sql
Showing posts with label master. Show all posts
Showing posts with label master. Show all posts
Tuesday, March 27, 2012
Deleting Model, MSDBData, Master Databases
Can I safely remove the model, msdbdata and/or the master
databases from SQL2000 (they may actually have been from
SQL7) without affecting the program operation and
population? I am incredibly low on storage (that is whole
other subject) and have already removed the northwind
database. Thanks...No... Unlike Northwind and Pubs, the three you mentioned are quite
important.
"Carl" <cgrau@.lakelandbank.com> wrote in message
news:19db501c41cd6$c2bfdf30$a101280a@.phx
.gbl...
> Can I safely remove the model, msdbdata and/or the master
> databases from SQL2000 (they may actually have been from
> SQL7) without affecting the program operation and
> population? I am incredibly low on storage (that is whole
> other subject) and have already removed the northwind
> database. Thanks...
databases from SQL2000 (they may actually have been from
SQL7) without affecting the program operation and
population? I am incredibly low on storage (that is whole
other subject) and have already removed the northwind
database. Thanks...No... Unlike Northwind and Pubs, the three you mentioned are quite
important.
"Carl" <cgrau@.lakelandbank.com> wrote in message
news:19db501c41cd6$c2bfdf30$a101280a@.phx
.gbl...
> Can I safely remove the model, msdbdata and/or the master
> databases from SQL2000 (they may actually have been from
> SQL7) without affecting the program operation and
> population? I am incredibly low on storage (that is whole
> other subject) and have already removed the northwind
> database. Thanks...
Deleting Model, MSDBData, Master Databases
Can I safely remove the model, msdbdata and/or the master
databases from SQL2000 (they may actually have been from
SQL7) without affecting the program operation and
population? I am incredibly low on storage (that is whole
other subject) and have already removed the northwind
database. Thanks...
No... Unlike Northwind and Pubs, the three you mentioned are quite
important.
"Carl" <cgrau@.lakelandbank.com> wrote in message
news:19db501c41cd6$c2bfdf30$a101280a@.phx.gbl...
> Can I safely remove the model, msdbdata and/or the master
> databases from SQL2000 (they may actually have been from
> SQL7) without affecting the program operation and
> population? I am incredibly low on storage (that is whole
> other subject) and have already removed the northwind
> database. Thanks...
databases from SQL2000 (they may actually have been from
SQL7) without affecting the program operation and
population? I am incredibly low on storage (that is whole
other subject) and have already removed the northwind
database. Thanks...
No... Unlike Northwind and Pubs, the three you mentioned are quite
important.
"Carl" <cgrau@.lakelandbank.com> wrote in message
news:19db501c41cd6$c2bfdf30$a101280a@.phx.gbl...
> Can I safely remove the model, msdbdata and/or the master
> databases from SQL2000 (they may actually have been from
> SQL7) without affecting the program operation and
> population? I am incredibly low on storage (that is whole
> other subject) and have already removed the northwind
> database. Thanks...
Sunday, March 25, 2012
Deleting jobs when MX Server not available.
Hi All
I have some SQL jobs which I can't delete because they
were originally setup from an MX (master) server. That MX
server is no longer available so how do I delete the jobs?
I assume I'm going to have to delete the entries from the
MSDB datase, but what tables do I need to take the rows
out from?
ThanksIT is easier to just change the originating_server column in sysjobs to your
machine name. This "makes" the jobs normal non-msx jobs. You can do this
with a simple update statement. Note that it is not supported to modify the
system tables directly, so do at your own risk and make sure you have a
backup first.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jono" <anonymous@.discussions.microsoft.com> wrote in message
news:16c801c3a89e$57848e70$a601280a@.phx.gbl...
> Hi All
> I have some SQL jobs which I can't delete because they
> were originally setup from an MX (master) server. That MX
> server is no longer available so how do I delete the jobs?
> I assume I'm going to have to delete the entries from the
> MSDB datase, but what tables do I need to take the rows
> out from?
> Thanks
>|||Thanks Tibor.
I tried it out and it works like a gem.
Thanks again
-Jono
I have some SQL jobs which I can't delete because they
were originally setup from an MX (master) server. That MX
server is no longer available so how do I delete the jobs?
I assume I'm going to have to delete the entries from the
MSDB datase, but what tables do I need to take the rows
out from?
ThanksIT is easier to just change the originating_server column in sysjobs to your
machine name. This "makes" the jobs normal non-msx jobs. You can do this
with a simple update statement. Note that it is not supported to modify the
system tables directly, so do at your own risk and make sure you have a
backup first.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jono" <anonymous@.discussions.microsoft.com> wrote in message
news:16c801c3a89e$57848e70$a601280a@.phx.gbl...
> Hi All
> I have some SQL jobs which I can't delete because they
> were originally setup from an MX (master) server. That MX
> server is no longer available so how do I delete the jobs?
> I assume I'm going to have to delete the entries from the
> MSDB datase, but what tables do I need to take the rows
> out from?
> Thanks
>|||Thanks Tibor.
I tried it out and it works like a gem.
Thanks again
-Jono
Thursday, March 22, 2012
Deleting extended stored procedures in master
Hello
Our Audit group is requiring us to delete many extended stored procedures in
the master database. If you google something to that effect you will see
the list all over the Net of the specific procedures that are recommended
for deletion. You will also see the comment that deleting some of them can
break SQL Server functionality, etc... We deleted all of the
recommendations on a test server and had problems with log shipping, viewing
Agent properties, setting up new users, viewing SQL Server logs, etc. So we
added back xp_regread, xp_availablemedia, xp_getnetname, xp_ntsecenumdomains
and a few more to get those basic functions working again. I sense that
those other registry procedures are used by SQL Server upgrades/service
packs/security patches but we haven't tested that yet.
Has anyone documented what procedure breaks what function? I can see a
whole lotta testing that we'll have to do to put SQL Server through its
paces to see what breaks if we are forced to delete all these procedures.
Documentation on the xps seems a bit sparse from what I can tell.
Ultimately, if they are locked down in master I'm not convinced they should
be deleted. If someone gets as far as master, they could just add them back
anyway. Am I just being naive?
Steve
It's a fairly pointless exercise that leaves you in an unsupported
configuration which should factor into such a decision. The list of extended
procedures with public execute permissions is fairly small and relatively
benign. Plus, if you are running the SQL Service under a non admin domain
account the reach of some that are still public such as xp_regread or
xp_getfiledetails are further curtailed. Do any of your Audit group actually
have any real experience with SQL Server?
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
"Steve" <pezguy@.mn.rr.com> wrote in message
news:1Vuge.53142$215.38295@.tornado.rdc-kc.rr.com...
> Hello
> Our Audit group is requiring us to delete many extended stored procedures
> in the master database. If you google something to that effect you will
> see the list all over the Net of the specific procedures that are
> recommended for deletion. You will also see the comment that deleting
> some of them can break SQL Server functionality, etc... We deleted all
> of the recommendations on a test server and had problems with log
> shipping, viewing Agent properties, setting up new users, viewing SQL
> Server logs, etc. So we added back xp_regread, xp_availablemedia,
> xp_getnetname, xp_ntsecenumdomains and a few more to get those basic
> functions working again. I sense that those other registry procedures are
> used by SQL Server upgrades/service packs/security patches but we haven't
> tested that yet.
> Has anyone documented what procedure breaks what function? I can see a
> whole lotta testing that we'll have to do to put SQL Server through its
> paces to see what breaks if we are forced to delete all these procedures.
> Documentation on the xps seems a bit sparse from what I can tell.
> Ultimately, if they are locked down in master I'm not convinced they
> should be deleted. If someone gets as far as master, they could just add
> them back anyway. Am I just being naive?
> Steve
>
|||Do any of the Audit group have real experience with SQL Server? LOL...it
doesn't appear that way from what I can tell. Part of trying to convince
them that this is a silly requirement is to tell them how it breaks SQL
Server. Thanks for your comments Jasper!
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:OAZylwnVFHA.1152@.tk2msftngp13.phx.gbl...
> It's a fairly pointless exercise that leaves you in an unsupported
> configuration which should factor into such a decision. The list of
> extended procedures with public execute permissions is fairly small and
> relatively benign. Plus, if you are running the SQL Service under a non
> admin domain account the reach of some that are still public such as
> xp_regread or xp_getfiledetails are further curtailed. Do any of your
> Audit group actually have any real experience with SQL Server?
> --
> 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
> "Steve" <pezguy@.mn.rr.com> wrote in message
> news:1Vuge.53142$215.38295@.tornado.rdc-kc.rr.com...
>
|||I have to deal with similar groups were I work so am glad it's not just us
that have to suffer :-) It's all about risk and I would say the risk of
leaving these extended procs in place is extremely low if you factor in the
fact that PSS would probably be perfectly within their rights to refuse to
support such a configuration (not saying they would but they could) if you
did have a real issue. I would certainly press for a list of exploits that
these expose the server to. Too often security/audit departments lay down
rules with no real knowledge or understanding of the product in question and
without any real word basis for the restrictions they want to impose.
Generally the restrictions simply hamper the teams that have to support the
application rather than alleviate any security risk.
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
"Steve" <pezguy@.mn.rr.com> wrote in message
news:qwOge.7$bn.2@.tornado.rdc-kc.rr.com...
> Do any of the Audit group have real experience with SQL Server? LOL...it
> doesn't appear that way from what I can tell. Part of trying to convince
> them that this is a silly requirement is to tell them how it breaks SQL
> Server. Thanks for your comments Jasper!
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:OAZylwnVFHA.1152@.tk2msftngp13.phx.gbl...
>
Our Audit group is requiring us to delete many extended stored procedures in
the master database. If you google something to that effect you will see
the list all over the Net of the specific procedures that are recommended
for deletion. You will also see the comment that deleting some of them can
break SQL Server functionality, etc... We deleted all of the
recommendations on a test server and had problems with log shipping, viewing
Agent properties, setting up new users, viewing SQL Server logs, etc. So we
added back xp_regread, xp_availablemedia, xp_getnetname, xp_ntsecenumdomains
and a few more to get those basic functions working again. I sense that
those other registry procedures are used by SQL Server upgrades/service
packs/security patches but we haven't tested that yet.
Has anyone documented what procedure breaks what function? I can see a
whole lotta testing that we'll have to do to put SQL Server through its
paces to see what breaks if we are forced to delete all these procedures.
Documentation on the xps seems a bit sparse from what I can tell.
Ultimately, if they are locked down in master I'm not convinced they should
be deleted. If someone gets as far as master, they could just add them back
anyway. Am I just being naive?
Steve
It's a fairly pointless exercise that leaves you in an unsupported
configuration which should factor into such a decision. The list of extended
procedures with public execute permissions is fairly small and relatively
benign. Plus, if you are running the SQL Service under a non admin domain
account the reach of some that are still public such as xp_regread or
xp_getfiledetails are further curtailed. Do any of your Audit group actually
have any real experience with SQL Server?
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
"Steve" <pezguy@.mn.rr.com> wrote in message
news:1Vuge.53142$215.38295@.tornado.rdc-kc.rr.com...
> Hello
> Our Audit group is requiring us to delete many extended stored procedures
> in the master database. If you google something to that effect you will
> see the list all over the Net of the specific procedures that are
> recommended for deletion. You will also see the comment that deleting
> some of them can break SQL Server functionality, etc... We deleted all
> of the recommendations on a test server and had problems with log
> shipping, viewing Agent properties, setting up new users, viewing SQL
> Server logs, etc. So we added back xp_regread, xp_availablemedia,
> xp_getnetname, xp_ntsecenumdomains and a few more to get those basic
> functions working again. I sense that those other registry procedures are
> used by SQL Server upgrades/service packs/security patches but we haven't
> tested that yet.
> Has anyone documented what procedure breaks what function? I can see a
> whole lotta testing that we'll have to do to put SQL Server through its
> paces to see what breaks if we are forced to delete all these procedures.
> Documentation on the xps seems a bit sparse from what I can tell.
> Ultimately, if they are locked down in master I'm not convinced they
> should be deleted. If someone gets as far as master, they could just add
> them back anyway. Am I just being naive?
> Steve
>
|||Do any of the Audit group have real experience with SQL Server? LOL...it
doesn't appear that way from what I can tell. Part of trying to convince
them that this is a silly requirement is to tell them how it breaks SQL
Server. Thanks for your comments Jasper!
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:OAZylwnVFHA.1152@.tk2msftngp13.phx.gbl...
> It's a fairly pointless exercise that leaves you in an unsupported
> configuration which should factor into such a decision. The list of
> extended procedures with public execute permissions is fairly small and
> relatively benign. Plus, if you are running the SQL Service under a non
> admin domain account the reach of some that are still public such as
> xp_regread or xp_getfiledetails are further curtailed. Do any of your
> Audit group actually have any real experience with SQL Server?
> --
> 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
> "Steve" <pezguy@.mn.rr.com> wrote in message
> news:1Vuge.53142$215.38295@.tornado.rdc-kc.rr.com...
>
|||I have to deal with similar groups were I work so am glad it's not just us
that have to suffer :-) It's all about risk and I would say the risk of
leaving these extended procs in place is extremely low if you factor in the
fact that PSS would probably be perfectly within their rights to refuse to
support such a configuration (not saying they would but they could) if you
did have a real issue. I would certainly press for a list of exploits that
these expose the server to. Too often security/audit departments lay down
rules with no real knowledge or understanding of the product in question and
without any real word basis for the restrictions they want to impose.
Generally the restrictions simply hamper the teams that have to support the
application rather than alleviate any security risk.
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
"Steve" <pezguy@.mn.rr.com> wrote in message
news:qwOge.7$bn.2@.tornado.rdc-kc.rr.com...
> Do any of the Audit group have real experience with SQL Server? LOL...it
> doesn't appear that way from what I can tell. Part of trying to convince
> them that this is a silly requirement is to tell them how it breaks SQL
> Server. Thanks for your comments Jasper!
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:OAZylwnVFHA.1152@.tk2msftngp13.phx.gbl...
>
Deleting extended stored procedures in master
Hello
Our Audit group is requiring us to delete many extended stored procedures in
the master database. If you google something to that effect you will see
the list all over the Net of the specific procedures that are recommended
for deletion. You will also see the comment that deleting some of them can
break SQL Server functionality, etc... We deleted all of the
recommendations on a test server and had problems with log shipping, viewing
Agent properties, setting up new users, viewing SQL Server logs, etc. So we
added back xp_regread, xp_availablemedia, xp_getnetname, xp_ntsecenumdomains
and a few more to get those basic functions working again. I sense that
those other registry procedures are used by SQL Server upgrades/service
packs/security patches but we haven't tested that yet.
Has anyone documented what procedure breaks what function? I can see a
whole lotta testing that we'll have to do to put SQL Server through its
paces to see what breaks if we are forced to delete all these procedures.
Documentation on the xps seems a bit sparse from what I can tell.
Ultimately, if they are locked down in master I'm not convinced they should
be deleted. If someone gets as far as master, they could just add them back
anyway. Am I just being naive?
SteveIt's a fairly pointless exercise that leaves you in an unsupported
configuration which should factor into such a decision. The list of extended
procedures with public execute permissions is fairly small and relatively
benign. Plus, if you are running the SQL Service under a non admin domain
account the reach of some that are still public such as xp_regread or
xp_getfiledetails are further curtailed. Do any of your Audit group actually
have any real experience with SQL Server?
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
"Steve" <pezguy@.mn.rr.com> wrote in message
news:1Vuge.53142$215.38295@.tornado.rdc-kc.rr.com...
> Hello
> Our Audit group is requiring us to delete many extended stored procedures
> in the master database. If you google something to that effect you will
> see the list all over the Net of the specific procedures that are
> recommended for deletion. You will also see the comment that deleting
> some of them can break SQL Server functionality, etc... We deleted all
> of the recommendations on a test server and had problems with log
> shipping, viewing Agent properties, setting up new users, viewing SQL
> Server logs, etc. So we added back xp_regread, xp_availablemedia,
> xp_getnetname, xp_ntsecenumdomains and a few more to get those basic
> functions working again. I sense that those other registry procedures are
> used by SQL Server upgrades/service packs/security patches but we haven't
> tested that yet.
> Has anyone documented what procedure breaks what function? I can see a
> whole lotta testing that we'll have to do to put SQL Server through its
> paces to see what breaks if we are forced to delete all these procedures.
> Documentation on the xps seems a bit sparse from what I can tell.
> Ultimately, if they are locked down in master I'm not convinced they
> should be deleted. If someone gets as far as master, they could just add
> them back anyway. Am I just being naive?
> Steve
>|||Do any of the Audit group have real experience with SQL Server? LOL...it
doesn't appear that way from what I can tell. Part of trying to convince
them that this is a silly requirement is to tell them how it breaks SQL
Server. Thanks for your comments Jasper!
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:OAZylwnVFHA.1152@.tk2msftngp13.phx.gbl...
> It's a fairly pointless exercise that leaves you in an unsupported
> configuration which should factor into such a decision. The list of
> extended procedures with public execute permissions is fairly small and
> relatively benign. Plus, if you are running the SQL Service under a non
> admin domain account the reach of some that are still public such as
> xp_regread or xp_getfiledetails are further curtailed. Do any of your
> Audit group actually have any real experience with SQL Server?
> --
> 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
> "Steve" <pezguy@.mn.rr.com> wrote in message
> news:1Vuge.53142$215.38295@.tornado.rdc-kc.rr.com...
>|||I have to deal with similar groups were I work so am glad it's not just us
that have to suffer :-) It's all about risk and I would say the risk of
leaving these extended procs in place is extremely low if you factor in the
fact that PSS would probably be perfectly within their rights to refuse to
support such a configuration (not saying they would but they could) if you
did have a real issue. I would certainly press for a list of exploits that
these expose the server to. Too often security/audit departments lay down
rules with no real knowledge or understanding of the product in question and
without any real word basis for the restrictions they want to impose.
Generally the restrictions simply hamper the teams that have to support the
application rather than alleviate any security risk.
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
"Steve" <pezguy@.mn.rr.com> wrote in message
news:qwOge.7$bn.2@.tornado.rdc-kc.rr.com...
> Do any of the Audit group have real experience with SQL Server? LOL...it
> doesn't appear that way from what I can tell. Part of trying to convince
> them that this is a silly requirement is to tell them how it breaks SQL
> Server. Thanks for your comments Jasper!
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:OAZylwnVFHA.1152@.tk2msftngp13.phx.gbl...
>sql
Our Audit group is requiring us to delete many extended stored procedures in
the master database. If you google something to that effect you will see
the list all over the Net of the specific procedures that are recommended
for deletion. You will also see the comment that deleting some of them can
break SQL Server functionality, etc... We deleted all of the
recommendations on a test server and had problems with log shipping, viewing
Agent properties, setting up new users, viewing SQL Server logs, etc. So we
added back xp_regread, xp_availablemedia, xp_getnetname, xp_ntsecenumdomains
and a few more to get those basic functions working again. I sense that
those other registry procedures are used by SQL Server upgrades/service
packs/security patches but we haven't tested that yet.
Has anyone documented what procedure breaks what function? I can see a
whole lotta testing that we'll have to do to put SQL Server through its
paces to see what breaks if we are forced to delete all these procedures.
Documentation on the xps seems a bit sparse from what I can tell.
Ultimately, if they are locked down in master I'm not convinced they should
be deleted. If someone gets as far as master, they could just add them back
anyway. Am I just being naive?
SteveIt's a fairly pointless exercise that leaves you in an unsupported
configuration which should factor into such a decision. The list of extended
procedures with public execute permissions is fairly small and relatively
benign. Plus, if you are running the SQL Service under a non admin domain
account the reach of some that are still public such as xp_regread or
xp_getfiledetails are further curtailed. Do any of your Audit group actually
have any real experience with SQL Server?
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
"Steve" <pezguy@.mn.rr.com> wrote in message
news:1Vuge.53142$215.38295@.tornado.rdc-kc.rr.com...
> Hello
> Our Audit group is requiring us to delete many extended stored procedures
> in the master database. If you google something to that effect you will
> see the list all over the Net of the specific procedures that are
> recommended for deletion. You will also see the comment that deleting
> some of them can break SQL Server functionality, etc... We deleted all
> of the recommendations on a test server and had problems with log
> shipping, viewing Agent properties, setting up new users, viewing SQL
> Server logs, etc. So we added back xp_regread, xp_availablemedia,
> xp_getnetname, xp_ntsecenumdomains and a few more to get those basic
> functions working again. I sense that those other registry procedures are
> used by SQL Server upgrades/service packs/security patches but we haven't
> tested that yet.
> Has anyone documented what procedure breaks what function? I can see a
> whole lotta testing that we'll have to do to put SQL Server through its
> paces to see what breaks if we are forced to delete all these procedures.
> Documentation on the xps seems a bit sparse from what I can tell.
> Ultimately, if they are locked down in master I'm not convinced they
> should be deleted. If someone gets as far as master, they could just add
> them back anyway. Am I just being naive?
> Steve
>|||Do any of the Audit group have real experience with SQL Server? LOL...it
doesn't appear that way from what I can tell. Part of trying to convince
them that this is a silly requirement is to tell them how it breaks SQL
Server. Thanks for your comments Jasper!
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:OAZylwnVFHA.1152@.tk2msftngp13.phx.gbl...
> It's a fairly pointless exercise that leaves you in an unsupported
> configuration which should factor into such a decision. The list of
> extended procedures with public execute permissions is fairly small and
> relatively benign. Plus, if you are running the SQL Service under a non
> admin domain account the reach of some that are still public such as
> xp_regread or xp_getfiledetails are further curtailed. Do any of your
> Audit group actually have any real experience with SQL Server?
> --
> 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
> "Steve" <pezguy@.mn.rr.com> wrote in message
> news:1Vuge.53142$215.38295@.tornado.rdc-kc.rr.com...
>|||I have to deal with similar groups were I work so am glad it's not just us
that have to suffer :-) It's all about risk and I would say the risk of
leaving these extended procs in place is extremely low if you factor in the
fact that PSS would probably be perfectly within their rights to refuse to
support such a configuration (not saying they would but they could) if you
did have a real issue. I would certainly press for a list of exploits that
these expose the server to. Too often security/audit departments lay down
rules with no real knowledge or understanding of the product in question and
without any real word basis for the restrictions they want to impose.
Generally the restrictions simply hamper the teams that have to support the
application rather than alleviate any security risk.
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
"Steve" <pezguy@.mn.rr.com> wrote in message
news:qwOge.7$bn.2@.tornado.rdc-kc.rr.com...
> Do any of the Audit group have real experience with SQL Server? LOL...it
> doesn't appear that way from what I can tell. Part of trying to convince
> them that this is a silly requirement is to tell them how it breaks SQL
> Server. Thanks for your comments Jasper!
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:OAZylwnVFHA.1152@.tk2msftngp13.phx.gbl...
>sql
Deleting extended stored procedures in master
Hello
Our Audit group is requiring us to delete many extended stored procedures in
the master database. If you google something to that effect you will see
the list all over the Net of the specific procedures that are recommended
for deletion. You will also see the comment that deleting some of them can
break SQL Server functionality, etc... We deleted all of the
recommendations on a test server and had problems with log shipping, viewing
Agent properties, setting up new users, viewing SQL Server logs, etc. So we
added back xp_regread, xp_availablemedia, xp_getnetname, xp_ntsecenumdomains
and a few more to get those basic functions working again. I sense that
those other registry procedures are used by SQL Server upgrades/service
packs/security patches but we haven't tested that yet.
Has anyone documented what procedure breaks what function? I can see a
whole lotta testing that we'll have to do to put SQL Server through its
paces to see what breaks if we are forced to delete all these procedures.
Documentation on the xps seems a bit sparse from what I can tell.
Ultimately, if they are locked down in master I'm not convinced they should
be deleted. If someone gets as far as master, they could just add them back
anyway. Am I just being naive?
SteveIt's a fairly pointless exercise that leaves you in an unsupported
configuration which should factor into such a decision. The list of extended
procedures with public execute permissions is fairly small and relatively
benign. Plus, if you are running the SQL Service under a non admin domain
account the reach of some that are still public such as xp_regread or
xp_getfiledetails are further curtailed. Do any of your Audit group actually
have any real experience with SQL Server?
--
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
"Steve" <pezguy@.mn.rr.com> wrote in message
news:1Vuge.53142$215.38295@.tornado.rdc-kc.rr.com...
> Hello
> Our Audit group is requiring us to delete many extended stored procedures
> in the master database. If you google something to that effect you will
> see the list all over the Net of the specific procedures that are
> recommended for deletion. You will also see the comment that deleting
> some of them can break SQL Server functionality, etc... We deleted all
> of the recommendations on a test server and had problems with log
> shipping, viewing Agent properties, setting up new users, viewing SQL
> Server logs, etc. So we added back xp_regread, xp_availablemedia,
> xp_getnetname, xp_ntsecenumdomains and a few more to get those basic
> functions working again. I sense that those other registry procedures are
> used by SQL Server upgrades/service packs/security patches but we haven't
> tested that yet.
> Has anyone documented what procedure breaks what function? I can see a
> whole lotta testing that we'll have to do to put SQL Server through its
> paces to see what breaks if we are forced to delete all these procedures.
> Documentation on the xps seems a bit sparse from what I can tell.
> Ultimately, if they are locked down in master I'm not convinced they
> should be deleted. If someone gets as far as master, they could just add
> them back anyway. Am I just being naive?
> Steve
>|||Do any of the Audit group have real experience with SQL Server? LOL...it
doesn't appear that way from what I can tell. Part of trying to convince
them that this is a silly requirement is to tell them how it breaks SQL
Server. Thanks for your comments Jasper!
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:OAZylwnVFHA.1152@.tk2msftngp13.phx.gbl...
> It's a fairly pointless exercise that leaves you in an unsupported
> configuration which should factor into such a decision. The list of
> extended procedures with public execute permissions is fairly small and
> relatively benign. Plus, if you are running the SQL Service under a non
> admin domain account the reach of some that are still public such as
> xp_regread or xp_getfiledetails are further curtailed. Do any of your
> Audit group actually have any real experience with SQL Server?
> --
> 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
> "Steve" <pezguy@.mn.rr.com> wrote in message
> news:1Vuge.53142$215.38295@.tornado.rdc-kc.rr.com...
>> Hello
>> Our Audit group is requiring us to delete many extended stored procedures
>> in the master database. If you google something to that effect you will
>> see the list all over the Net of the specific procedures that are
>> recommended for deletion. You will also see the comment that deleting
>> some of them can break SQL Server functionality, etc... We deleted all
>> of the recommendations on a test server and had problems with log
>> shipping, viewing Agent properties, setting up new users, viewing SQL
>> Server logs, etc. So we added back xp_regread, xp_availablemedia,
>> xp_getnetname, xp_ntsecenumdomains and a few more to get those basic
>> functions working again. I sense that those other registry procedures
>> are used by SQL Server upgrades/service packs/security patches but we
>> haven't tested that yet.
>> Has anyone documented what procedure breaks what function? I can see a
>> whole lotta testing that we'll have to do to put SQL Server through its
>> paces to see what breaks if we are forced to delete all these procedures.
>> Documentation on the xps seems a bit sparse from what I can tell.
>> Ultimately, if they are locked down in master I'm not convinced they
>> should be deleted. If someone gets as far as master, they could just add
>> them back anyway. Am I just being naive?
>> Steve
>|||I have to deal with similar groups were I work so am glad it's not just us
that have to suffer :-) It's all about risk and I would say the risk of
leaving these extended procs in place is extremely low if you factor in the
fact that PSS would probably be perfectly within their rights to refuse to
support such a configuration (not saying they would but they could) if you
did have a real issue. I would certainly press for a list of exploits that
these expose the server to. Too often security/audit departments lay down
rules with no real knowledge or understanding of the product in question and
without any real word basis for the restrictions they want to impose.
Generally the restrictions simply hamper the teams that have to support the
application rather than alleviate any security risk.
--
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
"Steve" <pezguy@.mn.rr.com> wrote in message
news:qwOge.7$bn.2@.tornado.rdc-kc.rr.com...
> Do any of the Audit group have real experience with SQL Server? LOL...it
> doesn't appear that way from what I can tell. Part of trying to convince
> them that this is a silly requirement is to tell them how it breaks SQL
> Server. Thanks for your comments Jasper!
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:OAZylwnVFHA.1152@.tk2msftngp13.phx.gbl...
>> It's a fairly pointless exercise that leaves you in an unsupported
>> configuration which should factor into such a decision. The list of
>> extended procedures with public execute permissions is fairly small and
>> relatively benign. Plus, if you are running the SQL Service under a non
>> admin domain account the reach of some that are still public such as
>> xp_regread or xp_getfiledetails are further curtailed. Do any of your
>> Audit group actually have any real experience with SQL Server?
>> --
>> 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
>> "Steve" <pezguy@.mn.rr.com> wrote in message
>> news:1Vuge.53142$215.38295@.tornado.rdc-kc.rr.com...
>> Hello
>> Our Audit group is requiring us to delete many extended stored
>> procedures in the master database. If you google something to that
>> effect you will see the list all over the Net of the specific procedures
>> that are recommended for deletion. You will also see the comment that
>> deleting some of them can break SQL Server functionality, etc... We
>> deleted all of the recommendations on a test server and had problems
>> with log shipping, viewing Agent properties, setting up new users,
>> viewing SQL Server logs, etc. So we added back xp_regread,
>> xp_availablemedia, xp_getnetname, xp_ntsecenumdomains and a few more to
>> get those basic functions working again. I sense that those other
>> registry procedures are used by SQL Server upgrades/service
>> packs/security patches but we haven't tested that yet.
>> Has anyone documented what procedure breaks what function? I can see a
>> whole lotta testing that we'll have to do to put SQL Server through its
>> paces to see what breaks if we are forced to delete all these
>> procedures. Documentation on the xps seems a bit sparse from what I can
>> tell.
>> Ultimately, if they are locked down in master I'm not convinced they
>> should be deleted. If someone gets as far as master, they could just
>> add them back anyway. Am I just being naive?
>> Steve
>>
>
Our Audit group is requiring us to delete many extended stored procedures in
the master database. If you google something to that effect you will see
the list all over the Net of the specific procedures that are recommended
for deletion. You will also see the comment that deleting some of them can
break SQL Server functionality, etc... We deleted all of the
recommendations on a test server and had problems with log shipping, viewing
Agent properties, setting up new users, viewing SQL Server logs, etc. So we
added back xp_regread, xp_availablemedia, xp_getnetname, xp_ntsecenumdomains
and a few more to get those basic functions working again. I sense that
those other registry procedures are used by SQL Server upgrades/service
packs/security patches but we haven't tested that yet.
Has anyone documented what procedure breaks what function? I can see a
whole lotta testing that we'll have to do to put SQL Server through its
paces to see what breaks if we are forced to delete all these procedures.
Documentation on the xps seems a bit sparse from what I can tell.
Ultimately, if they are locked down in master I'm not convinced they should
be deleted. If someone gets as far as master, they could just add them back
anyway. Am I just being naive?
SteveIt's a fairly pointless exercise that leaves you in an unsupported
configuration which should factor into such a decision. The list of extended
procedures with public execute permissions is fairly small and relatively
benign. Plus, if you are running the SQL Service under a non admin domain
account the reach of some that are still public such as xp_regread or
xp_getfiledetails are further curtailed. Do any of your Audit group actually
have any real experience with SQL Server?
--
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
"Steve" <pezguy@.mn.rr.com> wrote in message
news:1Vuge.53142$215.38295@.tornado.rdc-kc.rr.com...
> Hello
> Our Audit group is requiring us to delete many extended stored procedures
> in the master database. If you google something to that effect you will
> see the list all over the Net of the specific procedures that are
> recommended for deletion. You will also see the comment that deleting
> some of them can break SQL Server functionality, etc... We deleted all
> of the recommendations on a test server and had problems with log
> shipping, viewing Agent properties, setting up new users, viewing SQL
> Server logs, etc. So we added back xp_regread, xp_availablemedia,
> xp_getnetname, xp_ntsecenumdomains and a few more to get those basic
> functions working again. I sense that those other registry procedures are
> used by SQL Server upgrades/service packs/security patches but we haven't
> tested that yet.
> Has anyone documented what procedure breaks what function? I can see a
> whole lotta testing that we'll have to do to put SQL Server through its
> paces to see what breaks if we are forced to delete all these procedures.
> Documentation on the xps seems a bit sparse from what I can tell.
> Ultimately, if they are locked down in master I'm not convinced they
> should be deleted. If someone gets as far as master, they could just add
> them back anyway. Am I just being naive?
> Steve
>|||Do any of the Audit group have real experience with SQL Server? LOL...it
doesn't appear that way from what I can tell. Part of trying to convince
them that this is a silly requirement is to tell them how it breaks SQL
Server. Thanks for your comments Jasper!
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:OAZylwnVFHA.1152@.tk2msftngp13.phx.gbl...
> It's a fairly pointless exercise that leaves you in an unsupported
> configuration which should factor into such a decision. The list of
> extended procedures with public execute permissions is fairly small and
> relatively benign. Plus, if you are running the SQL Service under a non
> admin domain account the reach of some that are still public such as
> xp_regread or xp_getfiledetails are further curtailed. Do any of your
> Audit group actually have any real experience with SQL Server?
> --
> 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
> "Steve" <pezguy@.mn.rr.com> wrote in message
> news:1Vuge.53142$215.38295@.tornado.rdc-kc.rr.com...
>> Hello
>> Our Audit group is requiring us to delete many extended stored procedures
>> in the master database. If you google something to that effect you will
>> see the list all over the Net of the specific procedures that are
>> recommended for deletion. You will also see the comment that deleting
>> some of them can break SQL Server functionality, etc... We deleted all
>> of the recommendations on a test server and had problems with log
>> shipping, viewing Agent properties, setting up new users, viewing SQL
>> Server logs, etc. So we added back xp_regread, xp_availablemedia,
>> xp_getnetname, xp_ntsecenumdomains and a few more to get those basic
>> functions working again. I sense that those other registry procedures
>> are used by SQL Server upgrades/service packs/security patches but we
>> haven't tested that yet.
>> Has anyone documented what procedure breaks what function? I can see a
>> whole lotta testing that we'll have to do to put SQL Server through its
>> paces to see what breaks if we are forced to delete all these procedures.
>> Documentation on the xps seems a bit sparse from what I can tell.
>> Ultimately, if they are locked down in master I'm not convinced they
>> should be deleted. If someone gets as far as master, they could just add
>> them back anyway. Am I just being naive?
>> Steve
>|||I have to deal with similar groups were I work so am glad it's not just us
that have to suffer :-) It's all about risk and I would say the risk of
leaving these extended procs in place is extremely low if you factor in the
fact that PSS would probably be perfectly within their rights to refuse to
support such a configuration (not saying they would but they could) if you
did have a real issue. I would certainly press for a list of exploits that
these expose the server to. Too often security/audit departments lay down
rules with no real knowledge or understanding of the product in question and
without any real word basis for the restrictions they want to impose.
Generally the restrictions simply hamper the teams that have to support the
application rather than alleviate any security risk.
--
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
"Steve" <pezguy@.mn.rr.com> wrote in message
news:qwOge.7$bn.2@.tornado.rdc-kc.rr.com...
> Do any of the Audit group have real experience with SQL Server? LOL...it
> doesn't appear that way from what I can tell. Part of trying to convince
> them that this is a silly requirement is to tell them how it breaks SQL
> Server. Thanks for your comments Jasper!
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:OAZylwnVFHA.1152@.tk2msftngp13.phx.gbl...
>> It's a fairly pointless exercise that leaves you in an unsupported
>> configuration which should factor into such a decision. The list of
>> extended procedures with public execute permissions is fairly small and
>> relatively benign. Plus, if you are running the SQL Service under a non
>> admin domain account the reach of some that are still public such as
>> xp_regread or xp_getfiledetails are further curtailed. Do any of your
>> Audit group actually have any real experience with SQL Server?
>> --
>> 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
>> "Steve" <pezguy@.mn.rr.com> wrote in message
>> news:1Vuge.53142$215.38295@.tornado.rdc-kc.rr.com...
>> Hello
>> Our Audit group is requiring us to delete many extended stored
>> procedures in the master database. If you google something to that
>> effect you will see the list all over the Net of the specific procedures
>> that are recommended for deletion. You will also see the comment that
>> deleting some of them can break SQL Server functionality, etc... We
>> deleted all of the recommendations on a test server and had problems
>> with log shipping, viewing Agent properties, setting up new users,
>> viewing SQL Server logs, etc. So we added back xp_regread,
>> xp_availablemedia, xp_getnetname, xp_ntsecenumdomains and a few more to
>> get those basic functions working again. I sense that those other
>> registry procedures are used by SQL Server upgrades/service
>> packs/security patches but we haven't tested that yet.
>> Has anyone documented what procedure breaks what function? I can see a
>> whole lotta testing that we'll have to do to put SQL Server through its
>> paces to see what breaks if we are forced to delete all these
>> procedures. Documentation on the xps seems a bit sparse from what I can
>> tell.
>> Ultimately, if they are locked down in master I'm not convinced they
>> should be deleted. If someone gets as far as master, they could just
>> add them back anyway. Am I just being naive?
>> Steve
>>
>
Sunday, March 11, 2012
Deletes From Multiple Tables.
Hello everybody,
We have a Master table in the Db who's PK is referenced in at least 60 tables as FK.
We want to delete all the dependent records in 1 go without using multiple delete statements or any cursor or loop.
Is it possible? Please advice.
Thanks in advance.
Regards,
Ashishuse "Cascade Delete"...|||I'm sorry i forgot to mention that the Cascading Deletes are not allowed.
Even wrtting a trigger to delete from all the tables will mean writing some kind of loop or Cursor if we use the system tables.
I can use sys tables but the delete qury using sys tbales needs to execute in 1 go. No cursors or temp tables should be used.
Is there any other way?|||I'm sorry i forgot to mention that the Cascading Deletes are not allowed.
Frickin' morons.
My advice is to go work for a company that isn't managed by idiots.|||Now there's the blind dude we all know and love|||No cascading deletes allowed.
No temp tables allowed.
No cursors allowed. (Well, at least they are on the right track with that one...)
Absolute amateurs.
What advice would you give them?|||What advice would you give them?
Well, it realy sounds like a request for homework...but
DECLARE @.FKey sysname, @.keyvalue varchar(8000), @.sql varchar(8000)
SELECT @.FKey = 'EmployeeID', @.keyValue = '1', @.sql = ''
SELECT @.sql = @.sql + 'DELETE FROM ['+o.name+'] WHERE '+@.FKey+'='+@.KeyValue+' GO '
FROM sysreferences r
LEFT JOIN sysobjects o
ON r.fkeyid = o.id
WHERE rkeyid = OBJECT_ID('Employees')
SELECT @.sql
--EXEC(@.sql)|||Thanks dear friends.
Brett, it's not a homework at all.
In fact, i'm working on the one of the largest .Net implementations in the world. But somehow we have some basic guidelines which do not allow certain things.
Me being the modest developer cannot argue with the people who have set these guidelines. Please forgive those poor souls for their ignorance.
Thanks once again. :)|||In fact, i'm working on the one of the largest .Net implementations in the world. But somehow we have some basic guidelines which do not allow certain things
Using cascading deletes is the best solution for this problem (and is supported by all major DBMS).
If the "world largest" project does not apply the most effectice solutions to such a basic problem, then I'm pretty sure it is going to fail. This is extremely short-sighted and will create more maintenance trouble then they probably are willing to pay for. Why not use flat files, if they don't want to use the features of a database?
(nothing personal against you wash, it's just that I see this kind of stupid decisions all around...)|||Man, i don't mind it as long as they are paying me for the job. [:D]
I do not exactly know what has prompted the TAs to avoid cascade deletes.|||In fact, i'm working on the one of the largest .Net implementations in the world. But somehow we have some basic guidelines which do not allow certain things.
reading this made think he was working for the US federal government. in which case it may very well fail. The FBI can not get their case management software together, the IRS has been struggling for years to modernize.|||reading this made think he was working for the US federal government. in which case it may very well fail. The FBI can not get their case management software together, the IRS has been struggling for years to modernize.
Forgive me but i don't work on any such project.|||I have had everyone in my team at my company, including the so called SQL experts working with me, brainstorm on this issue.
It seems without a recursive loop , we cannot achieve this.
Can you give me any idea if we can achieve this using a simple loop.
I want to delete all the dependent records to the Nth level.
Thanks in advance.|||HAve one of your "sql experts" get in on this thread...and did you even look at my code?|||What in the world would drive such requirements? It makes as much sense as "We'd like to retrieve all the data from the database but I can't reference any table names or use the word 'select'".
I'm also guessing that you're going to generate a heap of log activity with this puppy, so you might want to think about doing a set of tables at a time or a set of primary key values, unless you own unlimited disk.|||Thanks Brett. i did use your code but it useful only for the first level of dependencies.
Thankfully people have realized what kind of issues have cropped up due to such requirement and thankfully we are working on some realistic solution.
I would like to thank everyone for chipping in with helpful suggestions and valuable advice.
thanks once again.
We have a Master table in the Db who's PK is referenced in at least 60 tables as FK.
We want to delete all the dependent records in 1 go without using multiple delete statements or any cursor or loop.
Is it possible? Please advice.
Thanks in advance.
Regards,
Ashishuse "Cascade Delete"...|||I'm sorry i forgot to mention that the Cascading Deletes are not allowed.
Even wrtting a trigger to delete from all the tables will mean writing some kind of loop or Cursor if we use the system tables.
I can use sys tables but the delete qury using sys tbales needs to execute in 1 go. No cursors or temp tables should be used.
Is there any other way?|||I'm sorry i forgot to mention that the Cascading Deletes are not allowed.
Frickin' morons.
My advice is to go work for a company that isn't managed by idiots.|||Now there's the blind dude we all know and love|||No cascading deletes allowed.
No temp tables allowed.
No cursors allowed. (Well, at least they are on the right track with that one...)
Absolute amateurs.
What advice would you give them?|||What advice would you give them?
Well, it realy sounds like a request for homework...but
DECLARE @.FKey sysname, @.keyvalue varchar(8000), @.sql varchar(8000)
SELECT @.FKey = 'EmployeeID', @.keyValue = '1', @.sql = ''
SELECT @.sql = @.sql + 'DELETE FROM ['+o.name+'] WHERE '+@.FKey+'='+@.KeyValue+' GO '
FROM sysreferences r
LEFT JOIN sysobjects o
ON r.fkeyid = o.id
WHERE rkeyid = OBJECT_ID('Employees')
SELECT @.sql
--EXEC(@.sql)|||Thanks dear friends.
Brett, it's not a homework at all.
In fact, i'm working on the one of the largest .Net implementations in the world. But somehow we have some basic guidelines which do not allow certain things.
Me being the modest developer cannot argue with the people who have set these guidelines. Please forgive those poor souls for their ignorance.
Thanks once again. :)|||In fact, i'm working on the one of the largest .Net implementations in the world. But somehow we have some basic guidelines which do not allow certain things
Using cascading deletes is the best solution for this problem (and is supported by all major DBMS).
If the "world largest" project does not apply the most effectice solutions to such a basic problem, then I'm pretty sure it is going to fail. This is extremely short-sighted and will create more maintenance trouble then they probably are willing to pay for. Why not use flat files, if they don't want to use the features of a database?
(nothing personal against you wash, it's just that I see this kind of stupid decisions all around...)|||Man, i don't mind it as long as they are paying me for the job. [:D]
I do not exactly know what has prompted the TAs to avoid cascade deletes.|||In fact, i'm working on the one of the largest .Net implementations in the world. But somehow we have some basic guidelines which do not allow certain things.
reading this made think he was working for the US federal government. in which case it may very well fail. The FBI can not get their case management software together, the IRS has been struggling for years to modernize.|||reading this made think he was working for the US federal government. in which case it may very well fail. The FBI can not get their case management software together, the IRS has been struggling for years to modernize.
Forgive me but i don't work on any such project.|||I have had everyone in my team at my company, including the so called SQL experts working with me, brainstorm on this issue.
It seems without a recursive loop , we cannot achieve this.
Can you give me any idea if we can achieve this using a simple loop.
I want to delete all the dependent records to the Nth level.
Thanks in advance.|||HAve one of your "sql experts" get in on this thread...and did you even look at my code?|||What in the world would drive such requirements? It makes as much sense as "We'd like to retrieve all the data from the database but I can't reference any table names or use the word 'select'".
I'm also guessing that you're going to generate a heap of log activity with this puppy, so you might want to think about doing a set of tables at a time or a set of primary key values, unless you own unlimited disk.|||Thanks Brett. i did use your code but it useful only for the first level of dependencies.
Thankfully people have realized what kind of issues have cropped up due to such requirement and thankfully we are working on some realistic solution.
I would like to thank everyone for chipping in with helpful suggestions and valuable advice.
thanks once again.
deleted xp_cmdshell... how do I restore it?
For security reasons I dropped xp_cmdshell from my master database. Now
I find that I need it once again... is there a way to restore it or
somehow copy it from another database server? I've tried to use
enterprise manager to DTS it over but I can't DTS anything from the
master database. Is there any other way?
Thanks in advance.
Rsto create it back use the following and let know if it works:
Use Master
sp_addextendedproc xp_cmdshell, 'xplog70.dll'
I find that I need it once again... is there a way to restore it or
somehow copy it from another database server? I've tried to use
enterprise manager to DTS it over but I can't DTS anything from the
master database. Is there any other way?
Thanks in advance.
Rsto create it back use the following and let know if it works:
Use Master
sp_addextendedproc xp_cmdshell, 'xplog70.dll'
deleted xp_cmdshell... how do I restore it?
For security reasons I dropped xp_cmdshell from my master database. Now
I find that I need it once again... is there a way to restore it or
somehow copy it from another database server? I've tried to use
enterprise manager to DTS it over but I can't DTS anything from the
master database. Is there any other way?
Thanks in advance.
Rsto create it back use the following and let know if it works:
Use Master
sp_addextendedproc xp_cmdshell, 'xplog70.dll'
I find that I need it once again... is there a way to restore it or
somehow copy it from another database server? I've tried to use
enterprise manager to DTS it over but I can't DTS anything from the
master database. Is there any other way?
Thanks in advance.
Rsto create it back use the following and let know if it works:
Use Master
sp_addextendedproc xp_cmdshell, 'xplog70.dll'
Friday, March 9, 2012
Deleted all data files of an instance
I have an instance where all of the data files (mdf and ldf) for every
database including master and msdb have been deleted. I need to restore
all databases from (native sql) backup, however I'm not sure how to
connect to the instance to perform the database restore.
The only way I can think of achieving this is to un-install the
instance, re-install the instance with the same name and then restore
my existing databases including master, msdb over the new ones. It's a
dev/test environment so not critical, but I'd appreciate any comments
on this strategy..
Regards,
David West
Read BOL for restore system databases.It will be good help to u.
from
Doller
|||If somebody else deleted the .mdf and .ldf files by mistake first look for
them on the Recycle Bin :-)
If the master database is not available, SQL Server will not start. Read
'Rebuild Master utility' from BOL. Run rebuildm and follow the instructions.
After this you will have your system databases restored.
Start SQL Server in single user mode using sqlservr -m to restore the master
database from your backup.
Then you can restore the other databases.
Ben Nevarez
<davidawest@.gmail.com> wrote in message
news:1131941368.575648.226390@.g47g2000cwa.googlegr oups.com...
>I have an instance where all of the data files (mdf and ldf) for every
> database including master and msdb have been deleted. I need to restore
> all databases from (native sql) backup, however I'm not sure how to
> connect to the instance to perform the database restore.
> The only way I can think of achieving this is to un-install the
> instance, re-install the instance with the same name and then restore
> my existing databases including master, msdb over the new ones. It's a
> dev/test environment so not critical, but I'd appreciate any comments
> on this strategy..
> Regards,
> David West
>
|||Thanks Ben.
The 'Rebuild Master' utility wants the source directory containing data
files. How do I extract the mdf and ldf files from my sql backup? ie. a
native sql backup which is a single file.
David
|||rebuildm asks for the SQL Server CD. On the CD browse to the data directory.
You will restore your master database backup (*.bak) later when starting SQL
Server in single user mode using sqlservr -m.
Ben Nevarez
<davidawest@.gmail.com> wrote in message
news:1132023984.137926.54610@.g43g2000cwa.googlegro ups.com...
> Thanks Ben.
> The 'Rebuild Master' utility wants the source directory containing data
> files. How do I extract the mdf and ldf files from my sql backup? ie. a
> native sql backup which is a single file.
> David
>
|||OK. That makes sense. Now I get the below error is cnfgsvr.out
################################################## #############################
Starting Service ...
SQL_Latin1_General_CP1_CI_AS
-m -Q -T4022 -T3659
An error occurred while attempting to start the service (1460)
SQL Server configuration failed.
################################################## #############################
|||Worked it out. Files copied from cd were read-only. I copied the files
locally, reset read-only bit and rebuilt from local files. Thanks for
all your help!
Cheers,
David
|||Hi,
You can give a try to Active@. undelete or Uneraser (for DOS) tool. IT
worked great for me and never failed before, so I suppose it might
really help.
http://www.active-undelete.com/
http://www.uneraser.com/
|||Hello,
EASEUS DataRecoveryWizard utility can help. Speaking about me, it was
easily able to restore deleted, lost file and unformat drive,
so I think you will also find it quite useful. Really recommended
tool, give it a try.
http://www.easeus.com/
Good work, Good day.
"davidawest@.gmail.com" wrote:
> I have an instance where all of the data files (mdf and ldf) for every
> database including master and msdb have been deleted. I need to restore
> all databases from (native sql) backup, however I'm not sure how to
> connect to the instance to perform the database restore.
> The only way I can think of achieving this is to un-install the
> instance, re-install the instance with the same name and then restore
> my existing databases including master, msdb over the new ones. It's a
> dev/test environment so not critical, but I'd appreciate any comments
> on this strategy..
> Regards,
> David West
>
database including master and msdb have been deleted. I need to restore
all databases from (native sql) backup, however I'm not sure how to
connect to the instance to perform the database restore.
The only way I can think of achieving this is to un-install the
instance, re-install the instance with the same name and then restore
my existing databases including master, msdb over the new ones. It's a
dev/test environment so not critical, but I'd appreciate any comments
on this strategy..
Regards,
David West
Read BOL for restore system databases.It will be good help to u.
from
Doller
|||If somebody else deleted the .mdf and .ldf files by mistake first look for
them on the Recycle Bin :-)
If the master database is not available, SQL Server will not start. Read
'Rebuild Master utility' from BOL. Run rebuildm and follow the instructions.
After this you will have your system databases restored.
Start SQL Server in single user mode using sqlservr -m to restore the master
database from your backup.
Then you can restore the other databases.
Ben Nevarez
<davidawest@.gmail.com> wrote in message
news:1131941368.575648.226390@.g47g2000cwa.googlegr oups.com...
>I have an instance where all of the data files (mdf and ldf) for every
> database including master and msdb have been deleted. I need to restore
> all databases from (native sql) backup, however I'm not sure how to
> connect to the instance to perform the database restore.
> The only way I can think of achieving this is to un-install the
> instance, re-install the instance with the same name and then restore
> my existing databases including master, msdb over the new ones. It's a
> dev/test environment so not critical, but I'd appreciate any comments
> on this strategy..
> Regards,
> David West
>
|||Thanks Ben.
The 'Rebuild Master' utility wants the source directory containing data
files. How do I extract the mdf and ldf files from my sql backup? ie. a
native sql backup which is a single file.
David
|||rebuildm asks for the SQL Server CD. On the CD browse to the data directory.
You will restore your master database backup (*.bak) later when starting SQL
Server in single user mode using sqlservr -m.
Ben Nevarez
<davidawest@.gmail.com> wrote in message
news:1132023984.137926.54610@.g43g2000cwa.googlegro ups.com...
> Thanks Ben.
> The 'Rebuild Master' utility wants the source directory containing data
> files. How do I extract the mdf and ldf files from my sql backup? ie. a
> native sql backup which is a single file.
> David
>
|||OK. That makes sense. Now I get the below error is cnfgsvr.out
################################################## #############################
Starting Service ...
SQL_Latin1_General_CP1_CI_AS
-m -Q -T4022 -T3659
An error occurred while attempting to start the service (1460)
SQL Server configuration failed.
################################################## #############################
|||Worked it out. Files copied from cd were read-only. I copied the files
locally, reset read-only bit and rebuilt from local files. Thanks for
all your help!
Cheers,
David
|||Hi,
You can give a try to Active@. undelete or Uneraser (for DOS) tool. IT
worked great for me and never failed before, so I suppose it might
really help.
http://www.active-undelete.com/
http://www.uneraser.com/
|||Hello,
EASEUS DataRecoveryWizard utility can help. Speaking about me, it was
easily able to restore deleted, lost file and unformat drive,
so I think you will also find it quite useful. Really recommended
tool, give it a try.
http://www.easeus.com/
Good work, Good day.
"davidawest@.gmail.com" wrote:
> I have an instance where all of the data files (mdf and ldf) for every
> database including master and msdb have been deleted. I need to restore
> all databases from (native sql) backup, however I'm not sure how to
> connect to the instance to perform the database restore.
> The only way I can think of achieving this is to un-install the
> instance, re-install the instance with the same name and then restore
> my existing databases including master, msdb over the new ones. It's a
> dev/test environment so not critical, but I'd appreciate any comments
> on this strategy..
> Regards,
> David West
>
Wednesday, March 7, 2012
Deleted all data files of an instance
I have an instance where all of the data files (mdf and ldf) for every
database including master and msdb have been deleted. I need to restore
all databases from (native sql) backup, however I'm not sure how to
connect to the instance to perform the database restore.
The only way I can think of achieving this is to un-install the
instance, re-install the instance with the same name and then restore
my existing databases including master, msdb over the new ones. It's a
dev/test environment so not critical, but I'd appreciate any comments
on this strategy..
Regards,
David WestRead BOL for restore system databases.It will be good help to u.
from
Doller|||If somebody else deleted the .mdf and .ldf files by mistake first look for
them on the Recycle Bin :-)
If the master database is not available, SQL Server will not start. Read
'Rebuild Master utility' from BOL. Run rebuildm and follow the instructions.
After this you will have your system databases restored.
Start SQL Server in single user mode using sqlservr -m to restore the master
database from your backup.
Then you can restore the other databases.
Ben Nevarez
<davidawest@.gmail.com> wrote in message
news:1131941368.575648.226390@.g47g2000cwa.googlegroups.com...
>I have an instance where all of the data files (mdf and ldf) for every
> database including master and msdb have been deleted. I need to restore
> all databases from (native sql) backup, however I'm not sure how to
> connect to the instance to perform the database restore.
> The only way I can think of achieving this is to un-install the
> instance, re-install the instance with the same name and then restore
> my existing databases including master, msdb over the new ones. It's a
> dev/test environment so not critical, but I'd appreciate any comments
> on this strategy..
> Regards,
> David West
>|||Thanks Ben.
The 'Rebuild Master' utility wants the source directory containing data
files. How do I extract the mdf and ldf files from my sql backup? ie. a
native sql backup which is a single file.
David|||rebuildm asks for the SQL Server CD. On the CD browse to the data directory.
You will restore your master database backup (*.bak) later when starting SQL
Server in single user mode using sqlservr -m.
Ben Nevarez
<davidawest@.gmail.com> wrote in message
news:1132023984.137926.54610@.g43g2000cwa.googlegroups.com...
> Thanks Ben.
> The 'Rebuild Master' utility wants the source directory containing data
> files. How do I extract the mdf and ldf files from my sql backup? ie. a
> native sql backup which is a single file.
> David
>|||OK. That makes sense. Now I get the below error is cnfgsvr.out
########################################
####################################
###
Starting Service ...
SQL_Latin1_General_CP1_CI_AS
-m -Q -T4022 -T3659
An error occurred while attempting to start the service (1460)
SQL Server configuration failed.
########################################
####################################
###|||Worked it out. Files copied from cd were read-only. I copied the files
locally, reset read-only bit and rebuilt from local files. Thanks for
all your help!
Cheers,
David|||Hi,
You can give a try to Active@. undelete or Uneraser (for DOS) tool. IT
worked great for me and never failed before, so I suppose it might
really help.
http://www.active-undelete.com/
http://www.uneraser.com/|||Hello,
EASEUS DataRecoveryWizard utility can help. Speaking about me, it was
easily able to restore deleted, lost file and unformat drive,
so I think you will also find it quite useful. Really recommended
tool, give it a try.
http://www.easeus.com/
--
Good work, Good day.
"davidawest@.gmail.com" wrote:
> I have an instance where all of the data files (mdf and ldf) for every
> database including master and msdb have been deleted. I need to restore
> all databases from (native sql) backup, however I'm not sure how to
> connect to the instance to perform the database restore.
> The only way I can think of achieving this is to un-install the
> instance, re-install the instance with the same name and then restore
> my existing databases including master, msdb over the new ones. It's a
> dev/test environment so not critical, but I'd appreciate any comments
> on this strategy..
> Regards,
> David West
>
database including master and msdb have been deleted. I need to restore
all databases from (native sql) backup, however I'm not sure how to
connect to the instance to perform the database restore.
The only way I can think of achieving this is to un-install the
instance, re-install the instance with the same name and then restore
my existing databases including master, msdb over the new ones. It's a
dev/test environment so not critical, but I'd appreciate any comments
on this strategy..
Regards,
David WestRead BOL for restore system databases.It will be good help to u.
from
Doller|||If somebody else deleted the .mdf and .ldf files by mistake first look for
them on the Recycle Bin :-)
If the master database is not available, SQL Server will not start. Read
'Rebuild Master utility' from BOL. Run rebuildm and follow the instructions.
After this you will have your system databases restored.
Start SQL Server in single user mode using sqlservr -m to restore the master
database from your backup.
Then you can restore the other databases.
Ben Nevarez
<davidawest@.gmail.com> wrote in message
news:1131941368.575648.226390@.g47g2000cwa.googlegroups.com...
>I have an instance where all of the data files (mdf and ldf) for every
> database including master and msdb have been deleted. I need to restore
> all databases from (native sql) backup, however I'm not sure how to
> connect to the instance to perform the database restore.
> The only way I can think of achieving this is to un-install the
> instance, re-install the instance with the same name and then restore
> my existing databases including master, msdb over the new ones. It's a
> dev/test environment so not critical, but I'd appreciate any comments
> on this strategy..
> Regards,
> David West
>|||Thanks Ben.
The 'Rebuild Master' utility wants the source directory containing data
files. How do I extract the mdf and ldf files from my sql backup? ie. a
native sql backup which is a single file.
David|||rebuildm asks for the SQL Server CD. On the CD browse to the data directory.
You will restore your master database backup (*.bak) later when starting SQL
Server in single user mode using sqlservr -m.
Ben Nevarez
<davidawest@.gmail.com> wrote in message
news:1132023984.137926.54610@.g43g2000cwa.googlegroups.com...
> Thanks Ben.
> The 'Rebuild Master' utility wants the source directory containing data
> files. How do I extract the mdf and ldf files from my sql backup? ie. a
> native sql backup which is a single file.
> David
>|||OK. That makes sense. Now I get the below error is cnfgsvr.out
########################################
####################################
###
Starting Service ...
SQL_Latin1_General_CP1_CI_AS
-m -Q -T4022 -T3659
An error occurred while attempting to start the service (1460)
SQL Server configuration failed.
########################################
####################################
###|||Worked it out. Files copied from cd were read-only. I copied the files
locally, reset read-only bit and rebuilt from local files. Thanks for
all your help!
Cheers,
David|||Hi,
You can give a try to Active@. undelete or Uneraser (for DOS) tool. IT
worked great for me and never failed before, so I suppose it might
really help.
http://www.active-undelete.com/
http://www.uneraser.com/|||Hello,
EASEUS DataRecoveryWizard utility can help. Speaking about me, it was
easily able to restore deleted, lost file and unformat drive,
so I think you will also find it quite useful. Really recommended
tool, give it a try.
http://www.easeus.com/
--
Good work, Good day.
"davidawest@.gmail.com" wrote:
> I have an instance where all of the data files (mdf and ldf) for every
> database including master and msdb have been deleted. I need to restore
> all databases from (native sql) backup, however I'm not sure how to
> connect to the instance to perform the database restore.
> The only way I can think of achieving this is to un-install the
> instance, re-install the instance with the same name and then restore
> my existing databases including master, msdb over the new ones. It's a
> dev/test environment so not critical, but I'd appreciate any comments
> on this strategy..
> Regards,
> David West
>
Deleted all data files of an instance
I have an instance where all of the data files (mdf and ldf) for every
database including master and msdb have been deleted. I need to restore
all databases from (native sql) backup, however I'm not sure how to
connect to the instance to perform the database restore.
The only way I can think of achieving this is to un-install the
instance, re-install the instance with the same name and then restore
my existing databases including master, msdb over the new ones. It's a
dev/test environment so not critical, but I'd appreciate any comments
on this strategy..
Regards,
David WestRead BOL for restore system databases.It will be good help to u.
from
Doller|||If somebody else deleted the .mdf and .ldf files by mistake first look for
them on the Recycle Bin :-)
If the master database is not available, SQL Server will not start. Read
'Rebuild Master utility' from BOL. Run rebuildm and follow the instructions.
After this you will have your system databases restored.
Start SQL Server in single user mode using sqlservr -m to restore the master
database from your backup.
Then you can restore the other databases.
Ben Nevarez
<davidawest@.gmail.com> wrote in message
news:1131941368.575648.226390@.g47g2000cwa.googlegroups.com...
>I have an instance where all of the data files (mdf and ldf) for every
> database including master and msdb have been deleted. I need to restore
> all databases from (native sql) backup, however I'm not sure how to
> connect to the instance to perform the database restore.
> The only way I can think of achieving this is to un-install the
> instance, re-install the instance with the same name and then restore
> my existing databases including master, msdb over the new ones. It's a
> dev/test environment so not critical, but I'd appreciate any comments
> on this strategy..
> Regards,
> David West
>|||Thanks Ben.
The 'Rebuild Master' utility wants the source directory containing data
files. How do I extract the mdf and ldf files from my sql backup? ie. a
native sql backup which is a single file.
David|||rebuildm asks for the SQL Server CD. On the CD browse to the data directory.
You will restore your master database backup (*.bak) later when starting SQL
Server in single user mode using sqlservr -m.
Ben Nevarez
<davidawest@.gmail.com> wrote in message
news:1132023984.137926.54610@.g43g2000cwa.googlegroups.com...
> Thanks Ben.
> The 'Rebuild Master' utility wants the source directory containing data
> files. How do I extract the mdf and ldf files from my sql backup? ie. a
> native sql backup which is a single file.
> David
>|||OK. That makes sense. Now I get the below error is cnfgsvr.out
###############################################################################
Starting Service ...
SQL_Latin1_General_CP1_CI_AS
-m -Q -T4022 -T3659
An error occurred while attempting to start the service (1460)
SQL Server configuration failed.
###############################################################################|||Worked it out. Files copied from cd were read-only. I copied the files
locally, reset read-only bit and rebuilt from local files. Thanks for
all your help!
Cheers,
David|||Hi,
You can give a try to Active@. undelete or Uneraser (for DOS) tool. IT
worked great for me and never failed before, so I suppose it might
really help.
http://www.active-undelete.com/
http://www.uneraser.com/|||Hello,
EASEUS DataRecoveryWizard utility can help. Speaking about me, it was
easily able to restore deleted, lost file and unformat drive,
so I think you will also find it quite useful. Really recommended
tool, give it a try.
http://www.easeus.com/
--
Good work, Good day.
"davidawest@.gmail.com" wrote:
> I have an instance where all of the data files (mdf and ldf) for every
> database including master and msdb have been deleted. I need to restore
> all databases from (native sql) backup, however I'm not sure how to
> connect to the instance to perform the database restore.
> The only way I can think of achieving this is to un-install the
> instance, re-install the instance with the same name and then restore
> my existing databases including master, msdb over the new ones. It's a
> dev/test environment so not critical, but I'd appreciate any comments
> on this strategy..
> Regards,
> David West
>
database including master and msdb have been deleted. I need to restore
all databases from (native sql) backup, however I'm not sure how to
connect to the instance to perform the database restore.
The only way I can think of achieving this is to un-install the
instance, re-install the instance with the same name and then restore
my existing databases including master, msdb over the new ones. It's a
dev/test environment so not critical, but I'd appreciate any comments
on this strategy..
Regards,
David WestRead BOL for restore system databases.It will be good help to u.
from
Doller|||If somebody else deleted the .mdf and .ldf files by mistake first look for
them on the Recycle Bin :-)
If the master database is not available, SQL Server will not start. Read
'Rebuild Master utility' from BOL. Run rebuildm and follow the instructions.
After this you will have your system databases restored.
Start SQL Server in single user mode using sqlservr -m to restore the master
database from your backup.
Then you can restore the other databases.
Ben Nevarez
<davidawest@.gmail.com> wrote in message
news:1131941368.575648.226390@.g47g2000cwa.googlegroups.com...
>I have an instance where all of the data files (mdf and ldf) for every
> database including master and msdb have been deleted. I need to restore
> all databases from (native sql) backup, however I'm not sure how to
> connect to the instance to perform the database restore.
> The only way I can think of achieving this is to un-install the
> instance, re-install the instance with the same name and then restore
> my existing databases including master, msdb over the new ones. It's a
> dev/test environment so not critical, but I'd appreciate any comments
> on this strategy..
> Regards,
> David West
>|||Thanks Ben.
The 'Rebuild Master' utility wants the source directory containing data
files. How do I extract the mdf and ldf files from my sql backup? ie. a
native sql backup which is a single file.
David|||rebuildm asks for the SQL Server CD. On the CD browse to the data directory.
You will restore your master database backup (*.bak) later when starting SQL
Server in single user mode using sqlservr -m.
Ben Nevarez
<davidawest@.gmail.com> wrote in message
news:1132023984.137926.54610@.g43g2000cwa.googlegroups.com...
> Thanks Ben.
> The 'Rebuild Master' utility wants the source directory containing data
> files. How do I extract the mdf and ldf files from my sql backup? ie. a
> native sql backup which is a single file.
> David
>|||OK. That makes sense. Now I get the below error is cnfgsvr.out
###############################################################################
Starting Service ...
SQL_Latin1_General_CP1_CI_AS
-m -Q -T4022 -T3659
An error occurred while attempting to start the service (1460)
SQL Server configuration failed.
###############################################################################|||Worked it out. Files copied from cd were read-only. I copied the files
locally, reset read-only bit and rebuilt from local files. Thanks for
all your help!
Cheers,
David|||Hi,
You can give a try to Active@. undelete or Uneraser (for DOS) tool. IT
worked great for me and never failed before, so I suppose it might
really help.
http://www.active-undelete.com/
http://www.uneraser.com/|||Hello,
EASEUS DataRecoveryWizard utility can help. Speaking about me, it was
easily able to restore deleted, lost file and unformat drive,
so I think you will also find it quite useful. Really recommended
tool, give it a try.
http://www.easeus.com/
--
Good work, Good day.
"davidawest@.gmail.com" wrote:
> I have an instance where all of the data files (mdf and ldf) for every
> database including master and msdb have been deleted. I need to restore
> all databases from (native sql) backup, however I'm not sure how to
> connect to the instance to perform the database restore.
> The only way I can think of achieving this is to un-install the
> instance, re-install the instance with the same name and then restore
> my existing databases including master, msdb over the new ones. It's a
> dev/test environment so not critical, but I'd appreciate any comments
> on this strategy..
> Regards,
> David West
>
Friday, February 17, 2012
Delete query taking long time to execute
hi,
I have a table which is a Master to other 12 tables and it is Child for
other 2 tables.
Therefore, It has 14 relationships with other tables. The size of table is
very small. (approx 30-40 rows). It also has a clustered index in its primary
key.
The problem I am facing here is that when I try to delete a row from the
table using its primary key in where clause, it takes more than 30 seconds to
execute it. Any subsequent attempt will be of a shorter duration (approx
20-25 sec.) but still it is very time taking.
I can't understand what could be the problem here when the table size is
very small. Is it because of constraints or any other sort.
Please comment if you have any idea about it. Any help would be highly
appreciated.
Thanks!
Regards,
~Somesh
Hi,
This is because of the multiple relationship. While deleting it checks all
the relationship. Where as if you execute a SELECT statement for the same
query
it will be faster. If you can do this during offline you could disable the
constraints [See ALTER TABLE WITH NOCHECK] to make the DELETE faster.
Since the number of records is very less it is not required to disable the
constratins
Thanks
Hari
"Somesh" <Somesh@.discussions.microsoft.com> wrote in message
news:FD6E5B4A-34AF-4CF9-948D-6EA21944587C@.microsoft.com...
> hi,
> I have a table which is a Master to other 12 tables and it is Child for
> other 2 tables.
> Therefore, It has 14 relationships with other tables. The size of table is
> very small. (approx 30-40 rows). It also has a clustered index in its
> primary
> key.
> The problem I am facing here is that when I try to delete a row from the
> table using its primary key in where clause, it takes more than 30 seconds
> to
> execute it. Any subsequent attempt will be of a shorter duration (approx
> 20-25 sec.) but still it is very time taking.
> I can't understand what could be the problem here when the table size is
> very small. Is it because of constraints or any other sort.
> Please comment if you have any idea about it. Any help would be highly
> appreciated.
> Thanks!
> Regards,
> ~Somesh
>
|||Thank you Hugo for the information.
FYI, I have asked him to do the NOCHECK when the system is offline (No users
are connected) and after the delete activity he can
make ALTER statement WITH CHECK. For one of my manual delete ( 5 million
record deletion) I did this and identified a huge
performance difference.
Thanks
Hari
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:55ccm2dgsfvcdco0iogo0oge8j9bvs953c@.4ax.com...
> On Thu, 23 Nov 2006 02:08:26 -0600, Hari Prasad wrote:
>
> Hi Hari,
> There are of course two downsides to disabling the constraints:
> 1. If there is some error in the manual checking process, or some
> concurrent opdate after the manual checking, the database may be left in
> an unconsistent state.
> 2. Even if all checks were made aith 100% accuracy, SQL Server doesn't
> know this, so the constraints are marked as "not trusted" once they are
> re-enabled. That means that the optimizer can no longer rely on those
> constraints for considering shortcuts for the query plan, which may
> result in longer running queries. (Unless you use the WITH CHECK option
> when the constraints are re-enabled, but in that case you still get the
> same performance hit, only at a later time).
> --
> Hugo Kornelis, SQL Server MVP
I have a table which is a Master to other 12 tables and it is Child for
other 2 tables.
Therefore, It has 14 relationships with other tables. The size of table is
very small. (approx 30-40 rows). It also has a clustered index in its primary
key.
The problem I am facing here is that when I try to delete a row from the
table using its primary key in where clause, it takes more than 30 seconds to
execute it. Any subsequent attempt will be of a shorter duration (approx
20-25 sec.) but still it is very time taking.
I can't understand what could be the problem here when the table size is
very small. Is it because of constraints or any other sort.
Please comment if you have any idea about it. Any help would be highly
appreciated.
Thanks!
Regards,
~Somesh
Hi,
This is because of the multiple relationship. While deleting it checks all
the relationship. Where as if you execute a SELECT statement for the same
query
it will be faster. If you can do this during offline you could disable the
constraints [See ALTER TABLE WITH NOCHECK] to make the DELETE faster.
Since the number of records is very less it is not required to disable the
constratins
Thanks
Hari
"Somesh" <Somesh@.discussions.microsoft.com> wrote in message
news:FD6E5B4A-34AF-4CF9-948D-6EA21944587C@.microsoft.com...
> hi,
> I have a table which is a Master to other 12 tables and it is Child for
> other 2 tables.
> Therefore, It has 14 relationships with other tables. The size of table is
> very small. (approx 30-40 rows). It also has a clustered index in its
> primary
> key.
> The problem I am facing here is that when I try to delete a row from the
> table using its primary key in where clause, it takes more than 30 seconds
> to
> execute it. Any subsequent attempt will be of a shorter duration (approx
> 20-25 sec.) but still it is very time taking.
> I can't understand what could be the problem here when the table size is
> very small. Is it because of constraints or any other sort.
> Please comment if you have any idea about it. Any help would be highly
> appreciated.
> Thanks!
> Regards,
> ~Somesh
>
|||Thank you Hugo for the information.
FYI, I have asked him to do the NOCHECK when the system is offline (No users
are connected) and after the delete activity he can
make ALTER statement WITH CHECK. For one of my manual delete ( 5 million
record deletion) I did this and identified a huge
performance difference.
Thanks
Hari
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:55ccm2dgsfvcdco0iogo0oge8j9bvs953c@.4ax.com...
> On Thu, 23 Nov 2006 02:08:26 -0600, Hari Prasad wrote:
>
> Hi Hari,
> There are of course two downsides to disabling the constraints:
> 1. If there is some error in the manual checking process, or some
> concurrent opdate after the manual checking, the database may be left in
> an unconsistent state.
> 2. Even if all checks were made aith 100% accuracy, SQL Server doesn't
> know this, so the constraints are marked as "not trusted" once they are
> re-enabled. That means that the optimizer can no longer rely on those
> constraints for considering shortcuts for the query plan, which may
> result in longer running queries. (Unless you use the WITH CHECK option
> when the constraints are re-enabled, but in that case you still get the
> same performance hit, only at a later time).
> --
> Hugo Kornelis, SQL Server MVP
Subscribe to:
Posts (Atom)