• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 634
  • Last Modified:

can not truncate sql table, nor drop the index of a tab

I have a table in the SQL data base, current have just over 1 million rows.
1. I need to create a new index, but it runs and run forever., I have to abort it.
2.  but I can use bcp utility to dump all the rows to a text file.
3. I I can also use ado recordset to insert new row to the table
4. I can do everything with the table, other than I can not truncate table (I intend to create the index    after I truncate it. then use bcp to reload the data.

can someone help?
0
mshox1
Asked:
mshox1
5 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
>I intend to create the index    after I truncate it. then use bcp to reload the data.
i dont recommend that.
Do you think any other application is using the table at the same time you creat the index?
>but it runs and run forever., I have to abort it.

i may take some time depending on your index , datattype, free space etc...
0
 
JoeNuvoCommented:
if table is not very busy with insert.
you may create new table, create whatever index you want
transfer existing data into new table
and then rename it.

exec sp_rename 'MyTable','MyTable_Old'
exec sp_rename 'MyTable_New','MyTable'

Open in new window

0
 
qasim_mdCommented:
else try this :::

WHILE (SELECT COUNT(*) FROM table1) > 0
BEGIN
DELETE TOP(n) /*top n rows*/
FROM table1
END
GO
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
qasim_mdCommented:
Another Option:
1) create a new table with all the indexes and then do a
INSERT into new_table
select * from old_table;
2) Then rename the table to
sp_rename 'old_table' ,'old_table_old'
sp_rename 'new_table' ,'old_table
3) Verify if evertying is perfect and drop the table old_table_old
4) Shrink the database to free up the space occupied.
0
 
ThakurVinayCommented:
raname the table would be good option, also make a partition on the table for better performance.

my one cent.

Thanx.
Vinay
0
 
mshox1Author Commented:
to :gaism_md

Your suggestions of the above, is very interesting.
I copied below. and I have a questions about #4
==== you wrote the following ====
1) create a new table with all the indexes and then do a
INSERT into new_table
select * from old_table;
   
2) Then rename the table to
sp_rename 'old_table' ,'old_table_old'
sp_rename 'new_table' ,'old_table
3) Verify if evertying is perfect and drop the table old_table_old
4) Shrink the database to free up the space occupied.

questions is how do you "shrink the database t ofree up the sapce occupied"
what is the query or command to perform the above?   Please advise

-- I will try later and confirm if your approach works.

thanks

also:  Thakur Vinay:
are you tell me when you rename the table, it will make apartion o nthe table for better performance?
or do you have a seperate command or query I can do to make new partition on a given table?

Please clarify.  thanks
0
 
qasim_mdCommented:
To shrink a database :::
Go to SQL server management Studio > connect to the SQL server > right click on the database the table exists > select TASK > shrink > Files and then click on OK.
0
 
mshox1Author Commented:
excellent experts input. I have resolved my issues and learns some new tricks.  thank you again.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now