Category Archives: Notes

All Articles

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

1 Reply

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?

Leave a reply

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

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 = ‘#’