May 18, 2008 at 2:30 am
Hi All,
I am totally new to sql server , The task assigned to me is to reindex the sql server database which is up to 70GB of database, so i have to reindex the database because application which is integrated with database is getting more slower day by day.
i have tried DB maintenance plan but it gave me error and here is the error log generated by SQL server
-----------------------------------
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server '2CONNECT-IVRDB' as 'NT AUTHORITY\SYSTEM' (trusted)
Starting maintenance plan 'index' on 5/16/2008 4:00:00 AM
[1] Database TEL_TEST: Index Rebuild (leaving 100%% free space)...
Rebuilding indexes for table 'ACCESS_ALLOW'
Rebuilding indexes for table 'ACCESS_BLOCK'
Rebuilding indexes for table 'ACCOUNT_ALIASES'
Rebuilding indexes for table 'ACCOUNT_GROUP_MERCHANT_MAP'
Rebuilding indexes for table 'ACCOUNT_GROUP_ROUTING_MAP'
Rebuilding indexes for table 'ACCOUNT_GROUPS'
Rebuilding indexes for table 'ACCOUNT_REFERENCES'
Rebuilding indexes for table 'ACCOUNT_TYPES'
Rebuilding indexes for table 'ACCOUNTS'
Rebuilding indexes for table 'ANI_FORMAT'
Rebuilding indexes for table 'BADCC'
Rebuilding indexes for table 'BATCHES'
Rebuilding indexes for table 'BILLING'
Rebuilding indexes for table 'BILLING_PACKAGE_ITEM_TYPES'
Rebuilding indexes for table 'BILLING_PACKAGE_ITEMS'
Rebuilding indexes for table 'BILLING_PACKAGES'
Rebuilding indexes for table 'BILLING_TYPES'
Rebuilding indexes for table 'CHARGE_TYPES'
Rebuilding indexes for table 'CLASS_OF_SERVICE'
Rebuilding indexes for table 'CURRENCY'
Rebuilding indexes for table 'CUSTOMER_INCIDENT_DETAILS'
Rebuilding indexes for table 'CUSTOMER_INCIDENTS'
Rebuilding indexes for table 'CUSTOMER_SERVICE'
Rebuilding indexes for table 'CUSTOMERS'
Rebuilding indexes for table 'DEVICE_MODULE_MAP'
Rebuilding indexes for table 'DEVICES'
Rebuilding indexes for table 'DNIS_FORMAT'
Rebuilding indexes for table 'DNIS_MODULE_MAP'
Rebuilding indexes for table 'EMAILLOG'
Rebuilding indexes for table 'ENTRY_TYPES'
Rebuilding indexes for table 'EVENT_LOG'
Rebuilding indexes for table 'EVENT_VIEWER_ENDPOINTS'
Rebuilding indexes for table 'INCIDENT_TYPES'
Rebuilding indexes for table 'INUSE'
Rebuilding indexes for table 'INVOICE_BATCH_TYPES'
Rebuilding indexes for table 'INVOICE_BATCHES'
Rebuilding indexes for table 'INVOICE_STATEMENTS'
Rebuilding indexes for table 'INVOICES'
Rebuilding indexes for table 'IPCOMMAND'
Rebuilding indexes for table 'LANGUAGE_GROUP_ITEMS'
Rebuilding indexes for table 'LANGUAGE_GROUPS'
Rebuilding indexes for table 'LANGUAGES'
Rebuilding indexes for table 'MODULES'
Rebuilding indexes for table 'NODE_TYPES'
Rebuilding indexes for table 'OPTIONS'
Rebuilding indexes for table 'OUTBOUND_ROUTES'
Rebuilding indexes for table 'PREFIX_MAP'
Rebuilding indexes for table 'RATE_PLANS'
Rebuilding indexes for table 'RATE_SCHEDULE_ITEMS'
Rebuilding indexes for table 'RATE_SCHEDULES'
Rebuilding indexes for table 'RATE_TIER_ITEMS'
Rebuilding indexes for table 'RATE_TIERS'
Rebuilding indexes for table 'RATE_TYPES'
Rebuilding indexes for table 'RATES'
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL Server Driver][SQL Server]DBCC failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
** Execution Time: 0 hrs, 0 mins, 29 secs **
End of maintenance plan 'index' on 5/16/2008 4:00:29 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
--------------------------------
I have tried to turn on QUOTED_IDENTIFIER & ARITHABORT manually in query analyzer but no use so please help me
so please help me
May 18, 2008 at 2:54 am
Check these Microsoft KB articles and see if it helps,
http://support.microsoft.com/kb/902388
http://support.microsoft.com/kb/301292
[font="Verdana"]- Deepak[/font]
May 18, 2008 at 3:45 am
Dear deepak ,
thanks for your use full reply , here situation is soo much critical and i am afraid to execute this query on my Database , because it is very critical machine.
so can you please tell me how much risk in that query and how much time it will take for the database which is about 70GB and how much chances of screwing up the service because it might cause big down time in case if index is not working
might my questions are stupid so please don't mind
Regards,
Bilal
May 18, 2008 at 4:07 am
The only thing what could happen is that an index is gone, no data corruption. Don't you have an test environment?
To be sure: to save your indexes, let Enterprise manager create a script for your indexes.
Also, if your database is slowing down, you might consider:
- are statistics up to date?
- have you ever run dbcc updateusage('{databasename}')
- is Autoshrink enabled ? (turn it off for production servers)
Wilfred
The best things in life are the simple things
May 18, 2008 at 5:53 am
thanks for wonderful reply
So please give me your suggestions weather my direction is right or wrong
here i have application use for billing for customers accounts
the application which is used by clients back provided by SQL Server is very slow now , even when client is trying to generate the encrypted numbers for prepaid accounts it will took 8 hours or more and some time it is hanged
and also my database seems like having too much garbage values in it and i want to remove these values .
so which solution is best & secure
indexing
updating statistics
integrity check
or any other
Thanks all for your excellent support
May 18, 2008 at 8:47 am
- if you want to play it safe, make a full backup before you start.
- then perform then integrety check, so you know the data is ok before you start your operation.
This is the script I use to rebuild indexes (after the backup and the check)
-- Rebuilding a disabled clustered index cannot be performed when the ONLINE option set to ON.
--
--
set nocount on
declare @OnlineRebuild char(1)
Set @OnlineRebuild = 'Y'
print '-- Begin ' + db_name() + ' ' + convert(varchar(25),getdate(),121) ;
Declare @SQLStmt varchar(max);
DECLARE @Tellerke BIGINT
SET @Tellerke = 0
declare c1 cursor for
--print 'use [' + db_name() + ']' + char(10) + 'GO'
-- Using a CTE to avoid multiple scans
-- Select Object that cannot use Online-reindexing
-- (disabled indexes, colomns of datatype (n)text, image, (n)varchar(max),.. )
WITH cteExcludedObjects (TABLE_SCHEMA , TABLE_NAME, INDEX_NAME, INDEX_ID )
AS (
Select T.TABLE_SCHEMA , T.TABLE_NAME, I.[name], I.[index_id]
from INFORMATION_SCHEMA.Tables T with (nolock)
Inner Join sys.indexes I with (nolock)
ON I.[object_id] = object_id('[' + T.TABLE_SCHEMA + '].['+ T.TABLE_NAME + ']')
and I.is_disabled = 0 -- Only select active indexes !
where exists ( Select *
from sys.indexes I with (nolock)
where I.[object_id] = object_id('[' + T.TABLE_SCHEMA + '].['+ T.TABLE_NAME + ']')
and I.is_disabled = 1 )
OR EXISTS (SELECT *
FROM INFORMATION_SCHEMA.Columns C
Where C.TABLE_SCHEMA = T.TABLE_SCHEMA
AND C.TABLE_NAME = T.TABLE_NAME
-- exclude text, ntext, image, varchar(max), nvarchar(max), varbinary(max)
AND( C.CHARACTER_OCTET_LENGTH > 8000
OR C.CHARACTER_OCTET_LENGTH = (-1)
)
)
)
Select 'use [' + db_name() + '] ;
print '' tabel [' + T.TABLE_SCHEMA + '].['+ T.TABLE_NAME + '] '' + convert(varchar(25),getdate(),121) ;
ALTER INDEX ALL ON [' + T.TABLE_SCHEMA + '].['+ T.TABLE_NAME + '] REBUILD '
+ case @OnlineRebuild when 'Y' then ' WITH ( ONLINE = ON )' else '' end + ' ;'
from INFORMATION_SCHEMA.Tables T with (nolock)
Left Join cteExcludedObjects D
on T.TABLE_SCHEMA = D.TABLE_SCHEMA
and T.TABLE_NAME = D.TABLE_NAME
Where T.TABLE_TYPE = 'BASE TABLE'
and D.TABLE_SCHEMA IS NULL
and T.TABLE_NAME <> 'dtproperties'
Union ALL
Select 'use [' + db_name() + '];
print '' tabel [' + T.TABLE_SCHEMA + '].['+ T.TABLE_NAME + '] IX ' + cast(D.INDEX_ID as varchar(15)) + ' [' + D.INDEX_NAME + '] '' + convert(varchar(25),getdate(),121) ;
ALTER INDEX [' + D.INDEX_NAME + '] ON [' + T.TABLE_SCHEMA + '].['+ T.TABLE_NAME + '] REBUILD WITH ( ONLINE = OFF ) ;'
from INFORMATION_SCHEMA.Tables T with (nolock)
inner join cteExcludedObjects D
on T.TABLE_SCHEMA = D.TABLE_SCHEMA
and T.TABLE_NAME = D.TABLE_NAME
Where T.TABLE_TYPE = 'BASE TABLE'
and INDEX_NAME is not NULL -- Heap niet van toepassing
order by 1 ;
open c1
FETCH NEXT FROM c1 INTO @SQLStmt
WHILE @@FETCH_STATUS = 0
BEGIN
-- print @sqlstmt
exec ( @SQLStmt )
set @Tellerke = @Tellerke + 1
FETCH NEXT FROM c1 INTO @SQLStmt
END
-- Cursor afsluiten
CLOSE c1
DEALLOCATE c1
Print ' '
Print 'Number of Objects processed'
Print '----------------------------'
Print @Tellerke
print ' '
print '-- Usage ' + db_name() + ' ' + convert(varchar(25),getdate(),121) ;
DBCC UPDATEUSAGE (0) with count_rows ;
print '-- Usage Statistics ' + db_name() + ' ' + convert(varchar(25),getdate(),121) ;
EXEC sp_updatestats ;
print '-- Finish ' + db_name() + ' ' + convert(varchar(25),getdate(),121) ;
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 18, 2008 at 11:58 am
if u want u can choose the most accessed tables then rebuild the indexes manually,
then update thier statistics with full scan.
thats only if u want to gain some time to do some tests on the scripts or if u want to figure out the best way.
and i recommend update statistics after rebuilding the indexes.
..>>..
MobashA
May 18, 2008 at 2:12 pm
do a dbcc updateusage() first
reindex
and finally update statistics
Wilfred
The best things in life are the simple things
May 19, 2008 at 6:10 am
do i need to make any changes in to this script run as it is
May 19, 2008 at 7:15 am
If I were in your shoes, I'd look for "exec" statements, and replace them with "print" statements, then execute the script a see what it does.
It was wrong of me in the first place, because I posted the script including the uncommented "exec" statements. :ermm:
Normaly I only post "generate this or that " kind of scripts.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 19, 2008 at 7:21 am
ok let me try with print statement and see the results and let you know , if you can write script for my specific database it will be very helpful for me , because i am totally empty in database knowledge
Regards,
Bilal
May 19, 2008 at 7:23 am
mobasha (5/18/2008)
if u want u can choose the most accessed tables then rebuild the indexes manually,then update thier statistics with full scan.
thats only if u want to gain some time to do some tests on the scripts or if u want to figure out the best way.
and i recommend update statistics after rebuilding the indexes.
Wilfred van Dijk (5/18/2008)
do a dbcc updateusage() firstreindex
and finally update statistics
update statistics after an index rebuild is not only unnecessary, it's counterproductive.
An index rebuild updates statistics with full scan. If you go after that and update the statistics with a sample, you can reduce the accuracy of the stats.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 19, 2008 at 7:42 am
syedbilalmasaud (5/19/2008)
ok let me try with print statement and see the results and let you know , if you can write script for my specific database it will be very helpful for me , because i am totally empty in database knowledgeRegards,
Bilal
You'll need to execute the script with a connection to the correct database.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 19, 2008 at 7:54 am
This may be way off base (as I sometimes am) but maybe the index is on a View and the settings are not correct?
Toni
From the BOL on Indexed Views:
The CREATE INDEX statement must meet these requirements in addition to the normal CREATE INDEX requirements:
The user executing the CREATE INDEX statement must be the view owner.
These SET options must be set to ON when the CREATE INDEX statement is executed:
ANSI_NULLS
ANSI_PADDING
ANSI_WARNINGS
ARITHABORT
CONCAT_NULL_YIELDS_NULL
QUOTED_IDENTIFIERS
The NUMERIC_ROUNDABORT option must be set to OFF.
May 20, 2008 at 6:24 am
I have a question. Are you using 2000, or 2005?
The reason is that the http://support.microsoft.com/kb/902388 matches your issue exactly.
Can you send us exactly what you type when you are running this?
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply