Category Archives: 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?

Dos Command to Check System Properties and Server Reboot time

1 Reply

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

Leave a reply

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

2 Replies

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.