0

As a developer from time to time I will need to restore a local copy of our live database for testing purposes. I 'm running into disk space issues because the logs are bloody 15gb. I was told the transaction log is basically a log of the operations that were executed on that db and the db never actually updates. It simply reflects whats in the log.

If this is true then how can apply those changes mention in the log then truncate it so it's not 15gb

Ageis
  • 199
  • 6
  • 1
    You probably don't want to shrink the log file attached to the production database, because that would be counter-productive. But perhaps you could back up the production database, restore a copy somewhere that does have the space, shrink that copy's log file, and then take another backup. Or add disk space to dev - it's not like 15GB is huge here in 2015. – Aaron Bertrand Mar 27 '15 at 22:59
  • Also note that the word 'TRUNCATE' means to remove all the data from the table in SQL. – BuahahaXD Mar 27 '15 at 22:59
  • 1
    @ŁukaszKastelik Truncate also refers to log file where when log is truncated it becomes reusable – Shanky Mar 28 '15 at 04:08
  • Good point @Shanky – BuahahaXD Mar 28 '15 at 11:15

1 Answers1

0

In order to stop your transaction log growth you need to take regular transaction log backups (along with full database backups).

If it is a test database and you don't need to recover it to a point in time in case of a disaster you can switch the database recovery model to SIMPLE (you probably are using FULL recovery because it is default in SQL Server).

Once you run regular log backups you can use DBCC SHRINKFILE to reduce the file size.

Please read the following question: Why Does the Transaction Log Keep Growing or Run Out of Space? - it provides you with a really good understanding of the problem.

Community
  • 1
BuahahaXD
  • 1,405
  • 2
  • 14
  • 23