Saturday, 12 March 2016

User is unable to connect to SQL Server in 2016. What may be scenarios and how to troubleshoot it?

Possible Scenarios and Resolution to login issue with Fixes-
    •  Error: 26: SQL Browser Firewall No connectivity between client and server.- Check SQL Server browser service using Services.msc 

    •  Error: 28: Instance TCP/IP was disabled- Go to SQL Server configuaration manager and enable the TCP/IP 

    •  Error: 40: Instance service is not running - Open the services and RUN the MSSQLSERVICE to permanent FIX run type make it as automatically. 

    •  Error: 18456: Login failed. (invalid login or password ) - Check your user name and pasowrd, Also server which you are trying to connect.

    •  Expired Timeout: Network issue, Server is busy, In server max sessions are open ,No available session memory - Try to ping the server, if network issue contact to Network team and if Memory issue contact to DBA support team or increase the memory. 

    •  Connection Forcibly Closed: Update the client computer to the server version of the SQL Server Native Client.

    •  In single user mode: if any other service is connected with the db Engine, it doesn't allow connections. change the mode as multiuser

Sunday, 6 March 2016

What are the recommendations to design a backup and recovery solution?

I hope the below recommendations will help you to design a backup and recovery solution.

  1. Determine what is needed
  2. Recovery Model
  3. Select Backup Types
  4. Backup Schedule
  5. Backup Process
  6. Document
  7. Backup to Disk
  8. Archive to Tape
  9. Backup to Different Drives
  10. Secure Backup Files
  11. Encrypt or Password Protect Backup Files
  12. Compress Backup Files
  13. How Much to Keep on Disk
  14. Online Backups
  15. Run Restore Verifyonly
  16. Offsite Storage
  17. Additional information:
  18. SQL Server Backup Checklist
Please comment if you have more recommendations to design a backup and recovery solution.  Jainendra Verma

Saturday, 5 March 2016

How to identify longest running queries in SQL Server?

There are two ways to identify slow running queries

1)      Profiler (By using duration of the query)

2)      sys.dm_exec_query_stats, sys.dm_exec_sql_text and sys.dm_exec_requests DMVs etc


Example 1-

SELECT  creation_time





        , execution_count

        , total_worker_time

        , total_elapsed_time

        , total_elapsed_time / execution_count avg_elapsed_time

        ,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,

         ((CASE statement_end_offset

          WHEN -1 THEN DATALENGTH(st.text)

          ELSE qs.statement_end_offset END

            - qs.statement_start_offset)/2) + 1) AS statement_text

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st

ORDER BY total_elapsed_time / execution_count DESC;

Example 2-

                        select *, sql_handle from sys.dm_exec_requests

                 select  * from sys.dm_exec_sql_text (0x020000004D4F6005A3E8119F3DD3297095832ABE63E312F2)

Example 3



    [ds_es].[login_name] AS [Login Name],

    DB_NAME (ds_tdt.database_id) AS [Database],

    [ds_tdt].[database_transaction_begin_time] AS [Begin Time],

    [ds_tdt].[database_transaction_log_bytes_used] AS [Log Bytes],

    [ds_tdt].[database_transaction_log_bytes_reserved] AS [Log Rsvd],

    [ds_est].text AS [Last T-SQL Text],

    [ds_eqp].[query_plan] AS [Last Plan]


    sys.dm_tran_database_transactions [ds_tdt]


    sys.dm_tran_session_transactions [ds_tst]


    [ds_tst].[transaction_id] = [ds_tdt].[transaction_id]


    sys.[dm_exec_sessions] [ds_es]


    [ds_es].[session_id] = [ds_tst].[session_id]


    sys.dm_exec_connections [ds_ec]


    [ds_ec].[session_id] = [ds_tst].[session_id]


    sys.dm_exec_requests [ds_er]


    [ds_er].[session_id] = [ds_tst].[session_id]


    sys.dm_exec_sql_text ([ds_ec].[most_recent_sql_handle]) AS [ds_est]


    sys.dm_exec_query_plan ([ds_er].[plan_handle]) AS [ds_eqp]

ORDER BY     [Begin Time] ASC;


Use this to clean buffer data for testing purpose

Dbcc Dropcleanbuffers

Person.Address SET AddressLine2 = '' WHERE AddressID = 112

Now open another connection in SQL and execute following script.


Result: - Using SPID can get Query-
Transaction information for database 'AdventureWorks2012'.
Oldest active transaction:
 SPID (server process ID): 57
 UID (user ID) : -1
 Name : user_transaction
 LSN : (163:324:1)
 Start time : Jan 16 2014 4:34:58:813PM
 SID : 0x0105000000000005150000001650720d0cca921474e68766e8030000
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Please commnet if you know any other way to get or  identify longest running queries in SQL Server.- Jainendra Verma

Important SQL commands for SQL Server DBAs and when it should use in SQL Server.

I know there are plenty of vital commands and Scripts are available for SQL DBAs,
I am mentioning few Important commands which every DBA must know. Also DBAs should know when it should use in production environment-

--To check for Blocked Processes:

--To find all the log files and the percentage space used of those log files in an instance:
dbcc sqlperf (logspace)  

--To shrink a database file without specifying target size(preferred for log files):
dbcc shrinkfile(fileid)

--To shrink a database file with specifying target size(preferred for datafiles):
dbcc shrinkfile(fileid,target_size)

--To check pages of table :
Dbcc ind('dbname','tablename',-1)

--To check pages contents
dbcc trace on(3604)  dbcc page('dbname',fid,pid,1)              -- F:File  P:page

--To consistancy check of database :
Dbcc CheckDB('dbname')

--To list all the files and their details in a particular database:
exec sp_helpfile

--To list all the databases and their details in a instance:
exec sp_helpdb

--To find the details about locks currently held by the processes:
exec sp_lock

--To find all the drive spaces through MS SQL:
EXEC master..xp_fixeddrives

--To put the database in single user mode at command prompt:
sqlservr.exe –m

--To put the database in Minimal mode at command prompt:
sqlservr.exe –f         -->  -c  for console applications

--To find out the Database ID?
 Select db_id ('db_name')

--To find out logins:
select * from sys.syslogins

--To find out current user:
Select current_user

--Short cut for MS SQL Server Management studio:

--To check the service pack at product level:
select serverproperty ('machinename')
SELECT  SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

--To check the open transactions :
dbcc opentran('dbname')

--To find MS SQL Server product level details:
exec master..xp_msver

--To find the details of current processes running like percent completed:
select * from sys.dm_exec_requests

--To find the longest running queries
select * from sys.dm_exec_query_stats

--Command to find the free space and used space :
select * from dbo.sysfiles

--To check load in server and database and files:
select * from sys.dm_io_virtual_file_stats(database id,file id)

--To find the longest running queries
select * from sys.dm_exec_sql_text

--To find Orphan login:

--To find orphaned users
EXEC sp_change_users_login 'Report'

--To map users to logins
exec sp_change_users_login @Action='update_one', @UserNamePattern='<database_user>', @LoginName='<login_name>'

--If you already have a login id and password for this user, fix it by doing:
exec sp_change_users_login 'Auto_Fix', 'user'

--To delete login:
exec sp_revokelogin 'USERX'

--To list all the objects owned by user id:
Select name from sysobjects where uid=user_id('user')

--To enable ‘show advanced options’ for server configuration options:
exec sp_configure 'show advanced options', 1

--To enable particlar server configuration option:
exec sp_configure 'option name', 1

--To find performnce related stastictics:
select * from sys.dm_os_performance_counters

--To find the information about indexes on tables and views.
select * from sys.dm_db_index_usage_stats

--To find out all of the users who are connected to the database server:
select * from sys.dm_exec_sessions

--To display the error log using the query:

--To display error log archive numbers and their dates:
exec sp_enumerrorlogs

--To view no. of traces running.
SELECT count(*) FROM :: fn_trace_getinfo(default) WHERE property = 5 and value = 1
--To find details about the traces which are running.
SELECT * FROM :: fn_trace_getinfo(default)

--To terminate a trace
EXEC sp_trace_setstatus 1, @status = 0 / @status=2

--To know the Space used bye Data,index in particular Database:

--To know  the DB status of particular Database:

--To view jobs which failed at last run:
 sysjobactivity, sysjobschedules, msdb.dbo.sysjobservers, msdb.dbo.sysjobs

--To set maximum connections.
exec SP_Configure

--To find no.of connections.
SELECT COUNT(dbid) as TotalConnections FROM sys.sysprocesses WHERE dbid > 0

--If log file is full,To find out reason.
Select name,log_reuse_wait_desc  from sys.databases