SQL Server blocked access to procedure 'dbo.sp_sqlagent_get_startup_info' of component 'Agent XPs'

SQL Server blocked access to procedure 'dbo.sp_sqlagent_get_startup_info' 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.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE
GO

I was getting a "the user cannot access resource due to not having rights on Locked in Memory"

The solution to the problem is:

On the Start menu, click Run. In the “Open” box, type gpedit.msc.
On the “Group Policy” console, expand Computer Configuration, and then expand Windows Settings.
Expand Security Settings, and then expand Local Policies.
Select the User Rights Assignment folder.
In the right details pane, double-click Lock pages in memory.

Add the relevant account to the permissions list on this policy, the account should be the start up account for the SQL Service

Windows jobs

Resilience

Quite often , I'm asked by managers , about the resilience of any given SQL Server system I may be working on. Ultimately resilience relates to high availability , specifically maintaininbg user connections.

To ensure an agreed level of resilience , a DBA has to look beyond SQL Server. The whole application stack needs to be monitored i.e sql server app, sql server , OS,Hardware,Network .

SQL Server 2005 has built in capacity to deal with resilience . 1)BackUp Log Shipping 2)Failover Clustering, 3)Mirroring

By the far the cheapest approach is BackupLog Shipping . In essence , it is  the regular transfer of transaction logs to a copy of the database , where they are stored or applied immediately to the copy. If a failure happens , then the copy can go live

Mirroring is more resilient but also more expensive. In effect , a sychronised copy of the database is maintained . Normally , the mirror and original can share the load, for example, one can handle the transaction side of things , the other can handle reporting

Capacity Planning

There are certain best practises to follow when considering capacity planning a SQL Server Production envirobnment.

1)Check that the hardware is modular and will facilitate hardware upgrades ,if the databases outgrow the the current hardware set up

2)Carry out a realistic load test on the prospective hardware to gauge the performance of the hardware .

3)Ensure the hardware is powerful enough to allow the efficient use of new caharacteristics / features of SQL Server

Capacity jobs

Setting Remote Auditing For SQL Server

Recently , I had to use osql for some remote auditing which I found useful. For those who don't know ,osql is a standard app that comes with SQL Server 2000 (T-SQL statements, system procedures, and script files can be executed). I had to audit some login activity for a suspected security breach. Essentially I was analysing attempted and failed login attempts . To configure this auditing, open Enterprise Manager, select a database server, right-click Properties, go to the Security tab, and set the required audit level. I recommend that you don't audit to the Application log; otherwise, SQL Server writes audit information about user-login activity to two places simultaneously and unnecessarily degrade system performance. After changing audit settings, restart the database.

Use the osql command line to enable auditing. You can use this tool to enable auditing on remote SQL Server systems. If you used an account with DBA privileges on the target database server to log on to Windows, type:
osql -S -E

-S tells osql.exe to connect to a remote server and myservername is the name of the database server or instance. The -E option tells osql.exe to use the credentials with which you logged on to Windows to establish a trusted database connection. If you didn't use an account with DBA privileges, you need to type:
osql -S
-U
-P

-U tells osql.exe that the following argument is the username (DBA privileges required) and -P signals that the next argument is the password for the specified account.

After enabling auditing for the default database or for an instance, the database server logs all activity to the data directory Jack Vamvas specified during the installation process.A disadvantage of using this method for audit log information is that it captures everything. To achieve a granular approach to this types of auditing, you'll need to turn to Server-Side Traces.

SQL Server writes all auditable activity to a file with the format audittrace_YYYYMMDDHHMMSS.trc, where YYYYMMDDHHMMSS is the log's creation time by year, month, day, hour, minute, and second

You can use SQL Server Profiler to view the log files that SQL Server creates.

SQL Server 2000 and SAP Business One

Please search for the whitepaper below.
"SAP with Microsoft SQL Server 2005: Best Practices for High Availability, Maximum Performance, and Scalability"
You should find some of the answers there even though it is for SQL Server 2005.
Also, check out the resources at http://microsoft-sap.com.

SQL Server: Database Size

I was inspecting a database data file which was about 80mb in size. However, the transaction log file which appeared to be exceeding 1gb.

The client had been experimenting with different ways of decreasing the size of the log files in a bid to restore some hard disk free space.

As this was a production server, I made sure the transaction log was not on truncate only. If you select the simple recovery model you are essentially saying that you want to truncate the log on checkpoint; removing all committed transactions from the log. Take a look at the sp_dboption and you will see that trunc. log on ckpt. is an option. Also, notice that in your simple recovery model you have the sp_dboption set to truncate the log on checkpoint.

My preferred choice is to leave the database model to Full Recovery mode and schedule regular backups of the transaction log. Periodically I recommend doing a scheduled shrinkfile on the transaction log during off hours setting turning the autoshrink option off. This is more of a performance gain if anything; but you will have to analyse the performance gain against giving in a little on disk space.

Every scheduling situation is different, but the concepts above will keep you minimise problems when you actually need the transaction log.