Hi everyone,
There is so many blog posts and articles out there about this case and I have been reading, but I am still confused. I restored a database and it took more than 12+ hours to complete. I noticed that the transaction log was more than 500GB, so I am guessing the redo/undo phase was very long. Database is in full recovery mode.
If the transaction log is 500GB, but its has 490GB of free space. Does it mean that I have only 10GB of committed/uncommitted transactions?
If the transaction log is 500GB and it has no free space, does it mean that I have a lot of committed/uncommitted transactions in the log file? If yes, should I just take a backup of the transaction log to clear the committed/uncommitted transactions ? And then take a full backup.
Thank you
In essence yes that is correct, regular log backups should be made to ensure you can meet your companies RTO and RPO as well as keep sizes manageable.
If you have a 500GB log and its full back up the log first, then do a full backup.
If you have restored this to a new server the restore command has to create the database structure as is in the full backup, so it will still have to create a 500GB log file even thought if 490GB is empty it still creates the file at 500GB not 10GB. Again another reason to do regular log backups to stop the file size getting to much out of control.
That being said, you may want to profile the wait stats when restoring, your probably getting a lot of IO waits, I would be inclined to see if you have "Instant File Initialization" enabled and try the restore again with a completely new database see if that helps with reducing the 12 hour restore time.
If I recall correctly, IFI doesn't help with the transaction log, SQL is still going to create the file, then zero out every sector of that file (whereas IFI does help with the data files, which SQL does *NOT* zero out.)
So, a 1/2 terabyte file is going to take a long time to zero out, even on fast storage...
So it might be worthwhile to find out *why* the log is 500GB, then look into what can be done to get it, and keep it, smaller (if you have to do restores on a regular basis.) That might mean increasing the frequency of log backups, finding transactions (data loads, etc) that bloat the log and tuning those, or other changes.