SQL Server DB size - why is it so large?

Question

I am building a database which contains about 30 tables:

• The largest amount of columns in a table is about 15.
• For datatypes I am mostly using VarChar(50) for text
• and Int og SmallInt for numbers.
• Identity columns is Uniqueidentifiers

I have been testing a bit filling in data and deleting again. I have no deleted all data, so everey table is empty. But, if I look inside the properties of the database in Management Studio, the size says 221,38 MB!

How comes that? Please help, I am getting notifications from my hosting company that I am exceeding my limits .

Best regards, :-)

Answer 1

I would suggest that you look first at the recovery mode for the database. By default, the recovery mode is FULL. This fills the log file with all transactions that you perform, never deleting them until you do a backup.

To change the recovery mode, right click on the database and choose Properties. In the properties list, choose the Options (on the right hand pane). Then change the "Recovery model" to Simple.

You probably also want to shrink your files. To do this, right click on the database and choose Tasks --> Shrink --> Files. You can shrink both the data file and the log file, by changing the "File Type" option in the middle.

Martin's comment is quite interesting. Even if the log file is in auto-truncate mode, you still have the issue of deletes being logged. If you created large-ish tables, the log file will still expand and the space not recovered until you truncate the file. You can get around this by using TRUNCATE rathe than DELETE:

truncate table <table>

does not log every record being deleted (http://msdn.microsoft.com/en-us/library/ms177570.aspx).

delete * from table

logs every record.

Answer 2

As you do inserts, updates, deletes, and design changes a log file with every transaction, and a whole bunch of other data is created. This transaction log is a required component of a SQL Server database, and thus cannot be disabled in any available settings.

Below is an article from Microsoft on doing backups to shrink the transaction logs generated by SQL Server.

http://msdn.microsoft.com/en-us/library/ms178037(v=sql.105).aspx

Also, are you indexing your columns? Indexes that consist of several columns on tables with a high row count can become unnecessarily large, especially if you are just doing tests. Try just having a single clustered index on only one column per table.

You may also want to learn about table statistics. They help your indexes out and also help you perform queries like SELECT DISTINCT, or SELECT COUNT(*), etc.

http://msdn.microsoft.com/en-us/library/ms190397.aspx

Finally, you will need to upgrade your storage allocation for the SQL Server database. The more you use it, the faster it will want to grow.