| Author |
Topic |
|
zeeshan13
Constraint Violating Yak Guru
347 Posts |
 Posted - 2008-10-08 : 15:54:47
|
| Hi All,Few days agao I posted a similar question abpout shrinking a specific database, and the conculusion was that it cannot be shrink. This time it is a different database and the log file is big which make me thin that it can be shrink. Please read below.I have a 171 GB database. I want to shrink this database as much as possible.Under SQL Managment studio when I see the property of this database, the size of the database is 174335.94 MB and the space available is 4144.27 MB.The Files for this database and have following sizes.MDF size = 14272 KBNDF size = 82286016 KBLDF size = 96219712 KBWhen i run sp_spaceused I get the following numbers;DatabaseName: TestDBDatabaseSize: 174335.94 MBUnAllocatedSpace: 4144.27 MBReserved: 78056560 KBData: 64241760 KBIndexSize: 13774472 KBUnused: 40328 KB I dont want to corrupt or lose the data.Please suggest the best & easist way to reduce the size of this database as much as possible.Thanks a million for your prompt help.Zee |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-10-08 : 16:02:28
|
| Did you try searching how to shrink huge log file in SQLteam.com? There are millions of post there. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-10-08 : 16:07:09
|
Do nothing. Unused is only 0.044GB out of 171.00GB. Calculate the percentage.If you shrink and later need to grow the files, you will almost certainly fragment your files leading to slower system.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-10-08 : 16:08:59
|
| He needs to shrink only Log file? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
zeeshan13
Constraint Violating Yak Guru
347 Posts |
Posted - 2008-10-08 : 16:32:21
|
| Someone told me to take a fullbackup first, and then try to shrink which will reduce the log size.Is it true? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
zeeshan13
Constraint Violating Yak Guru
347 Posts |
Posted - 2008-10-08 : 16:54:42
|
| tkizer,The transaction log size is 91 GB. I dont want to backup transaction log since I dont have more than 65 GB of space on the disk. But I want to truncate it. How do I truncate transaction log, without hurting the database?Please suggest.Zee |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
zeeshan13
Constraint Violating Yak Guru
347 Posts |
Posted - 2008-10-08 : 17:06:00
|
| I never backup the TLog, which I should have. Is this is what you asked? |
|
|
|
zeeshan13
Constraint Violating Yak Guru
347 Posts |
Posted - 2008-10-08 : 17:17:50
|
| tkizer,Sorry I mistype earlier.The LDF size is 91 GB. I dont want to backup LDF since I dont have more than 65 GB of space on the disk. But I want to truncate it. How do I truncate LDF (logs), without hurting the database?Please suggest.Zee |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
zeeshan13
Constraint Violating Yak Guru
347 Posts |
Posted - 2008-10-08 : 17:32:31
|
| The recover model for this database is "bulk-logged". |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-10-08 : 17:34:38
|
| The reason why your transaction log file has grown to this size is because you are using bulk-logged recovery model and are never backing up the transaction log. You must backup the transaction log on a regular basis for both bulk-logged and full recovery models.The next thing we need answered is what are your data recovery needs. Can you afford to lose all of the data after a full backup? Or do you need point in time restore capabilities? We can't answer these for you as it depends on the criticality of your data and the application's data needs.We require point in time restore capabilities due to the criticality of our data. We backup our transaction logs every 15 minutes since that's the maximum amount of time we can afford to lose data.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-10-08 : 17:37:08
|
| Strange!! Are you using Bulk-Insert or Reindex job daily ? You need to change your recovery model to simple and shrink LDF files and again back to Full Recovery model or Bulk Logged Recovery model(If you don't want to log those activities). Make sure you take full backup to where you have space after you do it. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
zeeshan13
Constraint Violating Yak Guru
347 Posts |
Posted - 2008-10-08 : 17:39:20
|
| tkizer,I cannot afford to lose any data. The free space on the disk is just 65GB, so I dont think I can do a full backup. We are going to increase the size of the disk in a month or so, but not at this point. My whole goal was to reduce the size of this database as much as possible, and I thought that perhaps I can remove the data in the LDF file, but not sure what is the best way without losing data or corrupting the database.Please suggest.Thanks,Zee |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-10-08 : 17:40:59
|
| Its because why not Simple Recovery model rather than Bulk-Logged if transaction log aren't being backed up? |
|
|
|
zeeshan13
Constraint Violating Yak Guru
347 Posts |
Posted - 2008-10-08 : 17:41:40
|
| In order to reduce the size of the database, I am OK with setting the recovery model to simple, and can keep it simple for future.Please suggest best way to reduce the size of the database. |
|
|
|
Next Page
|
How to shrink a 171 GB size SQL 2005 database