Home » Posts tagged 'SQL' (Page 2)

Tag Archives: SQL

SQL Server 2000 Extended Support ending soon………21 days left

· On April 9, 2013, Extended Support for SQL Server 2000 will come to an end, and SQL Server 2000 will no longer be supported.

· What will change :

· Self-Help Online Support will be available for a minimum of 12 months. Example: Microsoft online Knowledge Base articles, FAQs, troubleshooting tools, and other resources, that help customers resolve common issues.

· Updates to this software will stop and customers will no longer receive patches, including security updates.

What Are available Options :

· Upgrade to a supported version of SQL Server.

· Find out more about a Custom Support Agreement (CSA).

· Run SQL Server 2000 unsupported with access to Self-Help Online Support only (not recommended).

· Upgrade to SQL Server 2005 is not recommended as SQL Server 2005 is also transitioned from Mainstream Support to Extended Support.

Refer Link : http://www.microsoft.com/en-us/sqlserver/support/support-updates.aspx

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

DMV-2 : Explore the secrets of session…… sys.dm_exec_sessions

sys.dm_exec_sessions DMV (Dynamic Management View), delineated by BOL as follows: http://msdn.microsoft.com/en-us/library/ms176013.aspx

Returns one row per authenticated session on SQL Server. sys.dm_exec_sessions is a server-scope view that shows information about all active user connections and internal tasks. This information includes client version, client program name, client login time, login user, current session setting, and more.

sys.dm_exec_sessions is extremely useful DMV when you need to fetch details like login name, transaction isolation level, status, used databases, language etc.

Query 1 : Complete details of every SQL Session

SELECT

S.SESSION_ID,

S.STATUS,

S.HOST_NAME,

DB_NAME(S.DATABASE_ID) DBNAME,

C.CLIENT_NET_ADDRESS,

CASE WHEN S.LOGIN_NAME = S.ORIGINAL_LOGIN_NAME THEN S.LOGIN_NAME ELSE S.LOGIN_NAME + ‘ (‘ +S.ORIGINAL_LOGIN_NAME + ‘)’ END AS LOGIN_NAME,

S.PROGRAM_NAME,

C.CONNECT_TIME, — DIFFRENT BETWEEN CONNECT & LOGIN TIME IS TIME TAKEN BY PRELOGON ACTIVITIES

S.LOGIN_TIME,

CASE S.TRANSACTION_ISOLATION_LEVEL

WHEN 0 THEN ‘UNSPECIFIED’

WHEN 1 THEN ‘READUNCOMITTED’

WHEN 2 THEN ‘READCOMMITTED’

WHEN 3 THEN ‘REPEATABLE’

WHEN 4 THEN ‘SERIALIZABLE’

WHEN 5 THEN ‘SNAPSHOT’

ELSE CAST(S.TRANSACTION_ISOLATION_LEVEL AS VARCHAR(32))

END AS TRANSACTION_ISOLATION_LEVEL_NAME,

–S.LAST_SUCCESSFUL_LOGON, — REQUIRES ‘COMMON CRITERIA COMPLIANCE ENABLED’ OPTION VIA SP_CONFIGURE.

–S.LAST_UNSUCCESSFUL_LOGON, — REQUIRES ‘COMMON CRITERIA COMPLIANCE ENABLED’ OPTION VIA SP_CONFIGURE.

–S.UNSUCCESSFUL_LOGONS, — REQUIRES ‘COMMON CRITERIA COMPLIANCE ENABLED’ OPTION VIA SP_CONFIGURE.

S.CPU_TIME AS CPU_TIME_MS,

S.MEMORY_USAGE AS MEMORY_USAGE_PAGES,

S.ROW_COUNT,

S.PREV_ERROR,

S.LAST_REQUEST_START_TIME,

S.LAST_REQUEST_END_TIME,

C.NET_TRANSPORT,

C.PROTOCOL_TYPE,

S.LANGUAGE,

S.DATE_FORMAT,

ST.TEXT AS QUERY_TEXT

FROM

SYS.DM_EXEC_SESSIONS S

FULL OUTER JOIN SYS.DM_EXEC_CONNECTIONS C ON C.SESSION_ID = S.SESSION_ID

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(C.MOST_RECENT_SQL_HANDLE) ST

WHERE

S.SESSION_ID IS NULL

OR S.SESSION_ID > 50

ORDER BY

S.SESSION_ID

Query 2 :  What number session every login have?

SELECT LOGIN_NAME , COUNT(SESSION_ID) AS [SESSION_COUNT]

FROM SYS.DM_EXEC_SESSIONS

GROUP BY LOGIN_NAME ORDER BY COUNT(SESSION_ID) DESC ;

Remarks

1.       All Possible status of sessions :

·         Running – Currently running one or more requests

·         Sleeping – Currently running no requests

·         Dormant – Session has been reset because of connection pooling and is now in prelogin state.

·         Preconnect – Session is in the Resource Governor classifier.

2.       All possible Transaction isolation level of the session, on basis on integer values :

·         0 = Unspecified

·         1 = ReadUncomitted

·         2 = ReadCommitted

·         3 = Repeatable

·         4 = Serializable

·         5 = Snapshot

3.       Common Criteria Compliance Enabled : If this option in server configuration is enabled, logon statistics are displayed in the following columns. If this option is not enabled, these columns will return null values.

·         last_successful_logon

·         last_unsuccessful_logon

·         unsuccessful_logons

4.       Permissions : User required VIEW SERVER STATE permission on the server to see all executing sessions on the instance of SQL Server, otherwise, the user will see only the current session.

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

DMV-1 : Who All Are connected ? …. sys.dm_exec_connections

The sys.dm_exec_connections DMV (Dynamic Management View) is represented by Books Online (BOL) as follows : http://msdn.microsoft.com/en-us/library/ms181509.aspx

Returns information about the connections established to this instance of SQL Server and the details of each connection.

sys.dm_exec_connections is the most common DMV used to get connection details. We will get details info regarding every connection like protocol user, last read time, last write time, last executed SQL query, connection time etc.

Query 1 : Complete details of every SQL Connection

SELECT

C.SESSION_ID,

C.MOST_RECENT_SESSION_ID,

C.CONNECT_TIME,

C.LAST_READ,

C.LAST_WRITE,

C.NUM_READS,

C.NUM_WRITES,

C.NET_TRANSPORT,

C.ENCRYPT_OPTION,

C.AUTH_SCHEME,

C.PROTOCOL_TYPE,

C.PROTOCOL_VERSION,

C.NET_PACKET_SIZE,

C.ENDPOINT_ID,

C.CLIENT_NET_ADDRESS,

C.CLIENT_TCP_PORT,

C.LOCAL_NET_ADDRESS,

C.LOCAL_TCP_PORT,

C.NODE_AFFINITY,

C.CONNECTION_ID,

C.PARENT_CONNECTION_ID,

C.MOST_RECENT_SQL_HANDLE,

CASE WHEN ST.DBID = 32767 THEN ‘RESOURCEDB’ ELSE DB_NAME(ST.DBID) END AS DATABASE_NAME,

CASE WHEN ST.DBID IS NULL THEN NULL ELSE OBJECT_SCHEMA_NAME(ST.OBJECTID, ST.DBID) END AS OBJECT_SCHEMA_NAME,

CASE WHEN ST.DBID IS NULL THEN NULL ELSE OBJECT_NAME(ST.OBJECTID, ST.DBID) END AS OBJECT_NAME,

ST.TEXT AS QUERY_TEXT

FROM

SYS.DM_EXEC_CONNECTIONS C

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(C.MOST_RECENT_SQL_HANDLE) ST

Query 2 : Sample Query Get a count of SQL connections by IP address

SELECT EC.CLIENT_NET_ADDRESS ,

ES.[PROGRAM_NAME] ,

ES.[HOST_NAME] ,

ES.LOGIN_NAME ,

COUNT(EC.SESSION_ID) AS [CONNECTION COUNT]

FROM SYS.DM_EXEC_SESSIONS AS ES

INNER JOIN SYS.DM_EXEC_CONNECTIONS AS EC

ON ES.SESSION_ID = EC.SESSION_ID

GROUP BY EC.CLIENT_NET_ADDRESS ,

ES.[PROGRAM_NAME] ,

ES.[HOST_NAME] ,

ES.LOGIN_NAME

ORDER BY EC.CLIENT_NET_ADDRESS ,

ES.[PROGRAM_NAME] ;

Permissions : User required VIEW SERVER STATE permission on the server, to use this DMV.

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

SQL Server Discovery Report

How can I check what all SQL server features and version are installed on my Machine ?

It’s a frequently asked question. You need to study your environment, need to login each instance to check version & other details, to answer this question.

Till SQL Server 2005, you have no direct solution.

But Starting from SQL Server 2008, Microsoft added a SQL Server discovery report as an useful tool under Tools page of Installation Center.

SQL Server Discovery tool

Lunch SQL Server Installation Center under Configuration Tools ( Click on the Start menu, go to All Programs, click to Microsoft SQL Server <Version Name>, under Configuration Tools click on SQL Server Installation Center) OR Directly Run Setup.exe & browse to Tools Page

Step 1

Step 2

Step 3

Report will be generate & open in default browser automatically

Report Location

The SQL Server Discovery Report will be saved automatically in %ProgramFiles%Microsoft SQL Server100Setup BootstrapLog<last Setup Session>

Other Useful Options

· You can also generate the Discovery report through the command line. Run “Setup.exe /Action=RunDiscovery” from a command prompt

· If you add “/q” to the command line above no UI will be shown, but the report will saved in %ProgramFiles%Microsoft SQL Server100Setup BootstrapLog<last Setup Session>

Limitation

· SQL Server discovery tool cannot be used to discover Remote SQL server installation

 

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun
Reference : Rohit Garg (http://mssqlfun.com/)

Backup not starting for Database with Full Text Catalog…………..Failed to change the status to RESUME for full-text catalog in database. Error: 0x80043607

Issue :-

One of our Database with Full Text Catalog is not getting backed up. When I checked, backup keeps pending on 0% without any progress. In SQL server error log, I also found error related to Full text catalog that SQL server is facing issue in setting Full test catalog status.

These is some issue with FTS service due to which when backup ask FTS service to change Full text catalog status, it failed.

SQL server backup change status between PAUSE & RESUME before & after backup.

Failed to change the status to RESUME for full-text catalog “Test_FullTextCatalog” in database “Test”. Error: 0x80043607(An internal interface is being used after the corresponding catalog has been shutdown. The operation will be aborted.).

Solution :-

You need to restart the Full Text service to resolve the issue.

I have restarted the FTS & try backup again and it worked successful.

Please share if you face any more issue in this regard or any other possible solution.

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

How to get list of all available parameter of .EXE file ?

Today, We discuss How to get list of all available parameter of .EXE file OR How to find list of SQL Server setup parameters ?

Major problem is of all available parameter details. Sometime, We need to pass parameter to .EXE file to get some different & superior functionality.

We can get details of available parameters by passing “/?” to any .EXE file in windows cmd.

Example 1 : Check parameter of SQL Server Setup

List of parameters

Example 2 : Check parameter of Procmon.exe (other than SQL Server)

List of parameters

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

Refresh Intellisense in SSMS(SQL Server Management Studio) 2008 & above

Intellisense is a new feature that comes with SSMS(SQL Server Management Studio) 2008 onwards. Intellisense makes coding quite easy.

But Sometimes I find that intellisense becomes stale and you start getting the wavy red lines when writing code. Even though object exists in database but Intellisense is not able to recognize it.

Refreshing the cache is quite easy but not necessarily strictly required.

Go to Edit -> IntelliSense -> Refresh Local Cache OR you can use shortcut Ctrl + Shift + R

You can check in below image that database has 5 table but intellisense showing only 4 tables. Table “JKL” is missing from intellisense list. You can refresh the Intellisense to get all tables.

More details on Intellisense can be found over MSDN : http://msdn.microsoft.com/en-us/library/hh245114.aspx

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

Move or Relocate the files of Resoruce Database in SQL Server 2005

The Resource database is a read-only database that contains all the system objects that are included with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database does not contain user data or user metadata. It comes into picture from SQL Server 2005 onwards.

In SQL Server 2005, in order to move or relocate the files of the Resource Database :-

1) Stop the SQL Server service

2) Start it using either -m (single user mode) or -f (minimal configuration) startup option which will start it in the maintenance mode. In addition, use the -T3608 trace flag which will skip the recovery of all the databases other than the master database. By doing this, we are ensuring that there is no process using the Resource database.

3) After this, the move is the same as others by using the ALTER DATABASE command: Change the file location by below command.

ALTER DATABASE MSSQLSYSTEMRESOURCE MODIFY FILE (NAME=DATA, FILENAME= ‘<THE NEW PATH FOR THE DATA FILE>\MSSQLSYSTEMRESOURCE.MDF’)

ALTER DATABASE MSSQLSYSTEMRESOURCE MODIFY FILE (NAME=LOG, FILENAME= ‘<THE NEW PATH FOR THE LOG FILE>\MSSQLSYSTEMRESOURCE.LDF’)

4) Once above command completed, then stop the SQL Server service

5) Move the file or files to the new location.

6) Restart the instance of SQL Server, this time without those startup option flags and without the trace flag

Please do note that this behavior has changed from SQL Server 2008 onwards. Now, Resource database cannot be moved.

If you liked this post, do like on Facebook at https://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

Microsoft SQL Server License Helpline

Microsoft SQL Server license has lots of flavors in terms of user based \ CAL or processor based license, license based on versions or license based on environment (physical or virtual).

Although, Microsoft release license guidelines for each & every SQL server version. But there are more complex scenarios for licensing then we think.

Best person to answer your all queries over licensing is MICROSOFT itself.

You can call Microsoft Licensing at 1-800-426-9400, Monday to Friday, 6:00 A.M. to 6:00 P.M. (Pacific Time) to speak directly to a Microsoft licensing specialist.

If you liked this post, do like on Facebook at https://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

How to find/modify SQLServer Agent logfile location?

1. To get the location of SQLServer Agent log file, the log file is called SQLAGENT.out

DECLARE @AGENT_ERRORLOG NVARCHAR(255)

EXECUTE MASTER.DBO.XP_INSTANCE_REGREAD N’HKEY_LOCAL_MACHINE’,

N’SOFTWAREMICROSOFTMSSQLSERVERSQLSERVERAGENT’,

N’ERRORLOGFILE’,

@AGENT_ERRORLOG OUTPUT,

N’NO_OUTPUT’

SELECT @@SERVERNAME SERVERNAME, @AGENT_ERRORLOG AGENTERRORLOGLOCATION

This command will work for both default & named instance.

2. To modify location and name of SQLServer Agent log file

USE [MSDB]

GO

EXEC MSDB.DBO.SP_SET_SQLAGENT_PROPERTIES @ERRORLOG_FILE=N’C:TEMPSQLAGENT.OUT’

GO

3. To recycle SQLServer Agent log file

EXEC MSDB.DBO.SP_CYCLE_AGENT_ERRORLOG

When SQLServer Agent recycles the log file, SQLAGENT.out will be SQLAGENT.1 & SQLAGENT.1 will be SQLAGENT.2 and so on.

If you liked this post, do like on Facebook at https://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

%d bloggers like this: