how do I keep number of transactions down?

[DennisTheMenace]

My website is nearly 99.9% database driven. Virtually every page you view is data from the SQL server. 90% of that is simply retrieving data. The only time you write to the DB is if you add to your basket, and then more is done when you check out. My ISP is telling me that my Transaction Log keeps filling up.

Is there some guidance that someone(s) can give me to help me keep my transactions down - and understand what/why my log is filling? What "transactions" are there when retrieving data from SQL?

FYI: I was careful to make sure that all of my ASP pages included not leaving open recordsets and database connections -
rs.close
set rs=nothing
db.close
set db=nothing

Thanks for everyone's input! ) =====================
Dennis B

http://www.e-gunparts.com

 

[tlbroadbent]

Which version of SQL Server are you running? How frequently is the database backed up?

The transaction log records all updates and it will continue to grow until the database is backed up. You should be doing full and transaction log backups. If you use SQL 7 or SQL 2000, you can set options to SHRINK the log file. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[DennisTheMenace]

Thanks Terry!

Sorry I omitted that! It is SQL7. Unfortunately, it is a hosted server and I can not control changes such as setting options to shrink the log file. It is currently set to shrink on check point. I also can't control the backup situation on it as well. I have a current version of the DB on-site and regularly extract the new off of the web (again - more "reads" to the DB).

So my biggest concern is making sure I limit the number of transactions (if at all possible) so that the log doesn't actually fill... Does that make sense? I guess I am having difficulty understanding what type of "transactions" go into the log file that would need to be "restored" in case the SQL server was turned off in the middle of being used? Most of my transactions I would assume are simple reads to the DB?? I also once a week use DTS Bulk Copy to update (i should say overwrite existing data) the tblProductsTable. I thought that DTS Bulk Copy didn't write to the transaction log? *Phew* Am I making sense?


Remember - YOU ARE UNIQUE!!!... Just like EVERYONE ELSE! ;o) =====================
Dennis B

http://www.e-gunparts.com

 

[tlbroadbent]

Reads don't create transactions. DTS Bulk copy will not create transactions if the proper options are set. The ISP should provide adequate service such as full and log backups.

I'm not aware of an option to shrink on Checkpoint. You can truncate log on checkpoint. But that makes transaction unrecoverable if you should lose your database. There is an option to auto shrink. Perhaps, that is what you refer to. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[DennisTheMenace]

>> You can truncate log on checkpoint
Sorry... You are correct. That is what I meant.

I am unsure of where all my transactions are coming from then if reads and dts don't create transactions. Is it possible that the log is not getting backed up? My ISP is a very reputable one... so I am quite sure they know what they are doing on their end.

Any other reasons why a transaction log would continue to grow and only truncate on checkpoint?

What should I ask my ISP about the "auto shrink" option?
=====================
Dennis B

http://www.e-gunparts.com

Remember - YOU ARE UNIQUE!!!... Just like EVERYONE ELSE! ;o)

 

[DennisTheMenace]

PS. BOL says the following:

Truncating the Transaction Log
If log records were never deleted from the transaction log, the log would keep growing until it filled all the available space on the disks holding the log. At some point in time, old log records no longer necessary for recovering or restoring a database must be deleted to make way for new log records. The process of deleting these log records is called truncating the log.

The active portion of the transaction log can never be truncated. The active portion of the log is the part of the log needed to recover the database at any time. It must always be present in the database in case the server fails because it will be required to recover the database when the server is restarted. The record at the start of the active portion of the log is identified by the minimum recovery log sequence number (MinLSN).

=====================
Dennis B

http://www.e-gunparts.com

Remember - YOU ARE UNIQUE!!!... Just like EVERYONE ELSE! ;o)