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
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Transaction Log - Size control

Author  Topic 

Kbalz
Yak Posting Veteran

94 Posts

Posted - 2010-06-29 : 10:40:06
Sql Server Std, 2008 SP1. I have a database, FULL recovery. I am Transaction log shipping this database to a second server (same sql server version). The secondary server's DB is Read Only.

We have the trans.log backing up to a shared directory every 15 minutes, and we have set the secondary server in the list of secondary Dbs. The secondary server imports the new logs fine. However the primary database's Transaction Log still grows, where we expected it to shrink.

What are we missing here? How can we get the primary transaction log to shrink properly while Transaction Log shipping is occuring?

Yeoh Ray Mond
Starting Member

49 Posts

Posted - 2010-06-29 : 13:25:04
1. A transaction log will only physically shrink in size if you have set it the database files to auto-shrink. Which in general is a bad idea as will fragment your transaction log, cause it to have many virtual log files when it grows again etc etc.

2. Generally, a transaction log will get truncated, and the free space reused, when you perform a transaction log backup. Log shipping has no effect on this.

So if your transaction log is growing, common reasons could be:

- a transaction containing many modifications is still active
- replication is enabled for this database, and the transactions have not been replicated yet


Ray Mond
SQLBackupRestore.com - your quick guide to SQL Server backup and recovery issues
SQL Image Viewer - retrieve, view, convert and export images directly from SQL Server, SQLite and Firebird databases.
SQL Data Sets - share and distribute SQL Server and Oracle data sets securely and easily
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-06-29 : 13:44:17
To add to what Ray said, another common reason is if you are rebuilding the indexes. Do you have maintenance plan or any custom code that rebuilds the indexes? And if so, what settings are being used? Also, how big is your biggest clustered index, how big is the mdf file, and how big is the ldf file?

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

Subscribe to my blog
Go to Top of Page

Kbalz
Yak Posting Veteran

94 Posts

Posted - 2010-06-29 : 13:53:48
Ray, that is where our confusion is. Log Shipping (in the GUI for it), has a spot to setup Transaction Log backup, location, schedule etc. We assumed this would carry the normal backup side affects, like committing/shrinking the ldf. We are Transaction Log Shipping every 15 minutes, and at night doing a full database and transaction log backup. Where do I set the database files to Auto-Shrink, I have not seen that option?

Tara, yes - we use Maintenance plan, to cover every possible operation available in the GUI - all done at night however. Our MDF is about 5.5 Gigs, the LDF seems to grow to about 7 gigs after a day or two (according to the network guy who is complaining about disk space). As far as clustered index, I'm not sure what that is.. really I'm just a software developer who does basic sql server maintenance. We can not afford a dedicated SQL Administrator like we should!
Go to Top of Page

Kbalz
Yak Posting Veteran

94 Posts

Posted - 2010-06-29 : 14:11:44
Ray, I found Auto Shrink was set up True already
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-06-29 : 14:31:05
Yikes! Turn autoshrink off!

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

Subscribe to my blog
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-06-29 : 14:31:29
Your maintenance plan to rebuild the index is likely the culprit here. I will provide more details later, pressed for time at the moment.

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

Subscribe to my blog
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-06-29 : 14:33:43
quote:
Originally posted by Kbalz

and at night doing a full database and transaction log backup.



You can NOT backup the tlog outside of the log shipping plan without breaking log shipping (unless you are manually restoring that file each night to the secondary server, which seems highly unlikely).

You should not be doing any backups outside of the log shipping plan.

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

Subscribe to my blog
Go to Top of Page

Kbalz
Yak Posting Veteran

94 Posts

Posted - 2010-06-29 : 15:20:59
Tara, thanks, we're seeing that now. We can not have the Maintenance plan doing a back up of transaction log. It complains about gaps in the chain.

How can we shrink the transaction log, while keeping the shipping intact? We recently upgraded to 2008 from 2005, and our network admin said he has never seen the transaction log grow like it has this week.
Go to Top of Page

Kbalz
Yak Posting Veteran

94 Posts

Posted - 2010-06-29 : 15:35:44
Tara, I double checked our production server, it is NOT running all of the maintenance options like I thought, just our test server is. Every morning, these maintenance plans are running: Cleanup History (older than 1 week), Update Statistics(All stats, full scan), Maintenance Cleanup (older than 3 days). Also, a full backup (12am, all dbs), diff backup (4am, all dbs), and transaction log backup is occurring, all databases except the db we are discussing (per above).

Auto shrink is on for the database we are discussing, at the moment. No need to post about the Index Rebuild, but I'd like to hear about Auto shrink or link to your thoughts

Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-06-29 : 15:42:30
Autoshrink should never be enabled in a production environment. It causes huge performance issues. I don't enable it in dev or test environments even, in fact, I don't enable it anywhere. It's pointless to me.

You should not be shrinking the log on a scheduled basis. It should only be done manually and only when you know you don't need that space anymore. You need to determine if you need that space, and if it does, tell your admin to add more disk space so that he doesn't complain about low free space.

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

Subscribe to my blog
Go to Top of Page

Kbalz
Yak Posting Veteran

94 Posts

Posted - 2010-06-29 : 16:10:06
I turned off Auto Shrink, I found in a sub plan, that Shrink is occuring every night. What about Auto Index, and Auto Update stats?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-06-29 : 16:12:18
I would also delete your sub plan that is dong the shrink. Scheduled shrinks are not a good idea.

It's recommend to enable auto create stats and auto update stats. We enable the async stats for some of our systems. We also use READ_COMMITTED_SNAPSHOT isolation level for performance reasons, but that's a whole 'nother topic.



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

Subscribe to my blog
Go to Top of Page

Yeoh Ray Mond
Starting Member

49 Posts

Posted - 2010-06-29 : 23:14:52
>> Ray, that is where our confusion is. Log Shipping (in the GUI for it), has a spot to setup Transaction Log backup, location, schedule etc. We assumed this would carry the normal backup side affects, like committing/shrinking the ldf.

No, those are not the normal backup side effects. Committing transactions is application dependent, while shrinking is not turned on by default.

Ray Mond
SQLBackupRestore.com - your quick guide to SQL Server backup and recovery issues
SQL Image Viewer - retrieve, view, convert and export images directly from SQL Server, SQLite and Firebird databases.
SQL Data Sets - share and distribute SQL Server and Oracle data sets securely and easily
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-06-30 : 00:44:42
Kbalz, backups never shrink the transaction log file. It doesn't matter if log shipping is used or not, shrinking is not part of a transaction log backup.

Log shipping is just a way to automatically ship and restore the files to another server. It uses a normal backup plan on the primary server.

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

Subscribe to my blog
Go to Top of Page

Kbalz
Yak Posting Veteran

94 Posts

Posted - 2010-06-30 : 11:34:27
AH - Then what exactly brings the LDF down in size, for our information?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-06-30 : 11:54:20
Only a shrink takes the file down in size. But you shouldn't shrink the file as the database needed that size for a reason. The way to control the size, meaning not have it grow too big, is frequent tlog backups. We run tlog backups every 15 minutes, which looks like you do too.

The reason why you shouldn't shrink is due to the fragmentation it causes when you are shrinking the size and then the database is expanding it out because it needs more room. The shrinks and expansions also cause a performance issue as those are costly IO events.

How big is the MDF file and how big is the LDF file?

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

Subscribe to my blog
Go to Top of Page

Kbalz
Yak Posting Veteran

94 Posts

Posted - 2010-06-30 : 12:45:53
Yes every 15 minutes as well for us.

Right now the MDF is 5.3 Gigs, and LDF 19 mb. I'm starting not to believe my network guy who said the file can grow to 7 gigs in one day.

We do one scheduled shrink, at 4am at the moment, when there is typically zero logged into our small application. Is there anything further I can do to reduce fragmentation caused by that?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-06-30 : 12:49:02
The only way to prevent fragmentation is to stop shrinking the file.

Do you really have that little free disk space that an LDF at 7GB in size is a concern? 7GB seems a tad too high for an MDF at 5GB, but perhaps you've got long running transactions needing that space. I'm still suspicious of a rebuild index job, although I think you said you ruled that out already.

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

Subscribe to my blog
Go to Top of Page

glaudie
Starting Member

1 Post

Posted - 2010-06-30 : 12:57:29
hi there, i have the very same question? I have a log file that grew exponentially I have basically done the following.
1. executed an etl job inserting 13 mil records.
2. recreating index on this table
3, running an sp, aggregating these records into a pivot.

i'm not a dba so i'm not too sure how to manage the log file. The plan is to have the log file on its on HD, but we're waiting for stock at the moment.

currently we've moved the log file to another disk, because it's not in production yet, but will be soon.

1.what is the correct process for managing your log file when it has become very large because of normal operation, but also because of unexpected very large queries like in my case. how do one commit these transactions in order to shrink the file again to a very small size.
2. what is the recommended size for a log file?

i've tried to run a checkpoint, and then did a shrink log file, but that didn't make much difference. I then tried to shrink it to a specified size, but would also not work.

thanx
glaudie
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-06-30 : 13:20:29
glaudie, please start a new topic on your issue.

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

Subscribe to my blog
Go to Top of Page
    Next Page

- Advertisement -