empty sql server 2008 db backup file is very big

Im deploying my db, i more or less emptied the db(data) and then created a backup.

the .bak file is over 100mb.

why is this? how do i get it down?

im using 开发者_JS百科sql server 2008

---

EDIT

When you back up, please note that SQL Server backup files can contain multiple backups. It does not overwrite by default. If you choose the same backup file and do not choose the overwrite option, it simply adds another backup to the same file. So your file just keeps getting larger.

---

Run this and all will be revealed:

select dpages *8 [size in kbs]
from sysindexes
where indid <= 1
order by 1 desc

You can also..

Do two backups in a row to have the 2nd backup contain minimal log data. The first backup will contain logged activity so as to be able to recover. The 2nd one would no longer contain them.

There is also an issue with leaked Service Broker handles if you use SSSB in your database with improper code, but if this is the case, the query above will reveal it.

---

To get the size down, you can use WITH COMPRESSION, eg.

backup database mydb to disk = 'c:\tempdb.bak' with compression

It will normally bring it down to about 20% the size. As Martin has commented above, run also

exec sp_spaceused

To view the distribution of data/logs. From what you are saying, 1.5 MB for first table... down to 8kB on the 45th row, that accounts for maybe tens of MB, so the rest could be in the log file.