July 8, 2010 at 1:47 pm
Hi All,
I have couple of questions/confusions regarding transaction logs restores.
Here is my confusion:
We have here sql server 2005 logshipping environment with DB in Standby Mode in secondary server. Our servers are window server 2008 R2 64 bit with SQL SERVER 2005 Std 64 bit in both primary and secondary servers.(2.9 Xeon processor, 32 GB RAM, Primay server has SAN Storage & secondary server has local disks). The logshipping process is working fine in normal scenarios. Currently we have logshipping process running in following schedules:
backup job - every 3 minutes
copy job - every 5 minutes
restore job - every 12 hours( because of business requirements. I Know this kind of schedule is not approprite for logshipping implementation).
Currently Vendor is doing data conversion process which leads Transaction log backup with 300-800 MB/per file(which means total of 5-7GB in every 12 hrs). This 5-7GB log restores on secondary is taking flexible timings as follows:
5-7 GB - 34 minutes(This is the fastest ever in our environment)
5-7 GB - 7 hrs
5-7 GB - 12 hrs
My question is why same size of t-log restore is taking different timings ? Is there any difference in log restore operation if nature of conversion activity is different ? Is log restore will replays all the activities in secondary database ? My confusion is: while data conversion, if there is a activity like rebuild indexes, will this operation also executed on secondary while restoring t-log? Plz clarify me. about this transaction log restore operation.
I really appreciate you in advance.
Thanks
AKP
July 8, 2010 at 11:01 pm
>> My question is why same size of t-log restore is taking different timings ?
Good question. Taking your best case scenario, 7 GB in 34 minutes, that works out to ~ 3.5 MB/sec. Taking your worst case scenario, 7 GB in 12 hours, that works out to ~0.16 MB/sec. These are extremely slow numbers for restoring from local disks, even if they were old EIDE disks.
I would suggest checking if the local disks (where the database files are located, and also where the transaction log backup files are stored) are occupied with other tasks.
>> Is there any difference in log restore operation if nature of conversion activity is different ?
The transaction log restore would just redo every transaction that's contained in the backup file.
>> Is log restore will replays all the activities in secondary database ?
Yes.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
July 8, 2010 at 11:09 pm
I'm so sorry, I just noticed from the title of your post that your secondary server is in the standby mode. In that case, you should check if the conversion process is one long transaction. You can do this by checking the size of the undo file. Is it large?
If that's the case, you should restore the transaction logs with the NORECOVERY option. Once that's done, simply switch to NORECOVERY mode e.g.
RESTORE DATABASE mydb WITH STANDBY = '....'
This should speed up the restore process. See here for an example.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
July 11, 2010 at 9:16 am
Thanks for your kind reply. But still restore is taking more than 6 hrs if each t-log backup file size is 560KB(total: 240 files). When I saw the restore job history, each restore is taking around 20 minutes. I donot understand why 560KB restore is taking that much of time ?
We asked server team to review the disk performance, they are claiming there is no disk problems. But logshipping restore is taking long time. The tuf file is growing upto 3GB in some particular time.
Another change here we made is the SQL agent log history limit for each job and maxnumberof rows is 1000000. Is this will make any difference in logshipping ? any suggestions ???
IS this standby mode is culprit ????
Thanks
AKP
July 12, 2010 at 6:22 am
IS this standby mode is culprit ????
When you restore a transaction log using the NORECOVERY option, SQL Server does not need to analyze the transaction log and roll back any uncommitted transactions at the end of the restore process.
When you restore a transaction log using the STANDBY option, SQL Server has to undo any uncommitted transactions so that the data files contain only committed transactions. While it does this, it records whatever details necessary to be able to redo the uncommitted transactions. From what I can tell, beyond a certain limit, it simply records the entire contents of the pages that it had to undo to the undo file. Thus, if your transaction log contained uncommited transactions that affected rows contained in 1000 different pages, the entire contents of those 1000 pages will be stored in the undo file, even though not all the rows in those pages may be affected by the uncommitted transactions.
When you then restore the next transaction log, SQL Server has to first apply the contents of the undo file to the data files i.e. the 1000 pages above have to be first written to the data files. Only after that can SQL Server restore the transaction log, and if you are again using the STANDBY option, SQL Server has to again undo the changes made by any uncommitted trasactions, and record those operations to the undo file. Note that the uncommited transactions does not just come from the latest transaction log. It includes uncommitted transactions in all previous transaction logs that have been restored.
Say that in your conversion process, you made changes to 1000 rows in a table, and the tranaction is uncommitted. In your production database, if you viewed the contents of the table in read committed isolation level from a different connection, you will not see the changes to those rows. Actually, you'll be blocked from viewing the contents of the table. However, if you changed the isolation level to read uncommitted, you will see the changes. This is because the data files contain only the new values. If the transaction was now rolled back, SQL Server would record the rollback in the transaction log, essentially an update of the uncommited 'new' values with the 'old' values (obtained from the transaction log since it is still an active transaction).
Now in your standby database, if you viewed the contents of the table, you will not see the changes, regardless of which transaction isolation level you used. This is because the data pages contain only the old values. The new uncommitted values are stored in the undo file. So basically, there is more work to be performed when using the STANDBY option as compared to the NORECOVERY option.
You mentioned that the undo file grew to 3 GB in some cases. So on the next transaction log restore, your SQL Server instance would need to redo 3 GB worth of modifications/page writes, restore the transacton log (which was probably very fast), then scan through the entire transaction log (the .ldf file), undo all uncommitted transactions again, and generate the undo file containing details of those transactions.
You can log additional details re the backup/restore process to your SQL Server log using the following trace flags
DBCC TRACEON (3004, 3605, -1)
but do so at your own risk as I do not know all the side-effects of turning those options on.
Another change here we made is the SQL agent log history limit for each job and maxnumberof rows is 1000000.
I believe that only affects the number of records stored in the msdb..sysjobhistory table, and would have little or no impact on your restore throughput.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply