CODEWITHSUNDEEP
sql-servertransaction-log
Brian Tran
Brian Tran

Reputation: 125

Transaction log file size carry over when performing a backup on another SQL Server

Our production server has a large .ldf file (300 gb) associated with a .mdf file. On our DEV server, we restore the DEV database using the production backup from time to time. As our DEV server should not have many transactions associated with it, but the .ldf file is 300 gb also. I shrank the .ldf file to 50 gigs in size. Will the DEV .ldf file grow again after a restore from a production backup?

Upvotes: 0

Views: 954

Answers (3)

Alexander Volok
Alexander Volok

Reputation: 5940

Will the DEV .ldf file grow again after a restore from a production backup?

Yes, in case if your production database log file still has such size - 300 GB your database on dev will again have such log file.

We do not know what is the baseline of your live database.

However, if your production database has no regular heavy DML operations that result in long-running transactions, consider reducing the size of the log file on production to smaller size, for instance to 50 GB.

Also, this reduction will significantly reduce restore time, because LDF files are to be zeroed internally before RESTORE starts writing actual data. It means that SQL Server firstly has to create 300 GB file and write zeroes in it. In contrast with log files, data files can benefit from "instant file initialization" and such zeroing can be skipped if SQL Server instance service account correctly configured to have enough permissions.

Otherwise, every time when you do a restore to a dev environment such maintenance task to be done:

USE yourDev
ALTER DATABASE yourDev SET RECOVERY SIMPLE
-- assumption: only one ldf in db
DBCC SHRINKFILE(2, 1024)

Upvotes: 1

Greg
Greg

Reputation: 4055

When you restore a database backup, the size of the database and log files will be whatever file size it was from the original database. So if the file size in prod was 300 GB, it will be 300 GB when you restore it to another database/server. It's common to do two things after restoring a prod database backup to a dev environment - shrink the log file size, and set recovery model to SIMPLE (whether you do either will depend on your needs/requirements).

Since you reduced the file size to 50 GB in size, it can grow to exceed 50 GB for many reasons, but most notably:

  • You create a transaction that generates an enormous amount of log
  • You have FULL recovery model, and you generate a lot of log, and never do log backups
  • You enable some kind of replication feature (replication, CDC, etc.) and never run logreader agent.

Upvotes: 4

benjamin moskovits
benjamin moskovits

Reputation: 5458

You probably have either a background task that is running and running or a lot of open (long running) transactions that are preventing the log being backed up and shrunk. I bet that the same will happen on your new machine. You need to figure out what is preventing the transaction log from shrinking. You should not have to shrink it yourself.

Upvotes: 0

Related Questions