Category: SQL Server

  • sys.dm_clr_properties

    sys.dm_clr_properties: Returns a row for each property related to SQL Server common language runtime (CLR) integration, including the version and state of the hosted CLR.

    Enabling CLR using SP_Configure doesn’t gurantee that it is initialized. To initialize CLR, you have to execute ASSEMBLY statements, CLR Routine Type or Trigger.

    See below image for the output of this DMV.


    Below are the different state of CLR.

    • Mscoree is not loaded.
    • Mscoree is loaded.
    • Locked CLR version with mscoree.
    • CLR is initialized.
    • CLR initialization permanently failed.
    • CLR is stopped

    Check http://msdn.microsoft.com/en-us/library/ms174983.aspx link for different CLR state description.

  • Steps to clear the server list in SQL Server Management Studio

    Check out my new article

    Steps to clear the server list in SQL Server Management Studio on MSSQLTIPS.com.

    http://mssqltips.com/tip.asp?tip=2319

     


  • Policy Based Management in SQL Server 2008

    Policy-base Management is a new feature in SQL Server 2008 that helps SQL Server administration. It allows us to define and enforce policies for configuring and managing SQL Server across the enterprise. The policy-based framework implements the policies behind the scene with a Policy Engine, SQL Server Agent jobs, SQLCLR, DDL triggers and Service Broker. Policies can be applied or evaluated against a single server or a group of servers.

    To understand PBM we have to understand the below terms.
    Target – an entity that is managed by PBM (i.e. database, table, index, etc.)
    Facet – a predefined set of properties that can be managed
    Condition – a property expression that evaluates to facets on target and returns True or False;
    Policy – a condition to be checked and/or enforced

    Expand the Management Node to use the PBM.

    Expand the facets node to checkout the facets.

    To Check out facets properties double click on facets. I have clicked on Login Facets. These facet properties are used to specify a condition

  • Undocumented Server Property ErrorLogFileName

    You can use Server Property errorlogfilename to get the error log file path.

    SELECT SERVERPROPERTY(‘ErrorLogFileName’)

  • How to Hide System Objects in Object Explorer?

    Open SSMS -> GO TO Tools -> Option and Check the below highlighted check box and restart the Management Studio. It will hide all the system objects in Object Explorer.