# Tuesday, February 09, 2010

Sql 2008 and 'Agent XPs' component is turned off as part of the security configuration of this server when creating a Maintenance Plan

If you try to create a Maintenance Plan on Sql 2008 you may get the following error:
'Agent XPs' component is turned off as part of the security configuration of this server. A system administrator can enable the use of 'Agent XPs' by using sp_configure....

The reason is simple - the Sql Server Agent Service, which handles Maintenance plans among other things, is not started automatically. The service is set to Manual start. The solution is simple - just set it to Automatic (so it starts in case of a reboot) and start it up!

You can, of course, do this through windows services, but you can also do it through the Sql tools - specifically the "Sql Server Configuration Manager".

Step 1 - open up the Sql Server Configuration Manager and click on 'Sql Server Services in the left pane. You will see that the SQL Server Agetn is Stopped and has its 'Start Mode' set to Manual.




Step 2 - double click on the Sql Server Agent in the right tab and its properties will appear. Click on the 'Start' button to begin it running.




Step 3 - now click on the 'Service' tab and change the Start Mode to Automatic. This will ensure that if your server restarts this service will start as well (and hence you will get your Maintenance plans.




Now, give yourself a raise and take the rest of the day off....
Sql | Sql 2008
#    Comments [0] | Trackback
# Tuesday, November 10, 2009

Sql 2008 Disk Allocation Unit Size

Installing a new sql 2008 box and thought I would throw out the reminder to partition the drives that sql uses to 64k (not the default 4k) due to Sql's 'extents', which come in packages of eight 8k boxes...

Sql
#    Comments [0] | Trackback
# Tuesday, June 02, 2009

Cannot open database requested in login 'SharedServicesDatabase'. Login fails. Login failed for user MYDOMAIN\MyServer$

You may get an error like:
 
Cannot open database requested in login 'SharedServicesDatabase'. Login fails.
Login failed for user ' MYDOMAIN\MyServer$'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Cannot open database requested in login 'SharedServicesDatabase'. Login fails.
Login failed for user ' MYDOMAIN\MyServer$'.
 
This is triggered by the odd fact (which I can not find documented anywhere) that the server that is housing the Sharepoint site must have its computer account listed as a user within the Shared Services database for the search to function.

Sharepoint 2007 | Sql
#    Comments [0] | Trackback
# Monday, May 18, 2009

Sharepoint Backup Permissions or SqlException: Cannot open backup device '\\myserver\backups\spbr0000\00000024.bak'. Device error or device off-line

For those of you who are using Sharepoint's 'built-in' backup feature, you probably already know that one of the biggest problems with it is that you can not automate it directly. In order to do that you need to used AT ->
AT 01:00 /every:1,2,3,4,5,6,7 cmd /c "stsadm.exe -o backup -directory file://myserver/mybackupshare -backupmethod full"
 
This is not a showstopper. It works. However unless you have the correct perms it will fail. Or, to be more specific, the Sql Service Account needs to be able to read/write to that share. Again, this is the account that the Sql Service runs under. Hopefully a domain account.
 
Also, if you are going to run it from the Sharepoint Central Administration Website, the account you are logged in with needs those perms as well.
Sharepoint 2007 | Sql
#    Comments [0] | Trackback