SQL SERVER Articles Dear Readers, Please check below articles to get good knowledge on SQL SERVER. SNO DESCRIPTION ARTICLE LINK 1 SQL Server – Issue to view Alwayson Health Events https://www.ktexperts.com/sql-server-issue-to-view-alwayson-health-events/ 2 Script to copy one particular database role from one SQL server to another… Read More
Heimdall Data auto-caches and auto-invalidates, removing complex configuration. It requires zero changes to your application and database. Try it out. There are only two hard things in Computer Science: cache invalidation and naming things.” -Phil Karlton Caching is complex and engineering intensive.… Read More
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
Quickly Check SQL Server CPU Pressure Following query results how many threads are waiting to be executed on the server, if there are more than two threads waiting to be executed on a server, then that server can be assumed to have a CPU bottleneck… Read More
Live Insert Counts Without Disturbing Running Transaction There are times long running insert transactions are annoying and we don’t get direct details of running transaction like how many rows inserted and how many are pending. Following query gets estimated rows inserted for the specific table mentioned. SELECT… Read More
