CAN SOMEONE EXPLAIN TLOG MECHANICS (GROWTH, SHRINKAGE) ???
-
I know this is an oft-discussed topic. My issue is that through all the MSDN articles, KB searchs, forum discussions, books, expert pontifications, etc. No one seems to really fully disclose how the Tlog actually works.
For instance, I'm running a FULL recovery model on my databases. So I expect to see mammoth log files.
But, depending on who you talk to, a backup, a backup with no log a DBCC SHRINKDATABASE or A DBCC SHRIKFILE will all reduce the size of the Tlog - BUT there always seems to be some kind of cautionary note that doing some or all of these will compromise my ability to recovery from a nasty situation.
Is there anyone that honestly knows the real deal mechanics of running databases in FULL recovery mode and how I can control the growth (impose a shrinkage) of a Tlog file without compromising my ability to recover from a disaster?
Thanks in advance for any insights !!
randyvol
-
Read "Transaction Log Backups" in SQL BOL for TL.
-
Uh - thanks. Gee I thought I made it clear that all that available stuff (including BOL) really doesn't make it clear.
That's OK though. I finally found someone who wanted to take the time to explain it clearly.
Thanks anyway !
-
That's how you generate 5000 postings...
Greetz,
Hans Brouwer -
The basic issue with the warnings, is that without Transaction Log backups you cannot restore to a 'point-in-time'.
Full and differential backups are restored as 'all or nothing'. You can't restore a Full backup and tell it to stop at a certain point. That's the same with differentials.
With Transaction log backups, you can restore them and say WITH STOPAT = and give a date and time that the restore is to finish at.
What's the use? Well if a user deletes information at 1 pm, you can use a transaction log backup to restore data up to 1259 (prior to the delete). Then you have lost very little data. Without transaction logs, you can end up losing a lot of data.
Consider this....at 1 pm today someone accidentally runs: DROP TABLE usertable.
How much data did you lose? If you are doing Transaction Log backups, you lost very little (only the data AFTER the DROP Table command was run) because you can restore right up to the time the command was run. Then it's like the command never ran at all. But if you only have a FULL backup or differential backup, then you've lost all the data since the last backup.
-SQLBill
-
There is a very good basic explanation of Transaction Logs and their importance here:
http://techrepublic.com.com/5100-6313-5173108.html?tag=e101
As SQLBill explained quite well, how and when you decide to back up your transaction logs is mainly a question of how much data you're willing to lose, but there are other considerations that depend a great deal on your environment. I hope this information is helpful.
My hovercraft is full of eels.
-
This is interesting stuff. And I'm curious about the following situation:
We have a primairy server and a standby server. Via transactional replication we keep the standby server uptodate. Suppose the outlined scenario happens to the primary server: a table is dropped. This will also happen on the standby server. So with the transactional log backups we restore everything to just before the drop.
Will every action with the restoring of the T-log be duplicated on the standby server? Or do we have to implement the same proces on the standby server as well?
Greetz,
Hans Brouwer -
I always thought BOL in that article was fairly straight forward personally. However, could you post what you were told for anyone else looking for a similar explination it will make it easier for them.
-
Well, since this has spawned some interesting discussion, let me pose a hypothesis based on the previous missive about someone dropping a table accidentally - of course, the following is what we do after we take the poor 'accident' prone person out, chop off the fingers from both hands and then shoot them in the parking lot as a warning to the rest of the staf to be more careful, but ah, I digress ....
Say I do differential backups every hour starting at 12 am keeping the most recent 4 hours, AND
I do a full backup of the database every 4 hours starting at 1AM, AND
I do a backup of the Tlog immediately after a full backup, AND
then I shrink the database, AND
then I shrink the TLOG by doing the following:
BACKUP LOG winston
WITH TRUNCATE_ONLY
USE winston
GO
DBCC SHRINKFILE(2)
GO
then I do one last FULL backkp of the database...
Now assuming I have the firepower to do that whilst operations continue happily on, according to everything I've read and all the discussions I've had with 'gurus' I'm pretty well covered correct? I mean, suppose someone deletes the table between 12 and 4 - I have the diffs up to the last full backup, I have the Tlogs up to the 4th hour (backups if I need em) and after truncating the log and shrinking it, I have another full backup of the database - so the only issue I'm dealing with here is time to recover (which I agree, depending on the size of the database is a huge deal).
So my remaining question to the group is this ...
Which does the backup of the log with trunacte_only and the shrinking of the Tlog file actually do...
a) just forces a wrap around of the Tlog so free space can be taken out of the Tlog and given back to the O/S
b) deletes transactions that have been committed to the database and frees space back to the O/S
Anyone know definitively?
TiA
-
According to BOL from the 'BACKUP' topic:
"NO_LOG | TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it and truncates the log. This option frees space. Specifying a backup device is unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY are synonyms.
After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes recorded in the log are not recoverable. For recovery purposes, immediately execute BACKUP DATABASE."
(bold added by me) Which most closely resembles your statement b) deletes committed transactions and frees space.
Therefore in your scenario, you could only recover to your last Transaction Log back up before the Truncate_only command was issued. For this reason, maybe you should consider truncating the log before your full database backup instead of after. Just my thoughts.
My hovercraft is full of eels.
-
I think we agree... that is why I specifically stated that immediately after the truncation, I take yet another FULL DB backup
-
Woops,Just put my glasses back on and saw that.
My hovercraft is full of eels.
-
Randyv,
Just to pose another possible scenario for you...
Set up jobs to do a once a day full backup, and a transaction log backup every 4 hours. (Eliminate the differentials altogether). Implement those jobs, and during a quiet time, do your dbcc shrinkfile on your log. Now, because you are taking log backups every 4 hours, your log should stay small, and not require more shrinks. You will be completely covered; you can recover to any point in time you wish (barring a catastrophic failure in which the current log is not available for an additional backup). At most, you will have to restore 1 full backup, and 4 transaction logs to recover. And you don't have to mess with differentials.
I have scenarios in which I take tlog backups every 15 minutes. In this case, differentials may be desirable because of the number of tlogs that might have to be restored. I have a job that scripts the tlog restores, so thats not an issue in my shop.
Steve
-
Randyv,
Our site does what Steve recommends, and it seems to work fine. If shrinking the log is important to you, you can turn on AutoShrink (search for AutoShrink in BOL; note that AutoShrink may cause performance problems of its own), or you can shrink the logs manually with Enterprise Manager or dbcc.
I don’t know if this will be important to you or not, but it came as a surprise to us—it depends on how large your databases are. If you delete the underlying NTFS database files (.mdf, .ndf, .ldf) *before* restoring a large database, the restore will take longer than if the NTFS files already exist. Apparently, SQL Server will spend quite a bit of time re-creating the NFTS files from scratch before it actually restores any data to them. On smaller databases, you may not see much difference in elapsed time.
Jon
-
OK thanks for all the help. I've got it nailed now.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply