SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 check db size data file and log file
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

avipenina
Starting Member

44 Posts

Posted - 04/27/2008 :  15:58:57  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 04/27/2008 :  17:17:15  Show Profile  Reply with Quote
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
Go to Top of Page

avipenina
Starting Member

44 Posts

Posted - 04/28/2008 :  01:18:04  Show Profile  Reply with Quote
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
Go to Top of Page

jen
Flowing Fount of Yak Knowledge

Sweden
4110 Posts

Posted - 04/28/2008 :  05:27:26  Show Profile  Send jen a Yahoo! Message  Reply with Quote
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...
Go to Top of Page

avipenina
Starting Member

44 Posts

Posted - 04/28/2008 :  05:36:34  Show Profile  Reply with Quote
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
Go to Top of Page

jen
Flowing Fount of Yak Knowledge

Sweden
4110 Posts

Posted - 04/28/2008 :  06:01:49  Show Profile  Send jen a Yahoo! Message  Reply with Quote
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...

Edited by - jen on 04/28/2008 06:03:53
Go to Top of Page

avipenina
Starting Member

44 Posts

Posted - 04/28/2008 :  06:53:08  Show Profile  Reply with Quote
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
Go to Top of Page

jen
Flowing Fount of Yak Knowledge

Sweden
4110 Posts

Posted - 04/28/2008 :  07:14:41  Show Profile  Send jen a Yahoo! Message  Reply with Quote
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...
Go to Top of Page

avipenina
Starting Member

44 Posts

Posted - 04/28/2008 :  08:21:26  Show Profile  Reply with Quote
can you help me with the convert plz?

THX
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 04/28/2008 :  17:48:04  Show Profile  Reply with Quote
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
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 04/28/2008 :  18:17:11  Show Profile  Reply with Quote
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

Edited by - Michael Valentine Jones on 04/28/2008 21:40:37
Go to Top of Page

avipenina
Starting Member

44 Posts

Posted - 04/29/2008 :  01:43:20  Show Profile  Reply with Quote
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
Go to Top of Page

sudha_sql
Starting Member

2 Posts

Posted - 03/25/2013 :  09:48:24  Show Profile  Reply with Quote
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
Go to Top of Page

sudha_sql
Starting Member

2 Posts

Posted - 03/25/2013 :  09:50:56  Show Profile  Reply with Quote
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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000