| Author |
Topic |
|
rspinell
Starting Member
36 Posts |
 Posted - 2006-08-11 : 12:30:22
|
| I’m a newbie at log shipping and was hoping someone could shed some light on an issue. I have a DB that is 125GB, I ship it every 8 hours and usually the .trn file is around 5 GB. I have a maintenance plan that does a reindex of the DB once a week. I had it run over the weekend, and when I came in on Monday the log file had grown bigger then the DB, and the shipping failed. I now understand why it happened, since the DB is in full recovery mode, it logged ever transaction and a full reindex generates log amounts of transactions.How do other people handle having to reindex a DB that needs to be log shipped? I don't want the log file to grow so gigantic, since it effects the time it takes to log ship, and also my hard disk space on the servers.Thanks |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-08-11 : 12:35:24
|
| We log ship our databases every 15 minutes, not only so that the file sizes are smaller but also because we can't afford to lose more than 15 minutes worth of data in case of a disaster. Perhaps you should consider running DBCC INDEXDEFRAG instead of DBREINDEX. You should only run DBREINDEX when you know for sure that the index is fragmented and that the queries will benefit from a reindex. Check this out for more details:http://weblogs.sqlteam.com/tarad/archive/2006/04/11/9585.aspxTara Kizer |
 |
|
|
rspinell
Starting Member
36 Posts |
Posted - 2006-08-11 : 12:53:31
|
| Tara, thanks for responding so quick. We can't ship ever 15 minutes, since the standby server is used throught the day for people to run queries against. We have certain times throughout the day that we tell the business the DB is down, that is when the log shipping is taking place, but we can't have it be down every 15 minutes. I actually found your stored procedure that does the dbcc indexdefrag and am testing it in my lab. http://weblogs.sqlteam.com/tarad/archive/2004/07/30/1834.aspxCan log shipping take place while a indexdefrag is taking place ? I read somewhere that you can't logship while doing a DBREINDEX, is the same true while running indexdefrag using your stored procedure ?Thanks |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-08-11 : 13:17:18
|
| You can log ship with both DBREINDEX and INDEXDEFRAG taking place. You have to keep in mind what log shipping is though. It's just a backup log on the source, then an xcopy to the destination, and finally a restore log on the destination.Tara Kizer |
|
|
|
rspinell
Starting Member
36 Posts |
Posted - 2006-08-11 : 14:18:53
|
| Tara, sorry for monopolizing your time, but I found the following from a web article, that is why I’m under the impression you can’t do a log ship while doing a DBREINDEX.http://www.microsoft.com/technet/community/chats/trans/sql/sql0528.mspxHost: Allan (Microsoft)Q: We are considering using log shipping, but reindexing jobs create very large transaction log backup files. The backup SQL box would be in another city, so we're concerned about transmit time. Any suggestions?A: Not much you can do, as transaction log backups cannot happen while you are doing a full database reindex (i.e. DROP INDEX). And at the end, your TL will be huge. So maybe consider doing the online DBCC INDEXDEFRAG, but that may not buy you anything. Sorry, it's not an easy answer as it is just the way the SQL Server engine works to ensure your data is consistent.It looks like the MS guy is saying you can't do transaction log backups while you are doing a full database reindex. DBCC INDEXDEFRAG will keep my log size much smaller then doing a DBREINDEX correct? The other problem I had was that the log file was 120GB, but it was only using 2GB of space, even though I did a shrink DB from SEM and selected the log file, it would say it shrank it successfully but the file size was still 120GB. How can I shrink it so that file size goes from 120GB to the 2GB and the OS can reclaim the space so the hard disk doesn't appear to be full?Thanks again for all your help. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-08-11 : 14:25:43
|
| Yes for the INDEXDEFRAG using less log space. That is mentioned in the microsoft article in the blog post of mine that I posted.Try a DBCC SHRINKFILE to shrink the tlog file. It'll take a very long time to shrink a file of that size down to 2GB though. You will need to do this in a maintenance window.I've never had log shipping failures when the dbcc dbreindex was running. The Microsoft guy is probably right though. I'm sure it is documented in BOL if he is right, I just haven't seen it or I don't remember it. It's not a big deal for us though since we'll just back it up in the next 15 minutes if it fails.Tara Kizer |
|
|
|
rspinell
Starting Member
36 Posts |
Posted - 2006-08-11 : 14:52:24
|
| Tara, thanks for the great info, it’s appreciated.Have a good weekend. |
|
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2006-08-11 : 15:33:31
|
| We have the same problem. Reindex on Sunday night means the log file (shipped every 30 mins 8 a.m. - 2 a.m.) is huge on Monday. We've got our reindexing spread out over 2 weekends now. Using the week number from date part if it's even one set of tables gets reindexed, odd a different set. Made a huge difference. Also reconsider which tables you're reindexing. If the data doesn't change very much, it's not worth it.Mike"oh, that monkey is going to pay" |
|
|
|
rspinell
Starting Member
36 Posts |
Posted - 2006-08-11 : 16:50:27
|
| How would I go about setting a reindex using the odd / even scenario ? Thanks |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-08-11 : 16:54:45
|
quote:
Originally posted by rspinell
How would I go about setting a reindex using the odd / even scenario ? Thanks
You'd have to setup custom reindexing. You can't use the maintenance plan to do this as it does it on all indexes.Tara Kizer |
|
|
|
rspinell
Starting Member
36 Posts |
Posted - 2006-08-14 : 16:52:52
|
| Does anyone know why I can't just use bulk logged recovery mode on my DB that i'll be log shipping? I set it up last night and everything seems to be working fine. What do I lose by doing this ? I'm also running a DBCC DBREINDEX job on a test server that has a copy of my production 125GB DB, and the log file isn't growing gigantic like it did when I had Full recovery model set.Thanks |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-08-14 : 17:27:20
|
| For log shipping, you can use BULK-LOGGED or FULL recovery models. You just can't use SIMPLE. Please see SQL Server Books Online for details on the difference between BULK-LOGGED and FULL recovery models.Tara Kizer |
|
|
|
rspinell
Starting Member
36 Posts |
Posted - 2006-08-14 : 18:04:43
|
| Hey Tara, I read through the BOL, I guess it goes back to my original question, that this thread started on. If I keep my DB always in bulk-logged recovery instead, and do a reindex on the source, the 100GB log file won't be created correct ? Wouldn't this be a solution where I can do log shipping, do a full reindex once a week, and not have a large 100GB transcation file be created that then needs to be shipped to my target server ?Thanks for the help. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-08-14 : 18:06:04
|
| You will need to test to see how big your tlog file will get.Tara Kizer |
|
|
|
rspinell
Starting Member
36 Posts |
Posted - 2006-08-15 : 21:25:58
|
| Tara, the .trn file that used to be over 100GB was about 600mb. I did a reindex using the maintenance job and my DB is in bulk-logged mode. I'm trying to figure out what I lose by keeping it in bulk-logged and log shipping it ? I can reindex my source DB, my transcation file is small and it looks like all my data is getting over to my target. Do you see any issue with the way I have it setup ? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-08-16 : 12:39:19
|
| You need to read about BULK_LOGGED recovery model in BOL. It explains what you won't be logging as compared to FULL. Both recovery models don't impact what data gets sent to the destination server. It has to do with what you can recover from.Tara Kizer |
|
|
|
rspinell
Starting Member
36 Posts |
Posted - 2006-08-17 : 06:37:04
|
| Sounds good. I think bulk-logged is the solution for log shipping in my case. I don't really care about recovery since the target server is only used as a replica for people to runs queries off of instead of hitting our SMS production server. We do backups everynight of the master server, and SMS DB recovery mode is set to simple by default.Thanks for all the advice.Rob |
|
|
|
|
Log Shipping / Reindex