Transaction logs size not increasing

[enigma]

Just wondering if you could help me on this one. I'm not sure if my
transaction logs are behaving oddly or what. I've successfuly managed
to shrink my transaction logs from 7GB down to 1MB and now I find it
strange that the log file doesn't seem to increase its size. The
timestamp of the logfile is updating as well. But the size of the file
is constant. I haven't configured my database to do auto-shrink so Im
really confused why it hasn't changed its size for more than a month
now.
Hope I'm not losing any data here.
Kindly advise.

[Ray Higdon]

Did you somehow get switched to simple recovery mode? You can find this
out by typing sp_helpdb 'dbname' Have you been doing inserts, updates or
deletes?

Ray Higdon MCSE, MCDBA, CCNA

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Dan Guzman]

To add to Ray's response, in the SIMPLE recovery model the transaction
log needs to be sized large enough to accommodate data changes since the
oldest uncommitted transaction. Committed data are automatically
removed from the log in the SIMPLE model and your only recovery option
is to restore from database backup. Any changes made since the backup
will be lost.

In the FULL or BULK_LOGGED model, the log needs to be large enough to
contain all logged modifications since your last transaction log backup.
Committed data are only removed from the log during a transaction log
backup. Consequently, you'll need to backup your log periodically or
the log will grow indefinitely. The backups can be applied to a
restored database to reduce the amount of data lost following a
recovery.

--
Hope this helps.

Dan Guzman
SQL Server MVP

-----------------------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index....partmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------

"enigma" <jo***@pure-commerce.com> wrote in message
news:7a**************************@posting.google.c om...

Just wondering if you could help me on this one. I'm not sure if my
transaction logs are behaving oddly or what. I've successfuly managed
to shrink my transaction logs from 7GB down to 1MB and now I find it
strange that the log file doesn't seem to increase its size. The
timestamp of the logfile is updating as well. But the size of the file
is constant. I haven't configured my database to do auto-shrink so Im
really confused why it hasn't changed its size for more than a month
now.
Hope I'm not losing any data here.
Kindly advise.

[enigma]

Guys, thanks for your response. I think I wil be needing more
assistance to get a head start. Also, I missed to mention that Im
using SQL Server 7 and the database Im working with is consistently
receiving transactions. Most of these are inserts and updates but we
don't do much of deletions in our database.
I've done a dbcc loginfo just to check if it really does log the
transactions happening in the database.

FileId FileSize StartOffset FSeqNo Status Parity CreateTime
------ -------- ----------- ------ ------ ------
-----------------------
2 253952 8192 5675 2 64 2003-09-02
09:24:12.190
2 253952 262144 5674 2 128 2003-09-02
08:59:19.623
2 253952 516096 5623 0 128 2003-09-01
14:59:16.070

As you can see the DB's transaction log is currently divided into 3
small virtual logs. And 2 of which are indicated to be active. Now,
correct me if I wrong but am I right to say that my transaction log is
working fine?

Also, I did execute a dbcc sqlperf a couple of times to monitor the
perfomance of the transaction log. The latest stats below -- the
previous check I did has an approximate 67% log space used. The stats
did change!

Database Name Log Size (MB) Log Space Used (%) Status
------------- -------------- ------------------ ------
Gateway_01 0.7265625 49.663979 0

Thus, it made me think that everything is working fine. Having said
that, I still have this weird feeling that there is something wrong
with the way my transaction log is behaving. Could be that my log size
is too small that it can't accommodate data changes. Hence, I dont get
to see any log size expansion.

Another thing, the virtual logs are just showing timestamp from last 2
days. So what happened to the rest of the logs prior to these dates. I
didn't truncate nor back up the log files. I'm afraid it does log some
activities but somehow drops the rest of it. Hope you guys could shed
some light on this.

Thanks in advance

[enigma]

Hi Ray,

I did check and this what it gets to say

name : Gateway_01
db_size : 873.06 MB
owner : NULL
dbid : 8
created : Aug 7 2000
status : no options set

name : Gateway_01_Data Gateway_01_Log
fileid : 1 2
filename : C:\Data\Gateway_01_Data.MDF
C:\Data\Gateway_01_Log.LDF
filegroup: PRIMARY NULL
size : 892736 KB 1280 KB
maxsize : Unlimited Unlimited
growth : Unlimited 10% Unlimited 10%
usage : data only log only

Did you somehow get switched to simple recovery mode? You can find this
out by typing sp_helpdb 'dbname' Have you been doing inserts, updates or
deletes?

Ray Higdon MCSE, MCDBA, CCNA

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Ray Higdon]

I don't see anything wrong with your config and if the log file is
growing it seems OK. I don't think the timestamps and your virtual log
files are a problem either, although I don't have a SQL 7.0 server handy
to check.

Ray Higdon MCSE, MCDBA, CCNA

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Ray Higdon]

BTW: This is a good page on 7.0 FAQ's

http://support.microsoft.com/default...b;en-us;195760

HTH

Ray Higdon MCSE, MCDBA, CCNA

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Tom Kitta]

Interesting, I have the reverse problem -- log files tha grow to fast, mybe
we need to swap settings? :)
"enigma" <jo***@pure-commerce.com> wrote in message
news:7a**************************@posting.google.c om...

Just wondering if you could help me on this one. I'm not sure if my
transaction logs are behaving oddly or what. I've successfuly managed
to shrink my transaction logs from 7GB down to 1MB and now I find it
strange that the log file doesn't seem to increase its size. The
timestamp of the logfile is updating as well. But the size of the file
is constant. I haven't configured my database to do auto-shrink so Im
really confused why it hasn't changed its size for more than a month
now.
Hope I'm not losing any data here.
Kindly advise.