This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(288, 74%, 37%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ER%3C/text%3E%3C/svg%3E)
Hi Team,
I am looking for Best approach and Best practice in managing VLDB in SQL Server. In my environment the Huge database size is 10TB with just 5 tables in it and its highly fragmented.
Regards.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(28.799999999999997, 2%, 12%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ESX%3C/text%3E%3C/svg%3E)
Hi @rizwanrangrez ,
We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.
Best regards,
Seeya
Hi @rizwanrangrez ,
In order to benefit all community members who are having this similar issue, please choose an answer to accept or vote for the answer you think is useful to you. Your contribution is highly appreciated.
Best regards,
Seeya
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(67.2, 1%, 16%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ETP%3C/text%3E%3C/svg%3E)
There isn't any "magic" based on the database size. You still have to do the same maintenance you do in every database.
If you are not already, I highly suggest you use Ola's scripts. https://ola.hallengren.com/
On large databases, I use the parameter "TimeLimit" to limit how long index maintenance runs. Be aware, this doesn't stop anything running, it prevents new things from starting after the TimeLimit is expired.
Hi @rizwanrangrez ,
Welcome to Microsoft Q&A!
Here is another Microsoft document for your reference.
Quick list of VLDB maintenance best practices
Best regards,
Seeya
If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(35.2, 54%, 13%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ELL%3C/text%3E%3C/svg%3E)
Hi @rizwanrangrez ,
in your case it seems that basics for VLDB and Maintenance by Ola Hallengreen won't be enaugh. Having 10TB of data requires a data structure that supports it.
I assumed that you have regular structured data without LOB data (like varbinary(max) or nvarchar(max)) and millions of rows. In this case it is mostly a solution to partition your data. With partitioning you split up the data in the table according to a key (i.e a date). Only the partitions having modified data need maintenance. All other partitions don't.
I have to mention, that partitioning cannot always be applied to existing tables. This highly depends on the workload and the existing data structure.
If you like to dig a bit deeper into partioning you can find hier a good resource.
Best regards,
Lars