Transaction Logs 1

[JohnBates]

hi everyone,

I have a SQL Server 2000 db that is set to Simple logging mode.
But between database shrinks, the T-log file grows to 5 GB (the data file is only 4GB)

I guess I have 2 questions:

1. what kinds of activity cause the T-log to grow? Would a SQL select stmt (using joins) for a very large table use the T-Log? When using Simple mode, is the T-log just used for "workspace"?

2. Is there any way to turn off the use of the transaction log for a particular database?

(As you may have guessed, the server is low on available disk space - I'm looking for a quick-fix until the new disk drives arrive.)

Thanks, John

 

[Erics44]

have you tried shrinking the log files

I dont know if that is the best way to go about it but with stored proceedures I add statements to shrink the log files

 

[SQLBill]

1. INSERTS, DELETES, UPDATES, DBCC commands (DBCC INDEXDEFRAG for example)...any transaction makes the Transaction Log grow.
The only real difference with SIMPLE mode is that SQL Server sets a 'checkpoint' every so often and virtually marks that part of the log as re-usable.
2. Not anyway I know of.

Low disk space.....are you backing up your database? If you have a full backup, you can do transaction log backups that eventually recover the space used (see the BOL for more on this). With SIMPLE mode, or if you don't care about the transaction log, you can do BACKUP LOG dbname WITH TRUNCATE_ONLY. But if you do that, you should do a FULL backup immediately after.

-SQLBill

Posting advice: FAQ481-4875

 

[JohnBates]

Thanks Erics44 and SQLBill. I'm already running this via a Scheduled SQL Server job once each day:

BACKUP LOG reporting WITH TRUNCATE_ONLY
DBCC SHRINKFILE('reporting_log', 1)

So now I need to run this more frequently.

Thanks, John

Where are those new disk drives ?!