Welcome to the world (new face) of Knowledge Sharing Network.
To track the older blog posts refer to our historical knowledge sharing site  and you will find this as your destination for SQL Server knowledge pool.

Follow SQLMaster on Twitter SqlServer-QA.net - Knowledge Sharing Network (@sqlmaster)

January 2009 - Posts - SQL Server Knowledge Sharing Network (@sqlmaster)

January 2009 - Posts

Cumulative Update package 3 for SQL Server 2008, another instalment of hotfixes!

Here comes another instalment of Cumulative Update package 3 for SQL 2008 version and this contains hotfixes for the issues that have been fixed since the release of SQL Server 2008.

As usual the Microsoft guidance refers that this is a supported cumulative update package and it is intended to correct only the problems that are described in the KBA960484 article. (for some reason I was not able to view the referred KBA, hence giving out the list of releated KB articles below):

 

 

VSTS bug number

KB article number Description

263002

958760  (http://support.microsoft.com/kb/958760/ )

FIX: You may experience issues when you try to expand the Databases node in SQL Server Management Studio, or the sp_spaceused stored procedure takes a long time to run in SQL Server 2008

263017

958940  (http://support.microsoft.com/kb/958940/ )

FIX: The Disk I/O Usage chart and the Network Usage chart contain incorrect data when you view the Server Activity History report in SQL Server 2008

262998

958947  (http://support.microsoft.com/kb/958947/ )

FIX: When you run multiple full-text search queries in parallel in SQL Server 2008, the queries take much longer to finish than if you run them sequentially

263209; 263229

959893  (http://support.microsoft.com/kb/959893/ )

FIX: Some changes may not be replicated to the subscriber correctly in SQL Server 2005 or in SQL Server 2008 when the sum of the parameters exceeds 2,100

263228

960283  (http://support.microsoft.com/kb/960283/ )

FIX: You may encounter a deadlock scenario when you use table variables in SQL Server 2005 or in SQL Server 2008

263221

960542  (http://support.microsoft.com/kb/960542/ )

FIX: When you run the ALTER TRIGGER statement for a trigger in a merge publication in SQL Server 2005 or in SQL Server 2008, a change is not replicated to the subscriber database

263227

960543  (http://support.microsoft.com/kb/960543/ )

FIX: The x64 version of SQL Server 2005 or of SQL Server 2008 may stop responding when you are performing a backup

263217

960562  (http://support.microsoft.com/kb/960562/ )

FIX: Error message when you run two MDX queries on different connections at the same time in SQL Server 2005 Analysis Services or in SQL Server 2008 Analysis Services: "Transaction errors: The lock operation ended unsuccessfully because of deadlock"

263218

960566  (http://support.microsoft.com/kb/960566/ )

FIX: The chart of a mining model is empty when you browse the mining model in Excel 2007 by using Microsoft SQL Server 2008 Data Mining Add-ins for Microsoft Excel

263220

960571  (http://support.microsoft.com/kb/960571/ )

FIX: The Distribution Agent fails when you perform DML operations in a publication article and a concurrent snapshot is created in SQL Server 2005 or in SQL Server 2008

263090

960573  (http://support.microsoft.com/kb/960573/ )

FIX: Error message when you try to create a WMI event alert by using the sp_add_alert stored procedure in SQL Server 2008: "The @wmi_query could not be executed in the @wmi_namespace provided"

263222

960574  (http://support.microsoft.com/kb/960574/ )

FIX: Error message when you run the Snapshot Agent for a transactional replication that uses an Oracle 11g publisher in SQL Server 2005 or in SQL Server 2008: "Msg 21613, Level 16, State 1, Procedure sp_IHsyncmetadata, Line 223"

263223

960575  (http://support.microsoft.com/kb/960575/ )

FIX: Some DML statements that should not be replicated are replicated to the subscribers when you replicate the execution of a stored procedure in a transactional replication in SQL Server 2005 or in SQL Server 2008

263097

960770  (http://support.microsoft.com/kb/960770/ )

FIX: You receive error 605 and error 824 when you run a query that inserts data into a temporary table in SQL Server 2008

263106

960771  (http://support.microsoft.com/kb/960771/ )

FIX: Some attributes of a nested table do not appear in a SQL Server 2008 Data Mining model when the data source is a cube

263096

960864  (http://support.microsoft.com/kb/960864/ )

FIX: Error message when you view a data mining model in Mining Model Viewer in SQL Server 2008 Analysis Services: "Exception has been thrown by the target of an invocation.Item has already been added"

263211

960996  (http://support.microsoft.com/kb/960996/ )

FIX: When you use a ReportViewer control to view a SQL Server 2008 Reporting Services report that contains two pages, the formatting for the second page is lost

267583

961094  (http://support.microsoft.com/kb/961094/ )

FIX: Error message when you try to connect to a cube by using a user account that belongs to multiple database roles in SQL Server 2008 Analysis Services: "Two sets specified in the function have different dimensionality"

263105

961126  (http://support.microsoft.com/kb/961126/ )

FIX: Error message when you run a maintenance plan in SQL Server 2008: "The SQL Server Execute Package Utility requires Integration Services to be installed"

263202

961271  (http://support.microsoft.com/kb/961271/ )

FIX: In a SQL Server 2008 Reporting Services report that contains several levels, some items disappear when you collapse another item

263087

961272  (http://support.microsoft.com/kb/961272/ )

FIX: A maintenance plan that contains a Rebuild Index task takes much longer than you expect to run in SQL Server 2008

263094

961283  (http://support.microsoft.com/kb/961283/ )

FIX: An access violation may occur when you run a query that uses a parallel execution plan in SQL Server 2008

263115

961323  (http://support.microsoft.com/kb/961323/ )

FIX: The memory usage increases gradually when you frequently back up or restore a database in SQL Server 2008

262980

961359  (http://support.microsoft.com/kb/961359/ )

FIX: You receive an error message when you try to parse XML data in SQL Server 2008

263141

961360  (http://support.microsoft.com/kb/961360/ )

FIX: The page numbers may be hardcoded in the pages after you export a report to Excel format in SQL Server 2008

263100

961393  (http://support.microsoft.com/kb/961393/ )

FIX: When you print a report by using the SQL Server 2008 Reporting Services Client-Side Printing functionality, the spacing is too small or too large

263207

961481  (http://support.microsoft.com/kb/961481/ )

FIX: A database audit specification is set to the sysrowsets object instead of the schema object that you specified in SQL Server Management Studio in SQL Server 2008

263122

961699  (http://support.microsoft.com/kb/961699/ )

FIX: Error message when you perform a partition merge in SQL Server 2008 Analysis Services: "Detected inconsistency between User define slice and detected slice of partition"

Not only it is guidance from Microsoft, all about the best practices that dictates about these cumulative update packaages must have additional testing within your pre-production environment. You may not know sometimes it might bring up new problem after applying these hotfixes, so if you are not severely affected by any of these problems then it is better to stick for the next SQL Server 2008 service pack that contains the hotfixes in this cumulative update package.

The referred KBA960484 above gives the complete list of files that are modified/affected with these cumulative update hotfixes, make sure you follow them correctly. Lastly here is another revision of Incremental Servicing Model that is available from the SQL Server team to deliver hotfixes for reported problems.

SQL Server 2008 Policy Based Management - managing and troubleshooting policy failures

Policy Based Management (PBM) is an effective system for DBAs to manage one or more instances of SQL Server. Just to give a brief overview of what it consists, it consists 3 components:

  • Policy management for Administrators to create policies
  • Explicit administration for Administrators to select multiple targets to check whether they comply with a specific/multiple policy(ies)
  • Evaluation modes based upon the schedule and execution of  defined policies that is automated

Policy-Based Management relies on DDL triggers to detect and roll back DDL operations that do not comply with policies that use this evaluation mode. Removing the Policy-Based Management DDL triggers or disabling nest triggers, will cause this evaluation mode to fail or perform unexpectedly. A policy depends upon target, facet with the condition that was setup by the Administrators. How do you know whether a policy is effective or not? Its simple, such  policies of a target are those policies that govern this target. A policy is effective with regard to a target only if all the following conditions are satisfied: [The policy is enabled], [The target belongs to the target set of the policy] and [The target or one of the targets ancestors subscribes to the policy group that contains this policy].

In this case I would like to refer the important objects that exists within the MSDB database for displaying information about Policy-Based Management policies, conditions, expressions, groups, and filters, obviously these views are owned by the dbo schema (by default). These are:

syspolicy_conditions (Transact-SQL)

syspolicy_policies (Transact-SQL)

syspolicy_system_health_state (Transact-SQL)

The above 3 views that I use regularly to see required information about the policies that are defined within the Central Management server. As you can see the above are referenced from BOL for your information in order to see what they do and what for they are used. Now coming the important part of troubleshooting the policies that are used to make the SQL platform effective, such Policy-Based Management records errors to both the Windows event log and, for scheduled policies, the SQL Server Agent log. If policies are not enabled or do not effect the expected target, the failure is not considered an error and is not logged. 

So how do you troubleshoot to see what happened for the failure of any policy application, in any such troubleshooting exercise it is better to look for root cause on step by step. As you may aware that a policy will be using the mode of On change: log or On change_prevent and to get this affective you must ensure that such policy is 'not' disabled or the target does not subscribe to the policy group that contains the policy. Only in the case of  On change: prevent evaluation mode, the Service Broker eventing mechanism is not monitoring the correct events and it might fail due to the failure in the evaluation engine. Once you find the basic steps you can obtain the following troubleshooing process (as clearly referred in BOL):

  1. To see whether the policy executed with exception messages, check the policy execution history in the msdb.dbo.syspolicy_policy_execution_history view.
  2. To see whether the policy executed for the specific target, check the policy execution history for the specific target in question in the msdb.dbo.syspolicy_policy_execution_history_details view.
  3. To verify that the policy applies to the target, use the View Policies page in SQL Server Management Studio.
  4. Check the Windows event log for a Policy-Based Management error.

So in any case you must ensure that SQLAgent service must be up and running the central monitoring server to ensure all the Policies can be effective, in order to ensure that this is not happening again and again make sure the Service account that is used to start this service has got correct password set and set to NOT EXPIRED on the account status within the Active Directory. By now you would agree that monitoring and troubleshooting of such management aspect is simple/easy when you adopt the process correctly.