Install SQL Server Management Studio (SSMS)

Now SSMS (SQL Server Management Studio) can be installed as a stand alone, need not download full version only for SSMS.

SSMS can be downloaded and installed from:
https://msdn.microsoft.com/en-us/library/mt238290.aspx

-Vijred

Posted in Uncategorized | Tagged , , | Leave a comment

QUERY STORE for Azure database performance analysis

Query store can be enabled on Azure SQL Database to analyze any performance issues with SQL Server.

Example: Below image indicates one of the SQL statement was executed about 57,000 times along with Query plan to understand what is consuming maximum CPU. This query alone using at least 60% of the CUP during the given time.

Query_Store-Azure

Query store on a database can be enabled using below SQL Query;

ALTER DATABASE databasename SET QUERY_STORE = ON;
--

I frequently use Regressed and Top Resource Consuming queries to analyze high DTU consumption analysis.

NOTE: Azure Portal can also be used to identify index recommendations to improve performance but Query store can be used for detailed analysis.

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

Process Monitor – Simple and efficient tool to investigate processes

While working with Microsoft on a SQL bug I have used a powerful tool (Process monitor) to troubleshoot the problem.

What is interesting with this tool:
* Simple, installation not required
* Filter is great: Filter can be applied on specific process or user or session etc
* UI is simple and efficient to clear old messages and no performance issues
* Exclude specific processes from UI to focus on what we need with just 2 clicks
* One click filter options to include/exclude specific processes (Network activity/ File System Activity/ Registry activity etc)

This tool can be downloaded from http://www.sysinternals.com -> Process Utilities -> Process Monitor

Screenshot:

ProcessMonitor

 

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

Powershell script to search specific error on all log files in given servers



$searchpattern =  "Error I want to search in log file"

$Servers = @("MYWEBAPP42","MYWEBAPP43","MYWEBAPP60","MYWEBAPP61")


foreach ($server in $servers)
{
    $mypath = "\\" + $server + "\M$\SQLDBA\MyToolPath\LogFolder"
    


    $myfiles=Get-ChildItem $mypath | where {($_.CreationTime -ge $(Get-Date).AddHours(-120))}

    foreach ($file in $myfiles)
    {
         IF ( Get-Content $file.FullName | Select-String -Pattern  $searchpattern )
        {
            Write-Host $file.FullName
       }
    } 
}

#

I have used this code to find specific activity failure during a critical process which is running across 25 different servers.
this script also searches the log files which are created in last 5 days.

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

Using SQL Snapshot backups along with Availability group

Snapshot backup is beautiful option during releases and deploying changes
specially when the database size is large like few hundreds of GBs. We have a practice to take snapshot backup during the release and delete once testing is completed on a 600+GB database.

Recently I had an opportunity to rollback the change and restore the database using snapshot backup on a database with Always-On configuration. I have a few learning worth sharing!

Snapshot Restore:
* Suspend data movement of the database in Availability group.
* Removed the database from Availability group
* Change the status of database to Single user mode
* Restore the database from Snapshot backup
* Add the database back to Availability group


-- Suspend data movement of the database in Availability group.
ALTER DATABASE database_name SET HADR SUSPEND

-- Remove the database from Availability group
ALTER AVAILABILITY GROUP MyAG REMOVE DATABASE database_name; 

-- Change the status of database to Single user mode
ALTER DATABASE [database_name] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

-- Restore the database from Snapshot backup
RESTORE DATABASE [database_name] FROM DATABASE_SNAPSHOT = 'database_name_Snapshot';

-- -- Database will be available for testing now

-- Check previous blog to add the database back into Availability group
--
Posted in Productivity, SQL, SQL Query, SQL Server, Uncategorized | Tagged , , , | Leave a comment

TSQL to add a database to existing Availability group

Following are the TSQL statements that can be used to add database into existing Availability group.


--- YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.
:Connect PrimaryServer

USE [master]

GO

ALTER AVAILABILITY GROUP [AvailGroup]
ADD DATABASE [MyDatabase];

GO

:Connect PrimaryServer

BACKUP DATABASE [MyDatabase] TO  DISK = N'\\Sharedlocation\FULL\MyDatabase.bak' WITH  COPY_ONLY, FORMAT, INIT, SKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 5

GO

:Connect SecondaryServer

RESTORE DATABASE [MyDatabase] FROM  DISK = N'\\Sharedlocation\FULL\MyDatabase.bak' WITH  NORECOVERY,  NOUNLOAD,  STATS = 5

GO

:Connect PrimaryServer

BACKUP LOG [MyDatabase] TO  DISK = N'\\Sharedlocation\FULL\MyDatabase_20160624104225.trn' WITH NOFORMAT, NOINIT, NOSKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 5

GO

:Connect SecondaryServer

RESTORE LOG [MyDatabase] FROM  DISK = N'\\Sharedlocation\FULL\MyDatabase_20160624104225.trn' WITH  NORECOVERY,  NOUNLOAD,  STATS = 5

GO

:Connect SecondaryServer


-- Wait for the replica to start communicating
begin try
declare @conn bit
declare @count int
declare @replica_id uniqueidentifier 
declare @group_id uniqueidentifier
set @conn = 0
set @count = 30 -- wait for 5 minutes 

if (serverproperty('IsHadrEnabled') = 1)
	and (isnull((select member_state from master.sys.dm_hadr_cluster_members where upper(member_name COLLATE Latin1_General_CI_AS) = upper(cast(serverproperty('ComputerNamePhysicalNetBIOS') as nvarchar(256)) COLLATE Latin1_General_CI_AS)), 0) <> 0)
	and (isnull((select state from master.sys.database_mirroring_endpoints), 1) = 0)
begin
    select @group_id = ags.group_id from master.sys.availability_groups as ags where name = N'AvailGroup'
	select @replica_id = replicas.replica_id from master.sys.availability_replicas as replicas where upper(replicas.replica_server_name COLLATE Latin1_General_CI_AS) = upper(@@SERVERNAME COLLATE Latin1_General_CI_AS) and group_id = @group_id
	while @conn <> 1 and @count > 0
	begin
		set @conn = isnull((select connected_state from master.sys.dm_hadr_availability_replica_states as states where states.replica_id = @replica_id), 1)
		if @conn = 1
		begin
			-- exit loop when the replica is connected, or if the query cannot find the replica status
			break
		end
		waitfor delay '00:00:10'
		set @count = @count - 1
	end
end
end try
begin catch
	-- If the wait loop fails, do not stop execution of the alter database statement
end catch
ALTER DATABASE [MyDatabase] SET HADR AVAILABILITY GROUP = [AvailGroup];

GO

--
--
Posted in High Availability, Productivity, SQL, SQL Server, Uncategorized | Tagged , , , | Leave a comment

Delete old files using powershell script

Powershell script to delete old backup files


# Change.1 - Update how old files needs to be deleted 
$oldTime = [int] 26 # 26 hours
#change.2: Update the path where files needs to be deleted
$PathList = "C:\del" ,"C:\del\del2"

foreach ($path in $PathList) {
	Write-Host "Deleting files older than $oldTime hours, in $path" -ForegroundColor Green
#Change.3 Update the include files to guide which type of files to be deleted. Remove the clause to delete all files
#change.4 Remove Archive criteria if it is not applicable.
	Get-ChildItem $path -Recurse -Include "*.txt", "*.out", "*.bacpac" | where {($_.attributes -notmatch "Archive") -and ($_.CreationTime -le $(Get-Date).AddHours(-$oldTime))} | Remove-Item -Force

}



# NOTE: in SQL Server, I have removed comments and made few modifications to address escape character failures.

$oldTime = [int]26
$mydate=Get-Date
foreach ($path in Get-Content "M:\Path\pathList.txt") {
	Get-ChildItem $path -Recurse -Include "*.LSBAK", "*.LSTRN" | where {($_.attributes -notmatch "Archive") -and ($_.CreationTime -le $mydate.AddHours(-$oldTime))} | Remove-Item -Force
}

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