September 19, 2008 at 9:45 am
On SQL Server 2000...
I think I have my facts straight, but want to confirm.
We have a db that is very heavily used and the t-log grows at an exponential pace (the data file is almost 9GB but the log grows from 300MB to about 10 GB in just a few days.)
This is a server at one of our remote locations that I have nothing to do with so I don't know what applications are running on it. The IT resource (more of a network guy as opposed to database guy) has asked me about setting up a SQL Server agent job to truncate the t-log every few days.
They currently do a full backup every night and have a job that backs up the transaction log every 2 hours.
My initial thought is that setting up a job for this is not necessary and here is where I need confirmation that I am on the correct path...
The current recovery model is set to Full. My thought is that if that is changed to Simple, then when the transaction log is backed up (as well as when the daily db backup is done) that part of that process would be keep the transaction log truncated.
Am I way off-base here?
As a followup... if he does change the recovery model from full to simple, does the db need to be offline to make that change or can it be done "on the fly"?
Thanks for any insight...
Bob
September 19, 2008 at 9:52 am
Hello,
I would not recommend changing a production DB to the Simple Recovery Model. If the DB is damaged between full DB backups, you can not recover from the Transaction Log i.e. you risk data loss. In the case of one full backup per day, you could theoretically lose a whole day's data.
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
September 19, 2008 at 10:06 am
Bob Bridges (9/19/2008)
On SQL Server 2000...They currently do a full backup every night and have a job that backs up the transaction log every 2 hours.
...
The current recovery model is set to Full. My thought is that if that is changed to Simple, then when the transaction log is backed up (as well as when the daily db backup is done) that part of that process would be keep the transaction log truncated.
If this is a production OLTP type system, you probably don't want to switch to Simple recovery mode, because you won't be able to do point in time recovery if you need to do a database restore for whatever reason. (also limits what you can do in terms of database mirroring and replication)
When in Full recovery mode, and doing the transaction log backups every 2 hours, I'm a little suprised that the transaction log file would get as large as the data file, unless you were doing massively large data loads / updates. Once that log backup occurs, the space can be resused for other transactions, so shrinking it won't really solve the problem, just mask it. The key here is to understand what type of database this is, and what types of inserts/updates/deletes are happening that's causing the transaction log to grow so much.
September 19, 2008 at 10:15 am
I agree with John and Chris about not changing the database to simple recovery mode. You say that the database is very heavily used. I'd recommend doing log backups more frequently - every hour or even every half hour to truncate the log more frequently.
Greg
September 19, 2008 at 10:20 am
Greg Charles (9/19/2008)
I agree with John and Chris about not changing the database to simple recovery mode. You say that the database is very heavily used. I'd recommend doing log backups more frequently - every hour or even every half hour to truncate the log more frequently.
I've been doing more reading since my original post. I'm not disagreeing with anything that has been said, but seeking clarification. Greg, if we are currently doing transaction log backups every two hours, why does the log keep growing and growing... shouldn't it be truncated/shrunk every time a t-log backup is made?
September 19, 2008 at 4:34 pm
When the transaction log backup happens, it frees up space within the file to be reused by other transactions. If what you say is true, then to keep shrinking it is not really helping you any, because you said that it grows back in a couple of days. What kinds of INSERTS, UPDATES, and DELETES are occurring in the database?
September 20, 2008 at 2:43 am
If you switch to simple recovery, you will no longer be able to run log backups.
Check your log backup job, make sure that no one's added the 'WITH NO_TRUNCATE' clause to the BACKUP LOG command.
Do you have transactional replication running?
If you run the following in the DB in quesition, what do you see?
DBCC OPENTRAN
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 22, 2008 at 8:54 am
I'm having the very same issue with Bob..... however how can I determine what kinds of INSERTS, UPDATES, and DELETES are occurring in the database?
September 22, 2008 at 9:10 am
You can use profiler to see all the queries run against a server.
To check:
What recovery model is the DB in?
Do you have log backups running (not applicable if the above answer was Simple)
Do you have replication running (transactional or merge)
SQL 2000 or SQL 2005?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 22, 2008 at 9:18 am
Using Full Recovery
Backups are scheduled every hour from 6am to 9pm
Not sure if replication is running
SQL 2000
September 22, 2008 at 9:31 am
Molly Cary (9/22/2008)
Backups are scheduled every hour from 6am to 9pm
Full, diff or log backups?
Do you have any DB maintenance happening after 9pm (index rebuilds)?
What's the pattern of log growth?
What does the following return when run in the DB that's exhibiting growing tran logs?
DBCC OPENTRAN
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 22, 2008 at 10:07 am
I'm assuming they are full log backups (where can I verify?)
Sorry, log files are being backed up every hour from 6-9, however there is a maintenance plan scheduled at 11:30pm
Run the DBCC OPENTRAN and results were
No active open transactions.
September 22, 2008 at 10:27 am
Are the log backups succeeding?
Maintenance plan of custom job?
What's the pattern of log growth? Is it stable in size during the day and growing at night? Is it constantly growing?
If you run the following, what's the output? (just for the DB in question)
DBCC SQLPERF(logspace)
Can you run that as a scheduled job (maybe every half hour) and log to a table? Would be useful to see what's happening inside the log.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 22, 2008 at 3:40 pm
Are you shrinking the logs after you back them up? If so - they're going to keep growing.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 24, 2008 at 6:32 am
I've been monitoring the growth over the last two nights and we have a 30G drive that the logs are on and they are filing to 27, 28G overnight. I shrink them and they go down to 10G.
The back ups jobs are sucesseding, I ran that DBCC and it gave me an error for parameters. What could be making them do this?
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply