May 17, 2002 at 2:42 pm
I have been using sp_detach_db and sp_attach_db to shrink transaction logs - the old transaction log being deleted after detach and new one created. I always precede this job with truncate log. Is this step necessary? We do not perform transaction log backups.
May 17, 2002 at 3:12 pm
Since all transactions have to be completed prior to a successful detach then I would say that the truncate log step is not necessary.
Hope this helps.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
May 17, 2002 at 3:23 pm
Are you doing this just to delet the log. If what version are you using of SQL?
If 7 then use
BACKUP LOG DBNAMEHERE
WITH TRUNCATE_ONLY
Then look here for a script to force the log to shrink. http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=26
If 2000 do
BACKUP LOG ....same as 7
Then use DBCC SHRINKFILE, see BOL for proper use.
Otherwise no you do not need that step.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 18, 2002 at 1:55 pm
Thanks for all the feedbacks - great help! The detach/attach is to shrink the logs which sometimes have grown too big. I tried the force shrink log script. Works beautifully! Thanks again to all!
I apologize for the misleading message title. It was meant for my other question. Why would the following sql not work:
update t1 set t1.col1 = 'abc'
where t1.col2 in (select t2.col1 from t2)
This query fails whenever the subquery returns > one record with the following error:
server: msg 512, level16, state 1, ....
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <=, >, >= or when the subquery is used as an expression.
May 18, 2002 at 6:50 pm
Try this then
update t1 set t1.col1 = 'abc'
from
t1 t1x
inner join
t2
on
t1x.col2 = t2.col1
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 19, 2002 at 7:51 pm
It works with the inner join syntax as you suggested. However, I need a where clause to the update statement and it fails with the same error with the where clause. This is the full sql:
update t1 set t1.col1 = 'abc', t1.col2='def'
from t1
inner join t2
on t1.col3=t2.col1 and t1.col4=t2.col2
where t1.col1 = 'xy' and t2.col3 = '123' and t2.col4 is null
May 20, 2002 at 4:40 am
Try it with aliases like this.
update t1 set t1.col1 = 'abc', t1.col2='def'
from t1 t1x
inner join t2
on t1x.col3=t2.col1 and t1x.col4=t2.col2
where t1x.col1 = 'xy' and t2.col3 = '123' and t2.col4 is null
Should work.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 20, 2002 at 10:37 pm
I found out it was the update trigger for the table that was causing the error. As soon as I delete the update trigger, it works. Thanks for your help!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply