Solved

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

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

How to update Firmware and Bios in Dell Equalogic PS6000 Arrays and Hard Disks firmware update.
Microservice architecture adoption brings many advantages, but can add intricacy. Selecting the right orchestration tool is most important for business specific needs.
This tutorial will walk an individual through locating and launching the BEUtility application to properly change the service account username and\or password in situation where it may be necessary or where the password has been inadvertently change…
This tutorial will show how to configure a new Backup Exec 2012 server and move an existing database to that server with the use of the BEUtility. Install Backup Exec 2012 on the new server and apply all of the latest hotfixes and service packs. The…

744 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now