Archive

Archive for the ‘SQL’ Category

Cumulative update package 8 for SQL 2005 SP2

It seems there that CU#8 released on the 16th, you can get it from KB951217. I am applying it today, it seems to have a number of fixes in there.

here is a snippet from the KB article

SQL bug number
KB article number
Description

50001839
FIX: Only the import operation of the first batch is minimally logged when you import the data in multiple batches in SQL Server 2005

50002094
946032 (http://support.microsoft.com/kb/946032/LN/)
FIX: A data source view that connects to an Oracle database generates incorrect relationships when you develop a Microsoft SQL Server 2005 Analysis Services project by using BIDS

50002572
FIX: Error message when you run an MDX query after you synchronize a SQL Server 2005 Analysis Services database between two instances: “Current session is no longer valid due to structural changes in the database”

50002593
951194 (http://support.microsoft.com/kb/951194/LN/)
FIX: The hatch patterns are not consistent for all the groups between the legend and the data in a chart in a SQL Server 2005 Reporting Services report

50002658
FIX: An access violation occurs when you update a table through a view by using a cursor in SQL Server 2005

50002646
951190 (http://support.microsoft.com/kb/951190/LN/)
FIX: The transactional replication does not run the corresponding stored procedure at the subscriber when the stored procedure is run at the publisher in SQL Server 2005

50002655
952070 (http://support.microsoft.com/kb/952070/LN/)
FIX: You cannot receive notification messages from all the existing subscriptions after you restart the SQL Server service in SQL Server 2005

50002669
953547 (http://support.microsoft.com/kb/953547/LN/)
FIX: A time series prediction query takes a long time to run in SQL Server 2005 Analysis Services

50002676
An infinite recursion occurs in the CalculationPassValue function in SQL Server 2005 Analysis Services

50002692
In SQL Server 2005, subscriptions expire prematurely because the value of the genstatus column for the subscriptions is 4 in the Msmerge_genhistory table

50002710
953841 (http://support.microsoft.com/kb/953841/LN/)
FIX: E-mail replies are sent to the sender address instead of to the address that is specified in the database mail profile in SQL Server 2005 after you install the .NET Framework 2.0 Service Pack 1

50002727
Join filters in a merge replication do not replicate the rows that match the join filter clause to the partition after an update operation is performed on the rows in SQL Server 2005

50002731
953548 (http://support.microsoft.com/kb/953548/LN/)
FIX: An access violation occurs when you run two DMX queries against the same time series mining model at the same time in SQL Server 2005 Analysis Services

50002735
If a parent table of a join filter is updated on multiple rows, the rows that are inserted into the related table are not replicated to the partition in a merge replication of SQL Server 2005

50002736
953625 (http://support.microsoft.com/kb/953625/LN/)
FIX: Error message when SQL Server 2005 synchronizes a mirrored database: “Expression: result == LCK_OK”

50002752
When you export a report that contains a .png image file to a .pdf file in SQL Server 2005 Reporting Services, the image is displayed in bad quality

50002753
The Database Mail feature in SQL Server 2005 may access data in the memory that is already freed

50002759
API server cursors that implement pessimistic concurrency control may cause deadlocks and may generate incorrect results

50002760
952330 (http://support.microsoft.com/kb/952330/LN/)
FIX: The Replication Log Reader Agent may fail intermittently when a transactional replication synchronizes data in SQL Server 2005

50002762
953496 (http://support.microsoft.com/kb/953496/LN/)
FIX: CPU utilization is high when you run a trace that contains a text filter in SQL Server 2005

50002773
An access violation occurs in a PFString object because the sm_mskDefaultSettings mask for the PFString object is incorrect in SQL Server 2005 Analysis Services

50002786
During a merge replication in SQL Server 2005, the sp_Msmakegeneration stored procedure may always run

50002790
952624 (http://support.microsoft.com/kb/952624/LN/)
FIX: Error message when you run a query that contains many OR clauses in SQL Server 2005: “There is insufficient system memory to run this query”

50002812
953491 (http://support.microsoft.com/kb/953491/LN/)
FIX: Duplicate data is written to other flat files in an SSIS package that contains a Foreach Loop container

50002814
952723 (http://support.microsoft.com/kb/952723/LN/)
FIX: Error message when you use the bcp utility to run the sp_send_dbmail stored procedure in SQL Server 2005 Service Pack 2 Cumulative Update 5: “Error = [Microsoft][SQL Native Client]Function sequence error”

50002819
953852 (http://support.microsoft.com/kb/953852/LN/)
FIX: You cannot register a 64-bit local instance by clicking “Update Local Server Registration” in SQL Server Management Studio in SQL Server 2005

50002828
953721 (http://support.microsoft.com/kb/953721/LN/)
FIX: The performance of a query in SQL Server 2005 Service Pack 2 Cumulative Update 6 is slow

50002830
The MeasureGroupMeasures function does not work correctly in SQL Server 2005 Analysis Services

50002836
953497 (http://support.microsoft.com/kb/953497/LN/)
FIX: Error message when you call a table valued function that uses a common language runtime (CLR) assembly in SQL Server 2005: “AppDomain <ApplicationDomain> is marked for unload due to memory pressure”

50002841
953321 (http://support.microsoft.com/kb/953321/LN/)
FIX: Error message when you restart the SQL Server service or the SQL Server Agent service in SQL Server 2005: “Unable to start mail session”

50002849
953552 (http://support.microsoft.com/kb/953552/LN/)
FIX: Error message when you run an UPDATE CUBE statement against a write-enabled cube in SQL Server 2005 Analysis Services: “Errors in the high-level relational engine”

50002851
952233 (http://support.microsoft.com/kb/952233/LN/)
FIX: All the MDX queries that are running on an instance of SQL Server 2005 Analysis Services are canceled when you start or stop a SQL Server Profiler trace for the instance

50002854
When two threads call the sp_Msallocate_new_identity_range stored procedure at the same time in a merge replication, INSERT operations cannot be performed on the publisher in SQL Server 2005

50002862
If you perform many DELETE operations and many INSERT operations on a database that has the READ_COMMITTED_SNAPSHOT database option set to ON, the size of the database increases greatly

50002864
953569 (http://support.microsoft.com/kb/953569/LN/)
FIX: The compilation time of some queries is very long in an x64-based version of SQL Server 2005

50002865
Error message when you delete a row from a table that has a nonclustered primary key if the table is referenced by a foreign key of another table that uses the ON DELETE SET option: “Internal Query Processor Error: The query processor could not produce a query plan”

50002876
FIX: An access violation occurs when you run an XMLA query in SQL Server 2005 Analysis Services

50002890
953950 (http://support.microsoft.com/kb/953950/LN/)
FIX: Error message when you run an INSERT statement in SQL Server 2005: “Cannot insert an explicit value into a timestamp column”

50002907
When multiple Log Reader Agents are running, the computer may stop responding

50002687
SQL Native Client incorrectly retrieves data of the sql_variant data type
Note This issue is fixed in the stand-alone package of SQL Native Client that is released together with Cumulative Update 8.

50002689
FIX: The user name and the password of a SQL Server account is exposed in plain text in memory when you use SQL Native Client to connect to an instance of SQL Server 2005
Note This issue is fixed in the stand-alone package of SQL Native Client that is released together with Cumulative Update 8.

50002716
952135 (http://support.microsoft.com/kb/952135/LN/)
FIX: When you connect to an instance of SQL Server 2005 by using the SQL Native Client ODBC driver, the SQL_PARAM_IGNORE setting has no effect
Note This issue is fixed in the stand-alone package of SQL Native Client that is released together with Cumulative Update 8.

50002804
50002784
<TrustInfo> tags are add to the manifest file (MT_ADDITIONALMANIFEST) for the bcp utility. The <TrustInfo> tags require independent software vendors to obtain logo certification for applications that use SQL Server Express Edition
Note This issue is fixed in the stand-alone package of SQL Native Client that is released together with Cumulative Update 8.

Categories: SQL

SQL 2005 SQL Server Agent (Disabled) and won’t start

June 18, 2008 1 comment

Ok, I thought I would post about this as it caught me by surprise today, We have a SQL 2005 Std server that I use to host our main databases and our Data Warehouse.

It has all be running fine for a while, and today my MD commented that our reports show last Thursdays figures as incomplete, which I considered impossible as our Data Warehouse does a full load every morning and if there were figures for Friday then Thursday must be in there as well, well in my world!!

But when looking at the server I noticed that the SQL Agent was disabled and I could not start it, nore could I see the jobs on the server. I spent a few hrs and search the web for a while trying to figure out why the server now has the agent as disabled.

I found a error in the SQLagent,out found in the logging folder in my case C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\

2008-02-08 13:40:26 – ! [298] SQLServer Error: 5845, Address Windowing Extensions (AWE) requires the ‘lock pages in memory’ privilege which is not currently present in the access token of the process. [SQLSTATE 42000] (DisableAgentXPs)
2008-02-08 13:40:26 – ! [298] SQLServer Error: 15281, SQL Server blocked access to procedure ‘dbo.sp_sqlagent_has_server_access’ 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. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
2008-02-08 13:40:26 – ! [298] SQLServer Error: 15281, 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. [SQLSTATE 42000]
2008-02-08 13:40:26 – ? [100] Microsoft SQLServerAgent version 9.00.3042.00 ((Unknown) unicode retail build) : Process ID 5552
2008-02-08 13:40:26 – ? [101] SQL Server  version 9.00.3054 (0 connection limit)
2008-02-08 13:40:26 – ? [102] SQL Server ODBC driver version 9.00.3042
2008-02-08 13:40:26 – ? [103] NetLib being used by driver is DBNETLIB.DLL; Local host server is SQLSRV\SQL
2008-02-08 13:40:26 – ? [310] 4 processor(s) and 3071 MB RAM detected
2008-02-08 13:40:26 – ? [339] Local computer is SQLSRV running Windows NT 5.2 (3790) Service Pack 2
2008-02-08 13:40:26 – ! [000] SQLServerAgent must be able to connect to SQLServer as SysAdmin, but ‘(Unknown)’ is not a member of the SysAdmin role
2008-02-08 13:40:26 – ? [098] SQLServerAgent terminated (normally)

The problem seems to be the first line, I do not know what happened, if it was a update or something else, updates were applied last week and a reboot was preformed.

to fix follow these steps

 

  1. On the Start menu, click Run. In the Open box, type gpedit.msc.

    The Group Policy dialog box opens.

  2. On the Group Policy console, expand Computer Configuration, and then expand Windows Settings.

  3. Expand Security Settings, and then expand Local Policies.

  4. Select the User Rights Assignment folder.

    The policies will be displayed in the details pane.

  5. In the pane, double-click Lock pages in memory.

  6. In the Local Security Policy Setting dialog box, click Add.

  7. In the Select Users or Groups dialog box, add an account with privileges to run sqlservr.exe.

I added the local admins group and SQL server service account.

 

Hope this helps

Categories: SQL