aiDeeva.com

Tag: SQL Tips and Tricks

  • Get the List Of DBCC Commands & Syntax

    If you don’t remember all the DBCC commands “NO Issue” execute the DBCC HELP command to get the list of all the DBCC.

    DBCC HELP('?')

    checkalloc
    checkcatalog
    checkconstraints
    checkdb
    checkfilegroup
    checkident
    checktable
    cleantable
    dbreindex
    dropcleanbuffers
    free
    freeproccache
    freesessioncache
    freesystemcache
    help
    indexdefrag
    inputbuffer
    opentran
    outputbuffer
    pintable
    proccache
    show_statistics
    showcontig
    shrinkdatabase
    shrinkfile
    sqlperf
    traceoff
    traceon
    tracestatus
    unpintable
    updateusage
    useroptions

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    Don’t remember syntax, execute below command to get the syntax.

    DBCC HELP('checktable')

    DBCC

  • Script to get the SQL Agent Properties

    You can use the sp_get_sqlagent_properties undocumented stored procedure to retrieve the SQL Agent properties of a SQL Instance. It is available in SQL Server MSDB database. This procedure only works if the SQL Agetn Service is started.

    SQLAgent

  • sys.dm_server_registry

    sys.dm_server_registry DMV gives information about installation and configuration data that is stored in the windows registry for the current instance of SQL server.

    You can use to check the start up parameters, protocols, error log configuration, agent log configuration and IP Addresses.

    SQLReg

  • Script to check the database Properties

    It is always good to have the idea about the database properties while doing the migration, performance tuning or configuration.

    You can execute the below script to get the database property information.

    select 
 sysDB.database_id,
 sysDB.Name as 'Database Name',
 syslogin.Name as 'DB Owner',
 sysDB.state_desc,
 sysDB.recovery_model_desc,
 sysDB.collation_name, 
 sysDB.user_access_desc,
 sysDB.compatibility_level, 
 sysDB.is_read_only,
 sysDB.is_auto_close_on,
 sysDB.is_auto_shrink_on,
 sysDB.is_auto_create_stats_on,
 sysDB.is_auto_update_stats_on,
 sysDB.is_fulltext_enabled,
 sysDB.is_trustworthy_on
from sys.databases sysDB
INNER JOIN sys.syslogins syslogin ON sysDB.owner_sid = syslogin.sid