List of Data and TLog size - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

List of Data and TLog size

Author

Topic

dewacorp.alliances

452 Posts

Posted - 2007-08-15 : 01:12:47

I've ran the following tsql below
select
[FileSizeMB] =
convert(numeric(10,2),round(a.size/128.,2)),
[UsedSpaceMB] =
convert(numeric(10,2),round(fileproperty( a.name,'SpaceUsed')/128.,2)) ,
[UnusedSpaceMB] =
convert(numeric(10,2),round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2)) ,
[DBFileName] = a.name
from
sysfiles a

How do I run in such away so I can get list of database (sizes) on the server?

Thanks

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-08-15 : 22:51:46

Query master..sysaltfiles instead.

cwalston
Starting Member

25 Posts

Posted - 2007-08-16 : 15:31:19

Strange. When I run it from sysaltfiles, I get null values for Used/Unused for all DBs except master.

Any idea why?

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-16 : 15:54:48

did you get list of all databases. i get only the database under which the query is run

Ashley Rhodes

cwalston
Starting Member

25 Posts

Posted - 2007-08-16 : 17:08:05

I got a list of all the existing db's but with null values for used/unused...

cwalston
Starting Member

25 Posts

Posted - 2007-08-16 : 17:08:33

...except master

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-08-16 : 23:38:38

Do you have sysadmin rights?

cwalston
Starting Member

25 Posts

Posted - 2007-08-17 : 00:46:45

Yep!

dewacorp.alliances

452 Posts

Posted - 2007-08-17 : 01:58:57

Thank you and here's the updated script:

select
[FileSizeMB] =
convert(numeric(10,2),round(a.size/128.,2)),
[UsedSpaceMB] =
convert(numeric(10,2),round(fileproperty( a.name,'SpaceUsed')/128.,2)) ,
[UnusedSpaceMB] =
convert(numeric(10,2),round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2)) ,
[DBFileName] = a.name
from master..sysaltfiles a

dewacorp.alliances

452 Posts

Posted - 2007-08-17 : 02:01:49

I got the same too! This's interesting. It's only 1 database that return the value.

EDIT: This is related on fileproperty function and by looking at this it seems that it only knows the property of the current database that is running under.

cwalston
Starting Member

25 Posts

Posted - 2007-08-22 : 13:26:12

I got it!! Well...here is a way to do it...

First create a proc in master with the query we were discussing with the change of only returning the info from a passed DB name:

Create proc sp_DBFile_Info @DB varchar(50)

as

select
[FileSizeMB] =
convert(numeric(10,2),round(a.size/128.,2)),
[UsedSpaceMB] =
convert(numeric(10,2),round(fileproperty( a.name,'SpaceUsed')/128.,2)) ,
[UnusedSpaceMB] =
convert(numeric(10,2),round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2)) ,
[DBFileName] = a.name
from master..sysaltfiles a, master..sysdatabases d
where a.dbid = d.dbid
and d.name = @DB

Then run a cursor down the sysdatabases table grabbing the db name and using the sp_executesql command:

declare @DB varchar(30),
@sql nvarchar(200)

declare DBName cursor for
select name from master..sysdatabases

open DBName
fetch next from DBName into @DB

while @@fetch_status = 0
begin

select @sql = N'use ' + @DB + ' exec sp_DBFile_Info ' + @DB
exec sp_executesql @sql

fetch next from DBName into @DB

end

close DBName
deallocate DBName

Let me know what you think!

Cheers!!
Chris

dewacorp.alliances

452 Posts

Posted - 2007-08-22 : 21:24:22

I rename the store procedure name and I got the error when I run this.

Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'dbo.usp_DBFileInfo'.
Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'dbo.usp_DBFileInfo'.
Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'dbo.usp_DBFileInfo'.
Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'dbo.usp_DBFileInfo'.
Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'dbo.usp_DBFileInfo'.

(2 row(s) affected)

Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'dbo.usp_DBFileInfo'.
Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'dbo.usp_DBFileInfo'.
Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'dbo.usp_DBFileInfo'.

quote:
Originally posted by cwalston

I got it!! Well...here is a way to do it...

First create a proc in master with the query we were discussing with the change of only returning the info from a passed DB name:

Create proc sp_DBFile_Info @DB varchar(50)

as

select
[FileSizeMB] =
convert(numeric(10,2),round(a.size/128.,2)),
[UsedSpaceMB] =
convert(numeric(10,2),round(fileproperty( a.name,'SpaceUsed')/128.,2)) ,
[UnusedSpaceMB] =
convert(numeric(10,2),round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2)) ,
[DBFileName] = a.name
from master..sysaltfiles a, master..sysdatabases d
where a.dbid = d.dbid
and d.name = @DB

Then run a cursor down the sysdatabases table grabbing the db name and using the sp_executesql command:

declare @DB varchar(30),
@sql nvarchar(200)

declare DBName cursor for
select name from master..sysdatabases

open DBName
fetch next from DBName into @DB

while @@fetch_status = 0
begin

select @sql = N'use ' + @DB + ' exec sp_DBFile_Info ' + @DB
exec sp_executesql @sql

fetch next from DBName into @DB

end

close DBName
deallocate DBName

Let me know what you think!

Cheers!!
Chris

dewacorp.alliances

452 Posts

Posted - 2007-08-22 : 21:29:58

Never mind is working now. :)

Excellent script.

cwalston
Starting Member

25 Posts

Posted - 2007-08-22 : 21:43:48

That's funny! I did the same thing!!

I had either forgotten or never known that a proc in master had to be named sp_<yada> in order to be called from another DB!

IMHO that is very poor coding on Microsoft's part.

I wonder if that is legacy from the Sybase days???

Anyway, glad it worked for you!!

Cheers!
Chris

dewacorp.alliances

452 Posts

Posted - 2007-08-22 : 22:00:52

I've tested this script and I found that some of FileSizeMB is not correct as a result of that UnusedSpaceMB is turning to negative sign.

Any ideas?

cwalston
Starting Member

25 Posts

Posted - 2007-08-22 : 22:54:43

hmmm...is there an absolute value function??? I will check tomorrow. If not...it shouldn't be too hard to code up.

mverma82
Starting Member

5 Posts

Posted - 2007-08-27 : 11:15:30

Try using undocument command
DBCC SQLPERF

http://www.sqldev.net/articles/dbcc_sqlperf.htm

Manish

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-08-27 : 22:01:44

That only gives you log files info.

YogeshDesai
Posting Yak Master

136 Posts

Posted - 2007-09-07 : 10:46:02

hey thanks guys for useful links,but still I am not able to get the expected results

Yogesh V. Desai. | SQLDBA|

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources