January 20, 2005 at 7:44 am
I just started a contract at a new location and they asked me to look into a sql 2000 issue they are having since they do not have a fulltime DBA on staff. They created a db, set it to full recovery mode but never backed up the tlog so they have a 50GB tlog and asked me to get it backed up to tape and under control. They do have regular backups of the db itself though.
Now, here are the catches:
1. They are using arcserve but did not purchase / install the sql agent so I can't back up this tlog while it's in use.
2. I can't back this thing up any place using SQL b/c of the shear size of it. The systems involved w/ this app are segmented off from prod environment and none of the other servers it can connect to have the free disk space for a 50GB backup.
The only other option I've found so far is to detach the db, rename the current tlog, then reattach it and let it create a new one. Then since the tlog is no longer in use, I can get a backup of it. I'd rather find a solution that wouldn't require an outage though (don't we all).
So, my questions are:
1. Do I have any other options?
2. If I don't have any other options and / or the detach / attach method is the best bet, can I still use that tlog for rollbacks or is it worthless at that point?
January 20, 2005 at 8:29 am
Hi,
you can use the following:
Backup LOG [Database] with truncate_only.
Use BOL to show the syntax of DBCC Shrink file statement, to minimize your log file.
Be shure, that you dackup your database after these steps.
I hope these steps help.
Regards
Michael
January 20, 2005 at 8:48 am
Thanks for the response Michael.
I found the procedure you mention here (as well as BOL): http://www.support.microsoft.com/kb/272318/EN-US/
But it says to first back up the log file which I can't do b/c I don't have the free space anywhere. It looks to me (not a sql person) that we'll lose our ability to do a point in time restore for anything before the backup I perform after running this command. Is that true?
January 20, 2005 at 9:37 am
Is your database in the full recovery mode? If not you won't be able to do a point in time restore anyhow. The other thing is that you should be doing regular log dumps to disk, this will prevent your logfile from growing out of control and also provides you with improved recoverability.
January 20, 2005 at 9:57 am
Yes, like I said in the original post, it is in full recovery mode and as I also mentioned they did not set it up for tlog backups so I understand why it has grown to this size, my question was what can can be done about it now that it has gotten to this point.
I read that: If the database is in full recovery mode (and a full backup has been taken) the entries prior to the MinLSN will only be freed by a transaction log backup (not full backup).
So, it looks like if they want to be able to do a PIT restore, we have to get that tlog backed up. But is there another option since they don't have the disk space to do so?
January 20, 2005 at 10:00 am
If you can live without the log you could
BACKUP LOG <DBNAME> WITH NO_LOG
GO
DBCC SHRINKFILE ('<LOG NAME FROM SYSFILES>', 128, TRUNCATEONLY)
GO
January 20, 2005 at 10:05 am
When you say "live w/o the log" you mean live w/o being able to do point in time rollbacks?
January 20, 2005 at 10:06 am
Yes, sorry, should have been more clear.
January 20, 2005 at 10:07 am
BTW I wouldn't use the Arcserve agent even if they purchased it.
January 20, 2005 at 10:11 am
If the database is running with a domain account and there is space on another server somewhere, you can setup a backup device to a unc path. You will then be able to backup the log to that backup device creating a file that can then be backed up.
It is slower because it goes over the network.
After running the backup you can then shrink the log file.
Hope that helps.
Tom
January 20, 2005 at 10:13 am
Unless you have a Gb backbone I wouldn't even try to dump a 50Gb translog across the network, you'll kill traffic for everyone who's trying to connect into the db.
January 20, 2005 at 10:19 am
If I were to detach the db, rename the tlog and reattach the db to let it create a new one, could I then do PIT restores using the renamed tlog or is it useless now?
January 20, 2005 at 10:32 am
I'm not sure that without backing up the log that you could do a point in time restore from an LDF.
How about backing up the entire database, truncating the log, backing up the database again, then turning on regular log dumps?
This will allow you to get the full backup just before and after the log is dumped reducing the potential of problems.
January 20, 2005 at 10:34 am
As much as hat would kill the network, I haven't seen any other solutions to the problem. If what was originally proposed is indeed the requirement then.
a: Enough free space on the server has to be created so the transaction log can be backed up.
b: Attach a seperate tape device to the machine and backup directly to that tape device. Knowing if you want to restore that you will need a tape device.
c: They buy the pluggin for their backup software.(I don't suggest this at all)
d: Backup to unc device, as bad as it is to the network.(this could be scheduled off hours.)
e: SqlLiteSpeed backup utility. Never used it. I've read about it in these forums. I assume $$$.
There are other options, but they involve changing the original specs from your post. Truncate log. etc.
Tom
January 20, 2005 at 2:07 pm
How big are the full backups? Generally will be the size of the used part of the data file + the used part of the log. If this is the case, do a full backup, send it to tape, delete it from disk, do a transaction log backup, send it to tape, delete it, then go about business as usual.
Steve
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply