Transaction 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

General SQL Server Forums

New to SQL Server Programming

Transaction Log file

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2011-03-14 : 12:43:21

Hi,

I am working with SQL server 2005, and have a database with a large transaction log file.

Data file: 5104 MB
Log File: 12576 MB

Now, when I want to shrink the log file by going;

Tasks - Shrink - Files - File type (Log)

Currently allocated space: 12575.50 MB
Available free space: 460.57 MB (3%)

I want to reduce this log file as its huge. Also, the database is hardly being updated, its updated on a monthly basis, but has around 12 millions records, and the monthly update include addition of around around 1000 - 2000 records.

Any help please

Thank you

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-14 : 13:32:05

What recovery model is it using? If it's not SIMPLE, then how often are you running tlog backups?

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

Subscribe to my blog

GhantaBro
Posting Yak Master

215 Posts

Posted - 2011-03-14 : 14:09:39

Detach the database
Move your logfile to some other location or delete if you dont need it
Attach the DB back...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-14 : 14:15:23

Detach/attach isn't the solution for this at this time. We need to first diagnose it.

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

Subscribe to my blog

GhantaBro
Posting Yak Master

215 Posts

Posted - 2011-03-14 : 14:18:11

quote:
Originally posted by tkizer

Detach/attach isn't the solution for this at this time. We need to first diagnose it.

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

Subscribe to my blog

Ok.. diagnose why it increased to that size? It maybe due to database programmer like me who ran stupid transactions...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-14 : 14:22:59

It is almost certainly because the recovery model is set to FULL and not tlog backups are being done. If that is the case, then detach/attach is not the solution.

Detach/attach is rarely the solution actually for the bloated tlog. It will only temporarily resolve the problem, but the problem will happen again unless the root cause is fixed.

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

Subscribe to my blog

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-03-14 : 14:47:12

To add to that, deleting the transaction is a terrible thing to do. SQL cannot always recreate it...

As for the log management, please read through this - [url]http://www.sqlservercentral.com/articles/64582/[/url]

--
Gail Shaw
SQL Server MVP

GhantaBro
Posting Yak Master

215 Posts

Posted - 2011-03-14 : 14:58:09

quote:
Originally posted by GilaMonster

To add to that, deleting the transaction is a terrible thing to do. SQL cannot always recreate it...

As for the log management, please read through this - [url]http://www.sqlservercentral.com/articles/64582/[/url]

--
Gail Shaw
SQL Server MVP

That is why I said if u do not need it.. But yes I should not try to suggest on DBA stuff being a developer... I do that for only test databases if I ever do it anyway. Thanks guys.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-03-14 : 15:34:29

But how do you know if it's needed or not? If you get it wrong, the database will not reattach

--
Gail Shaw
SQL Server MVP

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2011-03-15 : 11:11:22

Ok, thanks for all your responses -- the Recovery model was FULL. Ok, changed it to Simple but that will sort the problem for future.. But how will O solve the problem at this present time.. I need to reduce the log file size.

Any hlep please

GhantaBro
Posting Yak Master

215 Posts

Posted - 2011-03-15 : 12:31:29

quote:
Originally posted by GilaMonster

But how do you know if it's needed or not? If you get it wrong, the database will not reattach

--
Gail Shaw
SQL Server MVP

So depending on the recovery model, the database might not re-attach? I am not a DBA want to know. I think his issue is solved based on Tkizer's suggestion... he needs to make space for log file now... should he be backing up the log file every week now? What steps DR223 should take now? I want to learn exactly how experienced DBAs like you do? Thanks for the response.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-15 : 12:34:30

Now that the recovery model is switched to SIMPLE, you need to shrink the file with DBCC SHRINKFILE.

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

Subscribe to my blog

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2011-03-15 : 13:12:11

Ok - what if I go to Tasks - Shrink - Files - File Type: Log

Reorganize pages before releasing unused space - shrink file to: 200 MB

N/B

Currently allocated space 12575.50 MB
Available free space: 12506.26 MB

Is the above procedure fine?

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-15 : 13:14:38

I wouldn't shrink it that much as it'll likely need to expand a bit for a 5GB MDF file. Try 500MB or even 1GB instead.

If you have regularly occurring index rebuilds, you'll need a bit of tlog space.

And the GUI way is fine too, it also uses DBCC SHRINKFILE.

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

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources