Script

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Script

23 posts, Page 1 of 3

1 2 3 »»»

Script

Rate Topic

Display Mode

Topic Options

Author

Message

Austin_123

Posted Thursday, November 08, 2007 10:43 AM

SSC-Addicted

Group: General Forum Members
Last Login: Friday, May 10, 2013 10:37 AM
Points: 447, Visits: 366

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

Jeff Moden

Posted Thursday, November 08, 2007 11:10 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 33,112, Visits: 27,038

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

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #420208

Austin_123

Posted Thursday, November 08, 2007 11:17 AM

SSC-Addicted

Group: General Forum Members
Last Login: Friday, May 10, 2013 10:37 AM
Points: 447, Visits: 366

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

Post #420210

russell-154600

Posted Thursday, November 08, 2007 12:12 PM

Valued Member

Group: General Forum Members
Last Login: Friday, February 22, 2013 11:13 AM
Points: 69, Visits: 183

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

Post #420228

russell-154600

Posted Thursday, November 08, 2007 12:15 PM

Valued Member

Group: General Forum Members
Last Login: Friday, February 22, 2013 11:13 AM
Points: 69, Visits: 183

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

Austin_123

Posted Thursday, November 08, 2007 3:49 PM

SSC-Addicted

Group: General Forum Members
Last Login: Friday, May 10, 2013 10:37 AM
Points: 447, Visits: 366

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

Susan S

Posted Thursday, November 08, 2007 6:31 PM

SSC 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

Susan S

Posted Thursday, November 08, 2007 6:38 PM

SSC 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

Jeff Moden

Posted Thursday, November 08, 2007 10:18 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 33,112, Visits: 27,038

edit math error...

Ummm... what's that mean? Blink

--Jeff Moden

Post #420358

Susan S

Posted Thursday, November 08, 2007 10:24 PM

SSC 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 »

23 posts, Page 1 of 3

1 2 3 »»»

Permissions