Need T-SQL for this requirment

Question

Need T-SQL for this requirment

SQLisAwe5oMe

SSChampion

Points: 14052
More actions
May 10, 2012 at 10:58 am

#255805

I have a table where it lists all the "ServerName", "DBName", "DBSize", "Date"...but this table has a lot of duplicates information which I don't need, meaning I have serverNames repeating, DB name repeating, DB size from different date, etc....
My requirement is to get the unique list of all the servers, DBname, and the DBsize but only show the latest db size.
Anyone have any clue or t-sql script to get this information? Looking forward to your suggestions.
Regards,
TA
Regards,
SQLisAwe5oMe.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Can you post the table definition please?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLisAwe5oMe SSChampion Points: 14052 More actions · Answer 2

GilaMonster (5/10/2012)
Can you post the table definition please?

Hi Gail,

Sorry, I don't have it.....I was trying to help someone. So, I don't have anymore information.

TA

Regards,
SQLisAwe5oMe.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Then the best I can do is guess and give you code that probably won't work as desired.

Select ServerName, DatabaseName, Size

FROM (

Select ServerName, DatabaseName, Size, RowNumber() Over (Partition by ServerName, DatabaseName order by SomeDateColumn DESC) AS RowNo

FROM SomeTable ) sub

Where RowNo = 1

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 4

if your doing this as part of managing multiple servers you can try using cms and query the sys tables directly

Jayanth Kurup[/url]

SQLisAwe5oMe SSChampion Points: 14052 More actions · Answer 5

Jayanth_Kurup (5/10/2012)
if your doing this as part of managing multiple servers you can try using cms and query the sys tables directly

Thanks Gail....Will keep you posted.

TA

Regards,
SQLisAwe5oMe.

SQLisAwe5oMe SSChampion Points: 14052 More actions · Answer 6

SQLCrazyCertified (5/10/2012)
Jayanth_Kurup (5/10/2012)
if your doing this as part of managing multiple servers you can try using cms and query the sys tables directly
Thanks Gail....Will keep you posted.
TA

Hi Jayanath,

This requirement is for monitoring purpose......Monitor all the server/DB growth for the whole environment.

TA

Regards,
SQLisAwe5oMe.