data reorganization

We are experiencing a situation where the data in our database gets
fragmented and we have to load the data into a flat file delete the data in
the tables and load it back. We have tried rebuilding the indexes with the
DBCC DBREINDEX command but it does seem to help. Any ideas how this could be
resolved?
Hi,
Execute the command DBCC SHOWCONTIG('table_name') and verify the scan
density. Using this you
can identify how fragmented is your table. If the table is highly
fragmentred jut run a DBCC DBREINDEX on the Tables--clustered index. This
will reorganize all the data pages and indexes. After the reindexing you can
execute the DBCC SHOWCONTIG and confirm, whether your fragmentation is
removed or not.
Thanks
Hari
MCDBA
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:#PTj6CVVEHA.3512@.TK2MSFTNGP12.phx.gbl...
> We are experiencing a situation where the data in our database gets
> fragmented and we have to load the data into a flat file delete the data
in
> the tables and load it back. We have tried rebuilding the indexes with the
> DBCC DBREINDEX command but it does seem to help. Any ideas how this could
be
> resolved?
>
|||make sure that your tables have a Clustred Index and Look into the usage of
Fill Factor Settings.
Greg Jackson
PDX, Oregon
|||You should read the whitepaper on fragmentation and when and how to address
it below:
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Any questions after reading this, let me know.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:#PTj6CVVEHA.3512@.TK2MSFTNGP12.phx.gbl...
> We are experiencing a situation where the data in our database gets
> fragmented and we have to load the data into a flat file delete the data
in
> the tables and load it back. We have tried rebuilding the indexes with the
> DBCC DBREINDEX command but it does seem to help. Any ideas how this could
be
> resolved?
>

data reorganization

We are experiencing a situation where the data in our database gets
fragmented and we have to load the data into a flat file delete the data in
the tables and load it back. We have tried rebuilding the indexes with the
DBCC DBREINDEX command but it does seem to help. Any ideas how this could be
resolved?Hi,
Execute the command DBCC SHOWCONTIG('table_name') and verify the scan
density. Using this you
can identify how fragmented is your table. If the table is highly
fragmentred jut run a DBCC DBREINDEX on the Tables--clustered index. This
will reorganize all the data pages and indexes. After the reindexing you can
execute the DBCC SHOWCONTIG and confirm, whether your fragmentation is
removed or not.
Thanks
Hari
MCDBA
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:#PTj6CVVEHA.3512@.TK2MSFTNGP12.phx.gbl...
> We are experiencing a situation where the data in our database gets
> fragmented and we have to load the data into a flat file delete the data
in
> the tables and load it back. We have tried rebuilding the indexes with the
> DBCC DBREINDEX command but it does seem to help. Any ideas how this could
be
> resolved?
>|||make sure that your tables have a Clustred Index and Look into the usage of
Fill Factor Settings.
Greg Jackson
PDX, Oregon|||You should read the whitepaper on fragmentation and when and how to address
it below:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Any questions after reading this, let me know.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:#PTj6CVVEHA.3512@.TK2MSFTNGP12.phx.gbl...
> We are experiencing a situation where the data in our database gets
> fragmented and we have to load the data into a flat file delete the data
in
> the tables and load it back. We have tried rebuilding the indexes with the
> DBCC DBREINDEX command but it does seem to help. Any ideas how this could
be
> resolved?
>sql

data reorganization

We are experiencing a situation where the data in our database gets
fragmented and we have to load the data into a flat file delete the data in
the tables and load it back. We have tried rebuilding the indexes with the
DBCC DBREINDEX command but it does seem to help. Any ideas how this could be
resolved?Hi,
Execute the command DBCC SHOWCONTIG('table_name') and verify the scan
density. Using this you
can identify how fragmented is your table. If the table is highly
fragmentred jut run a DBCC DBREINDEX on the Tables--clustered index. This
will reorganize all the data pages and indexes. After the reindexing you can
execute the DBCC SHOWCONTIG and confirm, whether your fragmentation is
removed or not.
Thanks
Hari
MCDBA
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:#PTj6CVVEHA.3512@.TK2MSFTNGP12.phx.gbl...
> We are experiencing a situation where the data in our database gets
> fragmented and we have to load the data into a flat file delete the data
in
> the tables and load it back. We have tried rebuilding the indexes with the
> DBCC DBREINDEX command but it does seem to help. Any ideas how this could
be
> resolved?
>|||make sure that your tables have a Clustred Index and Look into the usage of
Fill Factor Settings.
Greg Jackson
PDX, Oregon|||You should read the whitepaper on fragmentation and when and how to address
it below:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Any questions after reading this, let me know.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:#PTj6CVVEHA.3512@.TK2MSFTNGP12.phx.gbl...
> We are experiencing a situation where the data in our database gets
> fragmented and we have to load the data into a flat file delete the data
in
> the tables and load it back. We have tried rebuilding the indexes with the
> DBCC DBREINDEX command but it does seem to help. Any ideas how this could
be
> resolved?
>

Data Removal Real?

I have a situation where my client wants to ensure that data which is removed from a SQL Server database is truly removed. That is, if I delete a record, or delete data from a row, can I be sure that the data is truly gone and that it's not just the internal B-Tree pointers that were removed? Can I trust that someone with access to the .mdb file could not perform some binary pattern matching on the file and find the data that I assumed was deleted (regardless of how difficult that task would be)? I'm using SQL Server 2000. Thanks in advance.

The data is deleted.

However, depending upon your Recovery model, it may be 'hanging' around in the TRANSACTION LOG file for awhile, and anyone with access (and permissions) to the TRANSACTION LOG file, and one of several third party tools, may be able to inspect the deleted data.

(It's best, before they show up with the subpoenas, to backup and restore - and delete any saved backups and transaction logs, and the use a DOD approved sector wiping utility and burn the tapes... But of course, that is not a good thing to be caught having done...)

|||

Ok, thanks. So, I should look for a recovery model that doesn't use logging? This implies no recoverable transactions, but that would be ok.

(Side note, I'm not worried about subpoenas. Foreign governments are the perceived risk.)

|||

Simple recovery model has the shortest logging latency.

However, that means no data recovery since the last FULL BACKUP.

It seems that you may need to better secure the server ...

|||

I wasn’t sure about this one, so I asked the experts and this is what they replied back:

all the deletes are only logical … The only way to get rid of the data for sure is to DTS to a new (clean) database.

They mentioned some SQL Server 2005 SP2 feature for Common Criteria (CC) that is used to clear the information on the pages, but again, they mentioned it is only a logical deletion.

Arnie, it may be possible that you were talking about this feature, can you please confirm? Unfortunately I have limited knowledge on this particular area myself.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

Data manipulation with One-to-many-to-one relationship

Is it possible to INSERT, UPDATE, DELETE data in this type of relationship? If so, how in VS2005? Not having issues with SELECT, even without joins.

Thanks

Hi,

Try providing more information about what you are trying to do. Do you have tables set up in a DB already? What columns do you have in those tables, and what are the references that make a given row of data in each table unique (primary keys)?

If you don't have a schema yet, provide (in your own words), what facts you are trying to convey - such as 'A customer places an order. Order has line items. Each product has a product number.' etc... This will give viewers here a better understanding of what you need.

Good luck, in any event. BRN..

|||

Let's say for example:

Table 1 has Table1ID (PK) and Table1Data

Table 2 has Table2ID (PK), Table1ID (FK for Table1.Table1ID), Table3ID (FK for Table3.Table3ID)

Table 3 has Table3ID (PK), and Table3Data

Relationship is one-to-many for Table1.Table1ID to Table2.Table1ID and one-to-many for Table3.Tat.ble3ID to Table2.Table3ID, creating a many to many relationship for Table1 to Table 3 using Table 2 as an intersection table. I do not have any cascading.

I am able to query the database and gather the information, but need to be able to add, edit, and delete in a C# VS2005 project. Starting off small with something like this would enable me to conquer a bigger project that I would like to create. This is the first time I have had to work with data that required this type of relationship.

I'd prefer to see the SQL.

Is this enough information?

Many thanks

|||OK, let take these 3 tables for example:

create table Employee (EmplNumber int not null primary key,EmplName nvarchar(20) not null)

create table Department (DeptNumber int not null primary key,DeptName nvarchar(50) not null)

-- relationship between Employee and Department

create table DeptEmpl (DeptNumber int not null foreign key references Department(DeptNumber)
ON DELETE CASCADE ON UPDATE CASCADE,
EmplNumber int not null foreign key references Employee(EmplNumber)
ON DELETE CASCADE ON UPDATE CASCADE)

insert into Employee values (1, 'John')
insert into Employee values (2, 'Terry')
insert into Employee values (3, 'Andrew')

insert into Department values (1, 'Marketing')
insert into Department values (2, 'Sales')
insert into Department values (3, 'Accounting')

insert into DeptEmpl values (1, 1)
insert into DeptEmpl values (1, 3)
insert into DeptEmpl values (2, 1)
insert into DeptEmpl values (2, 2)
insert into DeptEmpl values (2, 3)
insert into DeptEmpl values (3, 1)

?
There should be no problem when you try to insert data-- the PK/FK contraint will maintain the reference integrity for you. The key point here is theON DELETE/UPDATE CASCADE, they will help to maintain reference integrity during DELETE/UPDATE, so no extra work need to be done manually. If you have other concerns, feel free to post them|||

Thanks Iori_Jay

It is completely clear to me what you are doing in this example. How does one handle user input variables from a web application?

Let's say the user wants to insert a new Employee 'Edgar' that belongs to Department 'Sales'.

|||OK, typical steps for such INSERT should be:

1. Insert the information for the new employee 'Edgar' toEmployee table, and get theEmplNumber for the new inserted employee
2. Check theDepartment table to see whether there is a 'Sales' department, if not then add the 'Sales' department.
3. Insert a row toDeptEmpl table to represent the relationship between 'Edgar' and 'Sales'.

So a sample stored procedure looks like:

CREATE PROCEDURE usp_InsertNewEmployee @.EmpName nvarchar(20), @.DeptName nvarchar(50)
AS
IF((LEN(@.EmpName)=0) OR (LEN(@.DeptName)=0))
RAISERROR('Please input both Employee name and the department name which he(she) belongs to',16,1)
DECLARE @.newEmpNumber INT,@.DeptNumber INT
SELECT @.newEmpNumber=max(EmplNumber)+1 FROM Employee
SELECT @.DeptNumber=DeptNumber FROM Department WHERE DeptName=@.DeptName

INSERT INTO Employee SELECT @.newEmpNumber, @.EmpName
IF (@.DeptNumber IS NULL)
BEGIN
SELECT @.DeptNumber=max(DeptNumber)+1 FROM Department
INSERT INTO Department SELECT @.DeptNumber,@.DeptName
END
INSERT INTO DeptEmpl SELECT @.newEmpNumber,@.DeptNumber
go

EXEC usp_InsertNewEmployee 'Edgar','Sales'|||

Iori_jay,

Thanks so much for your help. I've learned many things from this post. I appreciate you sharing your talents.

ICE|8

|||It's my pleasure to help with this issue Wish you a happy festival!

Data flow task to delete records and then insert records in transaction

HI,

I have been trying to solve the locking problem from past couple of days. Please help mee!!

Scenario:
--
I have a SSIS package in which 2 data flow tasks. 1st data flow task deletes records from a 5 tables and the 2nd data flow task should insert records into 1 of the five tables after the success of 1st data flow task. This scenario runs in Transacation.

The above scenrio in the 2nd data flow task hangs in runtime. It does not complete. with sp_who2 command i could see that there is an intent share lock(LK_M_IS) on the table and the status is SUSPENDED.

I dont know how to come out of this locking. Please help.

Thanks ,
SunilTry setting RetainSameConnection to TRUE on the connection manager.

|||It was already set to TRUE. Please help meeee..

|||Based on some other threads about similar issues, this may not be SSIS, but related to DTC and the core relational engine. You might try checking some of the other forums to see if they have any suggestions.

|||

Have you tried using a SQL Task (set based) for the deletes followed by your Insert Data Flow Task?

Larry

|||

Hey Larry,

Yes. I did use it.but still the blocking happens. Please suggest

Thanks,

Sunil