Tuesday, August 8, 2017

SQLServerAgent could not be started (reason: Unable to connect to server '(local)'; SQLServerAgent cannot start).


If SQL Server agent not started and if you find below error in event viewer

Error Details

 Log Name             :         Application
Source                  :         SQLSERVERAGENT
Date                      :         8/8/2017 3:49:43 PM
Event ID               :         103
Task Category      :         Service Control
Level                    :         Error
Keywords             :      Classic
User                      :          N/A
Computer             :      VmSQLServer
Description           :
SQLServerAgent could not be started (reason: Unable to connect to server '(local)'; SQLServerAgent cannot start).















Solution 

Provide instance in  in "ServerHost" under

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.MSSQLSERVER\SQLServerAgent

Goto run
type regedit
expand HKEY_LOCAL_MACHINE --> SOFTWARE --> Microsoft --> Microsoft SQL Server
click on SQLServerAgent in Microsoft SQL Server
In right side pane double click on ServerHost and provide instance name under  value data

Friday, June 9, 2017

Read only access to all databases for existing Login






SET NOCOUNT ON;

DECLARE @user_name    SYSNAME
        , @login_name SYSNAME;

SELECT @user_name = 'Domain\Login',
       @login_name = 'Domain\Login'

Select  '
    USE ' + QUOTENAME(NAME) + ';

    CREATE USER ' + QUOTENAME(@user_name)
       + ' FOR LOGIN ' + QUOTENAME(@login_name)
       + ' WITH DEFAULT_SCHEMA=[dbo];

    EXEC sys.sp_addrolemember
      ''db_datareader'',
      ''' + @user_name+ ''';

   
GO
'
FROM   sys.databases
WHERE  database_id > 4
       AND state_desc = 'ONLINE' 

Thursday, May 25, 2017

About Trace Flag -T 1117


In your SQL Server environment for any data base having more than one data file along with temp database data files with the same size and with auto-grow option.

Lets assume that you have a database with eight files & initial size of 1024 MB and auto-grow of 200 MB each.

The default behaviour is for one of these eight files to grow by the specified increment. This will happen when all of the files are in full and after one file to grow by the specified increment. This will happen when all the files are full, and after one file grows and SQL Server algorithm will direct new extent allocation to the file with the most available space, so new extents will be written to the one file that just grew.

To overcome this we have the trace flag 1117, it changes the behaviour of file growth. If one data file in file group grows, it forces other files in that file group to also grow.

All the files have about same percent of free space after auto-grow and the benefits of evenly distributing IO will be preserved

But Trace 1117 applied to the entire SQL Server instance. you can't apply to any one specific database. It will effect all files in the same filegroup of database. If the database having multiple filegroups and auto-grow event occurs in one filegroup will not affect the other filegroups.







Tuesday, April 4, 2017

sql server configuration default values of 2008R2/2012/2014 versions



Below are the SQL Server Default configuration values for 2008R2/2012/2014



SQL 2008R2
SQL 2012
SQL2014
Configuration Option
Minimum Value
Maximum Value
Minimum Value
Maximum Value
Minimum Value
Maximum Value
access check cache bucket count
0
65536
0
65536
0
65536
access check cache quota
0
2147483647
0
2147483647
0
2147483647
Ad Hoc Distributed Queries
0
1
0
1
0
1
affinity I/O mask
-2147483648
2147483647
-2147483648
2147483647
-2147483648
2147483647
affinity mask
-2147483648
2147483647
-2147483648
2147483647
-2147483648
2147483647
affinity64 I/O mask
-2147483648
2147483647
-2147483648
2147483647
-2147483648
2147483647
affinity64 mask
-2147483648
2147483647
-2147483648
2147483647
-2147483648
2147483647
Agent XPs
0
1
0
1
0
1
allow updates
0
1
0
1
0
1
awe enabled
0
1




backup checksum default




0
1
backup compression default
0
1
0
1
0
1
blocked process threshold (s)
0
86400
0
86400
0
86400
c2 audit mode
0
1
0
1
0
1
clr enabled
0
1
0
1
0
1
common criteria compliance enabled
0
1
0
1
0
1
contained database authentication


0
1
0
1
cost threshold for parallelism
0
32767
0
32767
0
32767
cross db ownership chaining
0
1
0
1
0
1
cursor threshold
-1
2147483647
-1
2147483647
-1
2147483647
Database Mail XPs
0
1
0
1
0
1
default full-text language
0
2147483647
0
2147483647
0
2147483647
default language
0
9999
0
9999
0
9999
default trace enabled
0
1
0
1
0
1
disallow results from triggers
0
1
0
1
0
1
EKM provider enabled
0
1
0
1
0
1
filestream access level
0
2
0
2
0
2
fill factor (%)
0
100
0
100
0
100
ft crawl bandwidth (max)
0
32767
0
32767
0
32767
ft crawl bandwidth (min)
0
32767
0
32767
0
32767
ft notify bandwidth (max)
0
32767
0
32767
0
32767
ft notify bandwidth (min)
0
32767
0
32767
0
32767
index create memory (KB)
704
2147483647
704
2147483647
704
2147483647
in-doubt xact resolution
0
2
0
2
0
2
lightweight pooling
0
1
0
1
0
1
locks
5000
2147483647
5000
2147483647
5000
2147483647
max degree of parallelism
0
1024
0
32767
0
32767
max full-text crawl range
0
256
0
256
0
256
max server memory (MB)
16
2147483647
128
2147483647
128
2147483647
max text repl size (B)
-1
2147483647
-1
2147483647
-1
2147483647
max worker threads
128
32767
128
65535
128
65535
media retention
0
365
0
365
0
365
min memory per query (KB)
512
2147483647
512
2147483647
512
2147483647
min server memory (MB)
0
2147483647
0
2147483647
0
2147483647
nested triggers
0
1
0
1
0
1
network packet size (B)
512
32767
512
32767
512
32767
Ole Automation Procedures
0
1
0
1
0
1
open objects
0
2147483647
0
2147483647
0
2147483647
optimize for ad hoc workloads
0
1
0
1
0
1
PH timeout (s)
1
3600
1
3600
1
3600
precompute rank
0
1
0
1
0
1
priority boost
0
1
0
1
0
1
query governor cost limit
0
2147483647
0
2147483647
0
2147483647
query wait (s)
-1
2147483647
-1
2147483647
-1
2147483647
recovery interval (min)
0
32767
0
32767
0
32767
remote access
0
1
0
1
0
1
remote admin connections
0
1
0
1
0
1
remote login timeout (s)
0
2147483647
0
2147483647
0
2147483647
remote proc trans
0
1
0
1
0
1
remote query timeout (s)
0
2147483647
0
2147483647
0
2147483647
Replication XPs
0
1
0
1
0
1
scan for startup procs
0
1
0
1
0
1
server trigger recursion
0
1
0
1
0
1
set working set size
0
1
0
1
0
1
show advanced options
0
1
0
1
0
1
SMO and DMO XPs
0
1
0
1
0
1
SQL Mail XPs
0
1




transform noise words
0
1
0
1
0
1
two digit year cutoff
1753
9999
1753
9999
1753
9999
user connections
0
32767
0
32767
0
32767
user options
0
32767
0
32767
0
32767
xp_cmdshell
0
1
0
1
0
1