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

What is CheckDB and How CheckDB is executed

The command CheckDB checks the logical and physical integrity of all the objects in the specified database. It will check internal structures, metadata, and data in the database.

CheckDB has two roles: Integrity Check, Correction.

Integrity check is helps to verify the allocated structures of GAM, SGAM, PFS and IAM etc., In addition to it verifies the table consistency of all the tables and of its indexes including system catalogs. DBCC CHECKDB automatically executes DBCC CHECKTABLE for each table in the database, DBCC CHECKALLOC and DBCC CHECKCATALOG, no need to run them separately.

DBCC CHECKALLOC –Used to check disk space allocation structures for a particular database.

DBCC CHECKTABLE – Used to check integrity of the structures and pages that build up the indexeds or tables.

DBCC CHECKCATALOG – Used to Checks the consistency of catalog for the specified database, but database has to be online.

How CheckDB is executed?

CheckDB doesn't run directly over the database, a hidden database snapshot is created and process runs on that snapshot.

To check the integrity of AdventureWorks
DBCC CheckDB(AdventureWorks);

The default output of checkdb will have lot of information and the last two lines will show you exact information like this:

CHECKDB found 0 allocation errors and 0 consistency errors in database 'AdventureWorks'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Few Execution Options:

NO_INFOMSGS -- It doesn't display any informational messages unless there is any error

ALL_ERRORMSGS -- This is the default parameter since SQL Server 2008 SP1, displays complete information

NOINDEX -- It Skips nonclustered indexes while doing integrity checks

ESTIMATEONLY -- Estimates the space needed for TempDB to run the checkdb, it is useful when you are planning to run this checkdb on VLDB

TABLOCK -- Uses locks instead of database snapshots, it is useful when the snapshot creation is failing

PHYSICAL_ONLY -- It skips most of the logical verifications, and it runs with less resources. But Full execution is recommended

TABLERESULTS -- It returns the information in a tabular format rather than free-form text.

DBCC CheckDB(AdventureWorks) with TABLERESULTS


DBCC CheckDB(AdventureWorks) with ESTIMATEONLY, TABLERESULTS -- If you want to include multiple switches


If CheckDB is not reported any errors then no need to repair your database. There are couple of options available in CheckDB to repair databases.


DBCC CheckDB(AdventureWorks, REPAIR_REBUILD)
DBCC CheckDB(AdventureWorks, REPAIR_ALLOW_DATA_LOSS)

Here we need to remember few points:

Repair_Rebuild -- It will repair only non-clustered indexes
Repair cannot fix all every things in database
Repairs are always run offline, (databases must be in single user mode)
At times repairs will cause data loss if you use REPAIR_ALLOW_DATA_LOSS
Repair cannot be done in master and tempdb because they cannot be put in single-user mode.

How to create drop down list with symbols in excel

 

If you want to create drop down list like below,

 

1. Click on data menu
2. Data validation - data validation - list
3. Allow -- Click on List button
4. On Source - Select the list of values that to be displayed on the list.
5. Click on OK

 

 

 

 

 If  you want to create drop down list like below but with sybmols,

 

1. Click on data menu
2. Data validation - data validation - list
3. Allow -- Click on List button
4. On Source - Select the list of values that to be displayed on the list.
5. Click on OK

If you want to get symbols then you need to type alt + 0245 (from numeric key pad)

SQL Server compression backup and trace flag 3042

SQL Server 2008 onwards we have a new feature which allows us to generate compressed backup. This feature is a part of Enterprise edition in SQL 2008 and available in SQL 2008 R2 Standard and Enterprise edition.


We will see how compression backup will works, when you run backup database command using compression option SQL server engine will think how much space it is required for backup and occupies that much space on the filesystem, if the backup is not fit with in the allocated space then it will simply extended to file size. If the backup completes and not used the pre-allocated space then it simply shrinks the file in the disk. The pre-allocation reduces the IO overhead rather constantly growing the file size while backup is running.


Most of the times the pre-allocated space is larger than the actual backup size. For example if your disk is low and you initiate a large backup backup may fail with insufficient disk space. If you want to switch off the pre-allocated space by SQL Server then you can use 3042 trace flag, enabling this trace flag will tells the database engine not to pre-allocate file size and just grow the file while backup is running. But there will be a little performance hit when you enable the trace flag

Moving or Restore CDC enabled databases from one instance to another instance

If a database is CDC enabled you need to follow few more steps while migrating to other instance or on the same instance.

As a first step we will create a CDC enabled database and will setup change data capture for a table with the below script

create database TestDB  -- Creates a sample database

use testdb
create table test1 (id int, name varchar(20))

select name, is_cdc_enabled from sys.databases where name='testdb'  -- Verify whether CDC is enabled on the database or not

use TestDB
exec sys.sp_cdc_enable_db            -- This command enables CDC on the database
select name, is_cdc_enabled from sys.databases where name='testdb'  -- Verify CDC flag updated on the table or not

USE TestDB
exec  sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name = N'test1', @role_name = NULL   -- Enable CDC at table level, after executing this command it creates two jobs in the SQL Server Agent (CDC Capture, CDC Cleanup)

To disable the CDC use the below commands

Use TestDB
go
exec sys.sp_cdc_disable_table @source_schema = N'dbo', @source_name   = N'test1', @capture_instance='dbo_test1'
GO


Script I  - Restore CDC enabled database on the same instance

restore database testdb from disk = 'C:\temp\testdb_full.bak' with replace

As the database exists on the same instance so it doesn't require any additional parameters required to restore the database (Just replace parameter is sufficient).

Script II - Restore CDC enabled database to a different instance (with same name or different name)

1. Take full backup of the database
2. Use the below script to restore on the target server

RESTORE DATABASE TestDB FROM 
DISK = N'F:\Testdb_full.bak' WITH  FILE = 1, 
MOVE N'TestDB' TO N'F:\Data1\TestDB_data.mdf', 
MOVE N'TestDB_log' TO N'F:\Log\TestDB_log.ldf', 
NOUNLOAD,  REPLACE,  STATS = 10, KEEP_CDC
GO

If you observe the above command it has a new parameter along with regular parameters called KEEP_CDC which helps to restore database with CDC values

3.We need to run the below commands to create the jobs on the target instance.

Use TestDB
exec sys.sp_cdc_add_job 'capture'
GO
exec sys.sp_cdc_add_job 'cleanup'
GO

 

Error when you select properties of database in SQL Server 2008 (Property Size is not available for Database)

When I am trying to setup mirroring for this database I got the below error message:

TITLE: Microsoft SQL Server Management Studio
------------------------------
Cannot show requested dialog.
------------------------------
ADDITIONAL INFORMATION:
Cannot show requested dialog. (SqlMgmt)
------------------------------
Property Size is not available for Database '[DatabaseName]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights.  (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.6000.34+((KJ_SP3).140819-1214+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.PropertyCannotBeRetrievedExceptionText&EvtID=Size&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
I am sysadmin on that server, so the issue is not related to permission.
I checked the database using sp_help and owner is set and the login is exists on the server.

Resolution : Change the database owner to "SA" or some other login in your system and change back to original owner then it works.

 

WMI provider error Invalid parameter [0X80041008] Error trying to change service account on SQL Server service

You may face the below error while changing back the SQL Server service accounts to virtual service accounts like NT Service\MSSQLServer then you may encounter the below error:

---------------------------
WMI Provider Error
---------------------------
Invalid parameter [0x80041008]
---------------------------
OK  
---------------------------
Resolution:

1. Verify whether NT Service\MSSQLServer, NT Service\SQLServerAgent accounts are find in the SQLServerSQLAgentUser$ComputerName, SQLServerMSSQLUser$ComputerName groups on computer management.

2. If there is no such virtual account add the account to those groups

3. Try use services.msc to change the service account to virtual service account (not use SQL configuration manager)