Deleting FileGroup/(.ldf) file

Hello, I'm currently busy with Table Partitioning. I have 9 FileGroups, which is all populated with data.

My question is, how do you delete / trucate a FileGroup / file(.ldf) which is already populated?

I tried

ALTER DATABASE ##

but it keeps telling me that the FileGroup cannot be deleted because it is populated.

Any answers would be greatly appreciated.

You have filegroups for the log files?

Deleting additional data files in a database primary filegroup

I created two additional data files in our database so that when the primary
location was full it would write to the added files. I though it would write
to the 2nd file and fill it before writing to the 3rd. It is writing to
both. Is it writing redundant data, or different data for better efficiency?
Also, how can I delete the 3rd data file without losing information? The
3rd location is not a RAID device, so I would like to keep as much
information in the data file in the 2nd location as possible. Any ideas or
suggestions?
kdirks
Rainbow Energy Marketing
If your files are part of the PRIMARY filegroup, SQL Server is free to
place data in either one. It doesn't write the same data to both.
Moving the second file to a RAID device makes good sense. See the
ALTER DATABASE topic in books online for a complete explanation
(including removing files, if you're worried about the extra). Here's
a quick example I used to move the tempDB data files off my primary
data array...
alter database tempdb modify file (name='tempdev',filename=
'e:\mssql\MSSQL$SQL2K\data\tempdb_data.mdf')
|||Oh, yes, I believe you will need to restart your sql server for the
actual file move to take place.

Deleting additional data files in a database primary filegroup

I created two additional data files in our database so that when the primary
location was full it would write to the added files. I though it would writ
e
to the 2nd file and fill it before writing to the 3rd. It is writing to
both. Is it writing redundant data, or different data for better efficiency
?
Also, how can I delete the 3rd data file without losing information? The
3rd location is not a RAID device, so I would like to keep as much
information in the data file in the 2nd location as possible. Any ideas or
suggestions?
--
kdirks
Rainbow Energy MarketingIf your files are part of the PRIMARY filegroup, SQL Server is free to
place data in either one. It doesn't write the same data to both.
Moving the second file to a RAID device makes good sense. See the
ALTER DATABASE topic in books online for a complete explanation
(including removing files, if you're worried about the extra). Here's
a quick example I used to move the tempDB data files off my primary
data array...
alter database tempdb modify file (name='tempdev',filename=
'e:\mssql\MSSQL$SQL2K\data\tempdb_data.mdf')|||Oh, yes, I believe you will need to restart your sql server for the
actual file move to take place.

Deleting additional data files in a database primary filegroup

I created two additional data files in our database so that when the primary
location was full it would write to the added files. I though it would write
to the 2nd file and fill it before writing to the 3rd. It is writing to
both. Is it writing redundant data, or different data for better efficiency?
Also, how can I delete the 3rd data file without losing information? The
3rd location is not a RAID device, so I would like to keep as much
information in the data file in the 2nd location as possible. Any ideas or
suggestions?
--
kdirks
Rainbow Energy MarketingIf your files are part of the PRIMARY filegroup, SQL Server is free to
place data in either one. It doesn't write the same data to both.
Moving the second file to a RAID device makes good sense. See the
ALTER DATABASE topic in books online for a complete explanation
(including removing files, if you're worried about the extra). Here's
a quick example I used to move the tempDB data files off my primary
data array...
alter database tempdb modify file (name='tempdev',filename='e:\mssql\MSSQL$SQL2K\data\tempdb_data.mdf')|||Oh, yes, I believe you will need to restart your sql server for the
actual file move to take place.

Delete on a single filegroup

I am running SQL 2005 SP2.
I have a partitioned table and was wondering if there is a view that could
tell me the data that resides on a specific filegroup. What I would like to
do is to delete only the data that resides on a specific filegroup. Thx.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200802/1Perhaps the $PARTITION() function will help you?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message news:801e77dbe7979@.uwe...
>I am running SQL 2005 SP2.
> I have a partitioned table and was wondering if there is a view that could
> tell me the data that resides on a specific filegroup. What I would like to
> do is to delete only the data that resides on a specific filegroup. Thx.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200802/1
>|||> I have a partitioned table and was wondering if there is a view that could
> tell me the data that resides on a specific filegroup. What I would like
> to
> do is to delete only the data that resides on a specific filegroup. Thx.
The query below will list partitions by filegroup. You can then delete the
data using $PARTITION function like Tibor mentioned or specify partitioning
column values that map to the partitions on the filegroup. If you have a
lot of data, you might consider switching partitions into a staging table
and then truncating the staging table.
SELECT
s.name AS SchemaName,
o.name AS TableName,
ds.name AS PartitionScheme,
p.partition_number AS PartitionNumber,
fg.name AS FileGroupName,
prv_left.value AS LowerBoundaryValue,
prv_right.value AS UpperBoundaryValue,
CASE pf.boundary_value_on_right WHEN 1 THEN 'RIGHT' ELSE 'LEFT' END AS
Boundary,
p.rows AS Rows
FROM sys.schemas AS s
JOIN sys.objects AS o ON
o.schema_id = s.schema_id
AND o.type = 'U'
JOIN sys.indexes AS i ON
i.object_id = o.object_id AND
i.index_id IN(0,1)
JOIN sys.partitions p ON
p.object_id = i.object_id AND
p.index_id = i.index_id
JOIN sys.data_spaces AS ds ON
ds.data_space_id = i.data_space_id
JOIN sys.partition_schemes AS ps ON
ps.data_space_id = ds.data_space_id
JOIN sys.partition_functions AS pf ON
pf.function_id = ps.function_id
JOIN sys.destination_data_spaces AS dds2 ON
dds2.partition_scheme_id = ps.data_space_id
AND dds2.destination_id = p.partition_number
JOIN sys.filegroups AS fg ON
fg.data_space_id = dds2.data_space_id
LEFT JOIN sys.partition_range_values AS prv_left ON
ps.function_id = prv_left.function_id
AND prv_left.boundary_id = p.partition_number - 1
LEFT JOIN sys.partition_range_values AS prv_right ON
ps.function_id = prv_right.function_id
AND prv_right.boundary_id = p.partition_number
ORDER BY
SchemaName,
TableName,
FileGroupName,
PartitionNumber;
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:801e77dbe7979@.uwe...
>I am running SQL 2005 SP2.
> I have a partitioned table and was wondering if there is a view that could
> tell me the data that resides on a specific filegroup. What I would like
> to
> do is to delete only the data that resides on a specific filegroup. Thx.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200802/1
>

Delete on a single filegroup

I am running SQL 2005 SP2.
I have a partitioned table and was wondering if there is a view that could
tell me the data that resides on a specific filegroup. What I would like to
do is to delete only the data that resides on a specific filegroup. Thx.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200802/1
> I have a partitioned table and was wondering if there is a view that could
> tell me the data that resides on a specific filegroup. What I would like
> to
> do is to delete only the data that resides on a specific filegroup. Thx.
The query below will list partitions by filegroup. You can then delete the
data using $PARTITION function like Tibor mentioned or specify partitioning
column values that map to the partitions on the filegroup. If you have a
lot of data, you might consider switching partitions into a staging table
and then truncating the staging table.
SELECT
s.name AS SchemaName,
o.name AS TableName,
ds.name AS PartitionScheme,
p.partition_number AS PartitionNumber,
fg.name AS FileGroupName,
prv_left.value AS LowerBoundaryValue,
prv_right.value AS UpperBoundaryValue,
CASE pf.boundary_value_on_right WHEN 1 THEN 'RIGHT' ELSE 'LEFT' END AS
Boundary,
p.rows AS Rows
FROM sys.schemas AS s
JOIN sys.objects AS o ON
o.schema_id = s.schema_id
AND o.type = 'U'
JOIN sys.indexes AS i ON
i.object_id = o.object_id AND
i.index_id IN(0,1)
JOIN sys.partitions p ON
p.object_id = i.object_id AND
p.index_id = i.index_id
JOIN sys.data_spaces AS ds ON
ds.data_space_id = i.data_space_id
JOIN sys.partition_schemes AS ps ON
ps.data_space_id = ds.data_space_id
JOIN sys.partition_functions AS pf ON
pf.function_id = ps.function_id
JOIN sys.destination_data_spaces AS dds2 ON
dds2.partition_scheme_id = ps.data_space_id
AND dds2.destination_id = p.partition_number
JOIN sys.filegroups AS fg ON
fg.data_space_id = dds2.data_space_id
LEFT JOIN sys.partition_range_values AS prv_left ON
ps.function_id = prv_left.function_id
AND prv_left.boundary_id = p.partition_number - 1
LEFT JOIN sys.partition_range_values AS prv_right ON
ps.function_id = prv_right.function_id
AND prv_right.boundary_id = p.partition_number
ORDER BY
SchemaName,
TableName,
FileGroupName,
PartitionNumber;
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:801e77dbe7979@.uwe...
>I am running SQL 2005 SP2.
> I have a partitioned table and was wondering if there is a view that could
> tell me the data that resides on a specific filegroup. What I would like
> to
> do is to delete only the data that resides on a specific filegroup. Thx.
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200802/1
>