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...
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

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...

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...

Deleteing SQL Aent Jobs in SQL2005

Hi,
I have a situation where I had created a maintenance plan and edited the
resulting job. I had to delete the maintenance plan and job. In SQL2000 I
would delete the job first then the maintenance plan. I tried this is
SQL2005 and had errors. It referred to the DELETE statement conflicted with
REFERENCE constraint "FK_subplan_job_id". The conflict occurred in msdb
table dbo.sysmaintplan_subplans", column 'job_id'.
I could not delete the maintenance plan either. In the end I renamed the job
then I could delete the maintenance plan.
This is on SQL2005 SP1.
Thanks
ChrisI have same problem. Did you ever get a reply? If so, what do I do? I hav
e
several orphan jobs I cannot delete. Thanks for any info.|||DaveK,
I mentioned in my post how I fixed this. Nobody else replied.
Chris
"DaveK" <DaveK@.discussions.microsoft.com> wrote in message
news:79B9F73B-C3E3-47AB-8CCB-3AEA8EC02062@.microsoft.com...
>I have same problem. Did you ever get a reply? If so, what do I do? I
>have
> several orphan jobs I cannot delete. Thanks for any info.

Deleteing SQL Aent Jobs in SQL2005

Hi,
I have a situation where I had created a maintenance plan and edited the
resulting job. I had to delete the maintenance plan and job. In SQL2000 I
would delete the job first then the maintenance plan. I tried this is
SQL2005 and had errors. It referred to the DELETE statement conflicted with
REFERENCE constraint "FK_subplan_job_id". The conflict occurred in msdb
table dbo.sysmaintplan_subplans", column 'job_id'.
I could not delete the maintenance plan either. In the end I renamed the job
then I could delete the maintenance plan.
This is on SQL2005 SP1.
Thanks
Chris
I have same problem. Did you ever get a reply? If so, what do I do? I have
several orphan jobs I cannot delete. Thanks for any info.
|||DaveK,
I mentioned in my post how I fixed this. Nobody else replied.
Chris
"DaveK" <DaveK@.discussions.microsoft.com> wrote in message
news:79B9F73B-C3E3-47AB-8CCB-3AEA8EC02062@.microsoft.com...
>I have same problem. Did you ever get a reply? If so, what do I do? I
>have
> several orphan jobs I cannot delete. Thanks for any info.

Deleteing SQL Aent Jobs in SQL2005

Hi,
I have a situation where I had created a maintenance plan and edited the
resulting job. I had to delete the maintenance plan and job. In SQL2000 I
would delete the job first then the maintenance plan. I tried this is
SQL2005 and had errors. It referred to the DELETE statement conflicted with
REFERENCE constraint "FK_subplan_job_id". The conflict occurred in msdb
table dbo.sysmaintplan_subplans", column 'job_id'.
I could not delete the maintenance plan either. In the end I renamed the job
then I could delete the maintenance plan.
This is on SQL2005 SP1.
Thanks
Chris
I have same problem. Did you ever get a reply? If so, what do I do? I have
several orphan jobs I cannot delete. Thanks for any info.
|||DaveK,
I mentioned in my post how I fixed this. Nobody else replied.
Chris
"DaveK" <DaveK@.discussions.microsoft.com> wrote in message
news:79B9F73B-C3E3-47AB-8CCB-3AEA8EC02062@.microsoft.com...
>I have same problem. Did you ever get a reply? If so, what do I do? I
>have
> several orphan jobs I cannot delete. Thanks for any info.

Deleteing SQL Aent Jobs in SQL2005

Hi,
I have a situation where I had created a maintenance plan and edited the
resulting job. I had to delete the maintenance plan and job. In SQL2000 I
would delete the job first then the maintenance plan. I tried this is
SQL2005 and had errors. It referred to the DELETE statement conflicted with
REFERENCE constraint "FK_subplan_job_id". The conflict occurred in msdb
table dbo.sysmaintplan_subplans", column 'job_id'.
I could not delete the maintenance plan either. In the end I renamed the job
then I could delete the maintenance plan.
This is on SQL2005 SP1.
Thanks
ChrisI have same problem. Did you ever get a reply? If so, what do I do? I have
several orphan jobs I cannot delete. Thanks for any info.|||DaveK,
I mentioned in my post how I fixed this. Nobody else replied.
Chris
"DaveK" <DaveK@.discussions.microsoft.com> wrote in message
news:79B9F73B-C3E3-47AB-8CCB-3AEA8EC02062@.microsoft.com...
>I have same problem. Did you ever get a reply? If so, what do I do? I
>have
> several orphan jobs I cannot delete. Thanks for any info.

Deleted Records space

Am new to SQL2000, but have worked with other RDBMS's.
I am wondering what the procedure is to recover the space taken up in a
database by records marked for deletion?
Is this something automatically done as part of the shrink database procedure?
Kind Regards,
Naj
Hi
As soon as a row is deleted, it's space can be occupied by other data. It is
best to re-index the clustered keys as this will re-organize the DB to it's
original fill factor again. Shrink DB will not help you in this case.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Naj Parandah" <Naj Parandah@.discussions.microsoft.com> wrote in message
news:B09A1600-7354-4D70-B5EB-3B41B4E9F1BD@.microsoft.com...
> Am new to SQL2000, but have worked with other RDBMS's.
> I am wondering what the procedure is to recover the space taken up in a
> database by records marked for deletion?
> Is this something automatically done as part of the shrink database
> procedure?
> Kind Regards,
> Naj
|||Thanks very much for the information Mike!
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> As soon as a row is deleted, it's space can be occupied by other data. It is
> best to re-index the clustered keys as this will re-organize the DB to it's
> original fill factor again. Shrink DB will not help you in this case.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Naj Parandah" <Naj Parandah@.discussions.microsoft.com> wrote in message
> news:B09A1600-7354-4D70-B5EB-3B41B4E9F1BD@.microsoft.com...
>
>

Deleted Records space

Am new to SQL2000, but have worked with other RDBMS's.
I am wondering what the procedure is to recover the space taken up in a
database by records marked for deletion?
Is this something automatically done as part of the shrink database procedure?
Kind Regards,
NajHi
As soon as a row is deleted, it's space can be occupied by other data. It is
best to re-index the clustered keys as this will re-organize the DB to it's
original fill factor again. Shrink DB will not help you in this case.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Naj Parandah" <Naj Parandah@.discussions.microsoft.com> wrote in message
news:B09A1600-7354-4D70-B5EB-3B41B4E9F1BD@.microsoft.com...
> Am new to SQL2000, but have worked with other RDBMS's.
> I am wondering what the procedure is to recover the space taken up in a
> database by records marked for deletion?
> Is this something automatically done as part of the shrink database
> procedure?
> Kind Regards,
> Naj|||Thanks very much for the information Mike!
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> As soon as a row is deleted, it's space can be occupied by other data. It is
> best to re-index the clustered keys as this will re-organize the DB to it's
> original fill factor again. Shrink DB will not help you in this case.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Naj Parandah" <Naj Parandah@.discussions.microsoft.com> wrote in message
> news:B09A1600-7354-4D70-B5EB-3B41B4E9F1BD@.microsoft.com...
> > Am new to SQL2000, but have worked with other RDBMS's.
> > I am wondering what the procedure is to recover the space taken up in a
> > database by records marked for deletion?
> >
> > Is this something automatically done as part of the shrink database
> > procedure?
> >
> > Kind Regards,
> > Naj
>
>

Deleted Records space

Am new to SQL2000, but have worked with other RDBMS's.
I am wondering what the procedure is to recover the space taken up in a
database by records marked for deletion?
Is this something automatically done as part of the shrink database procedur
e?
Kind Regards,
NajHi
As soon as a row is deleted, it's space can be occupied by other data. It is
best to re-index the clustered keys as this will re-organize the DB to it's
original fill factor again. Shrink DB will not help you in this case.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Naj Parandah" <Naj Parandah@.discussions.microsoft.com> wrote in message
news:B09A1600-7354-4D70-B5EB-3B41B4E9F1BD@.microsoft.com...
> Am new to SQL2000, but have worked with other RDBMS's.
> I am wondering what the procedure is to recover the space taken up in a
> database by records marked for deletion?
> Is this something automatically done as part of the shrink database
> procedure?
> Kind Regards,
> Naj|||Thanks very much for the information Mike!
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> As soon as a row is deleted, it's space can be occupied by other data. It
is
> best to re-index the clustered keys as this will re-organize the DB to it'
s
> original fill factor again. Shrink DB will not help you in this case.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Naj Parandah" <Naj Parandah@.discussions.microsoft.com> wrote in message
> news:B09A1600-7354-4D70-B5EB-3B41B4E9F1BD@.microsoft.com...
>
>

Delete using another tables values

Hi all,
I am trying to perform a delete that I could achieve in Access but
need to do this in sql2000.
I have two tables Warranty and Registrations. I would like to delete
all items in the warranty table where there is a match in
registrations on a common field.
I access the query would be:
DELETE warranty.*
FROM warranty INNER JOIN registrations ON warranty.BBMQCE =
registrations.vins;

But cannot replicate this in SQL server?

Any help would be much appreciated.

Thanks
SamOn 23 Sep 2004 02:40:24 -0700, SG wrote:

>I access the query would be:
>DELETE warranty.*
>FROM warranty INNER JOIN registrations ON warranty.BBMQCE =
>registrations.vins;
>But cannot replicate this in SQL server?

Hi Sam,

You're almost there. The Transact-SQL version of this would be

DELETE warranty
FROM warranty
INNER JOIN registration
ON warranty.BBMQCE = registrations.vins

Yes - you only need to drop the .* !!!

However, the above is proprietary code that will not port well to other
databases. If you want portability, use the ANSI-standard delete syntax
instead:

DELETE FROM warranty
WHERE NOT EXISTS (SELECT *
FROM registration
WHERE warranty.BBMQCE = registrations.vins)

(both queries untested - beware of spelling errors!)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Many thanks - i was so nearly there!
Sam

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Hugo Kornelis wrote:

> On 23 Sep 2004 02:40:24 -0700, SG wrote:
>
>>I access the query would be:
>>DELETE warranty.*
>>FROM warranty INNER JOIN registrations ON warranty.BBMQCE =
>>registrations.vins;
>>
>>But cannot replicate this in SQL server?
>
> Hi Sam,
> You're almost there. The Transact-SQL version of this would be
> DELETE warranty
> FROM warranty
> INNER JOIN registration
> ON warranty.BBMQCE = registrations.vins
> Yes - you only need to drop the .* !!!
>
> However, the above is proprietary code that will not port well to other
> databases. If you want portability, use the ANSI-standard delete syntax
> instead:
> DELETE FROM warranty
> WHERE NOT EXISTS (SELECT *
> FROM registration
> WHERE warranty.BBMQCE = registrations.vins)
> (both queries untested - beware of spelling errors!)
> Best, Hugo

DELETE FROM warranty
WHERE EXISTS (SELECT *
FROM registration
WHERE warranty.BBMQCE = registrations.vins)

There shouldn't be *NOT* in WHERE clause, because SG wants to delete all matches|||On Sat, 25 Sep 2004 06:01:13 GMT, Andrey wrote:

>There shouldn't be *NOT* in WHERE clause, because SG wants to delete all matches

Hi Andrey,

Good catch! Thanks for correcting my mistake.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo Kornelis wrote:
> On Sat, 25 Sep 2004 06:01:13 GMT, Andrey wrote:
>
>>There shouldn't be *NOT* in WHERE clause, because SG wants to delete all matches
>
> Hi Andrey,
> Good catch! Thanks for correcting my mistake.
> Best, Hugo

You're welcome :)