| Author |
Topic |
|
gavis
Starting Member
39 Posts |
 Posted - 2008-10-09 : 05:18:27
|
| hi.currently i have a database on simple recovery model and a schedule grows the db to 1gb every time the space available get less then 100 mb, so there is always free space,but now i have another problem, sql auto shrink is on to allow the log file to be shrunk but the db also shrinks at the same time, is it possible to only shrink the log file and not the db through sql and not using the (backup log) thanksGP |
|
|
Carat
Yak Posting Veteran
92 Posts |
Posted - 2008-10-09 : 05:49:43
|
| You can schedule a job where you use dbcc shrinkfile. |
 |
|
|
gavis
Starting Member
39 Posts |
Posted - 2008-10-09 : 05:53:29
|
| yes thanks, i did add it to the script i created,i just thought sql could do it on its own. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-10-09 : 08:19:45
|
| You don't have to worry about Log file growth if you are in simple Recovery Model.It truncates inactive log. |
|
|
|
gavis
Starting Member
39 Posts |
Posted - 2008-10-09 : 08:51:35
|
| thank you so much, sodeep,where can i get the info regrading this? |
|
|
|
gavis
Starting Member
39 Posts |
Posted - 2008-10-09 : 08:54:10
|
| hi,i just tested it on a local DB and it doesn't work, using sql 2005 std edition |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-10-09 : 08:55:32
|
quote:
Originally posted by gavis
thank you so much, sodeep,where can i get the info regrading this?
In books online. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-10-09 : 08:55:52
|
quote:
Originally posted by gavis
hi,i just tested it on a local DB and it doesn't work, using sql 2005 std edition
what did you test? |
|
|
|
Carat
Yak Posting Veteran
92 Posts |
Posted - 2008-10-09 : 09:39:48
|
| When you use the Simple recovery model it will indeed truncate the inactive log. But if your log file is for example 1GB en it grows to 10GB, your log file will stay at 10GB and it will not automatically shrink back to 1GB. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-10-09 : 09:43:33
|
quote:
Originally posted by Carat
When you use the Simple recovery model it will indeed truncate the inactive log. But if your log file is for example 1GB en it grows to 10GB, your log file will stay at 10GB and it will not automatically shrink back to 1GB.
I didn't say that.You should shrink it but not regularly with job. |
|
|
|
gavis
Starting Member
39 Posts |
Posted - 2008-10-09 : 09:54:02
|
| is there any other way beside a job and physical entering a script, |
|
|
|
gavis
Starting Member
39 Posts |
Posted - 2008-10-09 : 10:10:31
|
| and is there a way to disable a auto shrink via a script? |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-10-09 : 10:15:19
|
quote:
Originally posted by gavis
and is there a way to disable a auto shrink via a script?
Right click Database - Properties- Make False for AutoShrinkorSp_dboption "Databasename",'Auto Shrink','False' |
|
|
|
|
database shrink