Below are some T-SQL commands that we're running on a table with a few billion records. Most of the database's size is taken up by 5 tables like this. What is the quickest way to perform these steps without causing any issues? It takes more than an hour just to run the 1st command. Would it just be easier to drop the entire table and re-create it? Or is that not possible and safe with so much data? Can anyone think of any other ideas to speed this up? We are just trying to truncate the data, and then rebuild the table from scratch in our ETL process.
DROP INDEX [OF_IDX_ClusteredConcept] ON [dbo].[OBS_FACT] WITH ( ONLINE = OFF ) ALTER TABLE OBS_FACT DROP CONSTRAINT OBS_FACT_PK ALTER INDEX ALL ON OBS_FACT disable; -- add new data to OBS_FACT table via ETL process ALTER TABLE [dbo].[OBS_FACT] ADD CONSTRAINT [OBS_FACT_PK] PRIMARY KEY NONCLUSTERED ( [ENCOUNTER_NUM] ASC, [CONCEPT_CD] ASC, [PROVIDER_ID] ASC, [START_DATE] ASC, [MODIFIER_CD] ASC, [INSTANCE_NUM] ASC ) ON [PRIMARY] CREATE CLUSTERED INDEX [OF_IDX_ClusteredConcept] ON [dbo].[OBS_FACT] ( [CONCEPT_CD] ASC ); -- REBUILD indexes on OBSERVATION_FACT ALTER INDEX ALL ON OBS_FACT REBUILD
Often times, the drop index command causes this error in SQL Server Management Studio, if you try to re-launch SQL Server Management Studio in a different Window.
Lock request time out period exceeded (Microsoft SQL Server, Error: 1222)