July 22, 2011 at 9:00 am
I need to be able to determine how much space is remaining on SQL server 2005 box and find out how
to calculate how much space does it take for 10 million rows?
I used sp_Spaceused but it seems like it is specific to database.
Thanks for your help.
July 22, 2011 at 9:18 am
Here's how to get all tables for all dbs. This will include rowcount. From there on out it's just a rule of 3 away...
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
This is the script I use to estimate growth over time. It works with msdb's backup history so you need to [not, stop] clearing it.
The growth estimates are good but there's a bug if you have more than 1 datafile per db (It won't split the growth between them, but that's easy to fix in excel).
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
LEFT OUTER 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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply