Date parsed: 20/10/2007 01:21:24
Date: Sat, 20 Oct 2007 08:21:24 -0700

> Even an "empty" log has some overhead for things like checkpoint records.

OK, that makes some sense, but not completly.

My issue has less to do with the theory and more to do with practical
application.

Once a minute I have a program that looks at the transaction log usage, if
DBCC SQLPERF(logspace) says it's over a % used, then it backs the log up to
a .TRN file. Initially I was using 50%, but things like model which come as
a small file and are 51% used is firing the backup every minute. This has
two negitive effects: 1) an unused .5 MB database ends up with a 20 MB .TRN
file(s) daily and 2) the seperate process that copies backup files to tape
will frequently fail as it trys to access the file near the top of the
minute when the backup job is running.

The idea was to backup the transaction log every n minutes, or whenever
workload was about to auto-grow it and % used seemed the best way. Works
fine for any transaction log 5 MB and over, doesn't work very well for tlogs
2 MB and less.

My workaround has been to manuall increase the size of these .5/1 MB log
files to 5 MB. However, I find that to be sloppy and lazy. I should not have
to spend any disk resources to maintain log backups to unused databases.

Oh and this problem surfaced on a developers "play" db, not on model. Model
was just a good example that everyone would have.


>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "Jay" <spam@nospam.org> wrote in message
> news:OsCvl2sEIHA.4196@TK2MSFTNGP04.phx.gbl...
>> SQL Server 2000 & 2005
>>
>> I have noticed that an untouched model database (and others) with 512 KB
>> & 1 MB .ldf files usually show the transaction log (via the DBCC
>> sqlperf(logspace) command) as being about 50% used. Right now on my 2005
>> SQL Express server the values for model are:
>>
>> model 0.4921875 45.13889 0
>>
>> No amount of transaction log backups will clear this.
>>
>> Whats up with this?
>>
>

Date parsed: 20/10/2007 04:30:12
Date: Sat, 20 Oct 2007 11:30:12 -0700

Jay,

I just can't see your obsession with these small files. Why would you even
want a db to only be .5MB in size? If the amount of data you wish to store
is less than that I feel you have the wrong tool for the job. You should
always have plenty of free space in a db and log file so that a: you don't
have autogrowth issues and b: you have room for operations such as
reindexing and normal inserts, page splits etc. As for tran log backups it
is not a good idea to do these based on the space used. The main purpose of
tran log backups are to minimize data loss. You typically decide what is an
acceptable time that you can loose data and make the interval accordingly.
In a situation such as yours it may be simpler to always do full backups. If
the db is that small I don't know if you are saving much by issuing log
backups at odd intervals. In either case the size of the backup files is
proportional to the amount of data not the overall size of the files.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"Jay" <spam@nospam.org> wrote in message
news:%23kiMezyEIHA.280@TK2MSFTNGP03.phx.gbl...
>> Even an "empty" log has some overhead for things like checkpoint records.
>
> OK, that makes some sense, but not completly.
>
> My issue has less to do with the theory and more to do with practical
> application.
>
> Once a minute I have a program that looks at the transaction log usage, if
> DBCC SQLPERF(logspace) says it's over a % used, then it backs the log up
> to a .TRN file. Initially I was using 50%, but things like model which
> come as a small file and are 51% used is firing the backup every minute.
> This has two negitive effects: 1) an unused .5 MB database ends up with a
> 20 MB .TRN file(s) daily and 2) the seperate process that copies backup
> files to tape will frequently fail as it trys to access the file near the
> top of the minute when the backup job is running.
>
> The idea was to backup the transaction log every n minutes, or whenever
> workload was about to auto-grow it and % used seemed the best way. Works
> fine for any transaction log 5 MB and over, doesn't work very well for
> tlogs 2 MB and less.
>
> My workaround has been to manuall increase the size of these .5/1 MB log
> files to 5 MB. However, I find that to be sloppy and lazy. I should not
> have to spend any disk resources to maintain log backups to unused
> databases.
>
> Oh and this problem surfaced on a developers "play" db, not on model.
> Model was just a good example that everyone would have.
>
>
>>
>> --
>> Hope this helps.
>>
>> Dan Guzman
>> SQL Server MVP
>>
>> "Jay" <spam@nospam.org> wrote in message
>> news:OsCvl2sEIHA.4196@TK2MSFTNGP04.phx.gbl...
>>> SQL Server 2000 & 2005
>>>
>>> I have noticed that an untouched model database (and others) with 512 KB
>>> & 1 MB .ldf files usually show the transaction log (via the DBCC
>>> sqlperf(logspace) command) as being about 50% used. Right now on my 2005
>>> SQL Express server the values for model are:
>>>
>>> model 0.4921875 45.13889 0
>>>
>>> No amount of transaction log backups will clear this.
>>>
>>> Whats up with this?
>>>
>>
>
>

Date parsed: 20/10/2007 05:17:23
Date: Sat, 20 Oct 2007 12:17:23 -0700

> I just can't see your obsession with these small files. Why would you even
> want a db to only be .5MB in size?

Why would I want to increase the size of the model database? I do want
current backups of it. And if a developer wants a .5 MB database to play in,
then that's what they want, my job is to make sure I can recover it, not
pass judjment on the size they wanted.

> As for tran log backups it is not a good idea to do these based on the
> space used. The main purpose of tran log backups are to minimize data
> loss.

I do them on space used so that I can keep them from growing during peak
usage times. The problem is that the % calculation fires the log backup
every minute on the very small db's.

> You typically decide what is an acceptable time that you can loose data
> and make the interval accordingly.

In one form, or another, I'm dealing with financial information for the bulk
of my databases. So the answer I get is generally 0. However, you may have a
point here. The very small transaction logs simply can't be on a DB
important enough to fire the log backup to prevent .ldf file growth. Which
is the type of flag I'm looking for.

> In a situation such as yours it may be simpler to always do full backups.
> If the db is that small I don't know if you are saving much by issuing log
> backups at odd intervals.

I wrote the backup program because on a small server farm I have about
80-120 databases spread across 14 servers and I didn't want any database log
files filling up drives - anywhere. I also wanted to know I had current
backups of everything, without maintaining many programs, maintenance plans,
or SQL agent jobs.

> In either case the size of the backup files is proportional to the amount
> of data not the overall size of the files.

I think you misread something somewhere. The increase in the size of the log
file (not DB) was to keep the % below the threshold. Except for an image
copy of the .MDF/.LDF files, it would be silly for a backup to be the same
size as the physical file (I want to say no one wastes space like that, but
sadly, I know better).

Anyway, I think I have my solution.

Thanks,
Jay

>
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
>
> "Jay" <spam@nospam.org> wrote in message
> news:%23kiMezyEIHA.280@TK2MSFTNGP03.phx.gbl...
>>> Even an "empty" log has some overhead for things like checkpoint
>>> records.
>>
>> OK, that makes some sense, but not completly.
>>
>> My issue has less to do with the theory and more to do with practical
>> application.
>>
>> Once a minute I have a program that looks at the transaction log usage,
>> if DBCC SQLPERF(logspace) says it's over a % used, then it backs the log
>> up to a .TRN file. Initially I was using 50%, but things like model which
>> come as a small file and are 51% used is firing the backup every minute.
>> This has two negitive effects: 1) an unused .5 MB database ends up with a
>> 20 MB .TRN file(s) daily and 2) the seperate process that copies backup
>> files to tape will frequently fail as it trys to access the file near the
>> top of the minute when the backup job is running.
>>
>> The idea was to backup the transaction log every n minutes, or whenever
>> workload was about to auto-grow it and % used seemed the best way. Works
>> fine for any transaction log 5 MB and over, doesn't work very well for
>> tlogs 2 MB and less.
>>
>> My workaround has been to manuall increase the size of these .5/1 MB log
>> files to 5 MB. However, I find that to be sloppy and lazy. I should not
>> have to spend any disk resources to maintain log backups to unused
>> databases.
>>
>> Oh and this problem surfaced on a developers "play" db, not on model.
>> Model was just a good example that everyone would have.
>>
>>
>>>
>>> --
>>> Hope this helps.
>>>
>>> Dan Guzman
>>> SQL Server MVP
>>>
>>> "Jay" <spam@nospam.org> wrote in message
>>> news:OsCvl2sEIHA.4196@TK2MSFTNGP04.phx.gbl...
>>>> SQL Server 2000 & 2005
>>>>
>>>> I have noticed that an untouched model database (and others) with 512
>>>> KB & 1 MB .ldf files usually show the transaction log (via the DBCC
>>>> sqlperf(logspace) command) as being about 50% used. Right now on my
>>>> 2005 SQL Express server the values for model are:
>>>>
>>>> model 0.4921875 45.13889 0
>>>>
>>>> No amount of transaction log backups will clear this.
>>>>
>>>> Whats up with this?
>>>>
>>>
>>
>>
>

Date parsed: 20/10/2007 11:17:20
Date: Sat, 20 Oct 2007 18:17:20 -0700

> Why would you want to back the logs in a "play" database? What harm does
> it do the company if you have to rebuild one or more development
> databases? From what I read your program functions well in a "real"
> environment, so I begin to wonder what the fuss is about.

My job is not to pass judgment on each database. If someone thinks enough to
place a database on a system, it becomes my responsibility to safeguard it
along with the system it resides on - period. If you don't understand why,
then we will never agree on much.

The "fuss" as you refer to it, is making a general purpose program work for
the general case so that you truly have a backup system worthy of installing
and forgetting (except for the single daily status email).

Remember, the .5 MB DB backups were generating 10 MB/day of .TRN files and
the writes were locking the files, preventing them from being backed up to
tape and generating an error in that portion of the archive process.

Besides, I also thought it was a curious thing to have a Transaction log
that wouldn't go below 50% free - .5 MB, or not. I can see no one seems to
particulary care. Must be a Microsoft thing.

Jay

Date parsed: 21/10/2007 02:17:54
Date: Sun, 21 Oct 2007 09:17:54 -0700

No problem Rudd, I think I was feeling a little sensitive when I replied.

The policy of backing up everything is mine and mine alone. I also monitor
file growth and % used in the .mdf & .ndf files. To my surprise, in this
environment, management doesn't seem to care what I do, as long as the
database servers continue to function without causing issues.

Why I do this is simple enough. I have seen too many play db's that have
gigantic log files, cause they were never backed up and with 14 servers I'm
not likely to manually check them. Also, I dislike unnecessary file growth
because of the drive fragmentation is causes.

As to what to do, I've already decided to not fire the % full clause of the
program for log files under 5 MB (I'll add a command arg and just default
it), allowing them to auto-grow if they are really filling. In my reporting
program I'm allowing a 20 MB log file before it alerts me that there is a
disparity in the file sizes.

At some future point in time, I'll start to collect log file % used throught
the day to make a better decision on how big the .ldf's should be. In the
mean time, using a fixed size and a percent of the data file will keep them
from getting out of hand.

Thanks,
Jay

"Ruud de Koter" <nobody@internet.org> wrote in message
news:471b6dee$0$244$e4fe514c@news.xs4all.nl...
> Hi Jay,
>
> As I read it back I see my comment may have been a bit terse. It was not
> intended that way.
>
>>> Why would you want to back the logs in a "play" database? What harm does
>>> it do the company if you have to rebuild one or more development
>>> databases? From what I read your program functions well in a "real"
>>> environment, so I begin to wonder what the fuss is about.
>>
>> My job is not to pass judgment on each database. If someone thinks enough
>> to place a database on a system, it becomes my responsibility to
>> safeguard it along with the system it resides on - period. If you don't
>> understand why, then we will never agree on much.
>
> If there is a company policy to safeguard databases that may make sense.
> It may also make perfect sense to suggest to management that development
> play databases should be excluded from this policy, as the complications
> created by the backups of these small databases seem to be more important
> than the benefits (who ever restores a play database?). This would be
> equivalent to the suggestions by others in this group, of course.
> Fortunately I am living in an environment where this kind of feedback is
> appreciated, and often encouraged, but that may not be the case for you.
> If there is room for such feedback, I would certainly bring the issue to
> management attention.
>>
>> The "fuss" as you refer to it, is making a general purpose program work
>> for the general case so that you truly have a backup system worthy of
>> installing and forgetting (except for the single daily status email).
>
> Sorry for my use of the word fuss. Shouldn't have used it.
>>
>> Remember, the .5 MB DB backups were generating 10 MB/day of .TRN files
>> and the writes were locking the files, preventing them from being backed
>> up to tape and generating an error in that portion of the archive
>> process.
>>
>> Besides, I also thought it was a curious thing to have a Transaction log
>> that wouldn't go below 50% free - .5 MB, or not. I can see no one seems
>> to particulary care. Must be a Microsoft thing.
>
> That 's a strange thing indeed. Unfortunately that is not something I can
> provide intelligent comments on.
>
> Regards,
>
> Ruud.