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

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' 

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.

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