Deleting extra tempdb log and data files

We had someone create an extra data file and log file for tempdb. So
we currently have two data files and two log files. Is it possible to
delete the newly created data and log files? If I just delete the
physical files, I assume they'll get created as soon as SQL Server
gets started back up. Any help would be great, since a single data
and log file for tempdb is my goal.

Thanks much.

sean"Sean Lambert" <slambert007@.yahoo.com> wrote in message
news:279f38c0.0309241527.1e7b2546@.posting.google.c om...
> We had someone create an extra data file and log file for tempdb. So
> we currently have two data files and two log files. Is it possible to
> delete the newly created data and log files? If I just delete the
> physical files, I assume they'll get created as soon as SQL Server
> gets started back up. Any help would be great, since a single data
> and log file for tempdb is my goal.

You should be able to simply shut down SQL Server, delete them (back them up
just in case :-) and start it back.

(assuming SQL 2000).

If not, I'd probably try starting SQL Server in single user mode and
removing them there.

But, again, back up everything etc.

> Thanks much.
>
> sean|||slambert007@.yahoo.com (Sean Lambert) wrote in message news:<279f38c0.0309241527.1e7b2546@.posting.google.com>...
> We had someone create an extra data file and log file for tempdb. So
> we currently have two data files and two log files. Is it possible to
> delete the newly created data and log files? If I just delete the
> physical files, I assume they'll get created as soon as SQL Server
> gets started back up. Any help would be great, since a single data
> and log file for tempdb is my goal.
> Thanks much.
>
> sean

Assuming you have SQL2000, then this covers it:

http://support.microsoft.com/?kbid=814576

In summary, use DBCC SHRINKFILE with EMPTYFILE to remove any data on
the files, then ALTER DATABASE ... REMOVE FILE.

Simon|||I'm using SQL7. Sorry...I should have mentioned that initially. Thanks!

sean

sql@.hayes.ch (Simon Hayes) wrote in message news:<60cd0137.0309250040.1a1813d6@.posting.google.com>...
> slambert007@.yahoo.com (Sean Lambert) wrote in message news:<279f38c0.0309241527.1e7b2546@.posting.google.com>...
> > We had someone create an extra data file and log file for tempdb. So
> > we currently have two data files and two log files. Is it possible to
> > delete the newly created data and log files? If I just delete the
> > physical files, I assume they'll get created as soon as SQL Server
> > gets started back up. Any help would be great, since a single data
> > and log file for tempdb is my goal.
> > Thanks much.
> > sean
> Assuming you have SQL2000, then this covers it:
> http://support.microsoft.com/?kbid=814576
> In summary, use DBCC SHRINKFILE with EMPTYFILE to remove any data on
> the files, then ALTER DATABASE ... REMOVE FILE.
> Simon

Delete Query ?

>> Does the OP want portability? Why spend extra effort in coding for someth
ing that may never be required (ever), lets face it an extremely small propo
rtion of dev's need SQL portability. <<
I totally disagree with that last statement, based on 35 years in the
trade. Proprietary code bites you in ass from product to product and
even from release to release of the same product. To think that
writing Standard SQL is an "extra effort" is simply wrong. Even for
good programmers, trying to remember a dozen different syntaxes is more
effort than to use the real language.
Run the code; read the standards.
I also used a fixed length CHAR(n) column because the fixed length is a
good check on the data element that can put into a regular expression
and make maintaining the table easier. VARCHAR(n) can save storage,
but at the expense of access time and indexing.> I totally disagree with that last statement, based on 35 years in the
> trade. Proprietary code bites you in ass from product to product and
> even from release to release of the same product. To think that
> writing Standard SQL is an "extra effort" is simply wrong. Even for
> good programmers, trying to remember a dozen different syntaxes is more
> effort than to use the real language.
35 years, 20 years makes no difference, its what you have done in that time,
for the past 20 years i've been cutting code using a number of different
databases and languages so i can speak from developer experience - can you
(really) or is it just SQL?
Propritary code does not bite you in the ass, standard SQL is a myth, it
behaves different between products anyway - locking behaviour, query
behaviour etc...
Tools between products are different as well, the way products work etc...
Writing standard code involves significantly more effort, you need to test
for each platform you tell your customers you support - is that effort
really worth it? There are only a few companies that think so.
Good programmers know to take advantage of the tools that are in front of
them to get the most efficient business solution for the business problem
they are coding for, they do not indulge themselves in IT solutions for the
sake of a long lost and forgotten portability standard.
Products are diverging release by release.

> I also used a fixed length CHAR(n) column because the fixed length is a
> good check on the data element that can put into a regular expression
> and make maintaining the table easier. VARCHAR(n) can save storage,
> but at the expense of access time and indexing.
That is just complete rubbish - check the product specs as you put it, using
varchar is not at the expense of access time nor indexing, in fact its
better because the data is on less pages so you need less IO's, less locking
etc...
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1132863499.248876.91150@.g44g2000cwa.googlegroups.com...
> I totally disagree with that last statement, based on 35 years in the
> trade. Proprietary code bites you in ass from product to product and
> even from release to release of the same product. To think that
> writing Standard SQL is an "extra effort" is simply wrong. Even for
> good programmers, trying to remember a dozen different syntaxes is more
> effort than to use the real language.
>
> Run the code; read the standards.
> I also used a fixed length CHAR(n) column because the fixed length is a
> good check on the data element that can put into a regular expression
> and make maintaining the table easier. VARCHAR(n) can save storage,
> but at the expense of access time and indexing.
>

Delete one of the duplicated row

I have many data in a table in which some rows are duplicated. How can I, for all duplicated rows, delete the extra rows and leave only one? You may assume checking one column is enough to tell if a row is duplicated.

Thanks

Step one: create a second table with identical structure: yourSecondTable;

Step two: CREATE UNIQUE INDEX removedups ON yourSecondTable (col1) WITH IGNORE_DUP_KEY

Step three:

INSERT yourSecondTable

SELECT * FROM yourFirstTable

--Duplicate key was ignored.

|||

DECLARE @.FirstName varchar(20),
@.LastName varchar(20),
@.Age int --declare all fields in table

DECLARE c1 CURSOR FOR

--Find Dupes
SELECT FirstName, LastName, Age
FROM MyTable
GROUP BY FirstName, LastName, Age
HAVING COUNT(FirstName) > 1

OPEN c1
FETCH NEXT FROM c1
INTO @.FirstName, @.LastName, @.Age
WHILE @.@.FETCH_STATUS = 0

BEGIN

--Delete all dupes...the cursor remembers the current record
DELETE FROM MyTable
WHERE FirstName IN (SELECT FirstName FROM MyTable GROUP BY FirstName HAVING COUNT(FirstName) > 1)

--insert the current record back into the table
INSERT INTO MyTable(FirstName, LastName, Age) VALUES(@.FirstName, @.LastName, @.Age)

FETCH NEXT FROM c1
INTO @.FirstName, @.LastName, @.Age
END

CLOSE c1
DEALLOCATE c1

Adamus

|||

Please look at my reply in the thread below for some solutions. You can do this with a single DELETE statement and batch it using SET ROWCOUNT or TOP clause if you have large number of rows to delete. And if you don't have any primary key or unique key on the table then you can use a cursor based approach.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=666011&SiteID=1&PageID=1

|||

Umachandar Jayachandran - MS wrote:

Please look at my reply in the thread below for some solutions. You can do this with a single DELETE statement and batch it using SET ROWCOUNT or TOP clause if you have large number of rows to delete. And if you don't have any primary key or unique key on the table then you can use a cursor based approach.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=666011&SiteID=1&PageID=1

Question. Why would the absence of a primary key be a determinant in selecting a cursor based approach?

Adamus

|||Because there is no easy way to determine which row to keep out of the duplicates which you can do easily in a single DELETE statement without writing procedural code.