April 10, 2014

SQL Error on running of Enterprise portal ,sharepoint site

I am getting following SQL Error on running of Enterprise portal ,sharepoint site Could you advice solution on this error.

Unknown SQL Exception 10054 occured. Additional error information from SQL Server is included below.

A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

Possible solution:

An error has occurred while establishing a connection to the server. When connecting to SQL Serve and this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - An operation on a socket could not be performed because the system lacked sufficient buffer space or because a queue was full.)

April 7, 2014

Reason for application is running slow from a DBA side in SQL

There might be some reasons behind it:
1. Blocking situation
2. lack of userfull indexes
3. Lack of updated statistics
4. deadlock situation
5. network issue
6.Check the status of query.It can be either of two reasons.Either external or internal factor.External factors are resource crunches .Internal factors are because of missing index, invalid cache plan, fragmentation,so on.Starting point to identify higher level and then do a deep dive into particular area.

Error no:1418 on Mirroring in SQL

The server network address "xxxxx" can not be reached or does not exist. Check the network address name and reissue the command.

Pls check endpoints on all servers using
SELECT * FROM sys.endpoints

If 1418 error: check it these, duplicate end points exists, tcp/ip enabled or not, check editions principal and mirror, windows firewall off, check login not created in mirror instance.

How to delete duplicate end points if exists ?

IF EXISTS (SELECT * FROM sys.endpoints e WHERE e.name = N'EndPoint_name')
DROP ENDPOINT [EndPoint_name]

Why we go for Replication in SQL

Log shipping, mirroring & replication are various types of HA (high availability) features. Each of them have their own set of pros and cons and various sets of features. Hence there is no compulsion and requirement to implement all of them. Rather the decision to choose them is driven by business requirement and at times the resources available for use.

Db mirroring and log shipping are database level HA the replication is object level HA.

If you have SSRS, then go for replication, never keep OLTP database to fetch SSRS reports.

Replication is never a DR lets take an example that for your reporting environment you habe used transactional replication. On the reporting database server for best performance you build plenty of indexes which are not required on the OLTP environment therefore I believe clustering mirroring and log shipping are the best option always have a powerful backup strategy that will always help you.

Move Reporting server database instance from one server to another in SQL Server

Following the steps below to Move Reporting server database instance from one server to another in SQL Server 2005,2008,2012 etc

1. Take the backup report server and report server temp db and restore in another server. ( if you want to move db also)
2. In the new server open reporting service configuration manage and re configure with new server details and start again.
3. Remove the encryption keys .
Then reports will work from new server.

Committed and uncommitted data in Log Backup in SQL

Transaction log backup captures the transaction log which contains a record of all committed or uncommitted transactions.

In full recovery model, some time log file getting full that and first we will check log bak taken r not the we ill take log backup then it will clear all committed transaction from log file this will happen if have doubt create on database do some bulk(select into or insert into) trans and check log file size and take t log back then compare sizes.

Transaction log is a physical file in which SQL server stores the details of all transactions and data modifications performed on the database.In the event of of disaster, that causes SQL server to shutdown unexpectedly (Power failure/hardware failure), the transaction log is used to bring the database in a consistent state while restarting the server.On restarting the server, database goes through the recovery process.During this recovery process , the transaction log is used to make sure that all committed transactions are written to respective data pages (rolled forward) and revert the uncommitted transaction that were written to data pages.

Logically transaction log is a set of log records.Each records is identified by a log sequence number (LSN). The new log records is always written at the logical end of log file with a LSN which is greater than the previous one.Each LSN will be associated with a transaction id , which can be used to group the log records of a transaction. As log file store the log records in the sequential order as it happens, It is not necessary that, log records of a transaction are always available in sequence in the log file.Each log records will have the LSN of previous log as a backward pointer and that will help in rollback of transaction.

Transaction log will store separate log entries for each operation.For example, while inserting a record into a table, transaction log will store separate log entry for inserting into clustered index and other non clustered index. In the same way, if a single update statement is updating 10 records, transaction log will capture 10 separate log entries.For data modification, transaction log store either the logical operation performed or the before and after image of the record.

What is the query/SP used to get the LSN of a transaction?

Dbcc loginfo

dbcc log(your db ) or
select * from fn_dblog(null,null)

What is nolock in SQL Server

Nolock is nothing but it decrease the concurrency of busy system.

When there is nolock for data then that data can be taken by another transaction.

Reads the dirty page irrespective to the isolation level. (Not in all scenario).

NOLOCK is used in SELECT queries as query hint, it functions the same way as READ UNCOMITTED isolation level where dirty reads are possible. It is highly recommended for all applications where Performance and concurrency is vital and dirty reads takes a backseat. Like in non-banking and web based applications it is much used, and also in internal web applications used in back-office by multiple users. We can use NOLOCK in all non-financial and non-payment related transactions most of the times. Every domain and case needs to be verified.

What is the use of sqlserver browser service?

Sql browser service is used to connect to a instance within the server.

It runs at the OS LEVEL of architecture.

Sql server browser service is used by sql server for named instance name resolution and for server name listing. i.e., for default instance default port will be 1433 and for named instance port will be dynamically allocated to provide the named instance port details browser service is used.