release database unused space

Hi,
I have deleted 100K records from a table in a database. I ran the DBCC shrinkfile & DBCC shrinkdatabase to have the database release the space. I see no reduction in the database size.
I have run the following;
DBCC SHRINKFILE (Virten1_dat, 100) -- Data File Name
GO
DBCC SHRINKFILE (Virten3_dat, 100) -- Log File Name
GO
BACKUP LOG VIRTEN WITH TRUNCATE_ONLY
GO
DBCC SHRICKDATABASE (VIRTEN, 20)
GO
Backup database VIRTEN to disk = 'F:\DBBkp\VIRTEN.dmp'
GO
Please can anyone tell me, where I am making a mistake, or provide me with another method to release the space.
Many thanks.
DanielPerhaps you need to defrag the indexes first? (DBCC DBREINDEX or DBCC INDEXDEFRAG.)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Daniel" <danieljantony@.hotmail.com> wrote in message
news:D364CB21-9A8C-4D7A-942B-0C2038BA0675@.microsoft.com...
> Hi,
> I have deleted 100K records from a table in a database. I ran the DBCC shrinkfile & DBCC shrinkdatabase to
have the database release the space. I see no reduction in the database size.
> I have run the following;
> DBCC SHRINKFILE (Virten1_dat, 100) -- Data File Name
> GO
> DBCC SHRINKFILE (Virten3_dat, 100) -- Log File Name
> GO
> BACKUP LOG VIRTEN WITH TRUNCATE_ONLY
> GO
> DBCC SHRICKDATABASE (VIRTEN, 20)
> GO
> Backup database VIRTEN to disk = 'F:\DBBkp\VIRTEN.dmp'
> GO
> Please can anyone tell me, where I am making a mistake, or provide me with another method to release the
space.
> Many thanks.
> Daniel|||What Tibor is alluding to is that fact that just because you deleted a bunch
of records, it doesn't mean that pages have been freed. If the pattern of
your deletes is such that only one record per page is deleted, no pages will
be deallocated and so no space can be reclaimed by shrink.
You can compact such pages using DBCC DBREINDEX or DBCC INDEXDEFRAG (if you
have a clustered index). There's no such mechanism for heaps except bcp
out/in.
You should also be aware that if you shrink the database it will most likely
grow again if there is any insert/update activity on it.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:evs41PvTEHA.3336@.TK2MSFTNGP10.phx.gbl...
> Perhaps you need to defrag the indexes first? (DBCC DBREINDEX or DBCC
INDEXDEFRAG.)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Daniel" <danieljantony@.hotmail.com> wrote in message
> news:D364CB21-9A8C-4D7A-942B-0C2038BA0675@.microsoft.com...
> > Hi,
> >
> > I have deleted 100K records from a table in a database. I ran the DBCC
shrinkfile & DBCC shrinkdatabase to
> have the database release the space. I see no reduction in the database
size.
> >
> > I have run the following;
> > DBCC SHRINKFILE (Virten1_dat, 100) -- Data File Name
> > GO
> > DBCC SHRINKFILE (Virten3_dat, 100) -- Log File Name
> > GO
> > BACKUP LOG VIRTEN WITH TRUNCATE_ONLY
> > GO
> > DBCC SHRICKDATABASE (VIRTEN, 20)
> > GO
> > Backup database VIRTEN to disk = 'F:\DBBkp\VIRTEN.dmp'
> > GO
> >
> > Please can anyone tell me, where I am making a mistake, or provide me
with another method to release the
> space.
> >
> > Many thanks.
> >
> > Daniel
>

release database unused space

Hi,
I have deleted 100K records from a table in a database. I ran the DBCC shrin
kfile & DBCC shrinkdatabase to have the database release the space. I see no
reduction in the database size.
I have run the following;
DBCC SHRINKFILE (Virten1_dat, 100) -- Data File Name
GO
DBCC SHRINKFILE (Virten3_dat, 100) -- Log File Name
GO
BACKUP LOG VIRTEN WITH TRUNCATE_ONLY
GO
DBCC SHRICKDATABASE (VIRTEN, 20)
GO
Backup database VIRTEN to disk = 'F:\DBBkp\VIRTEN.dmp'
GO
Please can anyone tell me, where I am making a mistake, or provide me with a
nother method to release the space.
Many thanks.
DanielPerhaps you need to defrag the indexes first? (DBCC DBREINDEX or DBCC INDEXD
EFRAG.)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Daniel" <danieljantony@.hotmail.com> wrote in message
news:D364CB21-9A8C-4D7A-942B-0C2038BA0675@.microsoft.com...
> Hi,
> I have deleted 100K records from a table in a database. I ran the DBCC shrinkfile
& DBCC shrinkdatabase to
have the database release the space. I see no reduction in the database size.">
> I have run the following;
> DBCC SHRINKFILE (Virten1_dat, 100) -- Data File Name
> GO
> DBCC SHRINKFILE (Virten3_dat, 100) -- Log File Name
> GO
> BACKUP LOG VIRTEN WITH TRUNCATE_ONLY
> GO
> DBCC SHRICKDATABASE (VIRTEN, 20)
> GO
> Backup database VIRTEN to disk = 'F:\DBBkp\VIRTEN.dmp'
> GO
> Please can anyone tell me, where I am making a mistake, or provide me with another
method to release the
space.
> Many thanks.
> Daniel|||What Tibor is alluding to is that fact that just because you deleted a bunch
of records, it doesn't mean that pages have been freed. If the pattern of
your deletes is such that only one record per page is deleted, no pages will
be deallocated and so no space can be reclaimed by shrink.
You can compact such pages using DBCC DBREINDEX or DBCC INDEXDEFRAG (if you
have a clustered index). There's no such mechanism for heaps except bcp
out/in.
You should also be aware that if you shrink the database it will most likely
grow again if there is any insert/update activity on it.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:evs41PvTEHA.3336@.TK2MSFTNGP10.phx.gbl...
> Perhaps you need to defrag the indexes first? (DBCC DBREINDEX or DBCC
INDEXDEFRAG.)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Daniel" <danieljantony@.hotmail.com> wrote in message
> news:D364CB21-9A8C-4D7A-942B-0C2038BA0675@.microsoft.com...
shrinkfile & DBCC shrinkdatabase to[vbcol=seagreen]
> have the database release the space. I see no reduction in the database
size.
with another method to release the[vbcol=seagreen]
> space.
>sql

Relationships vs. no relationships

Hello!

I have a really small database in terms of number of objects, but very big in terms of data (possibly millions of records). I always try to design my databases to be in the 3rd normal form (at least I hope they are!), but sometimes (for databases consisting of just a few tables) maintaining M:N relationships looks like an over-bloated approach to me... Let's see an actual example. I have a table like this:

Code Snippet

Create table [Airfares]
(
[OriginCity] Char(3) NOT NULL,
[DestCity] Char(3) NOT NULL,
[FareDate] Datetime Default getdate() NOT NULL,
[Price] Decimal(9,2) Default 0 NOT NULL,
[PriceChange] Decimal(9,2) Default 0 NOT NULL,
[Wrong] Bit Default 0 NOT NULL,
Primary Key ([OriginCity],[DestCity],[FareDate])
)
go

Now, at first there also was an Airline nvarchar(50) field, which stored the airline name. But now the client requested to store fares for a few airlines if it's the same price and city pair. This way we can have 3 airlines offering the same price on the same itinerary for example. So I added a dictionary table called Airlines:

Code Snippet

Create table [Airlines]
(
[AirlineCode] Char(2) NOT NULL,
[Name] Nvarchar(50) NOT NULL,
Primary Key ([AirlineCode])
)
go

Now, there are two things I can do. We are obviously dealing with the M:N relationship type here. I can either add another nvarchar column in the first table, like Airlines nvarchar(255) and store something like 'AA,AB,AC' there - those would be airline codes. OR I could do it the proper way and create a cross-reference table which will connect both Airfares and Airlines tables (this will store as many records as there are airlines for that particular itinerary, offerring the same price):

Code Snippet

Create table [AirfareAirlines]
(
[AirlineCode] Char(2) NOT NULL,
[OriginCity] Char(3) NOT NULL,
[DestCity] Char(3) NOT NULL,
[FareDate] Datetime Default getdate() NOT NULL,
Primary Key ([AirlineCode],[OriginCity],[DestCity],[FareDate])
)
go

But this is starting to look overly complicated, isn't it? The pros of this are I'm going to get real relationships, referential integrity, etc., but cons are more difficult code to query all this. What would you do? Do we really need referential integrity everywhere? Are there times when we're better not using relationships? I have seen many programmers skip referential integrity before, which always made me think they are missing something important in a Relational Database Management System.

The second approach is of course much better. Referential integrity should not be that much of an issue. If you design the database optimally with the correct indexes the performance should be fine.

The downside of the first approach is 2 main isses from my point of view:

You will have to parse the text field in order to find the relations between the airfares and the airlines. This is a performance issue and in the same time it you might not be able to retrieve certain information as easily as you would with a database that is structured with correct relationships (searching for airfares that belong to an airline is one that comes in mind. You have to use LIKE of do a full text index) It violates the main concept of referntial integrity where you can delete an Airline (I know this might not really make sense in your case) and not remove it from the airfares (or not remove all the references). With relationships you can easily delete references without having to handle them yourselves|||

I think that it is a mistake to assume that at all times, all airlines serving the same city pair will have the same prices and same fare dates.

In my opinion, you don't need a third table at this point, add the AirlineCode to the Airfares table. Don't make it more complex than required.

|||

Arnie Rowland wrote:

I think that it is a mistake to assume that at all times, all airlines serving the same city pair will have the same prices and same fare dates.

Of course not. I'm sorry, an explanation is needed - FareDate is actually the date on which I inserted a record to the database, not a date of flight. It's called FareDate because both Date and DateTime are reserved keywords and I'd have to use square brackets in SQL everywhere.

|||

Not a problem. (Often tables have columns such as [CreateDate], [CreateUser], [ChangeDate], [ChangeUser], etc. so as to clearly indicate that it is metadata and not part of the dataset.)

Still, based upon your initial post, I think that you would be best served with only two tables.

|||To make it even more clear - I am inserting only the lowest fare, but in case there are more arlines with the same price for the same itinerary - I need to store all of them. And I can do that by placing them in a comma separated list in one field, or using a cross table as required by database normalization - I'm trying to decide on the best approach here. |||

Stay away from comna delimited list of values in a single field. I have had to 'rescue' many clients from that horrendous mistake.

You never err to stay fully normalized.

|||

Arnie Rowland wrote:

Stay away from comna delimited list of values in a single field. I have had to 'rescue' many clients from that horrendous mistake.

As a matter of fact, the comma delimited list of airline codes was client's idea. I didn't like it, because I would never think of doing things that way (but I know programmers who do this - worked with them), but thought I might as well ask other developers at MSDN Forums.

I will go with the normalized method.

Thanks.

|||

If you have SQL Server 2005 and you want to present a comma separated view of the normalized data then here is a sample of a trick using the xml functionality (text() and elements) in SQL Server (from an earlier post).

Code Snippet

DECLARE @.ItemTable table

(

ItemID int,

ItemNumber int,

TypeDesc varchar(20)

)

INSERT INTO @.ItemTable Values ( 1, 50000, 'CD' )

INSERT INTO @.ItemTable Values ( 2, 50000, 'Cassette' )

INSERT INTO @.ItemTable Values ( 3, 50001, 'CD' )

INSERT INTO @.ItemTable Values ( 4, 50002, 'CD' )

INSERT INTO @.ItemTable Values ( 5, 50002, 'Cassette' )

INSERT INTO @.ItemTable Values ( 6, 50002, 'DVD' )

INSERT INTO @.ItemTable Values ( 7, 50003, 'Cassette' )

-- Create Delimited list from multiple rows

-- From Tony Roberson

-- SQL 2005

SELECT DISTINCT ItemNumber, List = SUBSTRING(

(

SELECT ', ' + TypeDesc as [text()]

FROM @.ItemTable Det

WHERE Det.ItemNumber = Itm.ItemNumber

FOR XML path(''), elements

), 3, 4096

)

FROM @.ItemTable Itm

This will just run in a query window.

|||

I have a follow-up question. You already know the structure of those three tables, but additionally I need to partition data in Airfares, because it will grow too large and it needs to be really fast, so I will be creating a table called AirfaresHistory and moving old records there. Now, if I wanted to force integrity checks / relationships, I would have to create also an additional cross table to connect AirfaresHistory and Airlines, too. If I don't create relationships, I can have just one cross table and don't need to touch data in it when I move records from Airfares to Airfares history.

I can't use the partitioning feature from SQL Server 2005, because neither I nor the client has the Enterprise Edition, so I have to resort to old methods of creating member tables and a partitioned view. What should I do about relationships in such design? I highly doubt that when you have like 30 member tables you connect each of them to some cross table, do you? It looks like in order to stay sane you should only have informational (on the ERD) relationships when dealing with member tables. Unless you should partition cross tables as well?

|||

You didn't ask for any design comments, but here goes...

It does seem to me that the Airfares table is mis-named. Wouldn't it be more descriptive to name that table something like 'Segments', or 'Routes', or Legs?

And that the table that is most likely to experience growth is what is currently called AirfaresAirlines (which may be more acturately called 'Airfares')? Each Airfare should have a StartDate and an ExpirationDate.

Then you would have an Airline, that serves a Segment, and there would be one or more Airfares for each Airline/Segment during a specific date/time period ...

(The choice of object names seemed a bit confusing for the entities in question.)

I wouldn't worry too much about maintaining relationships on historical data. I would consider NOT allowing data to be deleted, but to have an Active/Inactive flag field.

I consider that the primary purpose of PK-FK relationships is to force all relevant pieces of data to be inserted into the database when a new record is entered.

Relational Data and RDA

I have a few questions for you guys. I have a client

application that can be offline or online. While offline, records can be added and need to be later synced to production.

I will use rda to pull the table down, and this is working fine. Now what if I

have multiple tables where I want a foreign key relationship?

With rda I can only pull down one table at a time from

everything I've read. Now say create a constraint after pulling the two or more tables down. While in offline mode I create a new record on two seperate tables with foreign key/primary key relationship.

When I do the push to the server will it automatically

update the foreign key reference (locally) to the right one on the production

server? Or will I get a duplicate primary key error? On the production server the primary key will be different because of the identity. This is very important because I will have multiple clients.

Thanks in advance

Yes, you can add a foreign key constraint to a pulled table with tracking turned on and when you push it back to the server the key will be created there as well. This is one of the few schema changes that you can propagate back to the server.

When pushing your updates back to the server, no, the push does not also updated foreign key tables. This means if you have table A and table B and B contains a column that is an FK to a column in Table A, you need to always push A then B when doing your RDA push back to the server to prevent an referential integrity issue.

In anything more complex than this in terms of constraints or change tracking, you'll need to consider using merge replication.

Darren

sql

Relational Data and RDA

I have a few questions for you guys. I have a client

application that can be offline or online. While offline, records can be added and need to be later synced to production.

I will use rda to pull the table down, and this is working fine. Now what if I

have multiple tables where I want a foreign key relationship?

With rda I can only pull down one table at a time from

everything I've read. Now say create a constraint after pulling the two or more tables down. While in offline mode I create a new record on two seperate tables with foreign key/primary key relationship.

When I do the push to the server will it automatically

update the foreign key reference (locally) to the right one on the production

server? Or will I get a duplicate primary key error? On the production server the primary key will be different because of the identity. This is very important because I will have multiple clients.

Thanks in advance

Yes, you can add a foreign key constraint to a pulled table with tracking turned on and when you push it back to the server the key will be created there as well. This is one of the few schema changes that you can propagate back to the server.

When pushing your updates back to the server, no, the push does not also updated foreign key tables. This means if you have table A and table B and B contains a column that is an FK to a column in Table A, you need to always push A then B when doing your RDA push back to the server to prevent an referential integrity issue.

In anything more complex than this in terms of constraints or change tracking, you'll need to consider using merge replication.

Darren

Relation between tables

Guys,

I have a "PABX' that records all callls did..and I send it to a table and my call operator send me my account details eletronically so I can import it to another table

So in both tables I have

Date and time

Telephone number

Location(of the call city - state)

Call type(DDD, DDI e LOCAL, VC* used for cell phone)

Duration(in minutes)

For both cases i need to

-recover all rows that the operator table has and mine don't

-recover all rows that the my table has and operator don't

-recover all rows that are in both tables

But there is some problens

- the duration of the calls are different from one table to another

- the datetime is different(the day is the same but the time don't)

Any suggestions examples I would appreciate...I have spent a long time on it and did'nt got a good result..

Hi,

Let's me analyze the situation :

- The operator time is different from your local time. So the time cannot be in the matching condition. However the Date can be included in the criteria. Of course for calls between 00:00 +- n seconds u can have problems because u can have 1 day difference between the two dates. But i think we can assume this.

- The Telephone number, the call city and the call type can be included in the matching criteria.

- The duration can be also included in the matching criteria because if there is some difference you want it to be logged in the differences list.

Correct me if i am wrong.

I Created two tables : OperatorLog and PABXLog with theses columns (CallDate, PhoneNumber, Location, CallType, Duration).

Here the sql need for each request :

- all rows that the operator table has and yours don't :

Code Snippet

SELECT dbo.OperatorLog.PhoneNumber, dbo.OperatorLog.Location, dbo.OperatorLog.CallType, dbo.OperatorLog.Duration, dbo.OperatorLog.CallDateTime
FROM dbo.OperatorLog LEFT OUTER JOIN
dbo.PABXLog ON DAY(dbo.OperatorLog.CallDateTime) = DAY(dbo.PABXLog.CallDateTime) AND MONTH(dbo.OperatorLog.CallDateTime)
= MONTH(dbo.PABXLog.CallDateTime) AND YEAR(dbo.OperatorLog.CallDateTime) = YEAR(dbo.PABXLog.CallDateTime) AND
dbo.OperatorLog.Duration = dbo.PABXLog.Duration AND dbo.OperatorLog.CallType = dbo.PABXLog.CallType AND
dbo.OperatorLog.Location = dbo.PABXLog.Location AND dbo.OperatorLog.PhoneNumber = dbo.PABXLog.PhoneNumber
WHERE (dbo.PABXLog.PhoneNumber IS NULL)

- all rows that the your table has and operator don't (reversed query) :

Code Snippet

SELECT dbo.PABXLog.PhoneNumber, dbo.PABXLog.Location, dboPABXLog.CallType, dbo.PABXLog.Duration, dbo.PABXLog.CallDateTime
FROM dbo.PABXLog LEFT OUTER JOIN
dbo.OperatorLog ON DAY(dbo.PABXLog.CallDateTime) = DAY(dbo.OperatorLog.CallDateTime) AND MONTH(dbo.PABXLog.CallDateTime)
= MONTH(dbo.OperatorLog.CallDateTime) AND YEAR(dbo.PABXLog.CallDateTime) = YEAR(dbo.OperatorLog.CallDateTime) AND
dbo.PABXLog.Duration = dbo.OperatorLog.Duration AND dbo.PABXLog.CallType = dbo.OperatorLog.CallType AND
dbo.PABXLog.Location = dbo.OperatorLog.Location AND dbo.PABXLog.PhoneNumber = dbo.OperatorLog.PhoneNumber
WHERE (dbo.OperatorLog.PhoneNumber IS NULL)

- All matching rows :

Code Snippet

SELECT dbo.OperatorLog.PhoneNumber, dbo.OperatorLog.Location, dbo.OperatorLog.CallType, dbo.OperatorLog.Duration, dbo.OperatorLog.CallDateTime
FROM dbo.OperatorLog LEFT OUTER JOIN
dbo.PABXLog ON DAY(dbo.OperatorLog.CallDateTime) = DAY(dbo.PABXLog.CallDateTime) AND MONTH(dbo.OperatorLog.CallDateTime)
= MONTH(dbo.PABXLog.CallDateTime) AND YEAR(dbo.OperatorLog.CallDateTime) = YEAR(dbo.PABXLog.CallDateTime) AND
dbo.OperatorLog.Duration = dbo.PABXLog.Duration AND dbo.OperatorLog.CallType = dbo.PABXLog.CallType AND
dbo.OperatorLog.Location = dbo.PABXLog.Location AND dbo.OperatorLog.PhoneNumber = dbo.PABXLog.PhoneNumber

---

HTH.

Hayder Marzouk

|||

Thanks

Yes...you're correct

I did something similar...I'm gonna test it tomorrow and give you the answer..tahnks a lot

|||OK thanks...Did'nt work for all registries but it's a good beginning...

Relating records with date ranges to Server Time dimension

I have a table whose records have a date range defined by a start_date and end_date. I would like to create a server time dimension so that I can relate each record in the table to a date in the time dimension when it is 'active' (i.e. the dimension date falls between the start_date and end_date inclusive). I am lost on how to accomplish this and any help is appreciated.

DW

DW, I would consider adding a column called "is_active" on the fact table. The SQL query would then include this column in the where clause. I would not limit the time dimension to just the active date range.
Vincent

related rooms query

hi, im working on this for a long time. i'm using MSsql-server2000
i have a table [visits] that records users visits to rooms. the columns are
room_id, user_id, visits.
i want to write a query that can calculate the top 10 rooms that are related
to any given room. i was thinking of firstly making a function that counts
how many users visited both room A and room B, and then running this
function on A and all other rooms, and order by the result. i keep getting
weird errors when doing that. please elaborate.Hi

Please post DDL (Create table statements you can use the generate SQL script
option in EM), example data (as insert statements), expected output and your
current queries. That will remove any ambiguity

It is not clear how you relate a users movement from one room to another,
what if the user has two browsers or shortcuts to specific rooms?

John

"Uri Lazar" <arielazar@.bezeqint.net> wrote in message
news:3f89b64e@.news.bezeqint.net...
> hi, im working on this for a long time. i'm using MSsql-server2000
> i have a table [visits] that records users visits to rooms. the columns
are
> room_id, user_id, visits.
> i want to write a query that can calculate the top 10 rooms that are
related
> to any given room. i was thinking of firstly making a function that counts
> how many users visited both room A and room B, and then running this
> function on A and all other rooms, and order by the result. i keep getting
> weird errors when doing that. please elaborate.|||Without DDL and example data I'm not sure I've fully understood your
requirement. Here's some assumed DDL and sample data:

CREATE TABLE RoomVisits (roomid INTEGER NOT NULL /* REFERENCES Rooms
(roomid) */, userid INTEGER NOT NULL /* REFERENCES Users (userid) */, visits
INTEGER NOT NULL CHECK (visits>0), PRIMARY KEY (roomid, userid))

INSERT INTO RoomVisits VALUES (1,100,1)
INSERT INTO RoomVisits VALUES (2,100,1)
INSERT INTO RoomVisits VALUES (3,100,4)
INSERT INTO RoomVisits VALUES (4,100,2)
INSERT INTO RoomVisits VALUES (1,222,2)
INSERT INTO RoomVisits VALUES (2,222,4)

Apparently for each room "A" you want the top 10 related rooms "B", ordered
by total number of visits to B. Rooms are deemed related if any user has
visited both - is that correct? If so, it seems a slightly artificial
requirement. Surely by that definition if users are making tours of rooms
then every room will inevitably become related to every other, unless there
are many more rooms than users.

Anyway, here's the query. First create a view which lists each related A-B
combination and the corresponding total number of visits to B.

CREATE VIEW Related_Room_Visits (room_A, room_B, visits_to_B)
AS
SELECT A.roomid, B.roomid, MAX(C.tot_visits)
FROM RoomVisits AS A
JOIN RoomVisits AS B
ON A.userid = B.userid AND A.roomid <> B.roomid
JOIN
(SELECT roomid, SUM(visits) AS tot_visits
FROM RoomVisits
GROUP BY roomid) AS C
ON B.roomid = C.roomid
GROUP BY A.roomid, B.roomid

Now display just the Top N for each room A. For my example data I've just
specified TOP 2 but you can change this as required:

SELECT R1.room_A, R1.room_B, R1.visits_to_B
FROM Related_Room_Visits AS R1
JOIN Related_Room_Visits AS R2
ON R1.room_A=R2.room_A AND R1.visits_to_B <= R2.visits_to_B
GROUP BY R1.room_A, R1.room_B, R1.visits_to_B
HAVING COUNT(*) <= 2 /* Top 2 for each Room_A */
ORDER BY R1.room_A, R1.room_B, R1.visits_to_b DESC

If this doesn't help then please post DDL, post some sample data as INSERT
statements and give an example of your required result.

--
David Portas
----
Please reply only to the newsgroup
--

Related Records Inport from single CSV

Hello,
I am working on an application that interfaces with an AS400. The AS400
programmers I am working with at the client, are not very flexible. They are
providing me with data for the initial database load for the application to
go live. After that my app will take care of all data manipulation. My
problem is, for the initial import, I have a single row in a CSV file that
holds a customer record, it is then followed by three fields for product
information that repeat based on the products that that customer has. I know
the number of fields leading up to the repeating portion and I know that
from that point on, every three fields goes to one record. In my database
the customer record is in a seperate table from the products in a one to
many relationship.
I need to write a script or DTS package that will manipulate the data
and create the related records in both tables. Can this be done? Is there a
simple way to do it? Any hints as to what to try or where to look would be
greatly appreciated. Thanks.
Andrew,
I don't fully understand whether the "single row" follwed by "fields"
is one long single row, or one row followed by more rows, but if you
have just one row per customer, you may be able to import your data into
a staging table with columns for the customer data and one additional
long column for all that customer's product information, which you can
then break up once it's in SQL Server.
Can you post a few customer's worth of what the data looks like? In
particular, what I can't tell from your description is how you know
where the product information ends and the next customer's information
begins.
If the customer and product info is on separate lines, you could
preprocess the data before importing it, by adding line numbers first,
then splitting it into two files, one for customers, one for products,
which the line numbers would help you link back together after you
import each into SQL Server.
Steve Kass
Drew University
Andrew L. Van Slaars wrote:

>Hello,
> I am working on an application that interfaces with an AS400. The AS400
>programmers I am working with at the client, are not very flexible. They are
>providing me with data for the initial database load for the application to
>go live. After that my app will take care of all data manipulation. My
>problem is, for the initial import, I have a single row in a CSV file that
>holds a customer record, it is then followed by three fields for product
>information that repeat based on the products that that customer has. I know
>the number of fields leading up to the repeating portion and I know that
>from that point on, every three fields goes to one record. In my database
>the customer record is in a seperate table from the products in a one to
>many relationship.
> I need to write a script or DTS package that will manipulate the data
>and create the related records in both tables. Can this be done? Is there a
>simple way to do it? Any hints as to what to try or where to look would be
>greatly appreciated. Thanks.
>
>
|||"Andrew L. Van Slaars" <andrew@.vanslaars.com> wrote in message
news:%23TxETPo8EHA.824@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I am working on an application that interfaces with an AS400. The AS400
> programmers I am working with at the client, are not very flexible. They
> are
> providing me with data for the initial database load for the application
> to
> go live. After that my app will take care of all data manipulation. My
> problem is, for the initial import, I have a single row in a CSV file that
> holds a customer record, it is then followed by three fields for product
> information that repeat based on the products that that customer has. I
> know
> the number of fields leading up to the repeating portion and I know that
> from that point on, every three fields goes to one record. In my database
> the customer record is in a seperate table from the products in a one to
> many relationship.
> I need to write a script or DTS package that will manipulate the data
> and create the related records in both tables. Can this be done? Is there
> a
> simple way to do it? Any hints as to what to try or where to look would be
> greatly appreciated. Thanks.
>
Andrew,
You CAN do this. In a nutshell, as long as the data is in some type of
consistent structured format, you will be able to manipulate it. I think
the easiest solution for you would be to use a DTS package and ActiveX
scripting within that package. You can instantiate ADO recordsets and then
manipulate the data in any fashion that you wish.
You should be able to read the CSV rows in and then based on what the data
values are in the various "fields", you can then instantiate additional
recordsets. Your choices from there are up to you. You could then create
ADO recordset instances that connect to your SQL Server tables and do
individual INSERTS (slow), or you can parse your data into a large chunk of
INSERT statements (store them in a string variable) and then use the
ADO.Connection object's Execute method to bulk those inserts statements into
SQL Server (faster).
Example:
Dim cn as ADODB.Connection
cn.ConnectionString = "Provider = OLEDB.1; ....."
cn.Execute(SQLString)
Hope this gets you off on the right foot.
Rick Sawtell
MCT, MCSD, MCDBA
sql

Related Records Inport from single CSV

Hello,
I am working on an application that interfaces with an AS400. The AS400
programmers I am working with at the client, are not very flexible. They are
providing me with data for the initial database load for the application to
go live. After that my app will take care of all data manipulation. My
problem is, for the initial import, I have a single row in a CSV file that
holds a customer record, it is then followed by three fields for product
information that repeat based on the products that that customer has. I know
the number of fields leading up to the repeating portion and I know that
from that point on, every three fields goes to one record. In my database
the customer record is in a seperate table from the products in a one to
many relationship.
I need to write a script or DTS package that will manipulate the data
and create the related records in both tables. Can this be done? Is there a
simple way to do it? Any hints as to what to try or where to look would be
greatly appreciated. Thanks.Andrew,
I don't fully understand whether the "single row" follwed by "fields"
is one long single row, or one row followed by more rows, but if you
have just one row per customer, you may be able to import your data into
a staging table with columns for the customer data and one additional
long column for all that customer's product information, which you can
then break up once it's in SQL Server.
Can you post a few customer's worth of what the data looks like? In
particular, what I can't tell from your description is how you know
where the product information ends and the next customer's information
begins.
If the customer and product info is on separate lines, you could
preprocess the data before importing it, by adding line numbers first,
then splitting it into two files, one for customers, one for products,
which the line numbers would help you link back together after you
import each into SQL Server.
Steve Kass
Drew University
Andrew L. Van Slaars wrote:
>Hello,
> I am working on an application that interfaces with an AS400. The AS400
>programmers I am working with at the client, are not very flexible. They are
>providing me with data for the initial database load for the application to
>go live. After that my app will take care of all data manipulation. My
>problem is, for the initial import, I have a single row in a CSV file that
>holds a customer record, it is then followed by three fields for product
>information that repeat based on the products that that customer has. I know
>the number of fields leading up to the repeating portion and I know that
>from that point on, every three fields goes to one record. In my database
>the customer record is in a seperate table from the products in a one to
>many relationship.
> I need to write a script or DTS package that will manipulate the data
>and create the related records in both tables. Can this be done? Is there a
>simple way to do it? Any hints as to what to try or where to look would be
>greatly appreciated. Thanks.
>
>|||"Andrew L. Van Slaars" <andrew@.vanslaars.com> wrote in message
news:%23TxETPo8EHA.824@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I am working on an application that interfaces with an AS400. The AS400
> programmers I am working with at the client, are not very flexible. They
> are
> providing me with data for the initial database load for the application
> to
> go live. After that my app will take care of all data manipulation. My
> problem is, for the initial import, I have a single row in a CSV file that
> holds a customer record, it is then followed by three fields for product
> information that repeat based on the products that that customer has. I
> know
> the number of fields leading up to the repeating portion and I know that
> from that point on, every three fields goes to one record. In my database
> the customer record is in a seperate table from the products in a one to
> many relationship.
> I need to write a script or DTS package that will manipulate the data
> and create the related records in both tables. Can this be done? Is there
> a
> simple way to do it? Any hints as to what to try or where to look would be
> greatly appreciated. Thanks.
>
Andrew,
You CAN do this. In a nutshell, as long as the data is in some type of
consistent structured format, you will be able to manipulate it. I think
the easiest solution for you would be to use a DTS package and ActiveX
scripting within that package. You can instantiate ADO recordsets and then
manipulate the data in any fashion that you wish.
You should be able to read the CSV rows in and then based on what the data
values are in the various "fields", you can then instantiate additional
recordsets. Your choices from there are up to you. You could then create
ADO recordset instances that connect to your SQL Server tables and do
individual INSERTS (slow), or you can parse your data into a large chunk of
INSERT statements (store them in a string variable) and then use the
ADO.Connection object's Execute method to bulk those inserts statements into
SQL Server (faster).
Example:
Dim cn as ADODB.Connection
cn.ConnectionString = "Provider = OLEDB.1; ....."
cn.Execute(SQLString)
Hope this gets you off on the right foot.
Rick Sawtell
MCT, MCSD, MCDBA

Related Records Inport from single CSV

Hello,
I am working on an application that interfaces with an AS400. The AS400
programmers I am working with at the client, are not very flexible. They are
providing me with data for the initial database load for the application to
go live. After that my app will take care of all data manipulation. My
problem is, for the initial import, I have a single row in a CSV file that
holds a customer record, it is then followed by three fields for product
information that repeat based on the products that that customer has. I know
the number of fields leading up to the repeating portion and I know that
from that point on, every three fields goes to one record. In my database
the customer record is in a seperate table from the products in a one to
many relationship.
I need to write a script or DTS package that will manipulate the data
and create the related records in both tables. Can this be done? Is there a
simple way to do it? Any hints as to what to try or where to look would be
greatly appreciated. Thanks.Andrew,
I don't fully understand whether the "single row" follwed by "fields"
is one long single row, or one row followed by more rows, but if you
have just one row per customer, you may be able to import your data into
a staging table with columns for the customer data and one additional
long column for all that customer's product information, which you can
then break up once it's in SQL Server.
Can you post a few customer's worth of what the data looks like? In
particular, what I can't tell from your description is how you know
where the product information ends and the next customer's information
begins.
If the customer and product info is on separate lines, you could
preprocess the data before importing it, by adding line numbers first,
then splitting it into two files, one for customers, one for products,
which the line numbers would help you link back together after you
import each into SQL Server.
Steve Kass
Drew University
Andrew L. Van Slaars wrote:

>Hello,
> I am working on an application that interfaces with an AS400. The AS400
>programmers I am working with at the client, are not very flexible. They ar
e
>providing me with data for the initial database load for the application to
>go live. After that my app will take care of all data manipulation. My
>problem is, for the initial import, I have a single row in a CSV file that
>holds a customer record, it is then followed by three fields for product
>information that repeat based on the products that that customer has. I kno
w
>the number of fields leading up to the repeating portion and I know that
>from that point on, every three fields goes to one record. In my database
>the customer record is in a seperate table from the products in a one to
>many relationship.
> I need to write a script or DTS package that will manipulate the data
>and create the related records in both tables. Can this be done? Is there a
>simple way to do it? Any hints as to what to try or where to look would be
>greatly appreciated. Thanks.
>
>|||"Andrew L. Van Slaars" <andrew@.vanslaars.com> wrote in message
news:%23TxETPo8EHA.824@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I am working on an application that interfaces with an AS400. The AS400
> programmers I am working with at the client, are not very flexible. They
> are
> providing me with data for the initial database load for the application
> to
> go live. After that my app will take care of all data manipulation. My
> problem is, for the initial import, I have a single row in a CSV file that
> holds a customer record, it is then followed by three fields for product
> information that repeat based on the products that that customer has. I
> know
> the number of fields leading up to the repeating portion and I know that
> from that point on, every three fields goes to one record. In my database
> the customer record is in a seperate table from the products in a one to
> many relationship.
> I need to write a script or DTS package that will manipulate the data
> and create the related records in both tables. Can this be done? Is there
> a
> simple way to do it? Any hints as to what to try or where to look would be
> greatly appreciated. Thanks.
>
Andrew,
You CAN do this. In a nutshell, as long as the data is in some type of
consistent structured format, you will be able to manipulate it. I think
the easiest solution for you would be to use a DTS package and ActiveX
scripting within that package. You can instantiate ADO recordsets and then
manipulate the data in any fashion that you wish.
You should be able to read the CSV rows in and then based on what the data
values are in the various "fields", you can then instantiate additional
recordsets. Your choices from there are up to you. You could then create
ADO recordset instances that connect to your SQL Server tables and do
individual INSERTS (slow), or you can parse your data into a large chunk of
INSERT statements (store them in a string variable) and then use the
ADO.Connection object's Execute method to bulk those inserts statements into
SQL Server (faster).
Example:
Dim cn as ADODB.Connection
cn.ConnectionString = "Provider = OLEDB.1; ....."
cn.Execute(SQLString)
Hope this gets you off on the right foot.
Rick Sawtell
MCT, MCSD, MCDBA

relate two tables based on an 'active' column

So I have a question and I will give a related example. Say I have a list of records in a table:

Product Cost Active

hat 1.00 false

coat 2.00 true

I have a DataGridView that shows purchases from a purchase table: Date, ProductID, Buyer

Is there a way to have a DataGridViewComboBoxColumn show only active items so that a user can only select active items in the puchases datagridview, but when they click a purchase from long ago when an item was active they have the ability to pick that old(unactive) item or a new one for an update.

Basically if I just have the ComboBoxColumn bound to a dataset with just active items, the datagrid will fail to load cause old purchases are not in its item list, but I also don't want all the items to be in the list.

So... you want a union of active items and inactive previously-bought items?

select *
from Products
where Active = 'true' --Should you be using a bit/tinyint/smallint here?
UNION ALL
select distinct p.*
from
Purchases pu
join

Products p
on p.ProductID = pu.ProductID
where p.Active = 'false'
and pu.Buyer = @.Buyer

Maybe?

Rob|||Hi,

well this is more a front-end related question. You can capture the SelectedIndexchangedEvent of the Combobox and select upon the value or the column where the row is marked as new / old if it can be choosen or not. Depending on your control you could also use another color for each combobox value (and therefore hrey out the non selectable). If you want to filter the combobox values in the first place without showing them to the user, you might want to hook into the ItemDatabound event. That highly depends on your used language / controls (which you did not mentioned)

HTH; Jens K. Suessmeyer.

http://www.sqlserver2005.de|||I got it hammered out. I set the combobox data source on a filtered binding source when the OnBeginEdit event fired. The filtered string simply included and "or combobox.Value = sourceTable.ID". Then on the OnEndEdit event, I set the combobox datasource back to an unfiltered view of the items.

Rejected Inserts: but key unique

I'm getting some records coming up in the conflict viewer which havn't been
inserted because there is a "Violation of the Unique key constraint". But if
I join the conflict table to the target table on the primary key I get no
result so keyfld in the conflict table is unique. Can anyone explain why it
is being rejected?
all the best
spike
Perhaps there is a unique index or unique constraint that is being voilated,
reather than the PK?
Rgds,
Paul Ibison
|||On Apr 30, 2:50 am, Spike <S...@.discussions.microsoft.com> wrote:
> I'm getting some records coming up in the conflict viewer which havn't been
> inserted because there is a "Violation of the Unique key constraint". But if
> I join the conflict table to the target table on the primary key I get no
> result so keyfld in the conflict table is unique. Can anyone explain why it
> is being rejected?
> all the best
> spike
I've seen this with merge replication in SQL 2005 with client pull
subscriptions, Scenario:
1) Publisher has master record "A" and detail records "1,2" with
foreign key constraint applied
2) Subscriber connects, receives master/detail data, disconnects
3) Publisher side deletes master record "A"
4) Subscriber {disconnected} adds detail records "3,4" for master
"A" {succeeds}
5) Subscriber replicates
This is what happens:
1) Master record "A" delete is processed without conflict
2) A compensating master "A" delete is sent to the subscriber - the
master record is removed from the subscription database
3) Detail record "3,4" inserts are sent up to publisher - they fail
with foreign key constraint error
4) Detail records "3,4" remain orphaned in the subscriber database -
each subsequent attempt to replicate generates a new insert/FK
constraint error - overwriting the old one - and the conflict date/
time is updated
Reinitializing the subscriber will remove the orphaned data from the
subscriber database and you should stop seeing the conflict date/time
update with subsequent replication attempts...

Reinserting certain records of a table in to the same table

Hi

How can I reinsert certain records of a table in to the same table and change only one column? This is story : I have a table that user enters daily records in it , most of these record are same as records of yesterday. So I want to reinsert them and let user to edit them if needed.

I have a other question too , there are 2 tables that are related to table above and I have to reinsert the related records too for example there are 4 records in second table that related to the fist row of the first table .

I can do all of it with asp.net using loops and connecting and disconnecting to database per insert but it doesn't seems to be so wisely , I rather do it with a stored procedure .

Thank you in advance

This ought to work:

insert into table (...columns...) select ...columns... from table where ....

Assumes you have an identity column that is NOT in the list of fields (ie, let sql server handle that for you)

I don't understand your second question, ie, how is it different than the first?

|||

Thank you David ,

Abut my first question, there is a column that I have to change it's value , It concerns abut date of report how do I handle that ?I gues it must be something like this :
insert into table (...columns...) ,[TodayReportID] select ...columns... from table where .... ? @.TodayReportIDFor better understanding see the example of question 2My second question :Assume these are table above columns :
? [Table1ID] ,[ column1], [ column2], [TodayReportID]
1 AAAA BBBB 1
2 CCCC DDDD 1
3 EEEE FFFF 1
And there is an other table I name it Table 2 and [Table1ID] is a foreign key in it related to table 1 so all records below are related to 2^nd row of table one .
? [Table2ID] ,[Table1ID], [ column2]
1 2 GGG
2 2 HHH
3 2 KKK
Now assume I reinsert second row of table 1 it will be something like this :
? [Table1ID] ,[ column1], [ column2], [TodayReportID]
…
4 CCCC DDDD 2
Now I have to reinsert related row of Table2 too :
? [Table2ID] ,[Table1ID], [ column2]
4 4 GGG
5 4 HHH
6 4 KKK

Thank very much

|||

u can use trigger ... while inserting and data to a certain table it will then automatically call your trigger and update your second table.

/* */

in your table use data time field to separate your distinct date data.

|||Thank you ,But I still didn't get my answer of first question about extra column that I have to insert it manually (by a parameter ). And I'm not familiar with trigger yet so can you give a an example how to use a trigger to perform this task ? Thank you again

|||I don't think triggers was the best option, I need something like loop so I can select some records and from Table1 and reinsert records for each row.|||

If you want to change the date when the record is created it's easy, just supply the new value in the select list that's getting the original data, eg, this example puts current date into column 3

insert into table1 (col1, col2, col3, col4)
select col1, col2,getdate(), col4) from table1

I don't see any use for triggers in this example, btw

|||

thank you ,

I did this and it works :

Create PROCEDURE [dbo].[proc_DailyReport_CopyInformation](@.ReportIDDECIMAL,@.ReportNewDECIMAL)ASBEGINSET NOCOUNT ONDECLARE @.ErrintINSERT INTO DailyReport_Activity (ReportID,ActivityDesc,hajm,vahedID,Tozihat,WBS) SELECT ReportID=(@.ReportNew),ActivityDesc,hajm,vahedID,Tozihat,WBS From DailyReport_Activity where ReportID = @.ReportIDSET @.Err = @.@.ErrorRETURN @.ErrEND

but I didn't find out my secont question yet please help .

|||

I don't really understand your second question -- can you elaborate? How is it different than your first question, aren't you still just inerting records based on existing records?

|||The problem is I don't know how can I make loop so based on the records I reinserted in the first table I reinsert records in the second table .

Or I need reinsert related records to second table while I'm inserting in the first table .

re-insert / templating records

I have a problem. The project that I'm working on requires me to duplicate
records. As in a series of records are entered into the system the user then
click on a button to make these as 'Templates' so that they would not have
to re-enter alot of the information. So from a db perspective I would have
to re-insert these records into the database. The problem is there're over
20 tables and the relationship is complex. Is there a way to copy the parent
the record and have sqlserver automatically cascade and re-insert all
related and referenced records back into the database ?

or is there a easy way to do this ?

Thanks
TomDuplicating rows in a table should never be necessary or desirable and it
shouldn't even be possible since evey table should have unique/primary key
constraints that prevent this. I assume therefore you will want to maintain
uniqueness by changing some column values. Unfortunately you haven't told us
anything about keys, constraints or the data you want to modify.

> Is there a way to copy the parent
> the record and have sqlserver automatically cascade and re-insert all
> related and referenced records back into the database ?

I guess here that you are talking about copying rows between tables with
IDENTITY columns. This is easy provided you have declared natural (not
IDENTITY) keys on the tables. IDENTITY should not be the only key of a
table. Here is an example of moving a parent entity and its related rows
between tables while maintaining the surrogate keys.

CREATE TABLE Departments (deptid INTEGER IDENTITY PRIMARY KEY, deptname
VARCHAR(30) NOT NULL UNIQUE /* Note the Key */)

CREATE TABLE Employees (employeeid INTEGER IDENTITY PRIMARY KEY, ssn
CHAR(10) NOT NULL UNIQUE /* Note the Key */, employeename VARCHAR(30) NOT
NULL, deptid INTEGER NOT NULL REFERENCES Departments (deptid))

CREATE TABLE New_Departments (deptid INTEGER IDENTITY PRIMARY KEY, deptname
VARCHAR(30) NOT NULL UNIQUE)

CREATE TABLE New_Employees (employeeid INTEGER IDENTITY PRIMARY KEY, ssn
CHAR(10) NOT NULL UNIQUE, employeename VARCHAR(30) NOT NULL, deptid INTEGER
NOT NULL REFERENCES New_Departments (deptid))

INSERT INTO New_Departments (deptname)
SELECT D.deptname
FROM Departments AS D
LEFT JOIN New_Departments AS N
ON D.deptname = N.deptname
WHERE N.deptname IS NULL

INSERT INTO New_Employees (ssn, employeename, deptid)
SELECT E1.ssn, E1.employeename, D2.deptid
FROM Employees AS E1
JOIN Departments AS D1
ON E1.deptid = D1.deptid
JOIN New_Departments AS D2
ON D1.deptname = D2.deptname
LEFT JOIN New_Employees AS E2
ON E1.ssn = E2.ssn
WHERE E2.employeeid IS NULL

--
David Portas
SQL Server MVP
--|||Tom Gao (tomgaomail@.optushome.com.au) writes:
> I have a problem. The project that I'm working on requires me to
> duplicate records. As in a series of records are entered into the system
> the user then click on a button to make these as 'Templates' so that
> they would not have to re-enter alot of the information. So from a db
> perspective I would have to re-insert these records into the database.
> The problem is there're over 20 tables and the relationship is complex.
> Is there a way to copy the parent the record and have sqlserver
> automatically cascade and re-insert all related and referenced records
> back into the database ?

There's a whole lot of information missing here, but in any case, the
answer is: no.

Are you inserting into the same table, or from a table with templates?
Well, in most cases it makes sense to store templates in the same table
as the real rows.

But then there are at least two columns that are not to be copied to
the new rows: the key and the column that marks that the template is a
template.

And I would not be surprised if there are more columns. For instance,
say that there are auditing columns who tells which which user that
created the row and when. Such data is of course not possible to
inherit from the client.

So, I am sorry, you just have to start coding. And pay attention to
the business requirements, so that you copy what you should copy, no
more, no less.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

REINDEXING?

I have a big table with more than 7583117
records. The table is updated every day during the End of the day activity.

There is a job running a query on this table for updating another table.

The table is not reindexed and the job running freezes on the EXECUTING state many times is it because of not reindexing the indexes?

Thanks in advance

Jacx

Hi Jacx,

What dou you see in SQL Server Error Log?

In the moment of this job running you monitoring the locks?

Regards,

|||

I checked the error log nothing about that particular job because it is not cancelled because of an error but it stays in the executing state.

|||When you say it "freezes" what do you mean, where are you seeing it "freeze"? Have you run the Profiler to see what is reallly going on?

How are you updating the table and selecting the records? Are you doing:

INSERT INTO XXX
SELECT ......

If so, that is probably expanding tempdb when you see it "freeze" because it writtes the entire select to tempdb and then inserts it into the target table.

Without seeing exactly how you are "updating" the table, it is hard to say what it is doing. The index being corrupt and needing reindexing is not normally a problem in MS SQL.

Reindexing 27GB table

On one of the database, I support has 16957722 records in a table and it's
clustered index size is 27606.30.
I used to run the Reindex job created on weekly Sunday starting at 11:00 PM.
It takes more than 7 hours.
Since it is a 24x7 server, when Reindexing job runs it causes blocks so I am
cancelling the job.
In this situation what would you suggest to reindex the table of
such big one ( 27 GB ) ?
Appreciate your advise.
--
Thanks
S SIVAPRASADFirst you need to determine whether you benefit from reindexing. See
[url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx.[/url
]
To check frag level use DBCC SHOWCONTIG (2000) or sys.dm_db_index_physical_s
tats (2005).
In 2000, you have little options of you want to do this "online", your optio
n is DBCC INDEXDEFRAG.
In 2005, you can use either ALTER INDEX with REORGANIZE (same as INDEXDEFRAG
), or use ALTER INDEX
with REBUILD, ONLINE (same as DBREINDEX, except for ONLINE which is new opti
on).
All have pro and cons, one thing to consider is the usage of transaction log
space (which depends on
what type of command you run and can also be influenced by recovery model).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"S Siva Prasad [SIVA]" <SSivaPrasadSIVA@.discussions.microsoft.com> wrote
in message
news:2484E248-2FCD-47F8-BACB-0AB9E71DEAC3@.microsoft.com...
> On one of the database, I support has 16957722 records in a table and it's
> clustered index size is 27606.30.
> I used to run the Reindex job created on weekly Sunday starting at 11:00 P
M.
> It takes more than 7 hours.
> Since it is a 24x7 server, when Reindexing job runs it causes blocks so I
am
> cancelling the job.
> In this situation what would you suggest to reindex the table of
> such big one ( 27 GB ) ?
> Appreciate your advise.
> --
> Thanks
> S SIVAPRASAD|||It is on SQL Server 2000 Enterprise Edition with SP4 (8.00.2040) .
Database is on Full recovery model and it has enough space for Log file
growth.
DBCC SHOWCONITG() ran and rebuilding of the indexes is necessary now.
Let me know what would be the best optimal way of rebuilding the indexes.
The box is on 24x7 and I want to rebuild the index as quick as possible.
The clustered index sized around 27 GB.
--
Thanks
S SIVAPRASAD
"Tibor Karaszi" wrote:

> First you need to determine whether you benefit from reindexing. See
> [url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx.[/u
rl]
> To check frag level use DBCC SHOWCONTIG (2000) or sys.dm_db_index_physical
_stats (2005).
> In 2000, you have little options of you want to do this "online", your opt
ion is DBCC INDEXDEFRAG.
> In 2005, you can use either ALTER INDEX with REORGANIZE (same as INDEXDEFR
AG), or use ALTER INDEX
> with REBUILD, ONLINE (same as DBREINDEX, except for ONLINE which is new op
tion).
> All have pro and cons, one thing to consider is the usage of transaction l
og space (which depends on
> what type of command you run and can also be influenced by recovery model)
.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "S Siva Prasad [SIVA]" <SSivaPrasadSIVA@.discussions.microsoft.com> wro
te in message
> news:2484E248-2FCD-47F8-BACB-0AB9E71DEAC3@.microsoft.com...
>
>|||There's no "optimal way". There are different options, all with advantages a
nd disadvantages. Based
on your requirements, you can weigh the advantages to the disadvantages and
pick the one that suits
your particular need best. Make sure you understand the pros and cons with e
ach method and read the
whitepaper I posted a link to.
Having said that, if the box is 24*7, indexdefrag might be better. But it ca
n take longer time than
dbreindex (but it doesn't block nearly as much), it can produce more log rec
ords, and the
defragmentation might not be as perfect as dbreindex.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"S Siva Prasad [SIVA]" <SSivaPrasadSIVA@.discussions.microsoft.com> wrote
in message
news:8A93A9FA-E357-475D-BB40-8F9BE0A43CCF@.microsoft.com...[vbcol=seagreen]
> It is on SQL Server 2000 Enterprise Edition with SP4 (8.00.2040) .
> Database is on Full recovery model and it has enough space for Log file
> growth.
> DBCC SHOWCONITG() ran and rebuilding of the indexes is necessary now.
> Let me know what would be the best optimal way of rebuilding the indexes.
> The box is on 24x7 and I want to rebuild the index as quick as possible.
> The clustered index sized around 27 GB.
> --
> Thanks
> S SIVAPRASAD
>
> "Tibor Karaszi" wrote:
>

Reindexing 27GB table

On one of the database, I support has 16957722 records in a table and it's
clustered index size is 27606.30.
I used to run the Reindex job created on weekly Sunday starting at 11:00 PM.
It takes more than 7 hours.
Since it is a 24x7 server, when Reindexing job runs it causes blocks so I am
cancelling the job.
In this situation what would you suggest to reindex the table of
such big one ( 27 GB ) ?
Appreciate your advise.
--
Thanks
S SIVAPRASADFirst you need to determine whether you benefit from reindexing. See
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx.
To check frag level use DBCC SHOWCONTIG (2000) or sys.dm_db_index_physical_stats (2005).
In 2000, you have little options of you want to do this "online", your option is DBCC INDEXDEFRAG.
In 2005, you can use either ALTER INDEX with REORGANIZE (same as INDEXDEFRAG), or use ALTER INDEX
with REBUILD, ONLINE (same as DBREINDEX, except for ONLINE which is new option).
All have pro and cons, one thing to consider is the usage of transaction log space (which depends on
what type of command you run and can also be influenced by recovery model).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"S Siva Prasad [SIVA]" <SSivaPrasadSIVA@.discussions.microsoft.com> wrote in message
news:2484E248-2FCD-47F8-BACB-0AB9E71DEAC3@.microsoft.com...
> On one of the database, I support has 16957722 records in a table and it's
> clustered index size is 27606.30.
> I used to run the Reindex job created on weekly Sunday starting at 11:00 PM.
> It takes more than 7 hours.
> Since it is a 24x7 server, when Reindexing job runs it causes blocks so I am
> cancelling the job.
> In this situation what would you suggest to reindex the table of
> such big one ( 27 GB ) ?
> Appreciate your advise.
> --
> Thanks
> S SIVAPRASAD|||It is on SQL Server 2000 Enterprise Edition with SP4 (8.00.2040) .
Database is on Full recovery model and it has enough space for Log file
growth.
DBCC SHOWCONITG() ran and rebuilding of the indexes is necessary now.
Let me know what would be the best optimal way of rebuilding the indexes.
The box is on 24x7 and I want to rebuild the index as quick as possible.
The clustered index sized around 27 GB.
--
Thanks
S SIVAPRASAD
"Tibor Karaszi" wrote:
> First you need to determine whether you benefit from reindexing. See
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx.
> To check frag level use DBCC SHOWCONTIG (2000) or sys.dm_db_index_physical_stats (2005).
> In 2000, you have little options of you want to do this "online", your option is DBCC INDEXDEFRAG.
> In 2005, you can use either ALTER INDEX with REORGANIZE (same as INDEXDEFRAG), or use ALTER INDEX
> with REBUILD, ONLINE (same as DBREINDEX, except for ONLINE which is new option).
> All have pro and cons, one thing to consider is the usage of transaction log space (which depends on
> what type of command you run and can also be influenced by recovery model).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "S Siva Prasad [SIVA]" <SSivaPrasadSIVA@.discussions.microsoft.com> wrote in message
> news:2484E248-2FCD-47F8-BACB-0AB9E71DEAC3@.microsoft.com...
> > On one of the database, I support has 16957722 records in a table and it's
> > clustered index size is 27606.30.
> >
> > I used to run the Reindex job created on weekly Sunday starting at 11:00 PM.
> > It takes more than 7 hours.
> >
> > Since it is a 24x7 server, when Reindexing job runs it causes blocks so I am
> > cancelling the job.
> >
> > In this situation what would you suggest to reindex the table of
> > such big one ( 27 GB ) ?
> >
> > Appreciate your advise.
> > --
> > Thanks
> > S SIVAPRASAD
>
>|||There's no "optimal way". There are different options, all with advantages and disadvantages. Based
on your requirements, you can weigh the advantages to the disadvantages and pick the one that suits
your particular need best. Make sure you understand the pros and cons with each method and read the
whitepaper I posted a link to.
Having said that, if the box is 24*7, indexdefrag might be better. But it can take longer time than
dbreindex (but it doesn't block nearly as much), it can produce more log records, and the
defragmentation might not be as perfect as dbreindex.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"S Siva Prasad [SIVA]" <SSivaPrasadSIVA@.discussions.microsoft.com> wrote in message
news:8A93A9FA-E357-475D-BB40-8F9BE0A43CCF@.microsoft.com...
> It is on SQL Server 2000 Enterprise Edition with SP4 (8.00.2040) .
> Database is on Full recovery model and it has enough space for Log file
> growth.
> DBCC SHOWCONITG() ran and rebuilding of the indexes is necessary now.
> Let me know what would be the best optimal way of rebuilding the indexes.
> The box is on 24x7 and I want to rebuild the index as quick as possible.
> The clustered index sized around 27 GB.
> --
> Thanks
> S SIVAPRASAD
>
> "Tibor Karaszi" wrote:
>> First you need to determine whether you benefit from reindexing. See
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx.
>> To check frag level use DBCC SHOWCONTIG (2000) or sys.dm_db_index_physical_stats (2005).
>> In 2000, you have little options of you want to do this "online", your option is DBCC
>> INDEXDEFRAG.
>> In 2005, you can use either ALTER INDEX with REORGANIZE (same as INDEXDEFRAG), or use ALTER INDEX
>> with REBUILD, ONLINE (same as DBREINDEX, except for ONLINE which is new option).
>> All have pro and cons, one thing to consider is the usage of transaction log space (which depends
>> on
>> what type of command you run and can also be influenced by recovery model).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "S Siva Prasad [SIVA]" <SSivaPrasadSIVA@.discussions.microsoft.com> wrote in message
>> news:2484E248-2FCD-47F8-BACB-0AB9E71DEAC3@.microsoft.com...
>> > On one of the database, I support has 16957722 records in a table and it's
>> > clustered index size is 27606.30.
>> >
>> > I used to run the Reindex job created on weekly Sunday starting at 11:00 PM.
>> > It takes more than 7 hours.
>> >
>> > Since it is a 24x7 server, when Reindexing job runs it causes blocks so I am
>> > cancelling the job.
>> >
>> > In this situation what would you suggest to reindex the table of
>> > such big one ( 27 GB ) ?
>> >
>> > Appreciate your advise.
>> > --
>> > Thanks
>> > S SIVAPRASAD
>>

ReHigh memory usage-SQLServer 7

Hi to all,
we are using MSSQLServer 7 for both Win2K and Win95/98 OS.
My problem is, that as we run our application overnight,
inserting records in the table, we notice that the memory
usage of sqlserver.exe in the Task Bar Manager keep on
increasing, thus "eats-up" the available physical memory.
How can we limit the memory usage of the sqlserver.exe?
coz in a low physical memory we could not open any
application unless we will stop/restart SQLServer to free-
up mem. we are just using 260MB of RAM.
thank you and more power.Hi Alvin.
SQL Server is designed to use up the available memory on a
computer. This is because it is configured (by default) to
behave like the only software running on the computer.
When installed on a dedicated (so often the case) this is
usually true - you want SQL Server to make use of the
available memory to run queries as quickly as possible.
If you're installing SQL Server on a computer where it is
NOT the only software running (ie not a dedicated server -
such as a desktop), you can configure SQL Server to use a
maximum (and / or a minimum) amount of memory.
Easiest way to do this is from the SQL Enterprise Manager.
As long as you've registered your EM connection to connect
as a systadmin, you should be able to simply right click
on the SQL Server in the EM, select properties, go to the
memory tab & set your memory settings.
HTH
Regards,
Greg Linwood
SQL Server MVP
>--Original Message--
>Hi to all,
>we are using MSSQLServer 7 for both Win2K and Win95/98
OS.
>My problem is, that as we run our application overnight,
>inserting records in the table, we notice that the memory
>usage of sqlserver.exe in the Task Bar Manager keep on
>increasing, thus "eats-up" the available physical memory.
>How can we limit the memory usage of the sqlserver.exe?
>coz in a low physical memory we could not open any
>application unless we will stop/restart SQLServer to free-
>up mem. we are just using 260MB of RAM.
>thank you and more power.
>.
>