Deleting Large Quantiy of Rows

Hi Guys,
I need a little advice. I need to delete 250000 rows on a monthly basis and
my users are using MSDE 2000 with merge replication, publisher is SQL Server
2000 Standard.
The users will never be uploading or making changes to this table. Is there
any way i can make this a quick delete as currently for each row that is in
the Article published table sql is sending down a delete statement for each
row to the subscriber. Thus taking longer than expected.
Any ideas or advice would be greatly apprecitaed.
Thanks, Tim.
From what you are saying it looks like these rows on the subscriber are read
only and the data flow is only from the publisher to the subscriber for this
table. If so remove this table from the merge publication, put it in a
transactional publication and do your deletes through a stored procedure and
replicate the execution of the stored procedure.
Hilary Cotter
Looking for a SQL Server replication book
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Tim Ford" <tim.ford@.nospamrubbishin2focus.com> wrote in message
news:%23kd7psfrFHA.3604@.tk2msftngp13.phx.gbl...
> Hi Guys,
> I need a little advice. I need to delete 250000 rows on a monthly basis
and
> my users are using MSDE 2000 with merge replication, publisher is SQL
Server
> 2000 Standard.
> The users will never be uploading or making changes to this table. Is
there
> any way i can make this a quick delete as currently for each row that is
in
> the Article published table sql is sending down a delete statement for
each
> row to the subscriber. Thus taking longer than expected.
> Any ideas or advice would be greatly apprecitaed.
> Thanks, Tim.
>
|||Hi Hilary,
Thanks for your response, this is not going to be possible as we use the
ActiveX control and the publications have been hard coded. Which i know is
not good. Is there any other way that we could perhaps specify the order
that things get run in I.E sp_AddScriptexec. I have worked a way out with
the system tables that we could remove generations. However i would need to
make sure that sp_addscriptexec was fired first in the replication process.
Thanks, Tim.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23boP2ShrFHA.1788@.tk2msftngp13.phx.gbl...
> From what you are saying it looks like these rows on the subscriber are
> read
> only and the data flow is only from the publisher to the subscriber for
> this
> table. If so remove this table from the merge publication, put it in a
> transactional publication and do your deletes through a stored procedure
> and
> replicate the execution of the stored procedure.
> --
> Hilary Cotter
> Looking for a SQL Server replication book
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Tim Ford" <tim.ford@.nospamrubbishin2focus.com> wrote in message
> news:%23kd7psfrFHA.3604@.tk2msftngp13.phx.gbl...
> and
> Server
> there
> in
> each
>
|||I think your best option would be to drop the subscription (possibly drop
the publication as well to drop the triggers which will slow the delete), do
the delete, and then recreate the publication and subscription and do a no
sync.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Tim Ford" <tim.ford@.nospamrubbishin2focus.com> wrote in message
news:eSsLlVhrFHA.3720@.TK2MSFTNGP14.phx.gbl...
> Hi Hilary,
> Thanks for your response, this is not going to be possible as we use the
> ActiveX control and the publications have been hard coded. Which i know is
> not good. Is there any other way that we could perhaps specify the order
> that things get run in I.E sp_AddScriptexec. I have worked a way out with
> the system tables that we could remove generations. However i would need
to
> make sure that sp_addscriptexec was fired first in the replication
process.[vbcol=seagreen]
> Thanks, Tim.
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23boP2ShrFHA.1788@.tk2msftngp13.phx.gbl...
is
>
|||Hi Hilary,
Thanks for that. What would happen to the information that was in the table
on the subscriber? Would that get deleted locally, as this information
would no longer be needed.
Again Thanks for all you help.
Tim.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23HJ2hOjrFHA.2592@.TK2MSFTNGP09.phx.gbl...
>I think your best option would be to drop the subscription (possibly drop
> the publication as well to drop the triggers which will slow the delete),
> do
> the delete, and then recreate the publication and subscription and do a no
> sync.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Tim Ford" <tim.ford@.nospamrubbishin2focus.com> wrote in message
> news:eSsLlVhrFHA.3720@.TK2MSFTNGP14.phx.gbl...
> to
> process.
> is
>
|||It would remain there. This is probably not what you want, so you would have
to delete on both sides.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Tim Ford" <tim.ford@.nospamrubbishin2focus.com> wrote in message
news:OBtaphsrFHA.240@.tk2msftngp13.phx.gbl...
> Hi Hilary,
> Thanks for that. What would happen to the information that was in the
table[vbcol=seagreen]
> on the subscriber? Would that get deleted locally, as this information
> would no longer be needed.
> Again Thanks for all you help.
> Tim.
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23HJ2hOjrFHA.2592@.TK2MSFTNGP09.phx.gbl...
delete),[vbcol=seagreen]
no[vbcol=seagreen]
the[vbcol=seagreen]
order[vbcol=seagreen]
with[vbcol=seagreen]
need[vbcol=seagreen]
are[vbcol=seagreen]
for[vbcol=seagreen]
a[vbcol=seagreen]
SQL[vbcol=seagreen]
Is[vbcol=seagreen]
that[vbcol=seagreen]
for
>

Deleting all but top record.

Hey Guys,

I have Performance Monitor running and storing the network usage to my MsSQL database, and this is done a few times a minute. I have a page that then shows show much of my bandwidth is being used. As you can gather, the database quickly starts filling up with hundrreds of records so I could do with a script that delete these records.
I cant simply delete all records because that would cause my webpage to fail so I need a way to delete all records apart from the latest one.
Wondering if anyone would know how I could do this?delete from tableRecords where recordid < (select max(recordid) from tablerecords)

Nick|||

Thank you, that works great.
I've just however noticed a potential problem. I understand that the RecordID can only go so high then will give errors. The thing is, the PerfMon has only been running a few hours and already it has produced 1800+ records. With your script I can just keep it as one record in the table, but the RecordID will get higher and higher. Im wondering what will happening if I let this run for a week or so, eventually it will stop working.
Anyone got any ideas?

|||Dont use an auto-identity field.
insert into table
select max(recordid) + 1 from table, [rest of fields]
Since you will only have one record in there at a time, this shouldnt hurt your performance.
Nick

Deleting a record older then 10 minutes

Hey Guys,
I have been trying to work out how I would delete a record that was created more then 10 minutes ago.
I can use this to delete records older then a day.
DELETE FROM DownloadQueue WHERE Downloading = '0' AND QueuePos = '0' AND DateTime < GETDATE() - 1
Just need something now that will do it for just 10 minutes.
Cheers.You could use this:
DELETE FROM DownloadQueue
WHERE Downloading = '0' AND QueuePos = '0' AND DateTime < DATEADD(mi,-10,GETDATE())

Deleted .LDF File how can the database be brought back online

Ok this morning one of the IT guys deleted the LDF file
from the server for a particular database. The file was
45 gig, he stopped the server then deleted the file,
without detaching the database.
We tried running sp_attach_db @.dbname='', @.physname=''
filling in the blanks but that refused to work.
We still have the 65 gig MDF file but we get error 9004
when we try to attach using enterprise manager.
We saw a knowledge base article saying to install service
pack 3a will this solve the problem, don't necessarily
want to mess about with these things unless absolutely
necessary
I will be very grateful is someone tells me how to sort
this and doesn't say i'm up the creek without a paddle
thanks in advanceYou can try sp_attach_single_file_db instead of sp_attach_db. That is still
not guaranteed to work in this scenario though, it is only _guaranteed_ to
work if you explicitly detach the database, but you might be lucky.
Otherwise you will have to restore from the back up.
--
Jacco Schalkwijk
SQL Server MVP
"Steven Scaife" <anonymous@.discussions.microsoft.com> wrote in message
news:08b501c48f52$f1c64690$a401280a@.phx.gbl...
> Ok this morning one of the IT guys deleted the LDF file
> from the server for a particular database. The file was
> 45 gig, he stopped the server then deleted the file,
> without detaching the database.
> We tried running sp_attach_db @.dbname='', @.physname=''
> filling in the blanks but that refused to work.
> We still have the 65 gig MDF file but we get error 9004
> when we try to attach using enterprise manager.
> We saw a knowledge base article saying to install service
> pack 3a will this solve the problem, don't necessarily
> want to mess about with these things unless absolutely
> necessary
> I will be very grateful is someone tells me how to sort
> this and doesn't say i'm up the creek without a paddle
> thanks in advance|||You also need to seriously suggest firing that IT guy...What he's done
really pissed off DBAs!
"Steven Scaife" <anonymous@.discussions.microsoft.com> wrote in message
news:08b501c48f52$f1c64690$a401280a@.phx.gbl...
> Ok this morning one of the IT guys deleted the LDF file
> from the server for a particular database. The file was
> 45 gig, he stopped the server then deleted the file,
> without detaching the database.
> We tried running sp_attach_db @.dbname='', @.physname=''
> filling in the blanks but that refused to work.
> We still have the 65 gig MDF file but we get error 9004
> when we try to attach using enterprise manager.
> We saw a knowledge base article saying to install service
> pack 3a will this solve the problem, don't necessarily
> want to mess about with these things unless absolutely
> necessary
> I will be very grateful is someone tells me how to sort
> this and doesn't say i'm up the creek without a paddle
> thanks in advance|||Common error. Look at the suggestion by Jasper Smith here
(http://tinyurl.com/5g2ol). Note that rebuilding the log this way is not
supported by Microsoft, and you should make a copy of the mdf file just in
case it does not work for you. Good luck.
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Use MiniSQLBackup Lite, free!
"Steven Scaife" <anonymous@.discussions.microsoft.com> wrote in message
news:08b501c48f52$f1c64690$a401280a@.phx.gbl...
> Ok this morning one of the IT guys deleted the LDF file
> from the server for a particular database. The file was
> 45 gig, he stopped the server then deleted the file,
> without detaching the database.
> We tried running sp_attach_db @.dbname='', @.physname=''
> filling in the blanks but that refused to work.
> We still have the 65 gig MDF file but we get error 9004
> when we try to attach using enterprise manager.
> We saw a knowledge base article saying to install service
> pack 3a will this solve the problem, don't necessarily
> want to mess about with these things unless absolutely
> necessary
> I will be very grateful is someone tells me how to sort
> this and doesn't say i'm up the creek without a paddle
> thanks in advance|||> You also need to seriously suggest firing that IT guy...What he's done
> really pissed off DBAs!
Or fire the IT manager who allow for non-trained personnel have this kind or privileges on a database
server... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Richard Ding" <rding@.acadian-asset.com> wrote in message news:eiYHCg2jEHA.4068@.TK2MSFTNGP10.phx.gbl...
> You also need to seriously suggest firing that IT guy...What he's done
> really pissed off DBAs!
>
> "Steven Scaife" <anonymous@.discussions.microsoft.com> wrote in message
> news:08b501c48f52$f1c64690$a401280a@.phx.gbl...
> > Ok this morning one of the IT guys deleted the LDF file
> > from the server for a particular database. The file was
> > 45 gig, he stopped the server then deleted the file,
> > without detaching the database.
> >
> > We tried running sp_attach_db @.dbname='', @.physname=''
> >
> > filling in the blanks but that refused to work.
> >
> > We still have the 65 gig MDF file but we get error 9004
> > when we try to attach using enterprise manager.
> >
> > We saw a knowledge base article saying to install service
> > pack 3a will this solve the problem, don't necessarily
> > want to mess about with these things unless absolutely
> > necessary
> >
> > I will be very grateful is someone tells me how to sort
> > this and doesn't say i'm up the creek without a paddle
> >
> > thanks in advance
>|||I wouldn't sack anyone for pissing of DBAs. Putting 65 GB of business data
at risk is a different matter though... not to mention the downtime and the
extra work involved.
--
Jacco Schalkwijk
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uWLfBT4jEHA.1404@.TK2MSFTNGP09.phx.gbl...
>> You also need to seriously suggest firing that IT guy...What he's done
>> really pissed off DBAs!
> Or fire the IT manager who allow for non-trained personnel have this kind
> or privileges on a database
> server... :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Richard Ding" <rding@.acadian-asset.com> wrote in message
> news:eiYHCg2jEHA.4068@.TK2MSFTNGP10.phx.gbl...
>> You also need to seriously suggest firing that IT guy...What he's done
>> really pissed off DBAs!
>>
>> "Steven Scaife" <anonymous@.discussions.microsoft.com> wrote in message
>> news:08b501c48f52$f1c64690$a401280a@.phx.gbl...
>> > Ok this morning one of the IT guys deleted the LDF file
>> > from the server for a particular database. The file was
>> > 45 gig, he stopped the server then deleted the file,
>> > without detaching the database.
>> >
>> > We tried running sp_attach_db @.dbname='', @.physname=''
>> >
>> > filling in the blanks but that refused to work.
>> >
>> > We still have the 65 gig MDF file but we get error 9004
>> > when we try to attach using enterprise manager.
>> >
>> > We saw a knowledge base article saying to install service
>> > pack 3a will this solve the problem, don't necessarily
>> > want to mess about with these things unless absolutely
>> > necessary
>> >
>> > I will be very grateful is someone tells me how to sort
>> > this and doesn't say i'm up the creek without a paddle
>> >
>> > thanks in advance
>>
>|||Well they managed to get it up and running with only a 2 week loss of data,
so that was good but they didnt get any sleep doing it, got it running the
next morning, not sure how they did it, but they did, luckily
think the backups are been re-thinked, lol
A day researching was different i s'pose
thanks for the suggestions
"Peter Yeoh" <nospam@.nospam.com> wrote in message
news:ekAqO$2jEHA.596@.TK2MSFTNGP11.phx.gbl...
> Common error. Look at the suggestion by Jasper Smith here
> (http://tinyurl.com/5g2ol). Note that rebuilding the log this way is not
> supported by Microsoft, and you should make a copy of the mdf file just in
> case it does not work for you. Good luck.
> --
> Peter Yeoh
> http://www.yohz.com
> Need smaller SQL2K backups? Use MiniSQLBackup Lite, free!
>
> "Steven Scaife" <anonymous@.discussions.microsoft.com> wrote in message
> news:08b501c48f52$f1c64690$a401280a@.phx.gbl...
> > Ok this morning one of the IT guys deleted the LDF file
> > from the server for a particular database. The file was
> > 45 gig, he stopped the server then deleted the file,
> > without detaching the database.
> >
> > We tried running sp_attach_db @.dbname='', @.physname=''
> >
> > filling in the blanks but that refused to work.
> >
> > We still have the 65 gig MDF file but we get error 9004
> > when we try to attach using enterprise manager.
> >
> > We saw a knowledge base article saying to install service
> > pack 3a will this solve the problem, don't necessarily
> > want to mess about with these things unless absolutely
> > necessary
> >
> > I will be very grateful is someone tells me how to sort
> > this and doesn't say i'm up the creek without a paddle
> >
> > thanks in advance
>

Deleted .LDF File how can the database be brought back online

Ok this morning one of the IT guys deleted the LDF file
from the server for a particular database. The file was
45 gig, he stopped the server then deleted the file,
without detaching the database.
We tried running sp_attach_db @.dbname='', @.physname=''
filling in the blanks but that refused to work.
We still have the 65 gig MDF file but we get error 9004
when we try to attach using enterprise manager.
We saw a knowledge base article saying to install service
pack 3a will this solve the problem, don't necessarily
want to mess about with these things unless absolutely
necessary
I will be very grateful is someone tells me how to sort
this and doesn't say i'm up the creek without a paddle
thanks in advanceYou can try sp_attach_single_file_db instead of sp_attach_db. That is still
not guaranteed to work in this scenario though, it is only _guaranteed_ to
work if you explicitly detach the database, but you might be lucky.
Otherwise you will have to restore from the back up.
Jacco Schalkwijk
SQL Server MVP
"Steven Scaife" <anonymous@.discussions.microsoft.com> wrote in message
news:08b501c48f52$f1c64690$a401280a@.phx.gbl...
> Ok this morning one of the IT guys deleted the LDF file
> from the server for a particular database. The file was
> 45 gig, he stopped the server then deleted the file,
> without detaching the database.
> We tried running sp_attach_db @.dbname='', @.physname=''
> filling in the blanks but that refused to work.
> We still have the 65 gig MDF file but we get error 9004
> when we try to attach using enterprise manager.
> We saw a knowledge base article saying to install service
> pack 3a will this solve the problem, don't necessarily
> want to mess about with these things unless absolutely
> necessary
> I will be very grateful is someone tells me how to sort
> this and doesn't say i'm up the creek without a paddle
> thanks in advance|||You also need to seriously suggest firing that IT guy...What he's done
really pissed off DBAs!
"Steven Scaife" <anonymous@.discussions.microsoft.com> wrote in message
news:08b501c48f52$f1c64690$a401280a@.phx.gbl...
> Ok this morning one of the IT guys deleted the LDF file
> from the server for a particular database. The file was
> 45 gig, he stopped the server then deleted the file,
> without detaching the database.
> We tried running sp_attach_db @.dbname='', @.physname=''
> filling in the blanks but that refused to work.
> We still have the 65 gig MDF file but we get error 9004
> when we try to attach using enterprise manager.
> We saw a knowledge base article saying to install service
> pack 3a will this solve the problem, don't necessarily
> want to mess about with these things unless absolutely
> necessary
> I will be very grateful is someone tells me how to sort
> this and doesn't say i'm up the creek without a paddle
> thanks in advance|||Common error. Look at the suggestion by Jasper Smith here
(http://tinyurl.com/5g2ol). Note that rebuilding the log this way is not
supported by Microsoft, and you should make a copy of the mdf file just in
case it does not work for you. Good luck.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Use MiniSQLBackup Lite, free!
"Steven Scaife" <anonymous@.discussions.microsoft.com> wrote in message
news:08b501c48f52$f1c64690$a401280a@.phx.gbl...
> Ok this morning one of the IT guys deleted the LDF file
> from the server for a particular database. The file was
> 45 gig, he stopped the server then deleted the file,
> without detaching the database.
> We tried running sp_attach_db @.dbname='', @.physname=''
> filling in the blanks but that refused to work.
> We still have the 65 gig MDF file but we get error 9004
> when we try to attach using enterprise manager.
> We saw a knowledge base article saying to install service
> pack 3a will this solve the problem, don't necessarily
> want to mess about with these things unless absolutely
> necessary
> I will be very grateful is someone tells me how to sort
> this and doesn't say i'm up the creek without a paddle
> thanks in advance|||> You also need to seriously suggest firing that IT guy...What he's done
> really pissed off DBAs!
Or fire the IT manager who allow for non-trained personnel have this kind or
privileges on a database
server... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Richard Ding" <rding@.acadian-asset.com> wrote in message news:eiYHCg2jEHA.4068@.TK2MSFTNGP10
.phx.gbl...
> You also need to seriously suggest firing that IT guy...What he's done
> really pissed off DBAs!
>
> "Steven Scaife" <anonymous@.discussions.microsoft.com> wrote in message
> news:08b501c48f52$f1c64690$a401280a@.phx.gbl...
>|||I wouldn't sack anyone for pissing of DBAs. Putting 65 GB of business data
at risk is a different matter though... not to mention the downtime and the
extra work involved.
Jacco Schalkwijk
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uWLfBT4jEHA.1404@.TK2MSFTNGP09.phx.gbl...
> Or fire the IT manager who allow for non-trained personnel have this kind
> or privileges on a database
> server... :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Richard Ding" <rding@.acadian-asset.com> wrote in message
> news:eiYHCg2jEHA.4068@.TK2MSFTNGP10.phx.gbl...
>|||Well they managed to get it up and running with only a 2 week loss of data,
so that was good but they didnt get any sleep doing it, got it running the
next morning, not sure how they did it, but they did, luckily
think the backups are been re-thinked, lol
A day researching was different i s'pose
thanks for the suggestions
"Peter Yeoh" <nospam@.nospam.com> wrote in message
news:ekAqO$2jEHA.596@.TK2MSFTNGP11.phx.gbl...
> Common error. Look at the suggestion by Jasper Smith here
> (http://tinyurl.com/5g2ol). Note that rebuilding the log this way is not
> supported by Microsoft, and you should make a copy of the mdf file just in
> case it does not work for you. Good luck.
> --
> Peter Yeoh
> http://www.yohz.com
> Need smaller SQL2K backups? Use MiniSQLBackup Lite, free!
>
> "Steven Scaife" <anonymous@.discussions.microsoft.com> wrote in message
> news:08b501c48f52$f1c64690$a401280a@.phx.gbl...
>

Deleted .LDF File how can the database be brought back online

Ok this morning one of the IT guys deleted the LDF file
from the server for a particular database. The file was
45 gig, he stopped the server then deleted the file,
without detaching the database.
We tried running sp_attach_db @.dbname='', @.physname=''
filling in the blanks but that refused to work.
We still have the 65 gig MDF file but we get error 9004
when we try to attach using enterprise manager.
We saw a knowledge base article saying to install service
pack 3a will this solve the problem, don't necessarily
want to mess about with these things unless absolutely
necessary
I will be very grateful is someone tells me how to sort
this and doesn't say i'm up the creek without a paddle
thanks in advance
You can try sp_attach_single_file_db instead of sp_attach_db. That is still
not guaranteed to work in this scenario though, it is only _guaranteed_ to
work if you explicitly detach the database, but you might be lucky.
Otherwise you will have to restore from the back up.
Jacco Schalkwijk
SQL Server MVP
"Steven Scaife" <anonymous@.discussions.microsoft.com> wrote in message
news:08b501c48f52$f1c64690$a401280a@.phx.gbl...
> Ok this morning one of the IT guys deleted the LDF file
> from the server for a particular database. The file was
> 45 gig, he stopped the server then deleted the file,
> without detaching the database.
> We tried running sp_attach_db @.dbname='', @.physname=''
> filling in the blanks but that refused to work.
> We still have the 65 gig MDF file but we get error 9004
> when we try to attach using enterprise manager.
> We saw a knowledge base article saying to install service
> pack 3a will this solve the problem, don't necessarily
> want to mess about with these things unless absolutely
> necessary
> I will be very grateful is someone tells me how to sort
> this and doesn't say i'm up the creek without a paddle
> thanks in advance
|||You also need to seriously suggest firing that IT guy...What he's done
really pissed off DBAs!
"Steven Scaife" <anonymous@.discussions.microsoft.com> wrote in message
news:08b501c48f52$f1c64690$a401280a@.phx.gbl...
> Ok this morning one of the IT guys deleted the LDF file
> from the server for a particular database. The file was
> 45 gig, he stopped the server then deleted the file,
> without detaching the database.
> We tried running sp_attach_db @.dbname='', @.physname=''
> filling in the blanks but that refused to work.
> We still have the 65 gig MDF file but we get error 9004
> when we try to attach using enterprise manager.
> We saw a knowledge base article saying to install service
> pack 3a will this solve the problem, don't necessarily
> want to mess about with these things unless absolutely
> necessary
> I will be very grateful is someone tells me how to sort
> this and doesn't say i'm up the creek without a paddle
> thanks in advance
|||Common error. Look at the suggestion by Jasper Smith here
(http://tinyurl.com/5g2ol). Note that rebuilding the log this way is not
supported by Microsoft, and you should make a copy of the mdf file just in
case it does not work for you. Good luck.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Use MiniSQLBackup Lite, free!
"Steven Scaife" <anonymous@.discussions.microsoft.com> wrote in message
news:08b501c48f52$f1c64690$a401280a@.phx.gbl...
> Ok this morning one of the IT guys deleted the LDF file
> from the server for a particular database. The file was
> 45 gig, he stopped the server then deleted the file,
> without detaching the database.
> We tried running sp_attach_db @.dbname='', @.physname=''
> filling in the blanks but that refused to work.
> We still have the 65 gig MDF file but we get error 9004
> when we try to attach using enterprise manager.
> We saw a knowledge base article saying to install service
> pack 3a will this solve the problem, don't necessarily
> want to mess about with these things unless absolutely
> necessary
> I will be very grateful is someone tells me how to sort
> this and doesn't say i'm up the creek without a paddle
> thanks in advance
|||> You also need to seriously suggest firing that IT guy...What he's done
> really pissed off DBAs!
Or fire the IT manager who allow for non-trained personnel have this kind or privileges on a database
server... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Richard Ding" <rding@.acadian-asset.com> wrote in message news:eiYHCg2jEHA.4068@.TK2MSFTNGP10.phx.gbl...
> You also need to seriously suggest firing that IT guy...What he's done
> really pissed off DBAs!
>
> "Steven Scaife" <anonymous@.discussions.microsoft.com> wrote in message
> news:08b501c48f52$f1c64690$a401280a@.phx.gbl...
>
|||I wouldn't sack anyone for pissing of DBAs. Putting 65 GB of business data
at risk is a different matter though... not to mention the downtime and the
extra work involved.
Jacco Schalkwijk
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uWLfBT4jEHA.1404@.TK2MSFTNGP09.phx.gbl...
> Or fire the IT manager who allow for non-trained personnel have this kind
> or privileges on a database
> server... :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Richard Ding" <rding@.acadian-asset.com> wrote in message
> news:eiYHCg2jEHA.4068@.TK2MSFTNGP10.phx.gbl...
>
|||Well they managed to get it up and running with only a 2 week loss of data,
so that was good but they didnt get any sleep doing it, got it running the
next morning, not sure how they did it, but they did, luckily
think the backups are been re-thinked, lol
A day researching was different i s'pose
thanks for the suggestions
"Peter Yeoh" <nospam@.nospam.com> wrote in message
news:ekAqO$2jEHA.596@.TK2MSFTNGP11.phx.gbl...
> Common error. Look at the suggestion by Jasper Smith here
> (http://tinyurl.com/5g2ol). Note that rebuilding the log this way is not
> supported by Microsoft, and you should make a copy of the mdf file just in
> case it does not work for you. Good luck.
> --
> Peter Yeoh
> http://www.yohz.com
> Need smaller SQL2K backups? Use MiniSQLBackup Lite, free!
>
> "Steven Scaife" <anonymous@.discussions.microsoft.com> wrote in message
> news:08b501c48f52$f1c64690$a401280a@.phx.gbl...
>

Delete takes extremly long time.

guys, im running MSSQL 2000 latest SP on PIII-1Ghz, 4GB RAM, SCSII and all good things but...

...when i execute this simple query to delete 8,000 records out of 18,000 total in the table it takes 11 seconds:

delete from tempViewResults where opportunity_id = '016158'

i do have an index on opportunity_id.
any ideas why? is this normal?

any help is greatly appreciated.please anybody?|||In this case, the index is not really helping you so much as hurting you. In order for the optimizer to consider using the index, you have to be going after <10% of the table. How many other indexes are there on the table? Also check for triggers on the table that may be firing for deletes.|||Also consider to create that "temp" table in the tempdb, which is faster.|||Two more cents:

Cent #1:Your delete statement will take longer if their are cascading relationships set up with subtables.

Cent #2:I'm not sure if using tempdb would be faster, because it still would be writing data to disk, but if you can get away with using a table variable which uses memory you might be able to avoid disk writes altogether.

blindman|||Using a #temp table is faster if your database is in FULL or BULK-LOGGED model.

Of course if your database is in SIMLE model you won't get any speed improvement|||Manowar:

What makes the temptable faster than a stored table? I couldn't find anything in Books Online regarding this. I'd have thought that querying a stored table would enable the optimizer to take advantage of cached query plans. Could you point me to where this is explained or documented in Books Online or Microsoft's support site?

blindman|||I think there's nothing about it on BOL or MSDN. I've found a note on Kalen Delaney "Inside SQL Server 2000" world-famous book.

You can look in the section about temp (#) tables. Kalen says that since in the tempdb sql server only register log data for rollback purposes (and not for database recovery), data modification operations can be up to for time faster that in a standard db.

DELETE Problem

Hi Guys,

I have this strange problem. Take the following SQL i wrote which
erturns a list of dates lower than the one entered in a table :-

SELECT * FROM HACPRP.EQ_POOL_ACT_VIEW
WHERE ACTVTY_DT IN

(SELECT ACTVTY_DT FROM HACPRP.EQ_POOL_ACT_VIEW

WHERE SUBSTR(ACTVTY_DT,5,2) < '02'
)

UNION

SELECT * FROM HACPRP.EQ_POOL_ACT_VIEW
WHERE ACTVTY_DT IN

(SELECT ACTVTY_DT FROM HACPRP.EQ_POOL_ACT_VIEW

WHERE SUBSTR(ACTVTY_DT,1,2) < '03'
AND
SUBSTR(ACTVTY_DT,5,2) = '02'
)

UNION

SELECT * FROM HACPRP.EQ_POOL_ACT_VIEW
WHERE ACTVTY_DT IN

(SELECT ACTVTY_DT FROM HACPRP.EQ_POOL_ACT_VIEW

WHERE SUBSTR(ACTVTY_DT,3,2) < '27'
AND
SUBSTR(ACTVTY_DT,1,2) = '03'
AND
SUBSTR(ACTVTY_DT,5,2) = '02'
);

I had to do this in this format as whoever created the table made the ACTVTY_DT a CHAR(06) instead of a DATE !
This works fine and gives me back my required results.

The problem is however - How do I get SQL to delete these results now
from the HACPRP.EQ_POOL_ACT_VIEW table ?

No matter what i try it just falls over and will not delete the records.
Is it possible to do ?

I really need help on this one, so any comments would be really appreciated.

Peter.just replace the select * with the word DELETE, most likely cannot do the UNION DELETE so it most likely will have to be three seperate statements without the union.

let me know if that does not work|||Try this:

DELETE FROM HACPRP.EQ_POOL_ACT_VIEW
WHERE ACTVTY_DT IN
(SELECT ACTVTY_DT FROM HACPRP.EQ_POOL_ACT_VIEW
WHERE SUBSTR(ACTVTY_DT,5,2) < '02'
OR (SUBSTR(ACTVTY_DT,1,2) < '03'
AND SUBSTR(ACTVTY_DT,5,2) = '02')
OR (SUBSTR(ACTVTY_DT,3,2) < '27'
AND SUBSTR(ACTVTY_DT,1,2) = '03'
AND SUBSTR(ACTVTY_DT,5,2) = '02'));

also:

DELETE FROM HACPRP.EQ_POOL_ACT_VIEW
WHERE TO_DATE(ACTVTY_DT,'MMDDYY') < '27-MAR-2003';

;)|||hi,

or just type delete from ( <your query)

hope this helps

Delete partial from column

Guys, i have a table that one of the columns (Email To) is
a concatenated list of email addresses separated by semi colons ";".

i.e.:

rrb7@.yahoo.com;richard.butcher@.sthou.com;administr ator@.sthou.com

etc like that.
each row varies with one exception. administrator@.sthou.com is in each one.

is there a simple way thru sql or T-SQL to delete that "administrator@.sthou.com" part? or should i call each row individually into say, a VB.net form using a split with the deliminator ";"
and then looping thru and updating each row?

thanks again for any easy answer
rikYou should be able to use the Replace function to delete that with an update query. It's available in both Access and T_SQL.|||Jelly Link update the post : It should be UPDATE, not DELETE
UPDATE <table_name>
set EmailTo = replace(EmailTo, 'administrator@.sthou.com;','')
where EmailTo like 'administrator@.sthou.com;%'

UPDATE <table_name>
set EmailTo = replace(EmailTo, ';administrator@.sthou.com;',';')
where EmailTo like '%;administrator@.sthou.com;%'

UPDATE <table_name>
set EmailTo = replace(EmailTo, ';administrator@.sthou.com','')
where EmailTo like '%;administrator@.sthou.com'|||delete <table_name>

delete?? And I'd think the where clause unnecessary, since the OP states that's in every record.|||ups sorry.....its UPDATE :p update...set......where.........

ehm...i use the where clause to remove the separator ";" and the email address when administrator@.sthou.com is at the beginning, in the middle, or even at the end of the list, but not emails which contain administrator@.sthou.com (eg : blablaadministrator@.sthou.com)|||delete?? And I'd think the where clause unnecessary, since the OP states that's in every record.

ups sorry.....its UPDATE :p update...set......where.........

ehm...i use the where clause to remove the separator ";" and the email address when administrator@.sthou.com is at the beginning, in the middle, or even at the end of the list, but not emails which contain administrator@.sthou.com (eg : blablaadministrator@.sthou.com)


Delete instead of update!!!!!!!!!.Stick this post and dont allow the poster to edit again in this post.hehehehe.lets every one see.I can see the panic in jelly link's face that time ,lol|||sorry.....hav so many things in my head :p|||it works great - i truly appreciate the help on this. Working with Oracle for so long, i feel like im running to catch up. it all looks familiar, but really not at all
thanks again
rik