Transaction is taking more space after shrinking also

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Transaction is taking more space after...

11 posts, Page 1 of 2

1 2 »»

Transaction is taking more space after shrinking also

Rate Topic

Display Mode

Topic Options

Author

Message

anjan.ashok

Posted Tuesday, June 03, 2008 10:12 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 01, 2011 3:13 AM
Points: 139, Visits: 282

Transaction is taking more space after truncating also.

The database is in Full recovery mode as we need both data and log backup for security purpose.

If i change to simple recovery mode i can get only data backup not the log backup in case of any database corrupt or deletion and we can't do point in time restore in case of db corrupt.

So pls tell me how to solve this transaction log growth issue.

Pls reply asap.

Post #511108

Suresh B.

Posted Tuesday, June 03, 2008 11:13 PM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 4:49 AM
Points: 1,075, Visits: 5,119

>> Transaction is taking more space after truncating also.

What is meant by taking more space? Does it mean log file size is same as before?

Did you shrink the log file?

Check the log space used using the following command.
DBCC SQLPERF(LOGSPACE)

Anyway after truncating log take full database backup.

Post #511121

anjan.ashok

Posted Wednesday, June 04, 2008 12:33 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 01, 2011 3:13 AM
Points: 139, Visits: 282

Suresh,

Once we shrink it will reduce,but after some hours log file size increases about more than 2gb.Thats the issue.

I am shrinking daily.Wats the solution.

Post #511148

Suresh B.

Posted Wednesday, June 04, 2008 12:40 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 4:49 AM
Points: 1,075, Visits: 5,119

>>after some hours log file size increases about more than 2gb. Thats the issue.

May be, there are so many transactions. Check it with Profiler.

Don't shrink the log file, unless you have shortage of free space.

What is the data and log file size?

Post #511151

anjan.ashok

Posted Wednesday, June 04, 2008 12:46 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 01, 2011 3:13 AM
Points: 139, Visits: 282

Data size is 8 gb and log size is 14 gb.

i checked through profiler lot of transaction are going on.

So during weekend the log file size reaches 20 gb

Post #511152

Cath Trimble

Posted Wednesday, June 04, 2008 3:01 AM

Right there with Babe

Group: General Forum Members
Last Login: Thursday, January 24, 2013 3:51 AM
Points: 787, Visits: 1,192

Hi,

Your transaction log is growing to accommodate the large number of transactions, as it's supposed to. Frequent shrinking will cause fragmentation and as the log file grows again it will use more server resources to do so - both not good. To control the size of the log, schedule more frequent transaction log backups. Log backups free up space within the log for reuse so if you monitor this you should be able to settle on the optimum size for the log and frequency of log backups. Check out 'Using Transaction Log Backups ' in BOL.

Cheers,

Cath

Regards,
Cath

"Hang on lads, I've got a great idea. "
Michael Caine (Charlie Croker) The Italian Job

Post #511209

anjan.ashok

Posted Wednesday, June 04, 2008 3:26 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 01, 2011 3:13 AM
Points: 139, Visits: 282

Cath,

Log file size was 5.62 gb.

I took transaction log backup twice but still the size is same when i crosscheck the size is still the same as 5.62 gb.

I think the log backup idea is working will monitor and let u know after some time.

Any other techniques?

Post #511220

Cath Trimble

Posted Wednesday, June 04, 2008 5:36 AM

Right there with Babe

Group: General Forum Members
Last Login: Thursday, January 24, 2013 3:51 AM
Points: 787, Visits: 1,192

Hi,

Yes, the log backup will only free up the space so it can be reused, so you won't see the size of the file decrease. If you want to know how much space is actually used inside the file try DBCC SQLPERF (LOGSPACE). That will give you the size of the log file in MB and the percentage actually used. From that you can work out how much free space there is. Monitor that for a while and you should be able to calculate a suitable log size and backup frequency.

Cheers,

Cath

Regards,
Cath

"Hang on lads, I've got a great idea. "
Michael Caine (Charlie Croker) The Italian Job

Post #511266

anjan.ashok

Posted Wednesday, June 04, 2008 6:04 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 01, 2011 3:13 AM
Points: 139, Visits: 282

Thanks cath.

I will monitor and will tell u.

Post #511286

tosscrosby

Posted Wednesday, June 04, 2008 7:03 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, April 19, 2011 7:31 AM
Points: 2,832, Visits: 5,316

I'd do exactly as Cath has suggested but just from your description of the problem (grows to 20GB over the weekend), I can tell you are not backing up the log frequently enough for the load you are having. If it's important to keep the log file at a certain size, increase your frequency of backups. And in reality, you kind of solved you own problem:

'I took transaction log backup twice but still the size is same when i crosscheck the size is still the same as 5.62 gb.'

It didn't increase in size because you did backups and freed the space in the log for reuse!!

-- You can't be late until you show up.

Post #511324

« Prev Topic | Next Topic »

11 posts, Page 1 of 2

1 2 »»

Permissions