I'm using SQL Server 2008 Developer Edition V. 10.0.2531.0. I recently ran a
program that did a lot of calculations to a database in SQL Server, but did
not add much data to the database. However, when I try to backup and restore
the DB onto a different PC, the size of the DB has grown tremendously, even
though there is not a lot of data in it. I've tried (in SSMS) using
Tasks-->Shrink-->Database, and it says the currently allocated space is
34,546 MB with only 0.11MB available free space. This is ridiculous, as the
DB was much smaller before I performed these calcs. Are there some caches or
something I can clear to stop it from using all this disk space? (Note: when
I backup the DB to the disk, the backup file is only 22.2 MGB in size).
--
Ed

Ed (Ed (AT) discussions (DOT) microsoft.com) writes:
Run this query, to see which table(s) that take up space:

select object_name(id), name, indid,
convert(int, 1E-6*reserved*8192) as size,
convert(int, 1E-6*usedsize*8192) as usedsize
from sysindexes
where indid IN (0, 1, 255)
order by size desc

The output is in MB.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Check your recovery model - I will bet you anything that your recovery model
is full and you have not performed a log backup on the database. Since this
is a developer edition/system - my recommendation would be to modify the
recovery model to simple, perform a couple of checkpoints to roll to the
beginning of the transaction log and then shrink the transaction log.

You can shrink the transaction log using DBCC SHRINKFILE which you can look
up in books online (SQL Server help).

Jeff

"Erland Sommarskog" <esquel (AT) sommarskog (DOT) se> wrote

Yes, that was it. The transaction log was taking something like 36 MB. I
wasn't aware of the transaction log before.
--
Ed


"Jeffrey Williams" wrote:

I just checked another database I have. When I use SSMS to try to back up
the 2nd DB, it doesn't have a transaction log. What makes one DB have a
transaction log and not another?
--
Ed


"Jeffrey Williams" wrote:

I figured it out. The DB with the transaction log uses FULL recovery mode,
and the one without the transaction log uses SIMPLE recovery mode. I didn't
know anything about this until I researched it this morning.
--
Ed


"Ed" wrote:

Very good - I am happy that you were able to figure this out.

"Ed" <Ed (AT) discussions (DOT) microsoft.com> wrote