Backup of truncated database large

Question

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.

Answer 1

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

Answer 2

Value from second of Your queries suggests that Your database is working in Read Committed Snapshot Isolation. This might be a reason, why database still allocates and uses that much space, even if it's tables were truncated. So best course of action would be to waiting for other still running transactions to finish. Or kill them if that is possible.

Try finding a culprit with either sp_who2 or SELECT * FROM sys.sysprocesses. First one should show You suspended process while with second one You will probably find out that its lastwaiting type is DISABLE_VERSIONING. To be honest using Activity Monitor should be enough to find that hanging transaction and kill it.

Last option is to cut any other connection to database with either taking database/sql server offline, or switching to single user/restricted mode.

Then check again what sp_spaceused returns and make a backup when it returns correct (for Your empty database) value.

More about Snapshot Isolation:

https://www.brentozar.com/archive/2013/01/implementing-snapshot-or-read-committed-snapshot-isolation-in-sql-server-a-guide/