Showing posts with label job. Show all posts
Showing posts with label job. Show all posts

Tuesday, March 27, 2012

Auto Create Stats & Auto Update stats

I've inherited a server where auto create stats and auto update stats have b
een turned off. There is a weekly job which executes Update Statistics on e
ach table with the norecompute clause. In looking at traces I'm seeing quit
e a few missing statistics
entries.
When is it appropriate to turn off auto create stats?Peter,
as I understand it, if you've just created a table and are loading it with
loads of rows, you might disable the creation of statistics because the
performance overhead is more than you can afford. Likewise if the table
already exists with statistics and requires loading, you might run
sp_autostats or the UPDATE STATISTICS command with the WITH NORECOMPUTE
option. However, this is only a temporary measure and once loaded it is
normal to have it auto updating, or else incorrect query plans may result.
Regards,
Paul Ibison

Auto Create Stats & Auto Update stats

I've inherited a server where auto create stats and auto update stats have been turned off. There is a weekly job which executes Update Statistics on each table with the norecompute clause. In looking at traces I'm seeing quite a few missing statistics
entries.
When is it appropriate to turn off auto create stats?
Peter,
as I understand it, if you've just created a table and are loading it with
loads of rows, you might disable the creation of statistics because the
performance overhead is more than you can afford. Likewise if the table
already exists with statistics and requires loading, you might run
sp_autostats or the UPDATE STATISTICS command with the WITH NORECOMPUTE
option. However, this is only a temporary measure and once loaded it is
normal to have it auto updating, or else incorrect query plans may result.
Regards,
Paul Ibison

Wednesday, March 7, 2012

Audit in SQL Server

Hi,
I need to setup a job in SQL Server for auditing all task made by the
sysadmin Group all time
I have tried to use the system stored procedures: sp_trace_create,
sp_trace_setevent, sp_trace_setstatus but I do not see the job's result in
any file or table.
I used this stored procedures in this way:
declare @.trace_id int
declare @.intOn bit
set @.intOn=1
exec sp_trace_create @.traceid = @.trace_id OUTPUT, @.options=3,
@.tracefile=N'c:\Audit', @.maxfilesize = Null, @.stoptime = Null
select @.trace_id
exec sp_trace_setevent @.traceid = @.trace_id, @.eventid = 117, @.columnid=1,
@.on=@.intOn
exec sp_trace_setstatus @.traceid = @.trace_id, @.status = 1
How can I make this task
Help me please
I know the SQL Profiler but I don't know the way to programming an automatic
task to run all time.
Thank so muchYou may want to look at a third party product such as Lumigent.
"Auditing SQL Server" wrote:

> Hi,
> I need to setup a job in SQL Server for auditing all task made by the
> sysadmin Group all time
> I have tried to use the system stored procedures: sp_trace_create,
> sp_trace_setevent, sp_trace_setstatus but I do not see the job's result in
> any file or table.
> I used this stored procedures in this way:
> declare @.trace_id int
> declare @.intOn bit
> set @.intOn=1
> exec sp_trace_create @.traceid = @.trace_id OUTPUT, @.options=3,
> @.tracefile=N'c:\Audit', @.maxfilesize = Null, @.stoptime = Null
> select @.trace_id
> exec sp_trace_setevent @.traceid = @.trace_id, @.eventid = 117, @.columnid=1,
> @.on=@.intOn
> exec sp_trace_setstatus @.traceid = @.trace_id, @.status = 1
> How can I make this task
> Help me please
> I know the SQL Profiler but I don't know the way to programming an automat
ic
> task to run all time.
> Thank so much|||Auditing SQL Server wrote:
> Hi,
> I need to setup a job in SQL Server for auditing all task made by the
> sysadmin Group all time
> I have tried to use the system stored procedures: sp_trace_create,
> sp_trace_setevent, sp_trace_setstatus but I do not see the job's
> result in any file or table.
>
The best way to see the code used to create a trace is to create the
exact trace you want in Profiler (including the server manages trace
option). Make sure yo include only the events you want and the columns
you require. Set any necessary filter conditions.
Then use the File - Script Trace menu option to generate the T-SQL for
the trace.
You'll need to manually turn it into a job and also manage the stopping
of the trace (which is not scripted). Also, consider placing the trace
file somewhere else besides to root folder of the boot drive. Place it
on a disk with sufficient available space (like the log file/temdb
drive). You cannot view the collected trace data in the file until you
stop the trace, so you might want to have the job start a new trace
using an incrementing file ID (or use a the new_id() function for the
name) and then stop the old trace. Then you can view the trace data
using fn_trace_gettable or using Profiler (SP4 Profiler will require a
soon-to-be released request-only hotfix to view the data in the file).
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Your problem seems to be @.options = 3. That is not documented. I tried with
2 and I got a proper
trace id back. Why don't you define the trace in Profiler and script that de
finition?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Auditing SQL Server" <Auditing SQL Server@.discussions.microsoft.com> wrote
in message
news:84B4D6A4-045D-4442-ACB0-0332B84AEB63@.microsoft.com...
> Hi,
> I need to setup a job in SQL Server for auditing all task made by the
> sysadmin Group all time
> I have tried to use the system stored procedures: sp_trace_create,
> sp_trace_setevent, sp_trace_setstatus but I do not see the job's result in
> any file or table.
> I used this stored procedures in this way:
> declare @.trace_id int
> declare @.intOn bit
> set @.intOn=1
> exec sp_trace_create @.traceid = @.trace_id OUTPUT, @.options=3,
> @.tracefile=N'c:\Audit', @.maxfilesize = Null, @.stoptime = Null
> select @.trace_id
> exec sp_trace_setevent @.traceid = @.trace_id, @.eventid = 117, @.columnid=1,
> @.on=@.intOn
> exec sp_trace_setstatus @.traceid = @.trace_id, @.status = 1
> How can I make this task
> Help me please
> I know the SQL Profiler but I don't know the way to programming an automat
ic
> task to run all time.
> Thank so much

Audit in SQL Server

Hi,
I need to setup a job in SQL Server for auditing all task made by the
sysadmin Group all time
I have tried to use the system stored procedures: sp_trace_create,
sp_trace_setevent, sp_trace_setstatus but I do not see the job's result in
any file or table.
I used this stored procedures in this way:
declare @.trace_id int
declare @.intOn bit
set @.intOn=1
exec sp_trace_create @.traceid = @.trace_id OUTPUT, @.options=3,
@.tracefile=N'c:\Audit', @.maxfilesize = Null, @.stoptime = Null
select @.trace_id
exec sp_trace_setevent @.traceid = @.trace_id, @.eventid = 117, @.columnid=1,
@.on=@.intOn
exec sp_trace_setstatus @.traceid = @.trace_id, @.status = 1
How can I make this task
Help me please
I know the SQL Profiler but I don't know the way to programming an automatic
task to run all time.
Thank so much
You may want to look at a third party product such as Lumigent.
"Auditing SQL Server" wrote:

> Hi,
> I need to setup a job in SQL Server for auditing all task made by the
> sysadmin Group all time
> I have tried to use the system stored procedures: sp_trace_create,
> sp_trace_setevent, sp_trace_setstatus but I do not see the job's result in
> any file or table.
> I used this stored procedures in this way:
> declare @.trace_id int
> declare @.intOn bit
> set @.intOn=1
> exec sp_trace_create @.traceid = @.trace_id OUTPUT, @.options=3,
> @.tracefile=N'c:\Audit', @.maxfilesize = Null, @.stoptime = Null
> select @.trace_id
> exec sp_trace_setevent @.traceid = @.trace_id, @.eventid = 117, @.columnid=1,
> @.on=@.intOn
> exec sp_trace_setstatus @.traceid = @.trace_id, @.status = 1
> How can I make this task
> Help me please
> I know the SQL Profiler but I don't know the way to programming an automatic
> task to run all time.
> Thank so much
|||Auditing SQL Server wrote:
> Hi,
> I need to setup a job in SQL Server for auditing all task made by the
> sysadmin Group all time
> I have tried to use the system stored procedures: sp_trace_create,
> sp_trace_setevent, sp_trace_setstatus but I do not see the job's
> result in any file or table.
>
The best way to see the code used to create a trace is to create the
exact trace you want in Profiler (including the server manages trace
option). Make sure yo include only the events you want and the columns
you require. Set any necessary filter conditions.
Then use the File - Script Trace menu option to generate the T-SQL for
the trace.
You'll need to manually turn it into a job and also manage the stopping
of the trace (which is not scripted). Also, consider placing the trace
file somewhere else besides to root folder of the boot drive. Place it
on a disk with sufficient available space (like the log file/temdb
drive). You cannot view the collected trace data in the file until you
stop the trace, so you might want to have the job start a new trace
using an incrementing file ID (or use a the new_id() function for the
name) and then stop the old trace. Then you can view the trace data
using fn_trace_gettable or using Profiler (SP4 Profiler will require a
soon-to-be released request-only hotfix to view the data in the file).
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Your problem seems to be @.options = 3. That is not documented. I tried with 2 and I got a proper
trace id back. Why don't you define the trace in Profiler and script that definition?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Auditing SQL Server" <Auditing SQL Server@.discussions.microsoft.com> wrote in message
news:84B4D6A4-045D-4442-ACB0-0332B84AEB63@.microsoft.com...
> Hi,
> I need to setup a job in SQL Server for auditing all task made by the
> sysadmin Group all time
> I have tried to use the system stored procedures: sp_trace_create,
> sp_trace_setevent, sp_trace_setstatus but I do not see the job's result in
> any file or table.
> I used this stored procedures in this way:
> declare @.trace_id int
> declare @.intOn bit
> set @.intOn=1
> exec sp_trace_create @.traceid = @.trace_id OUTPUT, @.options=3,
> @.tracefile=N'c:\Audit', @.maxfilesize = Null, @.stoptime = Null
> select @.trace_id
> exec sp_trace_setevent @.traceid = @.trace_id, @.eventid = 117, @.columnid=1,
> @.on=@.intOn
> exec sp_trace_setstatus @.traceid = @.trace_id, @.status = 1
> How can I make this task
> Help me please
> I know the SQL Profiler but I don't know the way to programming an automatic
> task to run all time.
> Thank so much

Saturday, February 25, 2012

Audit in SQL Server

Hi,
I need to setup a job in SQL Server for auditing all task made by the
sysadmin Group all time
I have tried to use the system stored procedures: sp_trace_create,
sp_trace_setevent, sp_trace_setstatus but I do not see the job's result in
any file or table.
I used this stored procedures in this way:
declare @.trace_id int
declare @.intOn bit
set @.intOn=1
exec sp_trace_create @.traceid = @.trace_id OUTPUT, @.options=3,
@.tracefile=N'c:\Audit', @.maxfilesize = Null, @.stoptime = Null
select @.trace_id
exec sp_trace_setevent @.traceid = @.trace_id, @.eventid = 117, @.columnid=1,
@.on=@.intOn
exec sp_trace_setstatus @.traceid = @.trace_id, @.status = 1
How can I make this task
Help me please
I know the SQL Profiler but I don't know the way to programming an automatic
task to run all time.
Thank so muchYou may want to look at a third party product such as Lumigent.
"Auditing SQL Server" wrote:
> Hi,
> I need to setup a job in SQL Server for auditing all task made by the
> sysadmin Group all time
> I have tried to use the system stored procedures: sp_trace_create,
> sp_trace_setevent, sp_trace_setstatus but I do not see the job's result in
> any file or table.
> I used this stored procedures in this way:
> declare @.trace_id int
> declare @.intOn bit
> set @.intOn=1
> exec sp_trace_create @.traceid = @.trace_id OUTPUT, @.options=3,
> @.tracefile=N'c:\Audit', @.maxfilesize = Null, @.stoptime = Null
> select @.trace_id
> exec sp_trace_setevent @.traceid = @.trace_id, @.eventid = 117, @.columnid=1,
> @.on=@.intOn
> exec sp_trace_setstatus @.traceid = @.trace_id, @.status = 1
> How can I make this task
> Help me please
> I know the SQL Profiler but I don't know the way to programming an automatic
> task to run all time.
> Thank so much|||Auditing SQL Server wrote:
> Hi,
> I need to setup a job in SQL Server for auditing all task made by the
> sysadmin Group all time
> I have tried to use the system stored procedures: sp_trace_create,
> sp_trace_setevent, sp_trace_setstatus but I do not see the job's
> result in any file or table.
>
The best way to see the code used to create a trace is to create the
exact trace you want in Profiler (including the server manages trace
option). Make sure yo include only the events you want and the columns
you require. Set any necessary filter conditions.
Then use the File - Script Trace menu option to generate the T-SQL for
the trace.
You'll need to manually turn it into a job and also manage the stopping
of the trace (which is not scripted). Also, consider placing the trace
file somewhere else besides to root folder of the boot drive. Place it
on a disk with sufficient available space (like the log file/temdb
drive). You cannot view the collected trace data in the file until you
stop the trace, so you might want to have the job start a new trace
using an incrementing file ID (or use a the new_id() function for the
name) and then stop the old trace. Then you can view the trace data
using fn_trace_gettable or using Profiler (SP4 Profiler will require a
soon-to-be released request-only hotfix to view the data in the file).
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Your problem seems to be @.options = 3. That is not documented. I tried with 2 and I got a proper
trace id back. Why don't you define the trace in Profiler and script that definition?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Auditing SQL Server" <Auditing SQL Server@.discussions.microsoft.com> wrote in message
news:84B4D6A4-045D-4442-ACB0-0332B84AEB63@.microsoft.com...
> Hi,
> I need to setup a job in SQL Server for auditing all task made by the
> sysadmin Group all time
> I have tried to use the system stored procedures: sp_trace_create,
> sp_trace_setevent, sp_trace_setstatus but I do not see the job's result in
> any file or table.
> I used this stored procedures in this way:
> declare @.trace_id int
> declare @.intOn bit
> set @.intOn=1
> exec sp_trace_create @.traceid = @.trace_id OUTPUT, @.options=3,
> @.tracefile=N'c:\Audit', @.maxfilesize = Null, @.stoptime = Null
> select @.trace_id
> exec sp_trace_setevent @.traceid = @.trace_id, @.eventid = 117, @.columnid=1,
> @.on=@.intOn
> exec sp_trace_setstatus @.traceid = @.trace_id, @.status = 1
> How can I make this task
> Help me please
> I know the SQL Profiler but I don't know the way to programming an automatic
> task to run all time.
> Thank so much

Thursday, February 9, 2012

attach/deattach database

is it possible to create a dts job or sproc that can be launched via a vbs
file or wsh script that:
1. unattaches a sql 2000 database
2. copies it to a removable drive
3. re-attaches original db
4. and does opposite at office to keep a sql in sync?
any ideas on best method and syntax would be appreciated.Scott,
If youo are trying to have a remote database that is in sync, or at least
is in syn some what, is it possible to use replication? A snap shot
replication using the SQL Server as both the publisher and the distributor
can create a push a snapshot of the entire database, or selected tables from
the database. If the databases are not connected (I noted that you want to
use a removeable drive) then a process can be developed to copy the files to
a drive and then mount the databases on the remote server. Here is just one
method:
1. Create a job that backups up the database
2. Create a DTS package that will copy the backup files to the removable
drive.
3. Step 2 of the job would be to execute the DTS package in step 2.
4. Do the reverse on the remote server but in place of the backup create a
job that restores the database.
Detaching and reattaching can be an option as well as long are not
connections to the database when it is detached. A Backup can be done
regardless if the database is in use or not.
Hope this helps.
Rick
"scott" wrote:
> is it possible to create a dts job or sproc that can be launched via a vbs
> file or wsh script that:
> 1. unattaches a sql 2000 database
> 2. copies it to a removable drive
> 3. re-attaches original db
> 4. and does opposite at office to keep a sql in sync?
> any ideas on best method and syntax would be appreciated.
>
>