BACKUP LOG questions

[aj]

SQL Server 2005 SP2

Using SSMS, I've just backed up my transaction log (which is around 314
megs) on a smallish database. I purposely generated a bunch of dummy
transactions to grow the trans log.

I scripted the action in SSMS, and it said this:

BACKUP LOG [test] TO DISK = N'C:\data\test. trn' WITH NOFORMAT, INIT,
NAME = N'test-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS
= 10
GO

However, when its done, my trans log is still 314 megs. Shouldn't the
BACKUP LOG command truncate the log?

I used DBCC LOGINFO to figure out where the VLF w/ status=2 is, and its
at the bottom of the many VLF's which I think explains why the trans log
is not being truncated.

Why is this happening? How do I actually truncate the log?
When/Why would I use DBCC shrinkfile rather than relying on BACKUP LOG
to decrease the size of the log file?

Thanks in advance for any help..

aj

[aj]

Some more info:

dbcc opentran says I have no open transactions

dbcc sqlperf(logspac e) says that I am using 4% of my log space for this
DB, w/ a log size of around 314 megs...

aj wrote:

SQL Server 2005 SP2

Using SSMS, I've just backed up my transaction log (which is around 314
megs) on a smallish database. I purposely generated a bunch of dummy
transactions to grow the trans log.

I scripted the action in SSMS, and it said this:

BACKUP LOG [test] TO DISK = N'C:\data\test. trn' WITH NOFORMAT, INIT,
NAME = N'test-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS
= 10
GO

However, when its done, my trans log is still 314 megs. Shouldn't the
BACKUP LOG command truncate the log?

I used DBCC LOGINFO to figure out where the VLF w/ status=2 is, and its
at the bottom of the many VLF's which I think explains why the trans log
is not being truncated.

Why is this happening? How do I actually truncate the log?
When/Why would I use DBCC shrinkfile rather than relying on BACKUP LOG
to decrease the size of the log file?

Thanks in advance for any help..

aj

[Roy Harvey (SQL Server MVP)]

The log file size does not change when it is backed up. The data
inside the log is truncated internally, and that space in the file is
now free. You can shrink the log (DBCC SHRINKFILE) but before you do
that you have to read the article:

http://www.karaszi.com/sqlserver/info_dont_shrink.asp

Roy Harvey
Beacon Falls, CT

On Tue, 08 Jul 2008 12:27:06 -0400, aj <ro****@mcdonal ds.comwrote:

>SQL Server 2005 SP2

Using SSMS, I've just backed up my transaction log (which is around 314
megs) on a smallish database. I purposely generated a bunch of dummy
transactions to grow the trans log.

I scripted the action in SSMS, and it said this:

BACKUP LOG [test] TO DISK = N'C:\data\test. trn' WITH NOFORMAT, INIT,
NAME = N'test-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS
= 10
GO

However, when its done, my trans log is still 314 megs. Shouldn't the
BACKUP LOG command truncate the log?

I used DBCC LOGINFO to figure out where the VLF w/ status=2 is, and its
at the bottom of the many VLF's which I think explains why the trans log
is not being truncated.

Why is this happening? How do I actually truncate the log?
When/Why would I use DBCC shrinkfile rather than relying on BACKUP LOG
to decrease the size of the log file?

Thanks in advance for any help..

aj

[aj]

Thanks for clarifying this, Roy. I believe I grok it now, and for me,
the key was to understanding the difference between allocated space and
used space. I was mixing the 2 up.

DBCC SHRINKFILE is used to shrink the /allocated/ space, assuming that
there is available non-used (free) space in the file. The allocated
space is the total size of the file on the file system.

A BACKUP LOG is used to back up, and then truncate the /used/ space that
is within the allocated space. This does not change the size of the
allocated space - it only affects how much of that allocated space is
actually used.

A trans log can be 100 megs in size, and you can be using only a few
megs of actual space in it. You can check this w/
DBCC SQLPERF(LOGSPAC E).

thanks

aj
Roy Harvey (SQL Server MVP) wrote:

The log file size does not change when it is backed up. The data
inside the log is truncated internally, and that space in the file is
now free. You can shrink the log (DBCC SHRINKFILE) but before you do
that you have to read the article:

http://www.karaszi.com/sqlserver/info_dont_shrink.asp

Roy Harvey
Beacon Falls, CT

On Tue, 08 Jul 2008 12:27:06 -0400, aj <ro****@mcdonal ds.comwrote:

>SQL Server 2005 SP2

Using SSMS, I've just backed up my transaction log (which is around 314
megs) on a smallish database. I purposely generated a bunch of dummy
transactions to grow the trans log.

I scripted the action in SSMS, and it said this:

BACKUP LOG [test] TO DISK = N'C:\data\test. trn' WITH NOFORMAT, INIT,
NAME = N'test-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS
= 10
GO

However, when its done, my trans log is still 314 megs. Shouldn't the
BACKUP LOG command truncate the log?

I used DBCC LOGINFO to figure out where the VLF w/ status=2 is, and its
at the bottom of the many VLF's which I think explains why the trans log
is not being truncated.

Why is this happening? How do I actually truncate the log?
When/Why would I use DBCC shrinkfile rather than relying on BACKUP LOG
to decrease the size of the log file?

Thanks in advance for any help..

aj