0

I need to delete some old logs from the database, however due to lack of space in the physical hard disk, there isn't enough space to sustain the growth of transaction log resulting from the delete activity.

My question is:

  1. If i were to write a cursor to delete the data, would this action still contribute to the transaction log growth from this activity? I think yes, but just to confirm.

  2. If #1 is not an option, then what else can I try? Physical disk space increase is not an option either.

Hope I've provided sufficient information to get some help. Please let me know if more is required.

Thanks in advance for any help received.

Nisha
  • 1
  • Is it an option to truncate the table entirely, or are you removing only some specific data? – Joachim Isaksson Mar 09 '16 at 08:55
  • The suggestions here should help: http://stackoverflow.com/a/24213640/1048425 – GarethD Mar 09 '16 at 09:00
  • @JoachimIsaksson I'm only trying to removing data from specific years, so 2010, 2011 etc. – Nisha Mar 10 '16 at 03:44
  • @GarethD is this a viable solution? Perform full backup of the entire database into a remote location. Assuming that you wish to retain the data from years 2012 to present day, export out ONLY all the data that you wish to retain from UGCALL. Truncate the UGCALL table. Re-import the data exported in step (2) into the UGCALL table and verify that the import is successful. Check the disk space usage once more to see if remaining space is sufficient. – Nisha Mar 10 '16 at 09:07

2 Answers2

0

Yes, deleting row-at-a-time by a cursor will cause the same problem.

As noted, only a TRUNCATE TABLE will delete all rows without logging them individually. It uses less log space, but still some.

cliffordheath
  • 2,536
  • 15
  • 16
  • A truncate statement can be rolled back and it is logged - [The Myth that DROP and TRUNCATE TABLE are Non-Logged](http://sqlperformance.com/2013/05/sql-performance/drop-truncate-log-myth) – GarethD Mar 09 '16 at 12:19
0

@GarethD is this a viable solution?

  1. Perform full backup of the entire database into a remote location – ensure that this backup copy can be restored successfully.
  2. Assuming that you wish to retain the data from years 2012 to present day, export out ONLY all the data that you wish to retain from UGCALL.
  3. Ensure that this export can be imported into an empty table successfully and the data is not corrupted.
  4. Truncate the UGCALL table. Check the disk space once truncate operation has been completed.
  5. Re-import the data exported in step (2) into the UGCALL table and verify that the import is successful.
  6. Check the disk space usage once more to see if remaining space is sufficient.
Nisha
  • 1