|
|
Posted by Erland Sommarskog on 01/07/06 11:25
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/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|