0

we have a audit table that will have couple of hundred thousand records added everyday. We do not want to keep increasing production db and log file size because of this one table. so we are thinking of moving this table to a separate database every night. A script will run every night that will move audit table records from production db to another db and then it will drop and recreate audit table in production db.

Our concern is if i drop and recreate then will it free up space in log file of production db ? or will it still hold space in db/log file ? because if it will hold space then we will have huge db or log files.

Can someone please give some idea on above. Another idea is we generate audit table directly into separate database but that requires some major programming change in program . Please suggest a better solution also if possible.

Thanks in advance

KPS Systems
  • 1
  • 1

2 Answers2

0

The log file still holds the full history even if you drop the table though.

As Kurt mentioned in the comment already, setting the recovery model to simple will simply solve the problem, but if you already have an existing database with full/bulk mode, you may need to backup the database regularly to flush the transaction log contents into backup file.

https://msdn.microsoft.com/en-us/library/ms179478.aspx

tsohr
  • 865
  • 2
  • 15
  • 25
0

Our concern is if i drop and recreate then will it free up space in log file of production db ? or will it still hold space in db/log file ?

For Log space:
Log Space will be reused unless until there is pending transaction which is stopping it from doing so.

For MDF/NDF space:
Dropping a table will free up space in database ,but that wont be released back to windows,it also will be reused like when you create a table again

TheGameiswar
  • 27,855
  • 8
  • 56
  • 94