Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250

    Unanswered: Enterprise manager|space available

    Hello All,

    I would like to know how SQL SErver 2000 Enterprise manager determines the "space available" for a database. In one of my databases, if the Database properties from SQL enterprise manager shows space available as 0.0MB,

    what is the difference between this and the o/p of sp_spaceused?


    Thanks in advance
    Cheers....

    baburajv

  2. #2
    Join Date
    Jun 2005
    Posts
    319
    are you pregrowing your databases?

    I have a job that runs every 15 minutes that calls this stored procedure to determine the amount of free space in a few of our DB's (all pre-grown):

    Code:
    USE [VINDICOSUITE_DATAWAREHOUSE_OPERATIONAL]
    GO
    
    /****** Object:  StoredProcedure [dbo].[db_size_isp]    Script Date: 12/22/2010 10:13:06 ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    create proc [dbo].[db_size_isp]
    as
    begin
    set nocount on  
    
    declare @id int   -- The object id that takes up space  
      ,@type character(2) -- The object type.  
      ,@pages bigint   -- Working variable for size calc.  
      ,@dbname sysname  
      ,@dbsize bigint  
      ,@logsize bigint  
      ,@reservedpages  bigint  
      ,@usedpages  bigint  
      ,@rowCount bigint  
      
      
    /*  
    **  If @id is null, then we want summary data.  
    */  
     select @dbsize = sum(convert(bigint,case when status & 64 = 0 then size else 0 end))  
      , @logsize = sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))  
      from dbo.sysfiles  
      
     select @reservedpages = sum(a.total_pages),  
      @usedpages = sum(a.used_pages),  
      @pages = sum(  
        CASE  
         -- XML-Index and FT-Index-Docid is not considered "data", but is part of "index_size"  
         When it.internal_type IN (202,204) Then 0  
         When a.type <> 1 Then a.used_pages  
         When p.index_id < 2 Then a.data_pages  
         Else 0  
        END  
       )  
     from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id  
      left join sys.internal_tables it on p.object_id = it.object_id  
      
     /* unallocated space could not be negative */  
     insert db_size(
     database_name 
    ,database_size 
    ,unallocated_space 
     )
     select   
      database_name = db_name(),  
      database_size_in_MB = 
      convert (dec (15,2),(
    	  (
    		convert (dec (15,2),@dbsize) 
    	  + convert (dec (15,2),@logsize)
    	  )   
    	  * 8192 / 1048576
      )),  
      unallocated_space_in_MB = 
      convert (dec (15,2),(
    	case 
    		when @dbsize >= @reservedpages 
    		then (
    				convert (dec (15,2),@dbsize) 
    			  - convert (dec (15,2),@reservedpages)
    			  )   
    			  * 8192 / 1048576 
    		else 0 end
    	)
    	)
    end
    
    GO
    Then I have another job that sends out an email with a report of the last 30 days so we know when we are getting low on space.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •