question

Aesl avatar image
Aesl asked

Rebuild Index Question - SQL Server 2012

Hi, According to the below query, the total size, occupied and free spaces of our project database (in SQLSERVER 2012) and data files are as below: select name , filename , convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB , convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)) as SpaceUsedMB , convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) as FreeSpaceMB from dbo.sysfiles a +----------+----------------------+-------------+--------------+---------------+ | name | filename | FileSizeMB | SpaceUsedMB | FreeSpaceMB | +----------+----------------------+-------------+--------------+---------------+ | Prod | S:\DATA\Prod.mdf | 19447.0 | 19446.4 | 0.6 | | Prod_log | L:\LOG\Prod_log.ldf | 42532.1 | 141.7 | 42390.3 | +----------+----------------------+-------------+--------------+---------------+ This query is for the count of indexes: SELECT file_name , count(*) from ( SELECT 'table_name' = OBJECT_NAME( i.id), i.indid, 'index_name' = i.name, i.groupid, 'filegroup' = f.name, 'file_name' = d.physical_name, 'dataspace' = s.name FROM sys.sysindexes i, sys.filegroups f, sys.database_files d, sys.data_spaces s WHERE OBJECTPROPERTY( i.id, 'IsUserTable') = 1 AND f.data_space_id = i.groupid AND f.data_space_id = d.data_space_id AND f.data_space_id = s.data_space_id -- ORDER BY f.name, OBJECT_NAME( i.id), groupid ) A group by file_name file_name Count S:\DATA\Prod.mdf 2665 The Prod.mdf properties are: +--------------+------------+------------------------------+----------------+------------------------+ | Logical Name | File Type | Filegroup Initial Size (MB) | Autogrowth | Maxsize | +--------------+------------+------------------------------+----------------+------------------------+ | Prod Rows | PRIMARY | 19447 | By 1 MB | Unlimited | | Prod_log | Log | 42533 | By 10 percent | Limited to 2097152 MB | +--------------+------------+------------------------------+----------------+------------------------+ All indexes have the SORT_IN_TEMPDB = OFF I believe according to the current available free spaces and autogrowth, if I rebuild the indexes, I will get not enough space error message. What is the best way to do this operation without any interrupt or possible error message? Shall I change the location of indexes to another data file or change the data file settings? Thanks for your answers in advance.
indexingindexeslog-file-size
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
JohnM avatar image
JohnM answered
Given that you have auto-growth enabled (although I would look at adjusting the growth rate on both the data & log files) and assuming you have enough drive space to allow for growth of the files, the database will grow when you do the index rebuild. NOTE: I cleaned up the formatting of your question just so that it's easier to read.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank You John, Your answer is clear to me and now I am confident to rebuild the indexes. To adjust the growth rate on data & log, as suggested by you, I need to read a best practice. I am also wondering what would be the best way to estimate the size of TempDB. Yesterday I increased the TempDB datafile from 512MB to 5120MB and TempDB Logfile from 1024KB to 1024MB. By monitoring the disk latency, I noticed a significant increase in the read and write time. Now I am looking for a way to determine the optimum size for Data, Log and TempDB files. Can you send me the link to any best practice or document about the optimum estimation of Database files size and other parameters? The current properties of our DB is attached.![alt text][1] Your help is highly appreciated. [1]: /storage/temp/3914-db-properties.png
1 Like 1 ·
db-properties.png (17.4 KiB)
Hi John, I did rebuild the large indexes (page_count) > 1000) with the high fragmentation (>30%), but in spite of my expectations, the disk latency got worse (increased). This could be related to the wrong size and autogrowth definition of the database files (data, log and temdb). The properties of our DB after rebuilding the indexes and update statistics: ![alt text][1] [1]: /storage/temp/3926-new-db-prop.png Is there any document explaining how to find the optimum size and setting for the TempDB and Logfiles size and autogrowth? Best Aesl
1 Like 1 ·
new-db-prop.png (16.0 KiB)

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.