Transaction log too big - 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

General SQL Server Forums

New to SQL Server Programming

Transaction log too big

Author

Topic

Elidas
Starting Member

33 Posts

Posted - 2008-04-21 : 06:14:20

I am using SQL Server 2005
My data file has only 2Gb and his log file has 25Gb. There is no users logged right now in this database and I need to reduce the size of the log before the disk runs out of space (it has only 0.4Gb free left)

Disk usage report tells me that 99,5% of the log is used

What is the best way to reduce the size of the transaction log?
thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-21 : 06:22:09

LOGs are automatically truncated when doing a full backup.
When was the last time you backed up your database?

You could run

BACKUP LOG {Database name here} WITH TRUNCATE_ONLY

to free space from log file.

E 12°55'05.25"
N 56°04'39.16"

Elidas
Starting Member

33 Posts

Posted - 2008-04-21 : 06:51:17

I do a full back up everyday, last one was 10 hours ago. Do I have to do another full backup to re-truncate the log? it seems that the first time it didn't truncate the log

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-21 : 06:52:47

Did the backup complete succesfully?

E 12°55'05.25"
N 56°04'39.16"

Elidas
Starting Member

33 Posts

Posted - 2008-04-21 : 07:04:38

yes, I chequed the log and the event viewer (widnows 2003 server) and there was no errors in the backup

X002548
Not Just a Number

15586 Posts

Posted - 2008-08-12 : 11:32:17

quote:
Originally posted by Peso

LOGs are automatically truncated when doing a full backup.

Is that right?

I thought so, but 2k db isn't doing that with a full recovery model...

The truncate log worked though, but I thought a full backup up did truncate the log

Anyone?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-08-12 : 11:41:31

there's a difference between log truncation and log shrinking.
truncate doesn't always shrink the log file. it frees empty space in it.
a very simplified explanation:
a log file is chain of chunks. if your log file is 5 Gb, and it has 1000 chunks and you current position is on the 900th chunk
then when doing backup the space from 0 to 900 will be marked as rewritable but your file will still be 5 Gb large.
now if you shrink the log file (DBCC shrinkfile) the 100 chunks from 900 to 1000 will be moved to the beginning of the file and your file will be smaller.

also note that WITH TRUNCATE_ONLY is discontinued in SQL Server 2008
http://msdn.microsoft.com/en-us/library/ms144262.aspx

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

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-12 : 13:42:38

quote:
Originally posted by X002548

quote:
Originally posted by Peso

LOGs are automatically truncated when doing a full backup.

Is that right?

I thought so, but 2k db isn't doing that with a full recovery model...

The truncate log worked though, but I thought a full backup up did truncate the log

Anyone?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Full backup will not truncate log automatically in all versions.
Well backup log with truncate_only is deprecated version in SQL 2005 also. It will break log chain and should be recovered by immediately taking full backup to continue the chain.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-12 : 13:51:59

backup log with truncate_only

still works on sql server 2005.

E 12°55'05.25"
N 56°04'39.16"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-08-12 : 14:08:34

yes it's deprecated (still works) in 2005 but discontinued (No more feature) in 2008

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

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-12 : 14:08:46

Yes correct it works but discontinued from SQL server 2008 as spirit said. We generally switch recovery model to simple and truncate log and change back to full.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-12 : 14:18:06

It should be noted here that only those completed transactions get truncated from the log when a log is backed up. Anything not completed will remain in there.

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

Subscribe to my blog

youruseridistoxic
Yak Posting Veteran

65 Posts

Posted - 2008-08-13 : 09:34:59

quote:
Originally posted by tkizer

It should be noted here that only those completed transactions get truncated from the log when a log is backed up. Anything not completed will remain in there.

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

Subscribe to my blog

Which method (if any) should be used to review uncompleted transactions? Some of our tlogs are 200%+ the size of their corresponding data file. This is despite hourly tlog backups.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-08-13 : 10:13:08

look at sys.dm_tran_locks view

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

youruseridistoxic
Yak Posting Veteran

65 Posts

Posted - 2008-08-13 : 11:37:51

quote:
Originally posted by spirit1

look at sys.dm_tran_locks view

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

Excellent! Many thanks.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-13 : 12:49:06

Or DBCC opentran (Although deprecated)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources