IF we have two Servers installed with SQL Server 2008 instances, Server1 has three drives C, D and E with 100, 500 and 500 GB respectively and Server2 has three drives C, D and E with 100, 300 and 300 GB respectively. Now you have database1 on Server1 with data file 250 GB on D drive and Log file 350GB on E drive, its recovery model is full and replication has been setup on this database, what is the best way to take backup from Server1 and restore it to Server2?

Problem is server2 has 300GB space but on Server1 has 350GB log file.

  • 2
    Glib answer: Get you IT infrastructure bods to allocate another disc (or 2) to Server 2 from your SAN! Its not clear if you're in enterprise environment, but with DBs of that sort of size I'm guessing you might be. – Jon Egerton Feb 12 '14 at 9:53
  • 1
    TBH, even if you compress the log, you're still looking tight on the data file - even if you've got 50GB free, as a percentage of the overall data quantity that's too small to give much longevity. – Jon Egerton Feb 12 '14 at 10:03

It's a little bit strange to have log file bigger than data file. I guess you didn't take a backup for long time and that is a reason for such a huge log file.

First try with this link: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/633ea08c-942d-4703-bc03-f2befd35f612/log-file-size-keeps-growing-replication?forum=sqlreplication

The simplest solution is log backup, log backup should reduce the size of file.

If that fails you can try to truncate log file (see dbcc command). However, you should use this as a last resort since truncation of log file is "quick and dirty" solution.

  • 1
    log backup alone will not reduce the size of the log file. Backing up the log will allow you to manually shrink the log file – Cougar9000 Feb 12 '14 at 15:38

You need to reduce the size of the log file first. If Full recovery model is truly needed in your environment then you need to rethink how often you are doing transaction log backups in order to keep the log size manageable. You can do transaction log backups as often as every minute if need be. If you don't need point in time recovery, think about switching to a simple recovery model.

First things first, backup the transaction log and then shrink it down to a manageable size for server 2, you may have to repeat this step a few times in order for the log file to reduce in size. Once you have reduced the transaction log size to a reasonable number, take a fresh full backup and use that for your restore on server 2. I would definitely recommend a more frequent transaction log backup schedule on server 2 to keep the log file under your 300 GB hard-cap.

To see the transaction log backups run this script

SELECT user_name, database_name, backup_start_date, backup_finish_date, backup_size, 
FROM [msdb].[dbo].[backupset]
WHERE type = 'L' AND database_name = 'YOURDATABASE'

This will give you backup start, backup finish, size of the backup, and compressed size if applicable. If this field is blank and you are in full recovery model, whoops!


you can use log shipping. check this http://technet.microsoft.com/en-us/library/ms190640.aspx


Change the recovery model to SIMPLE temporarily. Shrink the transaction log as much as possible. Backup database using backup compression. move backup file to new server and restore to new server specifying where you want the files to live. Change the autogrow increment of the transaction log to 10 or 20 GB (how big your transaction log needs to be and how to optimize it is another topic). If you need FULL recovery model, now is the time to change it back. Take ANOTHER FULL BACKUP and start scheduling transaction log backups at the needed frequency. If you don't do FULL backup before transaction log backups (and keep them linked), then backing up the transaction log does NOT truncate it and it continues to GROW humungous. Remember to set the size of your database to allow for a year's growth.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.