I have this piece of code which helps me rebuild indexes if they are fragmented to a certain percentage. Reorganize the data on the data and index pages by rebuilding indexes with a new fill factor. No, there is no auto-magical defragging of indexes. We need to drag and drop tasks from the Toolbox (to open the toolbox use Ctrl + Alt + X) into the gray workspace area on the bottom as shown in the below screenshot. ”. dm_db_index_physical_stats (under the Examples -> D section: Using sys. What works for me may not work for you. For that plan we will break those tables into seven groups and everyday run script against. Select the Compact large object column data checkbox to specify that all pages that contain large object (LOB) data are also compacted. Microsoft recommends Index Rebuild operation to defrag indexes if the fragmentation level of your index is greater. is_ms_shipped = 0 --Excludes any objects created as a part of SQL Server installation AND ss. Product REORGANIZE GO. LDAP_ENTRY. dm_db_index_physical_stats function is the preferred tool to use to check index fragmentation on any CA or DA database table. You cannot say, “If the index is 45% or more fragmented, rebuild it– otherwise do nothing. failed with the following error: "The index "SpatialIndex-20180705-165942" on table "extended_index_113435478_384000" cannot be reorganized because page level locking is disabled. Create a job to run your index reorganize ('Reorganize'). the year and month columns. 4) Move database back to full recovery mode. The algorithm to build an index from scratch is different to that which maintains it as data arrives so these non-clustered indexes will be unfragmented too. You can go the easy way and use SQL Server Maintenance Plans, but unfortunately they’re very simplistic: you can only say “rebuild all the indexes” or “reorganize all the indexes”. Everything is the same - configuration, CPU, RAM, disk drive layout, similar (but not the same) database with similar data and workload. dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats INNER JOIN sys. You can also see if a reorganize will help until you can do a full rebuild. For example, one of the indexes with a page_count of 967 has a fragmentation percentage. This option will rebuild your indexes so that the data is laid out in a more efficient manner when queries are run to use the data. If the index you wish to reorganize is not listed in the “Indexes to be reorganized” section, ensure that it has been added to this section and then click the “OK” button. Note: You can select “Reorganize All” to reorganize all the indexes in the table. To correct index fragmentation, you can reorganize an index or rebuild an. It is causing other queries to wait, even simple ones like: SELECT * FROM tableName WHERE indexedColumn = @value. For us to perform this test we will first create a simple test table with a few columns and load a fair amount of data into it. All you might notice is a drop in performance executing queries while the indexes are gone but dropping and creating a (none-clustered) index has no impact whatsoever on the actual data stored in your tables (Creating a clustered index impacts the physical ordering of your data but again, no data. It has an IF condition for the Reorganize but i don't need it, i need to modify the code so it queries indexes of a certain database and schema and rebuilds only if the fragmentation is bigger than 5%. In order to resolve the index reorganization issue, we will enable the row and page level locking by altering the index as shown below: USE MSSQLTipsDemo GO ALTER INDEX [PK_Product] ON [Production]. Therefore, focus on the larger indexes because their pages are less likely to be cached by SQL Server. We recently switched to Ola Hallengren's maintenance script and automated the deployment of MaintenanceSolution. در این مقاله روش بهینه سازی ایندکسها با استفاده از reorganize و rebuild کردن ایندکس تکه تکه شده (Fragmented Index) با استفاده از محیط SQL Server Management Studio و Transact-SQL شرح داده خواهد شد. If a nonclustered index is being rebuilt, a shared lock is held on the table in question during the operation. The REORGANIZE modifier for ALTER INDEX is not currently supported by Azure SQL. Don’t shrink your database files just to free up drive space. Additionally, after reorganizing indexes it is reasonable to update the statistics as this operation does not update the statistics like the index rebuild operation. Index automation Job script. Ignore: Fragmentation levels of 10 percent or less should not pose a performance problem, so you don’t need to do anything. Best regards,. [Test] REBUILD WITH (ONLINE = ON); Additionally, I would only rebuild indexes which require it. This tool provides index analysis to manage index defragmentation, including rebuild and reorganize fragmented indexes . 2. 3 and a half hours later, it's not finished. Resolution: Reorganize and rebuild indexes. The “Reorganize” index option is more facilitated than the “Rebuilt” index. I have many DBs that are currently used for insert and delete. Right-click the table on which the full-text index is defined, select Full-Text index, and on the Full-Text index context menu, select Properties. In an MS-SQL Server. Provide a name for your maintenance plan and click Next to choose the tasks we want to include in our maintenance plan. Note: You can select “Reorganize All” to reorganize all the indexes in the table. Below the last two rows that displays the fail: Source: Reorganize Index Executing query "ALTER INDEX [PK_xxTableName] O. It defragments the leaf level of clustered and nonclustered indexes on tables and views by physically reordering the leaf-level pages to match the logical, left to right, order of the leaf nodes. If you have fragmentation, you need to REBUILD or REORGANIZE. The simple reason which comes to my mind is rebuild should be done only when index is fragmented. Here is the image for additional clarity. TEST_INDX(id) WITH (DATA_COMPRESSION = PAGE);Index Reorganize/ Rebuild Time. But if you need to change something about an index (that. Add a comment. The following index operations require no additional disk space: ALTER INDEX REORGANIZE; however, log space is required. I want to reorganize or rebuild indexes. Start SSMS and connect to the SQL Server database engine. It is also recommended to read the previous article of this series - Rebuild Index Task before reading this one. I used this approach to improve performance and it worked perfectly for a long time. Yes, rebuilding indexes will take a toll on your transaction log file. This can be checked using:. In the IndexOptimize procedure, you can define a preferred index maintenance operation for each fragmentation group. SQL Server 2016 (13. The purpose is to reduce the size of database and increase the db performance. 2 contributors. x) and SQL Server 2014 (12. When using columnstore indexes, the delta store may end up with multiple small row groups after inserting, updating, and. I have an index on a . Plan B is fine. Summary: SharePoint Server uses SQL Server to store most of the content for the Web site and configuration settings. Instead of running rebuild index on whole database we are planning to run it against each table. The Reorganize Index task encapsulates the Transact-SQL ALTER INDEX statement. It may also take longer to REORGANIZE a highly fragmented index than to REBUILD it. So far, all good. My predecessor created a Maintenance Plan with 4 tasks: CHECKDB; SHRINKDATABASE (N'DB1',10,TRUNCATEONLY); it seems to be running a REORGANIZE on all Indexes for all Tables and Views and "Compact Large Objects" is checked; and then it UPDATE. The query result is matched against the full-text index. Q23: What is the difference between index Rebuild and Index Reorganize operations? Index fragmentation can be resolved by rebuilding and reorganizing SQL Server indexes regularly. This requires the DBA to create such a maintenance job. index rebuild/reorganize frequency. You can go the easy way and use SQL Server Maintenance Plans, but unfortunately they’re very simplistic: you can only say “rebuild all the indexes” or “reorganize all the indexes”. 1. This opens the Toolbox and the maintenance_plan_name [Design] surface with the Subplan_1 subplan. When rebuilding your indexes, be sure that you allocate enough memory to the session that rebuilds your index. The scripts has some cool features like. Same with updating the stats first and then rebuilding. The query causing the block is in the format: ALTER INDEX [indexName] ON tableName REORGANIZE. If you search for index rebuilding, you will find many complex scripts on the web, but this can also be done. This issue can be solved by reorganizing or rebuilding the index. @LeeWalters shrink is done, and yes, due to disk issues, not possible to add drives, that's why, in addition, db is populated with blob data, so the question is more from the index fragmentation side. It checks the SQL Server licences, if Enterprise it will rebuild the index online else it. Reorganize Pages with the Default Amount of Free Space—Drop the indexes on the tables in the database and re-create them with the fill factor that was specified when the indexes were created. You can do maintenance in phases, where each maintenance phase covers a subset of. This would also keep the original order of columns. 1. For more information, see ALTER INDEX (Transact-SQL). Custom SQL Server Index. It doesn’t work on the intermediate pages between the root and the leaf. dm_exec_requests to see how much longer your query has to finish. ALTER INDEX ALL ON table_name REBUILD OR. Well, I’d wager some of it is backwards compatibility. This job needs to be scheduled and ran on a weekly basis due to large data load. You can use sys. However I want advice whether it is required to setup the SQL Server Index and Statistics Maintenance scripts, because my SQL server is running on a SAN infrastructure and I have read that there is no benefit to setting fill-factor to less than 100%, or to perform index. This is a powerful feature that you can use to your advantage. Note that the time it takes to complete the operations depends on the size of the database and how fragmented the indexes. (About 1 TB of data including myIndex (non-clustered)). Which is the best method to reorganize sql server database. Index Rebuild operation first drops and then recreates the index. You could also refer another query which may be helpful to you. @LeeWalters shrink is done, and yes, due to disk issues, not possible to add drives, that's why, in addition, db is populated with blob data, so the question is more from the index fragmentation side. SQL Server ALTER INDEX Syntax. Our Production instance is running SQL Server 2014. Expand the Indexes folder. All they do is waste space and resources. ALTER INDEX index_name ON table_name REBUILD -- REORGANIZE. The maintenance plan. x) implemented major performance improvements for these index operations. Usually, you should rebuild the index if it has a fragmentation greater than 30% and reorganize it if it has less than 30% fragmentation. This method can help when. For example, we have a set of tables that support an application. Then, drag and drop Rebuild Index Task into the maintenance plan designer. sql to our customers's SQL Server instance. regarding reorganize - there is an exception to this - if you have columnstore indexes that are frequently subject to deletions or that are subject on inserts in small batches then running reorg. indexstats. Copy and paste the following example into the query window and select Execute. Similarly, removing fragmentation in a. For more information, see Specify Fill Factor for an Index. SQL Server doesn’t automatically correct index fragmentation problems. Reorganizing an index uses minimal system resources. When an index becomes skewed, parts of an index are accessed more frequently than others. This tip will explore two features to speed up SQL Server index and statistics maintenance. Default SQL Server value is 0 or 100%, which means that no free space should be left on each page. Share. Each night the maintenance plan is successful, but these commands take the longest to execute 3 hours, 3 hours and 5 hours respectivelly. Columns with text, image, ntext, varchar (max), nvarchar (max) and varbinary (max) cannot be used in the index key columns. we are using Ola index job to rebuild and reorganize the index. ) Reorganize = fragmentation level is between 5% and 30%. As part of query execution, the Full-Text Engine might receive input from the thesaurus and stoplist. Yup, that is one perfectly valid way. From all the research I've done, I can't really find any indication of why you would want page fullness to be low, and am anticipating that I'll want to do an index reorganize operation to set the value to. #1637994. Reorganizing an index only deals with the fragmentation that exists, and the more fragmentation there is, the longer a reorganize will take. Server Configuration: MS SQL Server 2008 Enterprise Windows Server. Burt King. Specify the name of the maintenance plan. As visible in the above image, REBUILD and REORGANIZE are the two available choices to play out the trim operation over the page. dm_db_index_physical_stats to get information about your index fragmentation (see the avg_fragmentation_in_percent column). When I run alter index index [IndexName] on dbo. deteriorating. Script and result below: ALTER INDEX ALL ON Fragmented REORGANIZE GO1 Answer. You can query the sys. Note that the time it takes to complete the operations depends on the size of the database and how fragmented the indexes. Just go to the table, further expand the indexing folder and right after that you can right-click on it and select the option to rebuild all the indexes. ” Select the vault database(s). It’s an unbelievably, disgustingly, repulsively bad idea. Largest table has around 500. – As well as maintenance plans, SQL Server Agent jobs are also a handy way to automate and schedule index defragmentation jobs in SQL Server. [Subscribers] REBUILD; You can replace REBUILD with REORGANIZE in the above query to. Mar 8, 2021, 2:47 PM. Also, some tables/indexes will hardly be fragmented. If you rebuild the index the stats are updated anyway, so doing it again is just wasteful. I know the sql to perform this action on all indexes in a table is. However, you can create a staging table insert all rows into that, drop original table and rename staging table to original. If page_count value( which is there in DMV sys. In Object Explorer, connect to an instance of Database Engine. In both these scenarios they are blocked by session 54 – the index rebuild. _in_percent > 50 AND ss. ALTER INDEX __NAME_OF_INDEX__ ON __NAME_OF_TABLE__ REBUILD. Click the plus sign to expand the Tables folder. You can also see if a reorganize will help until you can do a full rebuild. x) and in Azure SQL Database, we recommend using ALTER INDEX REORGANIZE instead of ALTER INDEX REBUILD for. To solve this, I want to use the REORGANIZE operation every time. This is a best practice for performance when you rebuild or reorganize indexes. As tables grow and shrink, this fragmentation can ultimately play a role in how well SQL runs. To create a new maintenance plan, right click on Maintenance Plan and select New Maintenance Plan and the following window will open. 1. The fill factor determines the amount of empty space on each page in the index, to accommodate future expansion. OBJECT_ID) AS TableName, ind. If the clustered index is being rebuilt, an exclusive table lock is held. To fix the fragmentation either rebuild or reorganize the index on the table. Rebuild/Reorganize working fine. - 1: The script will just output the index reorganization or rebuild commands without running them. -- Compute fragmentation information for all full-text indexes on the database SELECT c. Rebuild or Reorganize indexes Ask Question Asked 1 year, 1 month ago Modified 1 year, 1 month ago Viewed 1k times 0 I want to reorganize or rebuild indexes. I was going to take a look at Ola Hallengren's scripts and some other stuff but wanted to see if there was possibly a simple explanation for this. First, we will start the index reorganization in a session using the following T-SQL code. Hi Team, Today when I go through some of the SQL Server performance videos, found one interesting video (Please find the link below), where he said that rebuilding indexes is a very expensive thing and it will clear the cache every time an index rebuild happens, (It means that we indirectly killing the SQL Server everything we rebuild. skNumber) AS. The table can be in a local or a remote database. This sample T-SQL script performs basic maintenance tasks on SUSDB 1. Resolution: Reorganize and rebuild indexes. To estimate the duration of the REORGANIZE DATABASE you can use the SQL statement from the "Step 1" section in the attached "REORGANIZE estimator" . You can safely stop it but you need to find out why your database became slow. Note In SQL Server 2012 (11. Next the New Job window will open. To correct index fragmentation, you can reorganize an index or rebuild an. The default value for this parameter will be ‘CATALOG’. Product REBUILD. Creating a SQL Server Maintenance Plan. indexes ind ON ind. The following options are available on this page. Then you can do an alter index with the rebuild clause whenever your threshold is reached. Rebuilds are generally faster. Basically every hour I query the dm_db_index_physical_stats dynamic management view and if an index is between 5% and 30% fragmented I. Also, in earlier versions the granularity of control was less refined. No right from SQL Server 7. FileID, a. REORGANZE index - is for reducing fragmentation without index rebuild, so no drop and create. For more information about this enhancement, check the SQL Server 2016. Users(DisplayName) WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 1); Those parameters mean: ONLINE = ON means you’ve got the money for Enterprise Edition. On the right panel, right-click SQL Server Agent. However, each time I check (even immediately after recreating the indexes) the avg_fragmentation_in_percent shows 100%. We need to drag and drop tasks from the Toolbox (to open the toolbox use Ctrl + Alt + X) into the gray workspace area on the bottom as shown in the below screenshot. Reorganizing Indexes. Rebuilding basically creates an entirely new copy of the index, and is much more effective at reducing fragmentation - but this comes at a cost, both in terms of time and disk space. In the infrequent cases where you do need to reorganize or re-build index, consider these: Run index maintenance during off peak period. There needs to be an automated Index defragmentation job that will either reorganize or rebuild the indexes based on the fragmentation level for a specific table in a specific database. In the Object Explorer panel locate the database in question, and use right mouse click to bring up the context menu. Right-click the table on which you want to specify an index's fill factor and select Design. every 2-3 day if running maintenance once per day (night). This means loading the table and building all nonclustered indexes before moving on to the next one. インデックスの断片化の状態がわかったら、それによってインデックス (index) を再構成 (Reorganize)するか、または再構築 (Rebuild) するか決定します。. A clustered index's key columns are called a clustering key. Create table and compressed index. 3) Reorganize indexes. The Reorganize Index task encapsulates the Transact-SQL ALTER INDEX statement. The reason given is that the indexes and statistics are partly based on available CPU and memory resources. You need to do more research but basically, reorganize as often as needed to keep your fragmentation under 20-30% until you can rebuild it during off-hours. Reorganizing an index defragments the leaf level of an index by physically re-ordering pages to match the logical order. These are two different modes to remove index fragmentation. Reorganizing queue indexes. index_id = 0 -- Heap or table indexstats. [myTable] REORGANIZE WITH ( LOB_COMPACTION = ON ) I have the above query running for 16 days (still running), the table is a dummy table used for benchmark tests, it has over 10 Billion rows. 0. column_name DISABLE; ALTER INDEX. The following index operations require no additional disk space: ALTER INDEX REORGANIZE; however, log space is required. You can always update statistics on their own. Therefore, you do not need to update statistics after performing ALTER INDEX REBUILD, DBCC DBREINDEX, DBCC INDEXDEFRAG, or ALTER INDEX REORGANIZE operations. Our Production instance is running SQL Server 2014. do nothing; reorganize an index; rebuild an index; Don't reinvent the wheel - just go see and use the script!I am a new DBA to a SQL 2005 production Report server. They are also configured from SSMS. If the clustered index is being rebuilt, an exclusive table lock is held. Otherwise you're holding an exclusive lock on the table/partition before you start the reorganize. Index reorg only shuffles around leaf-level pages of your index and will try to compact those - but it doesn't completely rebuild the index structure. And if it is a clustered index, the entire table is copied. do nothing; reorganize an index; rebuild an index; Don't reinvent the wheel - just go see and use the script!Effective database performance tuning often relies on having precise database insights to determine actual root causes. When fragmentation is above 30%, rebuild the index. Navigate to Tasks / Shrink / Database. If you what you are saying is true, even reorganizing the indexes that have never been, may cause a larger impact on the server as well. Using above query, you could also query the progress of backup,restore,dbcc command and so on. I don't think Windows SQL Server Maintenance has this option yet. Step 1. No – alter index rebuild or reorganize. Once you select that option it will bring up the following screen. This means every time we need to scan the. REORGANIZE statement. To add to this, you need to learn your system and how it's used. To create SQL database maintenance plan, launch SQL Server Management Studio > expand the database instance > Management > right-click on the maintenance plan > New Maintenance Plan. Change it to be weekly or even less frequently. My problem is that the reorg is running for a very long time. ALTER INDEX __NAME_OF_INDEX__ ON __NAME_OF_TABLE__ REBUILD. Let’s first drop the Clustered Columnstore index that we created above using the below command. The SQL Server instance should have enough memory available to hold the largest table and perform the largest nonclustered index sort at the same time. Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) Poorly designed indexes and a lack of indexes are primary sources of database application bottlenecks. Rebuilding an index means that a whole new set of pages is allocated for it. If you choose to compact large object data, the statement uses the. Such indexes (fragmented) can lead to slow application response and decrease query performance. To work around this, try the following methods: Method 1 ( recommended ): Limit the number of full-text indexes in the same catalog. I think there is another way to rebuild fragmented index, You can create an sp and scheduled it via SQL Server Agent or via task scheduler. SQL Server 2005 Index Rebuild/Reorganize. When you rebuild, SQL creates a new fresh index. The detailed information can also be useful to determine whether your dedicated SQL pool is being impacted by a large number of small, fragmented tables, which can lead to delays in compilation. When you reorganize the index, you go through the existing index, cleaning up blocks for deleted records etc. Hallengren website has an option to reorganize indexes say with 5% Fragmentation, and rebuild for 30% Fragmentation. Ignore anything with less than 15-20 pages. The import process should be reworked to bulk-load one table at a time. Reorganizing only works on the leaf pages. Yup, that is one perfectly valid way. You can also change this threshold via parameters. 1. e. IndexOptimize provided by Ola Hallengren. Reorganizing the indexes will take less time, and less effort from the SQL server thus they can be done in a weeknight type of instances. This job needs to be scheduled and ran on a weekly basis due to large data. Hi, The widely used thresholds are: Reorganize if fragmentation is between 10-30%. In the Select Maintenance Tasks page, select the following tasks: Reorganize. create table dbo. For a dedicated SQL pool table with an ordered CCI, ALTER INDEX REBUILD will re-sort the data using tempdb. In SQL Server, you "might" run into issues with "updating primary key". USE AdventureWorks2022; GO -- The following example updates the statistics for all indexes on the. Expand the table on which you want to reorganize an index. Speaking for SQL Server, I tend to choose a index size and index fragmentation level at which point I begin performing index maintenance. The sys. REORGANIZE is done within the confines of what is currently in the indexes insofar as pages goes. 5) Perform full database backup. This procedure automatically chooses whether to rebuild or reorganize an index according to its fragmentation level, among other parameters, and update statistics with a linear threshold. However I want advice whether it is required to setup the SQL Server Index and Statistics Maintenance scripts, because my SQL server is running on a SAN infrastructure and I have read that there is no benefit to setting fill-factor to less than 100%, or to perform index. Index Fragmentation and out of date statistics! Don't forget stats, it's one of the most important factors in SQL Server deciding whether or not to use a index. On a data partitioned table, you can reorganize a specific nonpartitioned index on a partitioned table, or you can reorganize all the partitioned. dm_db_index_physical_stats ‘ Dynamic Management View (DMV). dm_db_index_physical_stats) is <1000 you don't need to rebuild or reorganize such. Rename. We leave default values here as well. REORGANIZE, UPDATE STATISTICS and a simple SELECT statement. Specify the name of the maintenance plan. Use the online version of the Rebuild Index task ; Use the Reorganize Index task followed by the Update Statistics task; If you have the Enterprise Edition of SQL Server, the Maintenance Plan Wizard offers a Keep index online while reindexing option, which means that the index will continue to be available to users even while it is being. The nonclustered B-tree index is updated as changes occur to the columnstore index. If you read complete article it was trying to point out the commands or operations in SQL Server which would require additional disk space and others that would not. When rebuilding offline your index is completely unavailable, but the operation is faster than that online. The database is using the Simple Recovery model. On the Table Designer menu, click Indexes/Keys. All nonclustered indexes will include the clustered key by default, in order to perform lookups when necessary. The maintenance plan. For example, LDAPDB2. ALTER DATABASEdb_name SETREAD_COMMITTED_SNAPSHOT ON go Alter index Index_name on table_name. You will often see advice to perform a REBUILD on highly-fragmented indexes, rather than a REORGANIZE-- This is because rebuilding from scratch can be more efficient. Create a maintenance plan. We don't want to run index optimization on multiple databases at the same time The code uses stored procedure dbo. Reorganizing also compacts the index pages. 4. Rename. Full-Text Engine. Right-click the index for which you want to set the max degree of parallelism and select Properties. May 5, 2010 at 8:00 am. Your disk drive space is for files, not for ornamentation. Copy and paste the following example into the query window and click Execute. fulltext_index_fragments GROUP BY OBJECT_NAME([table_id]) HAVING COUNT([fragment_id]) >=30 Also,. I have been advised by a contracted SQL Server expert that, after any change in # of CPUs and/or available memory, I should reorganize all indexes and then update all statistics or SQL Server will not make the full use of the new resources. avg_page_space_used_in_percent column in the sys. The second part of this tip is intended to reduce the duration for update statistics as it pertains to both. dm_db_index_physical_stats DMV to identify the fragmentation. SQL Server does not maintain when an Index was last rebuild, instead it keeps information when stats were last updated. It shouldn't be used on. Beginning with SQL Server 2016 (13. g. Find and remove unused indexes – everything that is unused doesn’t do anything good. Depending on the type of index and database engine version, a rebuild operation can be done online or offline. You also can create a linked server to SQLAZURE and create a sql agent job. Your new index will have the size non less than the size of disabled index. Monitor tempdb during rebuild operations. Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) Columnstore indexes are the standard for storing and querying large data warehousing fact tables. As well as maintenance plans, SQL Server Agent jobs are also a handy way to automate and schedule index defragmentation jobs in SQL Server. To reorganize the indexes and table in the database, run the appropriate command that is based on your requirement: To reorganize the tables with an asterisk in the last column, run the following command: db2 reorg table table_name. I will read through them and try implementing them. If you need more tempdb space, scale up the pool. Any full-text indexes of non-trivial size with fragmentation of at least 10% will be flagged to be re-built by our over-night maintenance. An index rebuild will always build a new index, even if there’s no fragmentation. Dec 19, 2021, 9:55 PM. It cames in two flavours: online and offline. For maintenance i create a procedure that: -Shrink Database file (if is enabled) -Shrink Database log file.