| Author |
Topic |
|
shehbazmunshi
Starting Member
13 Posts |
 Posted - 2004-07-09 : 14:30:05
|
| I have a problem with log shipping that is the log file from the SourceDatabase grows to 750MB each day after a process is run daily...this log file needs to get truncated with the sql below:backup log DB1 with TRUNCATE_ONLYWAITFOR DELAY '00:01:00'DBCC SHRINKDATABASE (DB1, 1, TRUNCATEONLY)But with a logshipping in place, these commands breaks logshipping....Where do I need to do to truncate the log file and shrink it without breaking logshipping. The SQL above is a last step in a DTS package. Do I need to perform a full backup as shown below?..BACKUP DATABASE DB1TO DISK c:\backups\ DB1backup log DB1 with TRUNCATE_ONLYWAITFOR DELAY '00:01:00'DBCC SHRINKDATABASE (DB1, 1, TRUNCATEONLY) |
|
|
MuadDBA
628 Posts |
Posted - 2004-07-09 : 14:37:01
|
| We had a similar problem here when we would rebuild indexes...the transaction log grows very large, and it tries to ship all those transactions to the logshipped db, which creates a ton of work for it.We ended up setting up a plan that does something like this:Logshipping TLOG backups/restores stop at 3am.Index Rebuild occursTlog is truncatedFull DB backup is donelogshipping TLOG backups resumeRestore of DB is initiated on logshipped serverrestore of TLOG backups on logshipped server is resumed. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-07-09 : 15:27:52
|
| For us that would not be a viable solution as it would take just as long to transfer the backup file as it would to transfer the transaction log over. Our remote site is over 300 miles away. Log shipping should not break even if the transaction log is taking a while to transfer to the remote site. The restore job might fail until the file is copied over by the copy job, but log shipping is not broken.Tara |
|
|
|
MuadDBA
628 Posts |
Posted - 2004-07-09 : 15:38:07
|
| Well, the tlog backups themselves don't take long to transfer. The problem always seemed to be with the restoring of the tlogs having to do with index rebuilds...they would just hang, and hang, and hang...nothing happened and the standby server would get very very behind.It takes most of the morning to accomplish the full backup, restore, and log shipping restart, but it's better than the damn thing not working at all. |
|
|
|
shehbazmunshi
Starting Member
13 Posts |
Posted - 2004-07-09 : 15:39:26
|
Hi Tara, The problem I am having is that once I have executed the truncate and shrink commands...the Logshipping is broken...I have let this run overnight and the destination logshipped server is still out of sync the next morning...So I have to reinitialize the complete logshipping process again... quote:
Originally posted by tduggan
For us that would not be a viable solution as it would take just as long to transfer the backup file as it would to transfer the transaction log over. Our remote site is over 300 miles away. Log shipping should not break even if the transaction log is taking a while to transfer to the remote site. The restore job might fail until the file is copied over by the copy job, but log shipping is not broken.Tara
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-07-09 : 15:41:51
|
| Don't run the truncate. You should only backup the transaction log without truncate option for log shipping. Perhaps you need a bigger pipe between the two sites or need to consider compressing the file at the source before sending it over. We are able to send a 750MB file 300 miles away in less than 30 minutes. And that's without compressing it.Tara |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-07-09 : 15:43:49
|
quote:
Originally posted by crazyjoe
Well, the tlog backups themselves don't take long to transfer. The problem always seemed to be with the restoring of the tlogs having to do with index rebuilds...they would just hang, and hang, and hang...nothing happened and the standby server would get very very behind.It takes most of the morning to accomplish the full backup, restore, and log shipping restart, but it's better than the damn thing not working at all.
I have not had that experience. On one of our databases, the tlog gets over a gigabyte after DBCC DBREINDEX. It only takes a couple of minutes for the restore to complete. Log shipping typically is only behind by less than an hour when this huge file gets sent over. The time mostly is due to the file being copied.Tara |
|
|
|
shehbazmunshi
Starting Member
13 Posts |
Posted - 2004-07-09 : 15:44:55
|
What about running just this command:DBCC SHRINKDATABASE (DB1, 1, TRUNCATEONLY)quote:
Originally posted by tduggan
Don't run the truncate. You should only backup the transaction log without truncate option for log shipping. Perhaps you need a bigger pipe between the two sites or need to consider compressing the file at the source before sending it over. We are able to send a 750MB file 300 miles away in less than 30 minutes. And that's without compressing it.Tara
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-07-09 : 15:47:03
|
| Personally, I never run DBCC SHRINKDATABASE or DBCC SHRINKFILE unless I know for sure that the database will never need that space again. Disk space is cheap compared to the performance hit you get when the database needs to expand again since it needs the space again. So if the tlog grows to over 1GB, what's the problem with keeping it at that size? The only problem would be disk space. So add disk space to support it! Once your process runs again, it is going to need the space, so let it have it.Tara |
|
|
|
shehbazmunshi
Starting Member
13 Posts |
Posted - 2004-07-09 : 15:56:41
|
Hi Tara, Yes, that is true it does not matter about Disk Space...but in my case the tlog will grow each day by 750MB...so in five days it will grow close to 5GB...But eventually I will need to truncate the tlog file...which means...that If I apply the Truncate Commands then Logshipping will break on the standby and I then need to reinitilize the logshipping process once again...quote:
Originally posted by tduggan
Personally, I never run DBCC SHRINKDATABASE or DBCC SHRINKFILE unless I know for sure that the database will never need that space again. Disk space is cheap compared to the performance hit you get when the database needs to expand again since it needs the space again. So if the tlog grows to over 1GB, what's the problem with keeping it at that size? The only problem would be disk space. So add disk space to support it! Once your process runs again, it is going to need the space, so let it have it.Tara
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-07-09 : 16:01:47
|
quote:
but in my case the tlog will grow each day by 750MB...so in five days it will grow close to 5GB
What! You don't have your environment setup correctly then.You aren't regularly backing up the transaction log. How often do you run the transaction log backup job? We run ours every 15 minutes. As long as you run it regularly, it will stay a reasonable size. It will not keep accumulating like you mention.Tara |
|
|
|
shehbazmunshi
Starting Member
13 Posts |
Posted - 2004-07-09 : 16:09:53
|
Hi Tara, Through logshipping the tlog is backedup every 15 minutes...and these files are copied over to the Standby Server...quote:
Originally posted by tduggan
quote:
but in my case the tlog will grow each day by 750MB...so in five days it will grow close to 5GB
What! You don't have your environment setup correctly then.You aren't regularly backing up the transaction log. How often do you run the transaction log backup job? We run ours every 15 minutes. As long as you run it regularly, it will stay a reasonable size. It will not keep accumulating like you mention.Tara
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-07-09 : 16:12:33
|
quote:
Originally posted by shehbazmunshi
Hi Tara, Through logshipping the tlog is backedup every 15 minutes...and these files are copied over to the Standby Server...quote:
Originally posted by tduggan
quote:
but in my case the tlog will grow each day by 750MB...so in five days it will grow close to 5GB
What! You don't have your environment setup correctly then.You aren't regularly backing up the transaction log. How often do you run the transaction log backup job? We run ours every 15 minutes. As long as you run it regularly, it will stay a reasonable size. It will not keep accumulating like you mention.Tara
Then your tlog will NOT grow to 5GB. The transaction log backup backs up the transaction log AND also clears it out.Tara |
|
|
|
shehbazmunshi
Starting Member
13 Posts |
Posted - 2004-07-09 : 16:20:07
|
Hi Tara, So I do not have to create a seperate job to back up the transaction log...Logshipping is already doing that every 15 minutes..and thus the tlog should not grow to the size I had assumed..quote:
Originally posted by tduggan
quote:
Originally posted by shehbazmunshi
Hi Tara, Through logshipping the tlog is backedup every 15 minutes...and these files are copied over to the Standby Server...quote:
Originally posted by tduggan
quote:
but in my case the tlog will grow each day by 750MB...so in five days it will grow close to 5GB
What! You don't have your environment setup correctly then.You aren't regularly backing up the transaction log. How often do you run the transaction log backup job? We run ours every 15 minutes. As long as you run it regularly, it will stay a reasonable size. It will not keep accumulating like you mention.Tara
Then your tlog will NOT grow to 5GB. The transaction log backup backs up the transaction log AND also clears it out.Tara
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-07-09 : 16:26:56
|
quote:
Originally posted by shehbazmunshi
Hi Tara, So I do not have to create a seperate job to back up the transaction log...Logshipping is already doing that every 15 minutes..and thus the tlog should not grow to the size I had assumed..
Yes! And as a matter of fact, you shouldn't backup the transaction log outside of log shipping or it gets out of sync, like you've found. Log shipping should be the one doing all of the work. If you do for some reason backup the transaction log outside of log shipping, then you MUST apply it to the destination database. Log shipping will not handle this transaction log for you as it wasn't the one who did it.Tara |
|
|
|
shehbazmunshi
Starting Member
13 Posts |
Posted - 2004-07-09 : 16:33:15
|
Thanks Tara...you made my day...One more question that is it ok to place the command below as the last step in my DTS package that runs each morning at 2:00am and Ends at 4:00am...or do you recommend not to use it at all...DBCC SHRINKDATABASE (DB1, 1, TRUNCATEONLY)quote:
Originally posted by tduggan
quote:
Originally posted by shehbazmunshi
Hi Tara, So I do not have to create a seperate job to back up the transaction log...Logshipping is already doing that every 15 minutes..and thus the tlog should not grow to the size I had assumed..
Yes! And as a matter of fact, you shouldn't backup the transaction log outside of log shipping or it gets out of sync, like you've found. Log shipping should be the one doing all of the work. If you do for some reason backup the transaction log outside of log shipping, then you MUST apply it to the destination database. Log shipping will not handle this transaction log for you as it wasn't the one who did it.Tara
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-07-09 : 16:40:22
|
| I don't recommend it. Shrinks should never be scheduled in a production environment. If you have the disk space, let the database have it so that it can use the space again when your process runs again. Shrinks should only be run after hours and only if you know the database no longer needs it (maybe due to a data retention change). Shrinks should be performed manually by the DBA.Tara |
|
|
|
shehbazmunshi
Starting Member
13 Posts |
Posted - 2004-07-09 : 16:51:22
|
Thanks Tara...you have been very helpful...quote:
Originally posted by tduggan
I don't recommend it. Shrinks should never be scheduled in a production environment. If you have the disk space, let the database have it so that it can use the space again when your process runs again. Shrinks should only be run after hours and only if you know the database no longer needs it (maybe due to a data retention change). Shrinks should be performed manually by the DBA.Tara
|
|
|
|
|
LogShipping Issue with Database Log File