2

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.

  • Actually this script I am testing on Development Environment. If everything gets perfect then this will be applied to Live sever. – RJ1990 Aug 21 '13 at 12:16
4

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.

| improve this answer | |
1

What Paul says is the major understanding someone should take from this error. Having said that, if you're in a development environment, like the OP mentions, and you're testing file/filegroup creation scripts, etc., and you do not care about a recovery scenario, you can

change your recovery model to simple and restart the database.

or, alternatively, and completely unsupported, you can,

truncate your transaction log without backing it up.

By changing the mode to simple, you will only be able to recover from a backup and any changes since then will be unrecoverable. See Recovery Models for more info.

By truncating your log, you're removing the change operation. Therefore, it won't bug require you to perform a backup before reusing the file. But nowadays, you can't truncate the log without backing it up, but Brent Ozar has a good page where he describes a hack to perform a truncation without actually backing up the log.

To reiterate, these are both cases where you're not concerned about needing to recover data. Brent even includes the warning "Don’t try what you’re about to see at home".

Here's the syntax of that operation:

BACKUP LOG [DATABASE_NAME] TO DISK='NUL:'

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.