locked
ndf file which we use for index getting full and shrink doesn't help RRS feed

  • Question

  • Hello All,

    we have database which has different filegroups and one mdf, one ldf, multiple ndf files. one of the ndf file we use to store for INDEXES. This ndf file shows space available ~50 G and size of this ndf file is ~120 G but when we try to shrink this free space is not getting free. 

    Tried to take shrink truncateonly but did not help.

    any thoughts?

    Wednesday, October 9, 2013 12:24 PM

Answers

  • This command did the trick:-- 


    USE [databasename]
    GO
    DBCC SHRINKFILE (N'filename' , 0, NOTRUNCATE)
    GO

    USE [databasename]
    GO
    DBCC SHRINKFILE (N'filename' , 0, TRUNCATEONLY)
    GO

    Thank you!

    • Marked as answer by SQL--DBA Wednesday, October 9, 2013 1:10 PM
    Wednesday, October 9, 2013 1:10 PM

All replies

  • Hello,

    See DBCC SHRINKFILE (Transact-SQL) => Troubleshooting => The File Does Not Shrink

    Is maybe an initial size of 120 GB defined for the file?


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, October 9, 2013 12:45 PM
    Answerer
  • This command did the trick:-- 


    USE [databasename]
    GO
    DBCC SHRINKFILE (N'filename' , 0, NOTRUNCATE)
    GO

    USE [databasename]
    GO
    DBCC SHRINKFILE (N'filename' , 0, TRUNCATEONLY)
    GO

    Thank you!

    • Marked as answer by SQL--DBA Wednesday, October 9, 2013 1:10 PM
    Wednesday, October 9, 2013 1:10 PM