Automated delete .ldf file - 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 Administration (2000)

Automated delete .ldf file

Author

Topic

georgij1986
Starting Member

12 Posts

Posted - 2008-10-16 : 06:16:19

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

12 Posts

Posted - 2008-10-16 : 06:18:50

P.S. It need's because hardware disk free space problem

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-16 : 09:04:23

Delete?

Do you mean shrink?

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

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-10-16 : 09:06:39

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.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-10-16 : 14:16:40

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 ([url]http://sqlinthewild.co.za/index.php/2008/07/23/recovery-model-and-transaction-logs/[/url]) 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

georgij1986
Starting Member

12 Posts

Posted - 2008-10-16 : 15:39:21

quote:
Originally posted by Peso

Delete?

Do you mean shrink?

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

Shrink...

georgij1986
Starting Member

12 Posts

Posted - 2008-10-16 : 15:41:33

But I want do it automatically

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-10-16 : 16:06:57

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

georgij1986
Starting Member

12 Posts

Posted - 2008-10-17 : 01:56:58

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.

georgij1986
Starting Member

12 Posts

Posted - 2008-10-17 : 02:00:20

Where are many databases and it's tiring to delete all .ldf files every morning.

georgij1986
Starting Member

12 Posts

Posted - 2008-10-17 : 02:04:23

I want condition of successfully complete backup, to point in sheduler
delete the files.
something like:
If (backup complete successfully) then (delete /../***.ldf)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-17 : 02:08:55

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"

georgij1986
Starting Member

12 Posts

Posted - 2008-10-17 : 03:38:11

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

georgij1986
Starting Member

12 Posts

Posted - 2008-10-17 : 03:40:15

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?

georgij1986
Starting Member

12 Posts

Posted - 2008-10-17 : 03:44:05

I can start SQL Serve service with attach only .mdf file, cann't I?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-10-17 : 05:42:04

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!

georgij1986
Starting Member

12 Posts

Posted - 2008-10-17 : 06:34:51

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).

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-10-17 : 08:49:41

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.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-10-17 : 11:25:06

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.
[url]http://www.sqlskills.com/blogs/paul/post/Search-Engine-QA-23-My-transaction-log-is-full-now-what.aspx[/url]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources