MS SQL Server - Transaction Logs : strange activity

Author

Transaction Logs : strange activity

Matt

2006-03-09, 8:23 pm

Hi there,

I have a database that processes lots of small transactions (something
like 3 million transactions on a daily basis, about a thousand every 6
seconds). If I backed up both log and database every day, the tran log
should be getting up to about a gigabyte in size, according to my rough
estimates.

If I backup the database and let the application run, pretty soon the
tran log starts to fill and grow in size. To shrink it back down again,
I use the following commands

DBCC SHRINKFILE(myDatabas
e_Log,2)
BACKUP LOG myDatabase WITH TRUNCATE_ONLY

which seems to do the trick. However, if I let the application run
again, something extremely weird happens.

As the app runs, I keep polling DBCC SQLPERF ( LOGSPACE ) with Query
Analyser which tells me how big the tran log is and how much of it is
used. It reports that the tran log is 0.9 MB and the usage 50%. The
usage percent increases swiftly until it hits 70% and then it goes back
down to 50% and the cycle repeats once more. The size on disk of the
tran log does not increase and there's no degradation in performance of
the application. It seems to run in this state quite happily.

As soon as I start backing up the database, the tran log soon fills and
causes the application to be unstable.

Can anyone offer any explanation about what's happening here? I would
really love to know what is going on because it's making my head bleed.

Thanks,

Matt

Tibor Karaszi

2006-03-10, 3:23 am

The behavior was introduced in 7.0. When you do:

> BACKUP LOG myDatabase WITH TRUNCATE_ONLY

SQL Server know that you have broken the chain of log backups, and if you try to do a proper log
backups after this, it would be useless. So, SQL Server is "smart" and auto-truncates the log in
this case. As soon as you have done a database backup, you have your baseline for the forthcoming
log backups and you are out of "log autotruncate mode".

Aside: http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/

"Matt" <matt.jones@studio10.co.uk> wrote in message
news:1141932538.989765.237230@v46g2000cwv.googlegroups.com...
> Hi there,
>
> I have a database that processes lots of small transactions (something
> like 3 million transactions on a daily basis, about a thousand every 6
> seconds). If I backed up both log and database every day, the tran log
> should be getting up to about a gigabyte in size, according to my rough
> estimates.
>
> If I backup the database and let the application run, pretty soon the
> tran log starts to fill and grow in size. To shrink it back down again,
> I use the following commands
>
> DBCC SHRINKFILE(myDatabas
e_Log,2)
> BACKUP LOG myDatabase WITH TRUNCATE_ONLY
>
> which seems to do the trick. However, if I let the application run
> again, something extremely weird happens.
>
> As the app runs, I keep polling DBCC SQLPERF ( LOGSPACE ) with Query
> Analyser which tells me how big the tran log is and how much of it is
> used. It reports that the tran log is 0.9 MB and the usage 50%. The
> usage percent increases swiftly until it hits 70% and then it goes back
> down to 50% and the cycle repeats once more. The size on disk of the
> tran log does not increase and there's no degradation in performance of
> the application. It seems to run in this state quite happily.
>
> As soon as I start backing up the database, the tran log soon fills and
> causes the application to be unstable.
>
> Can anyone offer any explanation about what's happening here? I would
> really love to know what is going on because it's making my head bleed.
>
> Thanks,
>
> Matt
>

Sponsored Links