SQL index and maintenance plan
Hi Experts,
I know something about SQL but I am not a Guru.
So I have a question about indexing with SQL databases.
After the weekend always our log file is very big. Grows up to 80GB.
The DB is nearly 100GB.
During the week the log backup works fine and log file is filled with 1% not more.
After the weekend the logfile is again 80GB and filled with 1%.
I have seen we have a maintenance job running which recreates the indexes.
What can I do, not to have so big logfiles ?
How bigger companies are doing this ?
I know something about SQL but I am not a Guru.
So I have a question about indexing with SQL databases.
After the weekend always our log file is very big. Grows up to 80GB.
The DB is nearly 100GB.
During the week the log backup works fine and log file is filled with 1% not more.
After the weekend the logfile is again 80GB and filled with 1%.
I have seen we have a maintenance job running which recreates the indexes.
What can I do, not to have so big logfiles ?
How bigger companies are doing this ?
SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
our backup of the transactions logs is each 4 hours.
The file stays the size of 50GB but it is empty or used with 1%.
After the weekend and the recreation of the indexes it grows to 50GB or more.
What can I do about the indexes and the recreation ?
The file stays the size of 50GB but it is empty or used with 1%.
After the weekend and the recreation of the indexes it grows to 50GB or more.
What can I do about the indexes and the recreation ?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
...so is it not a problem to have a transaction logfile which is 50GB and 1% used ?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
So in short modify your maintenance plan and add a TLog backup followed by a log shrink right after completion of your index rebuild.
I would never recommend scheduling a DBCC SHRINKFILE(). All you are causing is massive Transaction Log fragmentation. But don't take my word for it, just do it for a short while and then check DBCC LOGINFO(), if you get back more than 50 entries that is usually signs of fragmentation.
I would never recommend scheduling a DBCC SHRINKFILE(). All you are causing is massive Transaction Log fragmentation. But don't take my word for it, just do it for a short while and then check DBCC LOGINFO(), if you get back more than 50 entries that is usually signs of fragmentation.
After the weekend and the recreation of the indexes it grows to 50GB or more.
Why are you recreating the indexes. You should only be reindexing/reorganizing the ones that are required. You were given the solution to this earlier on here.
What can I do about the indexes and the recreation ?
Don't recreate all your indexes.
Why are you recreating the indexes. You should only be reindexing/reorganizing the ones that are required. You were given the solution to this earlier on here.
What can I do about the indexes and the recreation ?
Don't recreate all your indexes.
The only advantage of shrinking the unused space in this case is that your full backups will probably be smaller, specially if your backups are not compressed.
If you are referring to shrinking the Transaction Log, then there is no correlation between doing this and the size of full backups. In case I was not clear before, there is no reason to schedule a DBCC SHRINKFILE() ever. If you are doing it, that would be because you are (hopefully) unaware of the consequences and don't realize there are far better methods of keeping the Transaction Log size down.
If you are referring to shrinking the Transaction Log, then there is no correlation between doing this and the size of full backups. In case I was not clear before, there is no reason to schedule a DBCC SHRINKFILE() ever. If you are doing it, that would be because you are (hopefully) unaware of the consequences and don't realize there are far better methods of keeping the Transaction Log size down.
Is this online maintenance or offline? If offline, I would suggest taking backup, disabling logging and then running utilities/maintenance jobs. If online, I would suggest considering rebuild over reorg, or performing backup as soon as log gets full so that the size is again controlled.
Thanks,
Sam