| Author |
Topic |
|
Sarat
Constraint Violating Yak Guru
265 Posts |
 Posted - 2002-12-09 : 11:32:51
|
| Hi, I have an 18 GB database whose Ldf is around 20% 3.7 GB. Last week we moved to our new office location so we shut down from Wednesday till Friday. We did have our servers running. But there was no work (user intervention) on database except the nightly jobs and backups (full backups + Hrly Tlog Backups).Monday morning, I see my db's Ldf is at 1.5 GB!The one job (Update Stats) that ran on Thursday night where I have set the following options may have caused but I want to confirm because it is a weekly job and it never happened before.=> Update Stats = 60% sampling=> Remove unused space from Database files: Shrink DB when it grows beyond: 50 MB Amount of free space to remain after shrink: 10%Questions: 1. What caused the LDF to shrink to 1.5 GB? Combination of 'no work' + Update Stats job?2. Should the Ldf now be 3.5 GB? If Yes, How?Thanks,Sarat. |
|
|
stanle
Starting Member
1 Post |
Posted - 2002-12-10 : 06:47:49
|
| The shrink can happen only if it is done by DBCC SHRINKFILE or the autoshrink setting is on. You must check if it is on by sp_dboption or the function DATABASEPROPERTYEX (only in SS2K). In Enterprise manager, you can go to database properties, options tab and check it there.Cheers,Stanley |
 |
|
|
Sarat
Constraint Violating Yak Guru
265 Posts |
Posted - 2002-12-10 : 18:41:35
|
| Hi,I checked database property, auto shrink is NOT set and i am sure i didn't do dbcc shrink. the only thing that happened was update stats where I set the below option:-----------------------------------------Remove unused space from Database files: Shrink DB when it grows beyond: 50 MB -----------------------------------------I am thinking this caused the shrink of ldf but why from 3.7 gb to 1.5 gb? How can all of a sudden there be so much of free space?Thanks,Sarat. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2002-12-10 : 19:36:27
|
| I am confused as to what your job is doing. Could you send the code? The reason why I am asking is because update stats does not shrink a database. Is this through a maintenance plan? When you say: => Update Stats = 60% sampling => Remove unused space from Database files: Shrink DB when it grows beyond: 50 MB Amount of free space to remain after shrink: 10% are you just reading the maintenance plan screen and did not actually write any code? If so, then yes it is doing a shrink on the ldf file.If this is a production database, I would suggest turning the remove unused space flag off. Shrinking a database should only be done when needed. |
|
|
|
Sarat
Constraint Violating Yak Guru
265 Posts |
Posted - 2002-12-11 : 11:01:07
|
| Yes, You are right! I am doing this through a maintenance plan thats where I read the info from. The reason I had shrink db option checked is because I perform this task at 7 PM once a week when everyone is off including ME - dont want to stay late unless required! I am scared if I don't have the option set, the job will fill up the log file ( also nightly processes will fail) and next morning i will get bombarded with calls! Thanks! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2002-12-11 : 16:10:34
|
| I think that you are a bit confused.If your recovery model is set to full, then by doing backups of the transaction log, the log will get cleared out (the file will not be shrunk but there will be space inside of the file).If your recovery model is simple, then SQL Server will clear out the transaction log for you.What is your recovery model?Shrinking the log does not clear out the contents of the log. It just shrinks the physical file. You only need to shrink the log if you have disk space concerns and not because a job writes to the transaction log. |
|
|
|
Sarat
Constraint Violating Yak Guru
265 Posts |
Posted - 2002-12-11 : 17:04:08
|
| Hi again!My db recovery model is set to FULL and I have hourly transaction log backups. I have 16 GB disk space for log and I usually have 3.7 GB log file size. I think I understood your point:1. Shrinking file is physically reducing the file size not freeing up space in the log file.2. Backing up log is freeing up some space (virtual log?) in the log file not reducing file size.So, if a job writes to a t-log, i should backup log instead of shrinking.But IF the log fills up and the file size increases within an hour (unless i change 1 hr to 1/2 hr backups):I can shrink file right? I had to do this for defrag. I wrote an sp and scheduled it and it filled up log so fast that my db errored so I had to group tables then after each group, backup, then check log file size, ,if bigger than 5, shrink else do defrag on 2nd group followed by backup and shrink.Sorry if I still sound confused! I appreciate your help!Thanks,Sarat. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2002-12-11 : 18:06:16
|
| Yes you should shrink the file if it grows too large. But you should always backup the transaction log first (full recovery model) or truncate it (simple recovery model) so that you can shrink the file even more.Just don't shrink the file on a scheduled basis because it will cause performance problems. |
|
|
|
|
Ldf file shrink