SQL Server Bulk Delete Query

%3CLINGO-SUB%20id%3D%22lingo-sub-2461666%22%20slang%3D%22en-US%22%3ESQL%20Server%20Bulk%20Delete%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2461666%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20order%20to%20reclaim%20space%20from%20Audit%20table%20records%2C%20I%20deleted%20the%20rows%20from%20the%20Table%20and%20rebuilded%20Index.%20But%20later%20I%20came%20to%20know%20that%20these%20tables%20were%20not%20having%20any%20indexes.%20So%20after%20deletion%2C%20the%20storage%20space%3CSTRONG%3E%20got%20occupied%20more%20instead%20of%20reducing%3C%2FSTRONG%3E%20because%20of%20Tx%20logs%20%2B%20Indexes%20got%20increased.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20suggest%20me%20the%20correct%20action%20over%20here%20to%20reclaim%20the%20space.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2465678%22%20slang%3D%22en-US%22%3ERe%3A%20SQL%20Server%20Bulk%20Delete%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2465678%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1082104%22%20target%3D%22_blank%22%3E%40ashish35%3C%2FA%3E%26nbsp%3B%2C%20if%20you%20mean%20you%20have%20a%20heap%20table%20%3D%20no%20clustered%20index%2C%20then%20you%20can%20rebuild%20the%20table%20as%20it%20to%20defrag%20the%20table%20%26amp%3B%20get%20back%20some%20space%2C%20like%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3EALTER%20TABLE%20yourTableName%20WITH%20REBUILD%3B%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EThe%20table%20will%20be%20completley%20locked%20dureing%20the%20operation%2C%20so%20better%20schedule%20if%20for%20off-work%20hour.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Visitor

In order to reclaim space from Audit table records, I deleted the rows from the Table and rebuilded Index. But later I came to know that these tables were not having any indexes. So after deletion, the storage space got occupied more instead of reducing because of Tx logs + Indexes got increased.

 

Please suggest me the correct action over here to reclaim the space.

1 Reply

@ashish35 , if you mean you have a heap table = no clustered index, then you can rebuild the table as it to defrag the table & get back some space, like

 

ALTER TABLE yourTableName WITH REBUILD;

The table will be completley locked dureing the operation, so better schedule if for off-work hour.