| Author |
Topic |
|
velnias2010
Posting Yak Master
125 Posts |
 Posted - 2010-07-14 : 10:13:39
|
| Hey,My Database currently is quite large, 104489.94 MB. Im looking to reduce this, as I dont even have that much Data in my database. I have a lot of updates and deletes going on a daily database though.Whats the best to progress is there some commands I can run to give you guys more details where the root of my DB size issue is ? |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-07-14 : 10:18:14
|
| What is the size of:The MDF fileThe LDF fileIf you right click the database in SSMS and choose Properties: what are the "Size" and "Available space"?What Recovery model is the database using? (right click database, properties, choose OPTIONS)If FULL then:How often do you take Transaction log Backups?If you look back over the last few days are there some TLog backup files that are significantly larger? and do they occur at the same time each day / day of the week? |
 |
|
|
velnias2010
Posting Yak Master
125 Posts |
Posted - 2010-07-14 : 10:24:45
|
| Hey KristenRight Click Properties gave me- Size 104489.94 MB- Available Space 21128.23 MBRegardingThe MDF fileThe LDF fileHow can I get this information for you im not sure where these are located ?Recovery model is FULLHow often do you take Transaction log Backups?Again how can I get this information ? |
|
|
|
velnias2010
Posting Yak Master
125 Posts |
Posted - 2010-07-14 : 10:30:04
|
| The MDF fileThe LDF fileMDF File is 24.9 GBand LDF File is 77.0 GB |
|
|
|
velnias2010
Posting Yak Master
125 Posts |
Posted - 2010-07-14 : 10:54:16
|
| Hey guys, Just wondering if anyone could help with this. I know there is a lot of threads on this but they are very confusing and people are always saying different things.I was wondering if the below is what I should do********************1. Switch to SIMPLE recovery model2. Truncate the transaction log (BACKUP LOG dbName WITH NO_LOG)3. Shrink it (DBCC SHRINKFILE)********************Im confused about the last part3. Shrink it (DBCC SHRINKFILE) what the full command for this i.e how do I know what to shrink |
|
|
|
velnias2010
Posting Yak Master
125 Posts |
Posted - 2010-07-14 : 11:03:47
|
| Ok LDF file is now shrunk to 1MB using the above steps.How can I shrink the MDF File ?Another question I have some temp tables which hold data for a while then get deleted. Now at the moment these tables are empty but huge in size I suppose because of all the insert/ deletes going on. How can this size be removed size there is no actual data in them ? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-07-14 : 11:33:43
|
"MDF File is 24.9 GBand LDF File is 77.0 GB"OK, your LDF file (Transaction Log) is pretty big, relative to your MDF file (Data). I reckon on LDF being up to 120% of MDF, yours is/was 310%Most likely cause is that you are not running transaction log backups often enough. Second most likely cause is that Index Rebuild is jacking the size up.In changing to SIMPLE you can now only restore to the last FULL backup your made (or you can restore to the last FULL plus a later DIFFERENTIAL backup).If you are doing transaction processing (so called OLTP) (entering data such as Accounts, or products / stock) then you should be using FULL recovery model.If your database is just a static snapshot, and doesn't change, then SIMPLE is fine.I suspect you did not have Transaction log backup, or you only did Transaction log backup infrequently - once a day perhaps?If you use FULL Recovery Model your should schedule transaction log backup for every 15 minutes. The total backup filesize during the day will be the same the once-a-day approach (plus a little for overhead of each file), but the LDF file will not need to grow like crazy!Since you changed to SIMPLE you have lost your backup chain, so make sure you have regular full backups.If you use your database for OLTP change it back to FULL and set up scheduled, frequent, Transaction log backupsNOTE: In most/many cases if you have database corruption it will be possible to rescue the database using Transaction log Backups without data loss. TLog backups can also help if you restore a FULL backup and it is corrupted - you can use an earlier FULL backup (that is not corrupted) and all Tlog backups since that. If you use SIMPLE recovery model then all you can do is to restore from a FULL backup (and optional later DIFFERENTIAL backup) if those are corrupted you have to restore to an earlier, uncorrupted, copy. So TLog backups get you a second-chance  If you can recreate your database from another source then Tlog backups are much less important; if you would have to repeat all the data entry work then they are "essential""How can I shrink the MDF File ?"Given that your LDF file is now tiny if you do Database-Right-click, properties and check the SIZE / AVAILABLE SPACE then all the available space will be in the MDF file. You can shrink the MDF file, but do NOT do that if there is relatively little free space - it will just grow back again, and every time you shrink the MDF or LDF it fragments the file which will reduce performance. (Shrink is fine as a one-off action for something that has grown by accident, but not as a routine procedure) |
|
|
|
velnias2010
Posting Yak Master
125 Posts |
Posted - 2010-07-14 : 11:42:01
|
| Hey Kristen,Thanks for taking the time. I wish I could pick your brain for this problem hehe.Ok here is where I am at the momenetMy LDF is now 1MB after shrinkingand my mdf 24GB (Nothing has been done to this should I do anything) ??"Given that your LDF file is not tiny if you do the Database Right-click, properties and check the SIZE / AVAILABLE SPACE then all the available space will be in the MDF file. You can shrink the MDF file, but do NOT do that if there is relatively little free space"I dont quite understand is this- My Available Space is 21128.17 MB- and MDF is around 25 GBSo my MDF is this big because I have so much available im not sure wether this is a good or bad thing.So my next step then is (again not quite sure) to create the Transaction log backups ? How would I do this ? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-07-14 : 12:00:51
|
| "I dont quite understand is this- My Available Space is 21128.17 MB- and MDF is around 25 GB"If your LDF file is still around 1MB then this implies that 21GB of you 25GB file is empty!You should be able to shrink the MDF using right-click database, tasks, shrink.You can use Management : Maintenance Plans to set up scheduled Full and Transaction log backups |
|
|
|
velnias2010
Posting Yak Master
125 Posts |
Posted - 2010-07-14 : 12:08:06
|
| You should be able to shrink the MDF using right-click database, tasks, shrink.Sweet this worked nicely my DB is now only 15 MB haha... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-07-14 : 12:43:39
|
| Blimey! How did it get to be 100GB then? Lots of data, previously, since deleted ? |
|
|
|
|
How can I reduce the size of my Database