XML FeedsTo see all the databases with their size on an instance, you can use sp_helpdb. That works but returns the results in some random order. In my case I see master, model and
msdb followed by a couple of user database, then tempdb and then again some user databases. What if I want to get the list returned order by size descending? This is pretty easy if you dump the results into a table and then do the sorting when doing the SELECT query against this table. The one thing you have to do is taking out MB from the db_size column and converting it to something numeric.
Here is what the query looks like
tsql
- CREATE TABLE #test (name varchar(100), db_size varchar(100),owner varchar(100),db_id int,created varchar(100),status varchar(1000),compatibility_level int)
- INSERT #test
- EXEC sp_helpdb
- SELECT name,db_size,owner,db_id,created,compatibility_level
- FROM #test
- ORDER BY CONVERT(float,REPLACE(db_size,' MB','')) DESC
- DROP TABLE #test
And here is what you would see
name db_size owner db_id created compatibility_level TestBigger 988.31 MB Denis\Denis 8 Nov 26 2012 110 TestSmaller 710.31 MB Denis\Denis 9 Nov 26 2012 110 msdb 38.00 MB sa 4 Feb 10 2012 110 master 18.63 MB sa 1 Apr 8 2003 110 ReportServer 12.94 MB Denis\Denis 5 Aug 16 2012 110
This "check" will be part of the informational section of SQLCop
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.