SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Transaction log not truncating when backing up		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

masterdineen
Aged Yak Warrior

United Kingdom
518 Posts
Posted - 01/07/2013 :  11:32:26  Show Profile  Reply with Quote
Hello there.

I am trying to backup a transaction log that is about 2 gig in size but only a couple of MBs are being truncated.

what could be causing the log not to truncate.

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3328 Posts
Posted - 01/07/2013 :  11:46:38  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
What do you mean by truncated?
Are you expecting the log backup to shrink the log?
All a backup does is release log space to be reused.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

masterdineen
Aged Yak Warrior

United Kingdom
518 Posts
Posted - 01/07/2013 :  11:56:48  Show Profile  Reply with Quote
that's it. when I back the log that is 2 gig. it doesn't release any space

the log file still shows 2 gig
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7173 Posts
Posted - 01/07/2013 :  13:55:58  Show Profile  Reply with Quote
Check log_reuse_wait and log_reuse_wait_desc in sys.databases. It will tell why it didn't.
Go to Top of Page

masterdineen
Aged Yak Warrior

United Kingdom
518 Posts
Posted - 01/08/2013 :  03:48:17  Show Profile  Reply with Quote
when i select * from sys.databases i get the following message

An error occurred while executing batch. Error message is: The directory name is invalid.
Go to Top of Page

masterdineen
Aged Yak Warrior

United Kingdom
518 Posts
Posted - 01/08/2013 :  03:56:52  Show Profile  Reply with Quote
this is due to low disk space
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3328 Posts
Posted - 01/08/2013 :  04:01:49  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
Is that 2GB in size or 2GB used? Is there free space in the log - if not it will grow in size, if there is free space it will be used and is not a problem - you can reduce the size by a shrink.



==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

masterdineen
Aged Yak Warrior

United Kingdom
518 Posts
Posted - 01/08/2013 :  04:04:06  Show Profile  Reply with Quote
i thought shrinking was meant to be avoided?
Go to Top of Page

masterdineen
Aged Yak Warrior

United Kingdom
518 Posts
Posted - 01/08/2013 :  04:08:29  Show Profile  Reply with Quote
thank you i have done that and i have just freed up 2 gig.

why does that happen. why dont the unallocated space become released when the log is backed up
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000