Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Can you explain in your question why you need to repeatedly add and remove partitioning from this table? Use the edit button rather than responding in a comment please. –  Paul White Aug 21 '13 at 11:58
    
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
2  
Yes but why? I'm sure a lot of people would be interested to know what the need is that drives this implementation. You never know, we might be able to offer an improvement you hadn't thought of. –  Paul White Aug 21 '13 at 12:24
add comment

1 Answer

up vote 4 down vote accepted

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.

share|improve this answer
    
Thank you. But I am not going to use Backup from script. But Yes, your post and link helped me to explain that manual job at admin level. Thanks once again. –  RJ1990 Aug 21 '13 at 12:20
    
I consider your post as answer, but I am not marking so that other will think this Question Need Answer and may end up with better solution. Sorry for Poor English. thank you once again. –  RJ1990 Aug 21 '13 at 13:51
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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