Lock Pages In Memory SQL 2005/2008 Standard on 64bit

When you grant the SQL Server Service Account the ability to 'Lock Pages in Memory' with SQL 2005/2008 Standard on 64bit systems you need to enable a Trace Flag in the Startup Parameter for the SQL Service:

"Lock pages in memory" comes as a trace flag that can be enabled on the following cumulative updates:
CU2 for SQL Server 2008 SP1 => http://support.microsoft.com/kb/970315/en-us
CU4 for SQL Server 2005 SP3 => http://support.microsoft.com/kb/970279/en-us

I will not explain here how to Enable the 'Lock Pages In Memory' at the Windows level as there are plenty of Blogs on how to do this: See MSDN article http://msdn.microsoft.com/en-us/library/ms190730.aspx

To add the Trace Flag to the Startup Parameter of your SQL Server Service, follow these steps:

1. Open Configuration Manager for SQL Server.
2. Browse to the SQL Server Service, Right Click and Select Properties.
3. Click on the Advanced Tab.
4. Select Startup Parameters and add the Trace Flag -T845. It should look something like this: -dG:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\master.mdf;-eE:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lH:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mastlog.ldf;-T845 Obviously your file locations will be different and you may already have other Global Trace Flags set up.... If you do have another Trace Flag just add a ; after your existing Trace Flag and add the new -T845 after this.
5. Once the Trace Flag has been added to the Startup Parameter you will need to restart the service for the change to be applied.

If you need to apply this change temporarily for all sessions you can use the DBCC TRACEON command.

DBCC TRACEON(845, -1)

GO

To check the status of any Trace Flags running on your server, run the following DBCC Command:

DBCC TRACESTATUS