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.
>>
>

Database Design where some products are T-shirts with different sizes

Hello,

I'm wondering what would be the best approach to designing a database that will have different products one of them being T-Shirts of different sizes... for example 1 t-shirt design might only have 2 available sizes while another may have 4. I'm kinda stumped on how to approach this cuz there is multiple products like CD's, DVD's, Magazines etc which is pretty straight forward, but the T-shirts have this "variable" to it.

What i'm really wondering is should i have 1 main "Products" Table or should i have a separate table for the t-shirts?
Should there be a column for each available size?

Currently my database has a "products table" that has foreign keys to "Product Type", "Artists", "Genre"

The database is basically for a record company

If anyone has designed a database similar to this i'd love any insight or even possibly to see a database diagram

Thanks

Here is my suggestion:

Table: Products
Id - PK
Name
Description
Price

Table: Attributes
Id - PK
Name
Label

Table: AttributeValues
Id - PK
AttributeId - FK
Value

Table: ProductAttributes
Id - PK
ProductId - FK
AttributeId - FK

In your order line, you will have to store the ProductID And then in a child table store the zero to many associated ProductAttributeId's. Here is an example

Table: Products
Id - 1
Name - Men's ABC Polo
Description - Nice Shirt
Price - 20.00
Id - 2
Name - Women's ABC Polo
Description - Nice Shirt
Price - 25.00

Table: Attributes
Id - 100
Name - Men's Sizes
Label - Size
Id - 101
Name - Women's Sizes
Label - Size

Table: AttributeValues
Id - 201
AttributeId - 101
Value - Small
Id - 202
AttributeId - 101
Value - Medium
Id - 203
AttributeId - 101
Value - Large
Id - 204
AttributeId - 101
Value - Extar Large
Id - 205
AttributeId - 102
Value - Extra Small
Id - 206
AttributeId - 102
Value - Small
Id - 207
AttributeId - 102
Value - Medium

Table: ProductAttributes
Id - 300
ProductId - 1
AttributeId - 101
Id - 301
ProductId - 2
AttributeId - 102

The attrubutes are reuseable accross multiple products and the use of attributes is optional. When you take the order you would store the product id of 1 for a Man's polo with the attribute value in another table relating to the line item. You put this in another table so you can have potentially multiple attribute values.

Make sense? Just one way of many... this was off the top of my head and it is late.