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)
 Automated delete .ldf file
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

georgij1986
Starting Member

Georgia
12 Posts

Posted - 10/16/2008 :  06:16:19  Show Profile  Reply with Quote
How delete .ldf file after automated backup?
After automated backup(after working day) next day I must detach
database, delete .ldf file, and attach.
How do it automatically?

georgij1986
Starting Member

Georgia
12 Posts

Posted - 10/16/2008 :  06:18:50  Show Profile  Reply with Quote
P.S. It need's because hardware disk free space problem
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30188 Posts

Posted - 10/16/2008 :  09:04:23  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Delete?

Do you mean shrink?



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 10/16/2008 :  09:06:39  Show Profile  Reply with Quote
If space is issue:
Just change your recovery model to Simple,and shrink if it huge. Remember it doesn't let you point in time recovery.
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 10/16/2008 :  14:16:40  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
Do not ever delete your transaction log file. You're asking for a corrupt and unusable database.

I'm guessing you're in full recovery and aren't doing log backups. Read this (http://sqlinthewild.co.za/index.php/2008/07/23/recovery-model-and-transaction-logs/) then either set your DB to simple recovery (NB, no way to restore to point of failure) or schedule log backups.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

georgij1986
Starting Member

Georgia
12 Posts

Posted - 10/16/2008 :  15:39:21  Show Profile  Reply with Quote
quote:
Originally posted by Peso

Delete?

Do you mean shrink?



E 12°55'05.63"
N 56°04'39.26"



Shrink...
Go to Top of Page

georgij1986
Starting Member

Georgia
12 Posts

Posted - 10/16/2008 :  15:41:33  Show Profile  Reply with Quote
But I want do it automatically
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 10/16/2008 :  16:06:57  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
You shouldn't be shrinking the log regularly.

What recovery model is the database in?
If it's in full or bulk logged recovery, do you have regular log backups running?
If not, do you need to be able to restore the DB to point of failure (if say a drive fails)
If you don't need the point in time, and your DB is in full/bulk logged, switch it to simple
If you do need the point in time, set up regular log backups. The more frequent the log backups, the less the log will grow.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

georgij1986
Starting Member

Georgia
12 Posts

Posted - 10/17/2008 :  01:56:58  Show Profile  Reply with Quote
I have full recovery model. I shouldn't change it on simple.
I'm doing backup regularly (after workday, automatically, as I say)
I want automatically delete .ldf file, if backup complete successfully(I guess, I must run some script after backup).
(Because .ldf file is huge and it's unnecessary for me after full backup.
Go to Top of Page

georgij1986
Starting Member

Georgia
12 Posts

Posted - 10/17/2008 :  02:00:20  Show Profile  Reply with Quote
Where are many databases and it's tiring to delete all .ldf files every morning.
Go to Top of Page

georgij1986
Starting Member

Georgia
12 Posts

Posted - 10/17/2008 :  02:04:23  Show Profile  Reply with Quote
I want condition of successfully complete backup, to point in sheduler
delete the files.
something like:
If (backup complete successfully) then (delete /../***.ldf)
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30188 Posts

Posted - 10/17/2008 :  02:08:55  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Do you even know what impact that will have on your server?
If you do DELETE the log file, you will not be able to start SQL Serve service again.



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

georgij1986
Starting Member

Georgia
12 Posts

Posted - 10/17/2008 :  03:38:11  Show Profile  Reply with Quote
But it cann't be deleted before detach. And I can't detach database if there
are some connections.
at least, i want something like:
If (backup complete successfully) and (number of active connections = 0) then
begin
detach;
delete ../../***.ldf
attach;
end
Go to Top of Page

georgij1986
Starting Member

Georgia
12 Posts

Posted - 10/17/2008 :  03:40:15  Show Profile  Reply with Quote
quote:
Originally posted by Peso

Do you even know what impact that will have on your server?
If you do DELETE the log file, you will not be able to start SQL Serve service again.



E 12°55'05.63"
N 56°04'39.26"



Even if I have full backup for this moment?
Go to Top of Page

georgij1986
Starting Member

Georgia
12 Posts

Posted - 10/17/2008 :  03:44:05  Show Profile  Reply with Quote
I can start SQL Serve service with attach only .mdf file, cann't I?
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11750 Posts

Posted - 10/17/2008 :  05:42:04  Show Profile  Visit spirit1's Homepage  Reply with Quote
LDF's are IMPORTANT for correct database operations.
you're trying to handle this problem in a VERY! wrong way.

are you even grasping what we're trying to tell you here??

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!
Go to Top of Page

georgij1986
Starting Member

Georgia
12 Posts

Posted - 10/17/2008 :  06:34:51  Show Profile  Reply with Quote
quote:
Originally posted by spirit1

LDF's are IMPORTANT for correct database operations.
you're trying to handle this problem in a VERY! wrong way.

are you even grasping what we're trying to tell you here??

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!


For example?
Please, describe me event, when database can't continue normally functionality without ldf-s.
Because, I work with it about 2 year. Always deleted ldf-s (manually).
AFTER FULL DATABASE BACKUP(and saved only ldf-s after full backup).
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 10/17/2008 :  08:49:41  Show Profile  Reply with Quote
georgij1986,

Why are you ignoring what Gilamonster,Peso,Spirit has been repeating the consequences of it? They have got many-many years of experience with it and that's why they are suggesting you not to delete LOG file? Like we have been saying, if you are only doing Full backup,
then change your recovery model to simple so log files will be in control.
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 10/17/2008 :  11:25:06  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
quote:
Originally posted by georgij1986

I have full recovery model. I shouldn't change it on simple.
I'm doing backup regularly (after workday, automatically, as I say)



That's not sufficient in full recovery. You need to be doing regular log backups. Firstly it keeps your transaction log size under control. Second it allows you to do point-in-time restores.
If you don't need to be able to do a point-in-time restore, then there's no reason for the DB to be in full recovery at all.

Please read that post I linked to. Also have a look at this one.
http://www.sqlskills.com/blogs/paul/post/Search-Engine-QA-23-My-transaction-log-is-full-now-what.aspx

quote:
For example?
Please, describe me event, when database can't continue normally functionality without ldf-s.
Because, I work with it about 2 year. Always deleted ldf-s (manually).


Would you like me to find the link to a problem I've been dealing with on SQL Server Central where someone did exactly what you did, deleted the log file and restarted SQL? The DB came up corrupt and in such a way that it's not possible to repair. That person lost a full day's data because of that.

Log files are essential to ensure database consistency and recoverability. They're not an error log or similar that you can just delete and not worry. If one day SQL doesn't shut down cleanly and you delete the log file, that database will be suspect when it comes up again.

If you do insist on continuing to delete log files, make sure your resume is up to date. One day you will need it.

--
Gail Shaw
SQL Server MVP

Edited by - GilaMonster on 10/17/2008 11:27:07
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.16 seconds. Powered By: Snitz Forums 2000