Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
miranwar
Posting Yak Master
125 Posts |
 Posted - 2007-02-26 : 14:25:54
|
| Hi,In November our database size was 15 GIG I was advised to switch Auto Shrink DB settings off as this could have adverse impact on the performance on SQL Server. Now I find that the DB Size has grown to 27 Gig. Unused space in the datafile is around 4 Meg. Remaining space is all allocated. should i run dbcc re-index to reallocate space more efficiently within the data file ? Is there any other strategy apart from Shrink File to reduce the database size ?Thanks |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-02-27 : 01:25:11
|
| How often do you do LOG backups?Peter LarssonHelsingborg, Sweden |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-27 : 05:13:04
|
| How big is a Full backup file?How big are the individual MDF and LDF files?Is your Recovery model SIMPLE or FULL?(And if it is FULL then I'm also keen to here the answer to Peso's question!)"should i run dbcc re-index to reallocate space more efficiently within the data file"Might help, but will probably make the database bigger in the short term. You should definitely be doing Reindex (or Defrag) for performance reasons (and also Update Statistics if you aren't currently doing that) [or using the Maintenance Plan tools to do it for you]"Is there any other strategy apart from Shrink File to reduce the database size ?"Don't shrink the database - if it needs 27GB then it will just grow back to that size, and take a lot of resource to do so, plus fragment the files.Kristen |
|
|
|
miranwar
Posting Yak Master
125 Posts |
Posted - 2007-03-01 : 05:11:03
|
| Thanks.Sorry for the delay in getting back to you. I was on a course. The MDF is around 28 Gig and the LDF is around 2Gig. The recovery model is simple. The logs normally get truncated on the nightly backups.Is there any way i can use tempdb (SORT_IN_TEMPDB) to do a reindex and there for not increasing the db size in the short term. |
|
|
|
|
|
|
Database size issue