| Author |
Topic |
|
M_N_M
Starting Member
21 Posts |
 Posted - 2011-10-27 : 05:32:45
|
| Hello,I have a few DB's who are running on recovery mode "full"(daily a full backup at night and hourly log backups are made)In the weekend, I do a maintenance with the following components:1. reorganize indexes2. update statistics3. shrink DBDue to the maintenance plan, my log files become VERY VERY big.My database is about 10Gb but my log file becomes after a maintenance more than 10Gb.I thought the space would be "freed" to the operation system after a log backup but that's not the case.Any (automated) solution to this.I know I can set it to recovery "simple", do a shrink and set it back to "full" but I think that is not "the" way of doing it."BACKUP LOG ... WITH TRUNCATE_ONLY" is also not available anymore in the new version of SQL Server so that can't help me to shrin my logs to an acceptable filesize.Thanks in advance! |
|
|
human12
Starting Member
5 Posts |
Posted - 2011-10-27 : 07:29:39
|
| Reorganizing indexes update statistics by default, so u dont have to update the statistics if you reorganize indexesDon't do normal shrinkage, it will reallocate pages, do the truncate shrinkage first use TRUNCATE_ONLY with shrinkageUse the SORT_IN_TEMPDB for indexing, this will lessen your transaction log, as it will do the job in the tempdbTarek OmarTechnical Manager and Database Administratorhttp://intosql.blogspot.com |
 |
|
|
M_N_M
Starting Member
21 Posts |
Posted - 2011-10-27 : 12:37:52
|
| Hi,- thanks, I didn't know a "reorganize index" did also "update statistics", are you sure of this? (I thought that only "rebuild index" would do that?)- TRUNCATE_ONLY doens't work anymore in SQL 2008 R2 - can you explain "SORT_IN_TEMPDB" ? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-10-27 : 13:26:55
|
| Reorganise index will NOT update statistics. Reindex will, but only the statistics for the index, not the non-index statistics.Don't shrink the database. That will move all the carefully rebuilt, contiguous, indexes pages back to whatever free pages can be found, fragmenting the index and database.Increase backup frequency during the Rebuild / Reorganise index "window" - we backup log every 2 minutes during reindex as it is the biggest log-file usage task we have, and otherwise the LDF file is extended.Probably better you backup Transaction Log every 15 minutes normally, rather than every hour. Same total size of backups per day (except from a little extra overhead), but more files. Maximum data loss is 15 minutes, but also far less chance of a numebr of "heavy" transactions filling the LDF file cad causing it to be extended. |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-10-27 : 14:10:54
|
| Reorganize will not rebuild statistics...The reason?? Reorganize works on a set of index pages and may not know the statistics of the same index say 10 pages back.Also remember a reorganize will only work on the leaf nodes of an index.A rebuild on other hand builds the index from scratch.DO NOT shrink any of the DB files regularly.If your log files are getting big during regular maintenance one thing you could do is put the DB in bulk logged recovery model and then run the tasks and then bring it back to full recovery model.PBUH |
|
|
|
M_N_M
Starting Member
21 Posts |
Posted - 2011-10-28 : 01:31:20
|
| hello,I did the following:- don't do shrink anymore in my maint plan- set log backups every 3 minutesI tried it on a smaller database but still the same result :-(MDF-file = 2GbLDF-file = 5Gb (before I set the DB to "simple" and shrink so the log was small before executing the maintenance)Questions: * just to make sure (after a while, you start doubting everything..), my log backups are made without options, so just with the following command "BACKUP LOG .... TO DISK ....." ==> is it ok??* if I try it out to change my recovery mode to "bulk logged" during the maintenance; do I have to execute a "full" backup after finishing the maintenance (and going back to "full" recovery) or is it not necessary?Thanks. |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-10-28 : 02:57:48
|
| Make sure that you take a log backup before you change to BULK and take one more log backup after you change it FULL.PBUH |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-10-28 : 02:58:59
|
| Also what is the Autogrow set for the log file ?PBUH |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-10-28 : 05:20:00
|
"just with the following command "BACKUP LOG .... TO DISK ....." ==> is it ok??"If you use the same filename each time then you are appending - it would be better to use different filename each time (e.g. including Date and Time in the filename)I use:BACKUP LOG MyDatabaseName TO DISK = 'x:\MyBackupPath\MyDatabaseName_yymmdd_hhmm_TRANS.BAK'WITH STATS = 10 |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-10-28 : 05:22:06
|
| "LDF-file = 5Gb"What is the size of the largest Backup file that was created during the index rebuild process? If it is not close to 5GB then it is something other than the index rebuild that is causing the growth I reckon - such as the Autogrow setting as Sachin said. |
|
|
|
M_N_M
Starting Member
21 Posts |
Posted - 2011-10-28 : 05:46:45
|
| * autogrow is set to 10MB* if you use "TO DISK = 'x:\MyBackupPath\MyDatabaseName_yymmdd_hhmm_TRANS.BAK'" => will the "yymmdd" auto interpreted or do I have to use a variable?* largest log backup file during maintenance is 4.xx Gb |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-10-28 : 05:50:36
|
"will the "yymmdd" auto interpreted or do I have to use a variable?"No, I'm afraid you will have to do that programmatically. Would be nice if SQL did if for you, I agree  "largest log backup file during maintenance is 4.xx Gb"OK, well that at least ties in with the 5GB size. Are the backup files every 3 minutes, as per your maintenance plan?Or is there jsut one file? (In which case it is all the backups appended, which suggests that the log is not being reused - maybe you have a stuck transaction ... |
|
|
|
M_N_M
Starting Member
21 Posts |
Posted - 2011-10-28 : 06:11:05
|
| - ok I'll do it programmatically- every 3 minutes a log backup (first log backup was 4.xxGb, second was 1.xxGb and after that (= maintenance finished) the logs are back to a few Kb's/log) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-10-28 : 06:34:53
|
| "first log backup was 4.xxGb,"Is it possible that the Index rebuild started before the every-3-minute log backups?If not then I expect that one table has rebuilt the index under a single transaction (I don't know if that is how it works though - but if so I doubt there is any way around it, although using Index Reorganise for the large table may well allow backups during the reorganisation) |
|
|
|
M_N_M
Starting Member
21 Posts |
Posted - 2011-10-28 : 06:47:06
|
| I don't do a "reindex", only "reorganize + update stats".I just tried the "bulk logged" mode and that did the trick!My log is now about 50Mb instead of 5Gb!! |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-10-28 : 08:07:26
|
quote:
Originally posted by M_N_M
I don't do a "reindex", only "reorganize + update stats".I just tried the "bulk logged" mode and that did the trick!My log is now about 50Mb instead of 5Gb!!
Great...Any particular reason why you dont want to do a reindex ?PBUH |
|
|
|
M_N_M
Starting Member
21 Posts |
Posted - 2011-10-28 : 08:58:39
|
| Our production server is 24/7 in use, only on holidays there is no activity.As far as I know, a reindex needs to be done without users logged in (otherwise they wait until it's finished and that takes a long time)A reorganize can be done while the users are logged in so we chose to do that on a regular basis. Reindexes are planned on holidays (no fixed scheme)Btw: I notice most of the performance gains by using the "update statistics". |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-10-28 : 10:15:38
|
quote:
Originally posted by M_N_M
Our production server is 24/7 in use, only on holidays there is no activity.As far as I know, a reindex needs to be done without users logged in (otherwise they wait until it's finished and that takes a long time)A reorganize can be done while the users are logged in so we chose to do that on a regular basis. Reindexes are planned on holidays (no fixed scheme)Btw: I notice most of the performance gains by using the "update statistics".
No that's not true.Starting SQL 2008 you can rebuild an index with an option named ONLINE means an index is not dropped until an exact new copy of it is created and the data access of the users are not affected in the process.Also an reorganize is not gonna help in case of the tables where fragmentation if beyond 40% .PBUH |
|
|
|
M_N_M
Starting Member
21 Posts |
Posted - 2011-10-28 : 11:01:25
|
| oh thanks for the tip! (we started with SQL 2005 and recently upgraded)Is there a true performance boost when you plan a reindex weekly?Most of our customers don't have big databases (a few GB after a few years) so I guess they don't need that daily.Some others have about 1Gb extra data / month, maybe there it can be good to add it to the maintenance. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-10-28 : 11:32:08
|
| "Starting SQL 2008 you can rebuild an index with an option named ONLINE "Requires the Enterprise Version though?We use REORGANISE for large tables. Smaller amount of data twiddling, usually, and comparison reports I have seen suggest that there is little, if any, improvement in performance for REINDEX compared to REORGANISE.We rebuild indexes each night, but only for tables that have fragmentation over a cutoff limit - that way badly fragmented indexes don't have to wait until the weekend before being rebuilt, and we don't waste time / log space rebuilding indexes that are not sufficiently fragmented |
|
|
|
M_N_M
Starting Member
21 Posts |
Posted - 2011-10-28 : 13:23:58
|
| pff, it worked for one database but now i'm trying it on another and over here, I keep getting a very big log file.This is my script:ALTER DATABASE xxx SET RECOVERY BULK_LOGGEDEXEC sp_MSforeachtable @command1="print '?' DBCC INDEXDEFRAG ('xxx', '?')"EXEC sp_updatestatsALTER DATABASE xxx SET RECOVERY FULLis this correct?I'm starting to think that something in my script isn't good.I can't believe this is normal behaviour (log that becomes bigger than MDF after maintenance task) |
|
|
|
Next Page
|
SQL Server Maintenance