Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

OK so I've seen tons of information around about shrinking and free space and similar, but what I've not been able to see is just the basics for SQL Server 2008 on free space in the logfile.

I was under the impression (and this seems to be the case for previous versions of SQL Server) that when a full backup was done, then logs that had completed were freed up (i.e. if you had a 500MB log file with 10% free space before a full backup you might end up with 100MB log file with 70% free space after a full backup).

In SQL Server 2008 I have been checking the sizes (i've got about 10% free space), doing a backup (BACKUP DATABASE DBNAME TO DISK = 'C:\temp\DBNAME.bak') and then checking the sizes (no file size change, still about 10% free space) ... was expecting an increase in free space.

Am I doing my backup incorrect? Is this not the correct way to do this? What does one need to do to make this behave? Or does it just not behave this way anymore?

EDIT: Accepting Answer Below, Adding Script Items I Used

SELECT log_reuse_wait_desc, * FROM DBNAME.sys.databases WHERE name = 'DBNAME'
BACKUP LOG DBNAME TO DISK = 'c:\temp\DBNAMELog.bak'
BACKUP DATABASE DBNAME TO DISK = 'c:\temp\DBNAME.bak'
SELECT log_reuse_wait_desc, * FROM DBNAME.sys.databases WHERE name = 'DBNAME'

share|improve this question

1 Answer 1

up vote 1 down vote accepted

A backup does not automatically release the tx logs.

http://blog.sqlauthority.com/2006/12/30/sql-server-shrinking-truncate-log-file-log-full/

for a full backup sequence (kill the shrinkfile i oyou dont want that).

http://sqlserverpedia.com/blog/sql-server-backup-and-restore/backup-log-with-truncate_only-like-a-bear-trap/

To stay here (on server fault) that question was answered under

http://serverfault.com/questions/54958/sql-server-2005-2008-does-full-backup-truncate-the-log-in-full-recovery-mode

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.