 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / DB Engine / SQL Server / March 2008SQL2005 - Reorganize Index Maintenance task
I have a question regarding performing this task on our database. We run this once a week, Sunday at 1:00am. We are noticing that all of a sudden our Transaction Log is growing virtually from 100MB to 25GB over the weekend. I am trying to find out if re-organizing the indexes might perhaps be causing this problem? We recently upgraded to SQL2005, and this same job ran on our SQL2000 and that log when it was on that server had started to grow like this weeks before we did the migration. This task has been running for some time without causing this type of issue, but perhaps I didn't keep that close of an eye on it, I don't know. In any case, pertaining to our SQL2005 server, last Monday I shrank the log back to approximately 25MB and by Friday it was a little less than 100MB. When I came in yesterday, the log file was up to 25GB. The only thing I can think of is that reogranizing the indexes and checking the DB integrity is causing this issue. Is it necessary to be running these tasks? I am not a DBA or anything and really only have very basic knowledge in administering SQL. Thank you in advance for any help you can give! Sara > I am trying to find out if re-organizing the indexes might perhaps be causing this problem? Yes, it is possible. > Is it necessary to be running these tasks? Only you can determine that. Here's a good start: http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx Also, 25MB for a database where the lgo blows up to 25 GB when you reindex sound extreme. How much data do you have? You probably want to have something like at least 25% of log space compared to your data. What I'm trying to say is that you do not benefit from trying to have a too small log file.  SignatureTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi >I have a question regarding performing this task on our database. We run this once a week, Sunday >at 1:00am. We are noticing that all of a sudden our Transaction Log is growing virtually from [quoted text clipped - 12 lines] > > Sara Thank you for the response, Tibor. Our database is only approximate 21GB total. So, at times, the log file is getting bigger than the actual database. I guess I didn't realize that you didn't necessarily want that small of a log file, but yes, I agree that jump from 100MB to 25GB is definitely extreme and that is why I'm concerned. I will look into the link you sent me and see what I can find out. >> I am trying to find out if re-organizing the indexes might perhaps be >> causing this problem? [quoted text clipped - 32 lines] >> >> Sara There may also be some weekend activity (Data loads for example) that is increasing the log file size SignatureKevin3NF SQL Server dude You want fries with that? http://kevin3nf.blogspot.com/ I only check the newsgroups during work hours, M-F. Hit my blog and the contact links if necessary...I may be available. > Thank you for the response, Tibor. Our database is only approximate 21GB > total. So, at times, the log file is getting bigger than the actual [quoted text clipped - 38 lines] >>> >>> Sara We have looked at data loads - and the person who is responsible for those has suspended the larger loads for now. > There may also be some weekend activity (Data loads for example) that > is increasing the log file size [quoted text clipped - 52 lines] >>>> >>>> Sara What Recovery model are you in? What is your strategy for log backups? Ola Hallengren http://ola.hallengren.com > Thank you for the response, Tibor. Our database is only approximate 21GB > total. So, at times, the log file is getting bigger than the actual database. [quoted text clipped - 39 lines] > >> > >> Sara Recovery model is FULL. This is what we do for backups and such: A full DB backup is done every night at 6:45pm. Throughout the day, the Trans Log is backed up every 2 hrs from 10am-7:00pm. We also have the Reorganize Indexes task running once a week on Sunday at 1:00, as well as the Check DB Integrity which also runs at 1:00 on Sundays. Finally, we have cleanup tasks that run every day to remove BAK and TRN files older than 2 days. Thanks! Sara > What Recovery model are you in? What is your strategy for log backups? > [quoted text clipped - 49 lines] >>>> >>>> Sara Are your log backups also running in the weekend? Was the time for the Reorganize Indexes task Sunday 1.00 am or pm? One thing that you could do if you would like to find out exactly when the log file is growing, is to do a Profiler trace with the event [Log File Auto Grow]. Another thing that you could do is to check the sizes of your log backup files. This way you could see when there has been a lot of log activity. /Ola > Recovery model is FULL. > [quoted text clipped - 63 lines] > >>>> > >>>> Sara Yes, the log backups are also occuring on the weekend. The re-organize indexes task runs at 1:00am. I noticed that yesterday when I came to work the log file that backed up at 10:00am was the full 25GB in size. It was not like this on Friday night when I left work. I did not make a point of checking, but I believe this weekend I will try and keep an eye on the log file and try to pinpoint if this is happening before 1:00am on Sunday morning. If it's happening after 1:00am on Sunday, then I guess come Sunday morning I can assume it is this task causing the problem, if it can be defined as a problem, I guess, or just normal behavior? The Reorganize Indexes task is set for "Tables & Views" and to "Compact Large Objects". Should I maybe uncheck that or anything? Or change it to just tables or views? Our Log files is set to AutoGrow, by 10%, and is set to Unrestricted File Growth. I guess I am unsure how to run a Profiler trace. Thanks! Sara > Are your log backups also running in the weekend? > Was the time for the Reorganize Indexes task Sunday 1.00 am or pm? [quoted text clipped - 79 lines] >>>>>> >>>>>> Sara You could temporary change the log backup job to run hourly 24/7. Then you will most likely see some very large log backups right after the reorganize index job. I think that what you're seeing is just normal behaviour in this scenario. What you could do is to consider rebuilding / reorganizing only the indexes with fragmentation. (Say that you have a large table that is only updated rarely. Then your don't really have to reorganize that table weekly.) http://msdn2.microsoft.com/en-us/library/ms189858.aspx Unfortunately the Maintenance Plan does not support this. I have a stored procedure that you could use if you like. http://blog.ola.hallengren.com/blog/_archives/2008/1/1/3440068.html About Profiler I think that you just have to try it out and read a little in Books Online. http://msdn2.microsoft.com/en-us/library/ms187929.aspx Ola Hallengren http://ola.hallengren.com > Yes, the log backups are also occuring on the weekend. The re-organize indexes > task runs at 1:00am. I noticed that yesterday when I came to work the log [quoted text clipped - 100 lines] > >>>>>> > >>>>>> Sara Ola, Thank you very much for your help and information! I will take a look at the links you sent me and perhaps run an hour TRN backup. Sara > You could temporary change the log backup job to run hourly 24/7. Then > you will most likely see some very large log backups right after the [quoted text clipped - 129 lines] >>>>>>>> >>>>>>>> Sara Saral6978 I would not rely on MP task. Instead , I would identify heavy fragmented tables/insdexes and re-organize only them SELECT OBJECT_NAME(i.object_id) AS TableName , i.name AS TableIndexName , phystat.avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(db_id(), NULL, NULL, NULL, 'DETAILED') phystat inner JOIN sys.indexes i ON i.object_id = phystat.object_id AND i.index_id = phystat.index_id WHERE phystat.avg_fragmentation_in_percent > 40 and page_count>=1000 > Ola, > Thank you very much for your help and information! I will take a look at [quoted text clipped - 134 lines] >>>>>>>>> >>>>>>>>> Sara |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.