check db size data file and log file

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 2005 Forums

Transact-SQL (2005)

check db size data file and log file

Author

Topic

avipenina
Starting Member

44 Posts

Posted - 2008-04-27 : 15:58:57

Hi,

i'm trying to write this script that check my database file and log size(in MB) and insert them into a table.i need the following columns
dbid,dbname,compatability_level,recovery_model,db_size_in_MB,log_size_in_MB.
i try to write this a got stuck.
select sysdb.database_id,sysdb.name,sysdb.compatibility_level,
sysdb.recovery_model_desc,sysmaster.size from sys.databases sysdb,sys.master_files sysmaster
where sysdb.database_id = sysmaster.database_id

can anyone help me with this script?
THX

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-27 : 17:17:15

What about this one?

select distinct
sysdb.database_id,sysmaster.name,sysdb.compatibility_level,
sysdb.recovery_model_desc,sysmaster.size from sys.databases sysdb,sys.master_files sysmaster
where sysdb.database_id = sysmaster.database_id

avipenina
Starting Member

44 Posts

Posted - 2008-04-28 : 01:18:04

two things i need in this script.
one - the db size and log size for every DB will be in the same row.
two - the db size and log size will be in MB and not in KB

THX

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2008-04-28 : 05:27:26

play around with sys.sysaltfiles and join it with sys.databases to get the other properties

to get them in one row, use subquery for the log file (add any other data files to get the total data file size)

--------------------
keeping it simple...

avipenina
Starting Member

44 Posts

Posted - 2008-04-28 : 05:36:34

the sys.sysaltfiles is the same as sys.master_files
the size of the db and log are not in MB.
and can you help me with the join subquery?
I'm newbie in sql

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2008-04-28 : 06:01:49

select dbid
,d.name
,d.compatibility_level
,d.recovery_model_desc
,convert(decimal(18,2),(sum(size)*8)/1024.0) as db_size_in_mb
,(select (size*8)/1024.0 from sys.sysaltfiles where dbid=saf.dbid and groupid=0) as log_size_in_mb
from sys.sysaltfiles saf
join sys.databases d on saf.dbid=d.database_id
where groupid>0
group by dbid,d.name,d.compatibility_level,d.recovery_model_desc

--------------------
keeping it simple...

avipenina
Starting Member

44 Posts

Posted - 2008-04-28 : 06:53:08

THX.
the script looks good but 2 questions
1.in the log_size_in_mb results i get this result for example 500.000000 can the results be only 2 digit after the DOT?
2.how can i run the same script it SQL 2000.

THX

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2008-04-28 : 07:14:41

1. just do a convert to decimal(18,2)
2. i don't have 2000 anymore, just find out the equivalent views in sql2000 (you did post in 2005 forum

)

quote:
Originally posted by avipenina

THX.
the script looks good but 2 questions
1.in the log_size_in_mb results i get this result for example 500.000000 can the results be only 2 digit after the DOT?
2.how can i run the same script it SQL 2000.

THX

--------------------
keeping it simple...

avipenina
Starting Member

44 Posts

Posted - 2008-04-28 : 08:21:26

can you help me with the convert plz?

THX

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-28 : 17:48:04

add this in jen script.

select dbid
,d.name
,d.compatibility_level
,d.recovery_model_desc
,convert(decimal(18,2),(sum(size)*8)/1024.0) as db_size_in_mb
,(select convert(decimal(18,2),(size*8)/1024.0) from sys.sysaltfiles where dbid=saf.dbid and groupid=0) as log_size_in_mb
from sys.sysaltfiles saf
join sys.databases d on saf.dbid=d.database_id
where groupid>0
group by dbid,d.name,d.compatibility_level,d.recovery_model_desc

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-04-28 : 18:17:11

You should look at this script. It returns a number of detailed analysis and works with versions 7, 2000, and 2005.

Get Server Database File Information
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89058

CODO ERGO SUM

avipenina
Starting Member

44 Posts

Posted - 2008-04-29 : 01:43:20

quote:
Originally posted by sodeep

add this in jen script.

select dbid
,d.name
,d.compatibility_level
,d.recovery_model_desc
,convert(decimal(18,2),(sum(size)*8)/1024.0) as db_size_in_mb
,(select convert(decimal(18,2),(size*8)/1024.0) from sys.sysaltfiles where dbid=saf.dbid and groupid=0) as log_size_in_mb
from sys.sysaltfiles saf
join sys.databases d on saf.dbid=d.database_id
where groupid>0
group by dbid,d.name,d.compatibility_level,d.recovery_model_desc

THX

sudha_sql
Starting Member

2 Posts

Posted - 2013-03-25 : 09:48:24

how do the sql server knows this datafile and Log file. Before , Im new to SQL DBA. Please let me know the difference and What is datafile and Log file. And also about how SQL server recognises Full, Differential, and Transaction log backup

sudha_sql
Starting Member

2 Posts

Posted - 2013-03-25 : 09:50:56

how do the sql server knows this datafile and Log file. Before , Im new to SQL DBA. Please let me know the difference and What is datafile and Log file.
And also about how SQL server recognises Full, Differential, and Transaction log backup..

Thanks in Advance
sudha

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources