?
Solved

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

Posted on 2008-10-28
5
Medium Priority
?
585 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 2000 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

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

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

VM backup deduplication is a method of reducing the amount of storage space needed to save VM backups. In most organizations, VMs contain many duplicate copies of data, such as VMs deployed from the same template, VMs with the same OS, or VMs that h…
Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
This tutorial will walk an individual through the steps necessary to enable the VMware\Hyper-V licensed feature of Backup Exec 2012. In addition, how to add a VMware server and configure a backup job. The first step is to acquire the necessary licen…
This tutorial will walk an individual through the process of configuring basic necessities in order to use the 2010 version of Data Protection Manager. These include storage, agents, and protection jobs. Launch Data Protection Manager from the deskt…

765 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