All Databases without geo-replica on Azure Server

Query to find all Azure database without geo-replica configured.

--

select d.* from sys.geo_replication_links r
right join sys.databases  d on d.database_id = r.database_id
where r.database_id is NULL 

--
Posted in Azure, SQL, SQL Query, SQL Server, Uncategorized | Tagged , , | Leave a comment

SQL database and backup size of all databases on a server

--
SELECT @@SERVERNAME as ServerName, d.name,
ROUND(SUM(mf.size) * 8 / 1024, 0) Size_MBs
FROM sys.master_files mf
INNER JOIN sys.databases d ON d.database_id = mf.database_id
WHERE d.database_id > 4 -- Skip system databases
GROUP BY d.name
ORDER BY d.name

-- Backup size is not accurate
select database_name, max(backup_size)/1024/1024 as Backupsize_MB from msdb.dbo.backupset bs WITH (NOLOCK)
JOIN msdb.dbo.backupmediafamily bmf  WITH (NOLOCK) ON bs.media_set_id = bmf.media_set_id
WHERE bs.type = 'D'
AND bs.backup_start_date <= DateAdd(dd, -7, GETDATE())
group by database_name
--
Posted in SQL Server, Uncategorized | Tagged , | Leave a comment

TSQL to find Deadlock count in Azure Server by date

 


;WITH CTE AS (
SELECT * FROM sys.event_log
WHERE event_type = 'deadlock')
SELECT convert(DATE,start_time) as Day, database_name, count(*) as Deadlock_count FROM CTE
GROUP BY convert(DATE,start_time), database_name
ORDER BY 1

--

 

Posted in Azure, SQL, SQL Query, Uncategorized | Tagged , , | Leave a comment

Refine your search in outlook

After moving all alerts and typical less priority mail I receive 150 mails daily in my inbox. over the time it is very difficult to search a mail.
All these years I have been using Outlook search only with keyword. Recently I have started using To:, From: criteria but when we need to refine you can use Outlook Refine search tools to narrow down your search.

Example:
from:”Vijay” subject:”AppName” hasattachments:yes to:”ManagerName” received:last month.
flagstatus:followup

Sample:

refinedsearchinoutlook

 
04/18/2017:
hasflag:true to filter mails with Flag

Posted in Productivity, Uncategorized | Tagged , , , , , , , , , | Leave a comment

TSQL to find users and their access on a given database

TSQL to find users and their access on a given database

--
SELECT      dp1.name AS DatabaseRoleName,
            COALESCE(DP2.name, 'No members') AS DatabaseUserName
FROM        sys.database_principals AS dp1
LEFT  JOIN  sys.database_role_members AS drm
      ON    drm.role_principal_id = dp1.principal_id
LEFT  JOIN  sys.database_principals AS dp2
      ON    dp2.principal_id = drm.member_principal_id
WHERE       dp1.type = 'R'
ORDER BY    dp1.name 



SELECT pr.principal_id, pr.name, pr.type_desc,   
    pr.authentication_type_desc, pe.state_desc,   
    pe.permission_name, s.name + '.' + o.name AS ObjectName  
FROM sys.database_principals AS pr  
JOIN sys.database_permissions AS pe  
    ON pe.grantee_principal_id = pr.principal_id  
JOIN sys.objects AS o  
    ON pe.major_id = o.object_id  
JOIN sys.schemas AS s  
    ON o.schema_id = s.schema_id;   

-- Check access given to a specific role:
SELECT pr.principal_id, pr.name, pr.type_desc, 
    pr.authentication_type_desc, pe.state_desc, pe.permission_name , schema_name(pe.major_id) as Schema_name, * 
FROM sys.database_principals AS pr
JOIN sys.database_permissions AS pe
    ON pe.grantee_principal_id = pr.principal_id
where pr.name = 'myCust_readOnly_role'

-- Give Read access on a schema to a role
GRANT SELECT
    ON SCHEMA::[NewSchemaName] TO [myCust_readOnly_role];


--
--
Posted in Script, SQL, SQL Query, Uncategorized | Tagged , , , | Leave a comment

TSQL – Restore database,store filelistonly, restore on litespeed server

--

-- Show the lsit of all database files from backup files
-- This also includes the size of each file being restored, this helps to analyze and move specific file to a different folder.
RESTORE FILELISTONLY FROM DISK =  N'E:\FilePaath\File.BAK', WITH FILE = 1
GO


-- Restoring a Litespeed backup 
exec master.dbo.xp_restore_database @database = N'New_DatabaseName' ,
@filename = N'E:\FilePaath\File.LSBAK',
@filenumber = 1,
@with = N'STATS = 10',
@with = N'MOVE N''DB_dat1'' TO N''L:\PATH\mydatafile.ndf''',
@with = N'MOVE N''DB_Log1'' TO N''L:\PATH\mylogfile.ndf''',
@affinity = 0,
@logging = 0

-- Restoring a Litespeed backup 
RESTORE DATABASE 'New_DatabaseName' FROM DISK = N'E:\FilePaath\File.LSBAK'
WITH MOVE 'DB_dat1' TO 'L:\PATH\mydatafile.ndf',
MOVE 'AdventureWorks_Data' TO 'c:\mssql\data\yukon\AdventureWorksCopy_Data.mdf', 
MOVE 'AdventureWorks_Log' TO 'c:\mssql\log\yukon\AdventureWorksCopy_Log.ldf', 
,RECOVERY, REPLACE
--
--
Posted in SQL, SQL Server, Uncategorized | Tagged , , , | Leave a comment

TSQL to find size of all databases on a Server – Azure

--
-- DatabZse Name and Database Size

select
       @@serverName as ServerName,
    db.name as DatabaseName,
       (select top 1 s.storage_in_megabytes from sys.resource_stats s where s.database_name = db.name order by s.start_time desc) as DatabaseMB
from
       sys.databases db 
order by db.name 


-- DB Size and additional details including pool name 
select
       @@serverName as ServerName,
    db.name as DatabaseName, db.create_date, db.state_desc, 
   (select top 1 s.storage_in_megabytes from sys.resource_stats s where s.database_name = db.name order by s.start_time desc) as DatabaseMB
,do.edition, do.service_objective, do.elastic_pool_name
from
       sys.databases db JOIN sys.database_service_objectives do on db.database_id = do.database_id
order by db.name 
--
--
Posted in Azure, Script, SQL, SQL Query, SQL Server, Uncategorized | Tagged , , , , | 1 Comment