"Noise" in the SQL ERROR LOG when creating a DB Snapshot

Question

When creating a DB snapshot successfully, many rows are written to the Error log.

Since I'm creating snapshot every few minutes to a few DBs, this fills up my error log.

is there a way to suppress those messages?

Answer 1

Try this one

DBCC TRACEON (3226)  ----This flag disable a message "Successful Backup Messages.. " in error log.

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 2

Gal,

Can you share those messages you are seeing and what is the version of SQL Server (select @@version) you are using? Creating Snapshots every few minutes and dropping them may have performance impacts depending on the server you are using.

In SQL Server 2005, deleting the snapshot clears the cache. And in all versions from 2005, having too many snapshots at the sametime has perf implications for IO and memory too. This may not be a issue for you, just adding that info. Are these the messages you are seeing?

Starting up database 'abc'.

SQL Server has encountered 2 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
SQL Server has encountered 2 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
SQL Server has encountered 2 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.

---

Sankar Reddy

Blog: http://SankarReddy.com/
Twitter: http://twitter.com/SankarReddy13/

Answer 3

I did add DBCC traceon (3226) to my startup parameters but it doesn't change the snapshot messages.

the massages are like this:

1486 transactions rolled forward in database 'xxx' (30). This is an information message only. No user action is required.



















Answer 4

Hi Gal, I have not heard able to suppress this information written to the error log, perhaps Microsoft's internal flag can be, perhaps not .

---

Best Regards, nicofer

Answer 5

I did add DBCC traceon (3226) to my startup parameters but it doesn't change the snapshot messages.

the massages are like this:

1486 transactions rolled forward in database 'xxx' (30). This is an information message only. No user action is required.

There is no way to suppress these messages from Snapshots.  They will occur for every snapshot you create.  This has been discussed amongst the MVP's internally with Microsoft a number of times, and there currently is nothing you can do to stop the messages.

---

Jonathan Kehayias | Principal Consultant, SQLSkills.com
SQL Server MVP | Microsoft Certified Master: SQL Server 2008
Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
Please click the Mark as Answer button if a post solves your problem!