My whole diskspace is used by a .LDF file?

As a newbie I work with MS SQL 2005. On this server I suddently ran
out of diskspace and found out that a .LDF file uses 55GB+ ?!? The
database is just 2-3GB.
I think I have learned that I need to backup the database and this
logfile on a daily basis to free the diskspace (or at least to let it
be reused).
But a this moment....
I don't have enough diskspace to backup the logfile and futher more I
don't have tape or DVD or anything else, that could be usefull for
this purpose.
Can anyone give me a workaround, so I can shrink this logfile?
After that I promise to do a daily backup :-)
Thanks
What recovery mode are you in? Do you ever perform backups?
<finn@.boendergaard-jakobsen.dk> wrote in message
news:1171377134.744446.116340@.q2g2000cwa.googlegro ups.com...
> As a newbie I work with MS SQL 2005. On this server I suddently ran
> out of diskspace and found out that a .LDF file uses 55GB+ ?!? The
> database is just 2-3GB.
> I think I have learned that I need to backup the database and this
> logfile on a daily basis to free the diskspace (or at least to let it
> be reused).
> But a this moment....
> I don't have enough diskspace to backup the logfile and futher more I
> don't have tape or DVD or anything else, that could be usefull for
> this purpose.
> Can anyone give me a workaround, so I can shrink this logfile?
> After that I promise to do a daily backup :-)
> Thanks
>
|||On Feb 13, 8:32 am, f...@.boendergaard-jakobsen.dk wrote:
> As a newbie I work with MS SQL 2005. On this server I suddently ran
> out of diskspace and found out that a .LDF file uses 55GB+ ?!? The
> database is just 2-3GB.
> I think I have learned that I need to backup the database and this
> logfile on a daily basis to free the diskspace (or at least to let it
> be reused).
> But a this moment....
> I don't have enough diskspace to backup the logfile and futher more I
> don't have tape or DVD or anything else, that could be usefull for
> this purpose.
> Can anyone give me a workaround, so I can shrink this logfile?
> After that I promise to do a daily backup :-)
> Thanks
To fix your immediate problem, do the following:
1. Run this command - BACKUP LOG dbname WITH TRUNCATE_ONLY
2. DBCC SHRINKFILE logfilename
3. Do a full database backup - BACKUP DATABASE dbname TO
DISK=backupfilename
Now you need to decide what to do to prevent this from happening
again. Backing up the log file once per day is sort of pointless.
The intent behind transaction log backups is to allow you to restore
to a specific point in time, reducing your chances of data loss.
Backing the log up once per day gives you the potential to lose an
entire day's worth of data - if that risk is acceptable to you, just
forget the log file backups, put the database in Simple mode and do
daily full backups. I would suggest doing log backups AT LEAST once
per hour, we do them every five minutes here. Consider the following
scenario:
- nightly full backup at 9:00pm, no log backups
- database fails at 3:00pm
- only option for recovery is to restore the 9:00pm backup, losing all
work done today
Compare to this scenario:
- nightly full backup at 9:00pm
- log backup every 15 minutes
- database fails at 3:00pm
- recovery of all data up to 2:45pm is possible, by restoring the full
backup (WITH NORECOVERY) followed by each subsequent log file backup.
Which would you prefer?