SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Transaction is taking more space after shrinking also


Transaction is taking more space after shrinking also

Author
Message
anjan.ashok
anjan.ashok
Say Hey Kid
Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)

Group: General Forum Members
Points: 683 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.
Suresh B.
Suresh B.
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5074 Visits: 5330
>> 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.
anjan.ashok
anjan.ashok
Say Hey Kid
Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)

Group: General Forum Members
Points: 683 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.
Suresh B.
Suresh B.
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5074 Visits: 5330
>>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?
anjan.ashok
anjan.ashok
Say Hey Kid
Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)

Group: General Forum Members
Points: 683 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
Cath Trimble
Cath Trimble
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1385 Visits: 1196
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
anjan.ashok
anjan.ashok
Say Hey Kid
Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)

Group: General Forum Members
Points: 683 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?
Cath Trimble
Cath Trimble
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1385 Visits: 1196
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
anjan.ashok
anjan.ashok
Say Hey Kid
Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)

Group: General Forum Members
Points: 683 Visits: 282
Thanks cath.

I will monitor and will tell u.
tosscrosby
  tosscrosby
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5784 Visits: 5316
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search