Discussion on Database Performance

• shubham.saxena67

  Default port

  Points: 1490

  More actions

  January 19, 2011 at 5:07 am

  #233066

  Hi Friends,

  I am having a database having 4 millians row in a table and when i want to delete it i will write a simple query like "delete from <table name>" this will generate so much log that's why my database .Ldf extension file occupy so much space on server that is exceeded from the required purchasing space on sql server.This will aborted automatically.

  Please send some suggession to me...

  Thanks!!

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  January 19, 2011 at 5:10 am

  #1275210

  Either delete in batches or use TRUNCATE TABLE instead of delete if you're wiping the entire table and it has no foreign key constraints referencing it.

  Eg of batch delete:

  DECLARE @Done BIT = 0;

  WHILE @Done = 0

  BEGIN

  DELETE TOP (10000) FROM SomeTable WHERE <SomeConditions>

  IF @@RowCount = 0

  SET @Done = 1

  -- Wait or checkpoint or log backup here, depending on recovery model & requirements

  END

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• baabhu

  SSCertifiable

  Points: 6205

  More actions

  January 21, 2011 at 12:03 am

  #1276394

  Use Truncate rather than delete.

  If you have Foreign key and the primary key the follow the below steps.

  Rename the table to old.

  Create a new table with the same struture and name.

  Remove the foriegn key constraints to the old table.

  Then drop the table.