Procedure Time out: prod

Showing posts with label prod. Show all posts

Friday, March 23, 2012

Prod database counters

SQL Server 2000 SP4
How can I get "Transactions per second" and "write transactions percent"
values for a production database ?. I tried to find in the profiler but did
not see an event that would give me this info.
Thanks for any help.Those come from Perfmon not profiler.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:1456F10A-B293-48C1-9AFF-BB3FB5906F76@.microsoft.com...
> SQL Server 2000 SP4
> How can I get "Transactions per second" and "write transactions percent"
> values for a production database ?. I tried to find in the profiler but
> did
> not see an event that would give me this info.
> Thanks for any help.
>|||Thanks Andrew............ What do I need to choose in Performance
Monitoring ?
"Andrew J. Kelly" wrote:
> Those come from Perfmon not profiler.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:1456F10A-B293-48C1-9AFF-BB3FB5906F76@.microsoft.com...
> > SQL Server 2000 SP4
> >
> > How can I get "Transactions per second" and "write transactions percent"
> > values for a production database ?. I tried to find in the profiler but
> > did
> > not see an event that would give me this info.
> >
> > Thanks for any help.
> >
>|||Well the databases counters will have Transactions Per Second for each
database but I don't know what you mean by the other. If you want to see the
difference between requests and things that actually begin a transaction you
can monitor Batch Requests Per Second and Trans Per Second.
Some of these may be of interest:
http://www.sql-server-performance.com/sql_server_performance_audit10.asp
Performance Audit
http://www.microsoft.com/technet/prodtechnol/sql/2005/library/operations.mspx
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
http://sqldev.net/misc/WaitTypes.htm
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:8515ED62-75BF-4B44-AF03-6112174664A7@.microsoft.com...
> Thanks Andrew............ What do I need to choose in Performance
> Monitoring ?
>
> "Andrew J. Kelly" wrote:
>> Those come from Perfmon not profiler.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "DXC" <DXC@.discussions.microsoft.com> wrote in message
>> news:1456F10A-B293-48C1-9AFF-BB3FB5906F76@.microsoft.com...
>> > SQL Server 2000 SP4
>> >
>> > How can I get "Transactions per second" and "write transactions
>> > percent"
>> > values for a production database ?. I tried to find in the profiler but
>> > did
>> > not see an event that would give me this info.
>> >
>> > Thanks for any help.
>> >
>>|||Thanks.................
"Andrew J. Kelly" wrote:
> Well the databases counters will have Transactions Per Second for each
> database but I don't know what you mean by the other. If you want to see the
> difference between requests and things that actually begin a transaction you
> can monitor Batch Requests Per Second and Trans Per Second.
> Some of these may be of interest:
> http://www.sql-server-performance.com/sql_server_performance_audit10.asp
> Performance Audit
> http://www.microsoft.com/technet/prodtechnol/sql/2005/library/operations.mspx
> Performance WP's
> http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
> http://www.sql-server-performance.com/sql_server_performance_audit.asp
> Hardware Performance CheckList
> http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
> SQL 2000 Performance tuning tips
> http://www.support.microsoft.com/?id=224587 Troubleshooting App
> Performance
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
> Disk Monitoring
> http://sqldev.net/misc/WaitTypes.htm
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:8515ED62-75BF-4B44-AF03-6112174664A7@.microsoft.com...
> > Thanks Andrew............ What do I need to choose in Performance
> > Monitoring ?
> >
> >
> > "Andrew J. Kelly" wrote:
> >
> >> Those come from Perfmon not profiler.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >> Solid Quality Mentors
> >>
> >>
> >> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> >> news:1456F10A-B293-48C1-9AFF-BB3FB5906F76@.microsoft.com...
> >> > SQL Server 2000 SP4
> >> >
> >> > How can I get "Transactions per second" and "write transactions
> >> > percent"
> >> > values for a production database ?. I tried to find in the profiler but
> >> > did
> >> > not see an event that would give me this info.
> >> >
> >> > Thanks for any help.
> >> >
> >>
> >>
>

prod data to test server

I need to do some testing on our test servers but with production data. What
is the best way to get all of the production data into my test database? I'm
going from a SQL 2000 database in production to a SQL 2005 database in test.
Would the import data wizard work or a 'backup of the 2000 database and
restore to SQL 2005 database work?
Which would be the best way to do this?
MikeHi
On Jun 18, 3:41 pm, "Mike" <M...@.community.nospam.com> wrote:
> I need to do some testing on our test servers but with production data. What
> is the best way to get all of the production data into my test database? I'm
> going from a SQL 2000 database in production to a SQL 2005 database in test.
> Would the import data wizard work or a 'backup of the 2000 database and
> restore to SQL 2005 database work?
> Which would be the best way to do this?
> Mike
You can also use sp_detach_db/sp_attach_db as well see
http://support.microsoft.com/kb/314546, you would need to make sure
that you have the correct logins and the users are not orphaned. The
quickest methods tend to be backup/restore or sp_detach_db/
sp_attach_db. SQL 2000 database can be attached/restored to SQL 2005
but not vice versa.
Watch out for data protection issues!!
John|||I can't do that due to we need both SQl 2000 and SQL 05 running on the same
test box. None of the apps can go down, even in test. So I have both SQL
versions running on the same box in our test environment. Though once all of
the databases are tested that is the way I'm looking to convert the
production database server over to 05, the detach and attach method, but
first I need to test and get all of the prod data (data only) to my test
server.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:1182178695.604642.31080@.j4g2000prf.googlegroups.com...
> Hi
> On Jun 18, 3:41 pm, "Mike" <M...@.community.nospam.com> wrote:
>> I need to do some testing on our test servers but with production data.
>> What
>> is the best way to get all of the production data into my test database?
>> I'm
>> going from a SQL 2000 database in production to a SQL 2005 database in
>> test.
>> Would the import data wizard work or a 'backup of the 2000 database and
>> restore to SQL 2005 database work?
>> Which would be the best way to do this?
>> Mike
> You can also use sp_detach_db/sp_attach_db as well see
> http://support.microsoft.com/kb/314546, you would need to make sure
> that you have the correct logins and the users are not orphaned. The
> quickest methods tend to be backup/restore or sp_detach_db/
> sp_attach_db. SQL 2000 database can be attached/restored to SQL 2005
> but not vice versa.
> Watch out for data protection issues!!
> John
>|||Hi
On Jun 18, 4:05 pm, "Mike" <M...@.community.nospam.com> wrote:
> I can't do that due to we need both SQl 2000 and SQL 05 running on the same
> test box. None of the apps can go down, even in test. So I have both SQL
> versions running on the same box in our test environment. Though once all of
> the databases are tested that is the way I'm looking to convert the
> production database server over to 05, the detach and attach method, but
> first I need to test and get all of the prod data (data only) to my test
> server.
> "John Bell" <jbellnewspo...@.hotmail.com> wrote in message
> news:1182178695.604642.31080@.j4g2000prf.googlegroups.com...
>
> > Hi
> > On Jun 18, 3:41 pm, "Mike" <M...@.community.nospam.com> wrote:
> >> I need to do some testing on our test servers but with production data.
> >> What
> >> is the best way to get all of the production data into my test database?
> >> I'm
> >> going from a SQL 2000 database in production to a SQL 2005 database in
> >> test.
> >> Would the import data wizard work or a 'backup of the 2000 database and
> >> restore to SQL 2005 database work?
> >> Which would be the best way to do this?
> >> Mike
> > You can also use sp_detach_db/sp_attach_db as well see
> >http://support.microsoft.com/kb/314546, you would need to make sure
> > that you have the correct logins and the users are not orphaned. The
> > quickest methods tend to be backup/restore or sp_detach_db/
> > sp_attach_db. SQL 2000 database can be attached/restored to SQL 2005
> > but not vice versa.
> > Watch out for data protection issues!!
> > John- Hide quoted text -
> - Show quoted text -
But you can still use backup/restore which require no downtime of the
production system. The KB shows all three methods.
John|||I'm in the process of backup/restore method now.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:1182184181.666277.268590@.n15g2000prd.googlegroups.com...
> Hi
> On Jun 18, 4:05 pm, "Mike" <M...@.community.nospam.com> wrote:
>> I can't do that due to we need both SQl 2000 and SQL 05 running on the
>> same
>> test box. None of the apps can go down, even in test. So I have both SQL
>> versions running on the same box in our test environment. Though once all
>> of
>> the databases are tested that is the way I'm looking to convert the
>> production database server over to 05, the detach and attach method, but
>> first I need to test and get all of the prod data (data only) to my test
>> server.
>> "John Bell" <jbellnewspo...@.hotmail.com> wrote in message
>> news:1182178695.604642.31080@.j4g2000prf.googlegroups.com...
>>
>> > Hi
>> > On Jun 18, 3:41 pm, "Mike" <M...@.community.nospam.com> wrote:
>> >> I need to do some testing on our test servers but with production
>> >> data.
>> >> What
>> >> is the best way to get all of the production data into my test
>> >> database?
>> >> I'm
>> >> going from a SQL 2000 database in production to a SQL 2005 database in
>> >> test.
>> >> Would the import data wizard work or a 'backup of the 2000 database
>> >> and
>> >> restore to SQL 2005 database work?
>> >> Which would be the best way to do this?
>> >> Mike
>> > You can also use sp_detach_db/sp_attach_db as well see
>> >http://support.microsoft.com/kb/314546, you would need to make sure
>> > that you have the correct logins and the users are not orphaned. The
>> > quickest methods tend to be backup/restore or sp_detach_db/
>> > sp_attach_db. SQL 2000 database can be attached/restored to SQL 2005
>> > but not vice versa.
>> > Watch out for data protection issues!!
>> > John- Hide quoted text -
>> - Show quoted text -
> But you can still use backup/restore which require no downtime of the
> production system. The KB shows all three methods.
> John
>|||If you just need to sync small amounts of test/prod data, consider ApexSQL's
Diff product.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Mike" <Mike@.community.nospam.com> wrote in message
news:%236r99absHHA.1060@.TK2MSFTNGP06.phx.gbl...
>I need to do some testing on our test servers but with production data.
>What is the best way to get all of the production data into my test
>database? I'm going from a SQL 2000 database in production to a SQL 2005
>database in test.
> Would the import data wizard work or a 'backup of the 2000 database and
> restore to SQL 2005 database work?
> Which would be the best way to do this?
> Mike
>
>

Monday, March 12, 2012

Processing AS2000 Cubes from SSIS

Hi All,

I have a scenario where I want to execute AS 2000 Cubes from a SSIS package. In my Prod environment I have two servers one with the SQL 2000 database and the AS2000 cubes on it and the other with SSIS installed on it.

What I am doing here is I have a DTS package with a process cube task in it, this DTS package is saved as a structured file and then I call this DTS package from SSIS using the Execurte 2000 package task. Just FYI the process cubes task is using local as the server reference. I want to know how this will work when I execute this DTS package from within an SSIS package running on a different server? Appreciate all help.

Thanks

The "normal" way to process AS 2000 from DTS was to use the OLAP Processing task for the job, but that only gets installed as part of AS 2000, and hence will not be available on your SSIS/DTS machine. Using local as the server reference will also fail, unless you run the DTS package on the AS server, as quite obviously the local server is not the AS server, for a package running on SSIS/DTS machine. There is no magic way to get the DTS package to run on the other machine, other than calling the package from that machine itself. I would stick with a DTS package on the AS2000 box.

|||

Thanks Darren,

Actually I do have DTS and AS2000 installed on the same server which also has SQL Server 2000 databases on it, However I am executing these DTS packages (which process the cubes using local as server reference) from SSIS packages which are running on a different server. The DTS packages are saved as structured files on the SSIS server. Hope I am explaining it properly - what my concern is, whether executing these DTS packages having process cubes task within it from a different server will work?

Appreciate your help.

Thanks

|||

I Still have this concern whether my packages will run or not, it would be better to know now rather than getting to know in prod. Any help is appreciated.

Thanks

|||No this will not work. All the AS stuff is on the wrong server, it needs to be on the server upon which the DTS package runs. If calling DTS from SSIS then that means the DTS package runs on the same server as SSIS, which is not the AS server. This is all about execution location, which for both DTS and SSIS is cleint side, they are not client/server like SQL Server.|||

Thanks Darren,

Appreciate your help.

Thanks

|||

Hi,

I have a Cube on my AS2000 server running on the same machine as my SQL server 2000. I can execute the DTS from the SQL Enterprise manager properly.

I need to implement a C# code that will enable me to execute the DTS. I already have one as follows which works on any DTS:

Package2Class package = new Package2Class();

object pVarPersistStgOfHost = null;

package.LoadFromSQLServer(serverName,null,null,DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection,null,null,null,packageName,ref pVarPersistStgOfHost);

package.Execute();

As I said, it works with any DTS, but as soon as I work with one which Processes Cubes, it hangs for about 2 hours, when the cube can normally be processed in 20 seconds, and crash on the following

DTS Execution error:

Do you have a clue why is it so please?

|||

I've been thinking about this some more. As I recall you can process OLAP 200 cubes remotely, well I think you could in DTS, so all you need is -

1 DTS

2 DSO

3 OLAP Task

Now in SSIS we have 1 already. 2 we can get, see the feature pack download on MS site April or November. For 3, the task, it seems that the documenattion says you need to install AS 200 to get this, well as I recall it was actually just a DTS custom task that used DSO, so why not go get the DLL, register it by hand in DTS, and give it a try -

The task DLL should be in X:\Program Files\Microsoft Analysis Services\Bin\msmdtsp.dll on any SQL 2000 box with AS installed.

|||

Darren,

I am using SQL Server 2000. I do not have SSIS and I have to get this sorted way before we upgrade to 2005.

1. Are there any issues with the methods LoadFromSQLServer from the class Package2Class when we invoke a DTS with OLAP Cube.

2. I have already installed Analysis Services. When I deployed my application to a test web server which has SQL client installed, I have a COM exception. Does it mean that I need to have AS2000 installed in order for LoadFromSQLServer to instantiate the correct steps object which are of type OLAP tasks?

Can you please advise me on the coding and permission stuff that will allow a code on .net 1.1 (since it is an upgrade on a legacy system) to launch a DTS on SQL2000 which processes a cube on AS2000?

Thanks

WaaZ

|||

WaaZ, I was replying to the thread from db_guy. I'm not sure why you have posted a DTS question in the SSIS forum, and it is not really the same issue anyway, as you are not using SSIS at all. A better thread would make more sense to start with, but this is a SSIS focused forum I'm afraid. I can branch into a new thread if you want.

1 - No issues with that method that would not be raised if using another execution host in the same location and context. Instead of running your application, try running DTSRUN in the same place, as the same user, and see what errors you get.

2 - To load a DTS package, you need DTS installed locally. If you use the AS Proc Task, then you need AS support and the AS task also installed locally.

|||

Thanks Darren,

Finally we decided that we will be processing the cubes using DTS and not SSIS, but I will surely try to test the stuff you mentioned , will let you know soon.

Thanks

Processing AS2000 Cubes from SSIS

Hi All,

Thanks

|||

Thanks Darren,

Appreciate your help.

Thanks

|||

I Still have this concern whether my packages will run or not, it would be better to know now rather than getting to know in prod. Any help is appreciated.

Thanks

Thanks Darren,

Appreciate your help.

Thanks

|||

Hi,

I have a Cube on my AS2000 server running on the same machine as my SQL server 2000. I can execute the DTS from the SQL Enterprise manager properly.

I need to implement a C# code that will enable me to execute the DTS. I already have one as follows which works on any DTS:

Package2Class package = new Package2Class();

object pVarPersistStgOfHost = null;

package.LoadFromSQLServer(serverName,null,null,DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection,null,null,null,packageName,ref pVarPersistStgOfHost);

package.Execute();

As I said, it works with any DTS, but as soon as I work with one which Processes Cubes, it hangs for about 2 hours, when the cube can normally be processed in 20 seconds, and crash on the following

DTS Execution error:

Do you have a clue why is it so please?

|||

I've been thinking about this some more. As I recall you can process OLAP 200 cubes remotely, well I think you could in DTS, so all you need is -

1 DTS

2 DSO

3 OLAP Task

The task DLL should be in X:\Program Files\Microsoft Analysis Services\Bin\msmdtsp.dll on any SQL 2000 box with AS installed.

|||

Darren,

I am using SQL Server 2000. I do not have SSIS and I have to get this sorted way before we upgrade to 2005.

1. Are there any issues with the methods LoadFromSQLServer from the class Package2Class when we invoke a DTS with OLAP Cube.

Can you please advise me on the coding and permission stuff that will allow a code on .net 1.1 (since it is an upgrade on a legacy system) to launch a DTS on SQL2000 which processes a cube on AS2000?

Thanks

WaaZ

|||

2 - To load a DTS package, you need DTS installed locally. If you use the AS Proc Task, then you need AS support and the AS task also installed locally.

|||

Thanks Darren,

Finally we decided that we will be processing the cubes using DTS and not SSIS, but I will surely try to test the stuff you mentioned , will let you know soon.

Thanks

Saturday, February 25, 2012

Process constantly polling the tmw_queue table looking for work to do

I have created a package that will backup prod. database and restore
dev. database.
The job failed because there is a process constantly polling for work
to do.
How can I have the job kill this process, put database in single user
mode and then restore database?
Isabel
Use ALTER DATABASE to set restricted user or single user and the ROLLBACK option of ALTER DATABASE
(see Books Online for syntax).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"confused" <isabelellis@.hotmail.com> wrote in message
news:1133543598.767838.12810@.o13g2000cwo.googlegro ups.com...
>I have created a package that will backup prod. database and restore
> dev. database.
> The job failed because there is a process constantly polling for work
> to do.
> How can I have the job kill this process, put database in single user
> mode and then restore database?
> Isabel
>

Process constantly polling the tmw_queue table looking for work to do

I have created a package that will backup prod. database and restore
dev. database.
The job failed because there is a process constantly polling for work
to do.
How can I have the job kill this process, put database in single user
mode and then restore database?
IsabelUse ALTER DATABASE to set restricted user or single user and the ROLLBACK op
tion of ALTER DATABASE
(see Books Online for syntax).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"confused" <isabelellis@.hotmail.com> wrote in message
news:1133543598.767838.12810@.o13g2000cwo.googlegroups.com...
>I have created a package that will backup prod. database and restore
> dev. database.
> The job failed because there is a process constantly polling for work
> to do.
> How can I have the job kill this process, put database in single user
> mode and then restore database?
> Isabel
>

Process constantly polling the tmw_queue table looking for work to do

I have created a package that will backup prod. database and restore
dev. database.
The job failed because there is a process constantly polling for work
to do.
How can I have the job kill this process, put database in single user
mode and then restore database?
IsabelUse ALTER DATABASE to set restricted user or single user and the ROLLBACK option of ALTER DATABASE
(see Books Online for syntax).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"confused" <isabelellis@.hotmail.com> wrote in message
news:1133543598.767838.12810@.o13g2000cwo.googlegroups.com...
>I have created a package that will backup prod. database and restore
> dev. database.
> The job failed because there is a process constantly polling for work
> to do.
> How can I have the job kill this process, put database in single user
> mode and then restore database?
> Isabel
>

Friday, March 23, 2012

Prod database counters

prod data to test server

prod data to test server

prod data to test server

Monday, March 12, 2012

Processing AS2000 Cubes from SSIS

Processing AS2000 Cubes from SSIS

Saturday, February 25, 2012

Process constantly polling the tmw_queue table looking for work to do

Process constantly polling the tmw_queue table looking for work to do

Process constantly polling the tmw_queue table looking for work to do

Procedure Time out

Blog Archive

About Me