Hello All
I've set up a job to backup our financial db and restore it to the same
machine (different files & db name) for reporting. This was to avoid a
blocking issue caused by the front end financial system.
The thing is, the blocking is still there, even when you are reporting
on the mirror database and the blocking process is on the other db!
Is this something to do with the objects having the same names/indexes
in sysobjects? Any ideas on how to resolve it?
Thanks!
Paul
That isn't possible. I suspect you have more to this than you are reporting.
Locks in one db do not affect queries in another unless the query or
transaction is across both dbs. You might want to have a look at these:
http://support.microsoft.com/kb/271509
http://www.sql-server-performance.com/sf_block_prevention.asp
Andrew J. Kelly SQL MVP
"pdm" <paulusm@.gmail.com> wrote in message
news:1165263399.585739.293410@.16g2000cwy.googlegro ups.com...
> Hello All
> I've set up a job to backup our financial db and restore it to the same
> machine (different files & db name) for reporting. This was to avoid a
> blocking issue caused by the front end financial system.
> The thing is, the blocking is still there, even when you are reporting
> on the mirror database and the blocking process is on the other db!
> Is this something to do with the objects having the same names/indexes
> in sysobjects? Any ideas on how to resolve it?
> Thanks!
> Paul
>
|||You are absolutely right, I was being a dumb-XXX
I had the dbname hardcoded in the stored procedures
Thsnks for your time though!
P
|||That is the biggest argument to never using the db name in the object
qualifier unless you are actually intending to go cross db in the first
place. You are not the first one to get bit by that and you won't be the
last.
Andrew J. Kelly SQL MVP
"pdm" <paulusm@.gmail.com> wrote in message
news:1165316471.090188.94890@.16g2000cwy.googlegrou ps.com...
> You are absolutely right, I was being a dumb-XXX
> I had the dbname hardcoded in the stored procedures
> Thsnks for your time though!
> P
>
sql
Showing posts with label job. Show all posts
Showing posts with label job. Show all posts
Wednesday, March 28, 2012
mirror db blocking issue
Hello All
I've set up a job to backup our financial db and restore it to the same
machine (different files & db name) for reporting. This was to avoid a
blocking issue caused by the front end financial system.
The thing is, the blocking is still there, even when you are reporting
on the mirror database and the blocking process is on the other db!
Is this something to do with the objects having the same names/indexes
in sysobjects? Any ideas on how to resolve it?
Thanks!
PaulThat isn't possible. I suspect you have more to this than you are reporting.
Locks in one db do not affect queries in another unless the query or
transaction is across both dbs. You might want to have a look at these:
http://support.microsoft.com/kb/271509
http://www.sql-server-performance.com/sf_block_prevention.asp
--
Andrew J. Kelly SQL MVP
"pdm" <paulusm@.gmail.com> wrote in message
news:1165263399.585739.293410@.16g2000cwy.googlegroups.com...
> Hello All
> I've set up a job to backup our financial db and restore it to the same
> machine (different files & db name) for reporting. This was to avoid a
> blocking issue caused by the front end financial system.
> The thing is, the blocking is still there, even when you are reporting
> on the mirror database and the blocking process is on the other db!
> Is this something to do with the objects having the same names/indexes
> in sysobjects? Any ideas on how to resolve it?
> Thanks!
> Paul
>|||You are absolutely right, I was being a dumb-ass
I had the dbname hardcoded in the stored procedures
Thsnks for your time though!
P|||That is the biggest argument to never using the db name in the object
qualifier unless you are actually intending to go cross db in the first
place. You are not the first one to get bit by that and you won't be the
last.
--
Andrew J. Kelly SQL MVP
"pdm" <paulusm@.gmail.com> wrote in message
news:1165316471.090188.94890@.16g2000cwy.googlegroups.com...
> You are absolutely right, I was being a dumb-ass
> I had the dbname hardcoded in the stored procedures
> Thsnks for your time though!
> P
>
I've set up a job to backup our financial db and restore it to the same
machine (different files & db name) for reporting. This was to avoid a
blocking issue caused by the front end financial system.
The thing is, the blocking is still there, even when you are reporting
on the mirror database and the blocking process is on the other db!
Is this something to do with the objects having the same names/indexes
in sysobjects? Any ideas on how to resolve it?
Thanks!
PaulThat isn't possible. I suspect you have more to this than you are reporting.
Locks in one db do not affect queries in another unless the query or
transaction is across both dbs. You might want to have a look at these:
http://support.microsoft.com/kb/271509
http://www.sql-server-performance.com/sf_block_prevention.asp
--
Andrew J. Kelly SQL MVP
"pdm" <paulusm@.gmail.com> wrote in message
news:1165263399.585739.293410@.16g2000cwy.googlegroups.com...
> Hello All
> I've set up a job to backup our financial db and restore it to the same
> machine (different files & db name) for reporting. This was to avoid a
> blocking issue caused by the front end financial system.
> The thing is, the blocking is still there, even when you are reporting
> on the mirror database and the blocking process is on the other db!
> Is this something to do with the objects having the same names/indexes
> in sysobjects? Any ideas on how to resolve it?
> Thanks!
> Paul
>|||You are absolutely right, I was being a dumb-ass
I had the dbname hardcoded in the stored procedures
Thsnks for your time though!
P|||That is the biggest argument to never using the db name in the object
qualifier unless you are actually intending to go cross db in the first
place. You are not the first one to get bit by that and you won't be the
last.
--
Andrew J. Kelly SQL MVP
"pdm" <paulusm@.gmail.com> wrote in message
news:1165316471.090188.94890@.16g2000cwy.googlegroups.com...
> You are absolutely right, I was being a dumb-ass
> I had the dbname hardcoded in the stored procedures
> Thsnks for your time though!
> P
>
mirror db blocking issue
Hello All
I've set up a job to backup our financial db and restore it to the same
machine (different files & db name) for reporting. This was to avoid a
blocking issue caused by the front end financial system.
The thing is, the blocking is still there, even when you are reporting
on the mirror database and the blocking process is on the other db!
Is this something to do with the objects having the same names/indexes
in sysobjects? Any ideas on how to resolve it?
Thanks!
PaulThat isn't possible. I suspect you have more to this than you are reporting.
Locks in one db do not affect queries in another unless the query or
transaction is across both dbs. You might want to have a look at these:
http://support.microsoft.com/kb/271509
http://www.sql-server-performance.c..._prevention.asp
Andrew J. Kelly SQL MVP
"pdm" <paulusm@.gmail.com> wrote in message
news:1165263399.585739.293410@.16g2000cwy.googlegroups.com...
> Hello All
> I've set up a job to backup our financial db and restore it to the same
> machine (different files & db name) for reporting. This was to avoid a
> blocking issue caused by the front end financial system.
> The thing is, the blocking is still there, even when you are reporting
> on the mirror database and the blocking process is on the other db!
> Is this something to do with the objects having the same names/indexes
> in sysobjects? Any ideas on how to resolve it?
> Thanks!
> Paul
>|||You are absolutely right, I was being a dumb-XXX
I had the dbname hardcoded in the stored procedures
Thsnks for your time though!
P|||That is the biggest argument to never using the db name in the object
qualifier unless you are actually intending to go cross db in the first
place. You are not the first one to get bit by that and you won't be the
last.
Andrew J. Kelly SQL MVP
"pdm" <paulusm@.gmail.com> wrote in message
news:1165316471.090188.94890@.16g2000cwy.googlegroups.com...
> You are absolutely right, I was being a dumb-XXX
> I had the dbname hardcoded in the stored procedures
> Thsnks for your time though!
> P
>
I've set up a job to backup our financial db and restore it to the same
machine (different files & db name) for reporting. This was to avoid a
blocking issue caused by the front end financial system.
The thing is, the blocking is still there, even when you are reporting
on the mirror database and the blocking process is on the other db!
Is this something to do with the objects having the same names/indexes
in sysobjects? Any ideas on how to resolve it?
Thanks!
PaulThat isn't possible. I suspect you have more to this than you are reporting.
Locks in one db do not affect queries in another unless the query or
transaction is across both dbs. You might want to have a look at these:
http://support.microsoft.com/kb/271509
http://www.sql-server-performance.c..._prevention.asp
Andrew J. Kelly SQL MVP
"pdm" <paulusm@.gmail.com> wrote in message
news:1165263399.585739.293410@.16g2000cwy.googlegroups.com...
> Hello All
> I've set up a job to backup our financial db and restore it to the same
> machine (different files & db name) for reporting. This was to avoid a
> blocking issue caused by the front end financial system.
> The thing is, the blocking is still there, even when you are reporting
> on the mirror database and the blocking process is on the other db!
> Is this something to do with the objects having the same names/indexes
> in sysobjects? Any ideas on how to resolve it?
> Thanks!
> Paul
>|||You are absolutely right, I was being a dumb-XXX
I had the dbname hardcoded in the stored procedures
Thsnks for your time though!
P|||That is the biggest argument to never using the db name in the object
qualifier unless you are actually intending to go cross db in the first
place. You are not the first one to get bit by that and you won't be the
last.
Andrew J. Kelly SQL MVP
"pdm" <paulusm@.gmail.com> wrote in message
news:1165316471.090188.94890@.16g2000cwy.googlegroups.com...
> You are absolutely right, I was being a dumb-XXX
> I had the dbname hardcoded in the stored procedures
> Thsnks for your time though!
> P
>
Monday, March 19, 2012
min rights required to schedule a sql server job
Hello,
I need to create a user that can schedule jobs. I cannot use the 'sa' account.
What rights should I assign this user?
Michael
Michael,
You need a user that is a memeber of SQLAgentUserRole database role in msdb database.
You can read more about that in BOL topics:
"How to: Configure a User to Create and Manage SQL Server Agent Jobs"
"Security for SQL Server Agent Administration"
Regards,
|||Here is the link to the Books Online topic for SQL Server Agent security: http://msdn2.microsoft.com/en-us/library/ms190978.aspx
Cheers,
Dan
min rights required to schedule a sql server job
Hello,
I need to create a user that can schedule jobs. I cannot use the 'sa' account.
What rights should I assign this user?
Michael
Michael,
You need a user that is a memeber of SQLAgentUserRole database role in msdb database.
You can read more about that in BOL topics:
"How to: Configure a User to Create and Manage SQL Server Agent Jobs"
"Security for SQL Server Agent Administration"
Regards,
|||Here is the link to the Books Online topic for SQL Server Agent security: http://msdn2.microsoft.com/en-us/library/ms190978.aspx
Cheers,
Dan
Friday, March 9, 2012
Migration of DTS to SSIS
Well..
Have migrated my SQL 2000 DTS packages to SSIS packages through the package migration Wizard.Also, the hurdle to schedule it as a job has been resolved.But, the concern is the package contains entries\lins for old dataserver.
Is it possible to edit the SSIS pckage using management studio or integration services?
Please move this question to the " SQL Server Integration Services forum" .
Thanks
Migration of DTS to SSIS
Well..
Have migrated my SQL 2000 DTS packages to SSIS packages through the package migration Wizard.Also, the hurdle to schedule it as a job has been resolved.But, the concern is the package contains entries\lins for old dataserver.
Is it possible to edit the SSIS pckage using management studio or integration services?
Please move this question to the " SQL Server Integration Services forum" .
Thanks
Subscribe to:
Posts (Atom)