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)
 Simple recovery and logging

Author  Topic 

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-12-05 : 09:07:43
Hi all,

I'm a little confused about the simple recovery model after reviewing our log-files and their sizes, and especially the file timestamp.

I'm truncating all tables in a database every day and fill them up again using BULK INSERT and INSERT...SELECT. Doesn't this hit the transaction log *at all*?? The "date modified" timestamp in windows explorer is set to 20th of november even though the db is truncated and filled up every single day. Or does this simply mean that the file is beeing used but the free space is suficcient for all the operations beeing done, hence there is no need to change the actual file?

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

nr
SQLTeam MVY

12543 Posts

Posted - 2006-12-05 : 10:43:26
>> Doesn't this hit the transaction log *at all*??
It is probably logging just the deallocation and allocation of extents so you won't notice much log activity.

==========================================
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.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2006-12-05 : 13:46:26
"The "date modified" timestamp in windows explorer is set to 20th of november "

That will be the last re-start of SQL Server, or the last extension of the table - whichever is newer.

Just writing to the file doesn't adjust the timestamp - until SQL Server is shut down.

I expect this has to do with the exclusive lock that SQL Server places on the file - which in turn is probably an efficiency thing so that Windows doens;t have to tickle the Timestamp in the Directory EVERY time each write occurs!

Kristen
Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-12-06 : 03:21:37
Exellent fellas, I somehow assumed something like what Kristen explained was happeing but I wasn't really sure.

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"
Go to Top of Page
   

- Advertisement -