truncating sql logs 1

[terry712]

while users are in a db is it ok
if a script runs that pops the dbase into simple mode
truncates the log and pops it back to full

 

[mutley1]

Why would you want to do that? The whole point of full recovery mode is that you have the tran logs between full backups to roll back to. If you don't want to be able to restore to a point in time between full backups then just leave it set to simple mode. this means they wont fill up - otherwise, backup your tran logs and they will have the relevant data removed as and when it is backed up.

Rgds,

M.

 

[mutley1]

Thinking about it, besides the fact if you "pop the database into simple mode" it would trash the current log anyway and remove any transactions you had in there.

 

[terry712]

sorry my sql knowledge isnt that great an never will be.

i have a third party company that has installed the db - it runs in full security mode - db is 14gb -
main plan dumps the database out each night - the logs grow to 300gb and then box dies - it aint my baby but legacy reasons the people phone me

i was just going to script so that on say sunday - it backs the db up and then zaps the log - the users arent the best at logging out - is it safe to do this

i dont have the time to learn about sql (sorry - holds no interest to me)

 

[JayKusch]

When you say "main plan dumps the database out each night" are we talking a "backup" or is this some type of export of data and/or schema?

Thanks

J. Kusch

 

[mutley1]

I would ask the 3rd party if they are doing tran log backups (or if you can check yourself). If not, then set the database to simple mode and it should not be a problem and it will be kept under control.

I would guess the reason your tran log is growing is some kind of import / process running overnight, possibly even an indexing process, which can be quite intensive.

Basically (I know you said it doesn't interest you but a quick lowdown) a full backup is obviously your last data save at a point in time (i.e. 8 p.m.). If you then take tran logs every hour (when in full mode) then it will save all transactions undertaken during that time period, which causes the log file to grow. It will clear as much as it can when the tran log backup has completed. In simple mode it clears the tran log on a checkpoint (no specific timings but in general it's probably about every 5 seconds) which means you have no backup of the transactions that took place. you CANNOT backup a tranlog when the DB is in simple mode.

Long and the short is if your DB is in simple mode, you can only restore to the last full backup (automated or manual), but if you are in full recovery, then you can restore the last full backup and then any tran logs (I am avoiding diff backups here so you don't get confused) up to a certain point in time.

E.g. (Full mode)
1. Last full backup 8pm Mon
2. Tran logs backups every hour
3. DB dies at 1pm Tues
4. Restore full backup, then each log since and you are up to speed.

(Simple mode)
1. Same crash on the DB
2. No tran log backups so you can only revert to Mon 8pm and all data loaded since will be lost.

HTH,

M.

 

[terry712]

jay - it's maintenance plan to get the .bak file

so just to clarify - if i have a maintenance plan that is backing up the log's - i assume a .trn file or ? does the log get reduced or is the backup in the log file?

i need to relook at plans - sql 2000 was so much easier to us microsoft haters.

i know the backup works as i get a 14gb .bak each night - but i know log grows about 10gb a day