Wednesday, September 25, 2019

Check Who has Server Level Access, SQL Server

Using below two SQL queries we can retrieve who has what access on the database server.
I have divided it into two-parts

1. Role access [ sp_addsrvrolemember & sp_dropsrvrolemember ]
2. Grant/Deny access [ GRANT .. & DENY .. ]

1. Role access: this SQL will show the SQL command of Add/Remove user to this role. So that you can use any to add/ or remove

select prm.name as "UserName",
prr.name as "ServerRole",
'exec sp_addsrvrolemember '''+prm.name+''','''+prr.name+'''' as GrantCMD,
'exec sp_dropsrvrolemember '''+prm.name+''','''+prr.name+'''' as DenyCMD
from sys.server_role_members srm
left join sys.server_principals AS prm on prm.principal_id = srm.member_principal_id
left join sys.server_principals AS prr on prr.principal_id = srm.role_principal_id

The output of this SQL would be like below
Role Access

2. Grant/Deny access: using below SQL, we can retrieve the user list with the access name. I have also prepared the GRANT/DENY command to use when required.

select pr.name as "UserName",
sp.permission_name, sp.state_desc,
sp.state_desc collate SQL_Latin1_General_CP1_CI_AS+' '
+sp.permission_name collate SQL_Latin1_General_CP1_CI_AS+' TO '
+QUOTENAME( pr.name collate SQL_Latin1_General_CP1_CI_AS) as Command
from sys.server_permissions sp
left join sys.server_principals AS pr
on pr.principal_id = sp.grantee_principal_id
where pr.name not in ('public','sa','NT AUTHORITY\SYSTEM')
and pr.name not like '##%##'
and sp.permission_name not in ('CONNECT SQL')

The output of this SQL would be like below:

Grant / Deny Access
Thanks.

Tuesday, September 24, 2019

Shrink LOG files automatically, SQL Server

Use below script to shrink T-LOG files. This script will shrink the log file which is larger than the value of the variable "@LOG_SIZE_LIMIT". You can change it to meet your requirement.

To run this script automatically, create a SQL JOB and place this script in the STEP. That's all.

See Also: SHRINK DATABASE, Check Job Status, Grant Access to SQL Agent, View Job history.


USE master;
GO
DECLARE @LOG_SIZE_LIMIT int = 10240 -- 10GB

declare  DBName cursor for
select a.DATABASE_NAME,mf. name AS LogicalFileName ,LOG_SIZE_MB
from(
SELECT DATABASE_NAME = DB_NAME(s.database_id)
    , LOG_SIZE_MB = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(10,2))
    , ROW_SIZE_MB = CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(10,2))
    , TOTAL_SIZE_MB = CAST(SUM(size) * 8. / 1024 AS DECIMAL(10,2))
FROM sys.databases s
JOIN sys.master_files m
ON s.database_id = m.database_id
where s.database_id > 4 and s.state_desc= 'ONLINE' and s.recovery_model_desc = 'SIMPLE'
GROUP BY s.database_id,s.name
) a
inner join sys.master_files AS mf on DB_NAME(mf.database_id) = a.DATABASE_NAME and mf.type_desc = 'LOG'
where LOG_SIZE_MB > @LOG_SIZE_LIMIT
ORDER BY LOG_SIZE_MB desc

declare @DBNAme varchar(1000), @LogicalFileName varchar(1000), @LOG_SIZE_MB int, @SQLExec varchar(4000)

open DBName
fetch next from DBName into @DBNAme,@LogicalFileName,@LOG_SIZE_MB

WHILE @@FETCH_STATUS=0
BEGIN

SET @SQLExec= 'use '+@DBNAme+'; DBCC shrinkfile ('+@LogicalFileName+')'
print @SQLExec
execute (@SQLExec)

fetch next from DBName into @DBNAme,@LogicalFileName,@LOG_SIZE_MB
END

close DBName
deallocate DBName

Thanks.

Backup All Databases, SQL Server

Using the below script we can take all databases backup with one command!!

You can check my other post BACKUP STATUS to know the current DB backup status.

exec sp_msforeachdb '
use [?];
IF ''?'' not in ( ''master'',''tempdb'',''model'',''msdb'',''DBAdmin'')
BEGIN
       use [master]
       Print ''?''
       ALTER DATABASE [?] SET READ_ONLY WITH ROLLBACK IMMEDIATE
       Print ''Marked -- READ_ONLY''
       BACKUP DATABASE [?]
       to disk = ''<Disk Location/Path>\?_Backup_FULL_20190924.bak''
       with COMPRESSION
       Print ''Backup Done''
       ALTER DATABASE [?] SET MULTI_USER WITH NO_WAIT
END
ELSE
BEGIN
       Print DB_NAME()+ '' -- IGNORED''
END'

Thanks.

Monday, August 19, 2019

Check Database backup History, SQL Server

Always I like to use below SQL to find the backup history either DB backup or LOG backup. this is very handy and useful.

By this SQL you can find the backup DB name,  date-time when the backup was taken, media to use, backup-size, logical path to take backup and many more...

By this SQL you also can find the location where to take backup last time ...

Below Query will show the result for lastest 10 days history. If you need to number of the day of history to check, please change in WHERE condition...


SELECT CONVERT(CHAR(100), Serverproperty('Servername')) AS Server,
       bkset.database_name,
       bkset.backup_start_date,
       bkset.backup_finish_date,
       bkset.expiration_date,
       CASE bkset.type
         WHEN 'D' THEN 'Database'
         WHEN 'L' THEN 'Log'
       END                    AS backup_type,
       bkset.backup_size,
       bkf.logical_device_name,
       bkf.physical_device_name,
       bkset.NAME             AS backupset_name,
       bkset.description,
       *
FROM   msdb.dbo.backupmediafamily bkf
       INNER JOIN msdb.dbo.backupset bkset
               ON bkf.media_set_id = bkset.media_set_id
WHERE  ( CONVERT(DATETIME, bkset.backup_start_date, 102) 

                 >= Getdate() - 10 )
ORDER  BY bkset.database_name,
          bkset.backup_finish_date 


This SQL will give output as below .. due to some restriction, I am blackout some data ...


Sample Output of the above SQL

Thursday, April 18, 2019

Is Backup/Restore Database/Log running in SQL Server?

Sometimes it's very important to know whether database or log backup or restore is running or not!

in a word, if we need to check the stat/status of a database, we have to run below tSQL. This SQL will show the database names where a backup/restore is running whatever it is DB or LOG backup.

---------------


SELECT r.session_id
,DB_NAME(r.database_id) [Database]
    ,r.command
    ,CONVERT(NUMERIC(32, 2), r.percent_complete)  [Complete (%)]
    ,GETDATE()  [Current Database Time]
    ,CONVERT(NUMERIC(32, 2), r.total_elapsed_time / 1000.0 / 60.0) [Running Time (Minute)]
    ,CONVERT(NUMERIC(32, 2), r.estimated_completion_time / 1000.0 / 60.0) [Required Time (Minute)]
       ,(select t.text FROM sys.dm_exec_sql_text(sql_handle) t ) 'Statement text'
FROM master.sys.dm_exec_requests r
WHERE command like 'RESTORE%'
or  command like 'BACKUP%'