Monitoring Database Space Usage

–Space stats for each logical file in a db in a readable format

SELECT
[name]
,CONVERT(NUMERIC(10,2),ROUND([size]/128.,2)) AS [Size]
,CONVERT(NUMERIC(10,2),ROUND(FILEPROPERTY([name],‘SpaceUsed’)/128.,2)) AS [Used]
,CONVERT(NUMERIC(10,2),ROUND(([size]FILEPROPERTY([name],‘SpaceUsed’))/128.,2)) AS [Unused]
,UPPER(SUBSTRING(physical_name, 1, 3)) AS [Drive]
,UPPER([physical_name]) AS [Physical Location]
FROM [sys].[database_files]

–objects in different filegroups

select tablename = object_name(object_id),Data_located_on_filegroup = d.name
from sys.data_spaces d
join sys.indexes i on d.data_space_id = i.data_space_id
where i.index_id < 2

 
–Full Space report for each table in a database – similar to sp_spaceused but more detail

SELECT SCHEMA_NAME(tbl.schema_id) as [Schema]
, tbl.Name
, Coalesce((Select pr.name
        From sys.database_principals pr
        Where pr.principal_id = tbl.principal_id)
    , SCHEMA_NAME(tbl.schema_id)) as [Owner]
, tbl.max_column_id_used as [Columns]
, CAST(CASE idx.index_id WHEN 1 THEN 1 ELSE 0 END AS bit) AS [HasClusIdx]
, Coalesce( (Select sum (spart.rows) from sys.partitions spart
    Where spart.object_id = tbl.object_id and spart.index_id < 2), 0) AS [RowCount]
, Coalesce( (Select Cast(v.low/1024.0 as float) 
    * SUM(a.used_pages CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END) 
        FROM sys.indexes as i
         JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id
         JOIN sys.allocation_units as a ON a.container_id = p.partition_id
        Where i.object_id = tbl.object_id  )
    , 0.0) AS [IndexKB]
, Coalesce( (Select Cast(v.low/1024.0 as float)
    * SUM(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END) 
        FROM sys.indexes as i
         JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id
         JOIN sys.allocation_units as a ON a.container_id = p.partition_id
        Where i.object_id = tbl.object_id)
    , 0.0) AS [DataKB]
, tbl.create_date, tbl.modify_date
 FROM sys.tables AS tbl
  INNER JOIN sys.indexes AS idx ON (idx.object_id = tbl.object_id and idx.index_id < 2)
  INNER JOIN master.dbo.spt_values v ON (v.number=1 and v.type=‘E’)

–sp_spaceused

CREATE TABLE #sp_spaceused_all
(name nvarchar(255),
rows nvarchar(255),
reserved nvarchar(255),
data nvarchar(255),
index_size nvarchar(255),
unused nvarchar(255))
EXEC sp_msforeachtable ‘insert into #sp_spaceused_all exec sp_spaceused ”?”’
SELECT * FROM #sp_spaceused_all ORDER BY name
DROP TABLE #sp_spaceused_all

 

Advertisements

SQL Server 2008 Evaluation Edition Expired – After upgrade to licensed version!!

Once you install evaluation edition of SQL Server 2008 and then perform in place upgrade to full version, you may get below error message when you open SQL Server Management Studio.

Expired

For better search, below is the text of the message

Microsoft SQL Server Management Studio:
Evaluation period has expired. For information on how to upgrade your evaluation software please go to http://www.microsoft.com/sql/howtobuy

Have a close look at error… This is for Management Studio. This means that this is the problem with management studio only. If its not yet expired, inside of Management Studio, going to Help > About… still shows this the expiration countdown.

This is identified as a known issue where SSMS is not upgraded when you do edition upgrade for SQL Server 2008. To workaround the problem you can hack registry keys. Set CommonFiles registry value to 3 in HKLM\SOFTWARE\\Microsoft\Microsoft SQL Server\100\ConfigurationState Registry Hive. You will then need to reinstall (if the tools are not installed currently) or run edition Upgrade. Reference: connect bug

This is already fixed in SP1.

KB article published for same issue. http://support.microsoft.com/kb/971268

Thanks to Balmukund..