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 yetRay MondSQLBackupRestore.com - your quick guide to SQL Server backup and recovery issuesSQL 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 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
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! |
|
|
Kbalz
Yak Posting Veteran
94 Posts |
Posted - 2010-06-29 : 14:11:44
|
Ray, I found Auto Shrink was set up True already |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
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. |
|
|
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 |
|
|
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 KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
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? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
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 MondSQLBackupRestore.com - your quick guide to SQL Server backup and recovery issuesSQL 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 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
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? |
|
|
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 KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
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? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
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 table3, 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.thanxglaudie |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
Previous Page&nsp;
Next Page
|