IT Gal
asked on
Transaction Logfile Maintenance suggestions
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.
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.
>>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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Truncate the transaction log
and then shrink the log file.
and then shrink the log file.
Why a grade of "C"? The asker never came back to clarify anything.
ASKER
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.
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.
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:https://www.experts-exchange.com/help.jsp#hi73
Venabili
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:https://www.experts-exchange.com/help.jsp#hi73
Venabili
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.