SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Simple recovery and logging
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 12/05/2006 :  09:07:43  Show Profile  Reply with Quote
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

United Kingdom
12543 Posts

Posted - 12/05/2006 :  10:43:26  Show Profile  Visit nr's Homepage  Reply with Quote
>> 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

United Kingdom
22403 Posts

Posted - 12/05/2006 :  13:46:26  Show Profile  Reply with Quote
"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

Norway
3271 Posts

Posted - 12/06/2006 :  03:21:37  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000