Tag Archives: SQL

Script to find out the traces running on SQL Server instance

You can execute the below script on SQL Server instance to find out the traces, trace type, trace file path and trace status.

select
      TraceType =
       case trace.is_default
            when 1 THEN 'Default/System Trace'
            when 0 THEN 'User Trace'
       end,
      Trace_Status =
      case trace.status
            when 1 THEN 'Running'
            when 0 THEN 'Stopped'
      end,
       ssion.session_id as SessionID,
       [loginName] = coalesce(ssion.login_name,ssion.login_name,'Reader SPID Not mentioned'),
       [Trace_File_Path] = coalesce(trace.[Path],trace.[Path],'OLEDB Client Trace')
      from sys.traces trace
            left join sys.dm_exec_sessions ssion on trace.reader_spid = ssion.session_id
Advertisements

Error: Database diagram support objects cannot be installed

There will be situation while creating the database diagram, you got the below error message.

Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects.

Above error is self explanatory where it is stating that the “set the database owner to valid login” To fix the issue please follow below one of the solution.

Solution 1:
Execute the below script by mentioning the database name.

ALTER AUTHORIZATION ON DATABASE::MentionDatabaseName TO sa
GO

OR, you can change the owner by executing the below query.

EXEC sp_changedbowner 'sa'

Solution 2:
Right Click on the database -> Database Properties -> click on files page -> change the owner to SA

How to check Lock Pages In Memory is enabled?

You can use below simple technique to check whether lock pages in memory is enabled or not. If lock pages in memory is enabled you can see the “Using locked pages for buffer pool” message in the SQL Server error log.

exec xp_readerrorlog 0, 1, 'locked pages' 

To check if it is disabled. You have to check for the “Address Windowing Extensions (AWE) requires the ‘lock pages in memory’ privilege which is not currently present in the access token of the process.” message.

exec xp_readerrorlog 0, 1, 'lock pages in memory' 

Different ways to make a table read only in a SQL Server database

Problem

In some cases there may be a need to make a SQL Server table read only. There are several different options for doing this and in this tip we cover various ways that you can make a table read only in a SQL Server database.

Solution

http://www.mssqltips.com/sqlservertip/2711/different-ways-to-make-a-table-read-only-in-a-sql-server-database/#comments

Script to Create Foreign Key on the Compound Primary Key

Compound Primary key is a primary key which is created on more than one column. Now the questions is how to create the foreign key for the compound primary key where it references more than one column.

Check the below example.

create table employee
(
	empID int not null,
	SSN int not null,
	name varchar(20)
)


ALTER TABLE [employee]
ADD CONSTRAINT pk_employee PRIMARY KEY (empID, SSN)


create table EmpDetail
(
		empID int,
		SSN int,
		address varchar(20),
		city varchar(20),
		pin varchar(20)
)

ALTER TABLE dbo.empDetail
   ADD CONSTRAINT FK_Employee
   FOREIGN KEY(empID, SSN)
   REFERENCES dbo.employee(empID, SSN)


SELECT
    tc.TABLE_NAME,
    tc.CONSTRAINT_NAME, 
    ccu.COLUMN_NAME
FROM 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
INNER JOIN 
    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu 
      ON ccu.TABLE_NAME = tc.TABLE_NAME AND ccu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
WHERE
    tc.TABLE_NAME IN ('employee','employeeDetail')

Primary Key, Unique Key Constraints – Clustered Index and Non Clustered Index

You can use the below script to create the Primary Key on the already existing tables. Primary key enforces a uniqueness in the column and created the clustered index as default.

Primary key will not allow NULL values.

-- Adding the NON NULL constraint
ALTER TABLE [TableName]	 
ALTER COLUMN PK_ColumnName int NOT NULL

--Script to add the primary key on the existing table
ALTER TABLE [TableName]
ADD CONSTRAINT pk_ConstraintName PRIMARY KEY (PK_ColumnName)

If you want to define or create the non-clustered index on the existing table, you can use the below script. If the data in the column is unique, you can create the Unique Constraint as well.

Unique Key enforces uniqueness of the column on which they are defined. Unique Key creates a non-clustered index on the column. Unique Key allows only one NULL Value.

--script to create non-clustered Index
create index IX_ColumName on TableName(ColumnName)
--script to create Unique constraint on the existing table
ALTER TABLE TableName ADD CONSTRAINT ConstraintName UNIQUE(ColumnName)

Dedicated Administrator Connection

DAC: Dedicated Administrator Connection feature is available from the SQL Server 2005. It is available in all the higher editions by default except express edition. DAC will be useful when SQL Server is not responding any connections; in such kind of situation DBA will connect through the DAC and troubleshoot/fix the issue.

You can execute below kind of command for the initial troubleshooting.

-- Locking Info
SELECT * FROM sys.dm_tran_locks
GO
-- Running Sessions
SELECT * FROM sys.dm_exec_sessions
GO
-- Requests Status
SELECT * FROM sys.dm_exec_requests
GO
--Open Sessions
SP_WHO2
--To get the SQL Text
DBCC OPENTRAN (SPID)
--To terminate the curlprit process
KILL SPID

DAC is disabled by default, it is a good practice to enable the DAC. You can enable the DAC using by executing below query.

Use master
GO
--0 = Allow Local Connection,
--1 = Allow Remote Connections*/
sp_configure 'remote admin connections', 1
GO
RECONFIGURE
GO

You can connect using DAC on of the following method.
Command Prompt
SQL Server Management Studio

Using Command Prompt: Use SQLCMD utility to connect to SQL Server as below.
-A argument is to specify the DAC connection.
-S argument is to specify the server name.
-d argument is to specify the database name.
-E argument is for windows connection with integrated security true

Using Management Studio: Write ADMIN: before the server name in management studio connection window. It will give you the DAC connection.

To enable the DAC connection in SQL Server express edition add ;-T7806 trace flag as startup parameter.

Go into configuration manager — right click on SQL Server Service and select properties — go into advanced tab and add the trace flag ;-T7806. Once done restart the SQL Server Services.