November 6, 2009 at 11:58 am
Hello all
I am not versed in SQL. However, I ran DBCC SQLPERF(Logspace) and found that my log file is 4 gig but is only 3.8% used.
The DB is in full recovery mode as recommended by the app vendor.
Maintenance plan is in effect and there has not been any corruption/errors - all has been working day in and day out.
I would like to know my options for
1)testing further to see why this is so large
2)assessing whether the shrinkfile would be safe to use
I sort of stumbled upon this today and it just does not seem right to me
the associated .mdf file is 7 gig and I don't have a good grasp on the # of log entries that result from our transactions daily. However, I have to assume that it is fairly constant day to day.
thank you for any replies
November 6, 2009 at 12:16 pm
I would like to know my options for
1)testing further to see why this is so large
2)assessing whether the shrinkfile would be safe to use
These are my inputs and am glad if any one can correct.
You mentioned you have maintenance plan in place, what frequency are the Transaction Logs backed up?
Q1. All I have to say is you need to know the Usage of the Log File at Peak usage so that you can have a fair idea of what is the % of Log being used. May be an Index maintenance Job is growing the Log file to that extent, so you shrink and it would come back to this 4 Gigs size next time that Maintenance Job is run.
Q2. When it comes to safety shrinking will not delete any data, so you wont loose, it is mainly required to give the OS the free space so to enable another database to use that space, if you do not have issues about space, I would not consider shrinking, since any time the file grows would be hampering the performance.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 6, 2009 at 12:47 pm
Hello
Thx for responding
The maintenance plan does a complete backup nightly and a transaction log nightly right after the complete backup (that's what I see in properties of my maint plan)
The optimization options(reorg indexes) happens on sundays.
I do check space on that server daily and I don't see space being used up in an excessive manner.
However, I do not know how the log size has changed over time
What I do know is that we do not create transactions throughout the day - only in the mornings and a little in the afternoon for a short period.
It is a portfolio mgmt app
November 6, 2009 at 1:03 pm
The maintenance plan does a complete backup nightly and a transaction log nightly right after the complete backup (that's what I see in properties of my maint plan)
The optimization options(reorg indexes) happens on sundays.
First, I am not able figure out why a Maintenance plan is created for Full backup Nightly and Transaction Log Backups right after the Full backup.
Check and double check that maintenance plan, if it is still what you mentioned already, then I would be concerned. SInce a failure of the system occurs 1 hour before the Full backup, then you would have not a chance to bring the Data that is entered / modified that whole day, you are good till last night.
From you explanation it doesnot look its a heavy Transaction system, then I would suggest is on the day the reorg indexes occurs , may be a few hours before, shrink the Log File and monitor the Log after that reorg indexes and find if thats causing the Log Growth. (If incase, it is causing the growth then I would not shrink it next time)
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 6, 2009 at 1:05 pm
Great article in today News Letter on Transaction Log.
http://www.sqlservercentral.com/articles/Design+and+Theory/63350/
"When in danger or in doubt. Run in circles, scream and shout!" TANSTAAFL
"Robert A. Heinlein"
November 6, 2009 at 1:41 pm
You are absolutely right!
and i had not realized that. What I do see is separate jobs (1 for complete backup and 1 for just the log ) that are created with a comment that says (to be done after daily transacts are entered) however they are not on a schedule (i guess since we can't anticipate when all transactions for the day will be finished)
How should these backups be implemented then??
As for the shrinkfile - i need to read about it and see what parameters i need to specify but it sounds like great suggestions
thanks very much
November 6, 2009 at 1:48 pm
Implementing backups depends on what is the least amount of Data that you are ready to loose in case of a failure occurs. Once that is defined by you or your Manager who owns the responsibility, you need to schedule your backups depending on that.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 9, 2009 at 3:36 pm
However, I do not know how the log size has changed over time
This could be down to a lot of reasons. The most probable I would think is that the log file is set to autogrow and at one point in time there were enough transactions where the file ran out of space before a checkpoint could be created, so the file grew to the autogrowth size specified to account for more transactions.
If you go to the properties of the database and look at the properties fo the database files, it should tell you if they are set to autogrow or not.
Joie Andrew
"Since 1982"
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply