Sql-server – How to track if a trace is executing

sql serversql-server-2008-r2trace

I'm sure there's a trace here running and creating logs.

I have no idea who did this in the past ( Its here since my first day in this company ).

There's no trace of it on sp_whoisactive.

Activity monitor shows nothing.

Theres no job running.

There's no job about something like this ( I deleted all old jobs and created my own ones ).

But it's still creating this files. I can't delete if because SQL Server is using it. I can't stop this server because it's the production one. Maybe if I stop the server I can delete the files but they will be created again.

I'm sure there's no sign of a trace running.

This error log file is the same error log we can see on error logs > current

Best Answer

I think the log file you are referencing in your question is created by the SQL Server default trace. You should be able to verify that by running:

SELECT path AS [Default Trace File]
    ,max_size AS [Max File Size of Trace File]
    ,max_files AS [Max No of Trace Files]
    ,start_time AS [Start Time]
    ,last_event_time AS [Last Event Time]
FROM sys.traces
WHERE is_default = 1
GO

There is additional useful information about Different Ways to Find Default Trace Location in SQL Server.

Related Solutions

SQL Server – How to Find Databases with Large Number of Virtual Log Files

Here is a slightly simpler approach that avoids the cursor and nested exec:

SET NOCOUNT ON;

CREATE TABLE #to
(
  DBName SYSNAME,
  FileCount INT
);

DECLARE @v INT;
SELECT @v = CONVERT(INT, PARSENAME(CONVERT(VARCHAR(32), 
  SERVERPROPERTY('ProductVersion')), 4));

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'CREATE TABLE #ti
  (
    ' + CASE WHEN @v >= 11 THEN 'RecoveryUnitId INT,' ELSE '' END + '    
    FileId int
    , FileSize nvarchar(255)
    , StartOffset nvarchar(255)
    , FSeqNo nvarchar(255)
    , Status int
    , Parity int
    , CreateLSN nvarchar(255)
);';

SELECT @sql = @sql + N'
  INSERT #ti EXEC ' + QUOTENAME(name) 
    + '.sys.sp_executesql N''DBCC LOGINFO WITH NO_INFOMSGS'';
  INSERT #to(DBName,FileCount) SELECT N''' + name + ''', COUNT(*) FROM #ti;
  TRUNCATE TABLE #ti;'
FROM sys.databases
WHERE database_id > 4 AND [state] = 0;

EXEC sp_executesql @sql;

SELECT DBName, FileCount FROM #to -- WHERE FileCount > [some threshold];

DROP TABLE #to;

Sql-server – SQL Server, attaching a database with Filestream, error 2

It appears you are trying to use the GUI interface to attach the database. In this instance you are going to have to write a T-SQL statement in order to attach it, and let SQL Server know where you put the filestream data as well.

There is a write up here that provides a walk through on doing this, the command the author used in order to attach a database once he had moved the default location of the filestream data for the example database:

USE [master]
GO
CREATE DATABASE [FileStreamDB] ON 
( FILENAME = N'C:\FileStreamDB\FileStreamDB.mdf' ),
( FILENAME = N'C:\FileStreamDB\FileStreamDB_log.ldf' ),
FILEGROUP [FileStreamGroup] CONTAINS FILESTREAM DEFAULT 
( NAME = N'FileStreamDB_FSData', FILENAME = N'C:\FileStreamDB\FileStreamData' )
FOR ATTACH
GO

Best Answer

Related Solutions

SQL Server – How to Find Databases with Large Number of Virtual Log Files

Sql-server – SQL Server, attaching a database with Filestream, error 2

Related Question