Like this:

Anti-virus & SQL Server on one system together are friends not enemies, if configured properly.

Anti-virus are very useful programs from security, audit & venerability detection & removal point of view. But if team managing anti-virus server did not configure anti-virus policies properly then your SQL Server is going to face the problem.

Here, we will discuss the file types that must be in exclusion list of anti-virus scanning policy. In other words, Let anti-virus programs deal with what they do best, and let SQL Server handle what it does best and avoid, at all possible costs, any interaction between the two

1. Binaries: Or the the paths to the actual executable for any of your running SQL Server Services (MSSQL, SQL Server Agent, SSAS, etc). Typically these are found, by default, in the C:\Program Files\Microsoft SQL Server folder – though this could easily be a different path on many production machines. (And, note, you’ll likely want to make sure that C:\Program Files (x86)\Microsoft SQL Server is included in any exclusions as well on x64 machines).

2. SQL Server Error Logs : Not your database log files, but the text files that SQL Server uses to keep its own ‘event logs’ running or up-to-date. (Which, in turn is also different than Windows’ system event logs as well.) By default the path to these files is, in turn, covered in the paths outlined above – or it’s part of the ‘program files’ data associated with your binaries – though you CAN move the location of these logs if desired (as an advanced operation via the startup parameters).)

3. Data And Log Files: Your actual .mdf, .ndf, and .ldf files – or the locations of your data files and log files. (Which you’ll want to make sure get excluded from anything that anti-virus monitors – otherwise creation of new databases, file-growth operations, and other normal ‘stuff’ can/will get blocked by anti-virus operations – which would be fatal in many cases.)

4. Backups: Yes, the path to any of your backups – or backup locations is also something you’ll want to make sure that anti-virus doesn’t monitor.

5. Others: Any other files related to SQL server & for its proper working. Like .TUF, .SS, .TRC etc.

Like this:

In SQL Server versions prior to 2005, it was possible to have invalid data values into a database. That can lead to incorrect result or issue at time of query execution.

SQL Server 2005 brings new option to the DBCC CHECKDB and DBCC CHECKTABLE commands to solve this issue. “DATA_PURITY”, The new option to check for issues where column values are not valid or out-of-range. In SQL Server 2005 data purity check is enabled by default & performed each time DBCC CHECKDB runs on database. But this is not same for upgraded databases.

For upgraded databases, We need to run below DBCC command on database & In case DBCC CHECKDB with DATA_PURITY got completed successfully, a bit dbi_dbccFlags will be flipped from 0 to 2. Now from future onwards data purity is enabled by default for upgraded databases as well & it will be performed each time DBCC CHECKDB runs.

DBCC CHECKDB with DATA_PURITY

· How to check Data purity for your SQL instance ?

DBCC TRACEON (3604)

GO

CREATE TABLE #DBCC (

PARENTOBJECT VARCHAR(255),

[OBJECT] VARCHAR(255),

FIELD VARCHAR(255),

[VALUE] VARCHAR(255)

)

CREATE TABLE #DBCC2 (

DATABASENAME VARCHAR(255),

PARENTOBJECT VARCHAR(255),

[OBJECT] VARCHAR(255),

FIELD VARCHAR(255),

[VALUE] VARCHAR(255)

)

EXEC MASTER.DBO.SP_MSFOREACHDB

‘USE ? INSERT INTO #DBCC EXECUTE (”DBCC DBINFO WITH TABLERESULTS”);

INSERT INTO #DBCC2 SELECT ”?”, * FROM #DBCC;

DELETE FROM #DBCC’

SELECT DATABASENAME,

(SELECT VALUE FROM #DBCC2 WHERE DATABASENAME = A.DATABASENAME AND FIELD = ‘DBI_DBCCLASTKNOWNGOOD’) LASTGOODCHECKDBDATE,

(SELECT VALUE FROM #DBCC2 WHERE DATABASENAME = A.DATABASENAME AND FIELD = ‘DBI_CREATEVERSION’) DBCREATEVERSION,

· Self-Help Online Support will be available for a minimum of 12 months. Example: Microsoft online Knowledge Base articles, FAQs, troubleshooting tools, and other resources, that help customers resolve common issues.

· Updates to this software will stop and customers will no longer receive patches, including security updates.

What Are available Options :

· Upgrade to a supported version of SQL Server.

· Find out more about a Custom Support Agreement (CSA).

· Run SQL Server 2000 unsupported with access to Self-Help Online Support only (not recommended).

· Upgrade to SQL Server 2005 is not recommended as SQL Server 2005 is also transitioned from Mainstream Support to Extended Support.