Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
lfmn
Posting Yak Master
141 Posts |
 Posted - 2002-03-21 : 09:15:21
|
| when I run the sp_spaceused proc on my database, I get the following:database_name database_size unallocated space -------------------------------- ------------------ schedule 372.19 MB 101.43 MB reserved data index_size unused ----------------------------- ------------------ -------277256 KB 266528 KB 8728 KB 2000 KBwhen I go to enterprise manager, I see that size is 373 MB, but space available is 94.62I'm tring to reconcile these numbers, but have a number of questions:1) what is the difference between unallocated space in sp_spaceused and space available in enterprise manager2) reserved space is 277256 KB which is approx 275 MB (unless I'm converting incorrectly) It looks like reserved and unallocated from sp_spaceused = database size. Is this correct3) Is log size included in any of these numbers?Last but not least, does anyone know of a good article or book which deals with these issues? I looked at inside sql server 7, but didn't see this addressed (maybe I missed it)Thanks in advance for your helpSQL is useful if you don't know cursors :-) |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-21 : 10:40:00
|
| I found this paragraph in the sp_spaceused entry in Books Online:When updateusage is specified, Microsoft® SQL Server™ scans the data pages in the database and makes any necessary corrections to the sysindexes table regarding the storage space used by each table. There are some situations, for example, after an index is dropped, when the sysindexes information for the table may not be current. This process can take some time to run on large tables or databases. Use it only when you suspect incorrect values are being returned and when the process will not have an adverse effect on other users or processes in the database. If preferred, DBCC UPDATEUSAGE can be run separately.If you ran it without the updateusage parameter, it could explain the variation between it and EM.I don't think that the log is included in these because the log could be any size (if you've never backed up the log, it could be bigger than the data file, for instance). |
 |
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2002-03-21 : 11:02:44
|
| Actually, if I recall correctly it goes as follows: The database size reported by sp_spaceused includes the size of both the data and the log files. The unallocated space reported by sp_spaceused on the other hand, pertains to the datafiles only (it represents the amount of space available in the datafiles for the situations when SQL Server decides to allocate additional extents to the database tables). Space available as reported by EM represents the amount of free space available in both the data and the log file (which makes this number totally useless if you ask me).---------------Strong SQL Developer wanted in the Boston area. Please e-mail if interested.Edited by - izaltsman on 03/21/2002 11:03:01 |
|
|
|
|
|
|
|
|
sp_spaceused question