Mirroring and Log File Growth

We currently have a 10GB database that is functioning properly mirrored. The only issue we have is that the log files grow very quickly during the early morning hours when a large number of transactions hit our DB from scheduled jobs. We have transaction log backup and shrink job that runs every 3 hours to backup the log and shrink the logical file to 10GB. In most cases this will shrink the log back down to it's desired size of 10GB. However, on some mornings it takes more repetitions of the log backup/shrink job to return the log size back to "normal". During this these times when it does not shrink the DB effectively, I get a report of the job process by email that states "Cannot shrink log file 2 (e_Log) because all logical log files are in use." I run a DBCC OPENTRAN command, and there are no open transactions. Eventually, the log file will return to it's normal size of 10GB through the log file backup job. I have a few questions though.

1. Is this normal behavior during moderate database use?
2. Does mirroring have any effect on the SIZE of the log file?
3. Is it normal for the size of the data file to be smaller than the size of the log file on a heavily used database?
4. Does anyone have any suggestions to better maintain the log file size?

Thank you,
Greg

Mirroring won't affect the log, unless the Principal can't connect to the mirror in which case transactions will queue in the log and may cause issues.

Why are you shrinking your log? It should be as big as it needs to be, i.e. large enough to hold the transactions from your scheduled jobs. Having it auto-grow then re-shinking it is a waste of time and resources (it will slow down your scheduled jobs while the log grows).

The reason you can't shrink it sometimes is because the log actually consists of Virtual Log Files (VLFs). If the active one of these is near the end of the log file, it won't be shrinkable until it moves back to the beginning of the log file due to transactions having happened.

Normally the log file is smaller than the data file, but there may be exceptions. The bottom line is, the log needs to be as big as is required by the size and volume of transactions using the database.

Ensure you have adequate log backups of a suitable frequency all the time, and especially during your batch processing.

million rows user table and growing

For our web site, we now have a million users and we have one user table
right now..
We would like to consider scaling this out as we grow to 5 -10 million users
and not have it in one table.
Every time a user logs on or changes profile,etc, we dont want to cause
contention or blocking on this table.. so whats the best way to go about
this ?
If i partition the table, whats the best way to go about partitioning it ?
Thanks> Every time a user logs on or changes profile,etc, we dont want to cause
> contention or blocking on this table.. so whats the best way to go about
> this ?
As long as you have appropriate indexing, I wouldn't expect performance or
concurrency problems regardless of table size. A few million rows really
isn't that large nowadays and, in my option, doesn't warrant partitioning.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Hassan" <hassan@.test.com> wrote in message
news:%23ytph$8XIHA.4196@.TK2MSFTNGP04.phx.gbl...
> For our web site, we now have a million users and we have one user table
> right now..
> We would like to consider scaling this out as we grow to 5 -10 million
> users and not have it in one table.
> Every time a user logs on or changes profile,etc, we dont want to cause
> contention or blocking on this table.. so whats the best way to go about
> this ?
> If i partition the table, whats the best way to go about partitioning it ?
> Thanks|||Perhaps your company should consider hiring an experienced DBA' One that
can guide you proactively instead of reactively and successfully get you to
the level you wish to achieve.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Hassan" <hassan@.test.com> wrote in message
news:%23ytph$8XIHA.4196@.TK2MSFTNGP04.phx.gbl...
> For our web site, we now have a million users and we have one user table
> right now..
> We would like to consider scaling this out as we grow to 5 -10 million
> users and not have it in one table.
> Every time a user logs on or changes profile,etc, we dont want to cause
> contention or blocking on this table.. so whats the best way to go about
> this ?
> If i partition the table, whats the best way to go about partitioning it ?
> Thanks|||Focus on indexing strategy , statistics , as 1 million rows is not that
much.
--
Jack Vamvas
___________________________________
Search IT jobs from multiple sources- http://www.ITjobfeed.com
"Hassan" <hassan@.test.com> wrote in message
news:%23ytph$8XIHA.4196@.TK2MSFTNGP04.phx.gbl...
> For our web site, we now have a million users and we have one user table
> right now..
> We would like to consider scaling this out as we grow to 5 -10 million
> users and not have it in one table.
> Every time a user logs on or changes profile,etc, we dont want to cause
> contention or blocking on this table.. so whats the best way to go about
> this ?
> If i partition the table, whats the best way to go about partitioning it ?
> Thanks