SELECT*

The below image will describe about Log shipping in SQL Server.

You need to pass the values like below. When you are trying to connect to the SQL Server Named instance from Windows PowerShell. Dont give instance name here in this fomat like servername\instancename. Simply pass instance name alone.

To day i have done one more task with power shell command which creates a folder in D drive. I have written the script like below. Though you can find the error in the script, however it create a folder in the respective drive.

This is my first effort connecting sql server through windows power shell command. I am able to access the SQL Server default instance with the below command and able to export or get those results to notepad.

Recently i have come across one issue in my environment where i found databases have gone into Not Synchronizing/Recovery pending state in one of the secondary replicas as shown given below. In though above image there are 4 databases have gone into NotSynchronizing/ Recovery Pending state. However i am going explain here about one database. Check the same database status Under Availability Groups-->Under Avaiability Databases. If you see the status of the database has shown like this If you see the database status shown as in Red. Now runt the below command in Secondary replica. ALTER DATABSE  DatabaseName SET HADR OFF Once you run this command you can see the database status will change to Warning mode from error mode as shown below. At the same time you observer same database status in under databases it will change to restoring mode from Not Synchronizing /Recovery pending state to Restoring mode as shown below. Now you right click on the stat...

The below image will explain you about Database level permissions at database level. Extracted from BOL.

/****** Getting logins account information  from the server who are having sysadmin Privilege. But we can use this for other servers also if need. Script Date: 10/4/2017 Author: Ramesh. M ******/ SET NOCOUNT ON USE master go DECLARE @Table TABLE (ServerRole VARCHAR(50),[Member] VARCHAR(100),MemberSID VARCHAR(1000)) DECLARE @Table1 TABLE (Sno INT IDENTITY(1,1),Name VARCHAR(2000)) DECLARE @CaptureLoop TABLE (accountname varchar(200),types varchar(200),privelege varchar(100), MappedLogin varchar(100),PermissionPath VARCHAR(100)) INSERT INTO @Table1 (Name) SELECT NAME FROM sys.server_principals WHERE type IN('g'); DECLARE @i int declare @Count INT declare @Catchvalue VARCHAR(50) SET @i=0 SELECT @Count=COUNT(*) FROM sys.server_principals WHERE type IN('g') --PRINT @Count INSERT INTO @Table (ServerRole,[Member],MemberSID) exec sp_helpsrvrolemember 'sysadmin' WHILE @I<@Count BEGIN   set @i=@i+1   SELECT @Catchvalue=Name   FROM @...

Today in my environment i got this error and realized that this happened due to memory pressure. To confirm this whether is this really happened due to memory or not  i have ran the below queries. Symtoms for Memory pressure: If you run the below command and if it shows last waittype as RESOURCE_SEMAPHORE then it meas it is clearly memory issue 1)SELECT * FROM SYSPROCESSES WHERE lastwaittype LIKE '%RESOURCE%' 2) Another query you run the below one. Which shows long running queries in SQL Server along with memory ,CPU. And at this time there is also chance that CPU also is hike at that moment. The below query will also provide you query and its execution plan. Which intern also give you the recommend indexes SELECT session_id,DB_NAME(DATABASE_ID), text, query_plan FROM sys.dm_exec_requests CROSS APPLY sys.dm_exec_sql_text(sql_handle) CROSS APPLY sys.dm_exec_query_plan(plan_handle) ; 3) Another way to look at his in the 'perfmon' counter. Which is perfo...

SELECT TOP ( 10 ) SUBSTRING(ST.text, ( QS.statement_start_offset / 2 ) + 1, ( ( CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE QS.statement_end_offset END - QS.statement_start_offset ) / 2 ) + 1) AS statement_text , execution_count , total_worker_time / 1000 AS total_worker_time_ms , ( total_worker_time / 1000 ) / execution_count AS avg_worker_time_ms , total_logical_reads , total_logical_reads / execution_count AS avg_logical_reads , total_elapsed_time / 1000 AS total_elapsed_time_ms , ( total_elapsed_time / 1000 ) / execution_count AS avg_elapsed_time_ms , qp.query_plan FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp ORDER BY total_worker_time DESC -----There is also another query for the same SELECT s.session_id, r.status, r.blocking_session_id 'Blk by', r.wait_type, wait_resource, r.wait_time / (1000 * 60) 'Wait M', r.cpu_time, r.l...

Error message: I got this error when i am trying to configure Transnational publication in replication. I have chosen pull subscription while i am configuring Transnational publication Error messages: The process could not read file 'C:\Replication\unc\SERVERA_DBCREATOR_TRANPUB_DBCREATOR\20170830103552\Subjects_2.pre' due to OS error 3. (Source: MSSQL_REPL, Error number: MSSQL_REPL20024) Get help: http://help/MSSQL_REPL20024 The system cannot find the path specified.  (Source: MSSQL_REPL, Error number: MSSQL_REPL3) Get help: http://help/MSSQL_REPL3 From Eventviewr: Replication-Replication Distribution Subsystem:  agent SERVERA-dbCreator-TranPub_dbCreator-SERVERB-6 failed.  The process could not read file 'C:\Replication\unc\SERVERA_DBCREATOR_TRANPUB_DBCREATOR\20170830103552\Subjects_2.pre'  due to OS error 3. Troubleshooting Step: 1) When i check the snapshot agent status it has run fine. No issues with it 2) When i check log re...

SELECT Statement Life Cycle Summary: UPDATE  Query Life Cycle:

WITH Waits AS ( SELECT wait_type, wait_time_ms / 1000. AS wait_time_s, 100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct, ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn FROM sys.dm_os_wait_stats WHERE wait_type NOT IN ('CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK', 'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT') ) -- filter out additional irrelevant waits SELECT W1.wait_type, CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s, CAST(W1.pct AS DECIMAL(12, 2)) AS pct, CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct FROM Waits AS W1 INNER JOIN Waits AS W2 ON W2.rn <= W1.rn GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct HAVING SUM(W2.pct) - W1.pct < 95; -- percentage threshold; If you see here "PCT" column is percentage column if this percentage is high then there...

Just mention a couple of commands which uses for Windows.Go to command prompt and run these commands. NCPA.CPL CONTROL-->ControlPanel APPWIZ.CPL-->For Uninstall/Install programs COMPMGMT.MSC-->Computer Management DISKMGMT.MSC-->Disk Management TASKMGR-->TaskManager MSINFO32-->SystemInformation WINVER-->Windows versionm MSCONFIG-->System Configuration NSLOOKUP-->Default Server CONTROL ADMINTOOLS--> Admin Tools SET L-->Local App Data TASKLIST

The dead lock behavior would be like this. This is just for  understand purpose.   In the below i have chosen another tables.                         Session1                                     Session2 1. Begin Transaction 1. Begin Transaction 2. Update Part table 2. Update Supplier table 3. Update  Supplier  table 3. Update  Part  table 4. Commit Transaction 4. Commit Transaction CREATE TABLE authors (id VARCHAR(20),Lname VARCHAR(30),Value1 VARCHAR(30),Value2 VARCHAR(30),Value3 VARCHAR(30),Value4 VARCHAR(30),Value5 VARCHAR(30),SomeValue VARCHAR(30),Sno INT) GO ---1 st window DECLARE @Au_ID VARCHAR(11) DECLARE @Au_lname VARCHAR(30) SELECT @Au_ID='111-11-1112',@Au_lname='Test1' BEGIN TRAN INSERT INTO authors VALUES(@Au_ID,@Au_lname,' ',' ',' ',' ',' ','11111',0) WAITFOR DE...

‘User Lookup’ means query is looking for more data than it is present in the NON CLUSTERED index. And this index(NON CLUSTERED) uses clustered index as look ups.Now whether it is good or bad we cannot come up with sold answers as we usually don’t know the other users systems. To avoid we can add the columns to NON CLUSTERED index which is being used. Bu the good part is we can reduce the ‘User Lookup’, bad part is NON CLUSTERED index will be heavy

Whenever a Full,Differential and Transaction log backups which are handled through SQL Server Agent those logs also stored at eventviewer of Windows. You can check the information with the below EventID's The below Event id's trigger when backup jobs got successes. 18270--Differential Backup 18265--Log backup 18264--Full Backup If job fails the below even id's will get trigger. 995 means third party tool backups have been used. 18210-operating system 995 third party backups or disk space. 18204- Access denied 

How to transfer logins and passwords between instances of SQL Server And if we want to migrate server level role permissions too then we can use below script. SELECT 'EXEC master..sp_addsrvrolemember @loginame = N''' + SL.name + ''', @rolename = N''' + SR.name + ''' ' AS [-- Server Roles the Logins Need to be Added --] FROM master.sys.server_role_members SRM JOIN master.sys.server_principals SR ON SR.principal_id = SRM.role_principal_id JOIN master.sys.server_principals SL ON SL.principal_id = SRM.member_principal_id WHERE SL.type IN ('S','G','U') AND SL.name NOT LIKE '##%##' AND SL.name NOT LIKE 'NT AUTHORITY%' AND SL.name NOT LIKE 'NT SERVICE%' AND SL.name <> ('sa'); SELECT CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION' THEN SrvPerm.state_desc ELSE 'GRANT' END     + ' ' + SrvPerm.permissio...

We can use this query to check when the last log has been committed in Secondary replica in Alwasy on. SELECT AGS.NAME AS AGGroupName     ,AR.replica_server_name AS InstanceName     ,HARS.role_desc     ,DRS.synchronization_state_desc AS SyncState     ,DRS.last_hardened_time     ,DRS.last_redone_time     ,((DRS.log_send_queue_size)/8)/1024 QueueSize_MB ,datediff(MINUTE, last_redone_time, last_hardened_time) as Latency_Minutes FROM sys.dm_hadr_database_replica_states DRS LEFT JOIN sys.availability_replicas AR ON DRS.replica_id = AR.replica_id LEFT JOIN sys.availability_groups AGS ON AR.group_id = AGS.group_id LEFT JOIN sys.dm_hadr_availability_replica_states HARS ON AR.group_id = HARS.group_id     AND AR.replica_id = HARS.replica_id ---One more query for  Depending on which replica was having difficulty communicating, you should query both the primary and secondary for the local replica: select ...