Finding deatail Deadlock info IN SQl Server 2005 Standard Edtion

We are using SQL Server 2005 with SP2.Originally, we seemed to be having a lot of unnecessary lock escalation occurring to the page and table level.

I would like to track down all storedprocedure/queries or function that are being casue of deadlock in sql server 2005 with all deadlock input like transactionID ,command ran,loginID etc.But we have to need to find these information on SQL Server 2005 standard edtion.

Is there any tool,command or something like this which track down these information blindly in sql server 2005 without effecting system performance seriously.

Ahamd Drshen

Hi,

When executing DBCC TRACEON (1204,-1) in a command window, you're enabling the deadlock tracing option. (http://msdn2.microsoft.com/en-us/library/ms188396.aspx) When a deadlock occurs, you can find details in the logs. You can also try using the option 'with nolock' in your sql statements (SELECT * FROM Employees WITH (NOLOCK)).

Regards,

Remco

|||will it track all deadlock resource information SQL Server Errors log ?|||it will show all conflicting locks and its commands.|||

Use SQL Server Profiler with DeadLock Graph Event.

Or use stored procedures sp_create_trace and sp_trace_setevent for collecting profiler events without graphical interface.

@.eventId for DeadLock Graph = 148

Find/Replace Not Working in Management Console

I have the 2005 Standard Edition of Sql Server, and the managment console tonight stopped responding to quick find/replace requests either via menu or (ctrl+f, ctrl+h).

The UI bars above just grey out a bit and nothing happens. I saw a MSFT dev for VB (visual studio) respond with a fix that was to run "devenv.exe /resetsettings" that restored the defaults and fixed the problem for those affected.

Anyone know how to do that for management studio, since going to customize (restore) each of the individual bars didn't do it.

Thanks in advance,

Greg

Greg,

Please make sure that Find & Replace window is not docked (and possibly hidden) somewhere along the edges of Management Studio. It sometimes happens that a tool window gets docked and then slides to a hidden position so only it's caption bar is visible.

Regards,

|||

Maciek,

Thanks for the reply. I read this and said 'bah, no way'....

Well, there it was, the caption hiding like 1 pixel above the status bar...exposed if i made it temporarily auto-hide.

Thanks for this, again. One comment, though. I could select the window with ctrl+H...a nice way out of this would be an addition in the window menu bar to force it as floating center screen to get ahold of it again.

Greg

|||

Greg,

Glad I could help. We've seen this happening to number of people for some reason. I guess you are right - there should be an easy way to find that window. I'll open a suggestion for that.

Regards,

|||Did an easy way ever get implemented? I have the same problem and I can't find the tool window after moving everything around. I've noticed that the main menu changes when I attempt ctrl-F, the 'Query' between 'View' and 'Project' disappears.|||

I am not sure if a shortcut to center the windows was ever added. However, while the problem keeps happening to me I've become much more adept at finding that edge of the window hiding at a corner.

Drop the status bar, peer at the edges of the screen, and I find it every time...just drag it back to the middle.

Greg M

|||

I found it thanks to your hint though not quite in the way you describe. In fact I found a way of making the Find dialogue pop up centered. What I did was go to the "Task bar and Start Menu" properties window (right click on start menu). Then uncheck "autohide the task bar" and popup appeared in all its glory. Amazing. I had uninstalled and reinstalled and the problem persisted - cost hours! One thing it taught me though was how important find/replace functionality is to my development practice. I turn old code into new code using it.

|||

manay thanks

i had the same porblem

and i found the pop up under the task bar

many many thanks

Find/Replace Not Working in Management Console

I have the 2005 Standard Edition of Sql Server, and the managment console tonight stopped responding to quick find/replace requests either via menu or (ctrl+f, ctrl+h).

The UI bars above just grey out a bit and nothing happens. I saw a MSFT dev for VB (visual studio) respond with a fix that was to run "devenv.exe /resetsettings" that restored the defaults and fixed the problem for those affected.

Anyone know how to do that for management studio, since going to customize (restore) each of the individual bars didn't do it.

Thanks in advance,

Greg

Greg,

Please make sure that Find & Replace window is not docked (and possibly hidden) somewhere along the edges of Management Studio. It sometimes happens that a tool window gets docked and then slides to a hidden position so only it's caption bar is visible.

Regards,

|||

Maciek,

Thanks for the reply. I read this and said 'bah, no way'....

Well, there it was, the caption hiding like 1 pixel above the status bar...exposed if i made it temporarily auto-hide.

Thanks for this, again. One comment, though. I could select the window with ctrl+H...a nice way out of this would be an addition in the window menu bar to force it as floating center screen to get ahold of it again.

Greg

|||

Greg,

Glad I could help. We've seen this happening to number of people for some reason. I guess you are right - there should be an easy way to find that window. I'll open a suggestion for that.

Regards,

|||Did an easy way ever get implemented? I have the same problem and I can't find the tool window after moving everything around. I've noticed that the main menu changes when I attempt ctrl-F, the 'Query' between 'View' and 'Project' disappears.|||

I am not sure if a shortcut to center the windows was ever added. However, while the problem keeps happening to me I've become much more adept at finding that edge of the window hiding at a corner.

Drop the status bar, peer at the edges of the screen, and I find it every time...just drag it back to the middle.

Greg M

|||

I found it thanks to your hint though not quite in the way you describe. In fact I found a way of making the Find dialogue pop up centered. What I did was go to the "Task bar and Start Menu" properties window (right click on start menu). Then uncheck "autohide the task bar" and popup appeared in all its glory. Amazing. I had uninstalled and reinstalled and the problem persisted - cost hours! One thing it taught me though was how important find/replace functionality is to my development practice. I turn old code into new code using it.

|||

manay thanks

i had the same porblem

and i found the pop up under the task bar

many many thanks

Find SQL Server Product key installed on server

Hello;

I am trying to get information of SQL Server licensed key which is installed on our servers. I am using SQL Server 2005 standard edition. Is there a way to find out the product key which is installed on servers. I did some searches and found some thirdparty software to get that information but I don't want to use them on production, is there a way which microsoft recommends.

ThanksHello;

I am trying to get information of SQL Server licensed key which is installed on our servers. I am using SQL Server 2005 standard edition. Is there a way to find out the product key which is installed on servers. I did some searches and found some thirdparty software to get that information but I don't want to use them on production, is there a way which microsoft recommends.

Thanks
mmmm. Do you really think that Micro$oft is going to recommend software that could be used by the unscrupulous to pirate their software? :confused:|||Microsoft has a tool that is normally used by bulk licensors of their software that is used for license reporting that does display this information. If you are using bulk licensing (where you deploy from a set of master images, then write a check directly to Microsoft for the copies you have in production use) then check with either your own license administrator (usually a domain admin) or with your Microsoft Sales Representative.

-PatP

Find out the database name from the connection string

Hi,
Is there any standard string method to find out the database name from the connection string..
Thanks
Once you're connected, SELECT DB_NAME()
http://www.aspfaq.com/
(Reverse address to reply.)
"DbQuester" <anonymous@.discussions.microsoft.com> wrote in message
news:C4725B60-6FD5-4DCA-BE75-D2839E3B53D9@.microsoft.com...
> Hi,
> Is there any standard string method to find out the database name from the
connection string..
> Thanks
|||ok thanks, but wouldnt it be better to have the database name right in the app, instead of telling t-sql to talk to the database and then give out the result.. my connection string will be in app.config file and I want to just split it out and use info li
ke InstanceName and database etc all throughtout my app..
thanks
|||(a) If you're already connected to the database, SELECT DB_NAME() is not
going to be any more of a performance hit than string parsing. And it's
still something you only have to do once.
(b) In your app config file, keep the parameters you want separate and then
build the connection string in the app. This way you can keep certain
attributes in separate variables.
(c) There is no magic string parser that will look at a connection string
and tell you certain attribute values. You can use regular expressions, but
see (a).
http://www.aspfaq.com/
(Reverse address to reply.)
"DbQuester" <anonymous@.discussions.microsoft.com> wrote in message
news:CCB255A7-DF84-4BB4-B121-4975FE7C38B0@.microsoft.com...
> ok thanks, but wouldnt it be better to have the database name right in the
app, instead of telling t-sql to talk to the database and then give out the
result.. my connection string will be in app.config file and I want to just
split it out and use info like InstanceName and database etc all throughtout
my app..
> thanks
|||Thanks, it makes sense..
|||1. You could create a UDL and test it against the SQL Server/DB in question.
2. Then open the UDL as a notepad and copy the connection string which you
can use for app's init file.
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Find out the database name from the connection string

Hi,
Is there any standard string method to find out the database name from the c
onnection string..
ThanksOnce you're connected, SELECT DB_NAME()
http://www.aspfaq.com/
(Reverse address to reply.)
"DbQuester" <anonymous@.discussions.microsoft.com> wrote in message
news:C4725B60-6FD5-4DCA-BE75-D2839E3B53D9@.microsoft.com...
> Hi,
> Is there any standard string method to find out the database name from the
connection string..
> Thanks|||ok thanks, but wouldnt it be better to have the database name right in the a
pp, instead of telling t-sql to talk to the database and then give out the r
esult.. my connection string will be in app.config file and I want to just s
plit it out and use info li
ke InstanceName and database etc all throughtout my app..
thanks|||(a) If you're already connected to the database, SELECT DB_NAME() is not
going to be any more of a performance hit than string parsing. And it's
still something you only have to do once.
(b) In your app config file, keep the parameters you want separate and then
build the connection string in the app. This way you can keep certain
attributes in separate variables.
(c) There is no magic string parser that will look at a connection string
and tell you certain attribute values. You can use regular expressions, but
see (a).
http://www.aspfaq.com/
(Reverse address to reply.)
"DbQuester" <anonymous@.discussions.microsoft.com> wrote in message
news:CCB255A7-DF84-4BB4-B121-4975FE7C38B0@.microsoft.com...
> ok thanks, but wouldnt it be better to have the database name right in the
app, instead of telling t-sql to talk to the database and then give out the
result.. my connection string will be in app.config file and I want to just
split it out and use info like InstanceName and database etc all throughtout
my app..
> thanks|||Thanks, it makes sense..|||1. You could create a UDL and test it against the SQL Server/DB in question.
2. Then open the UDL as a notepad and copy the connection string which you
can use for app's init file.
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Find out the database name from the connection string

Hi
Is there any standard string method to find out the database name from the connection string.
ThanksOnce you're connected, SELECT DB_NAME()
--
http://www.aspfaq.com/
(Reverse address to reply.)
"DbQuester" <anonymous@.discussions.microsoft.com> wrote in message
news:C4725B60-6FD5-4DCA-BE75-D2839E3B53D9@.microsoft.com...
> Hi,
> Is there any standard string method to find out the database name from the
connection string..
> Thanks|||ok thanks, but wouldnt it be better to have the database name right in the app, instead of telling t-sql to talk to the database and then give out the result.. my connection string will be in app.config file and I want to just split it out and use info like InstanceName and database etc all throughtout my app.
thanks|||(a) If you're already connected to the database, SELECT DB_NAME() is not
going to be any more of a performance hit than string parsing. And it's
still something you only have to do once.
(b) In your app config file, keep the parameters you want separate and then
build the connection string in the app. This way you can keep certain
attributes in separate variables.
(c) There is no magic string parser that will look at a connection string
and tell you certain attribute values. You can use regular expressions, but
see (a).
--
http://www.aspfaq.com/
(Reverse address to reply.)
"DbQuester" <anonymous@.discussions.microsoft.com> wrote in message
news:CCB255A7-DF84-4BB4-B121-4975FE7C38B0@.microsoft.com...
> ok thanks, but wouldnt it be better to have the database name right in the
app, instead of telling t-sql to talk to the database and then give out the
result.. my connection string will be in app.config file and I want to just
split it out and use info like InstanceName and database etc all throughtout
my app..
> thanks|||1. You could create a UDL and test it against the SQL Server/DB in question.
2. Then open the UDL as a notepad and copy the connection string which you
can use for app's init file.
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.