June 25, 2010 at 9:24 am
yessen (6/25/2010)
I switched to simple logging like 5 days ago to prevent files growing too much because my boss was happy if we could not restore at particular point of time. He said it is ok because we have everyday backups.
So he's happy to lose up to a full day of data in the case of a problem?
But if I want to do log backup:
BACKUP LOG database
TO DISK = N'c:\backup.bak'
Some articles say, it is impossible when simple logging.
Correct, you can only do log backups in full or bulk logged recovery. If the boss is OK with simple (and the data loss risks associated) then there's no need to worry about log backups at all. SQL will auto truncate the log on a regular basis.
If that potential loss of a full day of data is not acceptable, that's when you need full (or bulk logged) recovery and regular log backups.
Please read through this - Managing Transaction Logs[/url]
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
June 25, 2010 at 8:17 pm
You guys are amazing!
I just switched to FULL logging.
Backed up database fully.
Shrinked log file using DBCC SHRINKFILE.
checked VLFs, now only 4.
Set auto-growth to 5gb.
backed up log file, it is only 81kb the size of .bak
checked how large is my log file now - 1mb only.
restarted SQL server to see how long it takes to "recover". It took less than a second 🙂
Thanks a lot!!!
The next question is to maintain the small size of the log file. How to schedule the regular log backups using SQL management studio? I want it to be done once a week.
June 26, 2010 at 2:58 am
yessen (6/25/2010)
I just switched to FULL logging.Backed up database fully.
Shrinked log file using DBCC SHRINKFILE.
checked VLFs, now only 4.
Set auto-growth to 5gb.
backed up log file, it is only 81kb the size of .bak
checked how large is my log file now - 1mb only.
Now grow it back to a reasonable size, a size that it regularly gets to in normal operation. If you've set autogrow to 5GB, I would suggest that you manually grow the log to around 10 or 20GB and monitor it.
The size of the log file is not a factor in the time for recovery. Only the size of the active portion of the log within the file contributes.
The next question is to maintain the small size of the log file. How to schedule the regular log backups using SQL management studio? I want it to be done once a week.
Once a week? Sure about that? That means that the log will get big enough to hold a full week of transactions. The usual frequency for log backups is somewhere between every 15 minutes and every 2 hours. Depends on the activity of the database and the maximum allowable data loss. Backing up the log less often than the database is totally pointless. You may as well set the DB into simple if you're going to manage it that way.
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
June 28, 2010 at 6:45 am
Once a week? Sure about that? That means that the log will get big enough to hold a full week of transactions. The usual frequency for log backups is somewhere between every 15 minutes and every 2 hours. Depends on the activity of the database and the maximum allowable data loss. Backing up the log less often than the database is totally pointless. You may as well set the DB into simple if you're going to manage it that way.
When I did my log backup it created 81kb .bak file from the original 80gb .ldf file.
With full logging, our database .ldf file grows about 3gb/day.
Can you please explain to me how do these log backups work? If I will do backups every 15min-2hours, I will get these .bak files, which means at most 12 .bak files per day (2 hours interval). What do I do with them? Keep them for like a week and discard them?
If my database fails, how do I use them to restore to particular point in time?
Sorry for such novice questions.
June 28, 2010 at 6:56 am
There a number[/url] of articles[/url] right here on SSC that can help you out. You just need to use the Search window over there in the upper right to track down this kind of information.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 28, 2010 at 9:41 am
Grant Fritchey (6/28/2010)
There a number[/url] of articles[/url] right here on SSC that can help you out. You just need to use the Search window over there in the upper right to track down this kind of information.
Thank you for writing and sharing that article. It is very useful. I will do my log backups every 2 hours and store them in the same directory, so I can choose which point in time I need to recover if I need to.
However, that article did not answer more fundamental question, where in SQL management studio I can set it to perform regular log backups every 2 hours?
Also, can I delete these backups after like a week or two?
Thanks
June 28, 2010 at 1:17 pm
yessen (6/28/2010)
Grant Fritchey (6/28/2010)
There a number[/url] of articles[/url] right here on SSC that can help you out. You just need to use the Search window over there in the upper right to track down this kind of information.Thank you for writing and sharing that article. It is very useful. I will do my log backups every 2 hours and store them in the same directory, so I can choose which point in time I need to recover if I need to.
However, that article did not answer more fundamental question, where in SQL management studio I can set it to perform regular log backups every 2 hours?
Also, can I delete these backups after like a week or two?
Thanks
I would start by using the Maintenance Plans to set up log backups. It's quick, easy, pretty powerful and get you where you want to go. However, if you really want to do it manually, SQL Agent is the built in scheduling mechanism in SQL Server. Look that up in the Books Online and here on SSC.
As to how long you keep log backups... it depends. I throw them out immediately after a successful full backup. Different places do it different ways.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 21, 2010 at 7:59 am
Sorry to bother again,
I am shipping my transaction logs every day at 10pm. The size of the log file was constantly around 20gb and the transaction log backups everyday were around 10-15gb. One day over the weekend, our server was down, so it did not ship the log files for two days and after that things started to get little bit out of control, the size of the log file grew to 57gb.
I tried to reduce the size by doing "Release unused space" and with dbcc shrinkfile, but all it did it resulted in a little shrinkage and percentage used to be 100%, basically it released previously non-used 9% (before it was 91% used).
How can I reduce the log file without braking anything in the log file and keeping it at the same size?
July 21, 2010 at 10:40 am
Using this for reference:
- run DBCC LOGINFO to see the status of the VLFs in the transaction log
- back up the transaction log
- run DBCC LOGINFO again, and see if a significant portion has been marked as unused
- run DBCC SHRINKFILE
- if the file is still significantly larger than your target size, run DBCC LOGINFO again to check if the active portion has been moved to the beginning of the file. If so, back up the log again, and run DBCC SHRINKFILE again
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 22, 2010 at 7:11 am
GilaMonster (6/22/2010)
yessen (6/22/2010)
But everytime SQL Server needs to be restarted it goes into the same "recovery mode". I want to prevent this from happening and I can't because I have no idea why it happens first of all.It will. It's called restart recovery and it's required to ensure that the databases are all transactionally consistent. It shouldn't take long. If it does then either your log is badly configured (too many VLFs) or there's IO bottleneck problems on the log and/or data drive, or there's something else hindering checkpoints or recovery.
Have you messed with the recovery interval setting? (sp_configure)
Gail, thanks for that tidbit that many VLFs can cause a delayed restart. Didn't realize it's the number, more than the size, of VLFs that causes this.
To the OP: here's a good link I found with more info. on this subject:
Rich
August 13, 2010 at 7:55 am
Something that I thought about:
My database ships transaction log once a day and that keeps it from growing too large, which is great!
However, I set it to delete all the shipped log files that are older than 7 days.
So, lets say something bad happens, I tell my SQL Management Studio to restore point in time (full logging) and point to the directory where I save those log files and it will do the job.
Since I delete week older material, does that mean I can only restore only within last week?
Please correct me If I am wrong.
P.S If I ship transaction log more than once a day, during database operation, will this affect the work? or does it have to be done when no one is using it? I heard people saying shipped every 15 minutes 😀
August 13, 2010 at 8:40 am
Log shipping once a day means that you can lose up to a full day of data in the case of a server failure. Is that acceptable to your users? If not, drop that log backup frequency down, a lot. I'd put it to once an hour to start with (max data loss = 1 hour). If they want less than that, drop the log backup frequency even lower.
I've heard of log backups every 5 minutes (on a rather important database)
How often do you do full backups? I hope it's more than once a week since you need a full backup and an entire, unbroken chain of log backups to restore to point-in-time.
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
August 15, 2010 at 9:37 am
However, I set it to delete all the shipped log files that are older than 7 days.
So, lets say something bad happens, I tell my SQL Management Studio to restore point in time (full logging) and point to the directory where I save those log files and it will do the job.
Since I delete week older material, does that mean I can only restore only within last week?
Please correct me If I am wrong.
If you have a week's worth of t-log backups, then you can only do point-in-time recovery to a point during that week. If for some reason you needed to restore to a point 2 weeks ago, you can still restore one of the older, full backups. (You are keeping full backups for longer than 1 week on tape or something, aren't you?)
I think most people find that the odds of needing a point-in-time recovery goes down the further back in time you're looking at.
HTH,
Rich
August 16, 2010 at 3:44 am
Normally you can delete all the log files once a full or diff back has been done.... as i our env we've a staging area where all the backups get copied and then a image of that drive is taken on the tapes.... and we keep around 6 months of the data on the tapes and recent full with subsequent diff & log backups....
August 16, 2010 at 12:10 pm
I try to keep T-logs going back two full database backups; just in case the most recent full backup file gets corrupted/lost/overwritten/whatever/the tape degrades/whatever.
Viewing 15 posts - 31 through 45 (of 57 total)
You must be logged in to reply to this topic. Login to reply