| Author |
Topic |
|
archVille
Starting Member
6 Posts |
 Posted - 2007-02-21 : 14:03:04
|
| I have a large DB (10 GB) installed.I have built an ASP.NET application using this DB.I have 2 questions :1.Is there a way to reduce this size or make it at least 8 GB to fit to a DL writebale DVD ? Unofortunately the original size was approximate 7-8 GB and i have added some necessary indexes that the application needs.2.Can I change the location of the database (.mdf file and .ldf i suppose) to a DVD or other HDD from SQL Server, so to have this available only when I choose to use my application(so that i save 10 GB + 3 GB (the ldf file) = 13 GB !) ???? In fact i want to have my DB available occasionaly... |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-02-21 : 14:18:27
|
| 10GB is actually a small-medium sized database. What is the size of the backup file?2. No.Tara Kizer |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-21 : 14:20:55
|
| 1. A Backup file will be smaller than the database, but won't be usable without restoring it first.Shrinking the database may recover some space. Increasing the Indexes to 100% Fill Factor may remove some redundant space.I'm assuming the database is effectively Read Only; this isn't good advice for an active database!2. If the database is Read Only I believe you can mount it on Read Only media.Take a Backup first.Detatch the database.Copy the MDF and LDF files to the new locationAttach the database from the new location(If the system is expecting the LDF to be in the old location then use the Single File Attach process instead)Kristen |
|
|
|
archVille
Starting Member
6 Posts |
Posted - 2007-02-21 : 15:45:21
|
| Thanx Tara.I used the word "large" because If I had 200-300 GB available free space, 13 GB would be a small DB for me.As you can imagine, I have space problem and I want to do reduce the DB or use another location for the original.Thanx very much Kristen.I am preparing the backup file right now.Yes,it is a read-only DB because the queries i am passing dont do any insert=delete=update.I will try to do as you are suggesting.The problem is that i want to reduce the .mdf as much as i can so to fit to a DL DVD-R,and not use another HDD(prefer!).If i increase fill factor at indexes will it have a slower effect on my queries?If is it so, it is not a very good idea, because my application needs fast query answers.How can I manage to do this by the way ??? |
|
|
|
archVille
Starting Member
6 Posts |
Posted - 2007-02-21 : 15:58:14
|
| The backup file of the 10,5 GB .mdf file is ... 5 GB.Not so useful.Any suggestion to reduce the 10,5 GB if possible??? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-21 : 16:30:55
|
| "If i increase fill factor at indexes will it have a slower effect on my queries?"No, they should be faster, But inserts will take a tumble!"The backup file of the 10,5 GB .mdf file is ... 5 GB.Not so useful"Perhaps I've misunderstood you?The MDF file is 10GB and the backup file is 5GB - and you think this is NOT a good thing??Kristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-02-21 : 16:33:23
|
| 10GB for MDF and 5GB for LDF is normal!Tara Kizer |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-21 : 16:40:40
|
| Tara: did I mis-read that then?I thought archVille was saying 10GB (well, 10.5GB) for MDF and 5GB for .BAK ??Kristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-02-21 : 16:43:59
|
| Nope, it was me mis-reading it. Sorry!Tara Kizer |
|
|
|
archVille
Starting Member
6 Posts |
Posted - 2007-02-21 : 17:01:55
|
 Lets get a conclusion :.mdf file is 10,5 GB.ldf file is 3,5 GBand the backup (.bak) of the whole database is 5 GB(unzipped).I dont need the .bak file but only the .mdf and .ldf files so to use them just inserting the DVD-R, when I run my application.I guess it's awful when i want to run my application every time to do a restore and delete.The basic thing that I want is to reduce the .mdf + .ldf file so when I run my application just to insert the DVD-R where I have written my DB and nothing else.Is this possible or I am demanding somthing impossible?Thanx for the answers although. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-21 : 21:12:56
|
| "Is this possible or I am demanding somthing impossible?"I reckon it may be impossible!I agree about it being a pain to have to restore the database each time.If you have lots of columns defined as CHAR, that contain data with lots of trailing spaces, then changing those to VARCHAR may save some extra space.The fact that your backup is only half the size of your MDF file suggests there may be some slack space in your database. You can try to reclaim this by doing a Reindex on all indexes, and then a Shrink - but a Shrink after a Reindex will most likely negate all the performance benefits of the reindex! An alternative plan might be to make a new database, script all the tables and clustered indexes across, transfer the data in clustered-index order, then create any secondary indexes and FKs. That might make a "tighter" database.Kristen |
|
|
|
archVille
Starting Member
6 Posts |
Posted - 2007-02-22 : 08:45:51
|
If you have lots of columns defined as CHAR, that contain data with lots of trailing spaces, then changing those to VARCHAR may save some extra space.I am afraid every column I have is already in varchar  I reckon it may be impossible!Isn't this a huge disadvantage of SQL Server?The fact that you cannot set a path where you have your DB stored and use it only when you want this DB?(for example D:\Test_DB.mdf , where D: is a DVD drive letter)(I dont know about oracle and other DB systems) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-22 : 09:12:37
|
| If you want to open a database on the fly then there are two options:1. ATTACH the database, then DETACH it when done.2. Use the AutoClose option - database file only has to be present when you attempt to access it.Kristen |
|
|
|
TonyTheDBA
Posting Yak Master
121 Posts |
Posted - 2007-02-23 : 09:36:07
|
| You could try partitoning your database, and split several of the tables and indices into another filegroup . . . then have that filegroup available on the DVD media. As there is no update activity you should be able to reduce the Log file size.I seem to remember seeing a Stored procedure sp_show_huge_tables on the web somewhere, that will list your tables by space used so you can work out which ones to move. I have a copy of this but am leery of posting something that isn't my own work, however it does work well though.Performance from the DVD will be diabolical to say the least.Of course this is somewhat easier to do in SQL2005 with vertical partitioning support built into the database engine directly.-- RegardsTony The DBA |
|
|
|
TonyTheDBA
Posting Yak Master
121 Posts |
Posted - 2007-02-23 : 09:42:35
|
| Found it [url]http://vyaskn.tripod.com/sp_show_biggest_tables.htm[/url]-- RegardsTony The DBA |
|
|
|
Kristen
Test
22859 Posts |
|
|
archVille
Starting Member
6 Posts |
Posted - 2007-02-26 : 06:13:22
|
| Thank you all for your help.I will have to check them out to see if any result will do for me... |
|
|
|
|
SQL Server Large Database