Solved

SQL 2005 - Run out of disk space when run the backup for transaction log

Posted on 2008-10-28
5
583 Views
Last Modified: 2013-12-01
Hi,

I failed to run the backup for transaction log in Drive F because temporary files is created in Drive E and makes the server is running out of disk space.

E drive : 3 GB free space avaialble / total : 27 GB (Original database : 500 mb (.mdb), 17 GB (.ldf))
F drive : 6 GB free space avaialble/ total : 27 GB

Any idea to backup the transaction log so that i'm able to shrink the log file?

Thanks for reply.
0
Comment
Question by:ian_khoo_sc
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22820560
create the temporary files in drive F?
0
 
LVL 20

Expert Comment

by:Marten Rune
ID: 22821454
Do you really need to back them up. If the data in the database is ok then all you need to do is truncate them to gain space.

If so, I would use this approach:

The last step of the code sets the database back to full recovery mode. I would look into if this is what you want. It looks like you dont do any logbackups. If you dont need point in time restore, then go for simple backup, wich is a full backup every night for instance.

/Marten

USE [master]
GO
ALTER DATABASE [DBNAME] SET RECOVERY SIMPLE WITH NO_WAIT
GO
ALTER DATABASE [DBNAME] SET RECOVERY SIMPLE 
GO
 
USE [DBNAME]
GO
DBCC SHRINKFILE (DBNAME_log)
GO
 
USE [master]
GO
ALTER DATABASE [DBNAME] SET RECOVERY FULL WITH NO_WAIT
GO
ALTER DATABASE [DBNAME] SET RECOVERY FULL 
GO
 
 
--BACKUP DATABASE [DBNAME]
use master
BACKUP DATABASE [DBNAME] 
 TO DISK = '\\networkshare\DBNAME.bak'
   WITH FORMAT;
GO

Open in new window

0
 

Author Comment

by:ian_khoo_sc
ID: 22827576
Hi,

Yes, I do not need the transaction log. NB : I'm assuming that all the committed transactions had been written to the .mdb file. Any risk of losing data?

Will the SQL server uses significant amount of disk space when switching from FULL to SIMPLE Recovery?

How long it will take to switch since my transaction log file is very big (17GB)?
0
 
LVL 20

Accepted Solution

by:
Marten Rune earned 500 total points
ID: 22829211
The database will remain online during the switch, what happends is that each transaction when committed is flushed from the log while in simple mode. It shouldn't take long. It will not require any space.

Shrink logfile to a suitable size, i e possibly the size of the database.

Now schedule a transaction backup every 2n'd hour or so. This will prevent the log from growing the way it has. Since it's flushed after every logbackup. The file remains in the same size, but it's empty, thus giving sql server a lot of space to store transactions.

After a fourtknight or so you can check the size again. From now on it shouldn't grow dramatically. The only time it will if you bulk load the database, but you have a benchmark of whats normal.

Its not normal with a db of 500mb and a transaction log of 17GB. Its ruffly 34 times the database! For clarification to do a full restore with this design you need the latest full backup, and every log backup done after this. Read up from BOL (Books On Line) if you're unsecure about this. It's crucial that you understand, in a case of failure you won't have the time to learn, you should now by then what steps are needed to do a point in time restore.

regards Marten

/Marten
0
 

Author Closing Comment

by:ian_khoo_sc
ID: 31510682
Thanks for your help.

We are in this situation because we were new to SQL server and unknowingly omitted the transaction log backup. Going forward, we should be OK.  
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

By default, Carbonite Server Backup manages your encryption key for you using Advanced Encryption Standard (AES) 128-bit encryption. If you choose to manage your private encryption key, your backups will be encrypted using AES 256-bit encryption.
How to update Firmware and Bios in Dell Equalogic PS6000 Arrays and Hard Disks firmware update.
This tutorial will walk an individual through configuring a drive on a Windows Server 2008 to perform shadow copies in order to quickly recover deleted files and folders. Click on Start and then select Computer to view the available drives on the se…
This tutorial will walk an individual through setting the global and backup job media overwrite and protection periods in Backup Exec 2012. Log onto the Backup Exec Central Administration Server. Examine the services. If all or most of them are stop…

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question