May 20, 2009 at 7:51 am
First I'd run a backup ASAP.
Then if you can't lose data,
1) make sure you have full backups scheduled, copy these to a remote machine.
2) schedule log backups every 5-15 minutes, depending on what you want. copy these to remote disks
Note the "copy", not "back up to remote disks".
3) Tell them you might lose data and you need training or a consultant to check your work.
May 20, 2009 at 7:57 am
This is what I do. It all depends on the amount of data loss that your users can endure - all depends on them and your application.
1) Full backup of all user database on Sunday 12:00AM
2) Differential backups - Monday through Saturday 12:00AM
3) Log backups - Monday-Friday (we're not 24X7 so this works for ME) every hour between 6:00AM and 6:00PM
I run diffs on Saturday just in case someone is in doing work - rare but you never know. This works for MY environment. Talk to your department heads, determine the acceptable threshold for data loss (could be 15 minutes or a full day) and modify your schedule. You need to determine what works for your environment.
My last job had 2 distinct environments, payroll/accounting and manufacturing. Payroll/accoutning would accept one hour loss so log backups went every hour. Manufacturing would accept 15 minutes, thus their log backups went every 15 minutes. I knew their expectations, documented it and had them signoff so there was NO misunderstanding in the event of failure.
"so basicly i ruined my backup with that truncate?" - You didn't ruin your backup but you could only restore to a PIT within your last log backup that didn't truncate. Once you truncate, you must take a full bakup immediately after. Subsequent log backups after that will recreate the chain. Read Gail's blog. Lot's of good stuff.
-- You can't be late until you show up.
May 20, 2009 at 8:05 am
thanx a lot.
time to close for today
again thanx everyone!!
May 20, 2009 at 8:29 am
ip2host (5/20/2009)
ok what do you think i should do:
1) Read the article that I linked about managing transaction logs
2) Tell your boss that he's risking the business by having no DBA.
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
May 21, 2009 at 7:10 am
Running DBCC SHRINKFILE is not the same as truncating the log. The command to truncate the log is BACKUP LOG ... WITH TRUNCATE_ONLY, and you should not use it. There are some extreme situations where truncating the log may be the right thing to do, but you have a lot to learn before you're qualified to make that judgement.
Shrinking the log is not uncommon, although you are overdoing it. If the log regularly grows to 30 GB, shrinking it to 1 MB daily means that your system will have to pause periodically to grow the log. In other words, you're adding overhead precisely at the times your system is busiest.
You can probably keep the logs smaller by running the transaction log backups more frequently. Shrinking the log daily is pointless because it will just grow back. If your log is happy at 5-10 GB most of the time but occasionally grows to 30 GB because of some exceptional (but predictable) event, such as a weekly index rebuild or large data load, you could schedule a log shrink to occur after the big event but only shrink it down to something it can live with rather than 1 MB.
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply