Control the Transaction Log - SQL Server Forums

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 Development (2000)

Control the Transaction Log

Author

Topic

ankurgupta26
Starting Member

32 Posts

Posted - 2002-09-27 : 07:36:01

Hi,

I have a scripts that downloads data froman external database to the one that I am using.

The script goes like this.....

Truncate ExtTable1
Insert into IntTable1
select * from ExtTable1

Truncate ExtTable2
Insert into IntTable2
select * from ExtTable2

...and so on for about 10 such tables. Each table has about more than 100,000 records.

Recently my DBA asked me to replace truncate with delete because truncate requires extra privileges. When I did that and ran the scripts there was a tremendous increase in the size of the log file.

So we reverted back to truncate. Now, the dba wants that each of the above sql should be included with a transaction somee what like this...

begin tran
Truncate ExtTable1
Insert into IntTable1
select * from ExtTable1
commit

begin tran
Truncate ExtTable2
Insert into IntTable2
select * from ExtTable2
commit

I was just wondering whether explicitly stating the begin tran...commit will help ...as the default mode of SQL server is autocommit... in reducing the size of the log file

Thanks !
AnkuR.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-09-27 : 07:42:56

Why does he want to do that?
He is right to be concerned about giving rights to do a truncate though. It also means that you can never use transaction log backups in that database.

Suggest that you create a new database for the import process on which your process can have permissions to do the truncate and on which the transaction log is truncated on checkpoint.
Then you don't have any problems and can access the table from the other database afterwards.
And it won't cause any trouble if this database gets corrupted as it doesn't hold any permanent data.

Don't bother with the transaction maybe something like
Truncate ExtTable1
if exists (select * from ExtTable1)
begin
raiserror
return
end
Insert into IntTable1
select * from ExtTable1
if @@error <> 0
begin
raiserror
return
end

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Edited by - nr on 09/27/2002 07:43:41

ankurgupta26
Starting Member

32 Posts

Posted - 2002-09-27 : 08:29:58

Thanks for your reply...

Can you tell what does this statement mean "transaction log is truncated on checkpoint"

Thanks !
AnkuR.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-09-27 : 09:12:20

In v2000 it's called simple recovery model.
It means the transaction log isn't saved - they are deleted up to the earliest active transaction on every checkpoint. It keeps the transaction log small (unless something goes wrong) and makes the database easier to administer - and allows non-logged operations.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources