Help
in [Using MS SQL Server]
|
From: Barry on 6 Jan 2006 05:27 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.
From: Erland Sommarskog on 7 Jan 2006 04:25 Barry (b.tucker(a)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(a)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
From: jsfromynr on 7 Jan 2006 06:18 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
From: Erland Sommarskog on 7 Jan 2006 06:53 [posted and mailed] jsfromynr (jatinder.singh(a)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(a)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
From: Greg D. Moore (Strider) on 7 Jan 2006 14:51 "Barry" <b.tucker(a)voisins.com> wrote in message news:1136543236.726269.87380(a)f14g2000cwb.googlegroups.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. >
|
Pages: 1 Prev: Deadlock on Update using temp table Next: Joining tables in different databases |