SQL Server – Issue to view Alwayson Health Events If you find AlwaysOn Health Events grayed out and you are not able to see extended events. Please find below screenshot. When you connect Dashboard of AlwaysOn, you will find View AlwaysOn Health Events grayed out… Read More
Below Script is to copy one particular database role from one SQL server to another declare @RoleName varchar(50) = ‘Database_Role_Name’ declare @Script varchar(max) = ‘CREATE ROLE ‘ + @RoleName + char(13) select @script = @script + ‘GRANT ‘ + prm.permission_name + ‘ ON ‘ +… Read More
Enable Instant File Initialization You may want to consider enabling Instant File Initialization (via SE_MANAGE_VOLUME_NAME a.k.a. “Perform volume maintenance tasks”) for your SQL Server startup/service account. This option allows for much faster data file allocations (CREATE AND ALTER FILE) but DOES NOT WORK FOR LOG FILE… Read More
To check the fragmentation of all indexes on the Database SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName, ind.name AS IndexName, indexstats.index_type_desc AS IndexType, indexstats.avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id AND ind.index_id = indexstats.index_id WHERE indexstats.avg_fragmentation_in_percent > 30 ORDER… Read More
Transparent Data Encryption Setup Steps Below are the bullet points in setting up and usage of TDE 1. Take Full Backup of Master and User/Application Database (User database part of Encryption). 2. Create Folder to hold Certificate and Key 3. Create Master Key 4. Create… Read More
Database files details – SQL Server This script will help to identify the Database files Size in MB, Space Used, Available Space, % used. If there is space crunch on Database Drive and this query will help to release space if the %used is very… Read More
Expensive Queries of a database Following Query will find the expensive queries on specific database on SQL Instance. select DB_NAME(s.dbid) DBNAME , t.Text, spid from sysprocesses s CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t where DB_NAME(s.dbid) like ‘Database_Name%’ Thank you for giving your valuable time… Read More
AlwaysOn SQL Agent Alerts Please find below script to create SQL Server Agent Alerts. Please note that operator name should be changed according to your SQL operator. The below script will provide one output, you will have to execute the out again to create the… Read More
Capture SQL Server Important Details Please find the below script to capture SQL Server Important Details before upgrade, migration etc. PRINT ‘***************** Server Name ********************’ SET NOCOUNT ON select @@SERVERNAME go PRINT ‘***************** Version ********************’ SET NOCOUNT ON select @@version go PRINT ‘***************** License Information********************’… Read More
Find the Database Restore History On SQL Instance, at times we should know what DB’s got restored on specific instance or want to know when specific DB got restored. Below query will help to achieve that. SELECT * FROM MSDB..RESTOREHISTORY ORDER BY RESTORE_DATE DESC … Read More
