January 8, 2013 at 9:38 pm
Hi experts!
This is my first time creating a Maintenance Plan in SQL Server 2005 and I’ve a couple of questions regarding some estrange behavior of the above mentioned Plan.
The maintenance plan was created using the Designer and consists in two separate sub plans. The first one performs a DB Integrity check (let’s call it A) and the second one performs and index rebuild, history cleanup and maintenance cleanup (sub plan B). I had to divide it because of the maintenance window we have during the weekend.
The first thing I’ve noticed regarding sub plan A is the following:
-First of all the DB integrity check takes only 5 seconds and the status of the task is success (verifying the logs). This brings my attention considering this a productive DB and its size is almost 600 GB. The same task in our QAS environment where the DB size is little bit smaller took almost 10 hrs. Is the task result ok or how can I check if the task is doing what is supposed it should do? The sub plan was run several times and in all the executions the elapsed time was the same…5 seg.
-Second point I would like to check and also related to sub plan A is the fact that the first task before performing the DB integrity check is to set the Recovery Model to simple. Then the integrity check is performed.
After the success completion of it…another task should set the recovery model back to “Full”. Even when the integrity check finalizes ok the db remains as “Simple”. Is like the next task was ignored even when the tasks precedence indicates that. How can I check why this task (set recovery model to “Full”) is being ignored or skipped.
Script bring executed:
USE master ;
ALTER DATABASE DB1 SET RECOVERY SIMPLE ;
USE master ;
ALTER DATABASE DB1 SET RECOVERY FULL ;
Regarding the sub plan B, I have the following questions/issues.
-The task index rebuild takes forever…more than 24hs and because of that the job was cancelled. How can I check the reason of this behavior? The same task is our QAS environment took between 11 and 18 hs. Is there any way to manage this?
-The second issue we I’m facing is that in case of fail tasks and operator should be notified by email. The database mail has been successfully configured and the test email has been tested ok also. However when a task fails….no email is being sent to the designated operators. I’ve configure the data base mail and I’ve also added several “Notify Operatos task”…Should I check or configure something else?
Sorry for the long description. I’ll wait for your suggestions.
Thanks in advance
January 8, 2013 at 10:24 pm
I recommend against changing the database to simple and then back to full for the checkdb. I can sort of see that being doing for the reindex but I'd rather it be full to bulk and back.
Item 2 is that I would not use the maintenance plan to rebuild indexes. That method is a brute force method and is very inefficient. For index maintenance try one of these.
http://sqlfool.com/2011/06/index-defrag-script-v4-1/
http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
I would expect to see the checkdb run much longer than a few seconds. It seems to me that it is changing the database to simple recovery and then stopping the job and not proceeding.
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
January 9, 2013 at 6:14 am
Thanks for your quick response.
I’ll modify the sub plan A avoiding recovery model to be changed from full to simple. However I’ll leave that task in sub plan B because we had some space issues while performing test in the past.
Thanks for the recommended links I’ll consider include this rebuild index scripts instead of using the pre defined task.
Do you have any suggestion regarding email to operator’s issue? I was not able to find any log or error message indicating why the email had been not delivered.
Best regards. Thanks!
January 9, 2013 at 6:21 am
diequintana (1/9/2013)
Thanks for your quick response.I’ll modify the sub plan A avoiding recovery model to be changed from full to simple. However I’ll leave that task in sub plan B because we had some space issues while performing test in the past.
Thanks for the recommended links I’ll consider include this rebuild index scripts instead of using the pre defined task.
Do you have any suggestion regarding email to operator’s issue? I was not able to find any log or error message indicating why the email had been not delivered.
Best regards. Thanks!
Remove the recovery model change from both A and B, unless you understand the effects that has on your recoveryabilty. If you have space issues while the plan runs, increase your transaction log backup schedule for the period the plan runs in.
Setting to SIMPLE breaks your log chain and will cause you problems if (touch wood this never happens) you need to restore your database.
Have you configured SQL Agent to use the alert system, and restarted the agent so the config gets picked up? If not when the job fails, the notifiy operator will not work.
January 9, 2013 at 12:47 pm
Hi,
Thanks for your answer. The logs are being backed up every 20 min. Do you consider reducing this frequency (let’s say every 5 min) will avoid run out of space during the plan execution?
Regarding the email notifications to operators, as per your advice the alert system has been enable within the SQL Server Agent. That step was missing.
Thanks a lot for your help.
Regards
January 9, 2013 at 1:35 pm
During index rebuilds, yes I would change the log backup to every 5 minutes. Once finished, then revert back to the old schedule.
If you use one of the preferred methods I noted, you will see less log use because the defrag is far more intelligent than the maint plan version.
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
January 9, 2013 at 1:47 pm
Thanks so much for all your help. I'll implement your recommendations.
Regards
January 21, 2013 at 11:28 am
Hi All,
That's me again.
Finally we had the approval to implement the above recommendations but unfortunately we have now another issue this time. Let me explain what we have done and the current scenario.
After several recommendations and to not braking the log chain, before starting index rebuild tasks the DB is set to bulk-logged recovery mode. This task ran for more than 24hs causing this task to overlap with the full backup one (which runs outside the maintenance plan). By the time the backup is scheduled a huge backup log was generated (almost 56.00 GB) causing the drive unit got full. For extra clarifications we use an external backup tool to perform this backup log. At that point, the following actions have been performed:
A-Maintenance Plan job was stopped
B-Cleared up the drive unit where the backup logs files (*.CFT) are stored. 150 GB free space available.
C-Application running on that server stopped.
D- Set the DB recovery mode back to Full.
The issue is that the backup log file is growing continuously with not limit apparently. He have stopped backing log task to avoid the unit drive to get full again and by that time the CFT file was 112 GB.
No active jibs running on that sql server neither running activities are being shown in the activity monitor.
Do you have any idea why these huge files are being generated? How can I determine what could be causing this behavior.
Please let me know if some extra detail is required to clarify the situation.
Thanks in advance for all your support.
Regards
January 22, 2013 at 1:07 am
CFT file, that is usually an animation studio file.
How are you backing up the transaction log?
Does it write to the same file everytime a backup is run?
If so this is bad practise and can render your whole media set usless in the event of corruption.
Can you attach a screen shot of the transaction log backup step from the maintenance plan?
January 22, 2013 at 6:56 am
Hi Anthony, thanks for your response
After some researching I believe I've found the reason for the above described behavior. This is an extract of some info found in the web: "....changing the recovery mode to bulk logged before index rebuild, any extents that are allocated and changed through a minimally-logged operation are marked, and then the next transaction log backup will also read all those extents and include them in the backup" This is the cause of huge log backups files. Unfortunately no enough available free space left in the server and no idea how much the log backup file will need to grow after an index rebuild operation.
We are continue working to find the best schema for our maintenance plan task.
Thanks and regards
January 22, 2013 at 7:03 am
Unsure if you can do it in a maintenance plan, but if you can have a look at the sort in TempDB option, might not create as big of a log file.
If it cant be done in an MP, take a look at Ola's script, link in my signature, to which you can schedule it to be more intelligent than a MP index rebuild task.
January 22, 2013 at 7:04 am
Thanks Anthony!
Regards
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply