QUOTED_IDENTIFIER and ARITHABORT for Maintenance plans

Hi,
I would like to setup a maintenance job to 'Rebuild Indexes' as my
'Optimization Job' keeps failing on "DBCC failed because the following set
options have incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
What command do I need to use to rebuild all the indexes on all the tables.
I see DBCC DBREINDEX but that needs table name as a parameter. Any kind of
help is greatly appreciated.
Thank you
Take a look at DBCC SHOWCONTIG in BooksOnLine for the sample near the
bottom. This will rebuild the indexes that actually require it given
certain percentage of fragmentation.
Andrew J. Kelly SQL MVP
"helpplease" <helpplease@.discussions.microsoft.com> wrote in message
news:7DB5F397-1671-4F97-9585-AC7C20382FA1@.microsoft.com...
> Hi,
> I would like to setup a maintenance job to 'Rebuild Indexes' as my
> 'Optimization Job' keeps failing on "DBCC failed because the following set
> options have incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
> What command do I need to use to rebuild all the indexes on all the
> tables.
> I see DBCC DBREINDEX but that needs table name as a parameter. Any kind
> of
> help is greatly appreciated.
> Thank you

QUOTED_IDENTIFIER and ARITHABORT for Maintenance plans

Hi,
I would like to setup a maintenance job to 'Rebuild Indexes' as my
'Optimization Job' keeps failing on "DBCC failed because the following set
options have incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
What command do I need to use to rebuild all the indexes on all the tables.
I see DBCC DBREINDEX but that needs table name as a parameter. Any kind of
help is greatly appreciated.
Thank youTake a look at DBCC SHOWCONTIG in BooksOnLine for the sample near the
bottom. This will rebuild the indexes that actually require it given
certain percentage of fragmentation.
Andrew J. Kelly SQL MVP
"helpplease" <helpplease@.discussions.microsoft.com> wrote in message
news:7DB5F397-1671-4F97-9585-AC7C20382FA1@.microsoft.com...
> Hi,
> I would like to setup a maintenance job to 'Rebuild Indexes' as my
> 'Optimization Job' keeps failing on "DBCC failed because the following set
> options have incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
> What command do I need to use to rebuild all the indexes on all the
> tables.
> I see DBCC DBREINDEX but that needs table name as a parameter. Any kind
> of
> help is greatly appreciated.
> Thank you

QUOTED_IDENTIFIER and ARITHABORT for Maintenance plans

Hi,
I would like to setup a maintenance job to 'Rebuild Indexes' as my
'Optimization Job' keeps failing on "DBCC failed because the following set
options have incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
What command do I need to use to rebuild all the indexes on all the tables.
I see DBCC DBREINDEX but that needs table name as a parameter. Any kind of
help is greatly appreciated.
Thank youTake a look at DBCC SHOWCONTIG in BooksOnLine for the sample near the
bottom. This will rebuild the indexes that actually require it given
certain percentage of fragmentation.
--
Andrew J. Kelly SQL MVP
"helpplease" <helpplease@.discussions.microsoft.com> wrote in message
news:7DB5F397-1671-4F97-9585-AC7C20382FA1@.microsoft.com...
> Hi,
> I would like to setup a maintenance job to 'Rebuild Indexes' as my
> 'Optimization Job' keeps failing on "DBCC failed because the following set
> options have incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
> What command do I need to use to rebuild all the indexes on all the
> tables.
> I see DBCC DBREINDEX but that needs table name as a parameter. Any kind
> of
> help is greatly appreciated.
> Thank you

quicker way to create indexes

Hi,

I have a new job. It needs to drop and re-create (by insert) a table
every night. The table contains approximately 3,000,000 (and growing)
records. The insert is fine, runs in 2 minutes. The problem is that
when I create the indexes on the table, it is taking 15-20 minutes.
There is one clustered index and 11 non-clustered. This is a lookup
table that takes many different paremeters, so it really needs the
indexes for the user interface to run efficiently. However, the
database owners aren't keen on a job taking 20 minutes to run every
night.

Any ideas?shelleybobelly (shelleybobelly@.yahoo.com) writes:

Quote:

Originally Posted by

I have a new job. It needs to drop and re-create (by insert) a table
every night. The table contains approximately 3,000,000 (and growing)
records. The insert is fine, runs in 2 minutes. The problem is that
when I create the indexes on the table, it is taking 15-20 minutes.
There is one clustered index and 11 non-clustered. This is a lookup
table that takes many different paremeters, so it really needs the
indexes for the user interface to run efficiently. However, the
database owners aren't keen on a job taking 20 minutes to run every
night.

Without knowing much about the data, it's difficult to tell. But I find it
difficult to believe that data changes that much in a lookup table. Then
again, I would not expect a look-up table to have three million rows.
Anyway, rather than dropping and recreating, maybe it's more effective
to load into staging table, and then update changed rows, insert new
ones, and delete old ones.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Right, I agree. If I run it the other way (just update the big lookup
table from the separate tables where there were changes), it runs much
quicker, only deleting/adding a thousand or so records a day. The
owners of this DB are doing things differently, but I guess I'll just
have to 'educate' them on this one. Then I'll create another job to
re-index this table about once a week or so to keep it from
fragmenting.

BTW, this is for a Legal department that has to look up files that can
be up to 20 years old, but they don't have all the information. That's
why the 3 million records in a lookup table. It is a subset of a HUGE
archive table that contains 255 columns, too.

Thanks Erland, keep up your postings.

Erland Sommarskog wrote:

Quote:

Originally Posted by

shelleybobelly (shelleybobelly@.yahoo.com) writes:

Quote:

Originally Posted by

I have a new job. It needs to drop and re-create (by insert) a table
every night. The table contains approximately 3,000,000 (and growing)
records. The insert is fine, runs in 2 minutes. The problem is that
when I create the indexes on the table, it is taking 15-20 minutes.
There is one clustered index and 11 non-clustered. This is a lookup
table that takes many different paremeters, so it really needs the
indexes for the user interface to run efficiently. However, the
database owners aren't keen on a job taking 20 minutes to run every
night.

>
Without knowing much about the data, it's difficult to tell. But I find it
difficult to believe that data changes that much in a lookup table. Then
again, I would not expect a look-up table to have three million rows.
Anyway, rather than dropping and recreating, maybe it's more effective
to load into staging table, and then update changed rows, insert new
ones, and delete old ones.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||Not starting over every night would certainly be the best approach,
but anyway...

One thing you might try is to leave the clustered index on the table,
and order the data by the clustering key before loading. It is
possible that the longer time to load with the index in place will be
more than offset by the time saved in creating the clustered index.

Roy Harvey
Beacon Falls, CT

On 18 Jul 2006 13:58:55 -0700, "shelleybobelly"
<shelleybobelly@.yahoo.comwrote:

Quote:

Originally Posted by

>Hi,
>
>I have a new job. It needs to drop and re-create (by insert) a table
>every night. The table contains approximately 3,000,000 (and growing)
>records. The insert is fine, runs in 2 minutes. The problem is that
>when I create the indexes on the table, it is taking 15-20 minutes.
>There is one clustered index and 11 non-clustered. This is a lookup
>table that takes many different paremeters, so it really needs the
>indexes for the user interface to run efficiently. However, the
>database owners aren't keen on a job taking 20 minutes to run every
>night.
>
>Any ideas?

|||"shelleybobelly" <shelleybobelly@.yahoo.comwrote in message
news:1153260795.628994.213350@.h48g2000cwc.googlegr oups.com...

Quote:

Originally Posted by

Right, I agree. If I run it the other way (just update the big lookup
table from the separate tables where there were changes), it runs much
quicker, only deleting/adding a thousand or so records a day. The
owners of this DB are doing things differently, but I guess I'll just
have to 'educate' them on this one. Then I'll create another job to
re-index this table about once a week or so to keep it from
fragmenting.
>
BTW, this is for a Legal department that has to look up files that can
be up to 20 years old, but they don't have all the information. That's
why the 3 million records in a lookup table. It is a subset of a HUGE
archive table that contains 255 columns, too.
>

To suggestions:

1) move the indexes to an NDF file on a separate set of physical disks.

This may help if you're disk I/O bound at all.

2) May want to consider using full-text indexing for some of this.

Quote:

Originally Posted by

Thanks Erland, keep up your postings.
>
>
Erland Sommarskog wrote:

Quote:

Originally Posted by

shelleybobelly (shelleybobelly@.yahoo.com) writes:

Quote:

Originally Posted by

I have a new job. It needs to drop and re-create (by insert) a table
every night. The table contains approximately 3,000,000 (and growing)
records. The insert is fine, runs in 2 minutes. The problem is that
when I create the indexes on the table, it is taking 15-20 minutes.
There is one clustered index and 11 non-clustered. This is a lookup
table that takes many different paremeters, so it really needs the
indexes for the user interface to run efficiently. However, the
database owners aren't keen on a job taking 20 minutes to run every
night.

Without knowing much about the data, it's difficult to tell. But I find

it

Quote:

Originally Posted by

Quote:

Originally Posted by

difficult to believe that data changes that much in a lookup table. Then
again, I would not expect a look-up table to have three million rows.
Anyway, rather than dropping and recreating, maybe it's more effective
to load into staging table, and then update changed rows, insert new
ones, and delete old ones.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/pr...oads/books.mspx

Quote:

Originally Posted by

Quote:

Originally Posted by

Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

>

|||On 18 Jul 2006 13:58:55 -0700, "shelleybobelly"
<shelleybobelly@.yahoo.comwrote:

Quote:

Originally Posted by

>the database owners aren't keen on a job taking 20 minutes to run
>every night.

Is it automated? Are there users doing regular work at night? If
the answers are "yes" and "no" respectively, then why do they care
how long it runs?

Quick question about Server copying

Ok guys, it's my first day on a job, I need a quick answer, and I'm not a
full time DBA. Refresh my memory.
I have a SQL Server 2000 (SP3) server with multiple databases of uncertain
size (probably a few gig at least.) I need to duplicate this entire server
(all databases, users, permissions, everything) to a second server on a
nightly basis. Any changes to the second servers's data are irrelevant.
They are to be wiped out and replaced by the first.
What's the fastest way to do this? (At the moment, everyone else in the
office is leaning towards a backup-restore solution. Anything better that I
might be forgettting?)
Normally I'd be suggesting log-shipping, but in your case you want to take a
copy of the master database as well, which can't be log-shipped. So, you
could use backup and restore for the entire databases, assuming the second
server can maintain the same name. Coordination of the backups might become
crucial here if the databases are related eg logical FKs across databases.
This will also apply to replication where the coordination of distribution
backup and publisher backup will be required. Also, you'll need to consider
peripheral areas eg FTIs which will need rebuilding (taken care of in SQL
2005). You might be better off looking at a server-mirroring solution eg
DoubleTake for your requirements.
Rgds,
Paul Ibison
"B. Chernick" wrote:

> Ok guys, it's my first day on a job, I need a quick answer, and I'm not a
> full time DBA. Refresh my memory.
> I have a SQL Server 2000 (SP3) server with multiple databases of uncertain
> size (probably a few gig at least.) I need to duplicate this entire server
> (all databases, users, permissions, everything) to a second server on a
> nightly basis. Any changes to the second servers's data are irrelevant.
> They are to be wiped out and replaced by the first.
> What's the fastest way to do this? (At the moment, everyone else in the
> office is leaning towards a backup-restore solution. Anything better that I
> might be forgettting?)
|||On Apr 16, 2:58 pm, B. Chernick <BChern...@.discussions.microsoft.com>
wrote:
> Ok guys, it's my first day on a job, I need a quick answer, and I'm not a
> full time DBA. Refresh my memory.
> I have a SQL Server 2000 (SP3) server with multiple databases of uncertain
> size (probably a few gig at least.) I need to duplicate this entire server
> (all databases, users, permissions, everything) to a second server on a
> nightly basis. Any changes to the second servers's data are irrelevant.
> They are to be wiped out and replaced by the first.
> What's the fastest way to do this? (At the moment, everyone else in the
> office is leaning towards a backup-restore solution. Anything better that I
> might be forgettting?)
This solution will keep a realtime copy of all your databases as well
as Master, System, etc. You can also schedule it to just do periodic
snapshots, so that should meet your needs for a nightly copy on a
secondary server.
http://www.steeleye.com/pdf/literature/lifekeeper_for_sql_server.pdf
David A. Bermingham, MCSE, MCSA:Messaging
Director of Product Management
www.steeleye.com
|||Thanks. Unfortunately, my boss has told me in no uncertain terms that this
project does not warrant the expense of 3rd party software so we're going the
batch file route (which I've mostly gotten working.)
"daveberm" wrote:

> On Apr 16, 2:58 pm, B. Chernick <BChern...@.discussions.microsoft.com>
> wrote:
> This solution will keep a realtime copy of all your databases as well
> as Master, System, etc. You can also schedule it to just do periodic
> snapshots, so that should meet your needs for a nightly copy on a
> secondary server.
> http://www.steeleye.com/pdf/literature/lifekeeper_for_sql_server.pdf
> David A. Bermingham, MCSE, MCSA:Messaging
> Director of Product Management
> www.steeleye.com
>
>

Queuing Files

I have a requirement of instantiating a job which will copy and move the
newly created file once a file has been created in a folder. How can I
perform this in SQL Server. Can you please give your thoughts about this.
What I was planning was to write a visual basic code which will pool the
files getting created into the folder and accordingly copy and paste into
another folder. I feel this is not a better method.
I really wanted a queuing system to be implemented.Hi, there's a script I am using to copy files:
USE [msdb]
GO
/****** Object: Job [CopyToCluster] Script Date: 03/20/2006 15:24:31 ******/
BEGIN TRANSACTION
DECLARE @.ReturnCode INT
SELECT @.ReturnCode = 0
/****** Object: JobCategory [Database Maintenance] Script Date: 03/20/2006
15:24:31 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database
Maintenance' AND category_class=1)
BEGIN
EXEC @.ReturnCode = msdb.dbo.sp_add_category @.class=N'JOB', @.type=N'LOCAL',
@.name=N'Database Maintenance'
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @.jobId BINARY(16)
EXEC @.ReturnCode = msdb.dbo.sp_add_job @.job_name=N'CopyToCluster',
@.enabled=1,
@.notify_level_eventlog=2,
@.notify_level_email=0,
@.notify_level_netsend=0,
@.notify_level_page=0,
@.delete_level=0,
@.description=N'Copies all files rom the local backup directory to
\\datacluster\\Backup',
@.category_name=N'Database Maintenance',
@.owner_login_name=N'YOUR_USER', @.job_id = @.jobId OUTPUT
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Copy files] Script Date: 03/20/2006 15:24:31 ******/
EXEC @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id=@.jobId, @.step_name=N'Copy
files',
@.step_id=1,
@.cmdexec_success_code=0,
@.on_success_action=1,
@.on_success_step_id=0,
@.on_fail_action=2,
@.on_fail_step_id=0,
@.retry_attempts=2,
@.retry_interval=0,
@.os_run_priority=0, @.subsystem=N'CmdExec',
@.command=N'c:\copybackups.cmd $(DATE)$(TIME)',
@.flags=4
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXEC @.ReturnCode = msdb.dbo.sp_update_job @.job_id = @.jobId, @.start_step_id =
1
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXEC @.ReturnCode = msdb.dbo.sp_add_jobschedule @.job_id=@.jobId,
@.name=N'CopyBackupFilesSched',
@.enabled=1,
@.freq_type=4,
@.freq_interval=1,
@.freq_subday_type=1,
@.freq_subday_interval=0,
@.freq_relative_interval=0,
@.freq_recurrence_factor=0,
@.active_start_date=20060106,
@.active_end_date=99991231,
@.active_start_time=50000,
@.active_end_time=235959
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXEC @.ReturnCode = msdb.dbo.sp_add_jobserver @.job_id = @.jobId, @.server_name
= N'(local)'
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@.@.TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
and this is copypackups.cmd:
rem PR 2006-01-09
rem This batch file copies daily backups of databases to network location
rem
@.echo . > c:\copybackups.log
@.echo %1 Copying files from c:\SQLBackup to \\datacluster\Backup... >>
c:\copybackups.log
xcopy c:\SQLBackup\*.* \\datacluster\Backup\*.* /L /R /H /D /V /Y /F /C >>
c:\copybackups.log
xcopy c:\SQLBackup\*.* \\datacluster\Backup\*.* /R /H /D /V /Y /F /C >>
c:\copybackups.log
@.echo done >> c:\copybackups.log
@.echo . >> c:\copybackups.log
Note that this file does not use date and time passed by the job. I have
another job that removes older files from local directory.
HTH
Peter|||Thank you very much for your response.
What I need is watch for newly added files to a folder. The moment a new
file is added, I need to copy and paste into another location. This is
something like a service which keeps on monitoring a folder for new files
coming in.
Finally once the day is fininshed I will have a copy of the master folder in
another location also. I need to do this on receipt of each file, not finall
y
at the end of a day.
Thanks & Regards,
VB Babunath
"Rogas69" wrote:

> Hi, there's a script I am using to copy files:
> USE [msdb]
> GO
> /****** Object: Job [CopyToCluster] Script Date: 03/20/2006 15:24:31 ******/
> BEGIN TRANSACTION
> DECLARE @.ReturnCode INT
> SELECT @.ReturnCode = 0
> /****** Object: JobCategory [Database Maintenance] Script Date: 03/20/2006
> 15:24:31 ******/
> IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Databa
se
> Maintenance' AND category_class=1)
> BEGIN
> EXEC @.ReturnCode = msdb.dbo.sp_add_category @.class=N'JOB', @.type=N'LOCAL',
> @.name=N'Database Maintenance'
> IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
> END
> DECLARE @.jobId BINARY(16)
> EXEC @.ReturnCode = msdb.dbo.sp_add_job @.job_name=N'CopyToCluster',
> @.enabled=1,
> @.notify_level_eventlog=2,
> @.notify_level_email=0,
> @.notify_level_netsend=0,
> @.notify_level_page=0,
> @.delete_level=0,
> @.description=N'Copies all files rom the local backup directory to
> \\datacluster\\Backup',
> @.category_name=N'Database Maintenance',
> @.owner_login_name=N'YOUR_USER', @.job_id = @.jobId OUTPUT
> IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
> /****** Object: Step [Copy files] Script Date: 03/20/2006 15:24:31 ******/
> EXEC @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id=@.jobId, @.step_name=N'Co
py
> files',
> @.step_id=1,
> @.cmdexec_success_code=0,
> @.on_success_action=1,
> @.on_success_step_id=0,
> @.on_fail_action=2,
> @.on_fail_step_id=0,
> @.retry_attempts=2,
> @.retry_interval=0,
> @.os_run_priority=0, @.subsystem=N'CmdExec',
> @.command=N'c:\copybackups.cmd $(DATE)$(TIME)',
> @.flags=4
> IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
> EXEC @.ReturnCode = msdb.dbo.sp_update_job @.job_id = @.jobId, @.start_step_id
=
> 1
> IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
> EXEC @.ReturnCode = msdb.dbo.sp_add_jobschedule @.job_id=@.jobId,
> @.name=N'CopyBackupFilesSched',
> @.enabled=1,
> @.freq_type=4,
> @.freq_interval=1,
> @.freq_subday_type=1,
> @.freq_subday_interval=0,
> @.freq_relative_interval=0,
> @.freq_recurrence_factor=0,
> @.active_start_date=20060106,
> @.active_end_date=99991231,
> @.active_start_time=50000,
> @.active_end_time=235959
> IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
> EXEC @.ReturnCode = msdb.dbo.sp_add_jobserver @.job_id = @.jobId, @.server_nam
e
> = N'(local)'
> IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
> COMMIT TRANSACTION
> GOTO EndSave
> QuitWithRollback:
> IF (@.@.TRANCOUNT > 0) ROLLBACK TRANSACTION
> EndSave:
>
> and this is copypackups.cmd:
> rem PR 2006-01-09
> rem This batch file copies daily backups of databases to network location
> rem
> @.echo . > c:\copybackups.log
> @.echo %1 Copying files from c:\SQLBackup to \\datacluster\Backup... >>
> c:\copybackups.log
> xcopy c:\SQLBackup\*.* \\datacluster\Backup\*.* /L /R /H /D /V /Y /F /C >>
> c:\copybackups.log
> xcopy c:\SQLBackup\*.* \\datacluster\Backup\*.* /R /H /D /V /Y /F /C >>
> c:\copybackups.log
> @.echo done >> c:\copybackups.log
> @.echo . >> c:\copybackups.log
>
> Note that this file does not use date and time passed by the job. I have
> another job that removes older files from local directory.
> HTH
> Peter
>
>