Go to respective shared folder and register -
20 August 2025
27 July 2025
SQL Server Daily Health Check - Script
DECLARE @html NVARCHAR(MAX) = '';
DECLARE @newline NVARCHAR(10) = CHAR(13) + CHAR(10);
-- HTML Header
SET @html = @html + '<html><head><style>
table { border-collapse: collapse; width: auto; max-width: 100%; }
th, td { border: 1px solid #ddd; padding: 8px; }
th { background-color: #f2f2f2; }
</style></head><body>' + @newline;
-- SQL Server Version and EOL Mapping
DECLARE @Servername NVARCHAR(128) = CAST(SERVERPROPERTY('ServerName') AS NVARCHAR);
DECLARE @version NVARCHAR(128) = CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR);
DECLARE @edition NVARCHAR(128) = CAST(SERVERPROPERTY('Edition') AS NVARCHAR);
DECLARE @eol NVARCHAR(256);
DECLARE @MajorVersion NVARCHAR(256);
SET @MajorVersion = CASE
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '8%' THEN 'SQL2000'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '9%' THEN 'SQL2005'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '10.0%' THEN 'SQL2008'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '10.5%' THEN 'SQL2008 R2'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '11%' THEN 'SQL2012'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '12%' THEN 'SQL2014'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '13%' THEN 'SQL2016'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '14%' THEN 'SQL2017'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '15%' THEN 'SQL2019'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '16%' THEN 'SQL2022'
ELSE 'Unknown/New Version'
END ;
SET @eol = CASE
WHEN @@VERSION LIKE '%2005%' THEN 'Mainstream: 2011-04-12, Extended Support: 2016-04-12'
WHEN @@VERSION LIKE '%2008 R2%' THEN 'Mainstream: 2014-07-08, Extended Support: 2019-07-09'
WHEN @@VERSION LIKE '%2008%' THEN 'Mainstream: 2014-07-08, Extended Support: 2019-07-09'
WHEN @@VERSION LIKE '%2012%' THEN 'Mainstream: 2017-07-11, Extended Support: 2022-07-12'
WHEN @@VERSION LIKE '%2014%' THEN 'Mainstream: 2019-07-09, Extended Support: 2024-07-09'
WHEN @@VERSION LIKE '%2016%' THEN 'Mainstream: 2021-07-13, Extended Support: 2026-07-14'
WHEN @@VERSION LIKE '%2017%' THEN 'Mainstream: 2022-10-11, Extended Support: 2027-10-12'
WHEN @@VERSION LIKE '%2019%' THEN 'Mainstream: 2025-02-28, Extended Support: 2030-01-08'
WHEN @@VERSION LIKE '%2022%' THEN 'Mainstream: 2028-01-11, Extended Support: 2033-01-11'
ELSE 'Unknown/New Version'
END;
SET @html = @html + '<h3>SQL Server Version and EOL</h3><table><tr>
<th>Instance Name</th><th>Version</th><th>PatchLevel</th><th>Edition</th><th>EOL Info</th></tr>';
SET @html = @html + '<tr><td>' + @Servername + '</td><td>' + @MajorVersion + '</td><td>' + @version + '</td><td>' + @edition + '</td><td>' + @eol + '</td></tr></table>' + @newline;
-- Uptime
SET @html = @html + '<h3>SQL Server Uptime</h3><table><tr><th>Start Time</th></tr>';
SELECT @html = @html + '<tr><td>' + CONVERT(NVARCHAR, sqlserver_start_time, 120) + '</td></tr>'
FROM sys.dm_os_sys_info;
SET @html = @html + '</table>' + @newline;
-- Offline Databases
SET @html = @html + '<h3>Offline Databases</h3>';
IF EXISTS (SELECT 1 FROM sys.databases WHERE state_desc = 'OFFLINE')
BEGIN
SET @html = @html + '<table><tr><th>Database</th><th>Status</th></tr>';
SELECT @html = @html + '<tr><td>' + name + '</td><td>' + state_desc + '</td></tr>'
FROM sys.databases
WHERE state_desc = 'OFFLINE';
SET @html = @html + '</table>' + @newline;
END
ELSE
BEGIN
SET @html = @html + '<p>All databases are online.</p>' + @newline;
END
-- Disk Space Usage in GB (2 decimal places)
SET @html = @html + '<h3>Disk Space Usage</h3><table><tr><th>Drive</th><th>Free Space (GB)</th></tr>';
CREATE TABLE #drives (drive CHAR(1), free_space_MB INT);
INSERT INTO #drives EXEC xp_fixeddrives;
SELECT @html = @html + '<tr><td>' + drive + '</td><td>' +
CONVERT(NVARCHAR, ROUND(CAST(free_space_MB AS FLOAT) / 1024, 2)) + '</td></tr>'
FROM #drives;
DROP TABLE #drives;
SET @html = @html + '</table>' + @newline;
-- Backup Status (Exclude tempdb and mirrored databases)
SET @html = @html + '<h3>Backup Status (Failed or No Backup in Past 2 Days)</h3>';
IF EXISTS (
SELECT 1
FROM sys.databases d
LEFT JOIN msdb.dbo.backupset bs ON bs.database_name = d.name
LEFT JOIN sys.database_mirroring dm ON d.database_id = dm.database_id
WHERE d.name <> 'tempdb' AND d.is_read_only = 0 AND (dm.mirroring_guid IS NULL)
GROUP BY d.name
HAVING MAX(bs.backup_finish_date) IS NULL OR MAX(bs.backup_finish_date) < DATEADD(DAY, -2, GETDATE())
)
BEGIN
SET @html = @html + '<table><tr><th>Database</th><th>Last Backup</th></tr>';
SELECT @html = @html + '<tr><td>' + d.name + '</td><td>' +
COALESCE(CONVERT(NVARCHAR, MAX(bs.backup_finish_date), 120), 'No Backup') + '</td></tr>'
FROM sys.databases d
LEFT JOIN msdb.dbo.backupset bs ON bs.database_name = d.name
LEFT JOIN sys.database_mirroring dm ON d.database_id = dm.database_id
WHERE d.name <> 'tempdb' AND d.is_read_only = 0 AND ISNULL(dm.mirroring_guid, '') = ''
GROUP BY d.name
HAVING MAX(bs.backup_finish_date) IS NULL OR MAX(bs.backup_finish_date) < DATEADD(DAY, -2, GETDATE());
SET @html = @html + '</table>' + @newline;
END
ELSE
BEGIN
SET @html = @html + '<p>All backups successful.</p>' + @newline;
END
-- Mirroring Status
SET @html = @html + '<h3>Mirroring Status</h3>';
IF EXISTS (SELECT 1 FROM sys.database_mirroring WHERE mirroring_guid IS NOT NULL)
BEGIN
SET @html = @html + '<table><tr><th>Database</th><th>Mirroring State</th><th>Sync State</th></tr>';
SELECT @html = @html + '<tr><td>' + d.name + '</td><td>' +
ISNULL(dm.mirroring_state_desc, 'Unknown') + '</td><td>' +
ISNULL(dm.mirroring_safety_level_desc, 'Unknown') + '</td></tr>'
FROM sys.databases d
JOIN sys.database_mirroring dm ON d.database_id = dm.database_id
WHERE dm.mirroring_guid IS NOT NULL;
SET @html = @html + '</table>' + @newline;
END
ELSE
BEGIN
SET @html = @html + '<p>No mirroring configured.</p>' + @newline;
END
-- Replication Status
SET @html = @html + '<h3>Replication Status</h3>';
IF EXISTS (SELECT 1 FROM sys.databases WHERE name = 'distribution')
BEGIN
IF EXISTS (
SELECT 1
FROM distribution.sys.objects
WHERE name = 'MSreplication_monitordata' AND type = 'U'
)
BEGIN
CREATE TABLE #COUNTERS
(
[DATABASE] SYSNAME,
REPLICATEDTRANS INT,
REPRATESEC FLOAT,
REPLATENCY FLOAT,
LSN1 BINARY(10),
LSN2 BINARY(10)
)
INSERT INTO #COUNTERS EXEC sp_replcounters
SELECT DISTINCT m.Publisher_db, m.Publication, ma.Subscriber_db,
CASE [Status]
WHEN 1 THEN 'Started'
WHEN 2 THEN 'Succeeded'
WHEN 3 THEN 'In Progress'
WHEN 4 THEN 'Idle'
WHEN 5 THEN 'Retrying'
WHEN 6 THEN 'Failed'
END AS [Status],
CASE [Warning]
WHEN 0 THEN NULL
WHEN 1 THEN 'Expiration'
WHEN 2 THEN 'Latency'
ELSE 'MergeWarning'
END AS [Warning],
C.REPLICATEDTRANS AS 'Awaiting Transactions To Dist',
CONVERT(INT, c.REPRATESEC) AS 'Avrage Trans/Sec to Dist',
CONVERT(DECIMAL(10, 2), c.REPLATENCY) AS 'Avg. Latency to Dist/Sec',
CONVERT(DECIMAL(10, 2), mm.cur_latency) AS 'Avg. Latency to Subscriber'
INTO #MonitorRepl
FROM distribution.dbo.MSpublications m
INNER JOIN distribution.dbo.MSreplication_monitordata mm ON mm.publisher_db = m.publisher_db AND mm.publication_id = m.publication_id AND mm.agent_type = 3
INNER JOIN distribution.dbo.MSdistribution_agents ma ON ma.publisher_id = m.publisher_id AND ma.publication = mm.publication AND ma.job_id = mm.job_id
--INNER JOIN sys.servers s ON ma.subscriber_id = s.server_id
INNER JOIN #COUNTERS C ON m.publisher_db = C.[DATABASE]
ORDER BY m.publisher_db, m.publication, ma.subscriber_db
DROP TABLE #COUNTERS
SET @html = @html + '<table><tr><th>publisher_db</th><th>Publication</th><th>subscriber_db (sec)</th><th>Status</th></tr>';
SELECT @html = @html + '<tr><td>' + publisher_db + '</td><td>' + publication + '</td><td>' +
subscriber_db + '</td><td>' + Status + '</td></tr>'
FROM #MonitorRepl;
SET @html = @html + '</table>' + @newline;
DROP TABLE #MonitorRepl
END
ELSE
BEGIN
SET @html = @html + '<p>No replication activity found in distribution database.</p>' + @newline;
END
END
ELSE
BEGIN
SET @html = @html + '<p>No replication configured.</p>' + @newline;
END
-- Always On Availability Groups
SET @html = @html + '<h3>Always On Availability Groups</h3>';
IF EXISTS (SELECT 1 FROM sys.availability_groups)
BEGIN
SET @html = @html + '<table><tr>
<th>Availability Group</th>
<th>Replica Server</th>
<th>Role</th>
<th>Database</th>
<th>Sync State</th>
<th>Health</th>
</tr>';
SELECT @html = @html + '<tr><td>' + ag.name + '</td><td>' + ar.replica_server_name + '</td><td>' +
ars.role_desc + '</td><td>' + adc.database_name + '</td><td>' +
dhs.synchronization_state_desc + '</td><td>' + dhs.synchronization_health_desc + '</td></tr>'
FROM sys.availability_groups ag
JOIN sys.availability_replicas ar ON ag.group_id = ar.group_id
JOIN sys.dm_hadr_availability_replica_states ars ON ar.replica_id = ars.replica_id
JOIN sys.dm_hadr_database_replica_states dhs ON ars.replica_id = dhs.replica_id
JOIN sys.availability_databases_cluster adc ON ag.group_id = adc.group_id
WHERE dhs.group_database_id = adc.group_database_id;
SET @html = @html + '</table>' + @newline;
END
ELSE
BEGIN
SET @html = @html + '<p>No Always On Availability Groups configured.</p>' + @newline;
END
-- HTML Footer
SET @html = @html + '</body></html>';
-- Send Email
declare @sub varchar(max)
SET @sub = 'Alert: SQL Server Health Check Report - ' + CAST(SERVERPROPERTY('MachineName') AS VARCHAR) + ''
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Default',
@recipients = 'DBA@mssqlserver.com',
@subject = @sub,
@body = @html,
@body_format = 'HTML';
05 May 2025
The server principal owns one or more server role(s) and cannot be dropped. (Microsoft SQL Server, Error: 15141)
Some time we are trying to delete or drop a login from SQL server instance , we encountered with below error.
The server principal owns one or more server role(s) and cannot be dropped. (Microsoft SQL Server, Error: 15141)
Step 1) Find the server roles owned by login using below script
SELECT sp1.name AS ServerRoleName,
sp2.name AS RoleOwnerName
FROM sys.server_principals AS sp1
JOIN sys.server_principals As sp2
ON sp1.owning_principal_id=sp2.principal_id
WHERE sp2.name='Test' --Change the login name
Step 2 ) Change server role to another login
USE [master]
GO
ALTER AUTHORIZATION ON SERVER ROLE :: Admin to SQLAdmin
Step 3) Drop login
USE [master]
GO
Drop Login Test
23 April 2025
Difference between NOLOCK and READPAST
NOLOCK
========
When we use NOLOCK in select command it will allow to read rows are available on dirty pages/uncommitted from buffer cache
Ex: i have table called replication with 4 records
update one record with out commit
in another session i try to select table, it not showing any result
Tried with NOLOCK , it resulting with dirty pages
=========
it will not read rows that are locked by other transactions. When READPAST is specified, row-level locks are skipped.
SELECT * FROM [replication] with(readpast)
it will skip modified records until transaction will commit
22 April 2025
Error: Database cannot be opened. It is in the middle of a restore
This may be a bug in SSMS, after restoring FULL and LOG backups when we start mirroring we get this error - To solve execute bellow commands so that mirroring will start -
First In Mirror instance:
ALTER DATABASE DBNAME
SET PARTNER =
'TCP://PRINCIPAL SERVER.DOMAIN:MIRRORING PORT NUMBER'
In Principal instance:
ALTER DATABASE DBNAME
SET PARTNER =
'TCP://MIRRIR SERVER.DOMAIN:MIRRORING PORT NUMBER' 07 April 2025
Restore oracle database on development server from production server using RMAN backup (Restore and Recovery Method)
Step 1) Copy Backup copies from Production to Development server using cp command
[Source server Backup]$ scp -p bkup_j93m9af4_95849_1_1 bkup_ja3m9ahh_95850_1_1 bkup_jb3m9aju_95851_1_1 bkup_jc3m9amb_95852_1_1 bkup_jd3m9aqj_95853_1_1 bkup_je3m9aql_95854_1_1 bkup_jf3m9aqn_95855_1_1 c-87645678-20250407-00 oracle@Target server:<Backup Location>
Step 2) Once copy done connect to target server verify spfile and create pfile from spfile
[oracle@sreenu ~]$ !sq
sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Apr 7 08:56:30 2025
Version 19.21.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition 2 Release 19.0.0.0.0 - Production
Version 19.21.0.0.0
SQL>
SQL> sho parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/opt/oracle/dbhome/dbs/spfilePROD.ora
SQL> create pfile from spfile;
File created.
Step 3) Check Listener status and stop Listener on Target server ( It will not allow incoming connections to connect database)
[oracle@sreenu ~]$ lsnrctl status
[oracle@sreenu ~]$ lsnrctl stop
Step 4) Drop Database on target server (Before drooping the database take full backup of the development server )
Note1:Only we can drop/delete database on mount mode only
Note2:Before drooping the database we need to shutdown database and startup restrict mode
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup restrict mount;
ORACLE instance started.
Total System Global Area 5905576504 bytes
Fixed Size 8938040 bytes
Variable Size 3909091328 bytes
Database Buffers 1979711488 bytes
Redo Buffers 7835648 bytes
Database mounted.
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
PROD MOUNTED
SQL> drop database;
Database dropped.
Step 5) Startup database using pfile in nomount mode.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 5905576504 bytes
Fixed Size 8938040 bytes
Variable Size 3909091328 bytes
Database Buffers 1979711488 bytes
Redo Buffers 7835648 bytes
SQL>
SQL> sho parameter pfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
SQL>
Note: Database started using by pfile.
Step 6) Restore controlfile from backup using RMAN tool / Prompt
[oracle@czprg-tcora303 PROD]$ rman target /
connected to target database: PROD (not mounted)
RMAN>
RMAN> restore controlfile from '/u01/opt/oracle/rman_bkup/PROD/c-87645678-20250407-00';
Starting restore at 07-APR-25
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/u01/opt/oracle/control_files/control01.ctl
output file name=/u01/opt/oracle/control_files/control02.ctl
output file name=/u01/opt/oracle/mpx_PROD/control_files/control03.ctl
Finished restore at 07-APR-25
RMAN>
Step 7) Once controlfile restored we need to bring database to mount mode.
RMAN> alter database mount;
released channel: ORA_DISK_1
Statement processed
RMAN>
Step 8) restore database.
RMAN> restore database;
Step 9) Once restore done , we need to recovery the database.
RMAN> recover database;
Step 10) Once recovery done , we need to open database using resetlogs.
RMAN> alter database open resetlogs;
Statement processed
RMAN>
Step 11) Once restore and recovery done , we need to start database with spfile
Note: Always run database using spfile only.
Note: To change database from pfile to spfile, need to create spfile from pfile and bounce the database.
SQL> create spfile from pfile;
File created.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 5905576504 bytes
Fixed Size 8938040 bytes
Variable Size 3909091328 bytes
Database Buffers 1979711488 bytes
Redo Buffers 7835648 bytes
Database mounted.
Database opened.
SQL>
Step 12) Verify Database mode and its running with spfile or not.
SQL> sho parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/opt/oracle/dbhome/dbs/spfilePROD.ora
SQL> select name,open_mode,log_mode from v$database;
NAME OPEN_MODE LOG_MODE
--------- -------------------- ------------
PROD READ WRITE ARCHIVELOG
SQL>
Step 13) Start Listener and check status
[oracle@sreenu ~]$ lsnrctl start
[oracle@sreenu ~]$ lsnrctl status
Step 14) Ask Application team to verify from application end.
04 April 2025
Grant select access to specific object or tables
We can grant select access to specific Tables / Objects in three ways
Create Login on SQL server instance
In my case Login name Appteam
Don't Grant any server role to Login
I need fetch data from TEST_Mirr database
Don't grant any database role
Default public will be there
On Database user will be created automatically
Here for Appteam not showing any tables on the TEST_Mirr database
1) using T SQL
Granted select on below two tables for Appteam user
use TEST_Mirr
Grant select on conn1 to Appteam
Grant select on conn2 to Appteam
2) Using GUI
Using Admin session go to user properties
Click on search and select all objects of the types and click ok , it will pop up new dialog box
Select Tables and click OK
This time i am going grant select access on Output_Field table
After granting select permissions on Output_Field table
3) Using Table Properties
For which we need to grand select on the table go to that table properties
Go to permissions tab >>> search for a user >>> grant select as shown in below screenshots
Now we can see all the tables which we granted select access
Subscribe to:
Posts (Atom)
-
Find More On : - http://www.sqlserver-training.com/250-sql-queries-interview-question-answers/-#5+ SQL Queries Interview Questions ...
-
http://technet.microsoft.com/en-us/library/cc731002(WS.10).aspx#BKMK_requirements How accounts are created through wizards in ...
-
ERROR: report server remote procedure call (RPC) end point. Verify that the Report Server Windows service is running, and then retry the o...
