September 14, 2015 at 7:59 am
Hi All,
I need some advice regarding log files.
We are moving our DB's to a VM box while we upgrade our physical current production server.
I need to move our DB's without losing any data.
1. How do I see if there is any uncommitted data in the log file?(If I restore a full backup and log backup I want to make sure I don't lose any data)
2. The only log backups were made twice a week. I have now changed it to every night because the log files are very big. When would be the best to to run a shrink file on my log file? (Should I do it initially before we go live, or should I leave it to go live, and check the log sizes and the end of the day and then shrink it just before the backups is made? I just want to reduce the log file sizes without affecting performance too much.)
Thanks guys
September 14, 2015 at 8:07 am
First things first.
Taking log backups once a day is a bad idea. It means you're exposed to 24 hours of data loss. If that's tolerable, then you could achieve it with full backups and simple recovery model with far less work.
1) Don't. Ensure that there isn't any unbacked up stuff. When you take the last log backup prior to moving the DB, take it WITH NORECOVERY. It takes the DB into the restoring state, allows no more transactions to occur.
2) Well, get your log backups running at a sensible frequency, then monitor log file usage. That'll tell you what file size you need.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 14, 2015 at 8:47 am
crazy_new (9/14/2015)
When would be the best to to run a shrink file on my log file? (Should I do it initially before we go live, or should I leave it to go live, and check the log sizes and the end of the day and then shrink it just before the backups is made?
If you do it later then the Restore on the new hardware will have to pre-create the Log file to the original size - as per the backup file which you restore from.
If you do it before you migrate then the Restore will create the new, smaller, size file.
Depending on how much you shrink the file by that might represent a useful time saving during migration.
That said we migrate in a way that typically has less than 5 minutes downtime as follows:
1. Take a full backup (or use an existing one), restore that on Destination with NORECOVERY
2. Then take (or use an existing) DIFF backup, restore that with NORECOVERY. make the DIFF backup relatively close to the actual switch-over time (to reduce the size of changes thereafter)
3. Just before switch over take a Log backup. Restore that (and any earlier Log files, perhaps generate by a schedule backup, made after the DIFF / FULL backup) in chronological order - still using NORECOVERY
4a. Once that has restore then you are ready for switch-over. Take a final log backup as Gail has described (we do it differently, setting the source database to READ_ONLY so it is relatively easy to re-mount it if we have a problem with the migration and need to back out).
4b. Restore that final Log backup (and any that were created by a scheduled backup in the meantime ...) using the RECOVERY option.
5. Database is now ready for use (if you set the original to READ ONLY the Destination database will have inherited that state, so change the Destination to READ WRITE).
We normally have less than 5 minutes downtime (i.e. the time to make, and restore, the final Log file) using this method. Plus we can restore the original FULL backup at an earlier time, to suit us. If the database is big copying over the Full Backup, and restoring it, can take "quite some time" 🙂
September 14, 2015 at 8:52 am
P.S. I can't see a justification to have Log Backups more than about 10 minutes apart. There is a small overhead in each file, and if you have to restore you have to restore lots of files (plenty of scripts out there which will mechanically generate the full backup script including all the log backup files, in order, which makes the process fairly painless - 144 small log files will restore in the same time as 1 huge 24-hour file 🙂 ), but other than that it reduces the Log File Size, and decreases the maximum data loss time down to just a few minutes.
P.S. If using SQL2008R2 or later, or an earlier Enterprise Version, consider using COMPRESS for the backups. IME it will cut the filesize by around 80% (which will save that much copy-to-destination time) and the backup and restore will each run in 40% less time because of the reduced I/O.
September 14, 2015 at 8:53 am
Kristen-173977 (9/14/2015)
(we do it differently, setting the source database to READ_ONLY so it is relatively easy to re-mount it if we have a problem with the migration and need to back out).
Easier?
ALTER DATABASE <db_name> SET READ_WRITE
vs
RESTORE DATABaSE <db_name> WITH RECOVERY
and my option allows for changes made on the new server to be transferred back to the old server (providing same version) if necessary/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 14, 2015 at 9:05 am
GilaMonster (9/14/2015)
my option allows for changes made on the new server to be transferred back to the old server (providing same version) if necessary/
That I didn't know and would be Well Handy 🙂
If the migration fails and need to back-out, back to the original server, having taken the final Log Backup with NORECOVERY presumably it is as simple as doing:
RESTORE OriginalDatabase WITH RECOVERY
to get it "live" again? (Plus the option to restore the additional LOG from Destination, as you have described :smooooth:)
Easier?
ALTER DATABASE <db_name> SET READ_WRITE
Dunno about easier on the restore, but ALTER suffers from needing to know what the original state of the Source database was - that might well have actually been READ ONLY. Or SINGLE USER / DBO ONLY ... all sorts to go wrong that I can see!
But on the Source I definitely think your way would be much easier - the ALTER DATABASE might well need IMMEDIATE if there are active connections or OFFLINE or somesuch, which seems to me to be a lot more "advanced" for a Junior DBA than telling them to use NORECOVERY.
(There again perhaps a Junior DBA should not be doing this flying-solo?!!)
I had never thought to use NORECOVERY in this way, other than as a Disaster Recovery tail-backup, but I've been and read the DOCs now 🙂 Thanks for that Gail.
September 14, 2015 at 9:08 am
Hi Guys,
Thanks for the great advise.
The only reason I am making the backups more frequently(Daily) is to manage the size. At the moment they were only making it twice a week and obviously that caused performance issues. How often would SQL backup the log file if I am in simple recovery model?
September 14, 2015 at 9:15 am
Never. The log can't be backed up in simple recovery because the log is marked reusable on checkpoint.
Please take a read through this http://www.sqlservercentral.com/articles/books/94938/ and have a chat with the owners of the database about data loss allowance and see whether they're really happy with losing 24 hours of data in a disaster.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 14, 2015 at 9:22 am
Thanks for the link and all the advise. I will be making more frequent log backups, but I will go through that ebook.
September 14, 2015 at 9:53 am
Windows allows creation of a VHD or image of disk which can be used to launch a VM , this will automatically move the required databases and files in your VM software, infact its the way VMs are launched in Azure. In addition you could let us know what kind of time frame you have to perform the migration. if its just a few minutes you might as well use log shipping or mirroring , if is a few hours you could even get away with just a full backup and restore provided you close all user connections before taking the backup.
Do you have a DR plan in place , could you use that ?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply