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 |
|
HardCode
Starting Member
8 Posts |
 Posted - 2004-10-15 : 15:02:34
|
I have a log file for my development DB on my SQL Server. This log file is 2GB large. In Enterprise Manager, I:[*]Right-click the DB[*]Select Backup Database[*]Run a backup of both DB and Log Files[*]Right-click the DB[*]Select Shrink Database (3549MD allocated, 2637 Space Free)[*]Select 10% for Maximum free space in file after shrinking[*]Click OK[*]Get MsgBox saying Shrink is done. When I check the size of the log file in MyComputer, the log file is still 2GB when the DB is only 1.5GB. So then I:[*]Right-click the DB[*]Select Shrink Database[*]Click Files... command button[*]Select the log file name in the dropdown box[*]It shows Current Size: 2014MB, Space Used 20MB[*]Try Compress pages ... option, file still 2GB[*]Try Truncate free space... option, file still 2GB[*]Shrink file to: and set it to 50MB ...file still 2GB No matter what I do, the log file remains at 2GB. If Enterprise manager says the file uses merely 20MB, why won't the file shrink any less than 2GB??? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-10-15 : 15:06:43
|
| Your database recovery model is probably set to FULL and you aren't performing transaction log backups. If you don't require point in time recovery, then change your recovery model to SIMPLE then run DBCC SHRINKFILE.Tara |
 |
|
|
HardCode
Starting Member
8 Posts |
Posted - 2004-10-15 : 16:09:06
|
| Yes, that was the case. I set the database option to "Auto Shrink," and I did a full backup. The log file shrunk down to about 750MB with a data file of about 1.2GB. Is 750MB a proper size for a DB set to FULL recovery model right after a truncate (backup) and shrink? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-10-15 : 16:11:29
|
| So do you require point in time recovery?Tara |
|
|
|
HardCode
Starting Member
8 Posts |
Posted - 2004-10-18 : 13:39:43
|
| Yes, that would be best, as we have data entry and other work going on throughout the day, and a late-day crash would be bad if the departments had to redo the day's work. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-10-18 : 13:45:25
|
| How often do you perform transaction log backups?Tara |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-10-18 : 14:13:26
|
quote:
Originally posted by HardCode
and a late-day crash would be bad if the departments had to redo the day's work.
Not to mention the need to update the resume, and having to go on all those interviews....You best bet is to buy a book.....http://www.sqlteam.com/store.aspBrett8-) |
|
|
|
|
|
|
|
|
MS SQL Server File Sizes