?
Solved

Transaction Logfile Maintenance suggestions

Posted on 2006-04-26
8
Medium Priority
?
287 Views
Last Modified: 2012-05-07
Hello all,

I'm a network administrator, and I have a programmer who works under me that has written a Real Estate listing search/Statistical package for which the database is MSSQL 2003.

The transaction logs regularly climb over 12-15gb and I end up having to detach and reattach the database so I can delete the transaction log to free up hard drive space.

My programmer tells me that this is 'normal' and that if I use the 'restrict filegrowth' option, the DB will stop working correctly once it reaches that allotted size. I suspect this is probably not true, but I don't want to risk it.

My question is, is there a way to set up a job within SQL server to delete that transaction log and recreate it once it reaches an allotted size? I don't see any way to do it. Is there even any reason to create a transaction log? Does SQL actually need it for anything?

Sorry if I seem ignorant here, but I just dont know that much about it.
0
Comment
Question by:IT Gal
7 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16543856
>I suspect this is probably not true, but I don't want to risk it.
it is true.

however:
if the db is in full recovery mode, you should implement a regular full and transaction log backup.
if the db is in simple recovery mode, the transaction log will NOT fill that fast unless your develop is doing tons of queries 'of the death' :-)

your choice.
0
 
LVL 20

Expert Comment

by:Sirees
ID: 16543994
>>Is there even any reason to create a transaction log? Does SQL actually need it for anything?  <<

It needs it for recovery.

You need to backup the database regularly to avoid these issues.

<< I end up having to detach and reattach the database so I can delete the transaction log to free up hard drive space. >>

Instead you can shrink the log file using DBCC Shrinkfile



0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 16544009
You can probably safely limit the log size to, say, 5G if you:

1) Change the recovery mode to "bulk_logged" instead of "full".
2) Backup the log fairly frequently, say every hour (you can do this was a Maintenance Plan or your own code).

Btw, you can shrink the log w/o detaching the db, like so:

EXEC sp_helpfile
-- write down the logical log file name, in the first column of the output ...
-- ... you'll need it later

BACKUP LOG databaseName WITH TRUNCATE_ONLY

USE databaseName

DBCC SHRINKFILE (logical_log_file_name, 5000)

The last value is the size to shrink the log to -- you don't want to have the log continually expanding, so leave it with a pretty good chunk of space.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 11

Expert Comment

by:Ved Prakash Agrawal
ID: 16544258
Truncate the transaction log
and then shrink the log file.

0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 16718124
Why a grade of "C"?  The asker never came back to clarify anything.
0
 

Author Comment

by:IT Gal
ID: 16718274
I didn't think it was a grading system like ABCDF

They just presented three options, A) Excellent B)Great and C) Good (or something to that effect).

It wasnt meant to be negative, it just wasnt excellent or great. It was good.
0
 
LVL 20

Expert Comment

by:Venabili
ID: 16718402
pir8matt ,

Well...
C is a bad grade. If you mean good, you give B, if you mean great/excellent - it is an A :)
I know that the help page tends to tell something else but these are the grades in fact and most of it is explained here:http://www.experts-exchange.com/help.jsp#hi73

Venabili
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question