473,394 Members | 1,494 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,394 software developers and data experts.

Transaction logs size not increasing

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.
Jul 20 '05 #1
7 3850
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!
Jul 20 '05 #2
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.

Jul 20 '05 #3
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
Jul 20 '05 #4
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!

Jul 20 '05 #5
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!
Jul 20 '05 #6
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!
Jul 20 '05 #7
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.

Jul 20 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

10
by: TZoner | last post by:
1) Can one find the location of the 'Transaction Log' at: <Hard Disk>\Program Files\Microsoft SQL Server\MSSQL\Data\MyDb_Log.ldf? 2) Is it safe to delete it, as SQL will create a new Transaction...
5
by: Jay Chan | last post by:
The transaction log in a database in our SQLSERVER-2000 server has grown to 16GB. I cannot shrink the transaction log manually because it says that the entire 16GB log size is not free. This is...
0
by: xo55ox | last post by:
Hi, I have been trying to set up an automated restore process from prod to backup server. First, I schedule the full database backup nightly, transfer the backup file and restore it to the...
5
by: BashiraInTrouble | last post by:
Hi Friends, I have tried almost everything but I cant seem to shrink the transaction log. Executing DBCC SQLPERF(LOGSPACE) gives me this info: Database Log Size (MB) Log Space Used (%) ...
1
by: jeffreyv | last post by:
Hi! I'm studying to have my MCSE 70-228 certification and I'm trying some things with backing up transaction logs and shrinking it. Here's what I do: There is no activity in the database by...
2
by: Wolfgang B. | last post by:
Hi. I have a problem with many transaction logs generated by DB2 Version 7.2 on Redhat 7.3. Log file size (4KB) (LOGFILSIZ) = 1000 Number of primary log files ...
3
by: TG | last post by:
Coming from a (mostly) Oracle shop, I am unclear how SS transaction logs work as far as up to point of failure recovery goes. I have the few MSSQL databases I look after in full recovery mode,...
0
by: kazper | last post by:
Good day, I have some problems regarding my Hard disk space for my logs in SQL Server 2005. When I try shrinking the database it returns an error not letting me to do anything about the size of...
5
by: Roger | last post by:
I have a siebel crm application thats cutting and archiving logs every minute. Here is the db cfg Log buffer size (4KB) (LOGBUFSZ) = 512 Log file size (4KB) ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.