Backup log with nonlogged operations

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.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Backup log with nonlogged operations

Author

Topic

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2005-02-28 : 21:35:45

What's a proper backup strategy for a production db with 15 concurrent users and fairly high transaction volume?

Now there's a full backup nightly, differential every three hours, and I would like a transaction log every half hour. Problem is, one table in the db has a TEXT column that gets updated via UPDATETEXT, and this invalidates the log. The backup software is Veritas 9.1, and it fails out on the transaction log backups because of the nonlogged operations. SQL Server is version 7.

Sarah Berger MCSD

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-02-28 : 21:47:25

Without moving to SQL Server 2000 and using Full recovery model, you'll continue to have this problem. And although this isn't specific to Veritas, I've always had some kind of problems with third-party backup software, except for SQL LiteSpeed.

Is it possible to change the procedure to do the UPDATETEXT operations in a temp table, and then simply do an UPDATE on the regular table? I haven't tried it but I'd think it would circumvent the issue.

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2005-03-01 : 12:34:50

If the temp table is in the same db, the problem still occurs. I guess the temp table resides in tempdb but it's still logged in the current database.

Are you suggesting a temporary table in a different database?

Sarah Berger MCSD

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-03-01 : 20:44:09

Temp tables (# or ## prefix) are always in tempdb, the UPDATETEXT function on a temp table would not affect the log in the regular database. A straight update WOULD be logged, even against a text column. Something like:

set nocount on
create table duh (id int not null, col1 text null)
insert duh(id) select 1 as a union all select 1 union all select 2

create table #duh (id int not null, col1 text null)
insert #duh(id,col1) select 1, '' as a union all select 2, ''

declare @p binary(16)

select @p=textptr(col1) from #duh where id=1
updatetext #duh.col1 @p 0 0 'hello there!' --not logged

select @p=textptr(col1) from #duh where id=2
updatetext #duh.col1 @p 0 0 'hello, you putz!' --not logged

update a set col1=b.col1
from duh a inner join #duh b on a.id=b.id --logged

select * from #duh
select * from duh
drop table #duh
drop table duh

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2005-03-15 : 21:45:23

The straight update doesn't get logged. Here's the sample code


CREATE Procedure WriteWordProc @CustID INT,@OrderNo INT = 0,@LineNum TINYINT = 0,@NewText TEXT, @Exists BIT
AS
DECLARE @Ptr VARBINARY(16)
CREATE TABLE #Temp (Note TEXT)
SET NOCOUNT ON
IF NOT EXISTS(SELECT * FROM WordProcessor WHERE Custid = @CustID AND OrderNo = @OrderNo AND LineNum = @LineNum)
	INSERT INTO WordProcessor(Custid,OrderNo,LineNum,Note) VALUES (@CustID,@OrderNo,@LineNum,'')
	--SELECT @Ptr = TEXTPTR(Note) FROM WordProcessor WHERE Custid = @CustID AND OrderNo = @OrderNo AND LineNum = @LineNum
	INSERT #Temp (Note) VALUES ('')
	SELECT @Ptr = TEXTPTR(Note) FROM #Temp
	WRITETEXT #Temp.Note @Ptr @NewText --alternatively use UPDATETEXT WordProcessor.Note @Ptr 0 0 @NewText
	UPDATE Wordprocessor SET Note = #Temp.Note FROM #Temp WHERE CustID = @CustID AND orderNo = @Orderno AND LineNum = @LineNum
	DROP TABLE #Temp
SET NOCOUNT OFF
RETURN

I truncated the log, then set the Truncate Log On Checkpoint to off. Ran the procedure above twice, and tried to do a BACKUP LOG. I got this error
Server: Msg 4213, Level 16, State 1, Line 1
Cannot allow BACKUP LOG because file 'MedMast_Data' has been subjected to nonlogged updates and cannot be rolled forward. Perform a full database, or differential database, backup.
Server: Msg 3013, Level 16, State 1, Line 1
Backup or restore operation terminating abnormally.

It seems the straight updates are also nonlogged. I guess I'll have to wait for the SQL2K upgrade.

Sarah Berger MCSD

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-03-16 : 04:45:42

> I truncated the log, then set the Truncate Log On Checkpoint to off.

But why it was set to ON?
For me rob's sample (and wrapped into a sp) works exactly as he predicted.

Edit: and like you I'm sitting with ver.7.0

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-03-16 : 20:54:31

I also tested it on 7.0 a few times, and was able to back up the log successfully. I did not test the restore operation though. I'd suggest doing a full backup and then testing it again. If it still does not work then there's some other problem.

What service pack are you using BTW?

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-03-17 : 01:42:41

rob, my SP = 0 :(

Sarah:
>I truncated the log, then set the Truncate Log On Checkpoint to off.

I think it should be:
I truncated the log,
then set the Truncate Log On Checkpoint to off,
then I did full backup,
then run my sproc.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-03-17 : 08:03:00

I ran a test where I truncated the log and ran the code, and I was still able to backup the log afterwards. But trunc. log on checkpoint was never set for this database. That could be what made the original run fail.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources