March 11, 2009 at 2:23 pm
I have 2 tables and a view that I cannot delete. The first table (file 1) is pretty big....1.4 million rows. The second one (file 2) has 39000 rows. The view is based on both these files plus another (file 3), which is fine. If i try opening either table, SQL returns a Time Out Expired error after a few minutes. If i try opening the view, I get the same problem. If I trying deleting the view, it just hangs there without anything happening.
Is there a way i can get rid of these tables so I can start over?
Thanks
March 11, 2009 at 2:28 pm
You can issue a drop command on a table or view (or any other database object). That'll actually get rid of it. Is that what you want? Or do you mean you just want to clean them out and delete all the data in them? If so, then what you want is "truncate table".
Be careful with these, as there is no "undo" command for either one, other than recovering from a backup made prior to the drop/truncate.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 11, 2009 at 2:36 pm
At this point, I'd do either. We can't ever run a select count (*) statement against these tables.
How would I issue the drop statement? Through query analyzer?
March 11, 2009 at 2:37 pm
Also, what is the syntax for the truncate command?
March 11, 2009 at 2:41 pm
SQL 2000:
Query Analyzer:
drop table YourTableName
Enterprise Manager:
Right-click table, select Delete
SQL 2005
Management Studio:
Right-click table, select Delete
drop table YourTableName
That'll get rid of the table completely.
Management Studio/Query Analyzer:
truncate table YourTableName
That'll empty the table out but leave the table structure in the database.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 11, 2009 at 2:53 pm
I tried these and nothing is happening. It's almost like there is some sort of corruption.
Are there any other options for deleting these?
March 11, 2009 at 4:29 pm
My guess, your delete generated a bunch of log and is still running (cascading deletes ?)
DBCC opentran (executed in the correct db) may give some answers.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 12, 2009 at 7:47 am
No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Where else could I check?
March 12, 2009 at 8:06 am
Question: I have a view that is linked to the two files I cannot drop. But I cannot drop that view either. Would I need to drop the view first, then the tables?
March 12, 2009 at 8:30 am
The view must be dropped first if it has been created using schemabinding
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 12, 2009 at 8:59 am
Having the same problem with the view.
:angry:
There has got to be a way.....
March 12, 2009 at 9:11 am
What is the exact error it shows when drop view/table fails?
March 12, 2009 at 9:18 am
use sp_depends and see if there are any dependencies on this table/view? There might be some parent-child relationship for this table to other tables.
You have to drop view first if it is created with schemabinding option.
March 12, 2009 at 9:20 am
When I right click on the view, select delete, then click on Drop All, the screen just freezes and will just sit there. I have to kill it in task manager. If I go through Query Analyzer, type in:
drop view filename
It runs and continues to run until I click stop. No errors are ever produced. If I try just doing a simple select statement:
Select * from filename or select count (*) from filename
Nothing happens.
March 12, 2009 at 9:21 am
Keep in mind you cannot drop objects that are actually in use !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply