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 |
|
rohans
Posting Yak Master
194 Posts |
 Posted - 2004-03-01 : 14:04:51
|
| How do I shrink my db_name.ldf file. I tried running a backup of the DB and it seemed not to move. I also tried DBCC shrinkfile no luck still. What should I do?All help appreciated. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-03-01 : 14:08:32
|
| What is your database recovery model set to? If FULL, are you performing regular transaction log backups? Do you have free space in the LDF file?Tara |
 |
|
|
rohans
Posting Yak Master
194 Posts |
Posted - 2004-03-01 : 14:45:31
|
| I can see the recovery model in sql 2000 but how do I view it in SQL 7?I am not performing regulat tlog backups.Yes I have free space in the database according to the DBCC slqperf.All help appreciated. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-03-01 : 14:48:36
|
| In SQL 7.0, you should be able to find it in the properties of the database in Enterprise Manager. I don't remember which tab though. If it says FULL, then you need to evaluate whether or not you want the capability of restoring to a point in time. If you don't, then change it from FULL to truncate log on checkpoint, which is similar to SQL 2000's SIMPLE recovery model. But don't just change it without knowing what your organization needs. If they need FULL, then start performing transaction log backups. Once you do that or change the recovery model, then you'll be able to shrink the LDF file.Tara |
|
|
|
rohans
Posting Yak Master
194 Posts |
Posted - 2004-03-01 : 14:54:12
|
| After changing it to truncate log on check point how long before it comes into effect. Should I also chek the auto shrink option?All help appreciated. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-03-01 : 14:58:21
|
| I do not suggest checking the auto shrink option. Shrinks should happen manually and during off-peak hours. Truncate log on checkpoint should affect it immediately. Do you now see more free space in the LDF?Tara |
|
|
|
rohans
Posting Yak Master
194 Posts |
Posted - 2004-03-01 : 15:07:40
|
| It has not shrunk. I think the application has info in it that isn't committed. Is there a commmand I can do that will force it to do the truncate?All help appreciated. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-03-01 : 15:11:45
|
| Run CHECKPOINT in Query Analyzer. If that doesn't work, you'll have to write code to move the active portion of the log. I believe the code can easily be googled.Tara |
|
|
|
|
|
|
Shrinking my db_name.ldf file