Home » Posts tagged 'SQL' (Page 2)
Tag Archives: SQL
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/)
SQL Server Silent unattended installation using configuration file
1) Open Installation Wizard
Open Installation Wizard
2) Select Feature You want to install
Select Feature You want to install
3) Provide installation configuration
Provide installation configuration
4) Now that you have the configuration file, copy it to your own folder or network share where you want to start the unattended installation.
Configuration file
5) Cancel setup as we are interested in the unattended silent mode of installation; not the UI one.
6) Edit the configuration file as follows:
- Set QUIET to “True”. This specifies that Setup will run in a quiet mode without any user interface
i. QUIET="True"
- Set SQLSYSADMINACCOUNTS to “BUILTINADMINISTRATORS”. This will ensure that administrators on the machine are added as members of the sysadmin role. You can set its value based on your needs (Ex: SQLSYSADMINACCOUNTS=”domainYourUser”), but this is the more generic approach. I have added My user instead of
BUILTINADMINISTRATORS, to secure SQL server from unwanted logins.
i. SQLSYSADMINACCOUNTS="BUILTINADMINISTRATORS"
- Add PID and set its value to your product license key. If your setup.exe already comes preloaded with the key, there is no need to add this option to the configuration file.
- Add IACCEPTSQLSERVERLICENSETERMS and set its value to “True”. This is to require to acknowledge acceptance of the license terms at time of unattended installations.
i. IACCEPTSQLSERVERLICENSETERMS="True
- Remove the ADDCURRENTUSERASSQLADMIN parameter. The reason is that this parameter can’t be used when SQLSYSADMINACCOUNTS is specified, and it only applies to Express installations.
- Remove the UIMODE parameter as it can’t be used with the QUITE parameter.
- Remove INSTALLSHAREDDIR, INSTALLSHAREDWOWDIR, INSTANCEDIR parameters if you want to install on the default installation directories or mention appropriate directories for installation.
- You can add or remove the feature you want to install, Select FEATURES=SQLENGINE,SSMS,ADV_SSMS in the configuration file. You can change that based on your needs.
- The full list of available feature parameters and their descriptions : http://msdn.microsoft.com/en-us/library/ms144259.aspx#Feature
- Now, Your configuration file is ready, you need to create a batch file that will run the silent unattended setup. Create a new file ”SQLServer2012_SilentInstall” with extension = “.bat”.
We have added, Date time to get the time taken by complete installation. Edit below script with your setup & configuration file location.
| @ECHO offecho Installing SQL Server 2008 R2date/ttime /t
“D:SQLFULL_x86_ENUsetup.exe” /ConfigurationFile=”D:ConfigurationFile.ini” date/t time /t |
7) My SQL Server got installed
SQL Server got installed
8) Let’s Verify
SQL Server Installed
If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun
Reference : Rohit Garg (http://mssqlfun.com/)
Cumulative Update – 9 for SQL Server 2008 Service Pack 3 Is Now Available !
The 9th cumulative update release for SQL Server 2008 Service Pack 3 is now available. Cumulative Update 9 contains all the hotfixes released since the initial release of SQL Server 2008 SP3.
Those who are facing severe issues with their environment, they can plan to test CU9 in test environment & then move to Production after satisfactory results.
To other, I suggest to wait for SP4 final release to deploy on your production environment, to have consolidate build.
KB Article For CU9 of SQL Server 2008 SP3
- CU#9 KB Article: http://support.microsoft.com/kb/2799883
Previous Cumulative Update KB Articles of SQL Server 2008 SP3:
- CU#8 KB Article: http://support.microsoft.com/kb/2771833
- CU#7 KB Article: http://support.microsoft.com/kb/2738350
- CU#6 KB Article: http://support.microsoft.com/kb/2715953
- CU#5 KB Article: http://support.microsoft.com/kb/2696626
- CU#4 KB Article: http://support.microsoft.com/kb/2673383
- CU#3 KB Article: http://support.microsoft.com/kb/2648098
- CU#2 KB Article: http://support.microsoft.com/kb/2633143
- CU#1 KB Article: http://support.microsoft.com/kb/2617146
If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun
Reference : Rohit Garg (http://mssqlfun.com/)
MSSQLFUN 