While I was testing out the method to remove trace related messages from the error log yesterday, I stumbled upon this error message in my Virtual Machine.
Error: 17207, Severity: 16, State: 1.
FCB::RemoveAlternateStreams: Operating system error 6(The handle is invalid.) occurred while creating or opening file 'C:AdventureWorks_Data.mdf'.
Thinking that it was a one time occurance, I restarted the SQL Server service. Still the error message persisted. But there was no issues with the AdventureWorks database. It was online. I found out some useful information regarding these error messages in this KB article.
As per the information available in that article, SQL Server makes use of alternate streams while creating the internal snapshots. Starting SQL Server 2005, when DBCC CHECKDB executed against a database, an internal snapshot of the database is created and the actual DBCC is executed against the snapshot. (Here I had talked about the CHECKDB failing due to lack of space for the snapshot to be created). When the CHECKDB command completes, the database snapshot is dropped. At times, this snapshot does not get dropped. In such situations, SQL Server will try to clean up these internal snapshots when the SQL Server service is started again. At that time if these file streams are accessed by some other application, SQL Server will not be able to clean them up. Hence the above error messages are logged in the Error Log.
I remembered running DBCC CHECKDB against AdventureWorks database sometime back. It is likely that I would have abruptly shutdown my VM and the internal snapshot did not get cleaned up. During SQL Server startup, some application was accessing it. The first culprit that came to my mind is the Antivirus software. A quick check revealed that the database files were also being scanned by the antivirus application. I configured the Antivirus application to exlcude the database related files as seen in this screenshot.
After excluding the database related file extensions in the Antivirus application, I restarted the SQL Server service. Its gone! The The handle is invalid error message disappeared from the SQL Server Error Log. It is always recommended to exclude the database file extensions/folders hosting the database files, in the Antivirus software. This post adds one more reason why it should be.