How to Kill SQL Threads or Kill User Sessions

0


How to Kill SQL Threads or Kill User Sessions

Method-1

This is one of best and quickest way to Kill All Threads or Kill All User Sessions or Kill All Sessions

Sometime you may get error while restoring a database that action cannot be performed as user’s sessions are active on the database. If it is important to restore and killing database user’s session won’t harm much, then you can use below script. This will temporally change the access mode to single user and kill sessions, and then revert it to multi user mode.

Note: This script will kill all users session associated with a database. In this script I have demonstrated AdventureWorks database.

USE master;

GO

ALTER DATABASE AdventureWorks

SET SINGLE_USER

WITH ROLLBACK IMMEDIATE;

ALTER DATABASE AdventureWorks

SET MULTI_USER;

GO


Method-2

Let’s say you want to kill all processes by a particular user or account, then you can use below script to find the related spid for all the processes initiated by user or account. Here my account is HABIB\Administrator

SELECT spid from master..sysprocesses

WHERE loginame = ‘HABIB\Administrator’


Once you get the spid details, you can simply execute KILL command against those processes.

Command is KILL <spid>

 

For more article updates, videos and posters join our official page in Facebook

Facebook Page: https://www.facebook.com/ServerGeeks

Web Site: https://servergeeks.wordpress.com/

Video Channel: https://www.youtube.com/user/Habibmvp

Advertisements

How to find list of all FOREIGN KEY, UNIQUE and PRIMARY KEY CONSTRAINTS

1


How to find list of all FOREIGN KEY, UNIQUE and PRIMARY KEY CONSTRAINTS

Method-1

You can always manually check the Constraint details using SQL Server Management Studio. However, if you just want to check all the Constraints and related tables in just one report, then this article will be a great help.

To know more about Constraints in SQL server, refer to below TechNet Article

http://technet.microsoft.com/en-us/library/ms189862(v=sql.105).aspx

Method-2

Sometimes to troubleshoot issues like Duplicate Keys, Constraints error and etc., we need details of the Keys and their corresponding table. Using INFORMATION_SCHEMA.TABLE_CONSTRAINTS you can view all the contraints for corresponding tables in a database. The bleow query give you result with Constraint_name, table_name and constraint_type columns and display all the Key Constraints in database.

SELECT constraint_name, table_name,constraint_type

FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS

If you want to display only a specific type of Key constraints then you can use the below script. In this example we will get result of all UNIQUE Key Constraints. You can modify the highlight part in script to get other constraints list.

SELECT constraint_name, table_name,constraint_type

FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS

WHERE constraint_type =
UNIQUE

ORDER
BY table_name


Method-3

If you want to see the Constraint details for a particular table, then you simply execute

SP_HELP <table name>

Let’s say I want to see all the associated constraints for table AccountBase then our script will be

sp_help AccountBase


For more article updates, videos and posters join our official page in Facebook

Facebook Page: https://www.facebook.com/ServerGeeks

Web Site: https://servergeeks.wordpress.com/

Video Channel: https://www.youtube.com/user/Habibmvp

DNS Zone Export

1

DNS Scenario – Zone Export

Your company has a DNS server that has 10 Active Directory integrated zones.

You need to provide copies of the zone files of the DNS server to the security department. What should you do?

A. Run the dnscmd /ZoneInfo command.

B. Run the ipconfig /registerdns command.

C. Run the dnscmd /ZoneExport command.

D. Run the ntdsutil > Partition Management > List commands.

Correct Answer: C

Explanation:

In Non-AD Integrated DNS Zones

DNS zone file information is stored by default in the %systemroot%\windows\system32\dns folder. When the DNS Server service starts it loads zones from these files. This behavior is limited to any primary and secondary zones that are not AD integrated. The files will be named as <ZoneFQDN>.dns.

In AD Integrated DNS Zones

AD-integrated zones are stored in the directory they do not have corresponding zone files i.e. they are not stored as .dns files. This makes sense because the zones are stored in, and loaded from, the directory.

Now it is important task for us to take a backup of these AD integrated zones before making any changes to DNS infrastructure. Dnscmd.exe can be used to export the zone to a file. The syntax of the command is:

DnsCmd <ServerName> /ZoneExport <ZoneName> <ZoneExportFile>
<ZoneName>   — FQDN of zone to export
/Cache to export cache

As an example, let’s say we have an AD integrated zone named habib.local, our DC is server1. The command to export the file would be:

Dnscmd server1 /ZoneExport habib.local habib.local.bak

You can refer to a complete article on DNSCMD in Microsoft TechNet website

http://technet.microsoft.com/en-us/library/cc772069(v=ws.10).aspx