We have a database running with about 40 users per branch (6 branches). The replication we are running at the moment (MERGE) often causes our systems to deadlock, upon which the only way to fix it is to kill the BLOCKING process.
I would like to set up an alert that can notify me when a deadlock occurs, but so far the only success i have is on a normal lock - which occus hundreds of times per second.
If anyone can help me out it will be of great help. My e-mail address is it@.bex.co.za
Thanx,
NeilRE: We have a database running with about 40 users per branch (6 branches). The replication we are running at the moment (MERGE) often causes our systems to deadlock, upon which the only way to fix it is to kill the BLOCKING process.
I would like to set up an alert that can notify me when a deadlock occurs, but so far the only success i have is on a normal lock - which occus hundreds of times per second.
If anyone can help me out it will be of great help. My e-mail address is it@.bex.co.za
Thanx,
Neil
From EM:
(Expand out to Alerts)
Sql Server Instance:
Management:
Alerts:
(right click Alerts, select)--> New Alert:
Name: [Deadlocks/sec]
Type: (select from dropdown)--> Sql Server Performance conditon alert
Object: (select from dropdown)--> YourInstanceName:Locks
Counter: (select from dropdown)--> Number of Deadlocks/sec
Alert if counter: (select from dropdown)--> rises above
Value: [0]
Showing posts with label users. Show all posts
Showing posts with label users. Show all posts
Tuesday, March 20, 2012
Saturday, February 25, 2012
Agent Job logs on using Default User Profile
Hi,
I get a strange problem whereby I have set up a Sql Server Agent job to run as a particular user. The job needs to access details from the users local profile.
The job fails to access the correct user profile when it logs in, and utilises the 'Default User' profile. This happens for scheduled jobs when the actual user used to run the job as is not logged into the machine where the Sql Server instance lives.
If, however, the user IS logged in at the time of job execution, the correct profile is loaded.
I can see this happening by simplying executing an OS command that kicks of a batch file with something like the following:
echo %USERPROFILE%
whoami
When the user is logged in I see the results as expected, eg:
UserProfile = C:\Documents and Settings\myUser
whoami=myDomain\myUser
However when the user is not logged on at the time of execution I get the following:
UserProfile = C:\Documents and Settings\Default User
whoami= myDomain\myUser
Any suggestions would be much appreciated....Its starting to do my head in...
Thanks.
What are associated privileges for that user to run as a SQLAgent account, in general if that job performs any copy or movement within network then the SQLAGent user account must need relevant privileges to complete that task.|||Thanks for the reply..
At this stage the only thing my job is doing is outputing the results of whoami and %USERPROFILE% to a txt file. The job doesn't fail - which I would expect if there was a permissions problem. It just displays the %USERPROFILE% as Default User, when executing via a proxy account (not the SQL Agent Acc) - even though the whoami command shows the correct user is logged in.
Any ideas which privileges need to be applied to both the SQL Agent account, and the account which the proxy maps to?
|||Because of the Proxy usage this is the behaviour, I think. Refer to the books online about SQLAgent operator role
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/719ce56b-d6b2-414a-88a8-f43b725ebc79.htm
|||The user that I am trying to get the step to log on as is a member of the sysadmin Server Role, so it should have access to all the Sql Server Agent functionality anyway - whether or not it has the SQLAgent operator role assigned..
The thing that has me really confused, is why can the Agent correctly logs the user in to execute the job (ie load the correct profile), ONLY when that same user is currently logged into the machine SQL Server is running on?
Is there maybe some way I could log the user in first, then execute the required step - ensuring that the user is logged out post execution - the thing is though I shouldn't need to do that...aaahhh..
thanks for our help...
Agent Job logs on using Default User Profile
Hi,
I get a strange problem whereby I have set up a Sql Server Agent job to run as a particular user. The job needs to access details from the users local profile.
The job fails to access the correct user profile when it logs in, and utilises the 'Default User' profile. This happens for scheduled jobs when the actual user used to run the job as is not logged into the machine where the Sql Server instance lives.
If, however, the user IS logged in at the time of job execution, the correct profile is loaded.
I can see this happening by simplying executing an OS command that kicks of a batch file with something like the following:
echo %USERPROFILE%
whoami
When the user is logged in I see the results as expected, eg:
UserProfile = C:\Documents and Settings\myUser
whoami=myDomain\myUser
However when the user is not logged on at the time of execution I get the following:
UserProfile = C:\Documents and Settings\Default User
whoami= myDomain\myUser
Any suggestions would be much appreciated....Its starting to do my head in...
Thanks.
What are associated privileges for that user to run as a SQLAgent account, in general if that job performs any copy or movement within network then the SQLAGent user account must need relevant privileges to complete that task.|||Thanks for the reply..
At this stage the only thing my job is doing is outputing the results of whoami and %USERPROFILE% to a txt file. The job doesn't fail - which I would expect if there was a permissions problem. It just displays the %USERPROFILE% as Default User, when executing via a proxy account (not the SQL Agent Acc) - even though the whoami command shows the correct user is logged in.
Any ideas which privileges need to be applied to both the SQL Agent account, and the account which the proxy maps to?
|||Because of the Proxy usage this is the behaviour, I think. Refer to the books online about SQLAgent operator role
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/719ce56b-d6b2-414a-88a8-f43b725ebc79.htm
|||The user that I am trying to get the step to log on as is a member of the sysadmin Server Role, so it should have access to all the Sql Server Agent functionality anyway - whether or not it has the SQLAgent operator role assigned..
The thing that has me really confused, is why can the Agent correctly logs the user in to execute the job (ie load the correct profile), ONLY when that same user is currently logged into the machine SQL Server is running on?
Is there maybe some way I could log the user in first, then execute the required step - ensuring that the user is logged out post execution - the thing is though I shouldn't need to do that...aaahhh..
thanks for our help...
Sunday, February 19, 2012
After SQL Help
This is probably a simple problem for all you SQL punters but I'm no so strong so here it is:
I have a 'users' table. I also have a 'friendship' table which dictates which users are friends with other users.
If I want to retrieve all 'friends' of a particular 'user' that is straight forward. But how would I write a query to retrieve all the 'friends' of all the 'friends' of a user.
It's quite simple using multiple hits to the DB but can it be contained in a single SQL statement or SP?
In SqlServer 2005 you can use the new (or old standard) CTE syntax like so:
declare @.uidintset @.uid = 1;with AllFriends(Id,Name)as(select u1.userid, u1.namefrom users u1join friends f1on u1.userid = f1.friendidwhere f1.userid = @.uidunionallselect u2.userid, u2.namefrom users u2join friends f2on u2.userid = f2.friendidjoin AllFriends aon f2.userid = a.id)select *from AllFriends|||
Yeah I'm using SQL Server 2005...
OK thanks for that I'll try to adapt it to what I'm doing.
|||I'm sorry but I just don't understand what you've done here...
I'm not even sure what to search for in terms of a tutorial to help me understand what you've done...
|||uhm.. sorry for that...|||Thanks mate.I'm kinda bad at explaining things, so here's a tutorial:
http://www.sqlservercentral.com/columnists/sSampath/recursivequeriesinsqlserver2005.aspxhope this makes things more clear
Thursday, February 16, 2012
after restore db to another server got problem
Hi,
Our production database JDE7334 tables not belong to dbo
owner; those tables belong to 4 different users.
Once I did backuped and restored this database to another
server. I can't use sp_dropuser to drop automatically
existing users because the user owns objects in the
database and cannot be dropped. The thing is I can't
change table's owner from specific user to dbo, this
database work with JDEdwards software.
My question is how to create login and user to access this
database in new server?
The primary server which I did backup installed SQL Server
Standard Edition with SP3 and the secondary server which I
did restore installed SQL Server Enterprise Edition with
SP2.
Regards
JennyTake a look at these:
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly
SQL Server MVP
"Jenny" <jyu@.iseoptions.com> wrote in message
news:12b1301c3f63d$ecccf980$a501280a@.phx
.gbl...
> Hi,
> Our production database JDE7334 tables not belong to dbo
> owner; those tables belong to 4 different users.
> Once I did backuped and restored this database to another
> server. I can't use sp_dropuser to drop automatically
> existing users because the user owns objects in the
> database and cannot be dropped. The thing is I can't
> change table's owner from specific user to dbo, this
> database work with JDEdwards software.
> My question is how to create login and user to access this
> database in new server?
> The primary server which I did backup installed SQL Server
> Standard Edition with SP3 and the secondary server which I
> did restore installed SQL Server Enterprise Edition with
> SP2.
> Regards
> Jenny|||Thanks a lot Andrew, it worked.
Jenny
>--Original Message--
>Take a look at these:
>
>http://www.support.microsoft.com/?id=314546 Moving
DB's between Servers
>http://www.support.microsoft.com/?id=224071 Moving
SQL Server Databases
>to a New Location with Detach/Attach
>http://support.microsoft.com/?id=221465 Using
WITH MOVE in a
>Restore
>http://www.support.microsoft.com/?id=246133 How To
Transfer Logins and
>Passwords Between SQL Servers
>http://www.support.microsoft.com/?id=298897 Mapping
Logins & SIDs after a
>Restore
>http://www.dbmaint.com/SyncSqlLogins.asp Utility
to map logins to
>users
>http://www.support.microsoft.com/?id=168001 User
Logon and/or Permission
>Errors After Restoring Dump
>http://www.support.microsoft.com/?id=240872 How to
Resolve Permission
>Issues When a Database Is Moved Between SQL Servers
>http://www.sqlservercentral.com/scr...iptdetails.asp?
scriptid=599
>Restoring a .mdf
>http://www.support.microsoft.com/?id=307775 Disaster
Recovery Articles
>for SQL Server
>
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Jenny" <jyu@.iseoptions.com> wrote in message
> news:12b1301c3f63d$ecccf980$a501280a@.phx
.gbl...
another
this
Server
which I
>
>.
>
Our production database JDE7334 tables not belong to dbo
owner; those tables belong to 4 different users.
Once I did backuped and restored this database to another
server. I can't use sp_dropuser to drop automatically
existing users because the user owns objects in the
database and cannot be dropped. The thing is I can't
change table's owner from specific user to dbo, this
database work with JDEdwards software.
My question is how to create login and user to access this
database in new server?
The primary server which I did backup installed SQL Server
Standard Edition with SP3 and the secondary server which I
did restore installed SQL Server Enterprise Edition with
SP2.
Regards
JennyTake a look at these:
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly
SQL Server MVP
"Jenny" <jyu@.iseoptions.com> wrote in message
news:12b1301c3f63d$ecccf980$a501280a@.phx
.gbl...
> Hi,
> Our production database JDE7334 tables not belong to dbo
> owner; those tables belong to 4 different users.
> Once I did backuped and restored this database to another
> server. I can't use sp_dropuser to drop automatically
> existing users because the user owns objects in the
> database and cannot be dropped. The thing is I can't
> change table's owner from specific user to dbo, this
> database work with JDEdwards software.
> My question is how to create login and user to access this
> database in new server?
> The primary server which I did backup installed SQL Server
> Standard Edition with SP3 and the secondary server which I
> did restore installed SQL Server Enterprise Edition with
> SP2.
> Regards
> Jenny|||Thanks a lot Andrew, it worked.
Jenny
>--Original Message--
>Take a look at these:
>
>http://www.support.microsoft.com/?id=314546 Moving
DB's between Servers
>http://www.support.microsoft.com/?id=224071 Moving
SQL Server Databases
>to a New Location with Detach/Attach
>http://support.microsoft.com/?id=221465 Using
WITH MOVE in a
>Restore
>http://www.support.microsoft.com/?id=246133 How To
Transfer Logins and
>Passwords Between SQL Servers
>http://www.support.microsoft.com/?id=298897 Mapping
Logins & SIDs after a
>Restore
>http://www.dbmaint.com/SyncSqlLogins.asp Utility
to map logins to
>users
>http://www.support.microsoft.com/?id=168001 User
Logon and/or Permission
>Errors After Restoring Dump
>http://www.support.microsoft.com/?id=240872 How to
Resolve Permission
>Issues When a Database Is Moved Between SQL Servers
>http://www.sqlservercentral.com/scr...iptdetails.asp?
scriptid=599
>Restoring a .mdf
>http://www.support.microsoft.com/?id=307775 Disaster
Recovery Articles
>for SQL Server
>
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Jenny" <jyu@.iseoptions.com> wrote in message
> news:12b1301c3f63d$ecccf980$a501280a@.phx
.gbl...
another
this
Server
which I
>
>.
>
after restore db to another server got problem
Hi,
Our production database JDE7334 tables not belong to dbo
owner; those tables belong to 4 different users.
Once I did backuped and restored this database to another
server. I can't use sp_dropuser to drop automatically
existing users because the user owns objects in the
database and cannot be dropped. The thing is I can't
change table's owner from specific user to dbo, this
database work with JDEdwards software.
My question is how to create login and user to access this
database in new server?
The primary server which I did backup installed SQL Server
Standard Edition with SP3 and the secondary server which I
did restore installed SQL Server Enterprise Edition with
SP2.
Regards
JennyTake a look at these:
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly
SQL Server MVP
"Jenny" <jyu@.iseoptions.com> wrote in message
news:12b1301c3f63d$ecccf980$a501280a@.phx.gbl...
> Hi,
> Our production database JDE7334 tables not belong to dbo
> owner; those tables belong to 4 different users.
> Once I did backuped and restored this database to another
> server. I can't use sp_dropuser to drop automatically
> existing users because the user owns objects in the
> database and cannot be dropped. The thing is I can't
> change table's owner from specific user to dbo, this
> database work with JDEdwards software.
> My question is how to create login and user to access this
> database in new server?
> The primary server which I did backup installed SQL Server
> Standard Edition with SP3 and the secondary server which I
> did restore installed SQL Server Enterprise Edition with
> SP2.
> Regards
> Jenny|||Thanks a lot Andrew, it worked.
Jenny
>--Original Message--
>Take a look at these:
>
>http://www.support.microsoft.com/?id=314546 Moving
DB's between Servers
>http://www.support.microsoft.com/?id=224071 Moving
SQL Server Databases
>to a New Location with Detach/Attach
>http://support.microsoft.com/?id=221465 Using
WITH MOVE in a
>Restore
>http://www.support.microsoft.com/?id=246133 How To
Transfer Logins and
>Passwords Between SQL Servers
>http://www.support.microsoft.com/?id=298897 Mapping
Logins & SIDs after a
>Restore
>http://www.dbmaint.com/SyncSqlLogins.asp Utility
to map logins to
>users
>http://www.support.microsoft.com/?id=168001 User
Logon and/or Permission
>Errors After Restoring Dump
>http://www.support.microsoft.com/?id=240872 How to
Resolve Permission
>Issues When a Database Is Moved Between SQL Servers
>http://www.sqlservercentral.com/scripts/scriptdetails.asp?
scriptid=599
>Restoring a .mdf
>http://www.support.microsoft.com/?id=307775 Disaster
Recovery Articles
>for SQL Server
>
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Jenny" <jyu@.iseoptions.com> wrote in message
>news:12b1301c3f63d$ecccf980$a501280a@.phx.gbl...
>> Hi,
>> Our production database JDE7334 tables not belong to dbo
>> owner; those tables belong to 4 different users.
>> Once I did backuped and restored this database to
another
>> server. I can't use sp_dropuser to drop automatically
>> existing users because the user owns objects in the
>> database and cannot be dropped. The thing is I can't
>> change table's owner from specific user to dbo, this
>> database work with JDEdwards software.
>> My question is how to create login and user to access
this
>> database in new server?
>> The primary server which I did backup installed SQL
Server
>> Standard Edition with SP3 and the secondary server
which I
>> did restore installed SQL Server Enterprise Edition with
>> SP2.
>> Regards
>> Jenny
>
>.
>
Our production database JDE7334 tables not belong to dbo
owner; those tables belong to 4 different users.
Once I did backuped and restored this database to another
server. I can't use sp_dropuser to drop automatically
existing users because the user owns objects in the
database and cannot be dropped. The thing is I can't
change table's owner from specific user to dbo, this
database work with JDEdwards software.
My question is how to create login and user to access this
database in new server?
The primary server which I did backup installed SQL Server
Standard Edition with SP3 and the secondary server which I
did restore installed SQL Server Enterprise Edition with
SP2.
Regards
JennyTake a look at these:
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly
SQL Server MVP
"Jenny" <jyu@.iseoptions.com> wrote in message
news:12b1301c3f63d$ecccf980$a501280a@.phx.gbl...
> Hi,
> Our production database JDE7334 tables not belong to dbo
> owner; those tables belong to 4 different users.
> Once I did backuped and restored this database to another
> server. I can't use sp_dropuser to drop automatically
> existing users because the user owns objects in the
> database and cannot be dropped. The thing is I can't
> change table's owner from specific user to dbo, this
> database work with JDEdwards software.
> My question is how to create login and user to access this
> database in new server?
> The primary server which I did backup installed SQL Server
> Standard Edition with SP3 and the secondary server which I
> did restore installed SQL Server Enterprise Edition with
> SP2.
> Regards
> Jenny|||Thanks a lot Andrew, it worked.
Jenny
>--Original Message--
>Take a look at these:
>
>http://www.support.microsoft.com/?id=314546 Moving
DB's between Servers
>http://www.support.microsoft.com/?id=224071 Moving
SQL Server Databases
>to a New Location with Detach/Attach
>http://support.microsoft.com/?id=221465 Using
WITH MOVE in a
>Restore
>http://www.support.microsoft.com/?id=246133 How To
Transfer Logins and
>Passwords Between SQL Servers
>http://www.support.microsoft.com/?id=298897 Mapping
Logins & SIDs after a
>Restore
>http://www.dbmaint.com/SyncSqlLogins.asp Utility
to map logins to
>users
>http://www.support.microsoft.com/?id=168001 User
Logon and/or Permission
>Errors After Restoring Dump
>http://www.support.microsoft.com/?id=240872 How to
Resolve Permission
>Issues When a Database Is Moved Between SQL Servers
>http://www.sqlservercentral.com/scripts/scriptdetails.asp?
scriptid=599
>Restoring a .mdf
>http://www.support.microsoft.com/?id=307775 Disaster
Recovery Articles
>for SQL Server
>
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Jenny" <jyu@.iseoptions.com> wrote in message
>news:12b1301c3f63d$ecccf980$a501280a@.phx.gbl...
>> Hi,
>> Our production database JDE7334 tables not belong to dbo
>> owner; those tables belong to 4 different users.
>> Once I did backuped and restored this database to
another
>> server. I can't use sp_dropuser to drop automatically
>> existing users because the user owns objects in the
>> database and cannot be dropped. The thing is I can't
>> change table's owner from specific user to dbo, this
>> database work with JDEdwards software.
>> My question is how to create login and user to access
this
>> database in new server?
>> The primary server which I did backup installed SQL
Server
>> Standard Edition with SP3 and the secondary server
which I
>> did restore installed SQL Server Enterprise Edition with
>> SP2.
>> Regards
>> Jenny
>
>.
>
Sunday, February 12, 2012
Affecting performance?
I have a table with 23 columns. 8 columns are not always filled by users and so valued to default. Would this affect the overall performance of querying this table? Should I separate those 8 columns and link with a one-to-one relation?
Thanks
Any idea?|||From a database design point of view I would seperate them.
Affecting performance?
I have a table with 23 columns. 8 columns are not always filled by users and so valued to default. Would this affect the overall performance of querying this table? Should I separate those 8 columns and link with a one-to-one relation?
Thanks
Any idea?|||From a database design point of view I would seperate them.
Subscribe to:
Posts (Atom)