DB Reverts to Single-User Mode

• Leo.Miller

  SSChampion

  Points: 13051

  More actions

  January 5, 2011 at 2:49 pm

  #1269773

  Ninja's_RGR'us (1/5/2011)

  ---

  Thanx. But what I need is to be able to control how much data I keep in the trace (above and beyond 5 files @ 20 mb).

  What I did to manage this is create a SQL job that runs on startup and regularly throughout the day. It checks if a new tracefile has been created and if so copies the previous file to an alternate location. It's part of a server management system, so uses a small table to control things like: last file copied, default trace location and destination folder. The start value for the last file copied is 0 so the first time it runs it copies all existing files.

  I haven't found a way to change the 5/20 predefined values.

  Here is the basic code:

  set nocount on

  declare @Cmd nvarchar(500)

  declare @FileName sysname

  declare @ServerName varchar(125)

  declare @SourceFolder varchar(255)

  declare @DestinationFolder varchar(255)

  declare @max-2 int

  declare @crnt int

  set @max-2 = 0

  set @crnt = 1

  select @SourceFolder = ControlValue from ManagmentDB..TblControl where ControlID = 'TraceSource'

  select @DestinationFolder = ControlValue from ManagmentDB..TblControl where ControlID = 'TraceDestination'

  select @Cmd = 'dir /B "' + @SourceFolder + '\Log_*.trc"'

  create table #Dir (

  SFilename Sysname null)

  create table #ToMove (

  Cntrl int identity (1,1),

  SFilename Sysname null)

  insert into #Dir exec

  master.dbo.xp_cmdshell @Cmd

  insert into #ToMove

  select * from #Dir where SFilename is not NULL

  and SFilename not in (select max(SFilename) from #Dir)

  and SFilename > (select rtrim(ControlValue) from ManagmentDB..TblControl where ControlID = 'TraceCopy')

  order by SFilename asc

  select @max-2 = (select max(Cntrl) from #ToMove)

  while @crnt <= @max-2

  Begin

  select @cmd = 'copy "' + @SourceFolder + '\'+SFilename+'" ' + @DestinationFolder + '\'+SFilename

  from #ToMove where Cntrl = @crnt

  --select @Cmd

  exec master.dbo.xp_cmdshell @Cmd

  set @crnt = @crnt + 1

  end

  --select * from #ToMove

  if @max-2 > 0

  begin

  select @Filename = max(SFilename) from #Dir

  update ManagmentDB..TblControl set ControlValue = @Filename where ControlID = 'DefaultTraceCopy'

  end

  drop table #Dir

  drop table #ToMove

  Cheers

  Leo

  Leo
  Nothing in life is ever so complicated that with a little work it can't be made more complicated.