Author: ADsql

Change database owner for multiple DBs in one Go

Disclaimer:Hey Guys, this post contains affiliate link to help our reader to buy best product\service for them. It helps us because we receive compensation for our time and expenses.

Note:  Best viewed in Internet Explorer 6 and above

Implementation: How to Change database owner for multiple DBs in one Go

Scenario: Lets say, you want to change DB owner for multiple DBs to say ‘SA’ in case  when the old owner is not active user/account on SQL server.

Infrastructure: The below query/procedure has been run/tested successfully on SQL server 2005,2008,2008 R2 and 2012 resp.

 

Dear Visitors!
To get Best Deals and Discounts Buy Now from Amazon just by
Clicking This Image

Simply send snap of your product details after delivery at ad22092012@gmail.com for more surprises or additional discounts

 

 

 

Resolution:

select ‘use ‘ + ‘[‘+ name + +’]’+  ‘;’ +’ ‘+ ‘exec sp_changedbowner ‘+’ ‘+ ”’sa”’ + ‘;’  from sys.databases

where suser_sname(owner_sid)  in (‘ ‘)

You can put the filter clause as per the requirement.

Hope this helps!

PowerShell –SQL Automate Tutorial-1

Disclaimer:Hey Guys, this post contains affiliate link to help our reader to buy best product\service for them. It helps us because we receive compensation for our time and expenses.

Note:  Best viewed in Internet Explorer 6 and above

Implementation: Basic of PowerShell before diving into SQL automation with the help of PS

Scenario: PS is most powerful and very helpful tool for windows and SQL server administrator and automation. So its todays need to learn at least basic of some PS cmdlets.

Dear Visitors!
To get Best Deals and Discounts Buy Now from Amazon just by
Clicking This Image

Simply send snap of your product details after delivery at ad22092012@gmail.com for more surprises or additional discounts

 

 

Note: Whether PS scripts are getting executed through CMD or On PS window itself we need to first check the Execution policy.

The Default Execution Policy is set to restricted; you can see it by typing:

Get-ExecutionPolicy

If you get restricted policy then change it to RemoteSigned to run the PS command.

Open the PS window (Run as Administrator) for both 32-64 bits and type below command:

For Windows 7, Windows 8, Windows Server 2008 R2 or Windows Server 2012, and so on run the following commands as Administrator:

Set-ExecutionPolicy RemoteSigned

Or you can bypass the execution policy

Example: powershell.exe  -noprofile  -executionpolicy  bypass  -file .\script.ps1

If you do not change to RemoteSigned you will be welcomed with below error message.

Example.ps1 cannot be loaded because the execution of scripts is disabled on this system.

Okay cool!!

Let’s start with code and explanation:

  • Step 1:- Create a simple text file.
  • Step 2:- Write any PowerShell commands

E.g. Get-Process | Sort-Object ID

Note: Get-Process using this you can get a list of processes; “Sort-Object ID” for the list will be sorted by ID. Use pipe symbol for separation “|”

  • Step 3:- Rename extension .txt to .PS1 extension (Create at some location with FirstPowershell.PS1 e.g. E:\Dharmesh\PSTutorial \getprocess.ps1)
  • Step 4:- Open PowerShell and Run E:\Dharmesh\PSTutorial \getprocess.ps1

Output: ID column (object) is sorted in acceding order by default. You can put more than one object (column) in sorting.

Getprocess

Happy Learning!!

 

 

 

Error While Restoring Databases in SQL Server Cluster-Only Formatted Files On Which The Cluster Resource Of The Server Has Dependency Can Be Used

Disclaimer:Hey Guys, this post contains affiliate link to help our reader to buy best product\service for them. It helps us because we receive compensation for our time and expenses.

Note:  Best viewed in Internet Explorer 6 and above

Dear Visitors!
To get Best Deals and Discounts Buy Now from Amazon just by
Clicking This Image

Simply send snap of your product details after delivery at ad22092012@gmail.com for more surprises or additional discounts
 

Implementation: Restore databases in SQL server fail-over cluster from backup files

Scenario: Today We have been asked to restore few databases from mirrored backup files

The backup files and mirror backup restore syntax were fine and Its two node active passive cluster say AT (Active) and Passive (PS).

Error:

Server: Msg 5184, Level 16, State 2, Line 1. Cannot use file ‘%.*ls’ for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used.

Server: Msg 1802, Level 16, State 1, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check previous errors.

InfrastructureThis steps are from the Windows server 2008 onward cluster .

To add a disk as a dependency to the SQL Server, the shared cluster disk must reside in the same group in the Cluster Administrator as the SQL Server resources.

Resolution:

First make sure the directory where you are restoring (creating) database should be added in SQL Server Group If not follow below instruction…

To move the shared cluster disk, Open Fail-over Cluster manager–>Go to Disk–>select the disk you want to move to the SQL Server group, and then right-click that resource and Change Group. After the disk is in the same group in which the SQL Server resource resides, follow these steps to add it as a SQL Server dependency:

  1. Open the Cluster Administrator (cluadmin).
  2. Make sure that all the physical disk resources that contain SQL Server databases are in the same group as the SQL Server resource.
  3. Right-click the SQL Server resource, and then bring the resource into an Offline state by clicking Bring Offline.
  4. Right-click the SQL Server resource, and then click Properties.
  5. Click the Dependencies tab.
  6. Click Modify to add the disk to the dependencies list for that resource.
  7. Bring the SQL Server resource back online, and then put the SQL Server files on that shared cluster disk
  8. Once this is done, now execute DB restoration or DB creation command.

Hope this helps!

 

 

Common SSPI handshake failed with error code 0x8009030c in SQL errorlog

Disclaimer:Hey Guys, this post contains affiliate link to help our reader to buy best product\service for them. It helps us because we receive compensation for our time and expenses.

Note:  Best viewed in Internet Explorer 6 and above

 

Error: SSPI handshake failed with error code 0x8009030c in SQL errorlog

InfrastructureSQL server 2005/2008/2008R2/2012 and so on..

Resolution:

Best explain by Microsoft.Follow the link below.

https://blogs.msdn.microsoft.com/docast/2016/02/11/common-sspi-handshake-failed-errors-and-troubleshooting/

Dear Visitors!
To get Best Deals and Discounts Buy Now from Amazon just by
Clicking This Image

Simply send snap of your product details after delivery at ad22092012@gmail.com for more surprises or additional discounts
 

 

Ls_Restore job failed every-time when executed through job throws error “Executed as user: Domain\UserAccount. The step failed.”

Disclaimer:Hey Guys, this post contains affiliate link to help our reader to buy best product\service for them. It helps us because we receive compensation for our time and expenses.

Note:  Best viewed in Internet Explorer 6 and above

Implementation: Configure Log shipping in SQL server 2016.

Scenario: Today I have faced very strange issue while configuring log shipping in SQL server 2016 standard edition.

The prerequisites for configuring log shipping were already been taken care.

  1. The backup log job (ls_backup) was happening as per its schedule.
  2. Copy job, copying TRN file to DR server with issue
  3. Here comes ls_restore job, this job was failing continuously when it was being from ls_restore job and throwing   “Executed as user: Domain\UserAccount. The step failed.” every time.
  4. I rechecked log shipping prerequisites ,verify logins and its rights everything but no luck

Infrastructure: Log shipping between SQL server 2016 standard edition.

Resolution:

1.SQL server 2016 requires .NET framework 4.2 as the prerequisites It was already there.Mostly people think that If you have .NET 4.2 that fine.

2. But here for log shipping it is require to have .NET 3.5 features enabled as well for the backward compatibility.

3.Install .NET framework 3.5 on DR server restart DR server and now let the ls_restore job run with its schedule time.

4.Now you will see that the log shipping will be running fine with no issue further.

This was really strange issue, since it is recommended to have .NET 3.5 enabled on server whether you already have 4.2 framework installed.

Hope this will help few DBAs who can come across with such issue “Executed as user: Domain\UserAccount. The step failed.”

Dear Visitors!
To get Best Deals and Discounts Buy Now from Amazon just by
Clicking This Image

Simply send snap of your product details after delivery at ad22092012@gmail.com for more surprises or additional discounts

 

 

 

 

 

 

How to Fail-over MSDTC and Quorum Resources in Windows server 2003/R2 cluster

Disclaimer:Hey Guys, this post contains affiliate link to help our reader to buy best product\service for them. It helps us because we receive compensation for our time and expenses.

Note:  Best viewed in Internet Explorer 6 and above

Implementation: How to Fail-over MSDTC and Quorum Resources in Windows server 2003 R2 cluster.

Scenario: Today I have been asked to fail-over the MSDTC and Quorum cluster resources to another active node where SQL server resource group was online and owned.

Consider two node NODE15 and NODE16 and active/passive cluster scenario.

  1. SQL Server resource was owned by Node15 (Assume)
  2. MSDCT and Quorum was owned by Node 16(Assume)

Since the MSDTC and Quorum was owned by NODE16 hence the its drives,network name and IP was also owned by NODE16.

So client wanted to move resources back to NODE15.

InfrastructureThis steps are for the Windows server 2003/R2 cluster only.

The Fail-over cluster is totally changed,manageable and handy from windows server 2008 onward.

For Windows server 2003/R2 it is bit confusing.

Here I am going to guide how to fail-over the MSDTC and Quorum resource to another node.

Step 1:

Go to Server NODE16–>All program–>fail-over cluster manager–>Expand the Windows fail-over cluster name–>expand the group–>Highlight the MSDTC group

Once you highlight the MSDTC group, on right pane you will see its resources list like Disk,IP and network name.

MSDTC Resource.png

Note: Here the MSDTC group resources are online ad owned at NODE16

Step 2:

Now right click on the MSDTC group and select the option move group as per image above.
The MSDTC group is separate group

Note: As soon as you click move group, Cluster manager start moving resources to NODE15 and you can see the change in status on right pane.

Step 3:

Move Quorum Disk:

Go to Server NODE16–>All program–>fail-over cluster manager–>Expand the Windows fail-over cluster name–>expand the group–>Highlight the cluster group

Once you highlight the cluster group, on right pane you will see its resources list like cluster name,IP and Quorum disk.Qourum disk.png

Note: As soon as you click move group, Cluster manager start moving resources to NODE15 and you can see the change in status on right pane.

This is how you can fail-over the MSDCT resources and Quorum disk in windows server 2003/R2.

Hope this will help out for those DBA who may receive same requirements from client.

Dear Visitors!
To get Best Deals and Discounts Buy Now from Amazon just by
Clicking This Image

Simply send snap of your product details after delivery at ad22092012@gmail.com for more surprises or additional discounts
 

 

 

 

 

 

 

 

Get Alerts For Long Running Queries By Duration

Disclaimer:Hey Guys, this post contains affiliate link to help our reader to buy best product\service for them. It helps us because we receive compensation for our time and expenses.

Note:  Best viewed in Internet Explorer 6 and above

Implementation: How to get alerts for long running queries by duration.

Dear Visitors!
To get Best Deals and Discounts Buy Now from Amazon just by
Clicking This Image

Simply send snap of your product details after delivery at ad22092012@gmail.com for more surprises or additional discounts
 

Scenario: Assume,you are asked to put alerts crossing threshold  on SQL server to intimate whenever there is long running queries hit on server

Infrastructure:The below query/procedure has been run/tested successfully on sql server 2005,2008,2008 R2 and 2012/2014.

Kindly find the below link for the same.

https://app.box.com/s/wu781ut4unptz6li5k6k6ymjff975jbf

spid1001 QueryInterface failed for “DTC_GET_TRANSACTION_MANAGER_EX::ITransactionDispenser”:0x8007138f(The cluster resource could not be found

Disclaimer:Hey Guys, this post contains affiliate link to help our reader to buy best product\service for them. It helps us because we receive compensation for our time and expenses.

ISSUE: Unable to perform distributed transaction in sql linked server and event viewer captures the error like ‘ITransactionDispenser:0x8007138f(The cluster resource could not be found’.

Dear Visitors!
To get Best Deals and Discounts Buy Now from Amazon just by
Clicking This Image

Simply send snap of your product details after delivery at ad22092012@gmail.com for more surprises or additional discounts

 

Follow the best link which I have found on search.

How to Properly Configure DTC for Clustered Instances of SQL Server (Revised)

https://www.mssqltips.com/sqlservertip/2083/troubleshooting-sql-server-distributed-transactions-part-1-of-2/

https://www.mssqltips.com/sqlservertip/2113/troubleshooting-sql-distributed-transactions-part-2-of-2/

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/45176b24-26f9-4ffc-bfdf-cb97f4737958/distributed-transactions-not-working?forum=sqldatabaseengine

A timeout (30000 milliseconds) was reached while waiting for a transaction response from the MSSQLSERVER service

Disclaimer:Hey Guys, this post contains affiliate link to help our reader to buy best product\service for them. It helps us because we receive compensation for our time and expenses.

ISSUE: A timeout (30000 milliseconds) was reached while waiting for a transaction response from the MSSQLSERVER service.

Dear Visitors!
To get Best Deals and Discounts Buy Now from Amazon just by
Clicking This Image

Simply send snap of your product details after delivery at ad22092012@gmail.com for more surprises or additional discounts
 
 

Follow the best link which I have found on search.

SQL SERVER – A Timeout (30000 milliseconds) was Reached While Waiting for a Transaction Response from the MSSQLSERVER

https://blogs.msdn.microsoft.com/psssql/2010/07/21/why-does-preemptive_os_getprocaddress-show-a-large-accumulation/

https://support.microsoft.com/en-us/kb/3126587

Get SQL Server agent Service Stopped Mail Notification

Implementation: Get a mail alert via powershell when SQL server agent service gets stopped.

Scenario: When SQL server agent service gets stopped,its dependence jobs,DR,operators,DB mail,replication failed to succeed.In these case,you can have a mail alert which will notify whenever agent service gets stopped by triggering mail to provided mail id via windows powershell.

Infrastructure:The below query/steps have been run/tested successfully on SQL server 2005,2008,2008 R2 and 2012.

Steps:

First write the Powershell code below in notepad and save in location say F:\   drive with name “WorkingPowershellScripts.ps1” without quote.

#Create a .net mail client

#Get the Machine Name

$SrvrName=$env:computername

#Put the recepient name.Put only one recepient here.

$recpts = “dharmesh.mishra@cloverinfotech.com”

#Enter your SMTP mail server name in place of email.cloverinfotech.com

$smtp = new-object Net.Mail.SmtpClient(“email.cloverinfotech.com”)

$subject=”Microsoft SQL Agent Service is down on Server” + ” ” + $SrvrName

#Put the from address

$from=”dharmesh.mishra@cloverinfotech.com”

$body=”Dear Team,`n`nPlease Check the SQL Agent Service Status On”+ ” “+   $SrvrName+”.”+” For any assistance plz mail us on dharmesh.mishra@cloverinfotech.com “+”`n`nThanks and Regards,`nDharmesh Mishra`nClover TSG-SQL Consultant`nCell No 7506268860”

foreach ($recpt in $recpts){

$msg = New-Object system.net.mail.mailmessage

$msg.From = $from

#Here you can add as many as mail id example

#$msg.To.add(“puneet.jadhav@cloverinfotech.com”)

$msg.To.add(“dharmesh.mishra@cloverinfotech.com”)

$msg.Subject = $subject

$msg.Body = $body

$smtp.Send($msg)

}

Copy the above code from below location.

https://app.box.com/s/zhwsdrfi16vedm36v749z631kmrg4ezz

 

Step 2:

Create one batch file to run above .PS1 file

Open notepad and copy the code below and save in drive say F:\ with name “RunPSByCMD.bat” with quote.

@ECHO OFF

SET ThisScriptsDirectory=F:\

SET PowerShellScriptPath=F:\WorkingPowershellScripts.ps1

PowerShell -NoProfile -ExecutionPolicy Bypass -Command “& {Start-Process PowerShell -ArgumentList ‘-NoProfile -ExecutionPolicy Bypass -File “”%PowerShellScriptPath%””‘ -Verb RunAs}”;

Copy the above code from below location.

https://app.box.com/s/lrjuiiwkhtkre4dbwdzfte90b2lpdj8z

Step 3:

Now go to task scheduler and create basis task. Follow the screen shot below.

Create a basic task.

1

Name the task and description(Optional)

2

 

 

 

 

3

4

5

6

8911

12