SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Show Database and Log sizes, free space and location


Show Database and Log sizes, free space and location

Author
Message
yleonidsh
yleonidsh
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 113
Comments posted to this topic are about the item Show Database and Log sizes, free space and location
roland.hangg
roland.hangg
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 120
Very nice script, thanks for it. I have a suggestion for it so it will work also in a mirroring environment if not all databases are principle or if a database is offline...
Just replace line 52 with this statement:

DECLARE cur_Databases CURSOR FAST_FORWARD FOR
SELECT DatabaseName = [name] FROM dbo.sysdatabases
where status not in (32, 36, 48, 512, 528, 536, 1024, 2048, 3104,
4096, 65568, 65664, 66048, 66056, 66560, 66568, 67072, 70664,
4194336, 4194340, 4194352, 4194824, 4260360, 4194352, 4259872, 1073807392)
ORDER BY DatabaseName

That's all. Have a nice day!
jtodd 60645
jtodd 60645
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 59
Thank you Leonio.

Newbie to SQl!

i need to get a report like this but that also details the last access date for databases.

How can i get this with your script.

Not a SQL expert.
yleonidsh
yleonidsh
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 113
Hello,

I have found nice solutions online. For instance this link
http://stackoverflow.com/questions/711394/how-do-you-find-the-last-time-a-database-was-accessed

Using this idea last statement in my script may look like following:

...
...
SELECT
DatabaseName = fsi.DatabaseName,
FileGroupName = fsi.FileGroupName,
LogicalName = RTRIM(fsi.LogicalName),
FileName = RTRIM(fsi.FileName),
FileSize = CAST(fsi.FileSize*@PageSize/@Kb as decimal(15,2)),
UsedSpace = CAST(ISNULL((fs.UsedExtents*@PageSize*8.0/@Kb), fsi.FileSize*@PageSize/@Kb * ls.SpaceUsedPercent/100.0) as decimal(15,2)),
FreeSpace = CAST(ISNULL(((fsi.FileSize - UsedExtents*8.0)*@PageSize/@Kb), (100.0-ls.SpaceUsedPercent)/100.0 * fsi.FileSize*@PageSize/@Kb) as decimal(15,2)),
[FreeSpace %] = CAST(ISNULL(((fsi.FileSize - UsedExtents*8.0) / fsi.FileSize * 100.0), 100-ls.SpaceUsedPercent) as decimal(15,2)),
lad.LastAccessDate
FROM #FileSize fsi
LEFT JOIN #FileStats fs
ON fs.FileName = fsi.FileName
LEFT JOIN #LogSpace ls
ON ls.DatabaseName = fsi.DatabaseName
LEFT JOIN (SELECT
DatabaseName = DB_NAME(database_id),
LastAccessDate = COALESCE(MAX(last_user_seek),MAX(last_user_scan),MAX(last_user_lookup),MAX(last_user_update))
FROM sys.dm_db_index_usage_stats
GROUP BY DB_NAME(database_id)
) lad ON fsi.DatabaseName = lad.DatabaseName
ORDER BY LEFT(RTRIM(fsi.FileName),1), CAST(ISNULL(((fsi.FileSize - UsedExtents*8.0)*@PageSize/@Kb), (100.0-ls.SpaceUsedPercent)/100.0 * fsi.FileSize*@PageSize/@Kb) as int) DESC

Regards,
Leonid
jtodd 60645
jtodd 60645
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 59
Thanks Leonid.

Unfortunately, I don't have any SQL programming training.

I took what you had and tried to run it and had errors.

I will be under taking some training, however, in the meantime I want to be able to run a script that would provide the info needed.

So I have found 2 seperate scripts but need to have the results produced in one report.

I would apprecaite any help you can offer.

Thanks
yleonidsh
yleonidsh
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 113
Hello,
See attachment
Regards
Attachments
DBAll_SpaceUsed_and_Free.txt (17 views, 2.00 KB)
jtodd 60645
jtodd 60645
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 59
Thank you very much Leonid. Perfect!

Since I'm running this on different servers, is there a way to list the server name on the report?

I would apprecaite any input on training suggestions. This is a learning curve and I'm getting excited!

Once again thanks. have a great day.
jtodd 60645
jtodd 60645
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 59
Got it!

Thanks again.
rjoseph
rjoseph
Old Hand
Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)

Group: General Forum Members
Points: 345 Visits: 145
Hi Leonid and Gurus

I really like your script and would like to use it as report via email in an html format. Would anyone help me how to get that to work. I am also a newbie in SQL environment. I would really appreciate your help on that.
Wayne West
Wayne West
SSCrazy Eights
SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)

Group: General Forum Members
Points: 9098 Visits: 3705
Nice one, Leonid. I've made similar scripts in the past for such information, but I like yours better. :-) Definitely going in to my toolbox.

-----
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search