July 10, 2013 at 8:49 pm
Comments posted to this topic are about the item Show Database and Log sizes, free space and location
July 11, 2013 at 7:14 am
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!
July 16, 2013 at 6:23 am
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.
July 16, 2013 at 7:37 am
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
July 16, 2013 at 9:25 am
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
July 16, 2013 at 11:29 am
Hello,
See attachment
Regards
July 17, 2013 at 2:07 am
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.
July 17, 2013 at 10:24 am
Got it!
Thanks again.
July 18, 2013 at 6:10 am
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.
April 16, 2015 at 9:13 am
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.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply