SQL Server 2005 SP3

  • I first installed it on a couple of non clustered instances.

    I don't perform unattended installs .... must be my age 😉

    Basic scenario on our cluster:

    - move over all instances to a single node

    - log backup and full backup of all databases (including system dbs)

    - activate the correct version of sp3.exe

    - test the instances for functionality

    - shut down all instances

    - reboot the node

    - startup the instances on the node that has been upgraded

    - apply sp3 to the second node, so all client software is upgraded to.

    (although it didn't present the client software to be upgraded, I still applied it because client software (ssms) on a cluster requires an extra install on each node)

    - reboot second node

    - balance the instances to their designated node.

    - log backup and full backup of all databases (including system dbs)

    (I renamed the old backup files for safety)

    - after backups did complete, app. teams were informed they could test and relaunch their systems.

    No problems noted.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Thanks for your tips!

  • Hi All,

    We successfully installed SP3 on clustered environment with no issues in production. We have a/a/p cluster setup,On node1 we have 1 instance and node 3 we have 4 instances. Everything is fine but Iam seeing a message in Monitoring tool after applying SP3, which says

    SeverityAlarmRaisedMessage

    InformationAgent Job List01/25/2009 11:31:46 AMSQLServer Error(15281) SQL Server blocked access to procedure 'sys.xp_sqlagent_enum_jobs' of component 'Agent XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Agent XPs' by using sp_configure. For more information about enabling 'Agent XPs', see 'Surface Area Configuration' in SQL Server Books Online.

    Could you plz advice me what should this error mean and what action I should take???

    Thanks

  • - your sqlagent has not been started (cluster manager)

    (do you use SQLAgent ?)

    - you can enable these procs this way:

    -- enable Agent XPs

    EXEC sp_configure 'Agent XPs','1'

    RECONFIGURE

    GO

    This way you can manage jobs, .. without having the sqlagent service active.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Sql server agent is running fine and the jobs are also running fine But still Iam getting this 15281 error

    Plz advice me

  • Did you try to run the script that ALZDBA gave you?

  • No, I did not run so far..because the agent is running fine. So even it is running fine do I need run this script?

    Its happened after applying SP3. We have 1 instance on node1 and 4 insatances on node3.

    The instance on node1 has not showing this message but the other 4 instances showing this error.

    And from BOL:

    Use the Agent XPs option to enable the SQL Server Agent extended stored procedures on this server. When this option is not enabled, the SQL Server Agent node is not available in SQL Server Management Studio Object Explorer.

    When you use the Surface Area Configuration tool to start the SQL Server Agent service, these extended stored procedures are enabled automatically. For more information, see Surface Area Configuration.

    Note:

    SQL Server Management Studio Object Explorer does not display the SQL Server Agent node unless these extended stored procedures are enabled regardless of the SQL Server Agent service state.

    But In my case, iam able to see the SQL Server Agent node .So this means, the monitoring tool is showing wrong thing?

    How it effects if Agent XPs option is disabled? what kind of activities we can not perform if it disabled?

  • madhu.arda (1/26/2009)


    ....

    But In my case, iam able to see the SQL Server Agent node .So this means, the monitoring tool is showing wrong thing?

    How it effects if Agent XPs option is disabled? what kind of activities we can not perform if it disabled?

    Can you post the results of:

    -- show Agent XPs state (0/1)

    EXEC sp_configure 'Agent XPs'

    GO

    If you sqlagent for the instance is running, you should be able to browse the sqlagent node if you are authorized (sqlagent roles in msdb)

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Thank You,

    -- show Agent XPs state (0/1)

    EXEC sp_configure 'Agent XPs'

    GO

    Result:

    Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51

    The configuration option 'agent XPs' does not exist, or it may be an advanced option.

    What will be the next step plz

  • madhu.arda (1/27/2009)


    Thank You,

    -- show Agent XPs state (0/1)

    EXEC sp_configure 'Agent XPs'

    GO

    Result:

    Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51

    The configuration option 'agent XPs' does not exist, or it may be an advanced option.

    What will be the next step plz

    Indeed, it is an advanced option.

    I should have included that in the first script :blush:

    To show advanced options you must execute:

    -- show advanced configuration options

    EXEC sp_configure 'show advanced options', '1'

    RECONFIGURE

    GO

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Thank you,

    -- show advanced configuration options

    EXEC sp_configure 'show advanced options', '1'

    RECONFIGURE

    GO

    RECONFIGURE;

    EXEC sp_configure;

    Result:

    name min maxconfig_value run_value

    Agent XPs01 1 1

    Thank You

  • it is strange that F5 (refresh) on SQLagent or a disconnect/connect doesn't solve the issue.

    Are you performing this on a client or at one of the nodes of the cluster ?

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • I did this by going to the active node and connecting virtualserver\instance and management studio, query editor

  • madhu.arda (1/28/2009)


    I did this by going to the active node and connecting virtualserver\instance and management studio, query editor

    Is his on the node where you implemented the SP3 or on one of the other nodes ?

    Did you apply sp3 (for the client software (ssms)) to all nodes ?

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Hi,

    Any patches / fixes / updates released after SP3 (2005.90.4035) ?

    Thanks in advance

    Regards

Viewing 15 posts - 106 through 120 (of 178 total)

You must be logged in to reply to this topic. Login to reply