I have truncated all tables in a rather large Microsoft SQL Server 2014 database, so that I could take a backup of just the data schema, permissions, etc. (I can't just let SSMS create scripts of the database, since there are circular dependencies between different databases..).
When I'm creating a backup of the database the resulting file size is 1.3GB which seems rather excessive for an empty database. What kind of meta data (all tables are empty and report sizes of 0) could explain such a large file size and how could I investigate this?
There are some CLR assemblies in the database, but when I let SSMS generate a database creation script (which includes the assemblies base64 encoded it seems), the resulting sql file is only 12MB large so I don't think this would explain it.
As far as I know the backup really only backs up the actual data and not the unused space – otherwise most backups would be much larger than they are. I tried to shrink the files just to be on the safe side though (not like I can fragment an index of an empty table too badly) but that didn't change anything.
sp_spaceused returns database_size 1291.57MB, unallocated space 1.95 MB; reserved 1192696 KB; data 1174208 KB; index_size 15208 KB; unused 3280 KB. SQL Server really seems to think that I have lots of data somewhere, just not in my tables.
SELECT is_read_committed_snapshot_on FROM sys.databases returns 1 for this database.
I put the database in single user mode and took it offline (so that should definitely kill every transaction) and then ran sp_spaceused again. I get the same numbers as before and the backup is still the same size.
SELECT count(*) AS NumRows FROM sys.dm_tran_version_store returns 0.
Best Answer
I think this might be a problem related to service broker messages - please take a look at this link
https://stackoverflow.com/questions/5172686/sql-server-database-file-not-being-truncated