SIZE OF ALL DATABASES IN SQL SERVER

THE ABOVE SCRIPT WILL GIVE THE SIZE OF ALL DATABASES INCLUDING THE SYSTEM AND USER DATABASE , BUT THE MIRRORING DATABASES WILL NOT BE REFLECTED IN THE LIST.THE SCRIPT GIVES THE TOTAL DATA UITLISATION TOTAL DATA LEFT AND TOTAL SPACE LEFT IN LOG,AND THE PERCENTAGE OF UTILISATION.TH

 
 
 
 
 
5 Star
(2)
5,061 times
Add to favorites
6/21/2016
E-mail Twitter del.icio.us Digg Facebook
  • not rel
    1 Posts | Last post July 20, 2018
    • https://gist.github.com/kriwil/b49da8a5bc14504f73fe
      
  • running on SQL Server 2000 produces error
    2 Posts | Last post March 20, 2017
    • I'm receiving the following on SQL Server 2000
      
      Server: Msg 197, Level 15, State 1, Line 36
      EXECUTE cannot be used as a source when inserting into a table variable.
    • hi david,
      
      sorry for the delay. please use the following query to get the database size information
      
      
      create table #spacetable 
      (
      database_name varchar(500) ,
      total_size_data int,
      space_util_data int,
      space_data_left int,
      percent_fill_data float,
      total_size_data_log int,
      space_util_log int,
      space_log_left int,
      percent_fill_log char(50),
      [total db size] int,
      [total size used] int,
      [total size left] int
      )
      insert into  #spacetable
      EXECUTE master.sys.sp_MSforeachdb 'USE [?];
      select x.[DATABASE NAME],x.[total size data],x.[space util],x.[total size data]-x.[space util] [space left data],
      x.[percent fill],y.[total size log],y.[space util],
      y.[total size log]-y.[space util] [space left log],y.[percent fill],
      y.[total size log]+x.[total size data] ''total db size''
      ,x.[space util]+y.[space util] ''total size used'',
      (y.[total size log]+x.[total size data])-(y.[space util]+x.[space util]) ''total size left''
       from (select DB_NAME() ''DATABASE NAME'',
      sum(size*8/1024) ''total size data'',sum(FILEPROPERTY(name,''SpaceUsed'')*8/1024) ''space util''
      ,case when sum(size*8/1024)=0 then ''less than 1% used'' else
      substring(cast((sum(FILEPROPERTY(name,''SpaceUsed''))*1.0*100/sum(size)) as CHAR(50)),1,6) end ''percent fill''
      from sys.master_files where database_id=DB_ID(DB_NAME())  and  type=0
      group by type_desc  ) as x ,
      (select 
      sum(size*8/1024) ''total size log'',sum(FILEPROPERTY(name,''SpaceUsed'')*8/1024) ''space util''
      ,case when sum(size*8/1024)=0 then ''less than 1% used'' else
      substring(cast((sum(FILEPROPERTY(name,''SpaceUsed''))*1.0*100/sum(size)) as CHAR(50)),1,6) end ''percent fill''
      from sys.master_files where database_id=DB_ID(DB_NAME())  and  type=1
      group by type_desc  )y'
      select * from #spacetable order by database_name;
      drop table #spacetable
      
  • Error running the script
    2 Posts | Last post June 21, 2016
    • When I run this script, I get this error: 
      
      Msg 8152, Level 16, State 2, Line 2
      String or binary data would be truncated.
      
      (0 row(s) affected)
      
      Can you help?
      
      Thanks! 
      
      David Tappan
    • Hi david,
      thank you for pointing out the error.
      when the error says "string or binary data would be truncated"  that means the 
      variable size is lesser then the size of the value passing to it.
      in this case i suppose your database name is greater than 50 characters, so i have declared
      the db  name length to varchar 500. try to download the script again and run the query and let me know if you still face the same issue.
      
      Regards,
      Pruthviraj K S