September 22, 2010 at 6:42 pm
Hi All
Consider following status :
- SQL Server 2005
- A large database in "Full Recovery" mode and taken transaction logs hourly
- Nightly Maintenance job for Update Statistics
- Update Statistics with "All existing statistics" and "Full scan" options
It creates large transaction log and hard to handle.
Questions :
1 - Is there any way to decrease the size of transaction log ?
2 - Is there any way to ignore logging in Update Statistics procedure ?
Thanks in advance
September 22, 2010 at 11:12 pm
The recovery model can be set to BULK LOGGED
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
September 22, 2010 at 11:22 pm
Just update stats, or is there an index rebuild job as well? Stats shouldn't generate that much log data (unless you have millions of them). Index rebuilds will.
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, 2010 at 11:30 pm
I have a small re-index job ( re-indexing 6 tables ) but timing is different and transaction logging happens hourly.
September 22, 2010 at 11:44 pm
with bulk logged recovery mode, I lost point in time recovery
September 22, 2010 at 11:46 pm
There's no way to turn logging off for any operation in SQL. Everything has to be logged. That said, stats updates shouldn't log very much. That's why I'm wondering if there's something else in the same period.
How many tables and how much log growth are we talking about here
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, 2010 at 11:47 pm
Adiga (9/22/2010)
The recovery model can be set to BULK LOGGED
Got a reference that indicates that stats updates are minimally logged? I don't see them listed under "Operations That Can Be Minimally Logged" in Books Online.
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, 2010 at 11:48 pm
a.shahnazi (9/22/2010)
with bulk logged recovery mode, I lost point in time recovery
Only if you're trying to restore to a log interval that had a bulk operation in it.
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, 2010 at 11:59 pm
Database size : 80 GB
Number of tables : 296
Log size after Update Statistics : 4 GB
September 23, 2010 at 1:43 am
4GB is not particularly large for a log file for an 80GB database. Why are you finding it hard to manage?
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 23, 2010 at 1:50 am
GilaMonster (9/22/2010)
Stats shouldn't generate that much log data (unless you have millions of them). Index rebuilds will.
informative
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
September 23, 2010 at 6:17 am
Actually business owner want to transfer all transaction logs to somewhere else through the WAN.
September 23, 2010 at 6:21 am
Urrr, sorry can you explain that please?
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 23, 2010 at 7:32 am
I mean transaction logs should copy to another server through the wan.
If there is not a way to prevent growing logs up, I should convince them to specify more bandwidth.
Thanks for your notes, specially Gail's notes.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply