Msg 22004, Level 16, State 1, Line 0

Today I was working on Producing the Deadlock in My local Machine, hence I have enabled the trace flag globally ,I did the work and made the Deadlock situation,so I Planned to read SQL error log so I hit the command in an new query window(even I was not able to view in SSMS as well) ->Got the below error message:

Msg 22004, Level 16, State 1, Line 0
Failed to open loopback connection. Please see event log for more information.
Msg 22004, Level 16, State 1, Line 0
error log location not found

so to trouble shoot this one I have checked the below things(Note that the issue depends on the various reason & I have showed what caused in my case).
==================================================================================================
1.Verified the SQL error log file exists in the proper path(Of course if the SQL error log path is in correct the SQL server Won't start up).

                             

2.Verified at the regsitry level of SQL log Path =>There is no issues at all.

3.verified at the Folder level permissions and Local administrators level =>No issues at all.

so as in the error it shows clearly that "Msg 22004, Level 16, State 1, Line 0,Failed to open loopback connection. Please see event log for more information.
Msg 22004, Level 16, State 1, Line 0
error log location not found"

So I moved to Event logs --Found some of the valuable informations as mentioned below-

Event Type:    Error
Event Source:    MSSQLSERVER
Event Category:    (2)
Event ID:    17052
Date:        02/01/2012
Time:        08:56:22
User:        N/A
Computer:    RAKUBERA
Description:
Severity: 16 Error:-1, OS: -1 [Microsoft][SQL Server Native Client 10.0]SQL Server Network Interfaces: Error getting enabled protocols list from registry [xFFFFFFFF].

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
=====================================================================
Event Type:    Error
Event Source:    MSSQLSERVER
Event Category:    (2)
Event ID:    17052
Date:        02/01/2012
Time:        08:57:56
User:        N/A
Computer:    RAKUBERA
Description:
Severity: 16 Error:-1, OS: -1 [Microsoft][SQL Server Native Client 10.0]SQL Server Network Interfaces: Error getting enabled protocols list from registry [xFFFFFFFF].

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
==================================================================
Event Type:    Error
Event Source:    MSSQLSERVER
Event Category:    (2)
Event ID:    17052
Date:        02/01/2012
Time:        09:01:10
User:        N/A
Computer:    RAKUBERA
Description:
Severity: 16 Error:-1, OS: -1 [Microsoft][SQL Server Native Client 10.0]SQL Server Network Interfaces: Error getting enabled protocols list from registry [xFFFFFFFF].

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
==========================================================
Event Type:    Error
Event Source:    MSSQLSERVER
Event Category:    (2)
Event ID:    17052
Date:        02/01/2012
Time:        09:01:40
User:        N/A
Computer:    RAKUBERA
Description:
Severity: 16 Error:-1, OS: -1 [Microsoft][SQL Server Native Client 10.0]SQL Server Network Interfaces: Error getting enabled protocols list from registry [xFFFFFFFF].

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
========================================================================
In the SQL agent log I can see below messages:

2012-01-02 02:10:26 - ! [298] SQLServer Error: 65535, SQL Server Network Interfaces: Error getting enabled protocols list from registry [xFFFFFFFF]. [SQLSTATE 08001]
2012-01-02 02:10:26 - ! [165] ODBC Error: 0, Login timeout expired [SQLSTATE HYT00]
2012-01-02 02:10:26 - ! [298] SQLServer Error: 65535, A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. [SQLSTATE 08001]
2012-01-02 02:10:26 - ! [000] Unable to connect to server 'RAKUBERA\SQL2K8'; SQLServerAgent cannot start


How ever there is no relations b/w the Agent and SQL Errorlog, How ever it provides valuable informations..

In My case is that ->I have SQL database engine local machine only (Obviously both Client & server is in same machine), I start checking the SQL Native clients via configuration Manger -->all the Protocols were disabled state , So I have checked the protocols of My SQL server 2008 what is the order of protocols :It was first shared memory,Named pipes then Tcp IP and VIA was in disbaled state...

so to resolve I went & enabled the protocols of Shared Memory then I executed the =>Exec xp_readerrorlog then Iam able to view the data in query window, also I was able to see in SSMS.

Bottom Lines
========
1.Ensure that before performing understand the Logs properly, before peforming on Production check it in your test box.

2.Also the above issues arises in difference cases ==>The similar type of issue recevide in different situation --->see some of the below Links-What other person faced the similar issue and how he resolved(Thanks to them).

http://sqlserverpedia.com/blog/sql-server-bloggers/error-log-location-not-found/
http://www.pythian.com/news/27905/sql-serveropening-errorlogfailed-to-retrieve-data-for-this-request-microsoft-sqlserver-management-sdk-sfc/
http://www.sqlservercentral.com/Forums/Topic767980-146-1.aspx
http://ms-abhay.blogspot.com/2010/09/msg-22004-level-16-state-1-line-0.html

Trace Flags.

About  Trace flags
============
Trace flags are used for temporarily or Permanently(when you specified in startup parameters) i,e  It depends on individual decisions for their applications(Note that you need to understand Properly -why you are enabling it & what will be the Impact on that if it is enabled.) to work on the troubleshotting purposes or to track the activities either Globally or session .

Types of Trace Flags?
===============

There are 2 Types of Trace flag -> 1,session Trace flag  2. global Trace flag.

Session Trace flag :It is only for the active connection and that will be visible only for that connection & effect of trace flag will losts once the SPID connections logs out from it's opened session.
                              (note that It won't affect for other sessions)

Global Trace flag : This will be set at the SQL server instance Level and it will be for all the connections


How to Enable/Disable or ON/OFF trace flags?
============================

To Turn on you have to use
DBCC TRACEON

To Turn off you have to use
 DBCC TRACEOFF


How to set on/off the trace flags globally?
===========================
You can use "-1" with your DBCC TRACEON  & DBCC TRACEOFF.
For eg:

DBCC TRACEON (4616, -1)
GO

Suppose if you want to enable Multiple trace flags just you can put Comma between trace flags ex :DBCC TRACEON (4616,7806,2528 -1)

To turn off replace ON with OFF.

How to check what traceflags are active/ON?

You can use DBCC TRACESTATUS  query to determine which trace flags are currently active whether it is Global/session.

see the below

How to displays the status of all trace flags that are currently enabled globally?
=================================================
You can use :
DBCC TRACESTATUS(-1)
GO

Bottom Lines
==========
1.As per BOL
---------
Some flags can only be enabled as global, and some can be enabled at either global or session scope.

The following rules apply:

    A global trace flag must be enabled globally. Otherwise, the trace flag has no effect. We recommend that you enable global trace flags at startup, by using the -T command line option.
    If a trace flag has either global or session scope, it can be enabled with the appropriate scope. A trace flag that is enabled at the session level never affects another session, and the effect of the trace flag is lost when the SPID that opened the session logs out.

Trace flags are set on or off by using either of the following methods:

    Using the DBCC TRACEON and DBCC TRACEOFF commands.
    For example, DBCC TRACEON 2528: To enable the trace flag globally, use DBCC TRACEON with the -1 argument: DBCC TRACEON 2528, -1. To turn off a global trace flag, use DBCC TRACEOFF with the -1 argument.
    Using the -T startup option to specify that the trace flag be set on during startup.
    The -T startup option enables a trace flag globally. You cannot enable a session-level trace flag by using a startup option.


2.Once the trace flag is turned on, it remains On until either you manually turned off or if it is SQL Server restarted.

INDEXES in ALL angles -1

I want to share something today, one of my friends asked me the question about Index-the one which he asked me is that what happens if I disabled the clustered Indexes on table or view?

Start from beginning I was try to understand about the indexes --It was difficult to understand(I believe for the experience people as well).

But Iam much ok & got an good Idea on the Indexes.

So Iam starting this  topic INDEXES in ALL angles -1 as an FAQ's for those who is preparing for the Interview (I will answers all questions shortly-sure If I get time).

In then Next year i,e next Month of Jan,Iam  taking session  on In and Out of Indexes in the office.(may be January 4th week).

Ok Lets start the Journey on Indexes.

1.What is Index and why it is required in RDBMS, How query optimizer will take advantage of good index choose by smart Developer or Vendor?Ofcourse DBA!

2.How Many types of indexes in SQL server 2000/2005/2008/R2/Denali/2012 & what are those?

3.What is B-tree structure and how you can identify how many b-structures you have & what are the levels it has(levels are like -Root node,Intermediate and Leaf level nodes)?

4.What are the major's differences B/w clustered and Non-clustered indexes?

5.What happens if I disable the clustered index on an table & what about the Non-clustered indexes?

6.Is there any way can I enable the clustered indexes, if yes how it is?


6.How query optimizer decides whether it has to go with Index scan or entire table scans?


7.Is that good option keeping too many indexes , because you know that the index is an good option for retrieving the data efficiently for the users as well as for the SQL server database engine (bcz Good index can
reduces the larger Physical I/O)?

8.Can always root node exists?


9.Will always Index get benefits for the users & database engine?

10.what is the meaning of Index page & data pages?

11.What is Keys? & How keys behaves in RDBMS?

12.Can I create an non-clustered index if the clustered index disabled?

13.What happens to the Non-clustered index,if I drop the clustered index?

14.Can I rename the Index, if so how & what is the causes if I rename the index?

15.How to: Move an Existing Index to a Different Filegroup?

16.How data will retrieve if no indexes exists in sql server?

17.why it is Index Sort Order?

Fragmentation's:
============
1.What is fragmentation in SQL server?

2.How many types of fragmentation's and why the fragmentation occurs?

3.What is the disadvantages of Fragmentation in SQL server?

4.You may have seen that after rebuilding the indexes still shows the fragmentation is same or High percentage, why is it so?

5.what is fill factor and how I can decide the proper fill factor?

6.When to Rebuild/Re-organize?

7.Can heavy Fragmentation dist rubes the business like timeout errors, takes more time to get the reports during urgent time?

8.How can I rebuild the indexes and how many modes we can rebuild the indexes.

9.What is the difference between Onilne rebuild /Off line rebuild index?

10.How internally works the rebuild index(you know that the rebuild indexes flows in three ways)?


11.What factors I need to consider before rebuilding the Indexes like as mentioned below -

A)How can we estimate the log file size before rebuilding the index?

B)what about the Filegroup size?

C)Proper tempdb space (if you have used the option sort_tempdb =on)?

D)how to decide the you need to rebuild online or offline?

E)Best practices?

12.what are all the Index Options available & how we will get benifits?

13.Why fill-factor , how page splitting occurs & how we can reduce?

14.What types of locks puts during the rebuild indexes(i,e here consider the 3phases)?

15.How to rebuild the indexes on single index or on entire tables?

16. My OS disk got fragmented then what is the Impact on the Indexes?

17.Whar Recovery Model I need to select for  Index Operations and how it behaves?

----I will post some more FAQ's-------------

Bottom Line:
========
1.In SQL server 2008 what ever may be the recovery model the Index operation is full logged.

2.A)Online index operations are available only in SQL Server 2005 Enterprise Edition.
   B)Online index operations are available only in SQL Server Enterprise, Developer, and Evaluation editions --->Denali and SQL server 2008/R2
3..Index-Related Dynamic Management Views and Functions --in SQL server 2005/2008/R2 and Denali
sys.dm_db_index_operational_stats
sys.dm_db_index_physical_stats
sys.dm_db_index_usage_stats
sys.dm_db_missing_index_columns
sys.dm_db_missing_index_details
sys.dm_db_missing_index_groups
sys.dm_db_missing_index_group_stats

4.In SQL server 2005 ->Number of clustered index is 1 and remaining 2 to 249 Non-clustered indexes  ==>Similar in 32 bit & 64 Bit .

In SQL server 2008/R2/Denali ->Number of clustered index is 1 and remaining 2 to 999 Non-clustered indexes  ==>Similar in 32 bit & 64 Bit .

All the Best Guys!!!!!

Concurrent Administrative Operations..

I want to share about the Concurrent operations at database level i,e in an mind may arise questions like-
A)Can we do Backup and shrink at the same time?
B)Can we add additional Database files when Backup is running?
C)Can we do Full backup database along with the differential Backup?

As we know that during any Backup operation(either full,log or differential) it's not possible to do the add/remove additonal files, shrink, file modify(alter).

You can easily do in your test machine, how ever for Pictorical if you need then Please refer the below links

Link :http://technet.microsoft.com/en-us/library/ms189315%28SQL.90%29.aspx