2

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

mfinni
  • 36,144
  • 4
  • 53
  • 86

4 Answers4

3

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

  • 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. –  Apr 30 '12 at 13:59
0

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

Diego
  • 101
  • 4
0

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.

0

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)

sivag
  • 44
  • 3