SQL Server: Script to Start and Stop a New Trace without Using Profiler GUI

SQL Server Profiler is an useful tool, but basically, important part of profiler is trace which this graphical interface shows. Trace data can be collected and saved with in a file, even without using SQL Server Profiler graphical interface.

To do so you need a script that will perform this task. Good thing is that, SQL Server Profiler helps us to create this script, through following path.

Now, only you need to change it according to your requirements. Here is a complete script for auto trace which we use on production servers, to capture quires taking time more than 5 seconds to execute. Script can be used in a job to execute on daily bases. 

 

To stop this auto trace, you just need to change its status to STOP and then CLOSE. Keep in mind to close a trace, you must stop it first.

SQL Server Log: I/O is frozen on database DatabaseName

One more daily base SQL Server log message reported from one of our production server was

I/O is frozen on database model. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.

AND

I/O was resumed on database DATABASENAME. No user action is required.

First thing to note is that it’s just a message and not an error and no user action is required. On investigation for said production server, I found that our System Administrators has enabled SQL Server backup through VSS (Volume Shadow Copy Services) and this process actually freeze I/O temporarily to take shadow copy and release it back once process is complete. How it works read here.


------------------------------------------------------------------------------------

Read More about SQL Server Log Errors/Messages 

• SQL Server has encountered 1 occurrence(s) of cachestore flush
• DeviceIoControl failed, error 21
• This instance of SQL Server has been using a process ID of xxxx since mm/dd/yyyy
• SQL Server has encountered occurrence(s) of I/O requests taking longer than 15 seconds to complete
• The time stamp counter of CPU on scheduler id 13 is not synchronized with other CPUs.

SQL Server has encountered 1 occurrence(s) of cachestore flush

SQL Server Log report from one of our production server was continuously showing following messages.

Log Date	Process Info	Process Text
2012-07-23T20:00:08.880	spid17s	SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2012-07-23T20:00:08.880	spid17s	SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2012-07-23T20:00:07.190	spid17s	SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2012-07-23T06:00:04.640	spid16s	SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2012-07-23T06:00:04.640	spid16s	SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2012-07-23T06:00:04.580	spid16s	SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.

It happens when you configure user database with Auto Close option.

SQL Server, close a user database automatically, when last session is closed and reactivated when a login request is received.  We must keep this option OFF for a better performance. Why so read this.

------------------------------------------------------------------------------------

Read More about SQL Server Log Errors/Messages 

• DeviceIoControl failed, error 21
• This instance of SQL Server has been using a process ID of xxxx since mm/dd/yyyy
• SQL Server has encountered occurrence(s) of I/O requests taking longer than 15 seconds to complete
• The time stamp counter of CPU on scheduler id 13 is not synchronized with other CPUs.
• I/O is frozen on database DatabaseName

SQL Server: How to Read Trace File to Detect Database Detachment

On of our production server contains more then 200 databases. Few of them are rarely used but still required. Few days back, someone from DBA’s  team accidentally detached on of less used database. But once we need it we got error as there was no required database on server.

To find out, that when and who detached this database we have quickly executed a simple script.

First get current trace file name from sys.traces table

SELECT * FROM sys.traces

GO

Then copy trace file name and assign it @trace_file parameter and execute following script.

DECLARE @trace_file NVARCHAR(500)

SELECT  @trace_file = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\log_88.trc'

SELECT  *

FROM    [fn_trace_gettable](@trace_file, DEFAULT)

WHERE   TextData LIKE '%DETACH%'

ORDER BY starttime DESC