Sql server 2005 : I have 200gb databse and the log size of the databse more than 300gb its very difficult to maintian log .

Question

0.00/5 (No votes)

See more:

Dear sir,

I am using sql server 2005 and my database size is 200gb and the log size goes on expand more than 300gb on the other drive many time disk gets full.

I have shrink the log within two days .

i am unable to trace why the log size expand so fast whats goes wrong.

i usually take the full backup of the databse it contains transaction log too my database using full recover mode.

What I have tried:

USE mydb

GO -- Set database recovery model to SIMPLE.

ALTER DATABASE SPARCIM SET RECOVERY SIMPLE

GO

-- Shrink the truncated log file to 4 MB.

DBCC SHRINKFILE (mydb_Log, 4)
DBCC SHRINKFILE (mydb_Log2, 4)

GO

-- Set database recovery model to FULL.

ALTER DATABASE mydb SET RECOVERY FULL

Posted 20-Apr-16 2:01am

deeptul

Updated 21-Apr-16 1:04am

Add a Solution

Comments

ZurdoDev 20-Apr-16 8:23am

Why are you using Full Recovery mode? If you use Simple, the log will stop growing so big.

deeptul 20-Apr-16 23:53pm

Thanks sir

CHill60 20-Apr-16 8:24am

"i am unable to trace why the log size expand so fast whats goes wrong" - it depends on what you are doing with your database!
Have a look at Transaction Log Management e.g. Transaction Log Management[^]

Richard Deeming 20-Apr-16 8:33am

You say you're taking a full backup, but are you taking transaction log backups as well? If you don't, the log file will just keep growing.

Either schedule regular transaction log backups in between your full backups, or switch to simple recovery mode as Ryan suggested.

deeptul 20-Apr-16 23:54pm

Thanks to your solution i will move to simple recovery mode.

Ben J. Boyle 20-Apr-16 10:09am

Here's a decent book on the subject of transaction log management - free PDF link is on the page.
http://www.sqlservercentral.com/articles/books/94938/

deeptul 20-Apr-16 23:53pm

Thanks sir

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

ZurdoDev · Accepted Answer · 2016-04-21T01:04:00

Solution 1

It is due to your recovery mode being full. You can either do full correctly (also backup the log) or change to simple. But use this info to make your decision, Recovery Models (SQL Server)[^]

Posted 21-Apr-16 1:04am

ZurdoDev