truncate log 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 2005 Forums

SQL Server Administration (2005)

truncate log file

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-12-28 : 04:04:44

in sql 2005 - How can I truncate the log file
it is now 38951944 mb

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-12-28 : 09:26:18

What's the recovery model? How often are you doing log backups?
How critical is the data in that database? (ie, what's allowable data loss in the case of a disaster?)

Read through this - http://www.sqlservercentral.com/articles/64582/

--
Gail Shaw
SQL Server MVP

saurabhsrivastava
Posting Yak Master

216 Posts

Posted - 2008-12-30 : 21:07:34

Is that database part of Replication?

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-12-31 : 01:27:28

not currently
i'm not sure what the recovery model is -- I have backups so for now I don't care if I lose the ability to restore.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-31 : 01:35:57

Right click on the database in Management Studio, go to properties and then to options. What's it say for recovery model? This is a very important thing for us to know in order to help you.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-12-31 : 01:54:12

pageverify - checksum

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-31 : 01:55:54

That isn't what was asked for. What does it say for recovery model?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-12-31 : 02:02:08

sorry full

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-12-31 : 02:49:01

How often are you doing log backups?
In full recovery, if you're not doing log backups, the log will grow until it fills the drive.

What's the allowable data loss for that DB in the case of a disaster? (ask your manager if you don't know)

--
Gail Shaw
SQL Server MVP

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-12-31 : 02:53:20

i backup daily
we need to always be able to recover on one hand - but it's also very large.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-12-31 : 06:52:25

quote:
Originally posted by esthera

i backup daily

I'm not asking about your full backups. I'm asking about your log backups.

Read the article I linked above if you haven't already

quote:
we need to always be able to recover on one hand - but it's also very large.

Do you need to be able to recover to the last full backup (potentially loosing a full day of data) or do you have a stricter data loss policy (1 hour, 15 min, etc)?

--
Gail Shaw
SQL Server MVP

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-12-31 : 07:47:27

really 1 hour.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-31 : 12:33:25

Well how often do you backup the log? You haven't answered that yet.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-12-31 : 12:45:47

never

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-12-31 : 12:46:05

unless it's included in the regular backup - is it?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-31 : 12:52:36

It is not included in the regular backup. So this is why your log is so big. You aren't managing its size properly.

Add a job to backup the transaction log at a minimum of every hour (as per your above requirement). We backup all of our logs every 15 minutes.

Once you have this process in place, you can do a one-time shrink with DBCC SHRINKFILE.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-12-31 : 13:02:14

can you point me to documentation as to how I set this up?
will this take up a lot of room on the server?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-31 : 13:28:33

You can add transaction log backups via a maintenance plan or you can switch your backups to use my script: http://weblogs.sqlteam.com/tarad/archive/2008/08/19/Backup-SQL-Server-Databases.aspx

Here's how I have my production environments setup: http://weblogs.sqlteam.com/tarad/archive/2008/06/30/SQL-Server-jobs-on-production-instances.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-12-31 : 13:46:57

thanks this is very helpful -- I will go through this in detail.
When i'm ready to use dbcc shrinkfile - how do I get the path of the log file to delete?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-31 : 15:22:27

You just need to pass the logical name of transaction log, which can be found in the database properties.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2009-01-01 : 02:40:09

my current backup is the following

declare @sql varchar(1000)
select @sql = 'BACKUP DATABASE '+'traffica '+' TO DISK = ''D:\sqlbackups\'+ ' traffica '+
convert(varchar,GETDATE(),112)+'.bak'' WITH INIT '
Exec(@sql)

Tara - Is it better to use yours? does that always overwrite the old backup? If I switched to that one and say had an issue that I needed to backup to a backup from the day before -would it be possible?

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources