Solved

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

Posted on 2008-10-28
5
582 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Create your own, high-performance VM backup appliance by installing NAKIVO Backup & Replication directly onto a Synology NAS!
Workplace bullying has increased with the use of email and social media. Retain evidence of this with email archiving to protect your employees.
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 show how to configure a single USB drive with a separate folder for each day of the week. This will allow each of the backups to be kept separate preventing the previous day’s backup from being overwritten. The USB drive must be s…

730 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