Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 588
  • Last Modified:

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

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
ian_khoo_sc
Asked:
ian_khoo_sc
  • 2
  • 2
1 Solution
 
momi_sabagCommented:
create the temporary files in drive F?
0
 
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
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
 
ian_khoo_scAuthor Commented:
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
 
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
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
 
ian_khoo_scAuthor Commented:
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 does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now