MSDN Blog Link!

I want each one of you ,go through the below link ->where the MS Engineers/Solid professional blogs over there-

http://blogs.msdn.com/b/sqlserverfaq/

http://blogs.msdn.com/b/sqlserverstorageengine/

tempdb database is marked suspect,,

How to recover SQL Server after a tempdb database is marked suspect?

You can refer the below link to resolve the issue-
http://support.microsoft.com/kb/288809

Msg 3257, Level 16, State 1, Line 2-There is insufficient free space on disk volume 'Drive:\' to create the database

Today there was an activity to perform restore on the PROD server using the TEST database backup file,the

test Backup file is around 23GB, so moved to Production server and started executing the restore command  but the below error has thrown-
Msg 3257, Level 16, State 1, Line 2
There is insufficient free space on disk volume 'R:\' to create the database. The database requires 293066571776 additional free bytes, while only 75202101248 bytes are available.
Msg 3119, Level 16, State 4, Line 2
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.



So used the below query to see what are the files that exists in the Backup file i,e size,Maxsize, Filegroup,Logical file name and Physical filename.

Restore filelistonly from disk='<Drive>:\XXXXXX.BAK'

so we observed that the Initial size of the MDF file is near to 176GB and remaining other files(1 ndf and 2 ldf files) occupies is 96.93GB ->Over all 272.3G.

But when we have checked - what is the usuage of the database ?

--the database has 78% free space in it & used space is 22%.

how ever our restore strategy  pointing to the drive where it was less space i,e R:Drive has 70.73GB free space out of 120GB, hence the  error throwns that-

"There is insufficient free space on disk volume 'R:\' to create the database. The database requires 293066571776 additional free bytes, while only 75202101248 bytes are available.
Msg 3119, Level 16, State 4, Line 2"


I,e A)the database requires 293066571776 additional free bytes --->Here the whole Database size 272.93GB i,e the Physical files to get initialize on the Physcial disk eventhough the usuage percentage of the database was less.
    B)while only 75202101248 bytes are available ---------->Here clearly expalins that in the  drive which you are restoring is have only 70.73GB hence database not able to Initialize .

so the next error will be as mentioned below -
Msg 3119, Level 16, State 4, Line 2
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

To resolve
==========
1.Before taking the Backup of the database --Verify what is the Initial file size and check what is the overall usuage of the indivual file size i,e how much used space & free space.

2.Once identifed then go for altering the initial file sizes using the Alter command or you can go for shrinking then take the backup and restore it on where  ever you want.

3.or If any other drive has enough space to hold then change the restore command i,e where you wan to be Physical files to be

or if you do not have the space then check Muliple drive free space then you can add accordingly.



Bottom Lines
=============
Want to know more about what is the Database file initialization then refer the below links -

http://msdn.microsoft.com/en-us/library/ms175935.aspx
http://sqlskills.com/BLOGS/PAUL/post/Misconceptions-around-instant-file-initialization.aspx
http://sqlserverperformance.wordpress.com/2011/02/10/windows-instant-file-initialization-and-sql-server/
http://sqlblog.com/blogs/tibor_karaszi/archive/2009/03/09/do-you-have-instant-file-initialization.aspx
http://www.databasejournal.com/features/mssql/article.php/3899156/Instant-File-Initialization-for-SQL-Server-20052008.htm


Thanks,
Rama Udaya.K

Higher to Lower version restoration!

Can we restore the database of Higher to Lower version restoration?
OK let's Plan like this -Backup the database of SQL server 2008(before backing up change the Database compatible level to 90) then restore the database on SQL server 2005.

Backup completed of SQL server 2008 database after changing 100 to 90.

Now I will go for the restoring the database on SQL server 2005 -let's see what it throws an error even though changed the compatibility of the database-

 

 So I plan to see what the Header of the Backup file to get some information about Backup file, how ever it is failed to reterive data in SQL server 2005 & it throws sam error-

 So one way we can do, not restore method but we can use script out method so,Let's see how it is.
*Log in to the SQL server 2008 ->select the database( which you want place in sql server 2005) ->Right click Tasks ->Generate the scripts and change the script for server version to your comfortable version.

Please see the below figures:-

                                          

Now I will use the above scripts and will execute in the new query window of SQL server 2005 -

(see the figure below)  before that I have created an dummy database called :DBMAINT_TEST

after executing the script you will get the outpuy as Command's completed successfully........(please see fig)

Finally it's done

Bottom Lines

---------------

1.You cannot restore the database from Higher to Lower version.

2You can also use -Import and export wizard instead of Generate SQL Server Scripts Wizard. This solution exports data to the targeted SQL Server using a wizard. It is more secure and effective than the first solution. But you can only export tables and views only.

3.Note ->When you restore or attach a database which is created from SQL Server 2008 to SQL Server 2005 or SQL Server 2000, you will get an error so other way you can use is the Bottomline 2 options.

Thank you!!!

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.