March 30, 2010 at 1:27 pm
Hi, I was asked to give the statistics of the disk usage of all the databases in our production servers for the last 3 months. Is there anyway that we could get this for the past 3 months? Is there anything like dmv that gives these details??
Thank you
March 30, 2010 at 2:02 pm
You can query the backup table in MSDB to get the backup size. The size of the backup will show how much space was being used in the data file, but not necessarily the actual size of the database on disk.
At least this will show you what your estimated growth rates are - and that should give you an idea of how much disk space will be needed over the next 3 months.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 30, 2010 at 2:33 pm
ok..This sounds good, atleast I will be able to estimate the size. Thanks a lot
March 30, 2010 at 2:43 pm
Try this script. I found it on this forum.
--see growth from backup size in pages.
DECLARE @dbname sysname
SET @dbname = DB_NAME()
SELECT CONVERT(char, backup_start_date, 111) AS [Date], --yyyy/mm/dd format
CONVERT(char, backup_start_date, 108) AS [Time],
@dbname AS [Database Name], [filegroup_name] AS [Filegroup Name],
logical_name AS [Logical Filename],
physical_name AS [Physical Filename],
CONVERT(numeric(9,2),(backed_up_page_count * 8192)/1048576) AS [File Size (MB)],
Growth AS [Growth Percentage (%)]
FROM
(
SELECT b.backup_start_date,
a.backup_set_id,
(a.backed_up_page_count),
a.logical_name,
a.[filegroup_name],
a.physical_name,
(
SELECT CONVERT(numeric(5,2),
(((a.backed_up_page_count*8192) * 100.00) / (i1.backed_up_page_count*8192))-100)
FROM msdb.dbo.backupfile i1
WHERE i1.backup_set_id =
(
SELECT MAX(i2.backup_set_id)
FROM msdb.dbo.backupfile i2 JOIN msdb.dbo.backupset i3
ON i2.backup_set_id = i3.backup_set_id
WHERE i2.backup_set_id < a.backup_set_id AND
i2.file_type='D' AND
i3.database_name = @dbname AND
i2.logical_name = a.logical_name AND
i2.logical_name = i1.logical_name AND
i3.type = 'D'
) AND
i1.file_type = 'D'
) AS Growth
FROM msdb.dbo.backupfile a JOIN msdb.dbo.backupset b
ON a.backup_set_id = b.backup_set_id
WHERE b.database_name = @dbname AND
a.file_type = 'D' AND
b.type = 'D'
) as Derived
WHERE (Growth <> 0.0) OR (Growth IS NULL)
ORDER BY logical_name, [Date]
HTH!
Manu
March 30, 2010 at 3:12 pm
For the historical trend (and since it is for the past), Jeff's suggestion is the most feasible if you have nothing else in place currently.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 30, 2010 at 3:55 pm
I had to do something similar a while back as part of an audit review, and I ended up writing a VB.Net application to monitor disk space usage (as opposed to database size) across a number of servers, and then a scheduled SQL job that ran every hour that just pulled in the current database size (broken down by file), as I find that trying to use the backup history isn't accurate enough..
March 30, 2010 at 7:19 pm
liteswitch (3/30/2010)
I had to do something similar a while back as part of an audit review, and I ended up writing a VB.Net application to monitor disk space usage (as opposed to database size) across a number of servers, and then a scheduled SQL job that ran every hour that just pulled in the current database size (broken down by file), as I find that trying to use the backup history isn't accurate enough..
The problem with this approach is that means you are relying on the autogrow feature to grow your databases. There are all kinds of issues with doing that - not to mention how expensive the autogrow is.
I am not sure what you mean by how accurate it needs to be. For trending purposes it doesn't have to be exact - which is why you monitor this in the first place. You need to know before you run out of disk space how much space you are going to order/add to the system. By trending, you'll know well in advance that your trend is going to use up your available space in XX time - so you will order it before hand.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 31, 2010 at 3:20 am
Did you try the script that I posted above?
Manu
March 31, 2010 at 7:46 am
If you have nothing in place currently, then backup size history is what you can go on to estimate space used... but this might not give you free disk space accurately depending on how much other stuff is on the disk.
You can put a process in place to track this info going forward. I do it by creating a table, then scheduling a SQL Agent job to call a stored proc to periodically write to that table... here's what I use:
/** CREATE TABLE- run this once**/
USE [DXDBA]
CREATE TABLE [dbo].[Capacity_DiskSpaceTracking](
[ID] [int] IDENTITY(1,1) NOT NULL,
[TimeCollected] [smalldatetime] NOT NULL CONSTRAINT [Capacity_DiskSpaceTracking_TimeCollected] DEFAULT (getdate()),
DriveLetter CHAR(1)
, TotalSpace bigint
, FreeSpace bigint
, Label varchar(10)
/** CREATE PROC- schedule a job to execute this proc periodically**/
CREATE PROC [CAPACITY_RECORD_DISK_SPACE]
AS
BEGIN
SET NOCOUNT ON
IF EXISTS (SELECT name FROM tempdb..sysobjects WHERE name = '##_DriveSpace')
DROP TABLE ##_DriveSpace
IF EXISTS (SELECT name FROM tempdb..sysobjects WHERE name = '##_DriveInfo')
DROP TABLE ##_DriveInfo
DECLARE @Result INT
, @objFSO INT
, @Drv INT
, @cDrive VARCHAR(13)
, @Size VARCHAR(50)
, @Free VARCHAR(50)
, @Label varchar(10)
CREATE TABLE ##_DriveSpace
(
DriveLetter CHAR(1) not null
, FreeSpace VARCHAR(10) not null
)
CREATE TABLE ##_DriveInfo
(
DriveLetter CHAR(1)
, TotalSpace bigint
, FreeSpace bigint
, Label varchar(10)
)
INSERT INTO ##_DriveSpace
EXEC master.dbo.xp_fixeddrives
-- Iterate through drive letters.
DECLARE curDriveLetters CURSOR
FOR SELECT driveletter FROM ##_DriveSpace
DECLARE @DriveLetter char(1)
OPEN curDriveLetters
FETCH NEXT FROM curDriveLetters INTO @DriveLetter
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
SET @cDrive = 'GetDrive("' + @DriveLetter + '")'
EXEC @Result = sp_OACreate 'Scripting.FileSystemObject', @objFSO OUTPUT
IF @Result = 0
EXEC @Result = sp_OAMethod @objFSO, @cDrive, @Drv OUTPUT
IF @Result = 0
EXEC @Result = sp_OAGetProperty @Drv,'TotalSize', @Size OUTPUT
IF @Result = 0
EXEC @Result = sp_OAGetProperty @Drv,'FreeSpace', @Free OUTPUT
IF @Result = 0
EXEC @Result = sp_OAGetProperty @Drv,'VolumeName', @Label OUTPUT
IF @Result <> 0
EXEC sp_OADestroy @Drv
EXEC sp_OADestroy @objFSO
SET @Size = (CONVERT(BIGINT,@Size) / 1048576 )
SET @Free = (CONVERT(BIGINT,@Free) / 1048576 )
INSERT INTO ##_DriveInfo
VALUES (@DriveLetter, @Size, @Free, @Label)
END
FETCH NEXT FROM curDriveLetters INTO @DriveLetter
END
CLOSE curDriveLetters
DEALLOCATE curDriveLetters
INSERT INTO Capacity_DiskSpaceTracking
(DriveLetter, Label, TotalSpace, FreeSpace)
SELECT DriveLetter, Label, TotalSpace, FreeSpace FROM ##_DriveInfo
/* Clean up. Drop the temp table */
DROP TABLE ##_DriveSpace
DROP TABLE ##_DriveInfo
END
) ON [PRIMARY]
I also have a step in the job which cleans up data in the disk space tracking table.. basically:
delete from Capacity_DiskSpaceTracking where 90 <DATEDIFF(day, timecollected, GETDATE())
March 31, 2010 at 8:20 am
ssismaddy (3/30/2010)
Hi, I was asked to give the statistics of the disk usage of all the databases in our production servers for the last 3 months. Is there anyway that we could get this for the past 3 months? Is there anything like dmv that gives these details??Thank you
I would go with Jeffrey's post. I have done this and there are tons of different queries out there already written for you. It is a quick copy/paste into Excel and get you a nice looking graph for the management to look at.
But take note you must ensure you are not cleaning out your backup history, or someone else isn't. You can only go back as far as what MSDB has stored. I am not aware of any DMVs that would have this type of information.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
March 31, 2010 at 8:37 am
Thank you very much for all your add ons. Manu, I used your script , it is excellent. But I could get information of only 1 month, as We delete the history in MSDB. So, I just gave them the statistics for the past month based on the backup history. I m going to schedule a job with the scripts here, so I can trace it for ever.
Thank you
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply