August 18, 2011 at 1:38 pm
When I did a check on my data file and log file about two weeks ago it had the following details
File_Type //// File_size_in_MB //// SpaceUSed in MB//// SpaceLeft in MB
Log file //// 8589 ////8434 //// 156
Now when I ran the check again today it has the following
File_Type //// File_size_in_MB //// SpaceUSed in MB//// SpaceLeft in MB
Log File //// 9448 ////9275 //// 173
Does this mean the file grew by 859 MB I am confused! Please help me to understand this growth.
Thanks in advance.
August 18, 2011 at 1:44 pm
Yes, it grew by that amount.
Is this database in Full recovery? Are you backing up the transaction log?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 18, 2011 at 1:47 pm
Can you check what is the increment set for the file growth of Log file? It is most probably set as 10% increment.
-Roy
August 18, 2011 at 1:50 pm
yes the db is set to full recovery and I am backing up the transaction log. But Why do you ask that question ..in other words what triggered you to ask that question ? 🙂
August 18, 2011 at 1:51 pm
Roy Ernest (8/18/2011)
Can you check what is the increment set for the file growth of Log file? It is most probably set as 10% increment.
Yes 10% growth.
August 18, 2011 at 1:56 pm
When you have regular transaction log back up the chances of file growing out of proportion is very low. I guess that is why he asked.
-Roy
August 18, 2011 at 2:21 pm
Roy Ernest (8/18/2011)
When you have regular transaction log back up the chances of file growing out of proportion is very low. I guess that is why he asked.
Precisely.
What is the schedule of your tran log backups?
If you have full backups running and are also backing up the t-logs regularly then you have some process that is running out of whack that needs fixed.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 18, 2011 at 2:53 pm
SQLRNNR (8/18/2011)
Roy Ernest (8/18/2011)
When you have regular transaction log back up the chances of file growing out of proportion is very low. I guess that is why he asked.Precisely.
What is the schedule of your tran log backups?
If you have full backups running and are also backing up the t-logs regularly then you have some process that is running out of whack that needs fixed.
I have full back up running at 3:00 AM then I back up T-logs starting from 7:AM till 11:59PM in every one hour.
August 18, 2011 at 2:59 pm
Then I would start looking at what caused that much growth and that much log space to be consumed.
Here is an article to help with that.
http://www.sqlservercentral.com/articles/Log+growth/69476/
In the meantime, I would NOT shrink the t-log. If you shrink it will likely just grow back out to that size.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 18, 2011 at 3:00 pm
The log grows to the peak space needed between log backups. If you had some process run that needed more space, it could have caused the log to grow.
Or if your log backup job missed some scheduled runs, the log might have grown.
August 19, 2011 at 7:49 am
I ran the DBCC openTran(mydb)
but there is no open transactions.
How do I to locate queries that consume a large amount of log space?
Thanks for your help.
August 19, 2011 at 7:53 am
You will have to monitor the DB. You could try running a server side trace to find out which is the costliest and what is doing.
-Roy
August 19, 2011 at 8:46 am
We found out one of the databases just had full back up and no t-log back up. We started the t-log back up now but the original t-log is still huge about 10G for a 4G database.Now that the t-log back up is back in schedule, can we shrink the t-log file moving forward?
After scheduling the t-log backup the database log file size is almost 10G but the space_used_in_MB = 106 ( this space went down) It was about 8G before. However the file size is still about 10G. Is this something I need to worry about because there is still about 9G space left.
Thank you all!
August 19, 2011 at 8:52 am
August 19, 2011 at 8:53 am
Do a one time shrink of the log. Look at the peak backup log size, add some pad, and shrink to that level.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply