none
How to reduce the Catilog size in sql server 2008 RRS feed

  • Question

  • Hi all,

     Is There any possibilitys are there to reduce the Catalog sizes (it shows in SQL Server 2008 as NDF files) actually in my production database have fulltext catilogs two and one MDF file , my MDF File size is less than the ndf files. is there any possibilities are there to reduce the size of NDF files?

    Thanks,

    Prasad.

    Wednesday, June 16, 2010 4:27 AM

Answers

  • Hi Prasad,

     

    Before we reduce the size of the file, we have to see if target file has space to be freed. Please follow the steps to retrieve the space information:

    1.       In “Object Explorer”, right-click database which you would like to shrink

    2.       In the context menu, please select “Reports | Standard Reports | Disk Usage”

    3.       In the report of “Disk Usage”, click “+” in the front of “Disk Space Used by Data Files” in the bottom of the report

     

    Normally, if files of database have free space and we want to release these space to operating system, we could use “DBCC SHRINKFILE” to reduce the size of the file.

     

    “DBCC SHRINKFILE” is used to release free space of specified file of specified database. Following statement demonstrates how to use it:

    DBCC SHRINKFILE (<file logic name>)

     

    We could get file’s logic name by using the following statement:

    SELECT name as [logic name], physical_name FROM sys.database_files

     

    For more information about “DBCC SHRINKFILE”, you could refer to this article in Microsoft’s website:

    DBCC SHRINKFILE (Transact-SQL): http://msdn.microsoft.com/en-us/library/ms189493.aspx

     

    If anything is unclear, please let me know.

    Friday, June 18, 2010 9:38 AM
    Moderator

All replies

  • You can try archiving unwanted data from those tables which are pointing to ndf files, and delete the same from main file.
    Phani Note: Please mark the post as answered if it answers your question.
    Wednesday, June 16, 2010 7:44 AM
  • HI Phaneendra babu, thanks for your fast response,

    actually i was move the data from that tables but Catalog sizes was not reduce, give me some idea how to reduce it after move the data and which queries are need to run for this?

    Thanks,

    Prasad R

    Thursday, June 17, 2010 1:46 PM
  • Hi Prasad,

     

    Before we reduce the size of the file, we have to see if target file has space to be freed. Please follow the steps to retrieve the space information:

    1.       In “Object Explorer”, right-click database which you would like to shrink

    2.       In the context menu, please select “Reports | Standard Reports | Disk Usage”

    3.       In the report of “Disk Usage”, click “+” in the front of “Disk Space Used by Data Files” in the bottom of the report

     

    Normally, if files of database have free space and we want to release these space to operating system, we could use “DBCC SHRINKFILE” to reduce the size of the file.

     

    “DBCC SHRINKFILE” is used to release free space of specified file of specified database. Following statement demonstrates how to use it:

    DBCC SHRINKFILE (<file logic name>)

     

    We could get file’s logic name by using the following statement:

    SELECT name as [logic name], physical_name FROM sys.database_files

     

    For more information about “DBCC SHRINKFILE”, you could refer to this article in Microsoft’s website:

    DBCC SHRINKFILE (Transact-SQL): http://msdn.microsoft.com/en-us/library/ms189493.aspx

     

    If anything is unclear, please let me know.

    Friday, June 18, 2010 9:38 AM
    Moderator