Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Script Expand / Collapse
Author
Message
Posted Thursday, November 8, 2007 10:43 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 9:54 AM
Points: 448, Visits: 405
Hi All,

I have around 80 databases in my one of sql server instance.
we hve to regularly send the database wise health report to the client.
The following fields mentioned by the client in excel sheet

Name of database
Logical file name of .MDF
.Mdf File location drive
Size of the file in MB
Physical file location(full path of .mdf file)
Maxsize
Growth

Now a days we have to check databases one by one and its taking very much time.
Could anyone provide me the script which automatically fetch all of required fields information from all of the databases.

Urgent help will be appreciable.

Austin

Post #420195
Posted Thursday, November 8, 2007 11:10 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:53 PM
Points: 36,795, Visits: 31,257
Do you have a script to do this one at a time?

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #420208
Posted Thursday, November 8, 2007 11:17 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 9:54 AM
Points: 448, Visits: 405
No I don't have script that is the reason I am looking for somebuddy who can help me.
Post #420210
Posted Thursday, November 8, 2007 12:12 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, November 15, 2013 4:46 PM
Points: 69, Visits: 185
-- this will get u started --

SET NOCOUNT ON

Create Table #t (
db varchar(255),
filename varchar(255),
name varchar(255),
[size in MB] int,
maxsize int,
growth int
)

Declare @db varchar(255)
Declare c Cursor
read_only
for
select name from sysdatabases where dbid > 4

Open c
fetch next from c into @db
while @@fetch_status = 0
begin
Exec ('INSERT #t
select ''' + @db + ''', filename, name, (size * 8)/1024, maxsize, growth
from ' + @db + '..sysfiles
where fileid = 1'
)
fetch next from c into @db
end

Close c
Deallocate c
Select * from #t

Drop table #t
edit math error...
Post #420228
Posted Thursday, November 8, 2007 12:15 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, November 15, 2013 4:46 PM
Points: 69, Visits: 185
u will need to modify if multiple data files. also note that maxsize and growth are in 8kb increments, so need to multiply by 128 to get mb. but, the values may be -1 for maxsize meaning unlimited and/or 0 for growth meaning no growth. u can add logic for those values if need.

oh yeah, run script in master db
Post #420229
Posted Thursday, November 8, 2007 3:49 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 9:54 AM
Points: 448, Visits: 405
Thanks for the script..
I used the script successfully which u have posted with little bit of editing coz' I am using SQL 2005 environment.

Thanks a lot...

Regards,
Austin
Post #420307
Posted Thursday, November 8, 2007 6:31 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, June 10, 2009 7:59 PM
Points: 284, Visits: 92
This script is very useful.
Thanks for that I can use this too for my server.
Post #420334
Posted Thursday, November 8, 2007 6:38 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, June 10, 2009 7:59 PM
Points: 284, Visits: 92
btw -can we capture the log file too?
where do we add it on the script?

Thanks,
Susan
Post #420337
Posted Thursday, November 8, 2007 10:18 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:53 PM
Points: 36,795, Visits: 31,257
edit math error...


Ummm... what's that mean?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #420358
Posted Thursday, November 8, 2007 10:24 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, June 10, 2009 7:59 PM
Points: 284, Visits: 92
I mean the above script will produce.

Name of database
Logical file name of .MDF
.Mdf File location drive
Size of the file in MB
Physical file location(full path of .mdf file)
Maxsize
Growth

and I want to add

Logical file name of .LDF
.LDF File location drive
Size of the file in MB
Physical file location(full path of .ldf file)


:)
Post #420360
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse