Category: SQL Scripts

  • Script to Monitor the progress of ALTER, Backup, Restore, DBCC, Rollback and TDE commands

    We often like to check the progess or completed percentage of time consuming command. You can query the sys.dm_exec_requests DMV to check the status of the command.

    You must have atleast view state permission to execute the below query.
    ALTER INDEX REORGANIZE
    AUTO_SHRINK option with ALTER DATABASE
    BACKUP DATABASE
    DBCC CHECKDB
    DBCC CHECKFILEGROUP
    DBCC CHECKTABLE
    DBCC INDEXDEFRAG
    DBCC SHRINKDATABASE
    DBCC SHRINKFILE
    RECOVERY
    RESTORE DATABASE
    ROLLBACK
    TDE ENCRYPTION

    SELECT dmr.session_id,
       dmr.status,
       dmr.start_time,
       dmr.command,
       dmt.TEXT,
       dmr.percent_complete
FROM sys.dm_exec_requests dmr  CROSS APPLY sys.Dm_exec_sql_text(dmr.sql_handle) dmt WHERE dmr.command IN (‘ALTER’, ‘Backup’, ‘Restore’, ‘DBCC’, ‘Rollback’,  ‘TDE’)

  • How to Check When SQL Server was installed?

    NT AUTHORITY\SYSTEM login gets created when we install SQL Server. By querying sys.syslogins, we would get an idea of the SQL Server Installation date time as per the creation date time of NT AUTHORITY\SYSTEM.

    SELECT createdate AS sql_server_install_date FROM sys.syslogins WHERE sid = 0x010100000000000512000000

    SELECT * FROM sys.syslogins WHERE sid = 0x010100000000000512000000

    SELECT createdate AS sql_server_install_date FROM   sys.syslogins WHERE  sid = 0x010100000000000512000000

SELECT * FROM   sys.syslogins WHERE  sid = 0x010100000000000512000000

  • Dos Command to Check System Properties and Server Reboot time

    Many times we would like to check OS information and boot time. We can check the  operating system properties and reboot time by executing systeminfo command from dos prompt.

    C:\>systeminfo

  • Script to Update Statistics by passing database name

    You can use below script to update the statistics with the FULL Scan. You can pass the database name in below script, I have given JShah as database name.

    EXEC Sp_msforeachdb
@command1=‘IF ”#” IN (”JShah”) BEGIN PRINT ”#”;
EXEC #.dbo.sp_msForEachTable ”UPDATE STATISTICS ? WITH FULLSCAN”, @command2=”PRINT CONVERT(VARCHAR, GETDATE(), 9) + ”” - ? Stats Updated””” END’
,@replaceChar = ‘#’

    You can use below script to update the statistics with the SAMPLE Percent agrument. You can pass the database name in below script, I have given JShah as database name.

    EXEC Sp_msforeachdb
@command1=‘IF ”#” IN (”JShah”) BEGIN PRINT ”#”;
EXEC #.dbo.sp_msForEachTable ”UPDATE STATISTICS ? WITH SAMPLE 50 PERCENT”, @command2=”PRINT CONVERT(VARCHAR, GETDATE(), 9) + ”” - ? Stats Updated””” END’
,@replaceChar = ‘#’

  • 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.