Intelligent delay between indexes rebuilt

• Indianrock

  SSC-Insane

  Points: 20333

  More actions

  November 23, 2010 at 2:26 pm

  #147730

  I want to add a block of code that checks log file fullness before going on to the next index. Instead of having a Use Database command in the middle of the stored procedure I had hoped to stay in the master database context but explicitly pull from my database's sysfiles table/view.

  The script below will give an accurate percentage of log file fullness if I change to mydatabase, but not if I stay in master and specify mydatabase.

  from mydatabase.dbo.sysfiles where isnull(filegroup_name(groupid),'log')='log'

  [/url]

  use mydatabase

  go

  declare @logpercentFree int

  select @logpercentFree=

  cast(100*(size/128-(FILEPROPERTY(name,'spaceused')/128))/(size/128.0)

  as int)

  from sysfiles where isnull(filegroup_name(groupid),'log')='log'

  select @logpercentfree

• Bhuvnesh

  SSC Guru

  Points: 59351

  More actions

  November 24, 2010 at 1:00 am

  #1254918

  Sample sql

  USE TEMPDB

  GO

  select * from msdb.dbo.sysfiles

  select * from master.dbo.sysfiles

  -------Bhuvnesh----------
  I work only to learn Sql Server...though my company pays me for getting their stuff done;-)