Wednesday, 23 February 2011

what is Log Shipping in SQL Server

What is Log Shipping

Essentially, log shipping is the process of automating the backup of database and transaction log files on a production SQL server, and then restoring them onto a standby server. But this is not all. The key feature of log shipping is that is will automatically backup transaction logs throughout the day (for whatever interval you specify) and automatically restore them on the standby server. This in effect keeps the two SQL Servers in "synch". Should the production server fail, all you have to do is point the users to the new server, and you are all set. Well, its not really that easy, but it comes close if you put enough effort into your log shipping setup.

Read more detail from :
http://www.sql-server-performance.com/articles/clustering/log_shipping_70_p1.aspx

Benefits of Log Shipping

While I have already talked about some of the benefits of log shipping, let's take a more comprehensive look:
  • Log shipping doesn't require expensive hardware or software. While it is great if your standby server is similar in capacity to your production server, it is not a requirement. In addition, you can use the standby server for other tasks, helping to justify the cost of the standby server. Just keep in mind that if you do need to fail over, that this server will have to handle not one, but two loads. I like to make my standby server a development server. This way, I keep my developers off the production server, but don't put too much work load on the standby server.
  • Once log shipping has been implemented, it is relatively easy to maintain.
  • Assuming you have implemented log shipping correctly, it is very reliable.
  • The manual failover process is generally very short, typically 15 minutes or less.
  • Depending on how you have designed your log shipping process, very little, if any, data is lost should you have to failover. The amount of data loss, if any, is also dependent on why your production server failed.
  • Implementing log shipping is not technically difficult. Almost any DBA with several months or more of SQL Server 7 experience can successfully implement it.

Monday, 14 February 2011

SQL Query to find Who is Blocking database

Simple Script to get Blocking Info in SQL Server Database:


select * from
(select spID, blocked, program_name, loginame from sys.sysprocesses (nolock) where blocked <> 0) locks,
(select spID, blocked, program_name, loginame from sys.sysprocesses (nolock)) allprocess
where locks.blocked = allprocess.spId

How to Find blocking Information in SQL Server

Objective: Below script is to find the blocking information in SQL Server


Declare @wTable Table (spid int, blocked int, loginame varchar(100), dbName varchar(100), last_batch dateTime)

Insert into @wTable
select spid, blocked, loginame, db_name(dbid), last_batch from sys.sysprocesses (nolock)--where blocked <> 0
select spid,blocked,loginame,dbName,last_batch from @wtable where blocked <> 0
Union all
select spid,blocked,loginame,dbName,last_batch from @wtable where spId in(select blocked from @wtable where blocked <> 0)
order by blocked

Monday, 7 February 2011

SQL query to Create Index

Use the following query to create index on a table in SQL Server.

CREATE INDEX IX_Log_timestamp
ON log(timestamp)