Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Maintenance Plans issues in SQL Server 2005 Expand / Collapse
Author
Message
Posted Tuesday, January 8, 2013 9:38 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 22, 2013 7:04 AM
Points: 7, Visits: 9
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
Post #1404537
Posted Tuesday, January 8, 2013 10:24 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:27 PM
Points: 21,631, Visits: 15,289
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1404544
Posted Wednesday, January 9, 2013 6:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 22, 2013 7:04 AM
Points: 7, Visits: 9
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!
Post #1404723
Posted Wednesday, January 9, 2013 6:21 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:20 AM
Points: 5,218, Visits: 5,067
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.




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1404727
Posted Wednesday, January 9, 2013 12:47 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 22, 2013 7:04 AM
Points: 7, Visits: 9
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
Post #1404955
Posted Wednesday, January 9, 2013 1:35 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:27 PM
Points: 21,631, Visits: 15,289
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1404987
Posted Wednesday, January 9, 2013 1:47 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 22, 2013 7:04 AM
Points: 7, Visits: 9
Thanks so much for all your help. I'll implement your recommendations.

Regards
Post #1404995
Posted Monday, January 21, 2013 11:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 22, 2013 7:04 AM
Points: 7, Visits: 9
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
Post #1409659
Posted Tuesday, January 22, 2013 1:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:20 AM
Points: 5,218, Visits: 5,067
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?




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1409845
Posted Tuesday, January 22, 2013 6:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 22, 2013 7:04 AM
Points: 7, Visits: 9
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
Post #1410011
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse