Is Any Way To Reindex 27 Gig Db Without Logging
Sep 25, 2002
Hello everybody.
1. I have 28 Gig database with 4 tables above 4 Gig each with very bad
fragmenataion, each table has between 3 and 5 indexes
2. Database set for full recovery and I use custom log Shipping to restore
db on stand by server every 15 min.
I tried to run DBCC INDEXDEFRAG on one index on 4Gig table .
following took place
1. It took 4 hrs to complite DBCC INDEXDEFRAG
2. log shipping fail.
3. log file size of 2 Gig generated after DBCC INDEXDEFRAG complited
I tried to run drop and create clustered index on table it create same
problem - log growing, log shipping fails
(if log shipping fails and stand by database get suspended it will take 6- 8
hrs to restore it from backup and apply all logs)
So my question is
What would be best way to rebuild- reindex - defragment 28 Gig database
when it set to fully recovery and log shipping
Thank you
Alex
View 1 Replies
ADVERTISEMENT
Jun 18, 2007
Greetings,
I am developing a package on my local workstation. I have defined two logging service providers. One is for SQL Server and the other is for the Windows Event Log. I am using the Dts.Log method in a script task to write log entries.
Logging is working properly with the SQL Server provider and rows are being inserted into the sysdtslog90 table. However, the only events that are being logged in the Windows Event Log are the package start and end events which I believe SSIS is doing automatically anyway.
Is there something I need to do to enable WIndows Event Log logging other than defining a log provider and making sure it is checked active? Won't SSIS write to two different logs with one Dts.Log call? Any ideas on what might be going wrong with my approach?
Thanks,
BCB
View 3 Replies
View Related
Oct 17, 2007
Hi,
I decided to use the SQL Server log provider to store logging data of all my Integration Services packages. I also created some reports about this data for operating purposes.
I have a problem occurs the name of the executing package is not always written to the log,but the name of the single task which failed. But that is not very useful information for operating, because I do not see any chance to get the name of the package by the information which is logged in the sysdtslog90 table in the database which I defined for SSIS Logging.
How do I configure the package to always log the package information into the table, too?
Best regards,
Stefoon
View 5 Replies
View Related
Feb 19, 2002
Hi guys.
I an application here developed by a third party software house.
In the past, for some reason, the database would fail daily. The software
house recommended that we use dbcc reindex on all tables within the
databases twice daily. This was scheduled and is now running. Now the
database no longer fails.
The fix works and I don't understand why.
I don't understand why this would fix the problem. Why would reindex
twice daily solve the problem.
It seems excessive to have to reindex every user table twice daily.
Parg
View 1 Replies
View Related
Apr 26, 2001
I tried to run dbcc reindex on all user tables in a database. There are no
clustered indexes, but multiple non-clustered indexes on each table.
The output file from dbcc reindex shows that it should have worked. But when I
run DBCC SHOWCONTIG, the scan density of the indexes that were in bad shape did
not improve.
Any ideas?
Thanks,
Ben Reeder
.
View 1 Replies
View Related
Apr 16, 2002
Hi guys,
I need a help with this question. In SQL 2000, Can i run update,insert or delete queries while the Indexes of that table is being Rebuilt? Will
i get blocked by the DBCC DBreindex process?
thank you
View 1 Replies
View Related
May 1, 2008
I have a process that runs each day and it imports about 550K records into a database. My questions is it appears I have to reindex the database after each import otherwise the sp's that I have written will just run and run and run. After the reindex job things run within 60 seconds. I am just looking for some insight on why, I understand why a reindex is done, but dont know if I understand why I have to reindex every time.
View 9 Replies
View Related
Jul 12, 2007
Hi experts, I would like to ask for this error that occurs upon executing my reindexing script
here is my script
USE mydatabase
DBCC DBREINDEX('outpatient', '', 70)
go
Then this error message will appear. I researched for this error and from the site that I've found they say that the table is corrupted? That I need to restore a better backup..?
The statement has been terminated.
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0x55555555; actual signature: 0x55555545). It occurred during a read of page (1:353409) in database ID 10 at offset 0x000000ac902000 in file 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataBizbox_HS7.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Darren Bernabe Blanco
View 4 Replies
View Related
Jul 18, 2007
how often should be done and if it don't do it what will happen?
=============================
http://www.sqlserverstudy.com
View 1 Replies
View Related
Oct 21, 2007
I#ve been doing disaster recovery on a web box that died today.
So I thought I'd do some "downtime" maintenance on the DB server
I ran a BDREINDEX on all tables, all indexes. (I know this is the 2000 way, but I assume its as good as the proper 2005 way??).
5 minutes on a 10GB database. Not bad!
I checked the DEFRAG and UPDATE STATS processes that run overnight.
They are basically defragging only tables with SHOWCONTIG indicating fragmentation. And then doing an UPDATE STATISTICS WITH FULLSCAN on all tables
That is taking an average of 30 minutes ...
Is DBREINDEX the equivalent of an UPDATE STATISTICS WITH FULLSCAN, or is it in some way a smaller-sample version?
I'm wondering why I don't just lock the DB and do a REINDEX of everything in 5 minutes ...
Kristen
View 4 Replies
View Related
Sep 27, 2006
Hi,i have several tables in production whose contents are renewd totally in 1week. So everyd day we delete ~15% records and then insert 15% new.And after a few days, the performances drops :TABLE level scan performed.- Pages Scanned................................: 169617- Extents Scanned..............................: 21630- Extent Switches..............................: 153827- Avg. Pages per Extent........................: 7.8- Scan Density [Best Count:Actual Count].......: 13.78% [21203:153828]- Logical Scan Fragmentation ..................: 45.06%- Extent Scan Fragmentation ...................: 52.66%- Avg. Bytes Free per Page.....................: 5042.5- Avg. Page Density (full).....................: 37.70%I can't program a dbcc reindex every day because of concurrent access (itlocks the tables too long), actually i can only program it on sunday.What else can i do ? I can adjust the fill factor but how to find the goodvalue if i don't want to waste space.The total size of the database is ~150GB.Thx
View 1 Replies
View Related
Sep 12, 2005
I recently read the project real ETL design best practices whitepaper. I too, want to do custom logging as I do today, and also use SSIS logging. The paper recommended using the variable system::PackageExecutionId to tie the 2 logging methods together.
View 4 Replies
View Related
Oct 2, 2001
I am currently running the Back Office Resource Kit Log shipping option for a database running on an SQL 7 installation. As part of the on-going maintenance work that we are being asked to perform by the application vendor I need to run a DBCC REINDEX run on most of the tables in the database. Currently this is done by stopping the log shipping routine and then running the reindex script, then taking a full backup and restoring the backup to the secondary server then restarting the log shipping scripts. This is a very time consuming task that has to be performed at unsociable hours.
Has anybody got an opinion as to if this would work at the same time as the log shipping scripts or do I have to continue as at present.
Responses gratefully received.
Regards
Phil Corby
Geest IT Services
View 3 Replies
View Related
Dec 14, 1999
I am maintaining a large table with millions of rows that has two non clustered indexes and data changing frequently, I need to keep the indexes fresh. Update Statistics runs much quicker than Reindex. What is the appropriate situation for each and why?
Thanks in advance.
View 1 Replies
View Related
Nov 29, 2007
All,
I first ran indexdefrag on a table with 1.5 billion rows.
logical fragmentation was at 95%.
logical frag went down to 3% with no real effect on disk.
DBCC reindex had previously been bombing undetected.
Now I've run a reindex on this table:
Reindex Job with Fillfactor =100
Ran in 3:05
Free Disk went from ~150GB before operation to 49GB
File4 went from 347GB to 504GB
Why has so much free disk been consumed by this operation and not released ??????????
Is my only choice to shrink data file???
thanks
Env.
Win2k ENT os
SQL 2k5 std 64bit
View 4 Replies
View Related
Dec 29, 2003
I have a production 60GB database set to Full Recovery and every 15 minutes I am log shipping to a Stand by Server .
During the production hours there are no problems but at night when I run DBCC DBREINDEX, the log grows to 22GB and because of this I have a problem sending this over the network to the stand by server.
I tried changing the recovery model to Bulk_Logged but the there is no difference in log file backup size.
AnyIdea
View 1 Replies
View Related
Jun 19, 2008
Dear All,
is it ok to run index rebuild script on publisher and after that in subscriber? what are the steps i need to take to do this?
will it affect the replication? please help me here.
Arnav
Even you learn 1%, Learn it with 100% confidence.
View 3 Replies
View Related
Jun 26, 2006
Hi Folks,SQL Server 2000 SP3 on Windows 2000. I have a database on which I ranthe command :dbcc dbreindex ('tablename')gofor all tables in the database. Then I compared the dbcc showcontigwith all_index output from before and after the reindex and on thelargest table in the database I found this. First output is prior toreindex:Table: 'PlannedTransferArchive' (1975014117); index ID: 1, database ID:7TABLE level scan performed.- Pages Scanned................................: 184867- Extents Scanned..............................: 23203- Extent Switches..............................: 23324- Avg. Pages per Extent........................: 8.0- Scan Density [Best Count:Actual Count].......: 99.07% [23109:23325]- Logical Scan Fragmentation ..................: 11.13%- Extent Scan Fragmentation ...................: 35.46%- Avg. Bytes Free per Page.....................: 60.0- Avg. Page Density (full).....................: 99.26%Second output is from after the reindex:DBCC SHOWCONTIG scanning 'PlannedTransferArchive' table...Table: 'PlannedTransferArchive' (1975014117); index ID: 1, database ID:8TABLE level scan performed.- Pages Scanned................................: 303177- Extents Scanned..............................: 37964- Extent Switches..............................: 42579- Avg. Pages per Extent........................: 8.0- Scan Density [Best Count:Actual Count].......: 89.00% [37898:42580]- Logical Scan Fragmentation ..................: 43.19%- Extent Scan Fragmentation ...................: 24.78%- Avg. Bytes Free per Page.....................: 75.1- Avg. Page Density (full).....................: 99.07%Following are my concerns:The following numbers are all higher after reindex than before reindex:pages scanned, extent switches, logical scan fragmentation, avg bytesfree per page, avg page density.scan density is lower after reindex than before reindexSeems to me that the numbers that are higher after reindex should belower and numbers that are lower after reindex should be higher? Ididn't specify the fill factor in the dbcc reindex command so it shouldhave used the default fill factor. The fill factor has never beenchanged on this machine.Am I missing something?Thanks,Raziq.*** Sent via Developersdex http://www.developersdex.com ***
View 3 Replies
View Related
Feb 3, 2000
Does running DBCCReindex update the space allocated columns in sysindexes? I understand that running dbcc updateusage updates the space allocated columns in the sysindexes table. But, I cannot find any documentation that indicates whether dynamically rebuilding the indexes as opposed to drop and recreating the indexes updates the space allocated columns in the sysindexes table?
Any information would be helpful.
Thanks.
Gail Wade
Database Administration
Raymond James Financial
gwade@it.rjf.com
View 3 Replies
View Related
Apr 22, 2004
I have a standard reorganise/reindex job running against a 32GB database on SQl Srever 2000. When trying to run the job it fails and returns Error 1105 <'PRIMARY' filegroup is full>. What's confusing me is that I have 53GB free on the drive on which my Primary file group sits.
Has anyone else come accross this problem when trying to set up a regular reindex job?
(more detail) the maintenance plan only includes the reorganisation/reindex job, no other jobs - including backing up the DB - are included. The DB in question is the only DB on the server: it's a test server.
View 2 Replies
View Related
Feb 22, 2015
I'm trying to understand what is happening to one large table.
In a DB SQL 2008R2, I'm trying to track a rapidly increasing DB size. It's due to one table recently added.
Despite the table's no rows increasing its size reduces after a scheduled re-index.
I've recorded the space used by this table by recording EXEC sp_spaceused 'tableName'
---
NoRows reserved data index_size unused
128864512300384 KB 2290928 KB9432 KB24 KB AFTER reindex
128864515406232 KB 5366184 KB39280 KB768 KBBEFORE reindex
N.B. The only thing I'm aware of happening in the time period is a reindex as part of scheduled task. I could be missing something else happening.
The table has only one Index the PK which is clustered, No Fill factor is specified. The Server Default fill factor =0.
I read fillfactor=0=100 Will always try and fill the pages so space used will be minimised?
After running the reindex the index fragmentation is v.low. I've not recorded the fragmentation before reindex.
I can see the Data is not added in Clustered index order.
View 2 Replies
View Related
May 18, 2015
I have a weekly Maintenance Plan Reindex job that has failed because of a deadlock. My question seems simple enough and I'm ashamed to say I ought to know this answer, but here goes: Does the rest of a given job continue after such failures (this one was maybe 3/4 through the log) occur?
View 6 Replies
View Related
Jan 18, 2006
Does anyone know what the commands would be? I am trying to create a job that puts a DB in simple mode then launches a reorg and re-index, then sets it back to full when it is complete. This way I can eliminate large transaction logs being created.
Any help would be great!
Thanks
View 1 Replies
View Related
Jun 3, 2015
I inherited a system which has an index on a set of columns which allow more than 900 bytes of data in it. We know one of the fields can be shortened to shrink the potential key size below 900 bytes.
The problem is the table is about 120m rows, and the index currently on that column is seeked (sought?) on about 2.5m times a day.
At its simplest, I want to drop the existing index, alter the column to shrink the varchar size, and then rebuild the index on the newly shortened column.
On a smaller, less used table, I might just do this in outside of business hours and call it a day, but I'm concerned that this will take a long time and block a lot of operations.
1) IIRC, shrinking a column, unlike widening it, is much more expensive, even if there are no values which would actually end up trunacted. Is this right?
2) I did a few tests on some other smaller (2+ m) row tables and was still able to select data out of the table. I don't think this covered all the read scenarios, but are there known scenarios which would simply not work during an index build?
3) I haven't yet tried DML operations to the table while it's doing either the column update or an index build. what scenarios would or would not be blocked?
View 0 Replies
View Related
Sep 25, 2007
here is example: DBCC REINDEX('?')
Thanks!
View 2 Replies
View Related
Dec 6, 2006
How can I check from database username and password? It doesent need any special authentication, just a lookup through the database and if the user exist than continue with the next page.Thanks
View 2 Replies
View Related
Oct 1, 2004
I have a web application accessing a SQL Server database (the ususal stuff).
I want to be able to log who did what on which table. I need to display this information on the web application. Is there an easy way of doing this, rather that making duplicates of a lot of data?
The best way I have thought of so far is making a new table with the following fields:
Table_Changed
Table_Primary_Key
Old_Field_Value
New_Field_Value
User
Date_Changed
Every time someone changes something, it is logged in this table, so that, at any time, I can display who changed what.
I have one more question. If I do do it this way, is there a way of getting the primary key value of any table? E.G. could I do something like this_table.primary_key.value ?
Jags
View 3 Replies
View Related
May 30, 2001
Is there a way to produce a log of all SQL statements hitting a database in a given range of time by a specific SPID? Obviously the SQL Server activity logs do not go into that much detail, except when errors are produced or a change is made to a system table. Is there a setting to add more detail, or to log a specific SPID's actions, or maybe a third party software that will give me what I am looking for?
Thanks. JT
View 1 Replies
View Related
Aug 22, 2000
does SQL 7.0 have any built in logging capabilities to identify row level actions by operator. For instance, can it tell me that a particular user deleted or inserted a row? How would I tell it who the operator is?
View 2 Replies
View Related
Jun 11, 2004
Is there a way to stop logging DML? I have a large delete and I dont want to log the operation, is it possible?
View 2 Replies
View Related
Dec 9, 2004
Hi,
is there any chance to log all incoming SQL-statements with date/time and duration ?
Thx for help.
dajm
View 2 Replies
View Related
Aug 20, 2007
Is there anyway to log every incoming command, INSERT/UPDATE/SELECT/DELETE to a seperate database table?
Thanks
View 4 Replies
View Related
Mar 27, 2008
I've been asked to write a trigger that will basically log changes to certain fields in certain tables, then create a front-end application where the user will be able to review the info. The front-end app. is not a problem for me - the trigger is. I have found example of how to do this on Update when it's a complete row you want to log, but not a specific field. In addition, I also need to know if someone is attempting to read certain data and who that user is. If the user is not someone that is allowed to read the data, then I need to send an email alert.
I believe it's possible to do the above (despite my lack of knowledge :) -
Does anyone know where I can get more information on how to accomplish the above - or where to start looking?
Thanks to any who can guide me in the right direction.
View 1 Replies
View Related
