Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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??

Thanks

share|improve this question
2  
deleting records does not reclaim space (with the default settings). Also, check TLog. Also, off-topic. –  Mitch Wheat Apr 30 '12 at 13:50
add comment

migrated from stackoverflow.com Apr 30 '12 at 16:10

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

4 Answers

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
1  
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
add comment

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
add comment

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
add comment

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);

table space:-SP_MSFOREACHTABLE

CHECK Log file also-DBCC SQLPERF(logspace)

share|improve this answer
add comment

Your Answer

 
discard

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