September 13, 2011 at 6:01 am
Hi,
I am trying to write some SQL which will show me the size of my database files, so that I can monitor growth over time.
I am getting the information from sys.database_files.
Ideally I would like to use sp_msforeachdb to do this, so that I do not have to amend the SQL every time a new database is created.
However, I do not know how to pass the database name back in the result set when I use msforeachdb.
Please see below:
EXECUTE sp_msforeachdb
'select getdate() as Date,
@@servername as Server,
-- I would like the database name here but do not know how to do this--
name as FileName,
type_desc as Type,
physical_name as PhysicalName,
size * 8 / 1024 as MB -- size is in 8KB pages
from [?].sys.database_files'
This works for all databases, but doesn't give me the database name in the result set.
Currently, if I want to get the database name I am using:
use MYDBName
go
select getdate() as Date,
@@servername as Server,
'MYDBName' as DBName,
name as 'File_Name',
type_desc as Type,
physical_name as 'Physical_Name',
size * 8 / 1024 as MB -- size is in 8KB pages
from sys.database_files
BUT this has to be coded for each database - not ideal.
Can I get the database name returned when I use msforeachdb?
September 13, 2011 at 6:12 am
Try this
EXECUTE sp_msforeachdb
'select getdate() as Date,
@@servername as Server,
''?'' as databaseName,
name as FileName,
type_desc as Type,
physical_name as PhysicalName,
size * 8 / 1024 as MB -- size is in 8KB pages
from [?].sys.database_files'
September 13, 2011 at 6:20 am
That was fantastic.
Thanks for the prompt reply.
September 13, 2011 at 7:00 am
If you want a replacement for sp_MSForEachDB, (it appears to have some issues) you can take a look here:
http://spaghettidba.wordpress.com/2011/09/09/a-better-sp_msforeachdb/
-- Gianluca Sartori
September 13, 2011 at 7:59 am
Hi,
Can I ask another question on this.
Now I have the data that I want, how can I get the rows inserted into a table that I have created, so that I can analyze growth in the future?
September 13, 2011 at 8:00 am
Gianluca Sartori (9/13/2011)
If you want a replacement for sp_MSForEachDB, (it appears to have some issues) you can take a look here:http://spaghettidba.wordpress.com/2011/09/09/a-better-sp_msforeachdb/
What issues?
September 13, 2011 at 8:01 am
barryFS (9/13/2011)
Hi,Can I ask another question on this.
Now I have the data that I want, how can I get the rows inserted into a table that I have created, so that I can analyze growth in the future?
Not the prettiest script, but you'll get the idea.
SET STATISTICS IO, TIME OFF
SET NOCOUNT ON
SET IMPLICIT_TRANSACTIONS ON
GO
USE [master]
GO
IF OBJECT_ID('dbo.spaceused', 'U') IS NULL
BEGIN
CREATE TABLE dbo.spaceused (
DbName sysname DEFAULT(''),
tblName sysname,
Row_count INT ,
Reserved VARCHAR(50),
data VARCHAR(50) ,
index_size VARCHAR(50),
unused VARCHAR(50),
PRIMARY KEY CLUSTERED (DbName, tblName)
);
END
ELSE
BEGIN
--DROP TABLE dbo.spaceused
TRUNCATE TABLE dbo.spaceused
END
COMMIT
GO
DECLARE @Cmd VARCHAR(8000)
SET @Cmd = 'USE [?];
IF ''?'' NOT IN (''tempdb''
--, ''master'', ''model'', ''msdb''
)
BEGIN
--PRINT ''?''
DECLARE @InnerCmd VARCHAR(8000)
SET @InnerCmd = ''
EXEC sp_spaceused '''''' + CHAR(63) + ''''''''
INSERT INTO master.dbo.spaceused(tblName, Row_count,Reserved,data,index_size,unused)
EXEC sp_MSforeachtable @InnerCmd
UPDATE master.dbo.spaceused SET DbName = ''?'' WHERE DbName = ''''
END
'
--PRINT @Cmd
EXEC sp_MSforeachdb @Cmd
DELETE FROM dbo.spaceused WHERE Row_count = 0
SELECT
DbName
, tblName
, Row_count
, CONVERT(BIGINT, REPLACE(Reserved, ' KB', '')) / 1024 AS MB_Reserved
, CONVERT(BIGINT, REPLACE(data, ' KB', '')) / 1024 AS MB_data
, CONVERT(BIGINT, REPLACE(index_size, ' KB', '')) / 1024 AS MB_index_size
, CONVERT(BIGINT, REPLACE(unused, ' KB', '')) / 1024 AS MB_unused
FROM
dbo.spaceused
ORDER BY
DbName
, MB_Reserved DESC
, Row_count DESC
COMMIT
September 13, 2011 at 8:09 am
This is Jeff's query and i've found it very useful. [works with 2k5 and above versions]
SELECT DB_NAME(database_id) AS DatabaseName,
CAST([Name] AS varchar(20)) AS NameofFile,
CAST(physical_name AS varchar(100)) AS PhysicalFile,
type_desc AS FileType,
((size * 8)/1024) AS FileSize,
MaxFileSize = CASE WHEN max_size = -1 OR max_size = 268435456 THEN 'UNLIMITED'
WHEN max_size = 0 THEN 'NO_GROWTH'
WHEN max_size <> -1 OR max_size <> 0 THEN CAST(((max_size * 8) / 1024) AS varchar(15))
ELSE 'Unknown'
END,
SpaceRemainingMB = CASE WHEN max_size = -1 OR max_size = 268435456 THEN 'UNLIMITED'
WHEN max_size <> -1 OR max_size = 268435456 THEN CAST((((max_size - size) * 8) / 1024) AS varchar(10))
ELSE 'Unknown'
END,
Growth = CASE WHEN growth = 0 THEN 'FIXED_SIZE'
WHEN growth > 0 THEN ((growth * 8)/1024)
ELSE 'Unknown'
END,
GrowthType = CASE WHEN is_percent_growth = 1 THEN 'PERCENTAGE'
WHEN is_percent_growth = 0 THEN 'MBs'
ELSE 'Unknown'
END
FROM master.sys.master_files
WHERE state = 0
AND type_desc IN ('LOG', 'ROWS')
ORDER BY database_id, file_id
September 13, 2011 at 8:15 am
Ninja's_RGR'us (9/13/2011)
Gianluca Sartori (9/13/2011)
If you want a replacement for sp_MSForEachDB, (it appears to have some issues) you can take a look here:http://spaghettidba.wordpress.com/2011/09/09/a-better-sp_msforeachdb/
What issues?
The ugly cursor in there can skip databases under heavy load. Read Aaron's blog here[/url] and here.
-- Gianluca Sartori
September 14, 2011 at 8:12 am
Hi,
Thanks for the replies, but I'm not sure the SQL does what I am after (although I am not an SQL expert).
I have created a new table with the following SQL:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DB_Size](
[Date] [datetime] NOT NULL,
[ServerName] [varchar](128) NOT NULL,
[DatabaseName] [varchar](256) NOT NULL,
[FileName] [varchar](256) NOT NULL,
[Type] [varchar](10) NOT NULL,
[PhysicalName] [varchar](256) NOT NULL,
[MB] [int] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
What I now want to do is to run my SQL (mentioned earlier) once a week, to append data to this new table.
In this way I will be able to query the table, over time, to check how the data / log files have grown.
So this will be an ever-growing table.
Does that make sense?
What SQL can I use that will enable me to append this data to my table every time it runs?
September 14, 2011 at 8:15 am
Do you take full backups of your important dbs?
Do you NOT clear the backup history table?
If so I have a script that does that for ya.
September 14, 2011 at 9:23 am
Yes I backup my DBs daily.
How does having a backup solve my problem?
I want to be able to run the SQL script, mentioned earlier, on a weekly basis, and for the data to be written to the table mentioned in my previous post.
I want to append the data to the table every time it runs.
September 14, 2011 at 9:26 am
barryFS (9/14/2011)
Yes I backup my DBs daily.How does having a backup solve my problem?
I want to be able to run the SQL script, mentioned earlier, on a weekly basis, and for the data to be written to the table mentioned in my previous post.
I want to append the data to the table every time it runs.
Don't have to. The data is already in the backup history table.
IF OBJECT_ID('tempdb..#dbs') > 0
DROP TABLE #dbs
IF OBJECT_ID('tempdb..#Drives') > 0
DROP TABLE #Drives
IF OBJECT_ID('tempdb..#Results') > 0
DROP TABLE #Results
CREATE TABLE #dbs
(
DBNAME sysname
, DBID INT
, [Total Size in MB] INT
, [Available Space In MB] INT
, DriveLetter CHAR(1)
)
INSERT INTO
#dbs
(
DBNAME
, DBID
, [Total Size in MB]
, [Available Space In MB]
, DriveLetter
)
EXEC sp_MSforeachdb '
USE [?];
SELECT
DB_NAME() As DBNAME
, DB_ID() AS DBID
, SUM(size / 128) AS ''Total Size in MB''
, SUM(size / 128 - CAST(FILEPROPERTY(name , ''SpaceUsed'') AS int) / 128) AS ''Available Space In MB''
, LEFT(physical_name, 1) AS DriveLetter
FROM
[?].sys.database_files
WHERE
type_desc = ''ROWS''
GROUP BY LEFT(physical_name, 1) '
CREATE TABLE #Drives
(
DriverLetter CHAR(1) PRIMARY KEY CLUSTERED
, FreeMBs INT NOT NULL
, FreeGBs AS CONVERT(DECIMAL(18 , 2) , FreeMBs / 1024.0)
)
INSERT INTO
#Drives ( DriverLetter , FreeMBs )
EXEC xp_fixeddrives
--
--SELECT
-- DB_NAME() As DBNAME
-- , DB_ID() AS DBID
-- , SUM(size / 128) AS 'Total Size in MB'
-- , SUM(size / 128 - CAST(FILEPROPERTY(name , 'SpaceUsed') AS int) / 128) AS 'Available Space In MB'
--FROM
-- sys.database_files
--WHERE
-- type_desc = 'ROWS'
--Rémi : I deleted 4 logging tables I had build on March 25th, hence the ±350 MB drop.
;
WITH CTE_Backups ( database_name, BackupDate, MinutesForBackup, GB_backup_size, seqFirst, seqLast )
AS (
SELECT
bs.database_name
, DATEADD(D , 0 , DATEDIFF(D , 0 , bs.backup_start_date)) AS BackupDate
, CONVERT(DECIMAL(18 , 1) , DATEDIFF(s , bs.backup_start_date ,
bs.backup_finish_date)
/ 60.0) AS MinutesForBackup
, CONVERT(DECIMAL(18 , 3) , bs.backup_size / 1024 / 1024 / 1024) AS GB_backup_size
, ROW_NUMBER() OVER ( PARTITION BY bs.database_name ORDER BY bs.backup_start_date ) AS seqFirst
, ROW_NUMBER() OVER ( PARTITION BY bs.database_name ORDER BY bs.backup_start_date DESC ) AS seqLast
FROM
msdb.dbo.backupset bs
WHERE
bs.[type] = 'D'
AND bs.is_snapshot = 0 --veem agent that backups the whole server
-- AND bs.database_name NOT LIKE '%ate%'
-- AND name IS NULL
)
SELECT
CONVERT(INT , dtBackups.[Available Space In GB]
/ CASE WHEN dtBackups.GB_ExpectedDailyGrowth <> 0
THEN dtBackups.GB_ExpectedDailyGrowth
ELSE 0.0001
END) AS DaysUntillDBGrowth
, *
-- INTO
-- #Results
FROM
(
SELECT
a.database_name
, dbs.DriveLetter
, drv.FreeGBs AS FreeGBs_Drive
, CONVERT(DECIMAL(18 , 3) , drv.FreeGBs * 0.85
/ NULLIF(b.GB_backup_size - a.GB_backup_size, 0)
/ NULLIF(DATEDIFF(dd , a.BackupDate , b.BackupDate), 0) * 30.468) AS FreeGBs_Drive_InMonths_WithExpected_DB_Growth
, a.BackupDate AS BackupDate_First
, b.BackupDate AS BackupDate_Last
, DATEDIFF(dd , a.BackupDate , b.BackupDate) AS DaysPeriod
, a.MinutesForBackup AS MinutesForBackup_First
, b.MinutesForBackup AS MinutesForBackup_Last
, b.MinutesForBackup - a.MinutesForBackup AS MinutesForBackup_Delta
, a.GB_backup_size AS GB_backup_size_First
, b.GB_backup_size AS GB_backup_size_Last
, b.GB_backup_size - a.GB_backup_size AS GB_BackupGrowth
--, a.seqLast - a.seqFirst AS QtyofBackups
, CONVERT(DECIMAL(18 , 3) , ( b.GB_backup_size - a.GB_backup_size )
/ NULLIF(DATEDIFF(dd , a.BackupDate , b.BackupDate), 0)) AS GB_ExpectedDailyGrowth
, CONVERT(DECIMAL(18 , 3) , ( b.GB_backup_size - a.GB_backup_size )
/ NULLIF(DATEDIFF(dd , a.BackupDate , b.BackupDate), 0) * 365.256) AS GB_ExpectedAnnualGrowth
, CONVERT(DECIMAL(18 , 3) , dbs.[Total Size in MB] / 1024.0) AS [Total Size in GB]
, CONVERT(DECIMAL(18 , 3) , dbs.[Available Space In MB] / 1024.0) AS [Available Space In GB]
FROM
CTE_Backups a
INNER JOIN CTE_Backups b
ON a.seqFirst = b.seqLast
AND a.seqLast = b.seqFirst
AND a.database_name = b.database_name
INNER JOIN #dbs dbs
ON b.database_name = dbs.DBNAME
LEFT OUTER JOIN #Drives drv
ON dbs.DriveLetter = drv.DriverLetter
WHERE
a.seqFirst = 1
AND a.seqFirst + a.seqLast > 2 --would always warn on the first day
) dtBackups
ORDER BY
database_name
--IF EXISTS ( SELECT
-- *
-- FROM
-- #Results R
-- WHERE
-- R.FreeGBs_Drive_InMonths_WithExpected_DB_Growth >= 0 AND R.FreeGBs_Drive_InMonths_WithExpected_DB_Growth < 1
-- OR R.DaysUntillDBGrowth >= 0 AND R.DaysUntillDBGrowth <= 30 )
-- BEGIN
-- INSERT INTO
-- dbo.RPT_Space_Warnings
-- (
-- [DaysUntillDBGrowth]
-- , [Warning_Description]
-- , [database_name]
-- , [DriveLetter]
-- , [FreeGBs_Drive]
-- , [FreeGBs_Drive_InMonths_WithExpected_DB_Growth]
-- , [BackupDate_First]
-- , [BackupDate_Last]
-- , [DaysPeriod]
-- , [MinutesForBackup_First]
-- , [MinutesForBackup_Last]
-- , [MinutesForBackup_Delta]
-- , [GB_backup_size_First]
-- , [GB_backup_size_Last]
-- , [GB_BackupGrowth]
-- , [GB_ExpectedDailyGrowth]
-- , [GB_ExpectedAnnualGrowth]
-- , [Total Size in GB]
-- , [Available Space In GB]
-- )
-- SELECT
-- [DaysUntillDBGrowth]
-- , CASE WHEN R.FreeGBs_Drive_InMonths_WithExpected_DB_Growth < 1 THEN 'HD IS FULL' ELSE 'AUTOGROWTH WARNING' END AS Warning_Description
-- , [database_name]
-- , [DriveLetter]
-- , [FreeGBs_Drive]
-- , [FreeGBs_Drive_InMonths_WithExpected_DB_Growth]
-- , [BackupDate_First]
-- , [BackupDate_Last]
-- , [DaysPeriod]
-- , [MinutesForBackup_First]
-- , [MinutesForBackup_Last]
-- , [MinutesForBackup_Delta]
-- , [GB_backup_size_First]
-- , [GB_backup_size_Last]
-- , [GB_BackupGrowth]
-- , [GB_ExpectedDailyGrowth]
-- , [GB_ExpectedAnnualGrowth]
-- , [Total Size in GB]
-- , [Available Space In GB]
-- FROM
-- #Results R
-- WHERE
-- R.DaysUntillDBGrowth >= 0 AND R.DaysUntillDBGrowth <= 30
--UNION ALL -- I want to see 2 warnings in the same day when it's the case... those are the really critical events.
-- SELECT
-- [DaysUntillDBGrowth]
-- , CASE WHEN R.FreeGBs_Drive_InMonths_WithExpected_DB_Growth < 1 THEN 'HD IS FULL_' ELSE 'AUTOGROWTH WARNING_' END AS Warning_Description
-- , [database_name]
-- , [DriveLetter]
-- , [FreeGBs_Drive]
-- , [FreeGBs_Drive_InMonths_WithExpected_DB_Growth]
-- , [BackupDate_First]
-- , [BackupDate_Last]
-- , [DaysPeriod]
-- , [MinutesForBackup_First]
-- , [MinutesForBackup_Last]
-- , [MinutesForBackup_Delta]
-- , [GB_backup_size_First]
-- , [GB_backup_size_Last]
-- , [GB_BackupGrowth]
-- , [GB_ExpectedDailyGrowth]
-- , [GB_ExpectedAnnualGrowth]
-- , [Total Size in GB]
-- , [Available Space In GB]
-- FROM
-- #Results R
-- WHERE
-- R.FreeGBs_Drive_InMonths_WithExpected_DB_Growth >= 0 AND R.FreeGBs_Drive_InMonths_WithExpected_DB_Growth <= 1
-- END
--IF OBJECT_ID('tempdb..#dbs') > 0
-- DROP TABLE #dbs
--
--IF OBJECT_ID('tempdb..#Drives') > 0
-- DROP TABLE #Drives
--
--IF OBJECT_ID('tempdb..#Results') > 0
-- DROP TABLE #Results
September 15, 2011 at 3:55 am
By taking part of Ninja's_RGR'us code I have managed to get the data inserted into my table - thanks.
Everything works fine when the destination table is on the same server as where the query executes.
However, I am now hitting another issue, and I don't know which forum I should put this on, as it isn't a T-SQL error.
My aim is to have only one copy of the DB_Sizes table on one server (let's call it server A).
What I want to do is to create a weekly SQL Agent job on all my servers, which will write the data for that particular server into my DB_Sizes table on server A (hence the reason I return the servername in my select).
Server A has been added as a linked server on the server where the query is running (Server B), but I get the following message when I run the query on server B:
OLE DB provider "SQLNCLI" for linked server "server A" returned message "The partner transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2, Line 1
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "server A" was unable to begin a distributed transaction.
Any ideas?
September 15, 2011 at 5:14 am
As I said earlier there's no point in saving that history. It's readily available in the backup tables. That's why I can use the whole script to see when I'm going to see the next growth even and when I'll be running out of space on the drives.
The longer the history is the better the predictions will be (assuming no major change in the system).
Anywho, in the linked server properties, check both rpc options. And in the surface area connection, make sure to enable remote connections.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply