Log file grown more - 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 Administration

Log file grown more

Author

Topic

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2010-07-22 : 01:05:30

Dear All,

I have a DB with 9gb of data file and 60 GB of log file.

I am not doing any log fiel backup and recovery model is SIMPLE.

We are taking evryday Differetial back up and Sunday we are taking ful backup.

Can you please help me to overcome this,as this is in PRODUCTION Database.

Need your help in this regard.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-22 : 01:13:49

If your recovery model is SIMPLE, then you've got something blocking the transaction log from being cleared. My guess is that it's either a long-running transaction or replication causing it.

Query master.sys.databases and check out the log_reuse_wait_desc column. Let us know what you find.

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

Subscribe to my blog

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2010-07-22 : 01:51:17

It shows "NOTHING"

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2010-07-22 : 01:53:56

We don't have any replication on this database.

But the query which i am trying to run is having 2400000 rows,,it was running morethan 6 hours thats why i killed the process..

Ones after that i saw the disk space we are running out of Disk space.

I am doing a insert query to database from another database having > 2400000 rows.

pls help me.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-22 : 02:16:49

Your query is the problem. That's a long-running transaction. You need to break it up into small batches such as 1000 rows per batch.

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

Subscribe to my blog

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2010-07-22 : 02:23:19

As of now i have disabled the Indexes and ran the qyery and rebuild it again.

But what about my transaction log size now ?

Recovery model is in Simple
Till now we have not taken any TRN log backup
Dially we are taking Diff backup
Evry Sunday we are taking Full backup.

Can you please help me TARA in this regard

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-22 : 02:31:11

Your long-running transaction is what caused it. You need to fix the code so that it doesn't try to move 2.4 million rows in one transaction.

Now that the query is no longer running, you can run DBCC SHRINKFILE to shrink it down. You can also do it from the GUI.

You can not take tlog backups when your recovery model is simple as you will get an error.

Full backup just once a week sounds risky to me.

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