February 25, 2009 at 2:45 pm
Is there some tables where the space usage info is been saved?
I am trying to come up with a report showing trends in disk space usage, file growth. The reports available with SQL 2005 SP2 dose provide this kind of data. Does anyone know where and how are these reports fetching the data. The reports I need to generate will need data regarding:
1) Capacity of the logical disk on server.
2) Free space available on each of the logical drives on the server.
3) Size and location of all the file groups on all DB's housed on this server.
I appreciate your help.
February 25, 2009 at 3:36 pm
Can you do it if we provide you with t-sql scripts to fetch the same info?
MJ
February 25, 2009 at 4:13 pm
For free space, try xp_fixeddrives
Use SYSALTFILES for location and size of databases..
--Sudhie.
February 25, 2009 at 10:23 pm
I can create a t-sql for this myself. That is not what I am looking for. From the reports that are generated in SQL Server 2005 SP 2, I think that this data is been stored some where as it provides historical information as well. Even I need to maintain historical info for the usage trends. SO I thought instead of having to run the t-sql and storing it in some table, I can use the data if already stored in some sys table by sql. Does anyone know of any table where it stored this kind of data? Or, does anyone know how are these reports been generated with historical information in it?
February 26, 2009 at 3:00 am
U can find out space in sql server:
---- sp_spaceused
----xp_fixeddrives
July 23, 2010 at 12:38 pm
I couldn't find a sys table that held this data historically so here is my TSQL stab at it. This query will pull the disk usage for all databases on a specific server and insert it into a permanent table. It will include a datadate for each time it runs so you can do historical trending. If you know of a better way of doing this please let me know.
Thanks,
Mike
DECLARE cChangeDatabase CURSOR FOR
SELECT Name
FROM master.dbo.sysdatabases
WHERE dbid > 4
IF OBJECT_ID('tempdb..#DiskUtilization','U') IS NOT NULL
DROP TABLE #DiskUtilization
CREATE TABLE #DiskUtilization (
TableName VARCHAR(255) NOT NULL
,Rows VARCHAR(255) NOT NULL
,Reserved VARCHAR(255) NOT NULL
,Data VARCHAR(255) NOT NULL
,IndexSize VARCHAR(255) NOT NULL
,Unused VARCHAR(255) NOT NULL
)
OPEN cChangeDatabase
DECLARE @SqlStr NVARCHAR(MAX)
,@DbName NVARCHAR(255)
FETCH NEXT FROM cChangeDatabase INTO @DbName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SqlStr =
'USE ' + @DbName + '
DECLARE cCalcDiskSpace CURSOR FOR
SELECT TableName = '''''''' + LTRIM(SCHEMA_NAME(so.schema_id)) + ''.'' + LTRIM(so.Name) + ''''''''
FROM sys.objects so
WHERE so.type = ''U'' AND
SUBSTRING(so.name,1,CHARINDEX(''.'',so.name)) <> ''' + @DbName + '''
--GROUP BY '''''''' + LTRIM(SCHEMA_NAME(so.schema_id)) + ''.'' + LTRIM(so.Name) + ''''''''
OPEN cCalcDiskSpace
DECLARE @SqlString NVARCHAR(MAX)
,@TableName NVARCHAR(255)
FETCH NEXT FROM cCalcDiskSpace INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SqlString = ''USE ' + @dbName + ' INSERT INTO #DiskUtilization EXECUTE sp_spaceused '' + @TableName
EXEC sp_executesql @SqlString
FETCH NEXT FROM cCalcDiskSpace INTO @TableName
END
CLOSE cCalcDiskSpace
DEALLOCATE cCalcDiskSpace'
--SELECT @sqlStr
EXEC sp_executesql @SqlStr
SET @SqlStr =
'USE ' + @DbName + '
INSERT INTO PermanentDiskUtilizationTable (
DatabaseName
,SchemaName
,TableName
,Rows
,Reserved
,Data
,IndexSize
,Unused
,DataDate
)
SELECT DatabaseName = db_name()
,SchemaName = SCHEMA_NAME(so.schema_id)
,dt.*
,GETDATE()
FROM #DiskUtilization dt
JOIN sys.objects so ON
dt.TableName = so.name and
so.type = ''U''
WHERE dt.TableName <> ''dtproperties''
TRUNCATE TABLE #DiskUtilization'
EXEC sp_executesql @SqlStr
FETCH NEXT FROM cChangeDatabase INTO @DbName
END
CLOSE cChangeDatabase
DEALLOCATE cChangeDatabase
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply