Remove files from databases

Hi, I've created a new filegroup, then added to it a new filename, then create a table pointing to this filegroup. So far everything is ok, but if I want to revert the process in this way:

1- Drop the table - OK

2- Drop the filegroup - OK

3- Drop the filename - ERROR: Msg 5009, Level 16, State 9, Line 2
One or more files listed in the statement could not be found or could not be initialized.

When I query a catalog view with this query:

Select*FromSys.Database_Files

I get the file that I had recently deleted, it is offline but I can not delete it using the ALTER DATABASE instructions.

This is the code I use:

Use [TESTING ]

Go

-- Add a FileGroup to the Database

AlterDatabase TESTING AddFileGroup FG01

Go

-- Add a file to a FileGroup

AlterDatabase TESTING AddFile(

NAME= TESTING_DATA01,

FILENAME='D:\Sql Server\Data\Testing_Data01.ndf',

SIZE= 1 MB,

MAXSIZE= 10 MB,

FILEGROWTH= 1 MB

)ToFileGroup FG01

Go

-- Create the table using a specific FileGroup

CreateTable TABLE1 (

Id IntNotNull,

FirstName VarChar(30)NotNull,

LastName VarChar(30)NotNull,

BirthDate SmallDateTime

)

On FG01

So far everything works ok, but in the next code there is an error:

-- Delete the table

Drop Table TABLE1

Go

-- Remove a FileGroup from the Database

AlterDatabase TESTING Remove FileGroup FG01

Go

-- Try to remove the file

AlterDatabase TESTING Remove File TESTING_DATA01

When I try to remove the file there is an error:

"Msg 5009, Level 16, State 9, Line 2
One or more files listed in the statement could not be found or could not be initialized"

You simply need to change the order of your Remove statements to remove the file BEFORE you remove the filegroup.|||Thanks for the answer, but how can I delete the entries in the Sys.Database_Files that already exists ?

Remove FileGroup

I have removed all the files from a filegroup ...when I do sp_helpdb I don't see the filegroup... but when i try to remove the filegroup using ALTER DATABASE <DB> REMOVE FILEGROUP, it says The filegroup 'XXX' cannot be removed because it is not empty

any idea...

Thanks

Ranga

Hi

I've encountered the same issue.

Hi guys, is there a solution for this problem.

Below is other post for the same issue.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=446565&SiteID=1

thanks,
Larry

|||

Hi Ranga,

Maybe a partition scheme is still mapped to the filegroups.

Try dropping the partition scheme before removing the filegroups

|||

We are having this same problem. We want to drop old files and filegroups from our partitioned database. We can drop the files. However, there does not seem to be anyway to remove a filegroup from an existing partition, even if the filegroup is empty. Dropping the partition is NOT an option in our case, as the partitioned table contain 2 billion+ records and to rebuild the partition would likely take 2 weeks or longer.

Q to Microsoft support: How do we remove a Filegroup from a partition? Once a FG is added to a partition, are we stuck with it being there indefinately?

Thanks.

Remove FileGroup

I have removed all the files from a filegroup ...when I do sp_helpdb I don't see the filegroup... but when i try to remove the filegroup using ALTER DATABASE <DB> REMOVE FILEGROUP, it says The filegroup 'XXX' cannot be removed because it is not empty

any idea...

Thanks

Ranga

Hi

I've encountered the same issue.

Hi guys, is there a solution for this problem.

Below is other post for the same issue.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=446565&SiteID=1

thanks,
Larry

|||

Hi Ranga,

Maybe a partition scheme is still mapped to the filegroups.

Try dropping the partition scheme before removing the filegroups

|||

We are having this same problem. We want to drop old files and filegroups from our partitioned database. We can drop the files. However, there does not seem to be anyway to remove a filegroup from an existing partition, even if the filegroup is empty. Dropping the partition is NOT an option in our case, as the partitioned table contain 2 billion+ records and to rebuild the partition would likely take 2 weeks or longer.

Q to Microsoft support: How do we remove a Filegroup from a partition? Once a FG is added to a partition, are we stuck with it being there indefinately?

Thanks.

sql

Remove FileGroup

I have removed all the files from a filegroup ...when I do sp_helpdb I don't see the filegroup... but when i try to remove the filegroup using ALTER DATABASE <DB> REMOVE FILEGROUP, it says The filegroup 'XXX' cannot be removed because it is not empty

any idea...

Thanks

Ranga

Hi

I've encountered the same issue.

Hi guys, is there a solution for this problem.

Below is other post for the same issue.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=446565&SiteID=1

thanks,
Larry

|||

Hi Ranga,

Maybe a partition scheme is still mapped to the filegroups.

Try dropping the partition scheme before removing the filegroups

|||

We are having this same problem. We want to drop old files and filegroups from our partitioned database. We can drop the files. However, there does not seem to be anyway to remove a filegroup from an existing partition, even if the filegroup is empty. Dropping the partition is NOT an option in our case, as the partitioned table contain 2 billion+ records and to rebuild the partition would likely take 2 weeks or longer.

Q to Microsoft support: How do we remove a Filegroup from a partition? Once a FG is added to a partition, are we stuck with it being there indefinately?

Thanks.

Remove duplicates within pipeline

I have a situation where we get XML files sent daily that need uploading into SQL Server tables, but the source system producing these files sometimes generates duplicate records in the file. The tricky part is, that the record isn't entirely duplicated. What I mean, is that if I look for duplicates by grouping the key columns, having count(*) > 1, I find which ones are duplicates, but when I inspect the data on these duplicates, the other details in the remaining columns may differ. So our rule is: pick the first record, toss the rest of the duplicates.

Because we don't sort on any columns during the import, the first record kept of the duplicates is arbitrary. Again, we can't tell at this point which of the duplicated records is more correct. Someday down the road, we will do this research.

Now, I need to know the most efficient way to accomplish this in SSIS. If it makes it easier, I could just discard all the duplicates, since the number of them is so small.

If the source were a relational table, I could use a SQL statement to filter the records to remove the duplicates, but since the source is an XML file, I don't know how to filter these out in the pipeline, since the file has to be aggregated to search for dups.

Thanks

Kory

Never mind... I think I found exactly what I needed: The Sort Transform.

-Kory

|||

The only way I can think is to use the sort or aggregate transformations. Have you explore those? Notice that those are full blcoking transformation, so memory usage and performance are things you may want to check.

Rafael Salas

|||

Yes, I thought the sort transform would do the trick- and it did for small files. Files with < 500,000 rows sorted immediately, within 5-10 seconds. Files > 500,000 or so just hung. Looking at task manager, the DTSDebugHost.exe kept climbing and my overall memory consumption was > 5G and I only have 3G total on the server.

I would have thought the performance was linearly decrease, and not go from 10 seconds to indefinite for just 200K rows more.

I've downloaded and installed the Extrasort component, but get an error when I try to put it on the design surface. It complains that it wasn't installed correctly. I've uninstalled and reinstalled it twice. I know NSort is another option, but I really am not needing sorting functionality, just removing duplicates.

SSIS comes with a sample solution that builds a component for removing duplicates, but as far as I can tell, the fields to pick to determine duplicates are the only fields that it passes through the pipeline. I need to remove dups based on 3 fields, but pass through the rest of the fields, like the sort component does.

Any other ideas out there?

Thanks

Kory

|||

Hi I'm having the same problem

does the ssis have the capabilities of seperating the duplicate records ? or still i use the query? can you give me some advice on this ?

KoryS wrote:

I have a situation where we get XML files sent daily that need uploading into SQL Server tables, but the source system producing these files sometimes generates duplicate records in the file. The tricky part is, that the record isn't entirely duplicated. What I mean, is that if I look for duplicates by grouping the key columns, having count(*) > 1, I find which ones are duplicates, but when I inspect the data on these duplicates, the other details in the remaining columns may differ. So our rule is: pick the first record, toss the rest of the duplicates.

Because we don't sort on any columns during the import, the first record kept of the duplicates is arbitrary. Again, we can't tell at this point which of the duplicated records is more correct. Someday down the road, we will do this research.

Now, I need to know the most efficient way to accomplish this in SSIS. If it makes it easier, I could just discard all the duplicates, since the number of them is so small.

If the source were a relational table, I could use a SQL statement to filter the records to remove the duplicates, but since the source is an XML file, I don't know how to filter these out in the pipeline, since the file has to be aggregated to search for dups.

Thanks

Kory

|||What version of ExtraSort are you using and what platform is it running on?

Had no problems running ExtraSort file version 1.0.0.3 (98,304 bytes) on a 32 bit dev platform Win XP SP2 as well as Win2k3 SP1. The SQL Server Build on both is 2153 , which "everyone" running IS should be on at this point. Have been unable to get ExtraSort to run natively on x64.

By default, the component installs to C:\Program Files\Ivolva Digital\ExtraSort Component\ExtraSort.dll.

Remove duplicates within pipeline

I have a situation where we get XML files sent daily that need uploading into SQL Server tables, but the source system producing these files sometimes generates duplicate records in the file. The tricky part is, that the record isn't entirely duplicated. What I mean, is that if I look for duplicates by grouping the key columns, having count(*) > 1, I find which ones are duplicates, but when I inspect the data on these duplicates, the other details in the remaining columns may differ. So our rule is: pick the first record, toss the rest of the duplicates.

Because we don't sort on any columns during the import, the first record kept of the duplicates is arbitrary. Again, we can't tell at this point which of the duplicated records is more correct. Someday down the road, we will do this research.

Now, I need to know the most efficient way to accomplish this in SSIS. If it makes it easier, I could just discard all the duplicates, since the number of them is so small.

If the source were a relational table, I could use a SQL statement to filter the records to remove the duplicates, but since the source is an XML file, I don't know how to filter these out in the pipeline, since the file has to be aggregated to search for dups.

Thanks

Kory

Never mind... I think I found exactly what I needed: The Sort Transform.

-Kory

|||

The only way I can think is to use the sort or aggregate transformations. Have you explore those? Notice that those are full blcoking transformation, so memory usage and performance are things you may want to check.

Rafael Salas

|||

Yes, I thought the sort transform would do the trick- and it did for small files. Files with < 500,000 rows sorted immediately, within 5-10 seconds. Files > 500,000 or so just hung. Looking at task manager, the DTSDebugHost.exe kept climbing and my overall memory consumption was > 5G and I only have 3G total on the server.

I would have thought the performance was linearly decrease, and not go from 10 seconds to indefinite for just 200K rows more.

I've downloaded and installed the Extrasort component, but get an error when I try to put it on the design surface. It complains that it wasn't installed correctly. I've uninstalled and reinstalled it twice. I know NSort is another option, but I really am not needing sorting functionality, just removing duplicates.

SSIS comes with a sample solution that builds a component for removing duplicates, but as far as I can tell, the fields to pick to determine duplicates are the only fields that it passes through the pipeline. I need to remove dups based on 3 fields, but pass through the rest of the fields, like the sort component does.

Any other ideas out there?

Thanks

Kory

|||

Hi I'm having the same problem

does the ssis have the capabilities of seperating the duplicate records ? or still i use the query? can you give me some advice on this ?

KoryS wrote:

I have a situation where we get XML files sent daily that need uploading into SQL Server tables, but the source system producing these files sometimes generates duplicate records in the file. The tricky part is, that the record isn't entirely duplicated. What I mean, is that if I look for duplicates by grouping the key columns, having count(*) > 1, I find which ones are duplicates, but when I inspect the data on these duplicates, the other details in the remaining columns may differ. So our rule is: pick the first record, toss the rest of the duplicates.

Because we don't sort on any columns during the import, the first record kept of the duplicates is arbitrary. Again, we can't tell at this point which of the duplicated records is more correct. Someday down the road, we will do this research.

Now, I need to know the most efficient way to accomplish this in SSIS. If it makes it easier, I could just discard all the duplicates, since the number of them is so small.

If the source were a relational table, I could use a SQL statement to filter the records to remove the duplicates, but since the source is an XML file, I don't know how to filter these out in the pipeline, since the file has to be aggregated to search for dups.

Thanks

Kory

|||What version of ExtraSort are you using and what platform is it running on?

Had no problems running ExtraSort file version 1.0.0.3 (98,304 bytes) on a 32 bit dev platform Win XP SP2 as well as Win2k3 SP1. The SQL Server Build on both is 2153 , which "everyone" running IS should be on at this point. Have been unable to get ExtraSort to run natively on x64.

By default, the component installs to C:\Program Files\Ivolva Digital\ExtraSort Component\ExtraSort.dll.

sql

Remove duplicates within pipeline

I have a situation where we get XML files sent daily that need uploading into SQL Server tables, but the source system producing these files sometimes generates duplicate records in the file. The tricky part is, that the record isn't entirely duplicated. What I mean, is that if I look for duplicates by grouping the key columns, having count(*) > 1, I find which ones are duplicates, but when I inspect the data on these duplicates, the other details in the remaining columns may differ. So our rule is: pick the first record, toss the rest of the duplicates.

Because we don't sort on any columns during the import, the first record kept of the duplicates is arbitrary. Again, we can't tell at this point which of the duplicated records is more correct. Someday down the road, we will do this research.

Now, I need to know the most efficient way to accomplish this in SSIS. If it makes it easier, I could just discard all the duplicates, since the number of them is so small.

If the source were a relational table, I could use a SQL statement to filter the records to remove the duplicates, but since the source is an XML file, I don't know how to filter these out in the pipeline, since the file has to be aggregated to search for dups.

Thanks

Kory

Never mind... I think I found exactly what I needed: The Sort Transform.

-Kory

|||

The only way I can think is to use the sort or aggregate transformations. Have you explore those? Notice that those are full blcoking transformation, so memory usage and performance are things you may want to check.

Rafael Salas

|||

Yes, I thought the sort transform would do the trick- and it did for small files. Files with < 500,000 rows sorted immediately, within 5-10 seconds. Files > 500,000 or so just hung. Looking at task manager, the DTSDebugHost.exe kept climbing and my overall memory consumption was > 5G and I only have 3G total on the server.

I would have thought the performance was linearly decrease, and not go from 10 seconds to indefinite for just 200K rows more.

I've downloaded and installed the Extrasort component, but get an error when I try to put it on the design surface. It complains that it wasn't installed correctly. I've uninstalled and reinstalled it twice. I know NSort is another option, but I really am not needing sorting functionality, just removing duplicates.

SSIS comes with a sample solution that builds a component for removing duplicates, but as far as I can tell, the fields to pick to determine duplicates are the only fields that it passes through the pipeline. I need to remove dups based on 3 fields, but pass through the rest of the fields, like the sort component does.

Any other ideas out there?

Thanks

Kory

|||

Hi I'm having the same problem

does the ssis have the capabilities of seperating the duplicate records ? or still i use the query? can you give me some advice on this ?

KoryS wrote:

I have a situation where we get XML files sent daily that need uploading into SQL Server tables, but the source system producing these files sometimes generates duplicate records in the file. The tricky part is, that the record isn't entirely duplicated. What I mean, is that if I look for duplicates by grouping the key columns, having count(*) > 1, I find which ones are duplicates, but when I inspect the data on these duplicates, the other details in the remaining columns may differ. So our rule is: pick the first record, toss the rest of the duplicates.

Because we don't sort on any columns during the import, the first record kept of the duplicates is arbitrary. Again, we can't tell at this point which of the duplicated records is more correct. Someday down the road, we will do this research.

Now, I need to know the most efficient way to accomplish this in SSIS. If it makes it easier, I could just discard all the duplicates, since the number of them is so small.

If the source were a relational table, I could use a SQL statement to filter the records to remove the duplicates, but since the source is an XML file, I don't know how to filter these out in the pipeline, since the file has to be aggregated to search for dups.

Thanks

Kory

|||What version of ExtraSort are you using and what platform is it running on?

Had no problems running ExtraSort file version 1.0.0.3 (98,304 bytes) on a 32 bit dev platform Win XP SP2 as well as Win2k3 SP1. The SQL Server Build on both is 2153 , which "everyone" running IS should be on at this point. Have been unable to get ExtraSort to run natively on x64.

By default, the component installs to C:\Program Files\Ivolva Digital\ExtraSort Component\ExtraSort.dll.

Remove Backup Files ?

We are using SQL Server 2000 with SP3 installed.
In the database maintenance plan, we have chosen
to "Remove files older than 1 week" for the production
database.
We would like to know does the setting mean that "SQL
Server will remove backup files generated by the DB
Maitenance Plan will be removed if it is older than 1
week ?". This is because we find that there are some DB
backups (Done in July last year) exist in that directory.
ThanksBelow KB might help:
http://support.microsoft.com/default.aspx?scid=kb;en-us;303292&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Roger Lee" <anonymous@.discussions.microsoft.com> wrote in message
news:045801c39db5$02521100$a601280a@.phx.gbl...
> We are using SQL Server 2000 with SP3 installed.
> In the database maintenance plan, we have chosen
> to "Remove files older than 1 week" for the production
> database.
> We would like to know does the setting mean that "SQL
> Server will remove backup files generated by the DB
> Maitenance Plan will be removed if it is older than 1
> week ?". This is because we find that there are some DB
> backups (Done in July last year) exist in that directory.
> Thanks|||Dear Tibor,
Thank you for your advice.
However, what I missed in last message is that -
Is it possible to make ad hoc backup of the production
database in the same directory where "Daily backup is
generated by the database maintenance plan that 'Remove
files older than 1 week' option is chosen for the
production database".
This is because if the "Database Maintenance Plan" only
remove backups generated by itself, it is perfect for us
to put ad hoc backup there as we don't want to create
another directory for holding ad hoc backup.
Thanks

Remove 2nd Log file

Hi all,
I've got a SQL 2000 SP4 server with a database that has 3 data files and 2
logs files.
I no longer need the 2nd log file and I would like to remove it. How is this
best accomplished with no or at least extremely minimal downtime. Is it done
the same way I remove a data file?
DBCC SHRINKFILE (MySecondLogFile, EMPTYFILE)
GO
USE master
GO
ALTER DATABASE MyDatabase
REMOVE FILE MySecondLogFile
Thanks for any advice or pointers!
-PhilHi,
As a first step take a transaction log backup to clear the active portion in
transaction log.
Step -1 - Transaction log backup
Step 2 - DBCC SHRINKFILE EMPTY FILE
Step -3 - ALTER DATABASE REMOVE FILE
Thanks
Hari
SQL Server MVP
"pmattson" <pmattson@.discussions.microsoft.com> wrote in message
news:CA34A3A3-1DC6-42EF-BFB9-C94DC2EC3CB2@.microsoft.com...
> Hi all,
> I've got a SQL 2000 SP4 server with a database that has 3 data files and 2
> logs files.
> I no longer need the 2nd log file and I would like to remove it. How is
> this
> best accomplished with no or at least extremely minimal downtime. Is it
> done
> the same way I remove a data file?
> DBCC SHRINKFILE (MySecondLogFile, EMPTYFILE)
> GO
> USE master
> GO
> ALTER DATABASE MyDatabase
> REMOVE FILE MySecondLogFile
>
> Thanks for any advice or pointers!
> -Phil