Database size question

• sqldba-294117

  Hall of Fame

  Points: 3486

  More actions

  July 12, 2007 at 8:09 am

  #179616

  I have 50-55 databases with different size & there file growth are been restricted due to certain reasons. i am looking for any help that can help me monitor this restricted file growth before it runs out of space. i want to set alerts that will check if database is approaching close to that size before it stop working. any help is appreciated. thank you.

• Strommy

  SSCrazy

  Points: 2386

  More actions

  July 12, 2007 at 8:37 am

  #718991

  Here is some fun code I use for this kind of thing.  It uses the undocumented command "dbcc showfilestats", which works in 2000 and 2005.  Here is the script with a table you can query.  I'll leave it to you to figure out what to do with it.

  Thanks,

  Eric

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

  declare

  @dataspace table

  ( FileID int,

  FileGrp

  int,

  TotExt

  int,

  UsdExt

  int,

  LFileNm

  varchar( 100),

  PFileNm

  varchar( 100)

  )

  insert

  into @dataspace

  exec

  ( 'dbcc showfilestats')

  select

  *, (UsdExt * 64 ) / 1024.0 as UsedMB

  from

  @dataspace

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

• Sugesh Kumar

  One Orange Chip

  Points: 27311

  More actions

  July 12, 2007 at 11:46 pm

  #719176

  You can also use alerts to notify you when the space usage exceeds your set limitations.

  Cheers,
  Sugeshkumar Rajendran
  SQL Server MVP
  http://sugeshkr.blogspot.com