How To Avoid Indexes Become Fragmented
May 26, 2008
Last week i found on my database that almost all indexes had more than 70% of fragmentation. I rebuilt the indexes to fix the "problem" but today i found out that in one particular table (one with little more than 1 million records") the indexes are again 99,80% fragmented.
Did I do somethin wrong? If you ask me if there are a lot of transactions running over that table, the answer is NO. There is one procces that could append (insert) between 500 and 1000 records but this happens just once a month.
View 3 Replies
ADVERTISEMENT
Jul 23, 2005
kalpesh.s...@gmail.com Feb 1, 6:50 am show optionsNewsgroups: comp.databases.informixFrom: kalpesh.s...@gmail.com - Find messages by this authorDate: 1 Feb 2005 06:50:21 -0800Local: Tues, Feb 1 2005 6:50 amSubject: how do you know indexes have been fragmentedReply | Reply to Author | Forward | Print | Individual Message | Showoriginal | Remove | Report Abusei ran dbcc showcontig on my sql server db and it returned fo*llowingTable: 'Table1' (1621580815); index ID: 1, database ID: 7TABLE level scan performed.- Pages Scanned................................: 4982- Extents Scanned..............................: 628- Extent Switches..............................: 627- Avg. Pages per Extent........................: 7.9- Scan Density [Best Count:Actual Count].......: 99.20% [623*:628]- Logical Scan Fragmentation ..................: 0.00%- Extent Scan Fragmentation ...................: 99.52%- Avg. Bytes Free per Page.....................: 38.3- Avg. Page Density (full).....................: 99.53%Based on searching for info on index defrag it seems my Exte*nt ScanFragmentation percentage is not what it should be (0%) . Is *it trueandif yes how can you be sure that your indexes have been fragm*ented.If indexes are really fragmented what is the best way withou*treindexing(or is that the best way) to defrag the indexes.Thank you Kalpesh
View 3 Replies
View Related
Feb 27, 2004
Hi,
I have a database and application that are running slowly at the moment. My investigations so far have led me to HD issues. After an analysis of the data drives (RAID 5 on 5x36Gb drives) it shows that they are 99% fragmented.
Can i run a defrag on this drive?
Will i have to stop the SQL services?
tia
fatherjack
View 4 Replies
View Related
Oct 1, 2007
Have a 1TB of heaped tables being used 24/7 and performance is degrading over time, and the vendors dynamic SQL won't include clustered indexes! (and they won't let me add them)
I can reorganise the heap with an ALTER TABLE statement, add a column,
clustered index, drop column etc. However this is intrusive and I would require an entire day to perform this piece-meal, and a blanket script for 2000+ tables would kill performance all together.
Would a back-up, then remove the 1TB DB followed by a restore placing the data back onto disk unfragmented. (with LS this would only require a 3 hour down-time window) In theory it should work?
View 4 Replies
View Related
Apr 8, 2015
I just did index defragmentation for some databases include MSDB . I notice there are 3 indexes from MSDB database that fragmented quickly ( I did rebuild last nite at 10 PM - > fragmentation level becomes zero but today at 9 am it become 80 % ).The indexes are backupsetuuid, backup media family uuid, backupmediasetuuid. I am thinking to set the fill factor for those indexes = 80 respectively.
View 6 Replies
View Related
Dec 1, 2007
Hi everyone,
If I have a table with some indexes on the foriegn keys and these indexes are heavily fragmented (80%+), is it normal for queries to return incorrect results?
For example if I had a table called Customer( CustID, Name) and Orders (OrderID, CustID, Product, Date).
Lets say I have a non clustered index on CustID in Orders table, and the clustered indexes are Customer.CustID and Orders.OrderID
If the non clusterd index on Orders.CustID becomes heavily fragmented and I am querying the Orders table with TSQL "SELECT * FROM Orders where CustID = @CustID" I sometimes get missing data or incorrect results. In one case all orders for a particular year were missing, but if I queried using OderID they were returned. Rebuilding the index fixed the problem.
I know the index should be rebuilt or reorganized depending on the fragmentation but if one happened to become this fragmented should it start returning incorrect data?
- Using SQL Server Express 2005
View 3 Replies
View Related
Jul 1, 2014
I'm working to improve performance on a database I've inherited, and there are several thousand indexes. I've got a list of ones which should definitely exist within the database, and I'm looking to strip out all the others and start fresh, though this list is still quite large (1000 or so).
Is there a way I can remove all the indexes that are not in my list without too much trouble? I.e. without having to manually go through them all individually. The list is currently in a csv file.
I'm looking to either automate the removal of indexes not in the list, or possibly to generate the Create statements for the indexes on the list and simply remove all indexes and then run these statements.
As an aside, when trying to list all indexes in the database, I've found various scripts to do this, but found they all seem to produce differing results. What is the best script to list all indexes?
View 5 Replies
View Related
Sep 18, 2007
So I'm reading http://www.sql-server-performance.com/tips/clustered_indexes_p2.aspx and I come across this:
When selecting a column to base your clustered index on, try to avoid columns that are frequently updated. Every time that a column used for a clustered index is modified, all of the non-clustered indexes must also be updated, creating additional overhead. [6.5, 7.0, 2000, 2005] Updated 3-5-2004
Does this mean if I have say a table called Item with a clustered index on a column in it called itemaddeddate, and several non-clustered indexes associated with that table, that if a record gets modified and it's itemaddeddate value changes, that ALL my indexes on that table will get rebuilt? Or is it referring to the table structure changing?
If so does this "pseudocode" example also cause this to occur:
sqlstring="select * from item where itemid=12345"
rs.open sqlstring, etc, etc, etc
rs.Fields("ItemName")="My New Item Name"
rs.Fields("ItemPrice")=1.00
rs.Update
Note I didn't explicitly change the value of rs.fields("ItemAddedDate")...does rs.Fields("ItemAddedDate")=rs.Fields("ItemAddedDate") occur implicitly, which would force the rebuild of all the non-clustered indexes?
View 4 Replies
View Related
Jun 25, 2015
I have a requirement to only rebuild the Clustered Indexes in the table ignoring the non clustered indexes as those are taken care of by the Clustered indexes.
In order to do that, I have taken the records based on the fragmentation %.
But unable to come up with a logic to only consider rebuilding the clustered indexes in the table.
create table #fragmentation
(
FragIndexId BigInt Identity(1,1),
--IDENTITY(int, 1, 1) AS FragIndexId,
DBNAME nvarchar(4000),
TableName nvarchar(4000),
[Code] ....
View 5 Replies
View Related
Sep 17, 2006
What is the difference please?
View 1 Replies
View Related
Jul 23, 2005
Hi All,I am working on Web Application which deals with history data forreports and keeping track of changes.Current Solution :1) For each Entity I am having a column TID (Tracking ID) whichkeep on increasing for an instance of Entity. (so history and presentdata in the same table) . It makes querying the data difficult.2) I am also using month year table for Managing data i.e<TableNameMMYYYY> for a given month and yearPurposed Soluion :1) Using a seperate table so that history and present data isplaced seperately .2) Store All data in one table <TableName>Please guide me on advantages and disadvantages you pin point in thetwo approaches.With warm regardsJatinder
View 5 Replies
View Related
Mar 2, 2007
Hello,
I have the following query,
SELECT GroupInfo.GroupID, GroupInfo.GroupName
FROM GroupInfo INNER Join DeviceGroup ON(DeviceGroup.GroupID=Groupinfo.GroupID)
INNER Join Deviceinfo ON (Deviceinfo.SerialNumber=DeviceGroup.SerialNumber )
It's out put is as follow:
Group ID GroupName
0 Abc
1 Beta
0 Abc
0 Abc
0 Abc
1 Beta
2 Alpha
Now, I want to make such query which will give me result as a Group ID and Group Name but not in repeating manner, Like,
Group ID GroupName
0 Abc
1 Beta
2 Alpha
Hope I explained what I need to see in result pane.
Thanks,
Junior
View 1 Replies
View Related
Jan 30, 2008
In out web application it happens very rarely that same query gets executed more than once meaning that sp_executesql is degrading performance. Does anyone know a way to tell ADO.NET to stop encapsulating queries in sp_executesql? Thank you.
View 1 Replies
View Related
Jun 25, 2001
I found some fragmented data pages in my database tables.
I have found out using DBCC Showcontig commands.
I want to remove data fragmentation.
My tables are replicated. Is it possible to remove fragmentation
without using export and import. Please help me in fixing this problem.
Is there any way to fix replication failure with out recreating it.
My replication has failed saying is timout error.error is could not connect
subscriber. But I can able to ping the subscriber and if I create a new publisher and subscriber it is working fine. But existing one is not working.
What might be the possible reason. We have recreated the replication. We droped the existing one and recreated it. It took around 14 hours to sync.
Is there any way to resync with out recreate publication. We are using transaction replication and merge replication.
Vani
View 4 Replies
View Related
Nov 16, 2005
Hi All,
Very Ad-hoc requirement I am having.
I've got one table with two columns.
Column Name Data Type
1) Id Integer Identity
2) RemDate DateTime
I've to write one SP/JOB in that there will be an integer input parameter @numofday.
Say value of @numofday is 5 then.... in SP/Job I need to insert 31 - 5 = 26 records to above-mentioned table where date starting from 1st of current month.
This logic can be achieve through looping but if anyone can suggest some better way to achieve this functionality without use of looping.
Thanks in advance,
Jai
View 2 Replies
View Related
Jan 13, 2006
Happy new year to all! Now a question...
I added a new column to StagePayments table - Activity - which is supposed to end up being the same as the JobActivityID from the JobActivities table. Basically, I need to get JobActivities (JA) info and put it in the StagePayment (SP) column. Problem is there are duplicate JA/Descriptions and SP/Activities, so what I need is to take the first sequence SP/Activity and grab the first JA/JobActivityID that matches for a particular JobID. Then get the next one of each and so on and so on...
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Tables...
CREATE TABLE [dbo].[StagePayment] (
[PaymentID] [uniqueidentifier] NOT NULL ,
[JobID] [uniqueidentifier] NOT NULL ,
[Paid] [bit] NOT NULL ,
[Sequence] [smallint] NOT NULL ,
[Amount] [decimal](10, 2) NOT NULL ,
[Comment] [varchar] (2000) COLLATE SQL_Latin1_General_CP437_BIN NULL ,
[Activity] [varchar] (30) COLLATE SQL_Latin1_General_CP437_BIN NULL ,
[ActivityID] [uniqueidentifier] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[StagePayment] WITH NOCHECK ADD
CONSTRAINT [PK__StagePayment__457442E6] PRIMARY KEY CLUSTERED
(
[PaymentID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[StagePayment] WITH NOCHECK ADD
CONSTRAINT [DF__StagePayme__Paid__4668671F] DEFAULT (0) FOR [Paid]
GO
CREATE INDEX [IX_StagePayment] ON [dbo].[StagePayment]([JobID], [Sequence]) ON [PRIMARY]
GO
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
CREATE TABLE [dbo].[JobActivities] (
[JobActivityID] [uniqueidentifier] NOT NULL ,
[JobID] [uniqueidentifier] NOT NULL ,
[Sequence] [smallint] NOT NULL ,
[Activity_Status] [char] (1) COLLATE SQL_Latin1_General_CP437_BIN NOT NULL ,
[Description] [char] (30) COLLATE SQL_Latin1_General_CP437_BIN NOT NULL ,
[PlanStartDate] [datetime] NULL ,
[PlanEndDate] [datetime] NULL ,
[ActEndDate] [datetime] NULL ,
[AmountDue] [decimal](10, 2) NOT NULL ,
[CanDelete] [bit] NOT NULL ,
[Comments] [varchar] (2000) COLLATE SQL_Latin1_General_CP437_BIN NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[JobActivities] WITH NOCHECK ADD
CONSTRAINT [PK_JobActivity] PRIMARY KEY CLUSTERED
(
[JobActivityID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[JobActivities] WITH NOCHECK ADD
CONSTRAINT [DF_JobActivities_JobActivityID] DEFAULT (newid()) FOR [JobActivityID]
GO
CREATE INDEX [IX_JobActivity] ON [dbo].[JobActivities]([JobID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_JobActivity_1] ON [dbo].[JobActivities]([JobID], [Activity_Status]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_JobActivity_2] ON [dbo].[JobActivities]([JobID], [ActEndDate]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_JobActivities] ON [dbo].[JobActivities]([JobID], [PlanEndDate]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [JobActivities53] ON [dbo].[JobActivities]([JobID], [Description], [ActEndDate]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [JobActivities50] ON [dbo].[JobActivities]([JobID], [Description], [PlanEndDate], [ActEndDate]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_JobActivities_6] ON [dbo].[JobActivities]([JobActivityID], [Activity_Status], [Description]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_JobActivities_1] ON [dbo].[JobActivities]([JobID], [Sequence], [Description], [JobActivityID]) ON [PRIMARY]
GO
CREATE INDEX [IX_JobActivities_2] ON [dbo].[JobActivities]([JobID], [Sequence], [ActEndDate]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[JobActivities] ADD
CONSTRAINT [FK_JobActivity_Job] FOREIGN KEY
(
[JobID]
) REFERENCES [dbo].[Jobs] (
[JobID]
) ON DELETE CASCADE
GO
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
My example for one single JobID...
In JA -
JobActivityID Seq Description
34969C69-FF70-4313-91CC-43921DB3D74D 18 FOLLOWUP
48ACFEEE-3349-4F16-83E0-58F9B19E28E5 16 INSTALL
22507D44-6D0D-42DE-A211-9C23FDFCD19D 5 PLOTPLAN
BA88D04E-EBAE-40DB-A2C9-F909463D7F22 1 THANKU
83C48207-895B-4775-A62D-07059D8DEB62 10 NOTCUST
DBB8DF00-E26B-4E6F-9482-08E8CFE1588D 11 ROOMORD
BF621E91-E819-4F84-B507-0AA644D5C3F6 0 DWNPAY
6F595880-59D9-4E55-845D-19B477E8B179 2 THANKU
86D0A650-3B72-47E1-BDC2-2CA177DC3D53 12 NOTCSTRM
B0ABCC4C-A626-41C2-890C-3B9580326774 13 ROOMREC
F131C6FF-A86C-4527-A580-60FF7D3F0164 19 1YRFLWUP
7132625C-8E8B-4748-9176-6F06E8D0F20F 17 ARCMNT
AE06A938-323B-46EA-BA11-7D17B0985ACC 15 24HRCALL
DBEF21DF-35DA-48DE-8BF5-7F4A0EC0FA7A 14 24HRCALL
EBC15C77-95D5-4C42-AD88-861F9DD7688B 9 RECPERMT
97D3D755-4B6F-4564-842B-A06945AA8890 8 SUBPERMT
CAACAACA-3B97-41D5-9A4A-A4E3E963D0BF 6 SUBCAD
0C3CB2E0-F4E9-43CD-81E4-ACE9F4022033 3 PHONCALL
42D498BE-308A-413E-965D-ADE7A7A21B97 4 MEASURE
7654C5E3-BED5-4F78-ADC8-DD4E283ADDEE 7 RECCAD
In SP -
Seq Activity ActivityID
1 NULL
2 24HRCALL
3 24HRCALL
4 INSTALL
I need to get SP to end up looking like this -
Seq Activity ActivityID
1 NULL NULL
2 24HRCALL DBEF21DF-35DA-48DE-8BF5-7F4A0EC0FA7A
3 24HRCALL AE06A938-323B-46EA-BA11-7D17B0985ACC
4 INSTALL 48ACFEEE-3349-4F16-83E0-58F9B19E28E5
I have tried various versions of this...
begin transaction
update StagePayment
set Activity = (Select J.JobActivityID
from JobActivities J (nolock)
inner join StagePayment SP (nolock) on J.JobID = SP.JobID and J.Description = SP.Activity)
where Activity is not NULL
way too basic as I get this error...
Server: Msg 512, Level 16, State 1, Line 2
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
I know somehow I have to get the duplicate Activities in the same sequence as the Descriptions, but that's where I'm stuck?!?
If anyone is able to offer suggestions on how to get this to update correctly, I'd be very happy to hear about it!
Thanks in advance!
Tiffanie
View 2 Replies
View Related
Sep 28, 2006
Hi,
I need to query a database for a recordset and insert this into another database row-by-row.
For each record I want to provide a time of insert, but for each insert this time has to be incremented by 1, the time format is HH:MM:SS.0000 so for each inserted record the last decimal point would increment by 1.
Can I do this without a cursor ?
Any additional info I will provide gladly.
Thanks!
View 9 Replies
View Related
Mar 19, 2004
I am conducting stress testing for my website and keep getting deadlocks with the following message when one process is adding about 100 records per second and another process is trying to access the data:
Transaction (Process ID 499) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
What do I need to do in my stored procedures to avoid this? I only have ONE stored prcoedure that locks a row while incrementing an ID value. I am not doing any other locks, so is this a SQL Server system lock?
Any advise would be much appreciated. Thanks!
View 2 Replies
View Related
Jul 23, 2005
Hi All,I want to avoid using cursors and loops in stored procedures.Please suggest alternate solutions with example (if possible).Any suggestion in these regards will be appreciated.Thanks in advance,T.S.Negi
View 22 Replies
View Related
Jul 23, 2005
i'm trying to write a batch that will perform a complex task usingset-based selects instead of a row-based cursor. let me know if you canhelp me figure out how.description of what i'm trying to do:there is TABLE1, TABLE2, and TABLE3i want to select each row from TABLE1, do some analysis on the data ofthat row, and then perform an insert of some data into TABLE2, and somedata into TABLE3how do i do this in a T-SQL batch?
View 9 Replies
View Related
Jul 23, 2005
Application is a Work Tracking/Timesheet database.The increments of work are stored in the TimesheetItem table. Thiscontains, inter alia, the Work Code, the Start and the Duration thatthe employee spent that day on a particular project.Some employees in the Network Support Department don't complete astandard 7.5 hour day for various reasons, so for every Network Supportperson I need to update these particular days with an amount to bringthe total day's hours to 7.5.This SQL will get me a list of all TimesheetItem records for thepersonnel concerned.SELECTTimesheetItem.TypeID,[Work].WorkCode,TimesheetItem.Start AS Start,SUM(CAST(TimesheetItem.DurationMins AS float) / 60) AS HoursFROMTimesheetItem LEFT OUTER JOIN[Work] ON TimesheetItem.WorkID = [Work].WorkIDWHERE(TimesheetItem.EmployeeID IN(SELECT EmployeeID FROM Employee WHERE DepartmentID = 2))GROUP BYTimesheetItem.TypeID, TimesheetItem.Start, [Work].WorkCodeHAVING(TimesheetItem.Start >= @FromDate) AND (TimesheetItem.Start <= @ToDate)ORDER BYTimesheetItem.StartWhat I need is to group these records by EmployeeID where theaccumulated hours per day are < 7.5, so that I can then insert anincrement to make up the difference.I'm writing this from home and I don't have access to the tables toprovide a script, but there's nothing untoward there.Hope someone can help!ThanksEdward
View 10 Replies
View Related
Feb 28, 2006
I have a long sql batch that does this:1. Gets a list of all tables in user database that start with name COREdeclare@tablenamevarchar(30),@commandvarchar(2000),@cntintegerdeclare GetCOREOids cursor forselect sysobjects.namefrom sysobjectswhere ( OBJECTPROPERTY(sysobjects.id,N'IsUserTable')=1 ) and( sysobjects.name like 'CORE%' or sysobjects.name ='CMNSTRStructGeomBasicPort') and( sysobjects.name not like 'CORESpatialIndex%') and (sysobjects.namenot like 'COREDeletedObjects%') and( sysobjects.name not in('CORERelationOrigin','CORERelationDestination') ) and( sysobjects.id in ( select id from syscolumns where name = 'oid') )for read only2. Populates a temporary table with distinct oids from the list.create table [#tSP3DCoreOid] (oid UNIQUEIDENTIFIER)open GetCOREOidsfetch GetCOREOids into @tablenamewhile ( @@fetch_status = 0)beginselect @command = 'insert into [#tSP3DCoreOid] select distinct oid from' + @tablenameexecute(@command)--print @tablename + ' rows: ' + convert(char,@@rowcount)fetch GetCOREOids into @tablenameend /* while */--Clean upclose GetCOREOidsdeallocate GetCOREOids3. Creates a cursor to get "invalid" oids from 2 other tablesdeclare DanglingRelation cursor forselect oid from [dbo].[CORERelationOrigin] RO where not exists( select oid from [#tSP3DCoreOid] where oid = RO.oid )unionselect oid from [dbo].[CORERelationDestination] RD where not exists( select oid from [#tSP3DCoreOid] where oid = RD.oid )4. Loops thru. the cursor examining each oid and then calls aStoredProc to update another tabledeclare @objectOid uniqueidentifierdeclare @tempOid uniqueidentifieropen DanglingRelationfetch DanglingRelation into @ObjectOidwhile ( @@fetch_status = 0)beginset @tempOid='00000000-0000-0000-0000-000000000000'if left(@ObjectOid,8)='00000002'select @tempOid=oid from COREToDoList where Oid=@ObjectOidelse if left(@ObjectOid,8)='00000003'select @tempOid=oid from COREToDoRecord where Oid=@ObjectOidelse if left(@ObjectOid,8)='00000004'elseselect @tempOid=oid from COREBaseClass where Oid=@ObjectOidif @tempOid = '00000000-0000-0000-0000-000000000000'BEGINexec CORESetObjectIntegrity @ObjectOid, 2ENDfetch DanglingRelation into @ObjectOidendclose DanglingRelationdeallocate DanglingRelationdrop table [#tSP3DCoreOid]Now the #tSP3DCoreOid temp table can sometimes get as many as 7.5million rows and this is making the batch fail on SQLServer as it runsout of memory.I think the problem is the cursor here and would like somefeedback/tips on how to best optimise it.thanks a lotSunit
View 4 Replies
View Related
Oct 25, 2007
hi all,
i have a huge database and i am using Cursor to retrieve data and with each fatched data i m doing some operation....as my database is increasing the time duration to execute the cycle is also increation hugely....how to solve it??
thanks,
View 5 Replies
View Related
Sep 4, 2006
Hi, all!
Having a table with a varchar(10) column, I try to insert a row where the data for that column is more than 10 characters long. No surprise, I get a MSG 8152 error.
What I want is to get the overlong data clipped to the maximum column width, and no error. Is that possible?
View 3 Replies
View Related
Oct 25, 2007
Hi everyone,
As first task I've got Data Flow which loads a set of data into a .NET recordset.
After that, inmediately flow execution goes to For..Each Loop. I'd like to avoid go in that direction when Data Flow returns zero rows.
How can I do such thing?
I've tried this on Precedence Constraint Editor but it doesn't work..It doesn't recognize EOF keyword..
@[User::ResulSet] == EOF
Thanks in advance for your input,
Enric
View 3 Replies
View Related
May 1, 2008
Hi All,
I use a report expression something like this.
= SUM(Fields!MTD_TotNetCOAmt.Value)/SUM(Fields!TotBalance.Value)* 12
When Both fields have 0 vlaues, it gives a error massege. How do I avoid this?
Thanks
View 5 Replies
View Related
Dec 16, 2007
Hi all,
I have 10 tables with unique values such as mobile no: and message in each table.But now the problem is that this same mobile no: may be there in other tables.How can i eliminate the records from other tables.Can anyone tel me a suggestion.
Thank U.
View 13 Replies
View Related
Jul 8, 2000
Hi,
I'd really appreciate advice on now to avoid displaying duplicates in a resultset like the one below, only displaying the first field once and all the rows that correspond to that field all listed listed below. Distinct doesn't work. Thanks
Customer Name Address
============= =======
Big Bob's Books 123 Stone Lane
456 Seaside Drive
789 Waterhouse Square
1234 Mystery Drive
5678 Simple St
Tiny Tim's Tools
Tiny Tim's Tools
View 1 Replies
View Related
Mar 12, 2003
I have to admit, I'm usually using the MySql database, but in this particular case I have to use MSSQL 2000.
Over to my problem.
I'm building a web-based system (who isn't these days) in which the user types arbitrary information that is published when the user pushes the save button. Nothing new about that.
Here comes the tricky part, when the user wants to edit an existing item I copy all information in the database and sets the id of the 'edit-copy' to the negative value ( id 45 becomes id -45 for the edit-copy). This is also done on all items in other tables that is connected to the main item.
This way I get a copy that the user may edit without messing up the published information. When the user is done I either delete all the negative items (cancel) or delete the positive items and update the negative to become positive (save).
So far so good, allmost... my problem is that the transaction log grows tremendously.
Is there any other way to accomplish a safe edit that doesn't affect the transaction log as much as my current solution?
Could I be doing something wrong when updateing or deleteing my items?
View 1 Replies
View Related
Jun 27, 2005
hate to gripe, but this was bad. I have taken several sql server classes over the years, and have always been happy with the class, but avoid the 2733a (upgrading you database administration skills to sql 2005). Class is poorly devloped and incomplete. Half of the examples were to do things such as mirroring and replication from a command prompt. I thought I was in an Oracle 8 class. It was clear much of this functionality is not complete. In fact most of the class was command prompt based. They had absolutley nothing about the upgrade process from SQL 2000 to SQL 2005. I hope MS is not taking a step backwards, I always thought the strong suite for MS was the enterprise manager tool suite. The instructor did tell me there was an upgrade to the class that just came out, but it has a long way to go.
View 1 Replies
View Related
Sep 21, 2005
How do you avoid phantom inserts? Can you recommend a strategy (or other posts, articles) to avoid "phantom inserts" with the default SQL Server isolation level ("Read Committed").
Thanks!
View 14 Replies
View Related
Jan 26, 2004
Hello All,
I have faced a network problem during some days, what forced one of our replications to be stopped.
The Publisher database is a high volume database.
After I re-started the replication, the Subscirber database has its transacting log size increased quickly, because of the high volume of information to be inserted.
My concern is the way it is working, there will be no enough space for the log or for its backup files.
So, I have created a TSQL job within the following commands:
BACKUP LOG database_name
WITH TRUNCATE_ONLY
DBCC SHRINKDATABASE (database_name,TRUNCATEONLY)
It's running every 20 minutes, however the transaction log remains increasing.
I have also changed the db_option "SELECT INTO/BULKCOPY" to TRUE, in order to avoid logging bulk copies, but I believe, it didn't work because it didn't apply to replication process.
Does Anybody know if I can disable the transaction log or avoid this incresing of size during the replication?
Thanks a lot!
Regards,
Felicia Schimidt
felicia.schimidt@br.flextronics.com
View 6 Replies
View Related
Nov 6, 2013
I have some SQL scripts for SQL Views and getting divide by zero error. I am using
COALESCE(Field1,Field2,1)/COALESCE(Field3,Field4,1)
This function can avoid NULL but how I can avoid 0 ?
Is there any function in SQL that can take care both NULL and 0 and replace it with 1.
View 5 Replies
View Related
