Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Transaction is taking more space after shrinking also Expand / Collapse
Author
Message
Posted Tuesday, June 3, 2008 10:12 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 1, 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
Posted Tuesday, June 3, 2008 11:13 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 4:17 AM
Points: 1,101, Visits: 5,279
>> 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
Posted Wednesday, June 4, 2008 12:33 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 1, 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
Posted Wednesday, June 4, 2008 12:40 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 4:17 AM
Points: 1,101, Visits: 5,279
>>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
Posted Wednesday, June 4, 2008 12:46 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 1, 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
Posted Wednesday, June 4, 2008 3:01 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 1:59 AM
Points: 803, Visits: 1,195
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
Posted Wednesday, June 4, 2008 3:26 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 1, 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
Posted Wednesday, June 4, 2008 5:36 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 1:59 AM
Points: 803, Visits: 1,195
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
Posted Wednesday, June 4, 2008 6:04 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 1, 2011 3:13 AM
Points: 139, Visits: 282
Thanks cath.

I will monitor and will tell u.

Post #511286
Posted Wednesday, June 4, 2008 7:03 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

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 »

Add to briefcase 12»»

Permissions Expand / Collapse