Updating permissions settting for file the file permissions setting were supposed to be set to 'D:P

I got the below error while performing SQL Server 2008 R2 installation with NT Authority\Network Service account









Workaround :


1. Run cacls and icacls and add network service to all your mount points
2. Make sure you don't see any access denied messages when you run step 1
3. If you see any access denied message then explicitly specify full permissions to Network Service from the mount point / folder security

SQL Agent job is failing with Trace file name '' is invalid error

SQL Server Agent Job is failing with Trace file name '' is invalid
Message
Executed as user: NT SERVICE\SQLSERVERAGENT. Trace file name '' is invalid. [SQLSTATE 42000] (Error 19050)  The statement has been terminated. [SQLSTATE 01000] (Error 3621).  The step failed.

Steps to resolve issue:
1. We need to make sure whether default tracing is enabled or not, we can check this using below options:
sp_configure 'default trace enabled' -- If the run_value is 1 then the default trace for SQL Server is enabled
(OR)
SELECT* FROM sys.configurations WHERE configuration_id = 1568


2. Once you make sure the trace is enabled then you need to verify what is the default trace file, below are the differnt ways to identify it
select * from sys.traces
(or)
select * from sys.fn_trace_getinfo(NULL)
(or)
select * from :: fn_trace_getinfo(default)


In my case though the default trace is enabled there is no trace file generated by SQL Server for some reason, so I have enabled and disabled the default trace option fixed the issue.


Enable / Disable default trace in SQL Server


sp_configure 'default trace enabled', 1 (1 is enable 0 is for disable)
reconfigure

How to check file allocation unit size in windows servers

File Allocation Unit Size


Run the below command to get the drive size information and Bytes Per Cluster is the file allocation unit size.


fsutil fsinfo ntfsinfo c:
fsutil fsinfo ntfsinfo f:


C:\>fsutil fsinfo ntfsinfo c:
NTFS Volume Serial Number :       0xe42ac0022abfcfb2
NTFS Version   :                  3.1
LFS Version    :                  1.1
Number Sectors :                  0x000000000634f7ff
Total Clusters :                  0x0000000000c69eff
Free Clusters  :                  0x00000000006b9ee1
Total Reserved :                  0x0000000000000f80
Bytes Per Sector  :               512
Bytes Per Physical Sector :       512
Bytes Per Cluster :               4096          -- It shows the allocation unit size information
Bytes Per FileRecord Segment    : 1024
Clusters Per FileRecord Segment : 0
Mft Valid Data Length :           0x000000000b440000
Mft Start Lcn  :                  0x00000000000c0000
Mft2 Start Lcn :                  0x0000000000000002
Mft Zone Start :                  0x00000000000cb440
Mft Zone End   :                  0x00000000000cc820


Windows provides a tool called diskpart which is used for disk partition alignment.


C:\>diskpart
Microsoft DiskPart version 6.3.9600
Copyright (C) 1999-2013 Microsoft Corporation.
On computer: KalyanServer


DISKPART> list disk
  Disk ###  Status         Size     Free     Dyn  Gpt
  --------  -------------  -------  -------  ---  ---
  Disk 0    Online           50 GB  1024 KB
  Disk 1    Online           30 GB  1024 KB  
 
DISKPART> list volume
Volume ###  Ltr  Label        Fs     Type        Size     Status     Info
----------  ---  -----------  -----  ----------  -------  ---------  --------
Volume 0     C   New Volume   NTFS   Simple        49 GB  Healthy
Volume 1     D   New Volume   NTFS   Simple        29 GB  Healthy

DISKPART> select volume 0
Volume 0 is the selected volume.


DISKPART> filesystems
Current File System
  Type                 : NTFS
  Allocation Unit Size : 4096
  Flags : 00000000
 
DISKPART> format unit=64k quick       -- If you want to format it to another allocation unit, Don't ever do this on your live hosts.
  100 percent completed
DiskPart successfully formatted the volume.


DISKPART> filesystems
Current File System
  Type                 : NTFS
  Allocation Unit Size : 64K                     -- After format