November 14, 2007 at 2:57 am
Hi folks!
My MSSQL log file has grown to humongous 60 GB while the DB itself is only about some hundred MB in size! (I'm tired of this DBMS but anyway I'm forced to use it.) I'm doing a backup/restore procedure every day. Tomorrow it'll probably get stuck because of insufficient space on that hard drive.
On the target machine that does the restore I'm not concerned about any logs because it is used in read only mode.
Now how can I restore my DB disregarding the log? Please notice that it is no option to use the simple or bulk-logged recovery model.
Or is it that I have to do anything special when I backup the original DB?
Thanks a lot!
Dankwart
--------------------------------------------------------
[font="Comic Sans MS"]Correct me if I'm wrong[/font]
November 14, 2007 at 2:52 pm
Do you backup/truncate your transaction log at all ?
For production databases I typically do it every 1/2 hr.
Sounds like you need to backup/truncate your transaction log
shrink it, and then backup/restore.
or detach and attach mdf file to new server.
November 14, 2007 at 4:12 pm
look up create database and the with attach_log_rebuild_log option to move just the MDF.
Are you backing up the log? What's the recovery mode? There are databases that have valid t-logs larger than the data size.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
November 15, 2007 at 1:40 am
Thanks for the replies!
You're really backing up the transaction log twice an hour? What's the size of your log compared to the data part of that DB? What size should a log usually have? 50%?
AND
attach_log_rebuild_log sounds interesting. I'll check that.
The recovery model is "full" so shrinking is not effective (just without backup I guess???)
best regards,
Dankwart
--------------------------------------------------------
[font="Comic Sans MS"]Correct me if I'm wrong[/font]
November 15, 2007 at 7:01 am
Is your flog file full or dos it have reserved space in it?
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
November 15, 2007 at 8:07 am
yes, especially for OLTP systems where you want to be able to recover up to a reasonable point in time.
I typically do:
Full goes to it's own BU device
Diff and Logs go to same BU device
Full backup with init
Diff Backup with init
Log Backup
Log Backup
Log Backup
Log Backup
Diff Backup
Log Backup
Log Backup
...
until the next day's full, then it begins all over. I typically keep and Current day and Previous day on disk, and schedule tape backups to start after full db backup.
It's a simple solution, but it has saved me more than once.
November 15, 2007 at 8:11 am
Awkwardly I restricted the logfile not to grow beyond 10GB. Obviously I'm missing something there...
It was 65GB and the hard drive was running out of space.
The log backup managed to reduce the size to 10GB again. But that's also lots of wasted space as I'll never need the log on that particular target machine. The best practice would be to restore the DB without any log. Just the Data. In case of a crash I just restore again. No Data will be lost as I read only.
I'm still stunned by the 1/2h period of log backups....
Cheers,
Dankwart
--------------------------------------------------------
[font="Comic Sans MS"]Correct me if I'm wrong[/font]
November 15, 2007 at 10:13 am
Lets say you have a production trading database and you backup every night, and do only one log backup midday, sat at 1:00PM. If that db crashes at 12:40PM, you can only recover up until your last full backup and apply any differential and log backups, for which you would have none.
Now, take my scenario, I take a log backup every 1/2 hr, and the same thing happens, assuming a log backup occurred at 12:30PM, I can recover my DB up until as it was at 12:30PM, not only as it was after the full backup.
capisce ?
November 16, 2007 at 1:32 am
Certainly I got your point, Don Corle... äh Grasshopper. Except from this one: Whats with the "init"?
Full backup with init
Diff Backup with init
Log Backup
Log Backup
Log Backup
Log Backup
Diff Backup
Log Backup
Log Backup
...
And do all of the lines (or backup steps) above follow the previous line in a 30 minutes interval? Or did I get something wrong?
btw: it's capisci not capisce to use the correct italian word. Anyway it's not very polite to use it. I'm NOT offended though...
Thanks again,
Dankwart
--------------------------------------------------------
[font="Comic Sans MS"]Correct me if I'm wrong[/font]
November 16, 2007 at 2:01 am
pamarant (11/15/2007)
If that db crashes at 12:40PM, you can only recover up until your last full backup and apply any differential and log backups, for which you would have none.
Not necessarily. If the failure that caused the crash doesn't involve the loss of the log file then you may be able to back that up and restore to any point in time you wish. Backing up the log file should be the first thing you attempt in the event of a disaster. But I agree, it's better to back up frequently and have that extra confidence that you can recover to a recent point in time.
John
November 19, 2007 at 10:03 am
dankwart menor (11/16/2007)
Certainly I got your point, Don Corle... äh Grasshopper. Except from this one: Whats with the "init"?Full backup with init
Diff Backup with init
Log Backup
Log Backup
Log Backup
Log Backup
Diff Backup
Log Backup
Log Backup
...
And do all of the lines (or backup steps) above follow the previous line in a 30 minutes interval? Or did I get something wrong?
btw: it's capisci not capisce to use the correct italian word. Anyway it's not very polite to use it. I'm NOT offended though...
Thanks again,
Dankwart
Dank,
Just trying to have a bit o' fun, glad your not offended, no intention to do so.
init just overwrites, as opposed to appending. I usually create a backup device for full and Diff (which also gets log backups), init them during the full backup cycle, then append to the diff device throughout the day (diff and log backups).
-Paul
November 19, 2007 at 10:30 am
Just an FYI to the original poster, init does NOT overwrite backups that have not expired or the set name in the t-sql doesn't match the name on the backup media. So make sure if you are using Init that your backups have expired and the names are all the same.
Also, password protected backup media is not overwritten when using Init unless the password is provided.
November 20, 2007 at 10:09 am
Marvellous you guys.
Thanks a lot for the helpful answers!
I'm fine now. But as I know MS Products it might not last forever...
Later!
Dankwart
--------------------------------------------------------
[font="Comic Sans MS"]Correct me if I'm wrong[/font]
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply