Sql-server – Msg 1833, File ‘ABC.ndf’ cannot be reused until after the next BACKUP LOG operation

partitioningsql serversql-server-2008

I am having a Database.
The size of my database is near about 4GB(Only .mdf file).
In that database only few tables present which have the maximum data of database. One of them and the biggest table is Table1. This is a huge table means everyday near about 80000 data inserted and same no. of records deleted. There are 30 days * 80000 records present in that table. This table is used very frequently. Hence it takes time to query large data. So my development team decided to create Partition of same table. Table11 has four columns in primary key.

Primary Key Columns :

  1. ChequeDate Date
  2. RunNo Int
  3. SorterNo Int
  4. SequenceNo Int

We decided to partition the table on the basis of SequenceNo column.

I created a partition script and ran it which partitions an existing table. This works perfectly.
Next I run a "rollback" script to remove the partitioning. This also runs perfectly.

When I try to reapply partitioning on the same table it gives me the error mentioned in title. I found a manual solution that worked for me.

My problem is this process should run from a script only. No manual work should be necessary.

I assume that the problem is in the rollback script:

ALTER TABLE [dbo].[Table1] DROP CONSTRAINT [PK_Table1] with (move to [primary]);
DROP PARTITION SCHEME Table1PartSch
DROP PARTITION FUNCTION Table1PartFunc

ALTER DATABASE [MyDataBase] REMOVE FILE Table125LData;
ALTER DATABASE [MyDataBase] REMOVE FILE Table117LData;
ALTER DATABASE [MyDataBase] REMOVE FILE Table17LData;
ALTER DATABASE [MyDataBase] REMOVE FILE Table12LData;
ALTER DATABASE [MyDataBase] REMOVE FILE Table1RestData;
GO
ALTER DATABASE [MyDataBase] REMOVE FILEGROUP Table125LFG;
ALTER DATABASE [MyDataBase] REMOVE FILEGROUP Table117LFG;
ALTER DATABASE [MyDataBase] REMOVE FILEGROUP Table17LFG;
ALTER DATABASE [MyDataBase] REMOVE FILEGROUP Table12LFG;
ALTER DATABASE [MyDataBase] REMOVE FILEGROUP Table1RestFG;
GO

If someone have better solution other than Partitioning, to this situation is Welcome.

Best Answer

File '%ls' cannot be reused until after the next BACKUP LOG operation.

SQL Server insists that a backup is taken before a file is reused so it can guarantee database recovery in case of disaster.

The minimum backup needed is a log backup, but the documentation recommends a full database backup after any operation that adds or removes files from a database:

Extract

By adding a file, removing it, and attempting to add it again, you run into this requirement.

If you have a genuine need to add and remove files, you will need to add a BACKUP DATABASE or BACKUP LOG statement to your script.

This backup will form part of the recovery chain, so it is important you keep it safe in case you need to recover the database.

See BACKUP (Transact-SQL) for syntax details and further information.

Related Question