| Author |
Topic |
|
brucec
Starting Member
3 Posts |
 Posted - 2005-10-24 : 14:32:50
|
| I have been working on a data warehouse of sorts, and I have a question about database size.We are using SQL server to collect tables from serveral dissimilar databases and put them all together in one place (using DTS). The data comes mostly from Oracle databases but I have some old databases (accu-COBOL!). I have two nearly identical databases on our SQL server. The first database is a "staging" database. In that, I mean all of the bulk transfers come into the staging database first. I make sure all transfers were successful (from each outside database) before copying it to the data warehouse database. This was needed because all of the data needs to be consistent.The problem I am having is with the size of the data warehouse database. For some reason, it is growing at scary rate. The wierd thing is that the staging database is hardly growing at all. Currently the data warehouse database is over eight times the size of the staging database.The only major difference between the two is a few indexes that were defined on the data warehouse database. Could it be the indexes? If so, why? We ran our SQL server out of drive space over the weekend, so this suddenly became a big problem! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-10-24 : 14:35:55
|
| Yes it could be the indexes and most likely is! Indexes take space especially if they are clustered. A clustered index will be about the same size as the table.Tara |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-10-24 : 14:37:16
|
| Check the recovery model on your production database, it is probably set to Bulk-Logged or Full. This means that the transaction log is not being truncated after each transaction, so it will continue to grow until you back it up or truncate it.If you are not doing transaction log backups on your production system, and have no need or intention to do so, you can change the recovery model to Simple. This will keep the log from growing significantly, however you lose the ability to restore data to a specific point in time. |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-10-24 : 14:57:54
|
| A clustered index "is the data", creating a clustered index on a table does not augment the size significantly.Other indexes do take storage space, specially if wide.A wide clustered index will make all the other indexes larger. |
|
|
|
brucec
Starting Member
3 Posts |
Posted - 2005-10-24 : 17:24:19
|
| My recovery model is set to simple. It is not the transaction logs that are getting big, it's the database file itself. The transaction log for this database is only about 1MB, but the database is now 40+ GB.I only have indexes on about 10-20% of the tables. I admit that I don't know a whole lot about indexes. I just know that I needed to add them to some tables for queries to run without timing-out. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-10-24 : 17:33:41
|
| Run EXEC sp_spaceused @updateusage = 'TRUE' on both databases. Post the output for both here.Tara |
|
|
|
brucec
Starting Member
3 Posts |
Posted - 2005-10-24 : 17:52:52
|
| Here is the output:database_name database_size unallocated space -------------------- ------------------ ------------------ DataCore 39177.75 MB 3439.89 MB reserved data index_size unused ------------------ ------------------ ------------------ ---------- 36594544 KB 36521712 KB 30584 KB 42248 KBdatabase_name database_size unallocated space ------------------- ------------------ ------------------ Staging 5555.88 MB 220.35 MB reserved data index_size unused ------------------ ------------------ ------------------ ---------- 5462552 KB 5438504 KB 2856 KB 21192 KBRemember, with the exception of the indexes and a couple of small tables, both databases are the same. Thanks for responding... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-10-24 : 17:56:41
|
| When you say that both databases are the same, are you referring to the table structures or to the data? According to the sizes reported by sp_spaceused, the amount of data is drastically differernt. Have you compared COUNT(*) on the tables between the databases?Tara |
|
|
|
|
Database growing for no reason