renaming tables

[revilord]

I have a table that, through a trigger on another table, is being writen to constantly by users, I want to rename it and then recreate the table because it is getting too big. When I say big I mean millions of records.
Background
There was a maintenenance script writen to be run nightly to keep this table under control but fun enough it was removed from the maintenance script. Running the script now will cause the maintenenance script to run for days.
Question
This is my process
EXEC sp_rename BIG_TABLE, BIG_TABLE_BACKUP
select * BIG_TABLE from BIG_TABLE_BACKUP where 1=2
Do I need to stop users from accessing BIG_TABLE before I rename it.

 

[bborissov]

You could add all records to your Backup table and truncate real data table:

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.Tables WHERE Table_Name = ' BIG_TABLE_BACKUP'
   BEGIN
       INSERT INTO BIG_TABLE_BACKUP
       SELECT * FROM BIG_TABLE
   END
ELSE
   BEGIN
      SELECT * INTO BIG_TABLE_BACKUP FROM BIG_TABLE
   END
ENDIF
IF @@ERROR = 0
   TRUNCATE TABLE BIG_TABLE

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[ptheriault]

If a user has a lock on BIG_TABLE it will block your rename till the lock is released. Also, If a user tries to access BIG_TABLE before you can create the new table and load the data they will get an error. I would do this type of maint off hours if that is possible.

- Paul
- Database performance looks fine, it must be the Network!

 

[SQLSister]

Note how reallly important that check for @error is in Boris' script. Truncating is faster than deleting becasue it is not recoverable from the transaction log. Do not use truncate until you have checked to make sure the backup worked!

Questions about posting. See faq183-874