Please help me on Script

we are using a data loading tool that periodically changes a database option for several databases in several of the database instances. One of these options is the database Auto-Close option. When this option is enabled, the database will close and generate a very large error log. We already using stored procedure script to monitor all the activity happening. I need to track all the activity happens after this Auto-Close option enables, for this i will have to add some script in my script which monitors daily activity.I am new to Scripts. Please help me, what to write in script.

Regards,
SumitWhy you're using AUTO_CLOSE option on that database?
In order to monitor the activity you can schedule server side trace and refer to http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm link fyi.|||Why you're using AUTO_CLOSE option on that database?
In order to monitor the activity you can schedule server side trace and refer to http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm link fyi.

Hi,
Thanks for your responce, I donot have option to use Auto_close option or not, I just have to find out how can I add this option to our daily report email script. There are lots of changes occur when this option enable mainly it increase the log file size that i need to include my mail report, I am not very much aware of the scrips. pls find some way so that i can add it in scripts running.

Regards,
sumit

Please can someone recommend?

Hi,

I was wondering if anyone could recommend some freeware code or a 3rd Party tool for identifying and logging SQL Server blocking and its cause.

Mny thanks.

Col.

Performance Monitor, SQL Profiler, and Management Studio all report blocking information and they're free with SQL Server.

Microsoft has a KB article that describes how blocking can be monitored in SQL Server 2005 and 2000.
http://support.microsoft.com/kb/271509

You may also want to check out Spotlight on SQL Server by Quest.
http://www.quest.com/spotlight-on-sql-server-enterprise/

I think this Idera product does too, but I've never used it.
http://www.idera.com/Products/SQLdm/Features.aspx

HTH...

Joe|||

Thanks Joe, I have been loocking at the quest stuff. It would appear that PA may be better suited for historical blocking analysis,

http://www.quest.com/Quest-Performance-Analysis-for-SQL-Server/

|||I believe they have a trial version available for download. If so, do that and play around around with it to see if it's providing the information you need. Typically someone from the company will contact you after the download and you can ask them all kinds of questions about it.

HTH...

Joe

playback tool

sql2k
Is there a tool that I can:
Box1 and Box2 start with exactly the same data.
Collect all activity for a day on Box1. Inserts/ Updates/ Deletes/ all
queries/ everything except maybe for jobs.
Run all of those activities on Box2. The goal being to make performance
adjustments and see if they do anything.
TIA, ChrisR
hI
SQL Profiler will capture database activity and you can script the output as
a SQL file. You can also use the data from profiler to feed into the Index
tuning wizard, therefore if you have a reasonable profileof the live system
you can see what it suggests.
John
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:B9839F43-5BC8-44DC-922D-D4A20EFD175C@.microsoft.com...
> sql2k
> Is there a tool that I can:
> Box1 and Box2 start with exactly the same data.
> Collect all activity for a day on Box1. Inserts/ Updates/ Deletes/ all
> queries/ everything except maybe for jobs.
> Run all of those activities on Box2. The goal being to make performance
> adjustments and see if they do anything.
> TIA, ChrisR
|||Yes,
Read the help file in the SQL Profiler. There are about 6 items that you
must log in order to do this.
Rick Sawtell
MCT, MCSD, MCDBA
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:B9839F43-5BC8-44DC-922D-D4A20EFD175C@.microsoft.com...
> sql2k
> Is there a tool that I can:
> Box1 and Box2 start with exactly the same data.
> Collect all activity for a day on Box1. Inserts/ Updates/ Deletes/ all
> queries/ everything except maybe for jobs.
> Run all of those activities on Box2. The goal being to make performance
> adjustments and see if they do anything.
> TIA, ChrisR
|||The help file is quite lengthy. Could you point me in the right direction?
What do I type in the Index?
"Rick Sawtell" wrote:

> Yes,
> Read the help file in the SQL Profiler. There are about 6 items that you
> must log in order to do this.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:B9839F43-5BC8-44DC-922D-D4A20EFD175C@.microsoft.com...
>
>
|||I'm able to trace to a trace file and replay. But its trying to replay in
Master. I dont see where to specify which db to replay in?
"John Bell" wrote:

> hI
> SQL Profiler will capture database activity and you can script the output as
> a SQL file. You can also use the data from profiler to feed into the Index
> tuning wizard, therefore if you have a reasonable profileof the live system
> you can see what it suggests.
> John
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:B9839F43-5BC8-44DC-922D-D4A20EFD175C@.microsoft.com...
>
>
|||Hi
The replay will use the default database of the account you logged in as,
therefore it looks like you are issuing a USE statement and have not
included it in the replay or you are using a different account?
John
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:564DF6FA-D4BE-48B6-83C2-D8831447607C@.microsoft.com...[vbcol=seagreen]
> I'm able to trace to a trace file and replay. But its trying to replay in
> Master. I dont see where to specify which db to replay in?
> "John Bell" wrote:
output as[vbcol=seagreen]
Index[vbcol=seagreen]
system[vbcol=seagreen]
performance[vbcol=seagreen]
|||As well as standard Profiler you might want to have a look at
Support Tools Available For Stress Testing & Performance Analysis
http://www.microsoft.com/downloads/d...DisplayLang=en
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:B9839F43-5BC8-44DC-922D-D4A20EFD175C@.microsoft.com...
> sql2k
> Is there a tool that I can:
> Box1 and Box2 start with exactly the same data.
> Collect all activity for a day on Box1. Inserts/ Updates/ Deletes/ all
> queries/ everything except maybe for jobs.
> Run all of those activities on Box2. The goal being to make performance
> adjustments and see if they do anything.
> TIA, ChrisR

playback tool

sql2k
Is there a tool that I can:
Box1 and Box2 start with exactly the same data.
Collect all activity for a day on Box1. Inserts/ Updates/ Deletes/ all
queries/ everything except maybe for jobs.
Run all of those activities on Box2. The goal being to make performance
adjustments and see if they do anything.
TIA, ChrisRhI
SQL Profiler will capture database activity and you can script the output as
a SQL file. You can also use the data from profiler to feed into the Index
tuning wizard, therefore if you have a reasonable profileof the live system
you can see what it suggests.
John
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:B9839F43-5BC8-44DC-922D-D4A20EFD175C@.microsoft.com...
> sql2k
> Is there a tool that I can:
> Box1 and Box2 start with exactly the same data.
> Collect all activity for a day on Box1. Inserts/ Updates/ Deletes/ all
> queries/ everything except maybe for jobs.
> Run all of those activities on Box2. The goal being to make performance
> adjustments and see if they do anything.
> TIA, ChrisR|||Yes,
Read the help file in the SQL Profiler. There are about 6 items that you
must log in order to do this.
Rick Sawtell
MCT, MCSD, MCDBA
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:B9839F43-5BC8-44DC-922D-D4A20EFD175C@.microsoft.com...
> sql2k
> Is there a tool that I can:
> Box1 and Box2 start with exactly the same data.
> Collect all activity for a day on Box1. Inserts/ Updates/ Deletes/ all
> queries/ everything except maybe for jobs.
> Run all of those activities on Box2. The goal being to make performance
> adjustments and see if they do anything.
> TIA, ChrisR|||The help file is quite lengthy. Could you point me in the right direction?
What do I type in the Index?
"Rick Sawtell" wrote:

> Yes,
> Read the help file in the SQL Profiler. There are about 6 items that you
> must log in order to do this.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:B9839F43-5BC8-44DC-922D-D4A20EFD175C@.microsoft.com...
>
>|||I'm able to trace to a trace file and replay. But its trying to replay in
Master. I dont see where to specify which db to replay in?
"John Bell" wrote:

> hI
> SQL Profiler will capture database activity and you can script the output
as
> a SQL file. You can also use the data from profiler to feed into the Index
> tuning wizard, therefore if you have a reasonable profileof the live syste
m
> you can see what it suggests.
> John
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:B9839F43-5BC8-44DC-922D-D4A20EFD175C@.microsoft.com...
>
>|||Hi
The replay will use the default database of the account you logged in as,
therefore it looks like you are issuing a USE statement and have not
included it in the replay or you are using a different account?
John
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:564DF6FA-D4BE-48B6-83C2-D8831447607C@.microsoft.com...[vbcol=seagreen]
> I'm able to trace to a trace file and replay. But its trying to replay in
> Master. I dont see where to specify which db to replay in?
> "John Bell" wrote:
>
output as[vbcol=seagreen]
Index[vbcol=seagreen]
system[vbcol=seagreen]
performance[vbcol=seagreen]|||As well as standard Profiler you might want to have a look at
Support Tools Available For Stress Testing & Performance Analysis
http://www.microsoft.com/downloads/...&DisplayLang=en
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:B9839F43-5BC8-44DC-922D-D4A20EFD175C@.microsoft.com...
> sql2k
> Is there a tool that I can:
> Box1 and Box2 start with exactly the same data.
> Collect all activity for a day on Box1. Inserts/ Updates/ Deletes/ all
> queries/ everything except maybe for jobs.
> Run all of those activities on Box2. The goal being to make performance
> adjustments and see if they do anything.
> TIA, ChrisR

playback tool

sql2k
Is there a tool that I can:
Box1 and Box2 start with exactly the same data.
Collect all activity for a day on Box1. Inserts/ Updates/ Deletes/ all
queries/ everything except maybe for jobs.
Run all of those activities on Box2. The goal being to make performance
adjustments and see if they do anything.
TIA, ChrisRhI
SQL Profiler will capture database activity and you can script the output as
a SQL file. You can also use the data from profiler to feed into the Index
tuning wizard, therefore if you have a reasonable profileof the live system
you can see what it suggests.
John
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:B9839F43-5BC8-44DC-922D-D4A20EFD175C@.microsoft.com...
> sql2k
> Is there a tool that I can:
> Box1 and Box2 start with exactly the same data.
> Collect all activity for a day on Box1. Inserts/ Updates/ Deletes/ all
> queries/ everything except maybe for jobs.
> Run all of those activities on Box2. The goal being to make performance
> adjustments and see if they do anything.
> TIA, ChrisR|||Yes,
Read the help file in the SQL Profiler. There are about 6 items that you
must log in order to do this.
Rick Sawtell
MCT, MCSD, MCDBA
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:B9839F43-5BC8-44DC-922D-D4A20EFD175C@.microsoft.com...
> sql2k
> Is there a tool that I can:
> Box1 and Box2 start with exactly the same data.
> Collect all activity for a day on Box1. Inserts/ Updates/ Deletes/ all
> queries/ everything except maybe for jobs.
> Run all of those activities on Box2. The goal being to make performance
> adjustments and see if they do anything.
> TIA, ChrisR|||The help file is quite lengthy. Could you point me in the right direction?
What do I type in the Index?
"Rick Sawtell" wrote:
> Yes,
> Read the help file in the SQL Profiler. There are about 6 items that you
> must log in order to do this.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:B9839F43-5BC8-44DC-922D-D4A20EFD175C@.microsoft.com...
> > sql2k
> >
> > Is there a tool that I can:
> >
> > Box1 and Box2 start with exactly the same data.
> >
> > Collect all activity for a day on Box1. Inserts/ Updates/ Deletes/ all
> > queries/ everything except maybe for jobs.
> >
> > Run all of those activities on Box2. The goal being to make performance
> > adjustments and see if they do anything.
> >
> > TIA, ChrisR
>
>|||I'm able to trace to a trace file and replay. But its trying to replay in
Master. I dont see where to specify which db to replay in?
"John Bell" wrote:
> hI
> SQL Profiler will capture database activity and you can script the output as
> a SQL file. You can also use the data from profiler to feed into the Index
> tuning wizard, therefore if you have a reasonable profileof the live system
> you can see what it suggests.
> John
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:B9839F43-5BC8-44DC-922D-D4A20EFD175C@.microsoft.com...
> > sql2k
> >
> > Is there a tool that I can:
> >
> > Box1 and Box2 start with exactly the same data.
> >
> > Collect all activity for a day on Box1. Inserts/ Updates/ Deletes/ all
> > queries/ everything except maybe for jobs.
> >
> > Run all of those activities on Box2. The goal being to make performance
> > adjustments and see if they do anything.
> >
> > TIA, ChrisR
>
>|||Hi
The replay will use the default database of the account you logged in as,
therefore it looks like you are issuing a USE statement and have not
included it in the replay or you are using a different account?
John
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:564DF6FA-D4BE-48B6-83C2-D8831447607C@.microsoft.com...
> I'm able to trace to a trace file and replay. But its trying to replay in
> Master. I dont see where to specify which db to replay in?
> "John Bell" wrote:
> > hI
> >
> > SQL Profiler will capture database activity and you can script the
output as
> > a SQL file. You can also use the data from profiler to feed into the
Index
> > tuning wizard, therefore if you have a reasonable profileof the live
system
> > you can see what it suggests.
> >
> > John
> >
> > "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> > news:B9839F43-5BC8-44DC-922D-D4A20EFD175C@.microsoft.com...
> > > sql2k
> > >
> > > Is there a tool that I can:
> > >
> > > Box1 and Box2 start with exactly the same data.
> > >
> > > Collect all activity for a day on Box1. Inserts/ Updates/ Deletes/ all
> > > queries/ everything except maybe for jobs.
> > >
> > > Run all of those activities on Box2. The goal being to make
performance
> > > adjustments and see if they do anything.
> > >
> > > TIA, ChrisR
> >
> >
> >|||As well as standard Profiler you might want to have a look at
Support Tools Available For Stress Testing & Performance Analysis
http://www.microsoft.com/downloads/details.aspx?FamilyID=5691ab53-893a-4aaf-b4a6-9a8bb9669a8b&DisplayLang=en
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:B9839F43-5BC8-44DC-922D-D4A20EFD175C@.microsoft.com...
> sql2k
> Is there a tool that I can:
> Box1 and Box2 start with exactly the same data.
> Collect all activity for a day on Box1. Inserts/ Updates/ Deletes/ all
> queries/ everything except maybe for jobs.
> Run all of those activities on Box2. The goal being to make performance
> adjustments and see if they do anything.
> TIA, ChrisR

PL/SQL to TSQL

I need a tool to convert (at least 50%) my source code of 200+ stored procedures from PL/SQL to TSQL.
Could any one help me?
ThanksHi Carlos,

Check out our website http://www.dbbest.com