Script

Question

Post reply

Script

Austin_123

SSCommitted

Points: 1648
More actions
November 8, 2007 at 10:43 am

#153946

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

Viewing 15 posts - 1 through 15 (of 22 total)

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

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 1

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Austin_123 SSCommitted Points: 1648 More actions · Answer 2

No I don't have script that is the reason I am looking for somebuddy who can help me.

russell-154600 Ten Centuries Points: 1375 More actions · Answer 3

-- this will get u started --

SET NOCOUNT ON

Create Table #t (

db varchar(255),

filename varchar(255),

name varchar(255),

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

wherefileid = 1'

)

fetch next from c into @db

end

Close c

Deallocate c

Select * from #t

Drop table #t

edit math error... :blink:

russell-154600 Ten Centuries Points: 1375 More actions · Answer 4

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

Austin_123 SSCommitted Points: 1648 More actions · Answer 5

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

Susan S SSCommitted Points: 1771 More actions · Answer 6

This script is very useful.

Thanks for that I can use this too for my server.

Susan S SSCommitted Points: 1771 More actions · Answer 7

btw -can we capture the log file too?

where do we add it on the script?

Thanks,

Susan

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 8

edit math error...

Ummm... what's that mean? :blink:

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Susan S SSCommitted Points: 1771 More actions · Answer 9

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)

🙂

Austin_123 SSCommitted Points: 1648 More actions · Answer 10

If you remove the where clause in given script then it will display the information about transaction log file coz' the file id of .mdf file is 1 and fileid of .ldf file is 2. The below block of the code will give the required information even if you are having multiple data files I mean primary(.mdf) and secondary(.ndf) or multiple log files because there is no where clause used. Follow the below script.

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')

-- dont use where clause here coz' it will restrict the result

I have executed above script successfully.

try it...

Regards,

Austin

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 11

Susan S (11/8/2007)
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)
🙂

No, no... wasn't directed at you, Susan... Russell's thread had "edit math error" at the bottom and I wanted to know what he meant by that...

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Susan S SSCommitted Points: 1771 More actions · Answer 12

Susan S

SSCommitted

Points: 1771

November 11, 2007 at 5:16 pm

#750114

Thanks Austin.

It works well 🙂

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 13

Just a different slant on things...

-- DROP TABLE #T

GO

--===== Create a table to store the results in

CREATE TABLE #T

(

Name SYSNAME,

FileID INT,

FileName NVARCHAR(512),

FileGroup VARCHAR(100),

Size VARCHAR(20),

MaxSize VARCHAR(20),

Growth VARCHAR(20),

Usage VARCHAR(20)

)

--===== Declare a local variable for some dynamic SQL... Could use VARCHAR(MAX) in 2k5

DECLARE @SQL VARCHAR(8000)

--===== Create all the commands necessary for ALL databases

SELECT @SQL = ISNULL(@SQL+CHAR(13),'')

+ REPLACE('USE [Mydb] INSERT #T EXEC sp_helpfile','Mydb',Name)

FROM Master.dbo.SysDatabases

--===== Execute the commands

EXEC (@SQL)

--===== Display the results

SELECT * FROM #T ORDER BY Name

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Adam Haines SSC-Insane Points: 23217 More actions · Answer 14

Nice one Jeff. I am adding this one to my toolbelt. 😀

My blog: http://jahaines.blogspot.com