T sql help (database size track)

Question

Post reply

T sql help (database size track)

Tac11

SSCertifiable

Points: 6980
More actions
September 2, 2015 at 8:04 am

#302705

Hello gurus,
I am trying to capture 'AdventureWorks' Database size every week. I have created a 'DBSizeTrack' database and a table 'Track'
When I run following script I 29 rows with the same values. My question is how to get a single row? Or is there a best way?
Create database DBSizeTrack
go
USE DBSizeTrack
Go
CREATE TABLE [dbo].[Track](
[RunDate] [date] NULL,
[Dbname] [sysname] NOT NULL,
[file_Size_MB] [decimal](30, 2) NULL
) ON [PRIMARY]
GO
Use DBSizeTrack
go
Insert into Track (RunDate,Dbname,file_Size_MB)
exec sp_msforeachdb
'use [AdventureWorks];
select
getdate() as RunDate,
DB_NAME() AS DbName,
sum(size)/128.0 AS File_Size_MB
from sys.database_files'

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Y.B. SSChampion Points: 11649 More actions · Answer 1

That's because you execute it once for every DB but always tell it to use 'AdventureWorks'

Try this instead

EXEC sp_MSforeachdb

'USE [?];

SELECT

GETDATE() as RunDate,

DB_NAME() AS DbName,

SUM(size)/128.0 AS File_Size_MB

FROM sys.database_files'

That being said you only mention the one DB, do you want just AdventureWorks? If so why are you using the MSforeachdb procedure?

SELECT quote FROM brain WHERE original = 1
0 rows returned

Tac11 SSCertifiable Points: 6980 More actions · Answer 2

Thanks yb751. Now I get sizes of all databases, this also helps. Now, Is there a way to get only 'Adventureworks' database size (instead of inserting all databases in the table)? Lastly, is there a way to void 'sp_MSforeachdb'?

Thanks again.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Insert into Track (RunDate,Dbname,file_Size_MB)

select

getdate() as RunDate,

DB_NAME() AS DbName,

sum(size)/128.0 AS File_Size_MB

from AdventureWorks.sys.database_files

John

SQLBill SSC Guru Points: 51440 More actions · Answer 4

exec sp_msforeachdb is used to run one or more commands on all databases (For Each DB)

Just run this:

Insert into Track (RunDate,Dbname,file_Size_MB)

select

getdate() as RunDate,

DB_NAME() AS DbName,

sum(size)/128.0 AS File_Size_MB

from AdventureWorks.sys.database_files

-SQLBill

Y.B. SSChampion Points: 11649 More actions · Answer 5

If you just want AdventureWorks just simply change your code to...

Use DBSizeTrack

INSERT INTO Track (RunDate,Dbname,file_Size_MB)

SELECT

GETDATE() as RunDate,

DB_NAME() AS DbName,

SUM(size)/128.0 AS File_Size_MB

FROM AdventureWorks2012.sys.database_files

SELECT quote FROM brain WHERE original = 1
0 rows returned

Tac11 SSCertifiable Points: 6980 More actions · Answer 6

Tac11

SSCertifiable

Points: 6980

September 2, 2015 at 11:04 am

#1824085

Thanks guys.