October 4, 2002 at 11:56 pm
Greetings all Experts out there,
I have a typical problem on My Databse server (Win2k-all patched up)(Ms SQl 2000 (Sp2).
I have over 200 Databases . ther is no restriction on the growth of Db. Since this is
shaed Db server and Diff customers work into it thru EM and the Dbs are updated thru web
sites too.
With Time the Database Transaction log grows to 1 Gb or even more than that for large Dbs
Now to reduce the space i manually(Thru EM) detach the db and kill log file an attach it
again.
But this is a tedious process . If somebody can help this problem as manually doing so one
by one eats all admin time...
1) why does the trans log keep on growing,, how can i make it resize it automatically.
2) If i can work out some script which susscessfully detaches and attaches all the dbs after
killing their log files.
I have high hopes from the DBA's out there !!
In anticipation that i will find some quick suggestions..
Thanks and regards
Deepa
October 5, 2002 at 5:39 am
What do you have the recovery model set to? Are you doing transaction log backups? You can use DBCC shrinkfile to reduce the file size. Not as quick as detaching and deleting the log, but lets you keep the db online.
Andy
October 5, 2002 at 8:26 am
Hi
Currently my database recovry is set as Full
also I am taking complete Databse backup of all Dbs
weekly.
No trans log backup is scheduled in this job...
Pls advise..
Regards
Deepa
October 5, 2002 at 9:33 am
If you're not going to do transaction log backups I'd recommend that you change the recovery model to simple. That will allow SQL to automatically truncate the log once the transaction is complete.
Andy
October 6, 2002 at 4:15 am
Hi
So the conclusion is:
if i dont need any trnsaction log backup--
1) change recovery option to : Simple
2) shedule the trans log backup
this way the log will truncate automatically..
step 2 is necessary..
also pls guide if i dont backup trans log then how will
It affect the state of my db...(In case I have to restore
the db back)
Thanks !!
Deepa
October 6, 2002 at 7:00 am
No need to do transaction log backup if you change to simple. By not doing them (and using simple) you have the ability to restore to the last full backup. Any changes since then would be lost.
Andy
October 6, 2002 at 7:12 am
Thanks..
So i will implement this on 1-2 Dbs and observe if theTrans log file comes down to resonable size ...
Thanks again
may need further help..
Cheers
Deepa
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply