How do I merge NDF files into the MDF ?

Hi
I have a database with a whole lot of secondary data files
(*.ndf's). What do I run to merge all of those into the
Primary data file (*.mdf)?
Thanks
HHi,
To remove a NDF file, you must first moved the data off from the NDF file
onto
the other members in the data set. To do this, use the EMPTY FILE
parameter in DBCC SHRINKFILE command.
This will empty the file and mark it as unavailable. From there, you should
be able to use the REMOVE FILE parameter in ALTER DATABASE command.
Steps:-
1. Do a Full database backup
2. Execute below to move the data of the NDF file to other files
DBCC SHRINKFILE('logical_ndf_name',EMPTYFILE)
3. Now you execute the command to remove the RDF file
ALTER database <dbname> REMOVE FILE 'logical_ndf_name'
Do the same steps for all available NDF files in the database.
--
Thanks
Hari
MCDBA
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:2329c01c45e5e$176c5950$a301280a@.phx.gbl...
> Hi
> I have a database with a whole lot of secondary data files
> (*.ndf's). What do I run to merge all of those into the
> Primary data file (*.mdf)?
> Thanks
> H|||Thanks Hari
I'll give it a go!
Regards
H|||Hari
I see that each *.ndf is also in it's own filegroup, so it
won't let me run a dbcc shrinkfile...it keeps saying that
the filegroup is full (it isn't physically and expand
dynamically).
Thanks
H|||Then you need to get the tables and indexes in that file group onto some other filegroup. Recreate
the indexes. If you have a table without a clustered index, move that by create a clustered index
(on another filegroup) and then possibly dropping that clustered index.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:22e0101c45e6a$13448b20$a601280a@.phx.gbl...
> Hari
> I see that each *.ndf is also in it's own filegroup, so it
> won't let me run a dbcc shrinkfile...it keeps saying that
> the filegroup is full (it isn't physically and expand
> dynamically).
> Thanks
> H|||Look ,H
CREATE DATABASE mywind
GO
ALTER DATABASE mywind ADD FILEGROUP new_customers
GO
ALTER DATABASE mywind ADD FILE
(NAME='mywind_data_1',
FILENAME='d:\mw.dat1')
TO FILEGROUP new_customers
GO
CREATE TABLE mywind..t1 (id int) ON new_customers
GO
INSERT INTO mywind..t1 (id ) VALUES (1)
GO
ALTER DATABASE mywind REMOVE FILE mywind_data_1
--Server: Msg 5042, Level 16, State 1, Line 1
--The file 'mywind_data_1' cannot be removed because it is not empty.
USE mywind
DBCC SHRINKFILE (mywind_data_1, EMPTYFILE)
--
I went to EM and change the filegroup for t1 to PRIMARY FILEGROUP
--
GO
ALTER DATABASE mywind REMOVE FILE mywind_data_1
ALTER DATABASE mywind REMOVE FILEGROUP new_customers
GO
DROP DATABASE mywind
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:22e0101c45e6a$13448b20$a601280a@.phx.gbl...
> Hari
> I see that each *.ndf is also in it's own filegroup, so it
> won't let me run a dbcc shrinkfile...it keeps saying that
> the filegroup is full (it isn't physically and expand
> dynamically).
> Thanks
> H|||I use the method to remove a .ndf file from the primary group. But after I ran the DBCC, the .ndf is still not empty and there are 0.6 MB for data in that file, which I couldn't clear. and I couldn't remove the file from the group as well since the file is not empty. and suggestion?
--
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.|||I use the method to remove a .ndf file from the primary group. But after I ran the DBCC, the .ndf is still not empty and there are 0.6 MB for data in that file, which I couldn't clear. and I couldn't remove the file from the group as well since the file is not empty. and suggestion?
--
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.|||I use the method to remove a .ndf file from the primary group. But after I ran the DBCC, the .ndf is still not empty and there are 0.6 MB for data in that file, which I couldn't clear. and I couldn't remove the file from the group as well since the file is not empty. and suggestion?
--
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.|||Try SHRINKFILE with EMPTYFILE option again. I've heard of cases where you need to do it twice before it is
completely empty...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:%231Ab8wDjEHA.3524@.TK2MSFTNGP10.phx.gbl...
> I use the method to remove a .ndf file from the primary group. But after I ran the DBCC, the .ndf is still
not empty and there are 0.6 MB for data in that file, which I couldn't clear. and I couldn't remove the file
from the group as well since the file is not empty. and suggestion?
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and
Searching.

How do I generate auto increment number in SQL Express?

Hi, in Access, I can use an Auto-Increment number for my primary key field. May I know how do I do that in SQL Express? In addition, is there any tutorial on how to use SQL Express to generate customised unique numbers (such as membership number, Customer ID such as A001 where A is based on the customer's name while 001 is due to the fact that the customer is the first among those with names starting with A)?Thanks a lot.For your first question: In SQL Server, you can use data type int as your identity column and assign this column as indentity field from the property window. It will work like autonumber field in Access.|||

cckiat:

Hi, in Access, I can use an Auto-Increment number for my primary key field. May I know how do I do that in SQL Express? In addition, is there any tutorial on how to use SQL Express to generate customised unique numbers (such as membership number, Customer ID such as A001 where A is based on the customer's name while 001 is due to the fact that the customer is the first among those with names starting with A)? Thanks a lot.

IDENTITY is the auto increament in SQL Server it is a property of the column, the second one you described is a SEQUENCE it is in Oracle not SQL Server but it is similar to IDENTITY. Both are defined by ANSI SQL but Microsoft and Oracle choose to implement one and not the other. But you can use GUID in SQL Server to generate Unique numbers but it is a 16bytes Binary data type so use it with care. Hope this helps.

How do I generate a Unique ID?

Hi,

I have 5 tables that need ID columns. These ID columns are the primary keys for these tables. They appear in the tables like so:

Clients table

CLT00001
CLT00002
CLT00003

Volunteers table

VOL00001
VOL00002
etc...

These ID's are given to the Clients/Volunteers so they need to be more than just a number (I want to use the 3 char prefix), but I want them to auto-increment. I am not concerned with gaps if a row is deleted.

At the moment I have a user defined datatype in MSSQL, and I am incrementing and adding the prefix in VB before inserting into the database.

This works fine, except I dont know how to retrieve the last ID number from the DB so I can increment it when I add a new record.

I was using a VB function that did this: SELECT MAX(client_id) FROM clients, then strip everything of the front but the number, increment + 1, add the prefix and leading zeros back on again, and return the new ID. This worked until CLT00011, then it returned CLT00002 again.

Is there a better way to do what I am trying to do? Please help!One solution:

Separate your ID into two fields; the prefix and the numeric portions. Autoincrement the numeric portion, but pad it with zeros and concatenate it with the prefix for display. You could also create a calculated field in your table that combined the two components.

Either way, you can set the combination of the two columns as the primary key, or just as a unique index.

blindman|||Does that mean I would have a Prefix field with 'VOL' in every row, and my IDENTITY field in another. Sounds good, but does it kmatter that I have redundant info. i.e. the prefix field?

How do I join the two with a calculated field, because I still need to have a 8 char long ID, 3 chars for the prefix, then the ID num padded with zeros between the two, e.g.

VOL00001
VOL00002|||Create a trigger what will generate primary key for your table (you can use autoincremet field or calculate new key).|||If every record has the same prefix then is it not necessary to dedicate a column to it.

Set up your autoincrement row, and then create your calculated field with this formula:

'VOL' + right('00000' + cast([AutoIncColumn] as varchar(5)))

blindman