Need Urgently
-
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.
-
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)
Intro to Tally Tables and Functions -
well i need to monitor growth of both log and data files.i need to maintain record
-
This will do for each server... all I did was search for "database size" and about a zillion of these showed up...
http://qa.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)
Intro to Tally Tables and Functions -
I apologize in advance for pointing to another site, but:
http://education.sqlfarms.com/education/ShowPost.aspx?PostID=47
-
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)
Intro to Tally Tables and Functions -
I would suggest to add "DBCC UPDATEUSAGE" into that script.
I saw several times negative values returned by sp_helpfiles.
_____________
Code for TallyGenerator -
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)
Intro to Tally Tables and Functions -
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!
-
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 -
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)
Intro to Tally Tables and Functions -
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
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply