BackUp and 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)

BackUp and Log File...

Author

Topic

danielc
Starting Member

49 Posts

Posted - 2009-05-05 : 18:44:53

Hello all,

I have setup a database to simple recovery mode. But the log file for that database has grown to about 4gigs. I was under the impression that if the database is set to simple mode the logs don't grow. Do I have to manually truncate the logs even though it is set to simple mode?

Thanks,

Daniel

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-05-05 : 20:20:54

You do not need to truncate the log in SIMPLE recovery model. Try shrinking the file down with DBCC SHRINKFILE. If it grows up to that size again, then that means you've got a large transaction running that needs to be broken into smaller chunks.

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

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

danielc
Starting Member

49 Posts

Posted - 2009-05-06 : 11:25:27

Thank you for the reply. Please excuse the newbie question, but how would I go about figuring out what large transaction is running?

Thank you,
Daniel

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-05-06 : 12:46:04

You can use SQL Profiler to view the transactions. I'd also check for SQL jobs and SSIS (or similar) packages.

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

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources