Powershell commands – my quick reference

# Check if a folder exists, create if not
$bybakupdir = "D:\parentFolder\NewFolder"
if(!(Test-Path -Path $bybakupdir )){
  New-Item -ItemType directory -Path $bybakupdir

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

Using Idera to investigate a problem during specific time

I had a situation to troubleshoot a performance problem on a server which occurred a few days back. Following is quick reference on how Idera can be used to drill down details.

1. Select History Icon and select the time which needs investigation

2. First option I look for Active alerts during the impacted time.


3. Check for SQL Queries for performance analysis
Sessions -> Details -> Analyze and Export to Excel.


4. Resource consumption trend
Resources -> CPU / Memory / Disk

5. Server Waits
Server waits is critical to identify bottleneck and UI provides east options to drill down Server Waits.


Posted in SQL, SQL Server, Uncategorized | Tagged , , | Leave a comment

Azure – All DBs that are not in Elastic pools


       @@SERVERNAME as [ServerName],
       d.name as DatabaseName,
       sys.databases d inner join sys.database_service_objectives dso on d.database_id = dso.database_id
WHERE d.Name <> 'master'
AND elastic_pool_name is NULL 
       d.name, dso.elastic_pool_name
Posted in Azure, SQL, SQL Query, Uncategorized | Tagged , , , | Leave a comment

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


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



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.

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



hasflag:true to filter mails with Flag

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