Find Stored procedure in SQL Server

we can find the Stored procedure using TSQL or by GUI using filter

TSQL:

select * 
from 
   sys.procedures 
where 
   name like '%SP_name%'

GUI :

Check for open ports on the windows server

netstat -ano

netstat –an | find “1433”

Established – Actual connections between your machine and remote IP and port

Listening – open ports listening for inbound connections

How to stripe SQL Server database backups Using Ola Hallengren’s DatabaseBackup procedure

with the @NumberOfFiles option you can stripe the SQL server Database backups upto 64.

EXECUTE dbo.DatabaseBackup

@Databases = ‘Adventureworks’,

@Directory = ‘E:\Backup\DBBackups\’,

@BackupType = ‘FULL‘,

@Compress = ‘Y‘,

@NumberOfFiles = 8

adding databases to AG with DBATools

used below script to add the databases DBtools,Test,proddb1 to the Avalialbility group AG_UAT

Add-DbaAgDatabase -SqlInstance primarynode1 -AvailabilityGroup AG_UAT -Database DBtools,Test,proddb1 -SharedPath \\sharedfolder\SQLBackup

SQL Server Migration with DBAtools

migration is a simple task with the DBAtools, it will copy all server level objects, jobs, logins, databases with the single click. i used below script to migrate the SQL

$param =@{
source = “sourceServer-PRDB01”
destination = “destServer-PRDB01”
sharedpath =”\168.168.63.8\yogi\Migration_folder”
backuprestore =$true
force=$true
}
Start-DbaMigration @param -Verbose

Replication latency issue on Asynchronous Availability group replica – SQL Server

we had a replication latency issue on Asynchronous availability configured nodes. the issue was started when the secondary replica went to offline during the OS patch and server reboot.

Resolved the issue after enabling the Trace Flag 1448 on both primary and secondary nodes.

Notes: This trace flag is applicable for asynchronous replicas only.

from Microsoft Docs:

1448

Enables the replication log reader to move forward even if the async secondaries have not acknowledged the reception of a change. Even with this trace flag enabled the log reader always waits for the sync secondaries. The log reader will not go beyond the min ack of the sync secondaries. This trace flag applies to the instance of SQL Server, not just an availability group, an availability database, or a log reader instance. Takes effect immediately without a restart. This trace flag can be activated ahead of time or when an async secondary fails. For more information, see this Microsoft Support article. Scope: global only

No of ways to shutdown the SQL Server service

1. by using SQL Server Configuration Manager
2. by using OS tool Services.MSC
3. by running net stop mssqlserver from a command prompt. For named instances net stop mssql$namedinstancename
4. From powershell: stop-service -name ‘servicename’
5. From Management studio (TSQL) : SHUTDOWN [WITH NOWAIT] ( WITH NOWAIT : SQL server terminate all user processes, SQL will rollback all uncompleted transactions after server restart.
6. From management studio (GUI)

SQL Server Service in Change pending state

Today after rebooting the SQL server after a OS patch, the SQL Service hanging with change pending Error.

I was able to start the services after shutdown the SQL service by running below query in SSMS

SHUTDOWN WITH NOWAIT

Export logins with permissions: DBATools

I have tried different scripts to transfer the logins with permissions, in all of that I like the DBATools command (Export-DBAlogin). which is easy and simple. If DBATools is not installed on your machine, you can do that offline with simple steps

1. Download DBATools from https://dbatools.io/offline/ . once the file has been downloaded unzip and place on below folders C:\Temp
2. copy the same folder to  one of the directories in your $env:PSModulePath (type $env:PSModulePath at the prompt and press enter). This will allow PowerShell to autoload the module, saving you from having to Import-Module each time you start a new session.
3. from PS > import-module dbatools ( you may need to grant Set-ExecutionPolicy Unrestricted)

once the installation is completed, use below command to export the logins.

Export-dbalogin -sqlinstance servername -filepath  ‘filepath’

— if you see execution policy-related errors while importing the DBATools module, try below one

set-executionpolicy -scope process -executionpolicy bypass

import-module DBATools

Database cannot be reverted. Either the primary or the snapshot names are improperly specified, all other snapshots have not been dropped, or there are missing files.

I was trying to revert a database to a database snapshot using below TSQL and got below error. After reading MS docs I came to know that , we need to drop any other database snapshots before restoring.

use master
RESTORE DATABASE test from
DATABASE_SNAPSHOT = ‘test_snapshot5 ‘
GO

---

————————-$$$$$$$$$$$$$$$$$$——————————-

TSQL to create snapshot:

--use master
CREATE DATABASE test_snapshot6 ON( NAME = test, FILENAME ='C:\Users\xxxxxy\Desktop\delete\test_snapshot6.ss' )
AS SNAPSHOT OF test
GO

some more facts about Snapshots : Reasons to why we can’t use snapshots instead of native SQL backups.

1. Database snapshot on the system databases are not allowed.
2. can’t create database snapshot on different server. it should remain on the same server instance as the source database
3. if the database is offline or corrupted, we can’t access Snapshots. will get error like below one