I have a database whose transaction log has swelled to 70 Gig
Overnight. Disk space free is only 30 gig therefore it won't let me
back it up. Any advice gratefully received.Barry (b.tucker@.voisins.com) writes:
> I have a database whose transaction log has swelled to 70 Gig
> Overnight. Disk space free is only 30 gig therefore it won't let me
> back it up. Any advice gratefully received.
You are backing up the transaction log to the same disk? Maybe you should
simply see your local vendor and add another disk, as that does not sound
like good practice to me.
In the meanwhile, there are two other options. The first is to backup the
log to another disk on the network. Once you have done that, use DBCC
SHRINKFILE to shrink the log to a reasonable size. That is, you should
specify a target size to SHRINKFILE.
The other alternative is set the database in simple recovery, run a
CHECKPOINT command, shrink the log as above, and then take a full
backup of the database. The last thing is very important, as by
truncating the log you break the log chain.
You should also investigate why the log exploded. If it was due to a
maintenance operation with DBCC DBREINDEX, you should consider running
that operation with BULK_LOGGED recovery mode.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi There,
May this solve your problem..
Step 1) deattach the Database.
Step 2) Phyiscally delete the Log file.
Step 3) Reattach the database.
Step 4) Take the backup.
With warm regards
Jatinder Singh|||[posted and mailed]
jsfromynr (jatinder.singh@.clovertechnologies.com) writes:
> May this solve your problem..
> Step 1) deattach the Database.
> Step 2) Phyiscally delete the Log file.
> Step 3) Reattach the database.
> Step 4) Take the backup.
DON'T DO THIS! AND NEVER GIVE THIS ADVICE! THIS IS VERY VERY DANGEROUS!
Never delete the log file! You would never delete the data file, would
you? So why delete the other half of the database?
The above way work, if you are lucky. You may also find that you cannot
access the database after this operation.
There are T-SQL commands to use to truncate and shrink the log. Use
these. Never manipulate the database files directly. And particularly
not if you don't understand what you are doing.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Barry" <b.tucker@.voisins.com> wrote in message
news:1136543236.726269.87380@.f14g2000cwb.googlegro ups.com...
> I have a database whose transaction log has swelled to 70 Gig
> Overnight. Disk space free is only 30 gig therefore it won't let me
> back it up. Any advice gratefully received.
To add to the advice: figure out why it swelled to 70 gig overnight.
That's a LOT.
