In Enterprise Manager, the taskpad view showed me how much space is available in the data and log files. Is this information available anywhere in SSMS?
For instance, in one database EM shows me that I have 1200MB free in the data file and 400MB free in the log file. It also says, at the top of the taskpad view, that I have 0 free space in the database - I'm not sure how they relate, but in SSMS the only number that I can see is the 0. It also takes several mouse clicks and scrolling over to see the breakdown of the data and log sizes.In the object browser, highlight your database.
On the right pane, click on the Summary tab.
(If it is not there, hit F7.)
Take a look at the Report dropdown. I think you'll like the Disk Usage
report a little bit better than EM's archaic taskpad view.
A
wrote in message
news:c99645b4-1bd4-45d3-8a71-46591c4597dd@.discussions.microsoft.com...
> In Enterprise Manager, the taskpad view showed me how much space is
> available in the data and log files. Is this information available
> anywhere in SSMS?
>
> For instance, in one database EM shows me that I have 1200MB free in the
> data file and 400MB free in the log file. It also says, at the top of
> the taskpad view, that I have 0 free space in the database - I'm not
> sure how they relate, but in SSMS the only number that I can see is the
> 0. It also takes several mouse clicks and scrolling over to see the
> breakdown of the data and log sizes.
>
>|||Thanks for the suggestion. However, the Report dropdown is greyed out on my Summary tab. I guess that's because we didn't install Reporting Services?|||No, I don't have reporting services installed either. Are you sure the
focus in the object browser is on a database name, like I originally
suggested?
>> In the object browser, highlight your database.
The Summary tab is context sensitive, so if in the object browser you have
highlighted "assemblies" or an individual stored procedure or table or the
database diagrams node or server objects, the reports dropdown will be
greyed out, because there are no reports relevant to those objects.
A
wrote in message
news:6134a1d2-8631-434a-897c-6308dc7fef63@.discussions.microsoft.com...
> Thanks for the suggestion. However, the Report dropdown is greyed out on
> my Summary tab. I guess that's because we didn't install Reporting
> Services?
>
>|||You're right - I must have had the focus in the wrong place. It is a nice report and contains a lot of useful information, but it took 2 minutes to run on an otherwise idle server (with 4 Xeon 3.6 processors in 64 bit mode, 4GB of RAM, and data and log on separate RAID 5 spindles). I would still like to have something that quickly gives me total size and free space at the file level.|||
> nice report and contains a lot of useful information, but it took 2
> minutes to run on an otherwise idle server
The first time you ran it? Was that the only time you've run it? It may
have had to do some background processing and/or caching the first time.
My server is less powerful than that (virtual machine with 2 CPUs and 3 GB
allocated), and it resides 60 miles away in our data center, and the report
took 4 seconds to load on my paltry workstation (dual xeon 3.4, 2 GB). And
I use this report all the time; if it was slow by nature and this instance
wasn't a freak phenomenen, I'm sure I would have observed this issue before?
A
|||It consitently takes over 2 minutes for our main databases. I suspect it has something to do with the size of the database or number of tables - our database has over 1100 tables. When I tried it on a much smaller database, it came back within a few seconds.|||
> our database has over 1100 tables.
Ah, I see. Well, "quick" is a pretty lofty goal, then, unless you want
relatively stale data. You could run queries on the hour that store this
information into your own tables, then that query will be quick, but it
won't be up-to-the-minute.
A
|||So, in summary, if I am monitoring a SQL2000 server in SSMS 05 then there is no way for me to see the free space on my data files. AND if it's a large 2005 database I have to wait a long time for the report because it wants to give me details on every table. So much for a quick check of my systems!|||
I agree. Monitoring free space in data files is something a DBA does frequently, and so in SQL2000 Enterprise Manager I often used the Taskpad view for a quick check (even though it was buggy and would sometimes throw interface errors). I had been hunting for the same kind of quick reporting in SQL 2005 Management Studio, and thanks to this thread I figured it out... but I am STILL waiting for my first report to come up on Disk Usage, and it has been over 5 minutes now! I manage a large number of SAP databases, and this is the first one to be configured on SQL 2005 (the others are still 2000, but they will be upgraded soon). This is one of the smaller ones, at only 20 GB in size, but it still has 27,000 tables. I wonder what will happen when I move our 70 GB R/3 database onto this platform? Also, although I haven't checked this out yet, it appears that this disk usage report may be putting a certain amount of load on the server, based on anecdotal observation. That can't be good, if true.
It's true that I can get the information I want on any given single database very quickly by logging into the SAP application and running application-specific database monitor tools (and if the application can do it so quickly, why can't the Disk Usage report? After all, it's just a collection of system stored procedures like sp_spaceused with the output presented graphically). However, when trying to quickly get status on 20+ servers, it's a little tedious to log into each one individually, whereas the Taspad could move through them a little quicker (even though it's still one at a time).
Ok, after more than 10 minutes of execution time while writing this post, the Disk Usage report finally came back with something. However, the information is not what I had hoped for. It tells me "the information needed to display a summary page for the selected object is not available" and throws an error about "object reference not set to an instance of an object." It took 10 minutes to get to that?
|||
I can do you one better. I used the TaskPad the way you did -- space problems can be seen at a glance. I moved a db instance from 2000 to 2005, but we are running in 2000 compatibility mode until we can make some code changes. When I try to run the space report in Management Studio, it barks at me for being in 2000 compatibility mode. I can't hit the dbs from EM and I can't get the data in Managment Studio until I switch compatibility mode (and then I'll have a long running on-line report...lucky me).
I think in an effort to use their own framework and tools, Microsoft has taken away the beauty of quick access to data in this case. That's a lot of extra work to break something that already worked really pretty well.
|||
OK, further research uncovered the following:
http://www.sqlservercentral.com/columnists/bBaliner/quicklyviewingavailablespace.asp
I tweaked it by adding total space to the mix:
SELECT name AS NameOfFile, size/128.0 AS TotalSpaceInMB,size/128.0 -CAST(FILEPROPERTY(name, 'SpaceUsed' )AS int)/128.0 AS AvailableSpaceInMB
FROM dbo.SYSFILES
Kudos to Boris Baliner!
