April 28, 2014 at 3:32 am
Dear All,
Is there any Exact reasons for growing the Log Size ,
I Have referred some online guides but, I didn't clear about that,All are giving the solutions like
1> Take the Log Backup
2> Commit the Open Transactions
3> Shrink the Log File
So If I want check the Exact reason behind that Growing what can i do..
Pleas help me out..
Thanks In Advance...
Regards
Chowdary...
April 28, 2014 at 3:46 am
If your log file is full and it isn't allowed to grow, the database can't do any transactions any more.
So you either make sure the log file is big enough to incorporate all foreseeable transactions until the next log backup, or you give it a reasonable size and allow it to grow.
Be careful about the advice though: there is no need to shrink the log file!
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 28, 2014 at 4:11 am
Thank you Verbeeck,
Actually i want to know the reasons for that growing,
Is there any particular reasons or anything else... for that Log File Growing .
We are having enough Space for Log File and my database is minimal growing only(Its a Small Database)
Regards.....
Regards
Chowdary...
April 28, 2014 at 4:15 am
The log file keeps recording transactions until you take a log backup (unless in simple recovery mode).
If the log file grows, it's because it needs that space to log all the transactions.
With DBCC SQLPERF, you can monitor how much of your log files are actually used.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 28, 2014 at 4:25 am
Here, read this about the log [/url]to understand why it's always growing.
You need to either set your database to simple recovery, or get log backups in place.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 28, 2014 at 5:08 am
Thank you all,
From your replies i came to know 2 main things to clear the Log File those are below
1> Need to take the Log Backup Frequently
2> Put the database in Simple Recovery Model
Is there any other things need to be follow??????
As per My knowledge few things i want to share i.e
1>Is there any issue with Long Running Queries if there what i have to do(Except Committing or Killing Those Tran)..
2>Is there any issue with Rebuilding the Large Indexes.
:-):-):-)
Regards
Chowdary...
April 28, 2014 at 5:28 am
You cannot take a log backup when you are in simple recovery mode (the log is recycled).
So it is either option 1, or option 2.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 28, 2014 at 5:42 am
Take a read through this: http://www.sqlservercentral.com/articles/Administration/64582/
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
April 28, 2014 at 5:54 am
Chowdary's (4/28/2014)
Thank you all,From your replies i came to know 2 main things to clear the Log File those are below
1> Need to take the Log Backup Frequently
2> Put the database in Simple Recovery Model
No. Not both. One or the other.
Is there any other things need to be follow??????
As per My knowledge few things i want to share i.e
1>Is there any issue with Long Running Queries if there what i have to do(Except Committing or Killing Those Tran)..
2>Is there any issue with Rebuilding the Large Indexes.
:-):-):-)
If a query runs long, tune it. Killing a transaction will usually just result in an equally long rollback and then the person resubmits the offending query again anyway.
Rebuilding an index will need space in the log, yes, and tempdb. Make sure you have enough room.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 28, 2014 at 6:56 am
Is this good to set the database recovery model to Simple,
Because if we want to recover the Data Point_In_Time ,its not possible in that Simple Recovery Model.
If suppose Daily 6AM we will take the full backup ,At 6PM Server crashed then surely the Business Team will not agree for 12hrs of loss of Data,
In that scenario what should we do....
Regards
Chowdary...
April 28, 2014 at 7:09 am
Chowdary's (4/28/2014)
Is this good to set the database recovery model to Simple,Because if we want to recover the Data Point_In_Time ,its not possible in that Simple Recovery Model.
If suppose Daily 6AM we will take the full backup ,At 6PM Server crashed then surely the Business Team will not agree for 12hrs of loss of Data,
In that scenario what should we do....
If you want to recover to a point in time, then no, setting the database to simple recovery is the wrong thing to do. That's why I said it was one thing, or the other. To maintain the log you must do one of two things.
Option 1) You can set your database to simple recovery. Then, you don't have to worry about log backups because you can't take them. But, you lose the ability to do a point in time recovery. I don't recommend doing this. But, it is one of your options.
Option 2) You set your database to full recovery, but then you must schedule log backups. And they need to be scheduled with an adequate frequency. I usually say a minimum of once an hour, but probably, for most systems, once every 1/2 hour to once every 15 minutes is better. This way you get point in time recovery. But, now, you must maintain the backup process in order to ensure the log stays as small as you can (it has to be able to store the amount of transactions you have over your backup time period, whatever that might be).
Those really are the two options and there's no getting around it. If you're not setting up regular log backups, your log will grow and grow until the drive is full. So, either, set up regular log backups or set the database to simple recovery.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 28, 2014 at 7:24 am
Thank you so much Grant..
Now am very much clear on that.
Still i am Expecting the Replies from Other Experts...
Thanks In Adv...
Regards
Chowdary...
April 28, 2014 at 7:28 am
Chowdary's (4/28/2014)
Thank you so much Grant..Now am very much clear on that.
Still i am Expecting the Replies from Other Experts...
Thanks In Adv...
Not sure I am an expert, but I second Grants remarks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 28, 2014 at 7:28 am
Chowdary's (4/28/2014)
Thank you so much Grant..Now am very much clear on that.
Still i am Expecting the Replies from Other Experts...
Thanks In Adv...
They may or may not respond. Gail provided you an article that she wrote that answers all your questions. I'm not sure she needs to follow up (although she'll get an alert for every response on this question). Koen also gave you good answers already. Everyone is saying the same thing.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 28, 2014 at 7:44 am
Chowdary's (4/28/2014)
Still i am Expecting the Replies from Other Experts...
Did you bother to read the article I linked?
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
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply