Search SQL Error Logs using TSQL – xp_readerrorlog

exec xp_readerrorlog 0	-- 0=current SQLServer log
,1 -- 1=SQL Server log, 2=SQL Agent log
,N'availability group'					-- Search string
,null					-- second search string
,'2013-11-10 15:30:39.083'			-- start date
,'2013-11-11 15:30:39.083'	-- end date
,N'DESC'				-- order the logs by date

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

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],
       dso.elastic_pool_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 
ORDER BY, 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

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

-- 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