Remove ldf file

Hi all!
I have a SQL Server database with a secondary datafile (*.ndf). I would like to be able to delete that file and only use the *.mdf to store data. Of cource I also have a transaction log file.
Is it possible to move data from the ndf to the mdf and the delete the ndf'
Regards
/FredrikHi,
SQL Server spans data across all data files within a filegroup.So all files
fill will be used for all DMLs. to remove a file , you must first have the
data moved off of the file onto the other members in the data set.
To do this, use the EMPTY FILE parameter in DBCC SHEINKFILE command.
This will empty the file and mark it as unavailable. After that execute
REMOVE FILE in ALTER DATABASE command to drop the NDF file.
See DBCC SHRINKFILE and ALTER database in books online for syntax.
--
Thanks
Hari
MCDBA
"Fredrik" <Fredrik@.discussions.microsoft.com> wrote in message
news:5DCC35E5-43A0-45B7-A3F6-81353A62F09E@.microsoft.com...
> Hi all!
> I have a SQL Server database with a secondary datafile (*.ndf). I would
like to be able to delete that file and only use the *.mdf to store data. Of
cource I also have a transaction log file.
> Is it possible to move data from the ndf to the mdf and the delete the
ndf'
> Regards
> /Fredrik

Remove inactive entries from transaction log and log shipping

When I try to take a Transaction log backup of a database (Recovery model
set to Full), I get an option to truncate the transaction log by checking
"Remove inactive entries from transaction log" check box in the backup
dialog. This basically causes the inactive portion of the transaction log
(where transaction is completes, either rolled back are committed) to be
truncated. So this potentially leads to saving of huge amount of hard disk
space.
Now, when I setup log shipping is there any similar option I can set on the
primary database? After every transaction log backup I want inactive entries
from the transaction log to be removed. Is there any way to do this? If yes,
how?. If no, why is that so? Once the backup is taken inactive log entries
on the primary database are of no use to log ship, right?
Rathna RajThe option in the backup dialog is very misleading. It basically means that
EM does *not* send the WITH NO_TRUNCATE option. This option is designed for
doing an "emergency" backup in case the database is corrupted, and I have
communicated to MS that the dialog shouldn't be misleading in this way. The
normal way to do log backup is by not specifying this option (having the
checkbox checked (I believe), the default). If the log shipping was written
by a sane individual ;-), then it should not specify this option, I.e., you
already have the desired behavior. To be certain, use a Profiler trace to
see what is going on.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Rathna Raj" <RathnaRajT@.icode.com> wrote in message
news:%2365JEWJyDHA.1272@.TK2MSFTNGP12.phx.gbl...
> When I try to take a Transaction log backup of a database (Recovery model
> set to Full), I get an option to truncate the transaction log by checking
> "Remove inactive entries from transaction log" check box in the backup
> dialog. This basically causes the inactive portion of the transaction log
> (where transaction is completes, either rolled back are committed) to be
> truncated. So this potentially leads to saving of huge amount of hard disk
> space.
> Now, when I setup log shipping is there any similar option I can set on
the
> primary database? After every transaction log backup I want inactive
entries
> from the transaction log to be removed. Is there any way to do this? If
yes,
> how?. If no, why is that so? Once the backup is taken inactive log entries
> on the primary database are of no use to log ship, right?
> Rathna Raj
>|||Hi Rathna,
Thanks for your post. Based on my research, log shipping does done backup
log part, so transaction log should not be large. Therefore, it seems that
truncating the transaction log of the primary server manually in log
shipping is not necessary.
For more information regarding log shipping, please refer to the following
article on SQL Server Books Online. Also, the example in this article will
help describe how the log shipping works.
Topic; "Log Shipping"
Thanks for using MSDN newsgroup.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.sql

remove ignored words - full text catalog

hey
when i try to search an "noise word" it drop me an ERROR
what i have done :
- stop full text service
- remove the words i want ,from noise.enu , noise.eng , noise.dat (@.@.language = u.s...) the files is in system32 and in program files\...\config .
- start full text service
-rebuild and after that re populate the catalog

and it still drop me an ERROR !!! how can i resolve this prob ?

tnx tnx .

Why are you storing SQL Server Full Text file in System 32, you don't create the part SQL Server creates the part. You just create the Text and NText datatype and upload the file SQL Server creates an Arithmetic pointer to the file and I know it cannot be in System 32. System 32 is in Windows folder SQL Server will not get a file from Windows folder. Run a search for Full Text, Microsoft Search Service, Micorsoft Catalog and CONTAINS, CONTAINSTABLE, FREETEXT and FREETEXTTABLE in SQL Server BOL(books online). Hope this helps.|||

no , i don't store the catalog in system 32 , the noise.* files are in library system32 and in ...\config .
when u install sql server , it copy the noise.* files to those librarys .
and correct , i use CONTAINS , i doesn't allways get this error , only when i search "ignored word"

|||

Try this ' "ignored word" ' or ' "ignored Wor*" ' it may make a difference. Hope this helps.

|||hye
sooo it doesn't give me an error ,BUT it doesn't give the "correct" answer exp' :
the phrase : "windows cd"
----------------
the search phrase : "cd"
it return "windows cd" <-CORRECT
----------------
----------------
the search phrase : "*c*"
it return "windows cd" <-NOT CORRECT
----------------
i dont need to search in part of the word .
so how can i search "c" that won't give me "ignored words" error & will search like all other words (that won't search part of the word , like the exm')
tnx a_r .
|||There are noise words try this link for better queries and more about noise words. Hope this helps.
http://www.databasejournal.com/features/mssql/article.php/3454281|||tnx .
but how can i remove those "noise words" ? that it won't give me the error .|||You did not read the aritcle it shows you how to edit that file. Hope this helps.|||hey , i read the article & and i did more that what the article intracte to do . see the first post .

Remove Identity seed w/ T-SQL

Does anyone know how to remove the Identity attribute of a column using T-SQL. I have created the Identity attribute with the following. I know I can do it with a script created through Enterprise Manager (which is really long and hard to re-create with Dynamic SQL), but I am looking for a one line opposite to the following statement:

IF (SELECT COL_LENGTH('[dbo].tablename', 'UserID')) IS NULL
ALTER TABLE [dbo].tablename ADD UserID INT NOT NULL IDENTITY (1, 1)

something like:
ALTER TABLE [dbo].tablename ALTER COLUMN UserID"REMOVE IDENTITY"

Thanks,
JoshThere is not a simple way to accomplish this. You need basically to drop the column altogether and then add it back to the table. as a non-identity column.

So you could add a new non-identity column to the table (UserIDNEW), copy your UserID data into it, drop the UserID column, then rename your UserIDNEW column back to UserID.

Wish I had better news.

I looked upthis article to explain more concretely what I am talking about.

Terri|||Terri,
I too found the same article. I didn't think there was a simple way to do it, but I thought I would ask.

Thanks,
Josh

Remove identity property of a primary key

Hi,
I have a table with a column named ID as primary key, this column has
the identity property. This ID is referenced by some other tables as foreign
key.
Is there a way, I can use "alter table alter ID int not null...." TSQL
to remove this identity property?
Thanks!
WWW: http://hardywang.1accesshost.com
ICQ: 3359839
yours Hardy1. drop FK
2. sp_rename table with identity to some other name
3. create table new with the same name without identity
4. insert new select * from old
5. create FK
6 drop old
"Hardy Wang" <hardywang@.hotmail.com> wrote in message
news:O7AqRnXrFHA.1252@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a table with a column named ID as primary key, this column has
> the identity property. This ID is referenced by some other tables as
> foreign key.
> Is there a way, I can use "alter table alter ID int not null...." TSQL
> to remove this identity property?
> Thanks!
> --
> WWW: http://hardywang.1accesshost.com
> ICQ: 3359839
> yours Hardy
>|||The only way is dropping the column. Try using EM if you really want to do
this. In EM, before saving changes, press button "save change script", third
from left to right in the tool bar. You will see what really EM does in orde
r
to accomplish this tak.
Example: (from northwind.orders)
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.Orders
DROP CONSTRAINT FK_Orders_Shippers
GO
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.Orders
DROP CONSTRAINT FK_Orders_Employees
GO
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.Orders
DROP CONSTRAINT FK_Orders_Customers
GO
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.Orders
DROP CONSTRAINT DF_Orders_Freight
GO
CREATE TABLE dbo.Tmp_Orders
(
OrderID int NOT NULL,
CustomerID nchar(5) NULL,
EmployeeID int NULL,
OrderDate datetime NULL,
RequiredDate datetime NULL,
ShippedDate datetime NULL,
ShipVia int NULL,
Freight money NULL,
ShipName nvarchar(40) NULL,
ShipAddress nvarchar(60) NULL,
ShipCity nvarchar(15) NULL,
ShipRegion nvarchar(15) NULL,
ShipPostalCode nvarchar(10) NULL,
ShipCountry nvarchar(15) NULL
) ON [PRIMARY]
GO
DECLARE @.v sql_variant
SET @.v = N''
EXECUTE sp_addextendedproperty N'MS_Description', @.v, N'user', N'dbo',
N'table', N'Tmp_Orders', N'column', N'OrderID'
GO
ALTER TABLE dbo.Tmp_Orders ADD CONSTRAINT
DF_Orders_Freight DEFAULT (0) FOR Freight
GO
IF EXISTS(SELECT * FROM dbo.Orders)
EXEC('INSERT INTO dbo.Tmp_Orders (OrderID, CustomerID, EmployeeID,
OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName,
ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry)
SELECT OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate,
ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion,
ShipPostalCode, ShipCountry FROM dbo.Orders (HOLDLOCK TABLOCKX)')
GO
ALTER TABLE dbo.[Order Details]
DROP CONSTRAINT FK_Order_Details_Orders
GO
DROP TABLE dbo.Orders
GO
EXECUTE sp_rename N'dbo.Tmp_Orders', N'Orders', 'OBJECT'
GO
ALTER TABLE dbo.Orders ADD CONSTRAINT
PK_Orders PRIMARY KEY CLUSTERED
(
OrderID
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX CustomerID ON dbo.Orders
(
CustomerID
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX CustomersOrders ON dbo.Orders
(
CustomerID
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX EmployeeID ON dbo.Orders
(
EmployeeID
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX EmployeesOrders ON dbo.Orders
(
EmployeeID
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX OrderDate ON dbo.Orders
(
OrderDate
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX ShippedDate ON dbo.Orders
(
ShippedDate
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX ShippersOrders ON dbo.Orders
(
ShipVia
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX ShipPostalCode ON dbo.Orders
(
ShipPostalCode
) ON [PRIMARY]
GO
ALTER TABLE dbo.Orders WITH NOCHECK ADD CONSTRAINT
FK_Orders_Customers FOREIGN KEY
(
CustomerID
) REFERENCES dbo.Customers
(
CustomerID
)
GO
ALTER TABLE dbo.Orders WITH NOCHECK ADD CONSTRAINT
FK_Orders_Employees FOREIGN KEY
(
EmployeeID
) REFERENCES dbo.Employees
(
EmployeeID
)
GO
ALTER TABLE dbo.Orders WITH NOCHECK ADD CONSTRAINT
FK_Orders_Shippers FOREIGN KEY
(
ShipVia
) REFERENCES dbo.Shippers
(
ShipperID
)
GO
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.[Order Details] WITH NOCHECK ADD CONSTRAINT
FK_Order_Details_Orders FOREIGN KEY
(
OrderID
) REFERENCES dbo.Orders
(
OrderID
)
GO
COMMIT
AMB
"Hardy Wang" wrote:

> Hi,
> I have a table with a column named ID as primary key, this column has
> the identity property. This ID is referenced by some other tables as forei
gn
> key.
> Is there a way, I can use "alter table alter ID int not null...." TSQ
L
> to remove this identity property?
> Thanks!
> --
> WWW: http://hardywang.1accesshost.com
> ICQ: 3359839
> yours Hardy
>
>

remove IDENTITY in sql server

suppose my table is
create table TEST (
id NUMERIC(10,0) IDENTITY(1,1) NOT NULL)
I want to remove IDENTITY(1,1) from id without dropping tableHi ankur
I suspect you are planning on sorting out your own no-gaps identity field here. This is a very common request but rarely is a good choice. Before you carry on I would really recommend you just carry for a little longer in this thread:
http://www.dbforums.com/showthread.php?t=1620216
It could save you a heck of a lot of wasted time if it turns out this is not the best way to procede.|||I am toatally new to sql server

so

initailly our project manager has ordered me to put auto incrment

but

now

PM has said to remove auto increment

I am in bit trouble dont know to remove auto increment

so

I asked help from you

help me please|||Look up the ALTER TABLE command in BOL. You will want to pay attention to the DROP COLUMN option.|||beh.

just tell him you removed it, but don't. PM shouldn't be making such low level design decisions!|||why don't you want to drop the table?

create a new one, copy over the numbers (after all, the table has only one column), then drop the old one and rename the new one

something about this whole scenario does not smell right

the project manager is actually you, isn't she|||Think bigger picture

If you still need to retain the id, what is the PM's plan to manage new id's?

And do you have a natural key?|||table is too large but I have taken a small example

there large data in I want not to drop

if there is any query are way to remove IDENTITY(1,1) without effecting
data|||table is too large but I have taken a small example

there large data in I want not to drop

if there is any query are way to remove IDENTITY(1,1) without effecting
data

No, but what's big? ALTER does not allow you to drop an IDENTITY Property on a Column...

As a matter of fact, do you know how save a script in Enterprise Mangler?

go create a test table with an identity column

Then go into EM and remove the identity column and save it...it wil ask you if you want to save the script SQL Server used to make the change...you'll see what SQL Server has to do

Well I can tell you..it it will copy the data to another table where the column will be defined as int, drop the old table and rename the new table to the old table...but go look for yourself

Personally, I'd bdp the data out in native format, create a new table with the definition you want, bcp the data in, rename the old table to something else like table_old, the rename the new table to the old

Again, how many rows are we talking about

Oh...you can disable the identity column in your stored procedures...you are using sprocs as data access only right...and then do

SET IDENTITY_INSERT ON

But that's a hack|||And do you have a natural key?as if this would make a difference to the yes-identity-no-wait-a-sec-no-identity PM|||I want to remove IDENTITY(1,1) from id without dropping tableDropping the identity property can not be done directly, but you can acheive similar effects in more than one way. The problem is that there are many reasons for not wanting to drop the table, and without understanding exactly what you are trying to do or avoid I can't give you clear instructions.

The short answer is to simply recreate a working copy of the table without the identity property, drop the original table, then rename the working copy so it has the same name as the original. This process is easy, safe, and relatively foolproof.

There are a number of other solutions, but without understanding your requirements I have no idea which solution or solutions to suggest.

-PatP|||This may just be naivety on my part but why can't you add a new int column and copy the identity values into the new column, then drop the identity column?|||This may just be naivety on my part but why can't you add a new int column and copy the identity values into the new column, then drop the identity column?Not naive at all George - that's the way I would do it (though with decimal(10) so you can insert half a million records a day for 6 years). There is a downside to that though:
http://www.nigelrivett.net/SQLAdmin/AlterTableProblems.html|||Ahh I see!
Thanks Poots :)

I guess as long as you're aware of the potential problems you can use your best judgement per case.|||Yes I got my answer to remove IDENTITY from column

see attachment set IDENTITY to NO|||see attachment set IDENTITY to NOJust curioius, but do you have any clue what setting IDENTITY to "NO" on this screen will actually do? It will produce exactly the result you have requested, using exactly the method that you wanted to avoid.

As long as you are happy with what you've found, that's fine. It just isn't what you requested! ;)

-PatP|||Yes I got my answer to remove IDENTITY from column

see attachment set IDENTITY to NO

To quote a Guiness Stout Commercial...

"Brilliant!"|||go create a test table with an identity column

Then go into EM and remove the identity column and save it...it wil ask you if you want to save the script SQL Server used to make the change...you'll see what SQL Server has to do

That sounds oddly familiar

Remove Identity column constraint/mgmt

Hello...
We have some tables that had been using Identity columns as a Primary
Key...but we abandoned that approach a few weeks ago and adopted GUIDs
instead.
These tables are included in Publications that were originally on SQL
2000...but we upgraded to SQL 2005.
Is there a way I can remove the Identity constraint from the server
pub...does the Not for Replication handle this?
Or...would be better to Drop the column and reinitialize the subscribers?
thanks for any help
- will
You would be best to drop the identity column, NFR will not drop the
identity column but will not enforce the identity property if the insert is
caused by a replication process.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"dw" <dw@.discussions.microsoft.com> wrote in message
news:16F706A2-F1D3-4215-9A70-DDDD5575920E@.microsoft.com...
> Hello...
> We have some tables that had been using Identity columns as a Primary
> Key...but we abandoned that approach a few weeks ago and adopted GUIDs
> instead.
> These tables are included in Publications that were originally on SQL
> 2000...but we upgraded to SQL 2005.
> Is there a way I can remove the Identity constraint from the server
> pub...does the Not for Replication handle this?
> Or...would be better to Drop the column and reinitialize the subscribers?
> thanks for any help
> - will
|||Thanks for the help. I may test out going the NFR route...just so I don't
have to mess with the table schema too much. Will changing to NFR force a
Re-Init of for the subscribers?
"Hilary Cotter" wrote:

> You would be best to drop the identity column, NFR will not drop the
> identity column but will not enforce the identity property if the insert is
> caused by a replication process.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "dw" <dw@.discussions.microsoft.com> wrote in message
> news:16F706A2-F1D3-4215-9A70-DDDD5575920E@.microsoft.com...
>
>
|||This is something you do on the subscriber - so for transactional
replication it will have no impact on reinitialization. For merge and
updateable subscribers it will.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"dw" <dw@.discussions.microsoft.com> wrote in message
news:C109A5B7-07DE-4E85-88C5-6BA71CBCCEFD@.microsoft.com...[vbcol=seagreen]
> Thanks for the help. I may test out going the NFR route...just so I
> don't
> have to mess with the table schema too much. Will changing to NFR force a
> Re-Init of for the subscribers?
> "Hilary Cotter" wrote:
sql