Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am transferring a database that i made onto a server machine, but the size of the database is around 8GB, for some reason. It shouldn't be needing so much space as i only have a few hundred records. How can i reduce this size? And if anyone knows, what happened here??


share|improve this question

migrated from Apr 30 '12 at 16:10

This question came from our site for professional and enthusiast programmers.

deleting records does not reclaim space (with the default settings). Also, check TLog. Also, off-topic. – Mitch Wheat Apr 30 '12 at 13:50

Check the size of your transaction log. That's probably where most of the space is being occupied. It's quite easy for a SQL Server transaction log file to grow very quickly, especially if you are not doing regular backups.

If this is the case, you can learn more about managing the size of the transaction log here

share|improve this answer
If you're not backing your database up, be sure the recovery model for the database is set to "Simple". In other models, The log will keep growing until there is a backup. – Andomar Apr 30 '12 at 13:55
If that's the case, the easy way to deploy this database is not from a .bak. Just detach the database locally, and then attach just the mdf in prod. A new ldf will be created automatically. – Brian White Apr 30 '12 at 13:59

was it ever bigger than that?

the size of the db is the "allocated size" not the real size. So let's say you have 99 mb of data and your DB has 100mb allocated. If you have your DB set to grow by 1Gb at a time, when it reaches 100mb (and needs to grow) it will allocate 1Gb and become 1.1Gb (100 mb of data and 1 Gb alocated)

You can check this configuration by right clicking your DB -> properties -> files

share|improve this answer

Try running this on your local database:

EXEC sp_MSforeachtable @command1= "EXEC sp_spaceused '?'"

That will show you for each table how much space is used for data, indexes, etc.

share|improve this answer

First check for how much free space is there in database and try to shrink the database and check any table is taking more space.

DB Shrinking Command:DBCC SHRINKDATABASE (UserDB, 10);


CHECK Log file also-DBCC SQLPERF(logspace)

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.