I am looking to calculate the total disk space used by every database on SQL Server 2000. Although I explored several options, I couldn’t locate a script that effectively measures the overall space consumption. I would appreciate any guidance or a sample script that can help identify the complete size of all databases in this SQL Server version.
hey, try sp_msforeachdb to loop thru each datbase and sum the sysfiles data. might need a bit of tweaking for sql2000 but it should do the trick. hope it helps
hey, i tried joinin master.dbo.sysdatabases with each db’s sysfiles to get total size. wonder if this method is as reliabl in all cases? anyone else experiemced issues or got tips for improvng the script? let’s discuss!
In my experience, a method I found quite effective was to design a dynamic T-SQL script that switches context to each database, retrieves space utilization details via system procedures like sp_spaceused, and then logs those values into a temporary table for a final aggregation. This approach not only provides a consolidated view of all databases but also gives flexibility for additional custom filtering or reporting on a per-database basis. I encountered fewer discrepancies with this method compared to relying solely on system tables, ensuring a more accurate overall size estimation.
hey, try using a cursor over master.dbo.sysdatabases and pulling each db’s sysfiles info. not as slick as sp_msforeachdb but it gets the job done with sql2000. works fine in my envrionmt sometimes.
hey, i tried a dynamic sp_spaceused mix which gave some real uniqe insights, though i noticed minor discrepuncies in some cases. anyone else triyng a similar method or having odd output on sql2000?