Showing posts with label trouble. Show all posts
Showing posts with label trouble. Show all posts

Thursday, March 29, 2012

cannot execute xp_cmdshell. Access is denied while copying data over the network

Hi,

I am having some trouble copying data over my workgroup network from my Windows 2003 Server Machine (machineA with SQL SERVER 2005) to one of my network Machine's drive(MachineB).
Here is the T-SQL code that I am trying to execute:

EXEC xp_cmdshell 'copy D:\Data\file.txt \\MachineB\Documents'

Whenever I tried to execute the above piece of code, I get the error message "Access is denied", but if I try to copy the file from the Command Prompt (cmd.exe) with the copy command, the file copies fine over the network.

I have already searched over the internet and I found out that loads of people have the same issue, and they were suggested something like this:
"Check in Services and make sure that the MSSQLServer service is run as a domain user and that domain user has rights to these network resources."

Well it sounds plausible, but I don't know what are the exact steps to do this. How do I know which user is running the MSSQL Server service? Are they referring to the user which I use to connect to my SQL Server Database engine throuhg the SQL Server Management Studio?
Also they are suggesting 'domain user', and as I said before I do not have domain network just regular simple workgroup network.

Here are some details of the user that I use to login. I generally login into my Windows 2003 Server machine with user called 'User1' and I use the same 'User1' to connect to SQL Server through the Management Studio Screen.
Should I create a user called 'User1' on my MachineB(Destination Machine)?

I would really appreciate, if someone can give me detailed steps explaning how to solve this problem.

Thank you very much once again.

Open the Surface Area Configuration Manager. Go to the features section. Make sure that xp_cmdshell is enabled. (It is turned off by default.) Then once you have enabled xp_cmdshell, make sure that the SQL Server service account has the appropriate permissions granted.|||Hi Michael,

Thank you very much for your reply. xp_cmdshell is already enabled. How do I check the permissions of SQL Server Srvice account? Is that the same account which I use to connect to SQL SERVER through Management Studio?

Thanks

|||No. It is the Windows account that the SQL Server service is running under. Specifically, you can find this within SQL Server Configuration Manager.|||

Michael Hotek wrote:

No. It is the Windows account that the SQL Server service is running under. Specifically, you can find this within SQL Server Configuration Manager.

Ok I went to the SQL SERVER Configuration Manager. Then I clicked on SQL SERVER 2005 Services in the left pane. On the right pane, it listen 5-6 services running like Intergration Service, Analysis Service, Reporting Service, and so on. One of them was SQL SERVER(MSSQLSERVER), so I think this must be the one that you were talking about. I right clicked on it-> Properties. Under the properties tab, I saw Log on as: Built-in Account (radio button) was checked and in the dropdown below it, Local System was selected. So basically the SQL Server Service is running under Local System. Now how do I figure out what user is associated with Local System?

Thank you once again for the help.Much appreciated.

|||Local system is the local machine account. That would be why you are getting an access denied on a network resource. The SID for localsystem is not valid external to the machine. In order to be able to execute that command you would need to create a Windows login and then use that login to run the SQL Server services. That login would have to have permissions on the network resource you are trying to write to.|||Thanks a lot Michael, that really makes sense .

Now for the extension based on your reply. Are there any disadvantages of using a Windows user to run the SQL Server services instead of a Local Machine account?

Thank you very very much for your prompt reply and insightful suggestion.

|||No, in fact, there are entire feature sets that you can't use in SQL Server if you are running under the localsystem account, replication being one of them. I don't run any SQL Server under a local machine account. I always run them under specific user accounts and in particular regular domain user accounts.|||I just did this and it worked like a charm. You someone already posted the answer. Go to SQL Server Configuration Manager. Right click on Sql Server, click properties and change the user account to run under a domain or local pc account. that has access to the share.

cannot execute xp_cmdshell. Access is denied while copying data over the network

Hi,

I am having some trouble copying data over my workgroup network from my Windows 2003 Server Machine (machineA with SQL SERVER 2005) to one of my network Machine's drive(MachineB).
Here is the T-SQL code that I am trying to execute:

EXEC xp_cmdshell 'copy D:\Data\file.txt \\MachineB\Documents'

Whenever I tried to execute the above piece of code, I get the error message "Access is denied", but if I try to copy the file from the Command Prompt (cmd.exe) with the copy command, the file copies fine over the network.

I have already searched over the internet and I found out that loads of people have the same issue, and they were suggested something like this:
"Check in Services and make sure that the MSSQLServer service is run as

a domain user and that domain user has rights to these network

resources."

Well it sounds plausible, but I don't know what are the exact steps to do this. How do I know which user is running the MSSQL Server service? Are they referring to the user which I use to connect to my SQL Server Database engine throuhg the SQL Server Management Studio?
Also they are suggesting 'domain user', and as I said before I do not have domain network just regular simple workgroup network.

Here are some details of the user that I use to login. I generally login into my Windows 2003 Server machine with user called 'User1' and I use the same 'User1' to connect to SQL Server through the Management Studio Screen.
Should I create a user called 'User1' on my MachineB(Destination Machine)?

I would really appreciate, if someone can give me detailed steps explaning how to solve this problem.

Thank you very much once again.Open the Surface Area Configuration Manager. Go to the features section. Make sure that xp_cmdshell is enabled. (It is turned off by default.) Then once you have enabled xp_cmdshell, make sure that the SQL Server service account has the appropriate permissions granted.|||Hi Michael,

Thank you very much for your reply. xp_cmdshell is already enabled. How do I check the permissions of SQL Server Srvice account? Is that the same account which I use to connect to SQL SERVER through Management Studio?

Thanks|||No. It is the Windows account that the SQL Server service is running under. Specifically, you can find this within SQL Server Configuration Manager.|||

Michael Hotek wrote:

No. It is the Windows account that the SQL Server service is running under. Specifically, you can find this within SQL Server Configuration Manager.

Ok I went to the SQL SERVER Configuration Manager. Then I clicked on SQL SERVER 2005 Services in the left pane. On the right pane, it listen 5-6 services running like Intergration Service, Analysis Service, Reporting Service, and so on. One of them was SQL SERVER(MSSQLSERVER), so I think this must be the one that you were talking about. I right clicked on it-> Properties. Under the properties tab, I saw Log on as: Built-in Account (radio button) was checked and in the dropdown below it, Local System was selected. So basically the SQL Server Service is running under Local System. Now how do I figure out what user is associated with Local System?

Thank you once again for the help.Much appreciated.|||Local system is the local machine account. That would be why you are getting an access denied on a network resource. The SID for localsystem is not valid external to the machine. In order to be able to execute that command you would need to create a Windows login and then use that login to run the SQL Server services. That login would have to have permissions on the network resource you are trying to write to.|||Thanks a lot Michael, that really makes sense .

Now for the extension based on your reply. Are there any disadvantages of using a Windows user to run the SQL Server services instead of a Local Machine account?

Thank you very very much for your prompt reply and insightful suggestion.|||No, in fact, there are entire feature sets that you can't use in SQL Server if you are running under the localsystem account, replication being one of them. I don't run any SQL Server under a local machine account. I always run them under specific user accounts and in particular regular domain user accounts.|||I just did this and it worked like a charm. You someone already posted the answer. Go to SQL Server Configuration Manager. Right click on Sql Server, click properties and change the user account to run under a domain or local pc account. that has access to the share.

cannot execute xp_cmdshell. Access is denied while copying data over the network

Hi,

I am havingsome trouble copying data over my workgroup network from my Windows2003 Server Machine (machineA with SQL SERVER 2005) to one of mynetwork Machine's drive(MachineB).
Here is the T-SQL code that I am trying to execute:

EXEC xp_cmdshell 'copy D:\Data\file.txt \\MachineB\Documents'

WheneverI tried to execute the above piece of code, I get the error message"Access is denied", but if I try to copy the file from the CommandPrompt (cmd.exe) with the copy command, the file copies fine over thenetwork.

I have already searched over the internet and I foundout that loads of people have the same issue, and they were suggestedsomething like this:
"Check in Services and make sure that the MSSQLServer service is run asa domain user and that domain user has rights to these networkresources."

Wellit sounds plausible, but I don't know what are the exact steps to dothis. How do I know which user is running the MSSQL Server service? Arethey referring to the user which I use to connect to my SQL ServerDatabase engine throuhg the SQL Server Management Studio?
Also they are suggesting 'domain user', and as I said before I do not have domain network just regular simple workgroup network.

Hereare some details of the user that I use to login. I generally logininto my Windows 2003 Server machine with user called 'User1' and I usethe same 'User1' to connect to SQL Server through the Management StudioScreen.
Should I create a user called 'User1' on my MachineB(Destination Machine)?

I would really appreciate, if someone can give me detailed steps explaning how to solve this problem.

Thank you very much once again.Check out the SQL Server Surface Area Configuration under SQL Server 2005 configuration. xp_cmdshell is disabled by default. You need to enable it manually and give permissions.|||

ndinakar:

Check out the SQL Server Surface Area Configuration under SQL Server 2005 configuration. xp_cmdshell is disabled by default. You need to enable it manually and give permissions.

Thank you very much for your reply. xp_cmdshell is already enabled.

|||

modest:

"Check in Services and make sure that the MSSQLServer service is run as a domain user and that domain user has rights to these network resources."

Well it sounds plausible, but I don't know what are the exact steps to do this.

You're on the rigth waySmile goto Start->All Programs->Microsoft SQL Server2005-> Configuration Tools->SQL Server Configuration Manager->SQL Server 2005 Services->locate the service for the SQL2005 instance, should be "SQL Server (InstanceName)"->double click it-> on the Log On tab, you can see the start up account for the service. Just make sure this account has proper permission on the remote resource.

|||Yeah I did that and it says under buit-in account, the user is Local System. Now how do I find out which user is associated with Local System.

Thanks for the reply.|||

modest:

Yeah I did that and it says under buit-in account, the user is Local System. Now how do I find out which user is associated with Local System.

Thanks for the reply.

xp_cmdshell runs with SQL Server Agent permissions Microsoft have finally documented the permission requirements. Try the link below and follow the instructions. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms175046.aspx

|||

Caddre:

modest:

Yeah I did that and it says under buit-in account, the user is Local System. Now how do I find out which user is associated with Local System.

Thanks for the reply.

xp_cmdshell runs with SQL Server Agent permissions Microsoft have finally documented the permission requirements. Try the link below and follow the instructions. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms175046.aspx

Well I already have the proxy credential created for the User1 Windows account as I use that credential to execute the dtsRun command from the xp_cmdshell and that runs fine. The problem is when I am trying to copy something over the network. For example I can copy a file within my local machine by using the xp_cmdshell stored procedure, but it won;t work if the destination drive is a network drive.|||That is because you installed SQL Server with the local systems account when you do that and most developers do, SQL Server service and SQL Server Agent are without access to network resources. Try running replication service and you will see what I am talking about. Run a search for local systems account limitations in SQL Server BOL (books online). Hope this helps.|||

Caddre:

That is because you installed SQL Server with the local systems account when you do that and most developers do, SQL Server service and SQL Server Agent are without access to network resources. Try running replication service and you will see what I am talking about. Run a search for local systems account limitations in SQL Server BOL (books online). Hope this helps.

Thanks. Actually I was worried, if changing the startup user for SQLSERVER 2005 will affect my ASP/ASP.NET scripts or for that matter IIS6.0. Will this change be transparent to all the users on the machineand the other machine on my network which connect to the SQL SERVER onmy machine?|||It is not complicated just run full backup of all your databases and rerun setup and make sure you install SQL Server service and SQL Server Agent with two Domain accounts with network permissions. And no it will not affect your application if you do the above because if you run into permissions problem your backup will help if recreate the databases before the changes. But I don't think it will affect anything because you are just giving SQL Server service and the Agent network access. Hope this helps.|||

Caddre:

It is not complicated just run full backup of all your databases and rerun setup and make sure you install SQL Server service and SQL Server Agent with two Domain accounts with network permissions. And no it will not affect your application if you do the above because if you run into permissions problem your backup will help if recreate the databases before the changes. But I don't think it will affect anything because you are just giving SQL Server service and the Agent network access. Hope this helps.

Thanks Caddre very much. I just have one question based on your suggestion. What do you mean by domain accout? Do you mean like actual Domain Network on which my computer is connected? As I said in my first post, we have a workgroup network and not domain network. I know I am confusing with the word Domain. Does domain account mean, a new Windows user that I create on my machine? Or does it mean something lese?

Thank you once again.|||OK my bad create service accounts in your workgroup to run SQL Server service and SQL Server Agent and no you cannot run both on one account because the Agent can do a lot of automation in your application for you so it is good practice to run them on separate accounts. Hope this helps.

Sunday, March 25, 2012

Cannot DROP SERVICE

I'm having trouble dropping a Service Broker Seriver using the DROP SERVICE command.

After about 15 minutes, my SQL Server 2005 Standard edition replies with the following message:

Msg 701, Level 17, State 171, Line 1
There is insufficient system memory to run this query.

Is there another way to remove a service? I've tried restarting SQL, but this does not work.

Open a second connection to the server and please run this command:

dbcc memorystatus

Run it one before the DROP statement is issued, one or twice while is running (e.g after 5 minutes and after 10 minutes) and then once when the error occurs. Save the output each time into a file and please send me this output to remus.rusanu@.microsoft.com

This way I hope to identify the problem.

Thanks,
~ Remus

|||For others out there, here is the before report.

<before>
Memory Manager KB
--
VM Reserved 1064060
VM Committed 693280
AWE Allocated 0
Reserved Memory 1024
Reserved Memory In Use 0

(5 row(s) affected)

Memory node Id = 0 KB
--
VM Reserved 1059964
VM Committed 689336
AWE Allocated 0
MultiPage Allocator 7704
SinglePage Allocator 159856

(5 row(s) affected)

MEMORYCLERK_SQLGENERAL (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 880
MultiPage Allocator 1592

(7 row(s) affected)

MEMORYCLERK_SQLBUFFERPOOL (Total) KB
- --
VM Reserved 1047772
VM Committed 677824
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 256

(7 row(s) affected)

MEMORYCLERK_SQLOPTIMIZER (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 144
MultiPage Allocator 72

(7 row(s) affected)

MEMORYCLERK_SQLUTILITIES (Total) KB
- --
VM Reserved 480
VM Committed 480
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 96
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SQLSTORENG (Total) KB
- --
VM Reserved 1024
VM Committed 1024
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 4088
MultiPage Allocator 72

(7 row(s) affected)

MEMORYCLERK_SQLCONNECTIONPOOL (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 240
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SQLCLR (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SQLSERVICEBROKER (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 17512
MultiPage Allocator 192

(7 row(s) affected)

MEMORYCLERK_SQLHTTP (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SNI (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 16

(7 row(s) affected)

MEMORYCLERK_FULLTEXT (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SQLXP (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_BHF (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 24
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_HOST (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 24
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SOSNODE (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 984
MultiPage Allocator 5320

(7 row(s) affected)

MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 24
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_OBJCP (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 816
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_SQLCP (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 6120
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_PHDR (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 864
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_XPROC (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 40
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_TEMPTABLES (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_NOTIF (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_VIEWDEFINITIONS (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_XMLDBTYPE (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_XMLDBELEMENT (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_XMLDBATTRIBUTE (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_STACKFRAMES (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 8

(7 row(s) affected)

CACHESTORE_BROKERTBLACS (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 152
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERKEK (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERDSH (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERUSERCERTLOOKUP (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERRSB (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERREADONLY (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 32
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERTO (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 39496
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_EVENTS (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_SYSTEMROWSET (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 368
MultiPage Allocator 0

(7 row(s) affected)

USERSTORE_SCHEMAMGR (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 424
MultiPage Allocator 0

(7 row(s) affected)

USERSTORE_DBMETADATA (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 224
MultiPage Allocator 0

(7 row(s) affected)

USERSTORE_TOKENPERM (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 72
MultiPage Allocator 0

(7 row(s) affected)

USERSTORE_OBJPERM (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 128
MultiPage Allocator 0

(7 row(s) affected)

USERSTORE_SXC (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

OBJECTSTORE_LBSS (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 40
MultiPage Allocator 0

(7 row(s) affected)

OBJECTSTORE_SNI_PACKET (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 240
MultiPage Allocator 48

(7 row(s) affected)

OBJECTSTORE_SERVICE_BROKER (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 34984
MultiPage Allocator 0

(7 row(s) affected)

OBJECTSTORE_LOCK_MANAGER (Total) KB
- --
VM Reserved 2048
VM Committed 2048
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 51608
MultiPage Allocator 0

(7 row(s) affected)

Buffer Distribution Buffers
--
Stolen 3016
Free 49283
Cached 16966
Database (clean) 3079
Database (dirty) 11117
I/O 0
Latched 1

(7 row(s) affected)

Buffer Counts Buffers
--
Committed 83462
Target 89991
Hashed 14197
Stolen Potential 103236
External Reservation 0
Min Free 32
Visible 89991
Available Paging File 178838

(8 row(s) affected)

Procedure Cache Value
--
TotalProcs 84
TotalPages 980
InUsePages 11

(3 row(s) affected)

Global Memory Objects Buffers
--
Resource 145
Locks 6454
XDES 461
SETLS 1
SE Dataset Allocators 2
SubpDesc Allocators 1
SE SchemaManager 52
SQLCache 33
Replication 2
ServerGlobal 25
XP Global 2
SortTables 2

(12 row(s) affected)

Query Memory Objects Value
--
Grants 0
Waiting 0
Available (Buffers) 62344
Maximum (Buffers) 62344
Limit 62344
Next Request 0
Waiting For 0
Cost 0
Timeout 0
Wait Time 0
Last Target 65625

(11 row(s) affected)

Small Query Memory Objects Value
--
Grants 0
Waiting 0
Available (Buffers) 3281
Maximum (Buffers) 3281
Limit 3281

(5 row(s) affected)

Optimization Queue Value
--
Overall Memory 575897600
Target Memory 470007808
Last Notification 1
Timeout 6
Early Termination Factor 5

(5 row(s) affected)

Small Gateway Value
--
Configured Units 4
Available Units 4
Acquires 0
Waiters 0
Threshold Factor 250000
Threshold 250000

(6 row(s) affected)

Medium Gateway Value
--
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 12

(5 row(s) affected)

Big Gateway Value
--
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 8

(5 row(s) affected)

MEMORYBROKER_FOR_CACHE Value
-- --
Allocations 16967
Rate 0
Target Allocations 70300
Future Allocations 0
Last Notification 1

(5 row(s) affected)

MEMORYBROKER_FOR_STEAL Value
-- --
Allocations 3010
Rate 0
Target Allocations 57374
Future Allocations 0
Last Notification 1

(5 row(s) affected)

MEMORYBROKER_FOR_RESERVE Value
-- --
Allocations 0
Rate 0
Target Allocations 70014
Future Allocations 15650
Last Notification 1

(5 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
</before>

Here is the 7 minute report.

<7min>
Memory Manager KB
--
VM Reserved 1067708
VM Committed 1055632
AWE Allocated 0
Reserved Memory 1024
Reserved Memory In Use 0

(5 row(s) affected)

Memory node Id = 0 KB
--
VM Reserved 1063612
VM Committed 1051688
AWE Allocated 0
MultiPage Allocator 11320
SinglePage Allocator 974488

(5 row(s) affected)

MEMORYCLERK_SQLGENERAL (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 872
MultiPage Allocator 1592

(7 row(s) affected)

MEMORYCLERK_SQLBUFFERPOOL (Total) KB
- --
VM Reserved 1047772
VM Committed 1036560
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 256

(7 row(s) affected)

MEMORYCLERK_SQLOPTIMIZER (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 144
MultiPage Allocator 72

(7 row(s) affected)

MEMORYCLERK_SQLUTILITIES (Total) KB
- --
VM Reserved 480
VM Committed 480
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 96
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SQLSTORENG (Total) KB
- --
VM Reserved 1024
VM Committed 1024
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 4096
MultiPage Allocator 3688

(7 row(s) affected)

MEMORYCLERK_SQLCONNECTIONPOOL (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 240
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SQLCLR (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SQLSERVICEBROKER (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 17512
MultiPage Allocator 192

(7 row(s) affected)

MEMORYCLERK_SQLHTTP (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SNI (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 16

(7 row(s) affected)

MEMORYCLERK_FULLTEXT (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SQLXP (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_BHF (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 24
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_HOST (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 24
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SOSNODE (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 944
MultiPage Allocator 5320

(7 row(s) affected)

MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 24
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_OBJCP (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 152
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_SQLCP (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 10072
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_PHDR (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_XPROC (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_TEMPTABLES (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_NOTIF (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_VIEWDEFINITIONS (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_XMLDBTYPE (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_XMLDBELEMENT (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_XMLDBATTRIBUTE (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_STACKFRAMES (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 8

(7 row(s) affected)

CACHESTORE_BROKERTBLACS (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 128
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERKEK (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERDSH (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERUSERCERTLOOKUP (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERRSB (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERREADONLY (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 32
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERTO (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 145136
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_EVENTS (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_SYSTEMROWSET (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 64
MultiPage Allocator 0

(7 row(s) affected)

USERSTORE_SCHEMAMGR (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 424
MultiPage Allocator 0

(7 row(s) affected)

USERSTORE_DBMETADATA (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 160
MultiPage Allocator 0

(7 row(s) affected)

USERSTORE_TOKENPERM (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 72
MultiPage Allocator 0

(7 row(s) affected)

USERSTORE_OBJPERM (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 128
MultiPage Allocator 0

(7 row(s) affected)

USERSTORE_SXC (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

OBJECTSTORE_LBSS (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0

(7 row(s) affected)

OBJECTSTORE_SNI_PACKET (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 208
MultiPage Allocator 48

(7 row(s) affected)

OBJECTSTORE_SERVICE_BROKER (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 742048
MultiPage Allocator 0

(7 row(s) affected)

OBJECTSTORE_LOCK_MANAGER (Total) KB
- --
VM Reserved 2048
VM Committed 2048
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 51512
MultiPage Allocator 0

(7 row(s) affected)

Buffer Distribution Buffers
--
Stolen 3012
Free 274
Cached 118808
Database (clean) 1899
Database (dirty) 4308
I/O 1
Latched 2

(7 row(s) affected)

Buffer Counts Buffers
--
Committed 128304
Target 128304
Hashed 6212
Stolen Potential 1421
External Reservation 0
Min Free 32
Visible 128304
Available Paging File 133742

(8 row(s) affected)

Procedure Cache Value
--
TotalProcs 1
TotalPages 1281
InUsePages 0

(3 row(s) affected)

Global Memory Objects Buffers
--
Resource 146
Locks 6442
XDES 914
SETLS 1
SE Dataset Allocators 2
SubpDesc Allocators 1
SE SchemaManager 52
SQLCache 31
Replication 2
ServerGlobal 25
XP Global 2
SortTables 2

(12 row(s) affected)

Query Memory Objects Value
--
Grants 0
Waiting 0
Available (Buffers) 5285
Maximum (Buffers) 5285
Limit 5240
Next Request 0
Waiting For 0
Cost 0
Timeout 0
Wait Time 0
Last Target 5880

(11 row(s) affected)

Small Query Memory Objects Value
--
Grants 0
Waiting 0
Available (Buffers) 640
Maximum (Buffers) 640
Limit 640

(5 row(s) affected)

Optimization Queue Value
--
Overall Memory 822476800
Target Memory 51404800
Last Notification 0
Timeout 6
Early Termination Factor 5

(5 row(s) affected)

Small Gateway Value
--
Configured Units 4
Available Units 4
Acquires 0
Waiters 0
Threshold Factor 250000
Threshold 250000

(6 row(s) affected)

Medium Gateway Value
--
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 12

(5 row(s) affected)

Big Gateway Value
--
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 8

(5 row(s) affected)

MEMORYBROKER_FOR_CACHE Value
-- --
Allocations 118885
Rate -706
Target Allocations 82639
Future Allocations 0
Last Notification 2

(5 row(s) affected)

MEMORYBROKER_FOR_STEAL Value
-- --
Allocations 3004
Rate -3
Target Allocations 6275
Future Allocations 0
Last Notification 0

(5 row(s) affected)

MEMORYBROKER_FOR_RESERVE Value
-- --
Allocations 0
Rate -96
Target Allocations 6275
Future Allocations 22354
Last Notification 0

(5 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
</7min>

Here is the after report.

<after>
Memory Manager KB
--
VM Reserved 1064060
VM Committed 755880
AWE Allocated 0
Reserved Memory 1024
Reserved Memory In Use 0

(5 row(s) affected)

Memory node Id = 0 KB
--
VM Reserved 1059964
VM Committed 751936
AWE Allocated 0
MultiPage Allocator 7704
SinglePage Allocator 154824

(5 row(s) affected)

MEMORYCLERK_SQLGENERAL (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 872
MultiPage Allocator 1592

(7 row(s) affected)

MEMORYCLERK_SQLBUFFERPOOL (Total) KB
- --
VM Reserved 1047772
VM Committed 740424
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 256

(7 row(s) affected)

MEMORYCLERK_SQLOPTIMIZER (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 144
MultiPage Allocator 72

(7 row(s) affected)

MEMORYCLERK_SQLUTILITIES (Total) KB
- --
VM Reserved 480
VM Committed 480
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 96
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SQLSTORENG (Total) KB
- --
VM Reserved 1024
VM Committed 1024
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 4088
MultiPage Allocator 72

(7 row(s) affected)

MEMORYCLERK_SQLCONNECTIONPOOL (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 224
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SQLCLR (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SQLSERVICEBROKER (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 17512
MultiPage Allocator 192

(7 row(s) affected)

MEMORYCLERK_SQLHTTP (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SNI (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 16

(7 row(s) affected)

MEMORYCLERK_FULLTEXT (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SQLXP (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_BHF (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 24
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_HOST (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 24
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SOSNODE (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 976
MultiPage Allocator 5320

(7 row(s) affected)

MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 24
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_OBJCP (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 152
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_SQLCP (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 56
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_PHDR (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_XPROC (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_TEMPTABLES (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_NOTIF (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_VIEWDEFINITIONS (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_XMLDBTYPE (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_XMLDBELEMENT (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_XMLDBATTRIBUTE (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_STACKFRAMES (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 8

(7 row(s) affected)

CACHESTORE_BROKERTBLACS (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 128
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERKEK (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERDSH (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERUSERCERTLOOKUP (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERRSB (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERREADONLY (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 32
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERTO (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 50480
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_EVENTS (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_SYSTEMROWSET (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 104
MultiPage Allocator 0

(7 row(s) affected)

USERSTORE_SCHEMAMGR (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 424
MultiPage Allocator 0

(7 row(s) affected)

USERSTORE_DBMETADATA (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 160
MultiPage Allocator 0

(7 row(s) affected)

USERSTORE_TOKENPERM (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 72
MultiPage Allocator 0

(7 row(s) affected)

USERSTORE_OBJPERM (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 128
MultiPage Allocator 0

(7 row(s) affected)

USERSTORE_SXC (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

OBJECTSTORE_LBSS (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0

(7 row(s) affected)

OBJECTSTORE_SNI_PACKET (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 208
MultiPage Allocator 48

(7 row(s) affected)

OBJECTSTORE_SERVICE_BROKER (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 27064
MultiPage Allocator 0

(7 row(s) affected)

OBJECTSTORE_LOCK_MANAGER (Total) KB
- --
VM Reserved 2048
VM Committed 2048
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 51560
MultiPage Allocator 0

(7 row(s) affected)

Buffer Distribution Buffers
--
Stolen 3012
Free 58046
Cached 16341
Database (clean) 1591
Database (dirty) 12296
I/O 0
Latched 1

(7 row(s) affected)

Buffer Counts Buffers
--
Committed 91287
Target 97284
Hashed 13888
Stolen Potential 103865
External Reservation 0
Min Free 32
Visible 97284
Available Paging File 170966

(8 row(s) affected)

Procedure Cache Value
--
TotalProcs 2
TotalPages 29
InUsePages 0

(3 row(s) affected)

Global Memory Objects Buffers
--
Resource 146
Locks 6448
XDES 461
SETLS 1
SE Dataset Allocators 2
SubpDesc Allocators 1
SE SchemaManager 52
SQLCache 31
Replication 2
ServerGlobal 25
XP Global 2
SortTables 2

(12 row(s) affected)

Query Memory Objects Value
--
Grants 0
Waiting 0
Available (Buffers) 68529
Maximum (Buffers) 68529
Limit 67688
Next Request 0
Waiting For 0
Cost 0
Timeout 0
Wait Time 0
Last Target 71250

(11 row(s) affected)

Small Query Memory Objects Value
--
Grants 0
Waiting 0
Available (Buffers) 3605
Maximum (Buffers) 3605
Limit 3605

(5 row(s) affected)

Optimization Queue Value
--
Overall Memory 622592000
Target Memory 524369920
Last Notification 1
Timeout 6
Early Termination Factor 5

(5 row(s) affected)

Small Gateway Value
--
Configured Units 4
Available Units 4
Acquires 0
Waiters 0
Threshold Factor 250000
Threshold 250000

(6 row(s) affected)

Medium Gateway Value
--
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 12

(5 row(s) affected)

Big Gateway Value
--
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 8

(5 row(s) affected)

MEMORYBROKER_FOR_CACHE Value
-- --
Allocations 16342
Rate 0
Target Allocations 76000
Future Allocations 0
Last Notification 1

(5 row(s) affected)

MEMORYBROKER_FOR_STEAL Value
-- --
Allocations 3006
Rate 0
Target Allocations 64010
Future Allocations 0
Last Notification 1

(5 row(s) affected)

MEMORYBROKER_FOR_RESERVE Value
-- --
Allocations 0
Rate 0
Target Allocations 76000
Future Allocations 16922
Last Notification 1

(5 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
</after>
|||

DROP SERVICE has to send an END CONVERSATION to each conversation owned by that service. Apparently, the system in question cannot handle this load in one single transaction.

To work around, end all the conversations first, using a cursor over sys.conversation_endpoints:

declare crsConversations cursor for

select conversation_handle

from sys.conversation_endpoints e

join sys.services s on s.service_id = e.service_id

where s.name = 'myservicename';

open crsConversations;

declare @.dh uniqueidentifier;

declare @.batch int;

select @.batch = 0;

begin transaction

fetch next from crsConversations into @.dh;

while @.@.fetch_status =0

begin

-- replace this with end conversation @.dh with error ... if

-- and error has to be sent to the peer

--

end conversation @.dh with cleanup;

-- commit every 1000 conversations ended

select @.batch = @.batch + 1;

if @.batch > 999

begin

commit transaction;

begin transaction;

end

fetch next from crsConversations into @.dh;

end

commit transaction;

close crsConversations;

deallocate crsConversations;

go

drop service 'myservicename'

go

Tuesday, March 20, 2012

Cannot Debug SQL 2005 Stored Procedures in VS 2005

I am having trouble in consistently debugging T-SQL stored procedures in SQL
2005 using VS 2005 Pro. I have enabled debugging on the connection in the
Server Explorer and enable CLR Integration in the Surface Area Configuration
tool. At one point I could step into the sproc but now I cannot. What has
gone wrong. How can I be able to consistently debug T-SQL sprocs?
Any input would be appreciated."=?Utf-8?B? RWFnbGVSZWRASGlnaEZseWluZ0JpcmRzLmNvbQ==
?="
<EagleRedHighFlyingBirdscom@.discussions.microsoft.com> wrote in
news:2EC86A08-79A4-44CC-ABDF-72DAE46D1BF0@.microsoft.com:

> I am having trouble in consistently debugging T-SQL stored procedures
> in SQL 2005 using VS 2005 Pro. I have enabled debugging on the
> connection in the Server Explorer and enable CLR Integration in the
> Surface Area Configuration tool. At one point I could step into the
> sproc but now I cannot. What has gone wrong. How can I be able to
> consistently debug T-SQL sprocs?
>
Hmm, in order to debug T-SQL proc's you should not have to do any
enabling at all.
1. Open VS 2005
2. Open Server Explorer
3. Create a connection to the database the proc is in
4. Expand the database and drill down to Stored Procedures, expand.
5. Right click on the proc and choose "Step Into Stored Procedure".
6. Debug
What happens for you when you try the above?
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||The debugger starts but when I either step into or step to the next statemen
t
in the proc the proc runs to completion. I have set a breakpoint in the pro
c
only to have it disabled when debugging starts.
"EagleRed@.HighFlyingBirds.com" wrote:

> I am having trouble in consistently debugging T-SQL stored procedures in S
QL
> 2005 using VS 2005 Pro. I have enabled debugging on the connection in the
> Server Explorer and enable CLR Integration in the Surface Area Configurati
on
> tool. At one point I could step into the sproc but now I cannot. What ha
s
> gone wrong. How can I be able to consistently debug T-SQL sprocs?
> Any input would be appreciated.|||Here's some additional information. The disabled breakpoint displays a
tooltip that says the breakpoint will not be hit because the object
containing the breakpoint has not been loaded. What's going on? I have bee
n
able to debug and hit breakpoints, but I cannot do it consistently and right
now it simply isn't working.
"Niels Berglund" wrote:

> "=?Utf-8?B? RWFnbGVSZWRASGlnaEZseWluZ0JpcmRzLmNvbQ==
?="
> <EagleRedHighFlyingBirdscom@.discussions.microsoft.com> wrote in
> news:2EC86A08-79A4-44CC-ABDF-72DAE46D1BF0@.microsoft.com:
>
> Hmm, in order to debug T-SQL proc's you should not have to do any
> enabling at all.
> 1. Open VS 2005
> 2. Open Server Explorer
> 3. Create a connection to the database the proc is in
> 4. Expand the database and drill down to Stored Procedures, expand.
> 5. Right click on the proc and choose "Step Into Stored Procedure".
> 6. Debug
> What happens for you when you try the above?
> Niels
> --
> ****************************************
**********
> * Niels Berglund
> * http://staff.develop.com/nielsb
> * nielsb@.no-spam.develop.com
> * "A First Look at SQL Server 2005 for Developers"
> * http://www.awprofessional.com/title/0321180593
> ****************************************
**********
>

Monday, March 19, 2012

Cannot create index, timeout expires (SQL Server Express 2005)

I am having trouble creating a primary key on a large table (6 mil
rows)... on a SQL Server Express 2005 database...
After I make the changes to the table and Save, the operation starts
but times out after about 30 seconds, saying that the timeout has
expired.
I tried looking for a timeout that would govern this but I have not
been able to find one...
My query execution timeout is set to 0 (which is unlimited), though it
should be irrelevant because this is not a query.... (in fact, I have
no problem running quries that take a few mins)...
Any idea where/what I need to change to resolve this?
Thanks in advance.
<sdragolov@.gmail.com> wrote in message
news:1159639554.270613.108730@.c28g2000cwb.googlegr oups.com...
>I am having trouble creating a primary key on a large table (6 mil
> rows)... on a SQL Server Express 2005 database...
> After I make the changes to the table and Save, the operation starts
> but times out after about 30 seconds, saying that the timeout has
> expired.
> I tried looking for a timeout that would govern this but I have not
> been able to find one...
> My query execution timeout is set to 0 (which is unlimited), though it
> should be irrelevant because this is not a query.... (in fact, I have
> no problem running quries that take a few mins)...
> Any idea where/what I need to change to resolve this?
>
What tool are you using to run the query? Can you get a script of the
change and run it in a query window in SQL Server Management Studio?
David
|||Well, in theory it's not a "query", right? It's a DDL...
I am using Management Studio - I right click on the table,and do
Modify, then I select the two fields and right click and do "Primary
Key"... Then I finally do Save and that's where the error comes after
about 30 seconds...
I guess I could get the DDL text of adding these PKs and then try to
run that in a query window... though why would that be any
different...?
David Browne wrote:
> <sdragolov@.gmail.com> wrote in message
> news:1159639554.270613.108730@.c28g2000cwb.googlegr oups.com...
>
> What tool are you using to run the query? Can you get a script of the
> change and run it in a query window in SQL Server Management Studio?
> David
|||<sdragolov@.gmail.com> wrote in message
news:1159647740.250757.210120@.c28g2000cwb.googlegr oups.com...
> Well, in theory it's not a "query", right? It's a DDL...
> I am using Management Studio - I right click on the table,and do
> Modify, then I select the two fields and right click and do "Primary
> Key"... Then I finally do Save and that's where the error comes after
> about 30 seconds...
> I guess I could get the DDL text of adding these PKs and then try to
> run that in a query window... though why would that be any
> different...?
Because it's the client tool that generates the timeout, not the server. If
Management Studio will give you the DDL, just run it in a new query window.
David
|||David - your suggestion did work... Thanks!
David Browne wrote:
> <sdragolov@.gmail.com> wrote in message
> news:1159647740.250757.210120@.c28g2000cwb.googlegr oups.com...
> Because it's the client tool that generates the timeout, not the server. If
> Management Studio will give you the DDL, just run it in a new query window.
> David

Cannot create index, timeout expires (SQL Server Express 2005)

I am having trouble creating a primary key on a large table (6 mil
rows)... on a SQL Server Express 2005 database...
After I make the changes to the table and Save, the operation starts
but times out after about 30 seconds, saying that the timeout has
expired.
I tried looking for a timeout that would govern this but I have not
been able to find one...
My query execution timeout is set to 0 (which is unlimited), though it
should be irrelevant because this is not a query.... (in fact, I have
no problem running quries that take a few mins)...
Any idea where/what I need to change to resolve this'
Thanks in advance.<sdragolov@.gmail.com> wrote in message
news:1159639554.270613.108730@.c28g2000cwb.googlegroups.com...
>I am having trouble creating a primary key on a large table (6 mil
> rows)... on a SQL Server Express 2005 database...
> After I make the changes to the table and Save, the operation starts
> but times out after about 30 seconds, saying that the timeout has
> expired.
> I tried looking for a timeout that would govern this but I have not
> been able to find one...
> My query execution timeout is set to 0 (which is unlimited), though it
> should be irrelevant because this is not a query.... (in fact, I have
> no problem running quries that take a few mins)...
> Any idea where/what I need to change to resolve this'
>
What tool are you using to run the query? Can you get a script of the
change and run it in a query window in SQL Server Management Studio?
David|||Well, in theory it's not a "query", right' It's a DDL...
I am using Management Studio - I right click on the table,and do
Modify, then I select the two fields and right click and do "Primary
Key"... Then I finally do Save and that's where the error comes after
about 30 seconds...
I guess I could get the DDL text of adding these PKs and then try to
run that in a query window... though why would that be any
different...'
David Browne wrote:
> <sdragolov@.gmail.com> wrote in message
> news:1159639554.270613.108730@.c28g2000cwb.googlegroups.com...
> >I am having trouble creating a primary key on a large table (6 mil
> > rows)... on a SQL Server Express 2005 database...
> >
> > After I make the changes to the table and Save, the operation starts
> > but times out after about 30 seconds, saying that the timeout has
> > expired.
> >
> > I tried looking for a timeout that would govern this but I have not
> > been able to find one...
> >
> > My query execution timeout is set to 0 (which is unlimited), though it
> > should be irrelevant because this is not a query.... (in fact, I have
> > no problem running quries that take a few mins)...
> >
> > Any idea where/what I need to change to resolve this'
> >
>
> What tool are you using to run the query? Can you get a script of the
> change and run it in a query window in SQL Server Management Studio?
> David|||<sdragolov@.gmail.com> wrote in message
news:1159647740.250757.210120@.c28g2000cwb.googlegroups.com...
> Well, in theory it's not a "query", right' It's a DDL...
> I am using Management Studio - I right click on the table,and do
> Modify, then I select the two fields and right click and do "Primary
> Key"... Then I finally do Save and that's where the error comes after
> about 30 seconds...
> I guess I could get the DDL text of adding these PKs and then try to
> run that in a query window... though why would that be any
> different...'
Because it's the client tool that generates the timeout, not the server. If
Management Studio will give you the DDL, just run it in a new query window.
David|||David - your suggestion did work... Thanks!
David Browne wrote:
> <sdragolov@.gmail.com> wrote in message
> news:1159647740.250757.210120@.c28g2000cwb.googlegroups.com...
> > Well, in theory it's not a "query", right' It's a DDL...
> >
> > I am using Management Studio - I right click on the table,and do
> > Modify, then I select the two fields and right click and do "Primary
> > Key"... Then I finally do Save and that's where the error comes after
> > about 30 seconds...
> >
> > I guess I could get the DDL text of adding these PKs and then try to
> > run that in a query window... though why would that be any
> > different...'
> Because it's the client tool that generates the timeout, not the server. If
> Management Studio will give you the DDL, just run it in a new query window.
> David

Cannot create index, timeout expires (SQL Server Express 2005)

I am having trouble creating a primary key on a large table (6 mil
rows)... on a SQL Server Express 2005 database...
After I make the changes to the table and Save, the operation starts
but times out after about 30 seconds, saying that the timeout has
expired.
I tried looking for a timeout that would govern this but I have not
been able to find one...
My query execution timeout is set to 0 (which is unlimited), though it
should be irrelevant because this is not a query.... (in fact, I have
no problem running quries that take a few mins)...
Any idea where/what I need to change to resolve this'
Thanks in advance.<sdragolov@.gmail.com> wrote in message
news:1159639554.270613.108730@.c28g2000cwb.googlegroups.com...
>I am having trouble creating a primary key on a large table (6 mil
> rows)... on a SQL Server Express 2005 database...
> After I make the changes to the table and Save, the operation starts
> but times out after about 30 seconds, saying that the timeout has
> expired.
> I tried looking for a timeout that would govern this but I have not
> been able to find one...
> My query execution timeout is set to 0 (which is unlimited), though it
> should be irrelevant because this is not a query.... (in fact, I have
> no problem running quries that take a few mins)...
> Any idea where/what I need to change to resolve this'
>
What tool are you using to run the query? Can you get a script of the
change and run it in a query window in SQL Server Management Studio?
David|||Well, in theory it's not a "query", right' It's a DDL...
I am using Management Studio - I right click on the table,and do
Modify, then I select the two fields and right click and do "Primary
Key"... Then I finally do Save and that's where the error comes after
about 30 seconds...
I guess I could get the DDL text of adding these PKs and then try to
run that in a query window... though why would that be any
different...'
David Browne wrote:
> <sdragolov@.gmail.com> wrote in message
> news:1159639554.270613.108730@.c28g2000cwb.googlegroups.com...
>
> What tool are you using to run the query? Can you get a script of the
> change and run it in a query window in SQL Server Management Studio?
> David|||<sdragolov@.gmail.com> wrote in message
news:1159647740.250757.210120@.c28g2000cwb.googlegroups.com...
> Well, in theory it's not a "query", right' It's a DDL...
> I am using Management Studio - I right click on the table,and do
> Modify, then I select the two fields and right click and do "Primary
> Key"... Then I finally do Save and that's where the error comes after
> about 30 seconds...
> I guess I could get the DDL text of adding these PKs and then try to
> run that in a query window... though why would that be any
> different...'
Because it's the client tool that generates the timeout, not the server. If
Management Studio will give you the DDL, just run it in a new query window.
David|||David - your suggestion did work... Thanks!
David Browne wrote:
> <sdragolov@.gmail.com> wrote in message
> news:1159647740.250757.210120@.c28g2000cwb.googlegroups.com...
> Because it's the client tool that generates the timeout, not the server.
If
> Management Studio will give you the DDL, just run it in a new query window
.
> David

Saturday, February 25, 2012

Cannot Connect using IP to Clustered Named Instance

I am having trouble connecting when using the IP address to a named
instanced using dynamic port assignments and hosted on a 2K3 cluster (even
when at the console on the node hosting the SQL instance). I receive "SQL
Server does not exist or access denied". There are no entries created in
the event log. I am able to connect with no problems using the instance
name. I'm attempting to connect usingQuery Analyzer. Any ideas?
Thanks!
______________________________________
Michael DiGiuseppe
Technical Services - Enterprise
North Carolina Department of Transportation
Century Center - Building B
______________________________________
E-mail correspondence to and from this address
may be subject to North Carolina Public Records
Law "NCGS.Ch.132" and may be disclosed to
third parties by an authorized state official.
Hi Michael,
In order to connect to a Named Instance you need to either:
a. Supply the Servername\InstanceName
b. Supply the Servername, port
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||It seems that you have trouble connecting through TCP/IP. Verify in event log that SQL is listening on TCPIP and not only on Shared Memory / Named Pipes.
To verify that this is a problem from QueryAnalyzer try to connect with tcp:ServerName\InstanceName. If it isn't work then server is not listening on TCP at all...
post back after this and we will see what then...
Rgds
Sinisa Perovic
|||Thanks for the help.
Thanks!
______________________________________
Michael DiGiuseppe
Technical Services - Enterprise
North Carolina Department of Transportation
Century Center - Building B
______________________________________
E-mail correspondence to and from this address
may be subject to North Carolina Public Records
Law "NCGS.Ch.132" and may be disclosed to
third parties by an authorized state official.
"Kevin McDonnell [MSFT]" <kevmc@.online.microsoft.com> wrote in message
news:51hmVwbIEHA.3636@.cpmsftngxa06.phx.gbl...
> Hi Michael,
> In order to connect to a Named Instance you need to either:
> a. Supply the Servername\InstanceName
> b. Supply the Servername, port
>
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>

Sunday, February 19, 2012

Cannot connect to SQL instance without port information

Hi all,

I'm having some trouble with connecting to SQL server instance lately
without adding a port number. I used to be able to always connect to
instance without having to input SQLSERVER,portnumber. However now
this is not the case. I doing this through an ADO connection object,
and creating the connection string. Does anyone know if there was a
recent Microsoft update or patch that would've cause this to stop
allowing me to connect to a SQL instance without having to put in the
port information? Thanks.Flash08 (stuart.karp@.gmail.com) writes:

Quote:

Originally Posted by

I'm having some trouble with connecting to SQL server instance lately
without adding a port number. I used to be able to always connect to
instance without having to input SQLSERVER,portnumber. However now
this is not the case. I doing this through an ADO connection object,
and creating the connection string. Does anyone know if there was a
recent Microsoft update or patch that would've cause this to stop
allowing me to connect to a SQL instance without having to put in the
port information? Thanks.


More likely this is a change in your environment. You don't say which
version of SQL Server you are using, but with SQL 2005 I think the
requirement is that the Browser service is running. On SQL 2000, you
need to be able to access port 1434 on UDP.

In both cases, firewalls may get in the way.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog <esquel@.sommarskog.sewrote in
news:Xns996556346440Yazorman@.127.0.0.1:

Quote:

Originally Posted by

Flash08 (stuart.karp@.gmail.com) writes:

Quote:

Originally Posted by

>I'm having some trouble with connecting to SQL server instance lately
>without adding a port number. I used to be able to always connect to
>instance without having to input SQLSERVER,portnumber. However now
>this is not the case. I doing this through an ADO connection object,
>and creating the connection string. Does anyone know if there was a
>recent Microsoft update or patch that would've cause this to stop
>allowing me to connect to a SQL instance without having to put in the
>port information? Thanks.


>
More likely this is a change in your environment. You don't say which
version of SQL Server you are using, but with SQL 2005 I think the
requirement is that the Browser service is running. On SQL 2000, you
need to be able to access port 1434 on UDP.
>
In both cases, firewalls may get in the way.
>
>


Is there a reason you can't connect using Server\Instance type syntax?

Cannot connect to SQL Express from my Application

Hey everyone, I'm having trouble connecting to my sql express instance through my vb.net application. I can connect to it from Visual Studio and the management tools, but get an error when connection with a connection string. What should my connection string look like? I'm using the a default SQLEXPRESS instance.

Can you try connection string like this "server=.\\SQLExpress;Trusted_Connection=true;"

|||I mean "server=.\SQLExpress;Trusted_Connection=true;"|||no luck so far. Just timeouts.. aarrrrr.

Tuesday, February 14, 2012

Cannot connect to Named Instance

I'm having trouble connecting to a named instance on a SQL 2000 EE cluster
(Windows 2003). I have no problems connecting to the first virtual server by
name or IP address, but I can only connect to the named instance by using
the IP address and specifying a port (192.168.0.1, 1434).
I can connect to the named instance by IP or name from both cluster nodes,
the problem only arises with remote clients. The client machine I am testing
from is running Windows XP SP2, MDAC 2.8
Thanks in advance,
Curtis
Curtis O. Bouknight wrote:
> I'm having trouble connecting to a named instance on a SQL 2000 EE cluster
> (Windows 2003). I have no problems connecting to the first virtual server by
> name or IP address, but I can only connect to the named instance by using
> the IP address and specifying a port (192.168.0.1, 1434).

> I can connect to the named instance by IP or name from both cluster nodes,

> the problem only arises with remote clients. The client machine I am testing
> from is running Windows XP SP2, MDAC 2.8
>
1433 is the default sql port. The problem could be with the udp listener
which tells the client where the instance is running.
when you installed the named instance you created an alias on both nodes.
Did you remove these, and if so does the sql agent connect with lpc or is it
still using named pipes? If so clean the registry
HKLM\SOFTWARE\Microsoft\MSSQLServer\Client\SuperSo cketNetLib\LastConnect
and restart sql agent and see it fail?
Are both sqlservices running as local admin?
Hans
|||Welcome to my world.
This issue has been ongoing for me with Microsoft for almost a year now.
They have bounced around from it being a SQL Problem, to a network problem,
back to a SQL Problem, now they are looking at an OS problem. If you call
support, be ready to take system dumps, netstats, captures, etc...
One thing you can do to "verify" that you have the problem is to take a
tasklist and a netstat -aon on each node, then match the PID's up. One will
not have a listener on UDP 1434, and the other will. We had the same
scenario for quite some time, then suddenly the other node started exhibiting
the same behavior. We now tell customers to just go directly to the TCP/IP
port for both nodes to solve any confusion down the way.
js
"Curtis O. Bouknight" wrote:

> I'm having trouble connecting to a named instance on a SQL 2000 EE cluster
> (Windows 2003). I have no problems connecting to the first virtual server by
> name or IP address, but I can only connect to the named instance by using
> the IP address and specifying a port (192.168.0.1, 1434).
> I can connect to the named instance by IP or name from both cluster nodes,
> the problem only arises with remote clients. The client machine I am testing
> from is running Windows XP SP2, MDAC 2.8
> Thanks in advance,
> Curtis
>
>
>

Sunday, February 12, 2012

Cannot connect to Local SQL Server

I am having trouble connecting to my local sql server from either the Server
Explorer or vb.net code. The problem is connected to the Norton Internet
Security 2005 Firewall as when I disable Norton everything works and if
Norton is enabled but the PC is not plugged into the network it also works.
When I am connected to the network the Firewall does pop up asking whether
or not I want to allow my vb.net program to access a DNS. I have tried using
localhost, the hardcoded IP of my machine, and my machine name (the usual
method) without any success. It is as if Visual Studio always tries to
resolve the machine name and cannot. VB6 programs ,dbArtisan, and SQL
Enterprise Manager all work fine at the same time Visual Studio/VB.Net does
not.
Any ideas?
Thanks,
Mark Lewis
mdlewis@.mdlewis.com
P.S I am using the latest production/old Visual Studio and SQL Server on XP
with all SP applied.here's an idea: configure the Norton product so that your machine can access
DNS.
-- Nick Malik [Microsoft]
MCSD, CFPS, Certified Scrummaster
http://blogs.msdn.com/nickmalik
Disclaimer: Opinions expressed in this forum are my own, and not
representative of my employer.
I do not answer questions on behalf of my employer. I'm just a
programmer helping programmers.
--
"Mark Lewis" <mdlewis@.mdlewis.com> wrote in message
news:u$P2swmHFHA.3336@.TK2MSFTNGP10.phx.gbl...
>I am having trouble connecting to my local sql server from either the
>Server Explorer or vb.net code. The problem is connected to the Norton
>Internet Security 2005 Firewall as when I disable Norton everything works
>and if Norton is enabled but the PC is not plugged into the network it also
>works.
> When I am connected to the network the Firewall does pop up asking whether
> or not I want to allow my vb.net program to access a DNS. I have tried
> using localhost, the hardcoded IP of my machine, and my machine name (the
> usual method) without any success. It is as if Visual Studio always tries
> to resolve the machine name and cannot. VB6 programs ,dbArtisan, and SQL
> Enterprise Manager all work fine at the same time Visual Studio/VB.Net
> does not.
> Any ideas?
> Thanks,
> Mark Lewis
> mdlewis@.mdlewis.com
> P.S I am using the latest production/old Visual Studio and SQL Server on
> XP with all SP applied.
>|||Resubmitting this with my new MSDN Alias.
Thanks,
Mark Lewis
mdlewis@.mdlewis.com
"Mark Lewis" <mdlewis@.mdlewis.com> wrote in message
news:u$P2swmHFHA.3336@.TK2MSFTNGP10.phx.gbl...
>I am having trouble connecting to my local sql server from either the
>Server Explorer or vb.net code. The problem is connected to the Norton
>Internet Security 2005 Firewall as when I disable Norton everything works
>and if Norton is enabled but the PC is not plugged into the network it also
>works.
> When I am connected to the network the Firewall does pop up asking whether
> or not I want to allow my vb.net program to access a DNS. I have tried
> using localhost, the hardcoded IP of my machine, and my machine name (the
> usual method) without any success. It is as if Visual Studio always tries
> to resolve the machine name and cannot. VB6 programs ,dbArtisan, and SQL
> Enterprise Manager all work fine at the same time Visual Studio/VB.Net
> does not.
> Any ideas?
> Thanks,
> Mark Lewis
> mdlewis@.mdlewis.com
> P.S I am using the latest production/old Visual Studio and SQL Server on
> XP with all SP applied.
>|||Resubmitting this with my new MSDN Alias to qualify for support.
Thanks and sorry about the double post,
Mark Lewis
mdlewis@.mdlewis.com
"Mark Lewis" <mdlewis@.mdlewis.com> wrote in message
news:u$P2swmHFHA.3336@.TK2MSFTNGP10.phx.gbl...
>I am having trouble connecting to my local sql server from either the
>Server Explorer or vb.net code. The problem is connected to the Norton
>Internet Security 2005 Firewall as when I disable Norton everything works
>and if Norton is enabled but the PC is not plugged into the network it also
>works.
> When I am connected to the network the Firewall does pop up asking whether
> or not I want to allow my vb.net program to access a DNS. I have tried
> using localhost, the hardcoded IP of my machine, and my machine name (the
> usual method) without any success. It is as if Visual Studio always tries
> to resolve the machine name and cannot. VB6 programs ,dbArtisan, and SQL
> Enterprise Manager all work fine at the same time Visual Studio/VB.Net
> does not.
> Any ideas?
> Thanks,
> Mark Lewis
> mdlewis@.mdlewis.com
> P.S I am using the latest production/old Visual Studio and SQL Server on
> XP with all SP applied.
>

Cannot connect to Local SQL Server

I am having trouble connecting to my local sql server from either the Server
Explorer or vb.net code. The problem is connected to the Norton Internet
Security 2005 Firewall as when I disable Norton everything works and if
Norton is enabled but the PC is not plugged into the network it also works.
When I am connected to the network the Firewall does pop up asking whether
or not I want to allow my vb.net program to access a DNS. I have tried using
localhost, the hardcoded IP of my machine, and my machine name (the usual
method) without any success. It is as if Visual Studio always tries to
resolve the machine name and cannot. VB6 programs ,dbArtisan, and SQL
Enterprise Manager all work fine at the same time Visual Studio/VB.Net does
not.
Any ideas?
Thanks,
Mark Lewis
mdlewis@.mdlewis.com
P.S I am using the latest production/old Visual Studio and SQL Server on XP
with all SP applied.
here's an idea: configure the Norton product so that your machine can access
DNS.
-- Nick Malik [Microsoft]
MCSD, CFPS, Certified Scrummaster
http://blogs.msdn.com/nickmalik
Disclaimer: Opinions expressed in this forum are my own, and not
representative of my employer.
I do not answer questions on behalf of my employer. I'm just a
programmer helping programmers.
"Mark Lewis" <mdlewis@.mdlewis.com> wrote in message
news:u$P2swmHFHA.3336@.TK2MSFTNGP10.phx.gbl...
>I am having trouble connecting to my local sql server from either the
>Server Explorer or vb.net code. The problem is connected to the Norton
>Internet Security 2005 Firewall as when I disable Norton everything works
>and if Norton is enabled but the PC is not plugged into the network it also
>works.
> When I am connected to the network the Firewall does pop up asking whether
> or not I want to allow my vb.net program to access a DNS. I have tried
> using localhost, the hardcoded IP of my machine, and my machine name (the
> usual method) without any success. It is as if Visual Studio always tries
> to resolve the machine name and cannot. VB6 programs ,dbArtisan, and SQL
> Enterprise Manager all work fine at the same time Visual Studio/VB.Net
> does not.
> Any ideas?
> Thanks,
> Mark Lewis
> mdlewis@.mdlewis.com
> P.S I am using the latest production/old Visual Studio and SQL Server on
> XP with all SP applied.
>
|||Resubmitting this with my new MSDN Alias.
Thanks,
Mark Lewis
mdlewis@.mdlewis.com
"Mark Lewis" <mdlewis@.mdlewis.com> wrote in message
news:u$P2swmHFHA.3336@.TK2MSFTNGP10.phx.gbl...
>I am having trouble connecting to my local sql server from either the
>Server Explorer or vb.net code. The problem is connected to the Norton
>Internet Security 2005 Firewall as when I disable Norton everything works
>and if Norton is enabled but the PC is not plugged into the network it also
>works.
> When I am connected to the network the Firewall does pop up asking whether
> or not I want to allow my vb.net program to access a DNS. I have tried
> using localhost, the hardcoded IP of my machine, and my machine name (the
> usual method) without any success. It is as if Visual Studio always tries
> to resolve the machine name and cannot. VB6 programs ,dbArtisan, and SQL
> Enterprise Manager all work fine at the same time Visual Studio/VB.Net
> does not.
> Any ideas?
> Thanks,
> Mark Lewis
> mdlewis@.mdlewis.com
> P.S I am using the latest production/old Visual Studio and SQL Server on
> XP with all SP applied.
>
|||Resubmitting this with my new MSDN Alias to qualify for support.
Thanks and sorry about the double post,
Mark Lewis
mdlewis@.mdlewis.com
"Mark Lewis" <mdlewis@.mdlewis.com> wrote in message
news:u$P2swmHFHA.3336@.TK2MSFTNGP10.phx.gbl...
>I am having trouble connecting to my local sql server from either the
>Server Explorer or vb.net code. The problem is connected to the Norton
>Internet Security 2005 Firewall as when I disable Norton everything works
>and if Norton is enabled but the PC is not plugged into the network it also
>works.
> When I am connected to the network the Firewall does pop up asking whether
> or not I want to allow my vb.net program to access a DNS. I have tried
> using localhost, the hardcoded IP of my machine, and my machine name (the
> usual method) without any success. It is as if Visual Studio always tries
> to resolve the machine name and cannot. VB6 programs ,dbArtisan, and SQL
> Enterprise Manager all work fine at the same time Visual Studio/VB.Net
> does not.
> Any ideas?
> Thanks,
> Mark Lewis
> mdlewis@.mdlewis.com
> P.S I am using the latest production/old Visual Studio and SQL Server on
> XP with all SP applied.
>

Friday, February 10, 2012

Cannot connect remotely to SQLExpress via SQL Server Management Studio Express

Hi all,

I'm having a little trouble connecting to a SQLExpress instance on a remote domain using SQL Server Management Studio Express.

I've read that by default the program is set up locally only, so have edited the Surface Area Configuration. It only gives me the option to edit 'Services and Connections' on the localhost, but have edited the Remote Connections to use TCP/IP and Named Pipes.


In the Management Studio, I enter the server name, and login credentials. On the Connection Properties tab I changed the network protocol to TCP/IP though everytime I attempt to connect I get the following error message:



Any help would be greatly appreciated.


Rich

Hello,

Does this help you?http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

Regards,

Louis

|||

Hmm, I followed the steps through, the only difference seemed the firewall exceptions which I have now edited, but still get the same error message.

Any ideas?

|||

from the client machine, can you ping the database server? Can you log the denial on the sql server box when you connect to it?

|||

How would I go about doing this?

Cheers