SQL DBA ISSUES
check for Orphan users if any
Exec sp_change_users_login ‘report’
–Use below code to fix Orphan User
issue
–To be used in any restored database
–To be used in any restored database
BEGIN
DECLARE
@username varchar(25)
DECLARE
@username varchar(25)
DECLARE fixusers CURSOR
FOR
SELECT UserName = name FROM sysusers
WHERE issqluser = 1 and (sid is not null and sid <> 0×0)
AND suser_sname(sid) is null
ORDER BY name
FOR
SELECT UserName = name FROM sysusers
WHERE issqluser = 1 and (sid is not null and sid <> 0×0)
AND suser_sname(sid) is null
ORDER BY name
OPEN fixusers
FETCH NEXT FROM fixusers
INTO @username
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_change_users_login ‘update_one’, @username, @username
FETCH NEXT FROM fixusers
INTO @username
END
CLOSE fixusers
DEALLOCATE fixusers
END
FETCH NEXT FROM fixusers
INTO @username
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_change_users_login ‘update_one’, @username, @username
FETCH NEXT FROM fixusers
INTO @username
END
CLOSE fixusers
DEALLOCATE fixusers
END
Download SQL Server 2008 SP3
Use below query to find the number
of connections against all databases in a particular instance.
SELECT
DB_NAME(dbid) as DBName,
COUNT(dbid) as NumberOfConnections,
loginame as LoginName
FROM
sys.sysprocesses
WHERE
dbid > 0
GROUP BY
dbid, loginame
order by NumberOfConnections desc
DB_NAME(dbid) as DBName,
COUNT(dbid) as NumberOfConnections,
loginame as LoginName
FROM
sys.sysprocesses
WHERE
dbid > 0
GROUP BY
dbid, loginame
order by NumberOfConnections desc
–You can get connection details from
below query also. You can change the values of status and open_tran
columns as per your requirement.
Select status, hostname,
program_name, cmd, loginame, DB_NAME(DBID) from sys.sysprocesses
where DB_NAME(DBID) = ‘DB_NAME’ and status = ‘sleeping’
and open_tran = 0
where DB_NAME(DBID) = ‘DB_NAME’ and status = ‘sleeping’
and open_tran = 0
–For SQL Server 2005 and above
CREATE PROCEDURE spGrantReadWriteViewToAllStoredProcs @user sysname
AS
CREATE PROCEDURE spGrantReadWriteViewToAllStoredProcs @user sysname
AS
SET NOCOUNT ON
– 1 – Variable declarations
DECLARE @CMD1 varchar(8000)
DECLARE @CMD2 varchar(8000)
DECLARE @CMD3 varchar(8000)
DECLARE @MAXOID int
DECLARE @OwnerName varchar(128)
DECLARE @ObjectName varchar(128)
DECLARE @CMD1 varchar(8000)
DECLARE @CMD2 varchar(8000)
DECLARE @CMD3 varchar(8000)
DECLARE @MAXOID int
DECLARE @OwnerName varchar(128)
DECLARE @ObjectName varchar(128)
– 2 – Create temporary table
CREATE TABLE #StoredProcedures
(OID int IDENTITY (1,1),
StoredProcOwner varchar(128) NOT NULL,
StoredProcName varchar(128) NOT NULL)
CREATE TABLE #StoredProcedures
(OID int IDENTITY (1,1),
StoredProcOwner varchar(128) NOT NULL,
StoredProcName varchar(128) NOT NULL)
– 3 – Populate temporary table
INSERT INTO #StoredProcedures (StoredProcOwner, StoredProcName)
SELECT ROUTINE_SCHEMA, ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME NOT LIKE ‘dt_%’
AND ROUTINE_TYPE = ‘PROCEDURE’
INSERT INTO #StoredProcedures (StoredProcOwner, StoredProcName)
SELECT ROUTINE_SCHEMA, ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME NOT LIKE ‘dt_%’
AND ROUTINE_TYPE = ‘PROCEDURE’
– 4 – Capture the @MAXOID value
SELECT @MAXOID = MAX(OID) FROM #StoredProcedures
SELECT @MAXOID = MAX(OID) FROM #StoredProcedures
– 5 – WHILE loop
WHILE @MAXOID > 0
BEGIN
WHILE @MAXOID > 0
BEGIN
– 6 – Initialize the variables
SELECT @OwnerName = StoredProcOwner,
@ObjectName = StoredProcName
FROM #StoredProcedures
WHERE OID = @MAXOID
SELECT @OwnerName = StoredProcOwner,
@ObjectName = StoredProcName
FROM #StoredProcedures
WHERE OID = @MAXOID
– 7 – Build the string
SELECT @CMD1 = ‘GRANT EXEC ON ‘ + ‘[' + @OwnerName + ']‘ + ‘.’ + ‘[' + @ObjectName + ']‘ + ‘ TO ‘ + @user
SELECT @CMD2 = ‘GRANT ALTER ON ‘ + ‘[' + @OwnerName + ']‘ + ‘.’ + ‘[' + @ObjectName + ']‘ + ‘ TO ‘ + @user
SELECT @CMD3 = ‘GRANT VIEW DEFINITION ON ‘ + ‘[' + @OwnerName + ']‘ + ‘.’ + ‘[' + @ObjectName + ']‘ + ‘ TO ‘ + @user
SELECT @CMD1 = ‘GRANT EXEC ON ‘ + ‘[' + @OwnerName + ']‘ + ‘.’ + ‘[' + @ObjectName + ']‘ + ‘ TO ‘ + @user
SELECT @CMD2 = ‘GRANT ALTER ON ‘ + ‘[' + @OwnerName + ']‘ + ‘.’ + ‘[' + @ObjectName + ']‘ + ‘ TO ‘ + @user
SELECT @CMD3 = ‘GRANT VIEW DEFINITION ON ‘ + ‘[' + @OwnerName + ']‘ + ‘.’ + ‘[' + @ObjectName + ']‘ + ‘ TO ‘ + @user
– 8 – Execute the string
EXEC(@CMD1)
EXEC(@CMD2)
EXEC(@CMD3)
EXEC(@CMD1)
EXEC(@CMD2)
EXEC(@CMD3)
– 9 – Decrement @MAXOID
SET @MAXOID = @MAXOID – 1
END
SET @MAXOID = @MAXOID – 1
END
– 10 – Drop the temporary table
DROP TABLE #StoredProcedures
DROP TABLE #StoredProcedures
SET NOCOUNT OFF
GO
GO
Below are major SQL Server perform
counters to be analyzed for SQL Server behaviour.
|
Performance Counter
|
Counter Object
|
Threshold
|
Notes
|
|
% Processor Time
|
Processor
|
> 80%
|
Potential causes include memory
pressure, low query plan reuse, non-optimized queries.
|
|
Context Switches/sec
|
System
|
> 5000 x processors
|
Potential causes include other
applications on the server, more than one instance of SQL Server running on
the same server, hyper-threading turned on.
|
|
Processor Queue Length
|
System
|
> 5 x processors
|
Potential causes include other
applications on the server, high compilations or recompilations, more than
one instance of SQL Server running on the same server.
|
|
Compilations/sec
|
SQLServer:SQL Statistics
|
Trend
|
Compare to Batch Requests/sec.
|
|
Re-Compilations/sec
|
SQLServer:SQL Statistics
|
Trend
|
Compare to Batch Requests/sec.
|
|
Batch Request/sec
|
SQLServer:SQL Statistics
|
Trend
|
Compare with the Compilation and Re-Compilations
per second.
|
|
Page Life Expectancy
|
SQLServer:Buffer Manager
|
< 300
|
Potential for memory pressure.
|
|
Lazy Writes/sec
|
SQLServer:Buffer Manager
|
Trend
|
Potential for large data cache
flushes or memory pressure.
|
|
Checkpoints/sec
|
SQLServer:Buffer Manager
|
Trend
|
Evaluate checkpoints against PLE
and Lazy Writes/sec.
|
|
Cache Hit Ratio: SQL Plans
|
SQLServer:Plan Cache
|
< 70%
|
Indicates low plan reuse.
|
|
Buffer Cache Hit Ratio
|
SQLServer:Buffer Manager
|
< 97%
|
Potential for memory pressure.
|
–For SQL Server 2005 and above
CREATE PROCEDURE spGrantReadWriteViewToAllStoredProcs @user sysname
AS
CREATE PROCEDURE spGrantReadWriteViewToAllStoredProcs @user sysname
AS
SET NOCOUNT ON
– 1 – Variable declarations
DECLARE @CMD1 varchar(8000)
DECLARE @CMD2 varchar(8000)
DECLARE @CMD3 varchar(8000)
DECLARE @MAXOID int
DECLARE @OwnerName varchar(128)
DECLARE @ObjectName varchar(128)
DECLARE @CMD1 varchar(8000)
DECLARE @CMD2 varchar(8000)
DECLARE @CMD3 varchar(8000)
DECLARE @MAXOID int
DECLARE @OwnerName varchar(128)
DECLARE @ObjectName varchar(128)
– 2 – Create temporary table
CREATE TABLE #StoredProcedures
(OID int IDENTITY (1,1),
StoredProcOwner varchar(128) NOT NULL,
StoredProcName varchar(128) NOT NULL)
CREATE TABLE #StoredProcedures
(OID int IDENTITY (1,1),
StoredProcOwner varchar(128) NOT NULL,
StoredProcName varchar(128) NOT NULL)
– 3 – Populate temporary table
INSERT INTO #StoredProcedures (StoredProcOwner, StoredProcName)
SELECT ROUTINE_SCHEMA, ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME NOT LIKE ‘dt_%’
AND ROUTINE_TYPE = ‘PROCEDURE’
INSERT INTO #StoredProcedures (StoredProcOwner, StoredProcName)
SELECT ROUTINE_SCHEMA, ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME NOT LIKE ‘dt_%’
AND ROUTINE_TYPE = ‘PROCEDURE’
– 4 – Capture the @MAXOID value
SELECT @MAXOID = MAX(OID) FROM #StoredProcedures
SELECT @MAXOID = MAX(OID) FROM #StoredProcedures
– 5 – WHILE loop
WHILE @MAXOID > 0
BEGIN
WHILE @MAXOID > 0
BEGIN
– 6 – Initialize the variables
SELECT @OwnerName = StoredProcOwner,
@ObjectName = StoredProcName
FROM #StoredProcedures
WHERE OID = @MAXOID
SELECT @OwnerName = StoredProcOwner,
@ObjectName = StoredProcName
FROM #StoredProcedures
WHERE OID = @MAXOID
– 7 – Build the string
SELECT @CMD1 = ‘GRANT EXEC ON ‘ + ‘[' + @OwnerName + ']‘ + ‘.’ + ‘[' + @ObjectName + ']‘ + ‘ TO ‘ + @user
SELECT @CMD2 = ‘GRANT ALTER ON ‘ + ‘[' + @OwnerName + ']‘ + ‘.’ + ‘[' + @ObjectName + ']‘ + ‘ TO ‘ + @user
SELECT @CMD3 = ‘GRANT VIEW DEFINITION ON ‘ + ‘[' + @OwnerName + ']‘ + ‘.’ + ‘[' + @ObjectName + ']‘ + ‘ TO ‘ + @user
SELECT @CMD1 = ‘GRANT EXEC ON ‘ + ‘[' + @OwnerName + ']‘ + ‘.’ + ‘[' + @ObjectName + ']‘ + ‘ TO ‘ + @user
SELECT @CMD2 = ‘GRANT ALTER ON ‘ + ‘[' + @OwnerName + ']‘ + ‘.’ + ‘[' + @ObjectName + ']‘ + ‘ TO ‘ + @user
SELECT @CMD3 = ‘GRANT VIEW DEFINITION ON ‘ + ‘[' + @OwnerName + ']‘ + ‘.’ + ‘[' + @ObjectName + ']‘ + ‘ TO ‘ + @user
– 8 – Execute the string
EXEC(@CMD1)
EXEC(@CMD2)
EXEC(@CMD3)
EXEC(@CMD1)
EXEC(@CMD2)
EXEC(@CMD3)
– 9 – Decrement @MAXOID
SET @MAXOID = @MAXOID – 1
END
SET @MAXOID = @MAXOID – 1
END
– 10 – Drop the temporary table
DROP TABLE #StoredProcedures
DROP TABLE #StoredProcedures
SET NOCOUNT OFF
GO
GO
What a virus or malware functions
mean to an Operating System, SQL injection or insertion means to a
database. A coding technique used in the strings of an
application and exploits a security vulnerability occurring in the database.
There are three main types of locks
that SQL Server uses:
- Shared locks
- Update locks
- Exclusive locks
Shared locks are used for operations that do not change or update
data, such as a SELECT statement.
Update locks are used when SQL Server intends to modify a page,
and later promotes the update page lock to an exclusive page lock before
actually making the changes.
Exclusive locks are used for the data modification operations, such as
UPDATE, INSERT, or DELETE.
Shared locks are compatible with other Shared locks or Update
locks.
Update locks are compatible with Shared locks only.
Exclusive locks are not compatible with other lock types.
Database Mirroring
Extend log shipping capabilities
with the database mirroring solution. You will be able to use database
mirroring to enhance availability of your SQL Server systems by setting up
automatic failover to a standby server.
Online Restore
With SQL Server 2005, database
administrators are able to perform a restore operation while an instance of SQL
Server is running. Online restore improves the availability of SQL Server
because only the data being restored is unavailable; the rest of the database
remains online and available.
Online Indexing Operations
The online index option allows
concurrent modifications (updates, deletes, and inserts) to the underlying
table or clustered index data and any associated indexes during index data
definition language (DDL) execution. For example, while a clustered index is
being rebuilt, you can continue to make updates to the underlying data and
perform queries against the data.
Fast Recovery
A new faster recovery option
improves availability of SQL Server databases. Administrators can reconnect to
a recovering database after the transaction log has been rolled forward.
SQL Server Management
Studio
SQL Server 2005 includes SQL Server
Management Studio, a new integrated suite of management tools with the
functionality to develop, deploy, and troubleshoot SQL Server databases, as
well as enhancements to previous functionality.
Dedicated Administrator
Connection
SQL Server 2005 provides a dedicated
administrator connection that administrators can use to access a running server
even if the server is locked or otherwise unavailable. This capability enables
administrators to troubleshoot problems on a server by executing diagnostic
functions or Transact-SQL statements.
Snapshot Isolation
Snapshot Isolation (SI) level is
provided at the database level. With SI, users can access the last committed
row using a transitionally consistent view of the database. This capability
provides greater scalability.
Data Partitioning
Data partitioning is enhanced with
native table and index partitioning that enables efficient manageability of
large tables and indexes.
Replication Enhancements
For distributed databases, SQL
Server 2005 provides comprehensive schema change (DDL) replication,
next-generation monitoring capabilities, built in replication from Oracle to
SQL Server, merge replication over https, and significant merge replication
scalability and performance improvements. Additionally, the peer-to-peer
transactional replication feature improves support for data scale out using
replication.
Indexed view
In SQL Server 2000 and 2005, a view
that has a unique clustered index is referred to as an indexed view.
From the Database Management System
(DBMS) perspective, a view is a description of the data (a form of metadata).
When a typical view is created, the metadata is defined by encapsulating a
SELECT statement that defines a result set to be represented as a virtual
table. When a view is referenced in the FROM clause of another query, this
metadata is retrieved from the system catalog and expanded in place of the
view’s reference. After view expansion, the SQL Server query optimizer compiles
a single execution plan for the executing query. The query optimizer searches
though a set of possible execution plans for a query, and chooses the
lowest-cost plan it can find, based on estimates of the actual time it will
take to execute each query plan.
In the case of a non-indexed view,
the portions of the view necessary to solve the query are materialized at run
time. Any computations such as joins or aggregations are done during query
execution for each query referencing the view1. After a unique clustered index
is created on the view, the view’s result set is materialized immediately and
persisted in physical storage in the database, saving the overhead of
performing this costly operation at execution time.
The indexed view can be used in a
query execution in two ways. The query can reference the indexed view directly,
or, more importantly, the query optimizer can select the view if it determines
that the view can be substituted for some or all of the query in the
lowest-cost query plan. In the second case, the indexed view is used instead of
the underlying tables and their ordinary indexes. The view does not need to be
referenced in the query for the query optimizer to use it during query
execution. This allows existing applications to benefit from the newly created
indexed views without changing those applications.
Views have been available throughout
the history of Microsoft SQL Server. However, using views that return very
large result sets can lead to poor performance, as the result set is not
indexed and the entire result must be table scanned if the view is used in a
join or a sub query of a T-SQL command. Additionally, products like Oracle have
come out with the concept of a Materialized View that give an additional
performance boost by being able to have indexes built on a view. So in the
continuing evolution of the SQL Server product line and in response to Oracle’s
Materialized View, Microsoft SQL Server 2000 has a new feature called the View
Index. View Indexes give the product the capability to define an index on a
view. Additionally, SQL Server View Indexes are dynamic in that changes to the
data in the base tables are automatically reflected in the indexed view. Also
the SQL Server query optimizer will try to use an indexed view even if the view
is not referenced in the from clause of a T-SQL command. These features are not
available in Oracle’s Materialized Views.
A covering index is a form of a
non-clustered composite index, which includes all of the columns referenced in
the SELECT, JOIN, and WHERE clauses of a query. Because of this, the index
contains the data the query is looking for and SQL Server does not have to look
up the actual data in the table, reducing logical and/or physical I/O and
boosting performance.
declare @ID int, @FS bigint,
@FilePath nvarchar(245), @1 bit
select @fs = 100,
@filepath =
‘F:\Trace\TSQLTrace’,
@1 = 1
exec sp_trace_create
@traceid
= @id output, — Trace ID (output)
@options
= 2, — File rollover option
@tracefile = @filepath, — Must be a local path on the server
@maxfilesize = @fs, — Trace File Size (in Meg)
@stoptime = null, — No predetermined stop time
@filecount
= 100 — Max number of trace files (rolls over)
– 10 = RPC Complete
– Sets columns for trace data
exec sp_trace_setevent @id, 10, 1,
@1
exec sp_trace_setevent @id, 10, 3,
@1
exec sp_trace_setevent @id, 10, 13,
@1
exec sp_trace_setevent @id, 10, 15,
@1
exec sp_trace_setevent @id, 10, 16,
@1
exec sp_trace_setevent @id, 10, 17,
@1
exec sp_trace_setevent @id, 10, 18,
@1
– 12 = SQL Batch Complete
– Sets columns for trace data
exec sp_trace_setevent @id, 12, 1,
@1
exec sp_trace_setevent @id, 12, 3,
@1
exec sp_trace_setevent @id, 12, 13,
@1
exec sp_trace_setevent @id, 12, 15,
@1
exec sp_trace_setevent @id, 12, 16,
@1
exec sp_trace_setevent @id, 12, 17,
@1
exec sp_trace_setevent @id, 12, 18,
@1
– Which database to trace (if not
set, will trace all)
exec sp_trace_setfilter @id, 35, 0,
0, N’MyDB’
– Turns on the trace
exec sp_trace_setstatus @id, 1
– Trace summary data (numeric codes,
not particularly human-readable)
select *
from fn_trace_getinfo(@id)
–Trace detail data
select *
from sys.traces
where id = @id
–exec sp_trace_setstatus 2, 0 — Stop
the trace
–exec sp_trace_setstatus 2, 2 —
Delete the trace
–exec sp_trace_setstatus 2, 1 —
Restart the trace
–select * from fn_trace_getinfo(0) —
Select data on all active traces
–select * from fn_trace_gettable (
filename , number_files )
— To
select data from the trace while running.
Select
session_id, blocking_session_id from sys.dm_exec_requests
An isolation level determines the
degree of isolation of data between concurrent transactions. The default SQL
Server isolation level is Read Committed. Here are the other isolation
levels: Read Uncommitted, Read Committed, Repeatable Read,
Serializable and Snapshot Isolation. Be sure to read about SET
TRANSACTION ISOLATION LEVEL, which lets you customize the isolation level at
the connection level.
READ COMMITTED
Specifies that shared locks are held
while the data is being read to avoid dirty reads, but the data can be
changed before the end of the transaction, resulting in nonrepeatable reads
or phantom data. This option is the SQL Server default.
READ UNCOMMITTED
Implements dirty read, or isolation
level 0 locking, which means that no shared locks are issued and no exclusive
locks are honored. When this option is set, it is possible to read uncommitted
or dirty data; values in the data can be changed and rows can appear or
disappear in the data set before the end of the transaction. This option has
the same effect as setting NOLOCK on all tables in all SELECT statements in a
transaction. This is the least restrictive of the four isolation levels.
REPEATABLE READ
Locks are placed on all data that is
used in a query, preventing other users from updating the data, but new phantom
rows can be inserted into the data set by another user and are included in
later reads in the current transaction. Because concurrency is lower than the
default isolation level, use this option only when necessary.
SERIALIZABLE
Places a range lock on the data set,
preventing other users from updating or inserting rows into the data set until
the transaction is complete. This is the most restrictive of the four isolation
levels. Because concurrency is lower, use this option only when necessary. This
option has the same effect as setting HOLDLOCK on all tables in all SELECT
statements in a transaction.
SNAPSHOT ISOLATION
Snapshot Isolation (SI) level is
provided at the database level. With SI, users can access the last committed row
using a transitionally consistent view of the database. This capability
provides greater scalability.
Dirty Reads occur when one transaction reads data written by another,
uncommitted, transaction. The danger with dirty reads is that the other
transaction might never commit, leaving the original transaction with “dirty”
data.
Non-repeatable Reads occur when one transaction attempts to access
the same data twice and a second transaction modifies the data between the
first transaction’s read attempts. This may cause the first transaction to read
two different values for the same data, causing the original read to be
non-repeatable.
Phantom Reads occur when one transaction accesses a range of data more than
once and a second transaction inserts or deletes rows that fall within that
range between the first transaction’s read attempts. This can cause “phantom”
rows to appear or disappear from the first transaction’s perspective.
Note: The setting of SET TRANSACTION ISOLATION
LEVEL is set at execute or run time and not at parse time.
Online Rebuild Index fails
Maintenance job mostly fails at Rebuild Index step as online rebuild operations can’t be performed on datatypes text, ntext, image, varchar(max), nvarchar(max), varbinary(max), xml, or large CLR type.To avoid this, we may perform offline index rebuild if business allows.
DBCC CHECKDB fails with error “database is in SUSPECT mode”
During database integrity
check, DBCC CHECKDB uses database snapshots as it’s mechanism for running
online in SQL Server 2005.
In SQL Server, DBCC commands use internal read-only database snapshots.
These database snapshots are created on the same drive where the corresponding
database data files are located. Database snapshots grow in proportion to the
amount of changed data in the database against which the DBCC commands run. If
transactional activity continues on this database, the database snapshots that
are created by DBCC commands may experience disk space issues. Because the
database snapshot files and the actual data files reside on the same disk
drive, both sets of files compete for disk space. In this case, application
transactions or user transactions are given preference. The internal database
snapshot used by DBCC is marked as suspect. Therefore, the DBCC commands
experience errors and cannot finish. Disk space is one of the reasons why
writes to the internal database snapshot may fail.SSIS job fails with Communication link failure
I searched on this error and found that this error mostly occurs when data is pulled from a different data source across the network.So when using a network packet size that is greater than 16388 (16K) in environments where encryption is enabled on SQL Server, an attempt to save SSIS packages to the MSDB package store fails with this error. To minimize or avoid this error, we may need to specify a network packet size that is smaller than 16384 (16K) bytes say 16383.
We can configure network packet size either using sp_configure or by going to SQL instance properties.
sp_recompile issue – The definition of object has changed since it was compiled
Last week, I came across an issue and thought I should share this on my blog. There were several jobs that execute specific stored procedures failing with error “The definition of object has changed since it was compiled”. Later on digging in I came to know that few indexes were created on a table and then dropped. And, this table was referenced in several stored procedures and thus the jobs executing these SPs were failing with the above error. I then recompiled all the stored procedures that were failing and the jobs ran fine in their next executions.Syntax to recompile an stored procedure…
sp_recompile ‘<Name of Stored Procedure>’
We get this error when
- we add any column to table/view or drop any column from table/view
- we add or drop an index/trigger/constraints.
- we drop an statistics that is being used in a query plan.
Using Dedicated Administrator Connection (DAC) in SQL Server 2008
Using DAC, we can connect to a SQL Server instance when the database engine is not responding to regular connection. In many scenarios, SQL Server restart is considered as a solution to fix issues may it be related to blocking, or some other issue which may lead to corruption of database(s). We can use DAC to connect to a SQL instance to fix these issues when database engine is not working properly. However, we can have only one Dedicated Administrator Connection at a time and can be connected using Database Engine Query. Lets go through the steps of enabling DAC and connecting to a SQL instance.Enabling DAC using T-SQL
use master;
sp_configure ‘remote admin connections’, 1
go
reconfigure
go
Enabling DAC using SQL Server 2008 Management Studio
- Right click on your SQL Instance.
- Go to Facets and click it.
- From Drop Down options against Facets select “Surface Area Configuration”.
- Select the option “True” for “RemoteDacEnabled”
- Click OK and DAC is now enabled.
You need to specify “ADMIN:” before SQL instance name. For example, if your instance name is VIVEKS\MSSQL2K8, then you need to specify ADMIN:VIVEKS\MSSQL2K8 in the server name option. You are now connected to SQL instance using DAC and can execute SQL queries.
SQL Server 2008 Service Pack 3
Download SQL Server 2008 SP3http://www.microsoft.com/download/en/details.aspx?id=27594
SQL Injection
What a virus or malware functions mean to an Operating System, SQL injection or insertion means to a database. A coding technique used in the strings of an application and exploits a security vulnerability occurring in the database.SQL Server Locks
There are three main types of locks that SQL Server uses:- Shared locks
- Update locks
- Exclusive locks
Update locks are used when SQL Server intends to modify a page, and later promotes the update page lock to an exclusive page lock before actually making the changes.
Exclusive locks are used for the data modification operations, such as UPDATE, INSERT, or DELETE.
Shared locks are compatible with other Shared locks or Update locks.
Update locks are compatible with Shared locks only.
Exclusive locks are not compatible with other lock types.
Categories: Administration
Top 10 Features for Database Administration in SQL Server 2005
Database Mirroring
Extend log shipping capabilities with the database mirroring solution. You
will be able to use database mirroring to enhance availability of your SQL
Server systems by setting up automatic failover to a standby server.Online Restore
With SQL Server 2005, database administrators are able to perform a restore operation while an instance of SQL Server is running. Online restore improves the availability of SQL Server because only the data being restored is unavailable; the rest of the database remains online and available.
Online Indexing Operations
The online index option allows concurrent modifications (updates, deletes, and inserts) to the underlying table or clustered index data and any associated indexes during index data definition language (DDL) execution. For example, while a clustered index is being rebuilt, you can continue to make updates to the underlying data and perform queries against the data.
Fast Recovery
A new faster recovery option improves availability of SQL Server databases. Administrators can reconnect to a recovering database after the transaction log has been rolled forward.
SQL Server Management Studio
SQL Server 2005 includes SQL Server Management Studio, a new integrated suite of management tools with the functionality to develop, deploy, and troubleshoot SQL Server databases, as well as enhancements to previous functionality.
Dedicated Administrator Connection
SQL Server 2005 provides a dedicated administrator connection that administrators can use to access a running server even if the server is locked or otherwise unavailable. This capability enables administrators to troubleshoot problems on a server by executing diagnostic functions or Transact-SQL statements.
Snapshot Isolation
Snapshot Isolation (SI) level is provided at the database level. With SI, users can access the last committed row using a transitionally consistent view of the database. This capability provides greater scalability.
Data Partitioning
Data partitioning is enhanced with native table and index partitioning that enables efficient manageability of large tables and indexes.
Replication Enhancements
For distributed databases, SQL Server 2005 provides comprehensive schema change (DDL) replication, next-generation monitoring capabilities, built in replication from Oracle to SQL Server, merge replication over https, and significant merge replication scalability and performance improvements. Additionally, the peer-to-peer transactional replication feature improves support for data scale out using replication.
Indexed view
In SQL Server 2000 and 2005, a view that has a unique clustered index is referred to as an indexed view.
From the Database Management System (DBMS) perspective, a view is a description of the data (a form of metadata). When a typical view is created, the metadata is defined by encapsulating a SELECT statement that defines a result set to be represented as a virtual table. When a view is referenced in the FROM clause of another query, this metadata is retrieved from the system catalog and expanded in place of the view’s reference. After view expansion, the SQL Server query optimizer compiles a single execution plan for the executing query. The query optimizer searches though a set of possible execution plans for a query, and chooses the lowest-cost plan it can find, based on estimates of the actual time it will take to execute each query plan.
In the case of a non-indexed view, the portions of the view necessary to solve the query are materialized at run time. Any computations such as joins or aggregations are done during query execution for each query referencing the view1. After a unique clustered index is created on the view, the view’s result set is materialized immediately and persisted in physical storage in the database, saving the overhead of performing this costly operation at execution time.
The indexed view can be used in a query execution in two ways. The query can reference the indexed view directly, or, more importantly, the query optimizer can select the view if it determines that the view can be substituted for some or all of the query in the lowest-cost query plan. In the second case, the indexed view is used instead of the underlying tables and their ordinary indexes. The view does not need to be referenced in the query for the query optimizer to use it during query execution. This allows existing applications to benefit from the newly created indexed views without changing those applications.
Views have been available throughout the history of Microsoft SQL Server. However, using views that return very large result sets can lead to poor performance, as the result set is not indexed and the entire result must be table scanned if the view is used in a join or a sub query of a T-SQL command. Additionally, products like Oracle have come out with the concept of a Materialized View that give an additional performance boost by being able to have indexes built on a view. So in the continuing evolution of the SQL Server product line and in response to Oracle’s Materialized View, Microsoft SQL Server 2000 has a new feature called the View Index. View Indexes give the product the capability to define an index on a view. Additionally, SQL Server View Indexes are dynamic in that changes to the data in the base tables are automatically reflected in the indexed view. Also the SQL Server query optimizer will try to use an indexed view even if the view is not referenced in the from clause of a T-SQL command. These features are not available in Oracle’s Materialized Views.
Isolation Levels
READ COMMITTED
Specifies that shared locks are held while the data is being read to avoid dirty reads, but the data can be changed before the end of the transaction, resulting in nonrepeatable reads or phantom data. This option is the SQL Server default.
READ UNCOMMITTED
Implements dirty read, or isolation level 0 locking, which means that no shared locks are issued and no exclusive locks are honored. When this option is set, it is possible to read uncommitted or dirty data; values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction. This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. This is the least restrictive of the four isolation levels.
REPEATABLE READ
Locks are placed on all data that is used in a query, preventing other users from updating the data, but new phantom rows can be inserted into the data set by another user and are included in later reads in the current transaction. Because concurrency is lower than the default isolation level, use this option only when necessary.
SERIALIZABLE
Places a range lock on the data set, preventing other users from updating or inserting rows into the data set until the transaction is complete. This is the most restrictive of the four isolation levels. Because concurrency is lower, use this option only when necessary. This option has the same effect as setting HOLDLOCK on all tables in all SELECT statements in a transaction.
SNAPSHOT ISOLATION
Snapshot Isolation (SI) level is provided at the database level. With SI, users can access the last committed row using a transitionally consistent view of the database. This capability provides greater scalability.
Dirty Reads occur when one
transaction reads data written by another, uncommitted, transaction. The danger
with dirty reads is that the other transaction might never commit, leaving the
original transaction with “dirty” data.
Non-repeatable Reads occur when one
transaction attempts to access the same data twice and a second transaction
modifies the data between the first transaction’s read attempts. This may cause
the first transaction to read two different values for the same data, causing
the original read to be non-repeatable.
Phantom Reads occur when one
transaction accesses a range of data more than once and a second transaction
inserts or deletes rows that fall within that range between the first
transaction’s read attempts. This can cause “phantom” rows to appear or
disappear from the first transaction’s perspective.
Note: The setting of SET TRANSACTION ISOLATION LEVEL is set at
execute or run time and not at parse time.
Find
number of User Connections in a SQL database
SELECT DB_NAME(dbid) as DBName,
COUNT(dbid) as NumberOfConnections,
loginame as LoginName
FROM
sys.sysprocesses
WHERE
dbid > 0
GROUP BY
dbid, loginame
order by NumberOfConnections desc
–You can get connection details from below query also. You can change the values of status and open_tran columns as per your requirement.
Select status, hostname, program_name, cmd, loginame, DB_NAME(DBID) from sys.sysprocesses
where DB_NAME(DBID) = ‘DB_NAME’ and status = ‘sleeping’
and open_tran = 0
No comments:
Post a Comment