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.

Idera_Perf_0

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

Idera_Perf_1

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.

Idera_Perf_2

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

Azure – All DBs that are not in Elastic pools

--

SELECT
       @@SERVERNAME as [ServerName],
       dso.elastic_pool_name,
       d.name as DatabaseName,
       dso.edition
FROM
       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
       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

 


;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