Need Urgently

Question

Post reply

Need Urgently

Bramha

SSC Veteran

Points: 201
More actions
May 26, 2007 at 12:24 pm

#117253

hi all,
Server name.......Database Name.....Datafile Size(MB).....Log File Size(MB)......DatabaseSize(MB)

Database Size(GB)



  Disk Space Total (GB) Available (MB) Available (GB)
  C
  D
  E

i am trying generate script which helps me get an out put in the above manner.
i guess dbcc sqlperf(logspace),exec xp_fixeddrives can be used please guid me in right way.
Thanks in advance.

Viewing 12 posts - 1 through 11 (of 11 total)

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

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

What do you want to do with partioned files and NDF files? And, what do you need this for?

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

Bramha SSC Veteran Points: 201 More actions · Answer 2

well i need to monitor growth of both log and data files.i need to maintain record

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 3

This will do for each server... all I did was search for "database size" and about a zillion of these showed up...

http://www.sqlservercentral.com/columnists/mnash/monitoringdriveanddatabasefreespace.asp

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

Omri Bahat-433962 SSC Eights! Points: 901 More actions · Answer 4

I apologize in advance for pointing to another site, but:

http://education.sqlfarms.com/education/ShowPost.aspx?PostID=47

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 5

Very cool... thanks Omri.

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

Sergiy SSC Guru Points: 110208 More actions · Answer 6

I would suggest to add "DBCC UPDATEUSAGE" into that script.

I saw several times negative values returned by sp_helpfiles.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 7

Yep... I agree... sorry I missed 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)

Omri Bahat-433962 SSC Eights! Points: 901 More actions · Answer 8

Sergiy,

You are correct, but this brings up the never-ending tradeoff of getting more up-to-the-point data vs. potentially causing performance implications on the target server- I worked in some environments where DBCC UPDATEUSAGE significantly impacted performance on high-transaction servers, and it took a very long time to find the reason for the issue.

From a functionality and accuracy standpoint- you are 100% correct. Great input!

Sergiy SSC Guru Points: 110208 More actions · Answer 9

You know, I would not probably mention this "minor improvement" if I would not see (with my own eyes) Reserved Space = -400MB.

Quite useful information, right?

And I suspect this thing is required for monitoring purposes. If that UPDATEUSAGE is absolutely required.

Otherwise it will indicate no changed usage for sometime, and one day it will indicate dramatical change. May lead people to wrong conclusions.

Performance hit? Yes, you're right. Applying any monitoring tool always involves some trade-off. That's something for them to consider.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 10

Heh... wise old fellow once said "To measure something is to change its state." No exception with SQL... sometimes, ya just gotta bite the bullet to find out what's going on.

I ran UPDATEUSAGE on a pretty big db at work... yeah, there was a bit of a slow down, but no major impact and it didn't last long.

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

Praveen Jain SSC Journeyman Points: 99 More actions · Answer 11

Hi

Following script will give you file name, database name and its sizes.

SELECT

alt.filename [File Name]

,alt.name [Database Name]

,alt.size * 8.0 / 1024.0 AS [Originalsize (MB)]

,files.size * 8.0 / 1024.0 AS [Currentsize (MB)]

FROM master.dbo.sysaltfiles alt

INNER JOIN dbo.sysfiles files

ON alt.fileid = files.fileid

WHERE alt.size <> files.size