Database File size monitor

What are folks using to monitor database file sizes? I have been tasked
with writing a script to monitor our db's (Yes I know there are growth
controls, etc...). I was hoping there maybe a way to keep track of this via
some widget dashboard, etc... I have Solarwinds and will look at importing
SAN mibs, but still would like to hear thoughts from others.
--
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.Take a look at sp_helpdb. It'll show you how the db sizes are calculated.
Instead of just displaying the values, store then in a table and then you'll
be able to see how fast you db is growing each day, month, minute, hour...or
whatever.
--
MG
"Paul Bergson [MVP-DS]" wrote:
> What are folks using to monitor database file sizes? I have been tasked
> with writing a script to monitor our db's (Yes I know there are growth
> controls, etc...). I was hoping there maybe a way to keep track of this via
> some widget dashboard, etc... I have Solarwinds and will look at importing
> SAN mibs, but still would like to hear thoughts from others.
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> Please no e-mails, any questions should be posted in the NewsGroup
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>|||DECLARE @.DB sysname
DECLARE @.SQL nvarchar(255)
if exists ( select * from tempdb..sysobjects where name LIKE
'#FileStats__%' ) drop table #FileStats
CREATE TABLE #FileStats(
[FileId] INT,
[FileGroup] INT,
[TotalExtents] INT,
[UsedExtents] INT,
[Name] sysname,
[Filename] varchar(255)
)
DECLARE @.FileStats TABLE (
[FileId] INT,
[FileGroup] INT,
[TotalExtents] INT,
[UsedExtents] INT,
[Name] sysname,
[Filename] varchar(255)
)
DECLARE cDatabases CURSOR FOR
SELECT QUOTENAME(sdb.name)
FROM master.dbo.sysdatabases sdb
WHERE status & 32 != 32
AND status & 64 != 64
AND status & 128 != 128
AND status & 256 != 256
AND status & 512 != 512
AND status & 1024 != 1024
AND status & 4096 != 4096
AND status & 32768 !=32768
OPEN cDatabases
FETCH FROM cDatabases INTO @.DB
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
DELETE FROM #FileStats
SET @.SQL = 'USE ' + @.DB + '; INSERT INTO #FileStats EXEC (''DBCC
SHOWFILESTATS'')'
EXEC (@.SQL)
UPDATE #FileStats SET name = @.DB
INSERT INTO @.FileStats SELECT * FROM #FileStats
FETCH FROM cDatabases INTO @.DB
END
CLOSE cDatabases
DEALLOCATE cDatabases
SELECT
[Name]
,[TotalExtents]*64/1024. AS TotalExtInMB
,[UsedExtents]*64/1024. AS UsedExtInMB
,([TotalExtents] - [UsedExtents]) / 16. AS UnAllocExtInMB --* 64 /
1024. AS UnAllocExtInMB
,CAST(FLOOR(ROUND([UsedExtents] * 100. / [TotalExtents], 0)) AS
VARCHAR(3)) + '%' AS Pct_Full
FROM @.FileStats
ORDER BY TotalExtInMB DESC
--exec sp_spaceused
DBCC sqlperf(logspace)
"Paul Bergson [MVP-DS]" <pbergson@.allete_nospam.com> wrote in message
news:uxH3fPY8HHA.2004@.TK2MSFTNGP06.phx.gbl...
> What are folks using to monitor database file sizes? I have been tasked
> with writing a script to monitor our db's (Yes I know there are growth
> controls, etc...). I was hoping there maybe a way to keep track of this
> via some widget dashboard, etc... I have Solarwinds and will look at
> importing SAN mibs, but still would like to hear thoughts from others.
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> Please no e-mails, any questions should be posted in the NewsGroup
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||I use a custom script based around aggregating data from DBCC SHOWFILESTATS.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Paul Bergson [MVP-DS]" <pbergson@.allete_nospam.com> wrote in message
news:uxH3fPY8HHA.2004@.TK2MSFTNGP06.phx.gbl...
> What are folks using to monitor database file sizes? I have been tasked
> with writing a script to monitor our db's (Yes I know there are growth
> controls, etc...). I was hoping there maybe a way to keep track of this
> via some widget dashboard, etc... I have Solarwinds and will look at
> importing SAN mibs, but still would like to hear thoughts from others.
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> Please no e-mails, any questions should be posted in the NewsGroup
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Thanks for your feedback, it is appreciated.
--
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hurme" <michael.geles@.thomson.com> wrote in message
news:F8CA1619-B919-45B9-9C76-75DF14160A66@.microsoft.com...
> Take a look at sp_helpdb. It'll show you how the db sizes are calculated.
> Instead of just displaying the values, store then in a table and then
> you'll
> be able to see how fast you db is growing each day, month, minute,
> hour...or
> whatever.
> --
> MG
>
> "Paul Bergson [MVP-DS]" wrote:
>> What are folks using to monitor database file sizes? I have been tasked
>> with writing a script to monitor our db's (Yes I know there are growth
>> controls, etc...). I was hoping there maybe a way to keep track of this
>> via
>> some widget dashboard, etc... I have Solarwinds and will look at
>> importing
>> SAN mibs, but still would like to hear thoughts from others.
>> --
>> Paul Bergson
>> MVP - Directory Services
>> MCT, MCSE, MCSA, Security+, BS CSci
>> 2003, 2000 (Early Achiever), NT
>> http://www.pbbergs.com
>> Please no e-mails, any questions should be posted in the NewsGroup
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>|||Thanks for your feedback, it is appreciated.
--
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jay" <nospam@.nospam.org> wrote in message
news:uUlhoBZ8HHA.5012@.TK2MSFTNGP02.phx.gbl...
> DECLARE @.DB sysname
> DECLARE @.SQL nvarchar(255)
> if exists ( select * from tempdb..sysobjects where name LIKE
> '#FileStats__%' ) drop table #FileStats
> CREATE TABLE #FileStats(
> [FileId] INT,
> [FileGroup] INT,
> [TotalExtents] INT,
> [UsedExtents] INT,
> [Name] sysname,
> [Filename] varchar(255)
> )
> DECLARE @.FileStats TABLE (
> [FileId] INT,
> [FileGroup] INT,
> [TotalExtents] INT,
> [UsedExtents] INT,
> [Name] sysname,
> [Filename] varchar(255)
> )
> DECLARE cDatabases CURSOR FOR
> SELECT QUOTENAME(sdb.name)
> FROM master.dbo.sysdatabases sdb
> WHERE status & 32 != 32
> AND status & 64 != 64
> AND status & 128 != 128
> AND status & 256 != 256
> AND status & 512 != 512
> AND status & 1024 != 1024
> AND status & 4096 != 4096
> AND status & 32768 !=32768
> OPEN cDatabases
> FETCH FROM cDatabases INTO @.DB
> WHILE (@.@.FETCH_STATUS = 0)
> BEGIN
> DELETE FROM #FileStats
> SET @.SQL = 'USE ' + @.DB + '; INSERT INTO #FileStats EXEC (''DBCC
> SHOWFILESTATS'')'
> EXEC (@.SQL)
> UPDATE #FileStats SET name = @.DB
> INSERT INTO @.FileStats SELECT * FROM #FileStats
> FETCH FROM cDatabases INTO @.DB
> END
> CLOSE cDatabases
> DEALLOCATE cDatabases
> SELECT
> [Name]
> ,[TotalExtents]*64/1024. AS TotalExtInMB
> ,[UsedExtents]*64/1024. AS UsedExtInMB
> ,([TotalExtents] - [UsedExtents]) / 16. AS UnAllocExtInMB --* 64 /
> 1024. AS UnAllocExtInMB
> ,CAST(FLOOR(ROUND([UsedExtents] * 100. / [TotalExtents], 0)) AS
> VARCHAR(3)) + '%' AS Pct_Full
> FROM @.FileStats
> ORDER BY TotalExtInMB DESC
> --exec sp_spaceused
> DBCC sqlperf(logspace)
> "Paul Bergson [MVP-DS]" <pbergson@.allete_nospam.com> wrote in message
> news:uxH3fPY8HHA.2004@.TK2MSFTNGP06.phx.gbl...
>> What are folks using to monitor database file sizes? I have been tasked
>> with writing a script to monitor our db's (Yes I know there are growth
>> controls, etc...). I was hoping there maybe a way to keep track of this
>> via some widget dashboard, etc... I have Solarwinds and will look at
>> importing SAN mibs, but still would like to hear thoughts from others.
>> --
>> Paul Bergson
>> MVP - Directory Services
>> MCT, MCSE, MCSA, Security+, BS CSci
>> 2003, 2000 (Early Achiever), NT
>> http://www.pbbergs.com
>> Please no e-mails, any questions should be posted in the NewsGroup
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>|||Thanks for your feedback, it is appreciated.
--
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:ON2b3EZ8HHA.5752@.TK2MSFTNGP04.phx.gbl...
>I use a custom script based around aggregating data from DBCC
>SHOWFILESTATS.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
> "Paul Bergson [MVP-DS]" <pbergson@.allete_nospam.com> wrote in message
> news:uxH3fPY8HHA.2004@.TK2MSFTNGP06.phx.gbl...
>> What are folks using to monitor database file sizes? I have been tasked
>> with writing a script to monitor our db's (Yes I know there are growth
>> controls, etc...). I was hoping there maybe a way to keep track of this
>> via some widget dashboard, etc... I have Solarwinds and will look at
>> importing SAN mibs, but still would like to hear thoughts from others.
>> --
>> Paul Bergson
>> MVP - Directory Services
>> MCT, MCSE, MCSA, Security+, BS CSci
>> 2003, 2000 (Early Achiever), NT
>> http://www.pbbergs.com
>> Please no e-mails, any questions should be posted in the NewsGroup
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>