DBA discussion

 Here is a detailed prompt for creating a database administrator (DBA) automation tool. This prompt can be used with GitHub Copilot, ChatGPT, or Gemini.

The "Master" Prompt:

"As a SQL Server DBA, I aim to automate backup reporting for Always On Availability Groups (AG). Please create a Python or PowerShell script that performs the following tasks:

1. Server Connection: Connect to the SQL environment using Windows Authentication. Automatically identify primary and secondary replicas using the AG Listener.
2. Data Extraction: Query the msdb..backupset and msdb..backupmediafamily tables across all nodes to find the last successful backup (Full, Differential, and Log) for every database.
3. Intelligence Logic: Flag any database without a Full Backup in more than 7 days as 'MISSING' or 'CRITICAL'.
4. Dashboard Generation: Generate a color-coded HTML report (Dashboard) with columns for Database Name, Last Backup Date/Time, Backup Type, File Location, and Status (SUCCESS/FAILED/MISSING).
5. Email Automation: Include a function to send the formatted HTML report as an email summary to the DBA team.

Ensure the code includes error handling, secure connection strings, and comments explaining each step."

---

Pro Tips for a Better Result:

• Specify the SQL Server version (e.g., "for SQL Server 2022") to ensure accurate T-SQL syntax.
• Include server naming conventions or preferred libraries (like pyodbc for Python or SqlServer module for PowerShell) in the prompt.
• Iterate: After the AI generates the first draft, request additional features like handling offline servers or formatting the HTML table with CSS.

Log Size Growth Alert in SQL

 

     @body = @tableHTML,@body_format = 'HTML';  

     

        

  

  END          

     -- Drop the Tempo

rary Table 

 --drop table log_size  

Grant_DB_owner_multiple_database or SamePermission on Multiple Database In SQL Server

 Use master

GO

DECLARE @dbname VARCHAR(50)          

DECLARE @SQL_statement varchar(max)

DECLARE @chng_role NVARCHAR(MAX)

DECLARE db_cursor CURSOR 

LOCAL FAST_FORWARD

FOR    

  SELECT name

    FROM MASTER.dbo.sysdatabases

    WHERE name NOT IN ('master','model','msdb','tempdb')    

OPEN db_cursor    

FETCH NEXT FROM db_cursor INTO @dbname    

WHILE @@FETCH_STATUS = 0    

 BEGIN 

    SET @SQL_statement = 'Declare  users_cursor CURSOR FOR SELECT name FROM '+ @dbname +'.sys.database_principals where (type='''S''' or type = '''U''')'

    EXEC sp_executesql @sqlstatement

    OPEN users_cursor

    FETCH NEXT FROM users_cursor INTO @UserName

    WHILE @@FETCH_STATUS = 0

        BEGIN       

            SET @chng_role = 'use [' + @dbname + ']; exec sp_addrolemember @rolename = ''db_owner'', @membername = ''' + @userName + ''''

            EXECUTE sp_executesql @chng_role 

            FETCH NEXT FROM users_cursor INTO @UserName

        END

    CLOSE users_cursor

    DEALLOCATE users_cursor

 FETCH NEXT FROM db_cursor INTO @dbname  

END  

CLOSE db_cursor  

DEALLOCATE db_cursor

Data Fil Shrink in Chunk in SQL server

 Use Apriso_IR1_PREPRD20

go

SELECT
    a.FILEID AS file_id,
    CONVERT(DECIMAL(12,2),ROUND(a.size/128.000,2)) AS [file_size_mb],
    CONVERT(DECIMAL(12,2),ROUND(FILEPROPERTY(a.name,'SpaceUsed')/128.000,2)) AS [space_used_mb] ,
    CONVERT(DECIMAL(12,2),ROUND((a.size-FILEPROPERTY(a.name,'SpaceUsed'))/128.000,2)) AS [free_space_mb],
    a.NAME AS name,
    a.FILENAME AS physical_name
FROM
    sys.sysfiles a



==============================================

Find script to shrink datafile by 2 GB.


Use Apriso_IR1_PREPRD20
go

declare @fsize int, @esize int, @fno int
Set @esize = 1118697.38 --Provide the existing size of the file in MB 81920000 KB
Set @fsize = 862312.56 --Provide the final size of the file needed in MB
Set @fno = 1 --Provide the file number that needs to be shrunk
While (@fsize <= @esize)
Begin

Print 'DBCC SHRINKFILE ('+rtrim(cast(@fno as varchar(5)))+','+rtrim(cast(@esize as varchar(10)))+')'+Char(10)+'Go'
Set @esize = @esize - 1024

End

Long Running Query in SQL

 SELECT A.Session_ID SPID

,CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
+ CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
+ CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as  running_time
,ISNULL(B.status,A.status) Status, A.login_name Login, A.host_name HostName, C.BlkBy, DB_NAME(B.Database_ID) DBName, B.command, b.wait_type,b.last_wait_type,ISNULL(B.cpu_time, A.cpu_time) CPUTime, ISNULL((B.reads + B.writes),(A.reads + A.writes)) DiskIO, A.last_request_start_time LastBatch, A.program_name ,D.text SQLStatement FROM sys.dm_exec_sessions A LEFT JOIN sys.dm_exec_requests B ON A.session_id = B.session_id LEFT JOIN (SELECT A.request_session_id SPID, B.blocking_session_id BlkBy FROM sys.dm_tran_locks as A INNER JOIN sys.dm_os_waiting_tasks as B ON A.lock_owner_address = B.resource_address) C ON A.Session_ID = C.SPID OUTER APPLY sys.dm_exec_sql_text(sql_handle) D Where a.session_id > 50 and b.status <> 'sleeping'
ORDER BY a.session_id

DBA Maintenance Query

 SELECT 'ALTER INDEX ALL ON [' + Table_schema + '].[' + Table_name + '] REBUILD;'

FROM information_schema.tables
WHERE Table_schema != 'sys' 

EXEC sp_updatestats


SELECT 'UPDATE STATISTICS ' + name + ' WITH FULLSCAN'
FROM sysobjects
WHERE objectproperty( id, N'IsMSShipped' ) = 0
AND objectproperty( id, N'IsTable' ) = 1



SELECT S.name as 'Schema',

T.name as 'Table',

I.name as 'Index',

DDIPS.avg_fragmentation_in_percent,

DDIPS.page_count

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS

INNER JOIN sys.tables T on T.object_id = DDIPS.object_id

INNER JOIN sys.schemas S on T.schema_id = S.schema_id

INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id

AND DDIPS.index_id = I.index_id

WHERE DDIPS.database_id = DB_ID()

and I.name is not null

AND DDIPS.avg_fragmentation_in_percent > 0

ORDER BY DDIPS.avg_fragmentation_in_percent desc

Fix Orphan User Settings in SQL server

-- Script to check the orphan user
EXEC sp_change_users_login 'Report'
--Use below code to fix the Orphan User issue
DECLARE @username varchar(25)
DECLARE fixusers CURSOR 
FOR
SELECT UserName = name FROM sysusers
WHERE issqluser = 1 and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null
ORDER BY name
OPEN fixusers
FETCH NEXT FROM fixusers
INTO @username
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_change_users_login 'update_one', @username, @username
FETCH NEXT FROM fixusers
INTO @username
END
CLOSE fixusers
DEALLOCATE fixusers

SQL Head Blocking Query

 SET NOCOUNT ON

GO
SELECT SPID, BLOCKED, REPLACE (REPLACE (T.TEXT, CHAR(10), ' '), CHAR (13), ' ' ) AS BATCH
INTO #T
FROM sys.sysprocesses R CROSS APPLY sys.dm_exec_sql_text(R.SQL_HANDLE) T
GO
WITH BLOCKERS (SPID, BLOCKED, LEVEL, BATCH)
AS
(
SELECT SPID,
BLOCKED,
CAST (REPLICATE ('0', 4-LEN (CAST (SPID AS VARCHAR))) + CAST (SPID AS VARCHAR) AS VARCHAR (1000)) AS LEVEL,
BATCH FROM #T R
WHERE (BLOCKED = 0 OR BLOCKED = SPID)
AND EXISTS (SELECT * FROM #T R2 WHERE R2.BLOCKED = R.SPID AND R2.BLOCKED <> R2.SPID)
UNION ALL
SELECT R.SPID,
R.BLOCKED,
CAST (BLOCKERS.LEVEL + RIGHT (CAST ((1000 + R.SPID) AS VARCHAR (100)), 4) AS VARCHAR (1000)) AS LEVEL,
R.BATCH FROM #T AS R
INNER JOIN BLOCKERS ON R.BLOCKED = BLOCKERS.SPID WHERE R.BLOCKED > 0 AND R.BLOCKED <> R.SPID
)
SELECT N'    ' + REPLICATE (N'|         ', LEN (LEVEL)/4 - 1) +
CASE WHEN (LEN(LEVEL)/4 - 1) = 0
THEN 'HEAD -  '
ELSE '|------  ' END
+ CAST (SPID AS NVARCHAR (10)) + N' ' + BATCH AS BLOCKING_TREE
FROM BLOCKERS ORDER BY LEVEL ASC
GO
DROP TABLE #T
go

User script permission

/*
Script DB Level Permissions v2.1
Source: http://www.sqlservercentral.com/scripts/Security/71562/
*/

DECLARE 
    @sql VARCHAR(2048)
    ,@sort INT 

DECLARE tmp CURSOR FOR


/*********************************************/
/*********   DB CONTEXT STATEMENT    *********/
/*********************************************/
SELECT '-- [-- DB CONTEXT --] --' AS [-- SQL STATEMENTS --],
        1 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT  'USE' + SPACE(1) + QUOTENAME(DB_NAME()) AS [-- SQL STATEMENTS --],
        1 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT '' AS [-- SQL STATEMENTS --],
        2 AS [-- RESULT ORDER HOLDER --]

UNION

/*********************************************/
/*********     DB USER CREATION      *********/
/*********************************************/

SELECT '-- [-- DB USERS --] --' AS [-- SQL STATEMENTS --],
        3 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT  'IF NOT EXISTS (SELECT [name] FROM sys.database_principals WHERE [name] = ' + SPACE(1) + '''' + [name] + '''' + ') BEGIN CREATE USER ' + SPACE(1) + QUOTENAME([name]) + ' FOR LOGIN ' + QUOTENAME([name]) + ' WITH DEFAULT_SCHEMA = ' + QUOTENAME([default_schema_name]) + SPACE(1) + 'END; ' AS [-- SQL STATEMENTS --],
        4 AS [-- RESULT ORDER HOLDER --]
FROM    sys.database_principals AS rm
WHERE [type] IN ('U', 'S', 'G') -- windows users, sql users, windows groups

UNION

/*********************************************/
/*********    DB ROLE PERMISSIONS    *********/
/*********************************************/
SELECT '-- [-- DB ROLES --] --' AS [-- SQL STATEMENTS --],
        5 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT  'EXEC sp_addrolemember @rolename ='
    + SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''') + ', @membername =' + SPACE(1) + QUOTENAME(USER_NAME(rm.member_principal_id), '''') AS [-- SQL STATEMENTS --],
        6 AS [-- RESULT ORDER HOLDER --]
FROM    sys.database_role_members AS rm
WHERE   USER_NAME(rm.member_principal_id) IN (  
                                                --get user names on the database
                                                SELECT [name]
                                                FROM sys.database_principals
                                                WHERE [principal_id] > 4 -- 0 to 4 are system users/schemas
                                                and [type] IN ('G', 'S', 'U') -- S = SQL user, U = Windows user, G = Windows group
                                              )
--ORDER BY rm.role_principal_id ASC


UNION

SELECT '' AS [-- SQL STATEMENTS --],
        7 AS [-- RESULT ORDER HOLDER --]

UNION

/*********************************************/
/*********  OBJECT LEVEL PERMISSIONS *********/
/*********************************************/
SELECT '-- [-- OBJECT LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
        8 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT  CASE 
            WHEN perm.state <> 'W' THEN perm.state_desc 
            ELSE 'GRANT'
        END
        + SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(SCHEMA_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name) --select, execute, etc on specific objects
        + CASE
                WHEN cl.column_id IS NULL THEN SPACE(0)
                ELSE '(' + QUOTENAME(cl.name) + ')'
          END
        + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default
        + CASE 
                WHEN perm.state <> 'W' THEN SPACE(0)
                ELSE SPACE(1) + 'WITH GRANT OPTION'
          END
            AS [-- SQL STATEMENTS --],
        9 AS [-- RESULT ORDER HOLDER --]
FROM    
    sys.database_permissions AS perm
        INNER JOIN
    sys.objects AS obj
            ON perm.major_id = obj.[object_id]
        INNER JOIN
    sys.database_principals AS usr
            ON perm.grantee_principal_id = usr.principal_id
        LEFT JOIN
    sys.columns AS cl
            ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
--WHERE usr.name = @OldUser
--ORDER BY perm.permission_name ASC, perm.state_desc ASC



UNION

SELECT '' AS [-- SQL STATEMENTS --],
    10 AS [-- RESULT ORDER HOLDER --]

UNION

/*********************************************/
/*********    DB LEVEL PERMISSIONS   *********/
/*********************************************/
SELECT '-- [--DB LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
        11 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT  CASE 
            WHEN perm.state <> 'W' THEN perm.state_desc --W=Grant With Grant Option
            ELSE 'GRANT'
        END
    + SPACE(1) + perm.permission_name --CONNECT, etc
    + SPACE(1) + 'TO' + SPACE(1) + '[' + USER_NAME(usr.principal_id) + ']' COLLATE database_default --TO <user name>
    + CASE 
            WHEN perm.state <> 'W' THEN SPACE(0) 
            ELSE SPACE(1) + 'WITH GRANT OPTION' 
      END
        AS [-- SQL STATEMENTS --],
        12 AS [-- RESULT ORDER HOLDER --]
FROM    sys.database_permissions AS perm
    INNER JOIN
    sys.database_principals AS usr
    ON perm.grantee_principal_id = usr.principal_id
--WHERE usr.name = @OldUser

WHERE   [perm].[major_id] = 0
    AND [usr].[principal_id] > 4 -- 0 to 4 are system users/schemas
    AND [usr].[type] IN ('G', 'S', 'U') -- S = SQL user, U = Windows user, G = Windows group

UNION

SELECT '' AS [-- SQL STATEMENTS --],
        13 AS [-- RESULT ORDER HOLDER --]

UNION 

SELECT '-- [--DB LEVEL SCHEMA PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
        14 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT  CASE
            WHEN perm.state <> 'W' THEN perm.state_desc --W=Grant With Grant Option
            ELSE 'GRANT'
            END
                + SPACE(1) + perm.permission_name --CONNECT, etc
                + SPACE(1) + 'ON' + SPACE(1) + class_desc + '::' COLLATE database_default --TO <user name>
                + QUOTENAME(SCHEMA_NAME(major_id))
                + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(grantee_principal_id)) COLLATE database_default
                + CASE
                    WHEN perm.state <> 'W' THEN SPACE(0)
                    ELSE SPACE(1) + 'WITH GRANT OPTION'
                    END
            AS [-- SQL STATEMENTS --],
        15 AS [-- RESULT ORDER HOLDER --]
from sys.database_permissions AS perm
    inner join sys.schemas s
        on perm.major_id = s.schema_id
    inner join sys.database_principals dbprin
        on perm.grantee_principal_id = dbprin.principal_id
WHERE class = 3 --class 3 = schema


ORDER BY [-- RESULT ORDER HOLDER --]


OPEN tmp
FETCH NEXT FROM tmp INTO @sql, @sort
WHILE @@FETCH_STATUS = 0
BEGIN
        PRINT @sql
        FETCH NEXT FROM tmp INTO @sql, @sort    
END

CLOSE tmp
DEALLOCATE tmp 

LINK SERVER CREATION [SQL SERVER TO ORACLE SERVER STEP BY STEP]

Link Server:-A linked server allows joining several SQL Server instances using a single T-SQL statement when data exists on multiple databases on different SQL instances.Using a linked server to retrieve data from several SQL instances, the only thing that should be done is to connect to one SQL instance. 

 

Here we will learn how to create Link Server from SQL To Oracle Server Below are the points are need to follow..............

Download the files for the Oracle database server 

After the installation process completes, reboot your computer. If your installation went like mine, then your Services listing will include go to services.msc and check it services .

Steps to install and configurations:-

Step1

Step2

 

 Step3

 

Step4

 

Step5

After Install Oracle Client go to services .MSc and check it install oracle client status:-

Tns.ora file client look like this

 

Step6

Entery in tns.ora file as provide by oracle team

 

 Step7

Save it and go to cmd.exe and chk using below steps as per snapshot

 

 

 

Step8

 

 

Step9

 

 

Step 10

 

 

Step10

 

 

Step 11

Go to Provider Folder and change the below settings 

 

Thanks You..............