February 26, 2014 at 12:56 am
Hi,
Just entered a new role in my company as junior DBA.
I am looking for assistance or guidance with regards to a script to see the weekly growth of all our databases in the company for reporting purposes.
At this stage I am looking at about +- 20 databases and we need to establish a trend for the growth in the company.
Currently I am using the following query below but would like a more detailed query so that I can get a graph going in Excel. Also this query is only for one database at a time and I would like to execute one query for all the databases.
SELECT
[database_name] AS "Database",
DATEPART(month,[backup_start_date]) AS "Month",
AVG([backup_size]/1024/1024) AS "Backup Size MB",
AVG([compressed_backup_size]/1024/1024) AS "Compressed Backup Size MB",
AVG([backup_size]/[compressed_backup_size]) AS "Compression Ratio"
FROM msdb.dbo.backupset
WHERE [database_name] = N'XXX'
AND [type] = 'D'
GROUP BY [database_name],DATEPART(mm,[backup_start_date]);
Kind Regards,
February 26, 2014 at 1:47 am
Hi,
My first step would be to set up a Central Management Server. There is a great article on it here:
http://www.brentozar.com/archive/2008/08/sql-server-2008s-new-central-management-server/
Then I would execute sp_spaceused against your group of servers, see this forum post for more..
Ta
David
February 26, 2014 at 7:52 am
You don't need to set up a central management server if you're the only DBA and you are only hitting one server.
You can run something like this,
CREATE TABLE #TEMP1 (
DATABASE_NAME VARCHAR(500)
, LOGICAL_NAME VARCHAR(500)
, PHYSICAL_NAME VARCHAR(500)
, FILE_SIZE INT
)
DECLARE @name VARCHAR(500)
DECLARE database_cursor CURSOR
FOR
SELECT NAME
FROM sysdatabases
ORDER BY dbid ASC
OPEN database_cursor;
FETCH NEXT
FROM database_cursor
INTO @name;
WHILE @@fetch_status = 0
BEGIN
INSERT INTO #TEMP1 (
DATABASE_NAME
, LOGICAL_NAME
, PHYSICAL_NAME
, FILE_SIZE
)
SELECT DB_NAME(database_id)
, NAME
, Physical_Name
, (size * 8) / 1024
FROM sys.master_files
WHERE DB_NAME(database_id) = @name
FETCH NEXT
FROM DATABASE_CURSOR
INTO @name;
END
CLOSE DATABASE_cursor;
DEALLOCATE DATABASE_cursor;
GO
This will return db and log file sizes, you can modify it to only show db sizes if you want and you can also look at sys.master_files to get more data like max db size, growth amount, etc. If you need more data on top of that, you can simply join to the statements I provided to get more data. You can also mix this up a bit, put the data into a permanent table for you to review in the future, etc. If you put this into a permanent table, make sure to add a datetime variable so you know when this script was run. You can also do the same thing with while loops instead of a fetch if you'd like. Also, make sure to run this against the master DB.
February 26, 2014 at 9:51 pm
pietero 48156 (2/26/2014)
Hi,Just entered a new role in my company as junior DBA.
I am looking for assistance or guidance with regards to a script to see the weekly growth of all our databases in the company for reporting purposes.
At this stage I am looking at about +- 20 databases and we need to establish a trend for the growth in the company.
Currently I am using the following query below but would like a more detailed query so that I can get a graph going in Excel. Also this query is only for one database at a time and I would like to execute one query for all the databases.
SELECT
[database_name] AS "Database",
DATEPART(month,[backup_start_date]) AS "Month",
AVG([backup_size]/1024/1024) AS "Backup Size MB",
AVG([compressed_backup_size]/1024/1024) AS "Compressed Backup Size MB",
AVG([backup_size]/[compressed_backup_size]) AS "Compression Ratio"
FROM msdb.dbo.backupset
WHERE [database_name] = N'XXX'
AND [type] = 'D'
GROUP BY [database_name],DATEPART(mm,[backup_start_date]);
Kind Regards,
So take the database Name out of the WHERE clause, format the month as YYYY_MM, and maybe throw in a ROLLUP or CUBE on the GROUP BY.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 26, 2014 at 11:56 pm
JoshDBGuy (2/26/2014)
You don't need to set up a central management server if you're the only DBA and you are only hitting one server.You can run something like this,
CREATE TABLE #TEMP1 (
DATABASE_NAME VARCHAR(500)
, LOGICAL_NAME VARCHAR(500)
, PHYSICAL_NAME VARCHAR(500)
, FILE_SIZE INT
)
DECLARE @name VARCHAR(500)
DECLARE database_cursor CURSOR
FOR
SELECT NAME
FROM sysdatabases
ORDER BY dbid ASC
OPEN database_cursor;
FETCH NEXT
FROM database_cursor
INTO @name;
WHILE @@fetch_status = 0
BEGIN
INSERT INTO #TEMP1 (
DATABASE_NAME
, LOGICAL_NAME
, PHYSICAL_NAME
, FILE_SIZE
)
SELECT DB_NAME(database_id)
, NAME
, Physical_Name
, (size * 8) / 1024
FROM sys.master_files
WHERE DB_NAME(database_id) = @name
FETCH NEXT
FROM DATABASE_CURSOR
INTO @name;
END
CLOSE DATABASE_cursor;
DEALLOCATE DATABASE_cursor;
GO
This will return db and log file sizes, you can modify it to only show db sizes if you want and you can also look at sys.master_files to get more data like max db size, growth amount, etc. If you need more data on top of that, you can simply join to the statements I provided to get more data. You can also mix this up a bit, put the data into a permanent table for you to review in the future, etc. If you put this into a permanent table, make sure to add a datetime variable so you know when this script was run. You can also do the same thing with while loops instead of a fetch if you'd like. Also, make sure to run this against the master DB.
You could just run this
SELECT DB_NAME(database_id)
, NAME
, Physical_Name
, (size * 8) / 1024
FROM sys.master_files
instead of looping through the databases.
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
February 26, 2014 at 11:59 pm
Here is an alternative script that could help with your trending report
DECLARE @SQLVer SQL_VARIANT
,@DBName VARCHAR(128)
,@NumDaysSMALLINT
,@SQLvarchar(1024)
,@WhereClauseVARCHAR(256)
SET @DBName = 'TFCCS_Transfers'
;
SET @NumDays = 90
;
SET @SQLVer = CONVERT(INTEGER, PARSENAME(CONVERT(VARCHAR(20),SERVERPROPERTY('ProductVersion')),4));
SET @WhereClause = 'WHERE a.type IN (''D'',''I'')
And a.backup_start_date > GETDATE()- ' + Cast(@NumDays as varchar)+''
IF @DBName IS NOT NULL
Begin
SET @WhereClause = @WhereClause + '
AND a.database_name = '''+ @DBName +''''
END
Set @sql = '
SELECT a.database_name,a.backup_start_date,a.backup_finish_date
,datediff(minute,a.backup_start_date,a.backup_finish_date) as Duration
,b.physical_device_name AS BackupPath
,a.position
,a.type
,a.backup_size/1024/1024 AS BackupSizeMB
,' + CASE
WHEN @SQLVer < 10
THEN '0'
ELSE 'a.compressed_backup_size/1024/1024'
END + ' AS CompressedBackMB
FROM msdb.dbo.backupset a
INNER JOIN msdb.dbo.backupmediafamily b
ON a.media_set_id = b.media_set_id
' + @WhereClause + '
ORDER BY a.database_name,a.backup_start_date;'
--PRINT @sql
Execute (@SQL);
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply