question

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.