Backup device size info

• jgebri

  Grasshopper

  Points: 11

  More actions

  January 21, 2003 at 12:49 am

  #58231

  Here is a challenge… for me anyway

  I am currently creating a capacity management stats database for all of our production databases…utilising performance monitor and DTS scripts to populate my stats DB with the correct information.

  I ‘nearly’ have all the info that I need BUT.. I am unable to get the size of my database backup device (it’s a dump file).

  -I have looked at sysfiles, sysdevices tables with no luck (the backup device size is zero in sysdevices) .. yes the file exists… on average it’s around 11Gb in size.

  -I have tried xp_getfiledetails, it shows me all the correct information, accept file size value is a minus value …..?? for a 11.221Gb file shows me ‘-1394380290’, the file is definitely not 13.99Gb size.. even if you ignore the minus value .

  -I do have full admin access on local file storage.

  -This happens on SQL7/NT4 and SQL2000/W2k.

  Any ideas..

  Jozsef 😉

• Rodrigo Acosta

  SSC-Insane

  Points: 21261

  More actions

  January 21, 2003 at 7:36 am

  #446846

  I've never looked for the size in sys tables, so if you say that it doesn't exists I guess you are right. I think that you can execute:

  Create table tmp_Size

  (Size Varchar(8000))

  Go

  Insert tmp_Size

  EXEC xp_cmdshell 'Dir path\Device_File.bak',

  and then extract the size. It's not the best I imagine, but I did it once to know the free disk size.