Blocking and detection

What is blocking and how would you troubleshoot it?

Blocking occurs when a process has acquired lock on a set of rows, and another process is trying to acquire a lock on the same set of rows. In such a case, the other process has to wait until the first process finishes its job and releases the lock on the above said rows.

• Use sp_lock procedure to see type of locks acquired by various sessions on the server to find the cause of blocking.
• Problem is hinted by the WAIT status is a lot of rows that are returned as an output of sp_lock stored procedure execution.
• Use sp_who and sp_who2 to return more columns to get more information around the blocking.
• Use DBCC INPUTBUFFER (spid).This will show the last 128 characters of the last T-SQL statement executed from connection referred through spid. This way one can identify the stored procedure or application module that caused blocking.
• To resolve blocking, you can disconnect the connection causing the blocking using KILL command. If this does not solve the problem permanently, then rewrite the stored procedure/module causing the block more efficiently.

sp_lock

sp_who2

select * from master..sysprocesses

(

select spid,cmd,program_name,blocked,waittime,waitresource,lastwaittype from master..sysprocesses WHERE spid in (66,64) and dbid=7

)

to view the last issued statement, select * from ::fn_get_sql(sql handle returned from sysprocesses)

SQL Server Services

From online book.
http://msdn.microsoft.com/en-us/library/ms143504%28v=sql.90%29.aspx

• SQL Server Database Services - The service for the SQL Server relational Database Engine.
  
• SQL Server Agent - Executes jobs, monitors SQL Server, fires alerts, and allows automation of some administrative tasks.
  

  Note:

  For SQL Server and SQL Server Agent to run as services in Windows, SQL Server and SQL Server Agent must be assigned a Windows user account. Typically, both SQL Server and SQL Server Agent are assigned the same user account—either the local system or a domain user account. However, you can customize the settings for each service during the installation process. For more information about how to customize account information for each service, see Service Account.

• Analysis Services - Provides online analytical processing (OLAP) and data mining functionality for business intelligence applications.
  
• Reporting Services - Manages, executes, renders, schedules, and delivers reports.
  
• Notification Services - A platform for developing and deploying applications that generate and send notifications.
  
• Integration Services - Provides management support for Integration Services package storage and execution.
  
• Full-Text Search - Quickly creates full-text indexes on content and properties of structured and semi-structured data to allow fast linguistic searches on this data.
  
• SQL Server Browser - The name resolution service that provides SQL Server connection information for client computers.
  
• SQL Server Active Directory Helper - Publishes and manages SQL Server services in Active Directory.
  
• SQL Writer - Allows backup and restore applications to operate in the Volume Shadow Copy Service (VSS) framework.

SQL server XML operation sample

with lan as
(SELECT progmsgid,msgTime,
MsgText.value('(/s2sMessage/s2sHeader/@messageId)[1]', 'varchar(20)') AS messageId
FROM progressivemsglan),
wan as
(
SELECT progmsgid,msgTime,
MsgText.value('(/s2sMessage/s2sHeader/@messageId)[1]', 'varchar(20)') AS messageId
FROM progressivemsgwan
)
SELECT lan.progmsgid,lan.messageid,lan.msgtime,wan.messageid,wan.msgtime
FROM lan left join wan on lan.messageid=wan.messageid
--where wan.messageid is null
order by lan.progmsgid desc

SQL Server, Database mirroring

--principle and mirror role(hot standby server).
--All database mirroring sessions support only one principal server and one mirror server
--High-safety mode with automatic failover requires a third server instance, known as a witness

role switching
roling upgrade

Unlike replication, which works at the logical level, database mirroring works at the level of the physical log record. Beginning in SQL Server 2008, the principal server compresses the stream of transaction log records before sending it to the mirror server. This log compression occurs in all mirroring sessions

operation mode:
high-safety==synchronous ,
high-performance== asynchronous

openrowset,

openrowset
Includes all connection information necessary to access remote data from an OLE DB data source. This method is an alternative to accessing tables in a linked server and is a one-time, ad hoc method of connecting and accessing remote data using OLE DB. The OPENROWSET function can be referenced in the FROM clause of a query as though it is a table name. The OPENROWSET function can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement, subject to the capabilities of the OLE DB provider. Although the query may return multiple result sets, OPENROWSET returns only the first one.

opendatasource

The OPENDATASOURCE function can be used in the same Transact-SQL syntax locations as a linked server name. Thus, OPENDATASOURCE can be used as the first part of a four-part name that refers to a table or view name in a SELECT, INSERT, UPDATE, or DELETE statement, or to a remote stored procedure in an EXECUTE statement. When executing remote stored procedures, OPENDATASOURCE should refer to another SQL Server. OPENDATASOURCE does not accept variables for its arguments.

Like the OPENROWSET function, OPENDATASOURCE should only reference OLE DB data sources accessed infrequently. Define a linked server for any data sources accessed more than a few times. Neither OPENDATASOURCE, nor OPENROWSET provide all the functionality of linked server definitions, such as security management and the ability to query catalog information. All connection information, including passwords, must be provided each time OPENDATASOURCE is called.

SELECT   * FROM      OPENDATASOURCE(          'SQLOLEDB',          'Data Source=ServerName;User ID=MyUID;Password=MyPass'          ).Northwind.dbo.Categories

openquery
Executes the specified pass-through query on the given linked server, which is an OLE DB data source. The OPENQUERY function can be referenced in the FROM clause of a query as though it is a table name. The OPENQUERY function can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement, subject to the capabilities of the OLE DB provider. Although the query may return multiple result sets, OPENQUERY returns only the first one.

EXEC sp_addlinkedserver 'OracleSvr',     'Oracle 7.3',     'MSDAORA',     'ORCLDB' GO SELECT * FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles')  GO

setup SQL server replication using backup files

from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=127368
/* Steps to set up replication using backup files instead of snapshots  1. Run the Create Publication wizard on the publisher. Make sure NOT to create a snapshot.  2. Once the wizard has finished, right-click the new publication, choose Properties    and make sure that the "Allow initialization from backup files" is set to True  3. Disable the "Distribution clean up: distribution" job to make sure that no     commands are deleted from MSrepl_commands before the entire backup/restore operation     is complete  4. Create a full database backup to disk and make sure that the file is available for the     publisher until the entire replication setup is finished.   5. Transfer the backup file to the subscriber server and restore it there. If you do     regular backups to disk make sure to use one that was taken *after* the publication     was created. Also restore trans log backups taken after the full backup. The last backup    should be restored using WITH RECOVERY, and keep in mind that a more recent backup saves     you time in the syncing process when the replication is being initialized.  6. Disable all triggers on the subscriber database:    EXEC sp_msforeachtable 'ALTER TABLE ? DISABLE TRIGGER all'  7. Disable all constraints on subscriber database:    EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'   8. Run the following script ON THE PUBLISHING SERVER IN THE PUBLISHING DATABASE to enable     the replication:    EXEC sp_addsubscription      @publication ='myPublication',              --> Name of the publication     @subscriber = 'myserver.myDomain.com',      --> Fully qualified name or IP of subscriber server     @destination_db ='MySubscriberDB',          --> Name of the database you just restored (doesn't have to be the same as the publisher)     @sync_type = 'initialize with backup',      --> no need to change this     @backupdevicetype = 'disk',                 --> no need to change this     @backupdevicename = 'F:\backupfile.bak'     --> Pointer to the last backupfile that was restored, but from the folder on the on the                                                       publishing server. If you restored trans logs also the last translog file is what you                                                      need to put here  9. Enable the "Distribution clean up: distribution" job again  */

openrowset

sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

SELECT a.*
FROM OPENROWSET('SQLNCLI', 'ip';'sa';'password',
'SELECT *
FROM tbl'
) AS a;