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 |
|
DaveGKeller
Starting Member
10 Posts |
 Posted - 2010-12-30 : 21:11:52
|
| My MSDE database is near the 2 gig limit. The .LDF file is 169,100 (something like that). So I go in and delete any records over 9 months old (we have no more need for that).I restart the system and notice that the .mdf file hasn't changed size at all (in the Program Files -> Microsoft SQL Server -> MSSQL$InstanceName -> Data directory. It is still at 2,080,064. However, my .LDF file is now 199, 296 (300 megs more than it was).Is there anyway to "optimize" the database now that the records have been deleted. I would think that having removed over 1.5 million records (1580730 to be exact), it would be smaller.Also, why is the .LDF file so much larger? I am not normally a d/b person and am hoping that there is a relatively easy fix to reduce the size of both of those files.I would very much appreciate any help that you can lend.Thank you.Dave G. Keller |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
DaveGKeller
Starting Member
10 Posts |
Posted - 2010-12-31 : 00:33:37
|
quote:
Originally posted by tkizer
You first need to run DBCC DBREINDEX to remove the fragmentation, then you need to shrink the database files via DBCC SHRINKFILE. Deleting data only frees up space inside the file.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
OK. I'm not sure what all that means but I'm going to Google it (and search here, of course) and hopefully understand. Thank you for the information and I'll be looking to implement it asap. DBCC DBREINDEX and DBCC SHRINKFILE - Googling now ;) |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2010-12-31 : 09:56:00
|
And, if possible, you really should consider upgrading to SQL Server R2 Express Edition. It has a limit of 10GB file size.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
|
|
|
|
|
msde cleanup (optimize) after huge delete