Home / server / Questions / 195671
Accepted
ChrisHDog
Asked: 2010-10-28 22:19:44 +0800 CST

SQL Server 2008 Backup not increasing FreeSpace

  • 772

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'

backup log-files sql-server-2008

1 Answers

  1. Best Answer

    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

    SQL Server (2005/2008): Does full backup truncate the log in full recovery mode

    • 1