Deleting of Databases

Windows 2003 SP2 with SQL 2000:
We are trying to delete some databases that are old and the Enterprise
Manager hangs on deletion. We upgraded from SP 3a to 4 hoping that would fix
the problem but it doesn't. We've tried from other SQL boxes' Enterprise
Managers as well with no privail. Is there any way (potentially from SQLCMD)
to properly force the deletion of a database? Thanks in advance to anyone
with some info!
ctowndu33 wrote:
> Windows 2003 SP2 with SQL 2000:
> We are trying to delete some databases that are old and the Enterprise
> Manager hangs on deletion. We upgraded from SP 3a to 4 hoping that would fix
> the problem but it doesn't. We've tried from other SQL boxes' Enterprise
> Managers as well with no privail. Is there any way (potentially from SQLCMD)
> to properly force the deletion of a database? Thanks in advance to anyone
> with some info!
Have you tried to switch database to off-line mode (or detach database)
and then delete mdf/ldf files?
PL
|||1) Do you get any error message (perhaps logged in the sql server error
logs?
2) Have you tried this from the command line or from query analyzer?
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"ctowndu33" <ctowndu33@.discussions.microsoft.com> wrote in message
news:450B4B7A-F575-4F29-8D31-55B6F395ABB4@.microsoft.com...
> Windows 2003 SP2 with SQL 2000:
> We are trying to delete some databases that are old and the Enterprise
> Manager hangs on deletion. We upgraded from SP 3a to 4 hoping that would
> fix
> the problem but it doesn't. We've tried from other SQL boxes' Enterprise
> Managers as well with no privail. Is there any way (potentially from
> SQLCMD)
> to properly force the deletion of a database? Thanks in advance to anyone
> with some info!
|||Now we realize that we can delete the databases if we clear the check mark to
remove backups. It's almost like it is searching for old backups that don't
exist any more. I though maybe I would see some really slow speeds (if it is
searching for old backups) if I try and do a restore. Turns out it took like
10 minutes for the restore box to pop up. Could the history of backups
contain orphaned informationt that would be causing this non-responsive
behavior? Thanks again everyone!
"ctowndu33" wrote:

> Windows 2003 SP2 with SQL 2000:
> We are trying to delete some databases that are old and the Enterprise
> Manager hangs on deletion. We upgraded from SP 3a to 4 hoping that would fix
> the problem but it doesn't. We've tried from other SQL boxes' Enterprise
> Managers as well with no privail. Is there any way (potentially from SQLCMD)
> to properly force the deletion of a database? Thanks in advance to anyone
> with some info!
|||Or perhaps it is simply taking forever to find the backups to delete? I
have a client with 6500+ databases on one server. I long ago had to create
several indexes on msdb tables so that things like this would work. The
index seeks enabled by the indexes let to very fast operations on
backup/restore tables.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"ctowndu33" <ctowndu33@.discussions.microsoft.com> wrote in message
news:AADA5A38-70AC-4B89-862E-BDE64C5F1178@.microsoft.com...[vbcol=seagreen]
> Now we realize that we can delete the databases if we clear the check mark
> to
> remove backups. It's almost like it is searching for old backups that
> don't
> exist any more. I though maybe I would see some really slow speeds (if it
> is
> searching for old backups) if I try and do a restore. Turns out it took
> like
> 10 minutes for the restore box to pop up. Could the history of backups
> contain orphaned informationt that would be causing this non-responsive
> behavior? Thanks again everyone!
> "ctowndu33" wrote:
sql

Deleting of Databases

Windows 2003 SP2 with SQL 2000:
We are trying to delete some databases that are old and the Enterprise
Manager hangs on deletion. We upgraded from SP 3a to 4 hoping that would fi
x
the problem but it doesn't. We've tried from other SQL boxes' Enterprise
Managers as well with no privail. Is there any way (potentially from SQLCMD
)
to properly force the deletion of a database? Thanks in advance to anyone
with some info!ctowndu33 wrote:
> Windows 2003 SP2 with SQL 2000:
> We are trying to delete some databases that are old and the Enterprise
> Manager hangs on deletion. We upgraded from SP 3a to 4 hoping that would
fix
> the problem but it doesn't. We've tried from other SQL boxes' Enterprise
> Managers as well with no privail. Is there any way (potentially from SQLC
MD)
> to properly force the deletion of a database? Thanks in advance to anyone
> with some info!
Have you tried to switch database to off-line mode (or detach database)
and then delete mdf/ldf files?
PL|||1) Do you get any error message (perhaps logged in the sql server error
logs?
2) Have you tried this from the command line or from query analyzer?
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"ctowndu33" <ctowndu33@.discussions.microsoft.com> wrote in message
news:450B4B7A-F575-4F29-8D31-55B6F395ABB4@.microsoft.com...
> Windows 2003 SP2 with SQL 2000:
> We are trying to delete some databases that are old and the Enterprise
> Manager hangs on deletion. We upgraded from SP 3a to 4 hoping that would
> fix
> the problem but it doesn't. We've tried from other SQL boxes' Enterprise
> Managers as well with no privail. Is there any way (potentially from
> SQLCMD)
> to properly force the deletion of a database? Thanks in advance to anyone
> with some info!|||Now we realize that we can delete the databases if we clear the check mark t
o
remove backups. It's almost like it is searching for old backups that don't
exist any more. I though maybe I would see some really slow speeds (if it i
s
searching for old backups) if I try and do a restore. Turns out it took lik
e
10 minutes for the restore box to pop up. Could the history of backups
contain orphaned informationt that would be causing this non-responsive
behavior? Thanks again everyone!
"ctowndu33" wrote:

> Windows 2003 SP2 with SQL 2000:
> We are trying to delete some databases that are old and the Enterprise
> Manager hangs on deletion. We upgraded from SP 3a to 4 hoping that would
fix
> the problem but it doesn't. We've tried from other SQL boxes' Enterprise
> Managers as well with no privail. Is there any way (potentially from SQLC
MD)
> to properly force the deletion of a database? Thanks in advance to anyone
> with some info!|||Or perhaps it is simply taking forever to find the backups to delete? I
have a client with 6500+ databases on one server. I long ago had to create
several indexes on msdb tables so that things like this would work. The
index seeks enabled by the indexes let to very fast operations on
backup/restore tables.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"ctowndu33" <ctowndu33@.discussions.microsoft.com> wrote in message
news:AADA5A38-70AC-4B89-862E-BDE64C5F1178@.microsoft.com...[vbcol=seagreen]
> Now we realize that we can delete the databases if we clear the check mark
> to
> remove backups. It's almost like it is searching for old backups that
> don't
> exist any more. I though maybe I would see some really slow speeds (if it
> is
> searching for old backups) if I try and do a restore. Turns out it took
> like
> 10 minutes for the restore box to pop up. Could the history of backups
> contain orphaned informationt that would be causing this non-responsive
> behavior? Thanks again everyone!
> "ctowndu33" wrote:
>

Deleting of Databases

Windows 2003 SP2 with SQL 2000:
We are trying to delete some databases that are old and the Enterprise
Manager hangs on deletion. We upgraded from SP 3a to 4 hoping that would fix
the problem but it doesn't. We've tried from other SQL boxes' Enterprise
Managers as well with no privail. Is there any way (potentially from SQLCMD)
to properly force the deletion of a database? Thanks in advance to anyone
with some info!ctowndu33 wrote:
> Windows 2003 SP2 with SQL 2000:
> We are trying to delete some databases that are old and the Enterprise
> Manager hangs on deletion. We upgraded from SP 3a to 4 hoping that would fix
> the problem but it doesn't. We've tried from other SQL boxes' Enterprise
> Managers as well with no privail. Is there any way (potentially from SQLCMD)
> to properly force the deletion of a database? Thanks in advance to anyone
> with some info!
Have you tried to switch database to off-line mode (or detach database)
and then delete mdf/ldf files?
PL|||1) Do you get any error message (perhaps logged in the sql server error
logs?
2) Have you tried this from the command line or from query analyzer?
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"ctowndu33" <ctowndu33@.discussions.microsoft.com> wrote in message
news:450B4B7A-F575-4F29-8D31-55B6F395ABB4@.microsoft.com...
> Windows 2003 SP2 with SQL 2000:
> We are trying to delete some databases that are old and the Enterprise
> Manager hangs on deletion. We upgraded from SP 3a to 4 hoping that would
> fix
> the problem but it doesn't. We've tried from other SQL boxes' Enterprise
> Managers as well with no privail. Is there any way (potentially from
> SQLCMD)
> to properly force the deletion of a database? Thanks in advance to anyone
> with some info!|||Now we realize that we can delete the databases if we clear the check mark to
remove backups. It's almost like it is searching for old backups that don't
exist any more. I though maybe I would see some really slow speeds (if it is
searching for old backups) if I try and do a restore. Turns out it took like
10 minutes for the restore box to pop up. Could the history of backups
contain orphaned informationt that would be causing this non-responsive
behavior? Thanks again everyone!
"ctowndu33" wrote:
> Windows 2003 SP2 with SQL 2000:
> We are trying to delete some databases that are old and the Enterprise
> Manager hangs on deletion. We upgraded from SP 3a to 4 hoping that would fix
> the problem but it doesn't. We've tried from other SQL boxes' Enterprise
> Managers as well with no privail. Is there any way (potentially from SQLCMD)
> to properly force the deletion of a database? Thanks in advance to anyone
> with some info!|||Or perhaps it is simply taking forever to find the backups to delete? I
have a client with 6500+ databases on one server. I long ago had to create
several indexes on msdb tables so that things like this would work. The
index seeks enabled by the indexes let to very fast operations on
backup/restore tables.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"ctowndu33" <ctowndu33@.discussions.microsoft.com> wrote in message
news:AADA5A38-70AC-4B89-862E-BDE64C5F1178@.microsoft.com...
> Now we realize that we can delete the databases if we clear the check mark
> to
> remove backups. It's almost like it is searching for old backups that
> don't
> exist any more. I though maybe I would see some really slow speeds (if it
> is
> searching for old backups) if I try and do a restore. Turns out it took
> like
> 10 minutes for the restore box to pop up. Could the history of backups
> contain orphaned informationt that would be causing this non-responsive
> behavior? Thanks again everyone!
> "ctowndu33" wrote:
>> Windows 2003 SP2 with SQL 2000:
>> We are trying to delete some databases that are old and the Enterprise
>> Manager hangs on deletion. We upgraded from SP 3a to 4 hoping that would
>> fix
>> the problem but it doesn't. We've tried from other SQL boxes' Enterprise
>> Managers as well with no privail. Is there any way (potentially from
>> SQLCMD)
>> to properly force the deletion of a database? Thanks in advance to
>> anyone
>> with some info!

delete query hangs

I ran a simple delete query that should have affected about 2.5 million rows. The query was:

delete from traffic where left(filename, 4) = '0304'

After running this the TLOG increased to about 2 gig, but still had over 5 gig of space left on the disk. Then the SPID for the query showed a status of rollback this stayed like this for over 3 hours. After the status changed to rollback I noticed one of my 4 processors was running near 100% while the others were idle. I was forced to kill the query and reboot the computer. After rebooting the same thing happened again.

Any ideas what is causing this?

I'm running SQL Server 2000Long transactions will cause all sorts of problems - transaction log issues, lock escalation issues ... The short answer is to delete in smaller chunks.

delete query hangs

ran a simple delete query that should have affected about 2.5 million = rows. The query was:
delete from traffic where left(filename, 4) =3D '0304'
After running this the TLOG increased to about 2 gig, but still had over = 5 gig of space left on the disk. Then the SPID for the query showed a = status of rollback this stayed like this for over 3 hours. After the = status changed to rollback I noticed one of my 4 processors was running = near 100% while the others were idle. I was forced to kill the query and = reboot the computer. After rebooting the same thing happened again.
Any ideas what is causing this?
I'm running SQL Server 2000There may have been an error somewhere, whether it was a deadlock, timeout,
out of space in the db(not set to grow) or whatever.
It's also possible that somebody killed the process.
Queries don't just roll back on their own.
Also, deleting 2.5 million rows can often be achieved by other means. That's
one big honkin' transaction.
Can you delete this data in a batch mode, say, 10,000 records at a time or
something?
You'd be amazed at the performance boost you can get from that technique
(although then you're not doing an atomic bit of work).
Another alternative method is to transfer the data from your source table
that _doesn't_ fit the criteria, and just drop the original table.
It all depends on your situation, though, such as if you've got steady data
flow into the table, or lots of updating, etc.
James Hokes
"jdoyle" <anonymous@.discussions.microsoft.com> wrote in message
news:03d101c3c5d2$212988c0$a601280a@.phx.gbl...
ran a simple delete query that should have affected about 2.5 million rows.
The query was:
delete from traffic where left(filename, 4) = '0304'
After running this the TLOG increased to about 2 gig, but still had over 5
gig of space left on the disk. Then the SPID for the query showed a status
of rollback this stayed like this for over 3 hours. After the status changed
to rollback I noticed one of my 4 processors was running near 100% while the
others were idle. I was forced to kill the query and reboot the computer.
After rebooting the same thing happened again.
Any ideas what is causing this?
I'm running SQL Server 2000|||I think James covered most in his post. Just one comment:
<<I was forced to kill the query and reboot the computer. >>
SQL Server was for some reason doing a rollback. And you now stop SQL Server, which mean that the
rollback will be restarted again at startup. If you think about it, SQL Server can't let a
transaction be partial committed... So, it could have been a good idea to let the on-going rollback
finish instead of restarting SQL Server. This is why MS added the WITH STATUSONLY option to the KILL
command in SQL Server 2000.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"jdoyle" <anonymous@.discussions.microsoft.com> wrote in message
news:03d101c3c5d2$212988c0$a601280a@.phx.gbl...
ran a simple delete query that should have affected about 2.5 million rows. The query was:
delete from traffic where left(filename, 4) = '0304'
After running this the TLOG increased to about 2 gig, but still had over 5 gig of space left on the
disk. Then the SPID for the query showed a status of rollback this stayed like this for over 3
hours. After the status changed to rollback I noticed one of my 4 processors was running near 100%
while the others were idle. I was forced to kill the query and reboot the computer. After rebooting
the same thing happened again.
Any ideas what is causing this?
I'm running SQL Server 2000|||I don't think the rollback was in progress. There appeared to be no disk =activity and one cpu just stayed at 100% usage for 3 hours. This query =should have taken about 15 minutes. Other applications such as EM =started to lock up during this time which is why I had to do the reboot. =
>--Original Message--
>I think James covered most in his post. Just one comment:
><<I was forced to kill the query and reboot the computer. >>
>SQL Server was for some reason doing a rollback. And you now stop SQL =Server, which mean that the
>rollback will be restarted again at startup. If you think about it, SQL =Server can't let a
>transaction be partial committed... So, it could have been a good idea =to let the on-going rollback
>finish instead of restarting SQL Server. This is why MS added the WITH =STATUSONLY option to the KILL
>command in SQL Server 2000.
>-- >Tibor Karaszi, SQL Server MVP
>Archive at: =http://groups.google.com/groups?oi=3Ddjq&as_ugroup=3Dmicrosoft.public.sql=
server
>
>"jdoyle" <anonymous@.discussions.microsoft.com> wrote in message
>news:03d101c3c5d2$212988c0$a601280a@.phx.gbl...
>ran a simple delete query that should have affected about 2.5 million =rows. The query was:
>delete from traffic where left(filename, 4) =3D '0304'
>
>After running this the TLOG increased to about 2 gig, but still had =over 5 gig of space left on the
>disk. Then the SPID for the query showed a status of rollback this =stayed like this for over 3
>hours. After the status changed to rollback I noticed one of my 4 =processors was running near 100%
>while the others were idle. I was forced to kill the query and reboot =the computer. After rebooting
>the same thing happened again.
>Any ideas what is causing this?
>I'm running SQL Server 2000
>
>.
>|||could you explain how to do the batch delete and the move method? Thanks
>--Original Message--
>There may have been an error somewhere, whether it was a deadlock, =timeout,
>out of space in the db(not set to grow) or whatever.
>It's also possible that somebody killed the process.
>Queries don't just roll back on their own.
>Also, deleting 2.5 million rows can often be achieved by other means. =That's
>one big honkin' transaction.
>Can you delete this data in a batch mode, say, 10,000 records at a time =or
>something?
>You'd be amazed at the performance boost you can get from that =technique
>(although then you're not doing an atomic bit of work).
>Another alternative method is to transfer the data from your source =table
>that _doesn't_ fit the criteria, and just drop the original table.
>It all depends on your situation, though, such as if you've got steady =data
>flow into the table, or lots of updating, etc.
>James Hokes
>
>"jdoyle" <anonymous@.discussions.microsoft.com> wrote in message
>news:03d101c3c5d2$212988c0$a601280a@.phx.gbl...
>ran a simple delete query that should have affected about 2.5 million =rows.
>The query was:
>delete from traffic where left(filename, 4) =3D '0304'
>
>After running this the TLOG increased to about 2 gig, but still had =over 5
>gig of space left on the disk. Then the SPID for the query showed a =status
>of rollback this stayed like this for over 3 hours. After the status =changed
>to rollback I noticed one of my 4 processors was running near 100% =while the
>others were idle. I was forced to kill the query and reboot the =computer.
>After rebooting the same thing happened again.
>Any ideas what is causing this?
>I'm running SQL Server 2000
>
>.
>|||What I do is like this:
/*Create a temporary table, say #_Keys*/
CREATE TABLE #_Keys
(
KeyValue int NOT NULL,
Ready bit NOT NULL DEFAULT(0),
Deleted bit NOT NULL DEFAULT(0)
)
/*Populate the temp table with the key values of the rows that you want to
delete*/
INSERT INTO #_Keys
SELECT
T.KeyValue,
0 AS Ready,
0 AS Deleted
FROM
MyTable AS T
WHERE
LEFT(T.filename, 4) = '0304'
CREATE INDEX SK_Ready ON #_Keys (Deleted, Ready) WITH FILLFACTOR=100
/*now you've got #_Keys populated and ready to go.*/
/*Next, start a WHILE loop like so:*/
WHILE EXISTS
(
SELECT
K.KeyValue
FROM
#_Keys AS K
WHERE
K.Deleted = 0
)
BEGIN
UPDATE
K
SET
K.Ready = 1
FROM
#_Keys AS K
INNER JOIN
(
SELECT
TOP 1000 K1.KeyValue
FROM
#_Keys AS K1
WHERE
K1.Ready = 0 AND
K1.Deleted = 0
) AS K2 ON
K2.KeyValue = K.KeyValue
/*Now, you've got 1000 rows marked and ready to delete.*/
/*Next, delete them*/
BEGIN TRANSACTION
DELETE
T
FROM
MyTable AS T
INNER JOIN #_Keys AS K ON
K.KeyValue = T.KeyValue
WHERE
K.Ready = 1 AND
K.Deleted = 0
UPDATE
K
SET
K.Deleted = 1
FROM
#_Keys AS K
WHERE
K.Ready = 1 AND
K.Deleted = 0
COMMIT TRANSACTION
END
DROP TABLE #_Keys
This will most likely delete the rows in less time than a normal DELETE
going after all 2.5 Million,
and it will likely cause less resource utilization for other concurrent
users as well.
Now, there are many other ways to do it; this is just one small example.
James Hokes
<anonymous@.discussions.microsoft.com> wrote in message
news:0a0301c3c641$fe3d7400$a501280a@.phx.gbl...
could you explain how to do the batch delete and the move method? Thanks
>--Original Message--
>There may have been an error somewhere, whether it was a deadlock, timeout,
>out of space in the db(not set to grow) or whatever.
>It's also possible that somebody killed the process.
>Queries don't just roll back on their own.
>Also, deleting 2.5 million rows can often be achieved by other means.
That's
>one big honkin' transaction.
>Can you delete this data in a batch mode, say, 10,000 records at a time or
>something?
>You'd be amazed at the performance boost you can get from that technique
>(although then you're not doing an atomic bit of work).
>Another alternative method is to transfer the data from your source table
>that _doesn't_ fit the criteria, and just drop the original table.
>It all depends on your situation, though, such as if you've got steady data
>flow into the table, or lots of updating, etc.
>James Hokes
>
>"jdoyle" <anonymous@.discussions.microsoft.com> wrote in message
>news:03d101c3c5d2$212988c0$a601280a@.phx.gbl...
>ran a simple delete query that should have affected about 2.5 million rows.
>The query was:
>delete from traffic where left(filename, 4) = '0304'
>
>After running this the TLOG increased to about 2 gig, but still had over 5
>gig of space left on the disk. Then the SPID for the query showed a status
>of rollback this stayed like this for over 3 hours. After the status
changed
>to rollback I noticed one of my 4 processors was running near 100% while
the
>others were idle. I was forced to kill the query and reboot the computer.
>After rebooting the same thing happened again.
>Any ideas what is causing this?
>I'm running SQL Server 2000
>
>.
>