Establishing connection from SQL MMC fails

Hi,
I have Win2003 and SQL server 2000 installed. I have restarted SQL services
and the services crashed and could not be killed. I have killed the
processes manually but when I open SQL Server Enterprise Manager, expand SQL
Server group and click on the desired SQL Server group it is failing and the
MMC becomes not responding.
It fails on 'Establing connection to SQL Server and checking Windows
security'
I have tried to telnet on the respective SQL Server port and the port
responds ok. I have also done a netstat and the server is listening on the
respective port.
From the sql error logs, the following information is being logged
2007-09-26 16:45:04.32 spid8 63 transactions rolled forward in database
'msdb' (4)
2007-09-26 16:45:34.04 spid5 Starting up database 'tempdb'
2007-09-26 16:50:33.06 spid8 Time out occurred while waiting for buffer
latch type 4,bp 0xf97e40, page 1:1508), stat 0x9, object ID 4:2073058421:0,
EC 0x42CAC3F0 : 0, waittime 3000. Continuing to wait.
2007-09-26 16:50:33.06 spid8 Waiting for type 0x4, current count
0x10000a, current owning EC 0x42CAC3F0.
Your suggetions will be highly appreciated.Dear Quintin,
Thank you for posting here.
From the problem description, I understand that the error message
"Establish connection to SQL Server and checking Windows security" is
encountered when trying to connect to the SQL Server 2000 via the
Enterprise Manage. If I have misunderstood about your concern, feel free to
let me know.
Before moving on, I suggest that you try the following method to check if
you could connect via osql.exe
1. Click Start, click Run, type cmd, and then click OK
2. At the command prompt, type the following command, and then press ENTER.
OSQL -E -S <server name>\<instance name>
OSQL -E -S tcp:<computer name>,<port number>
Also, you could try SQL authentication by using sa
OSQL -S <server name>\<instance name> -Usa -P<password>
3. Please check if we can successfully connect to the instance of SQL
Server 2000 by using osql.exe.
If any error message appears, please let me know the extract error message
we received.
Please also help me gather the following information for the further
research:
Information One: SQL MPS Report
=====================Please help create a MPSReport on the problematic machine, which will
collect ERRORLOG, Windows event log and other helpful information about the
SQL Server. To create a MPSReport, please visit the following web site:
http://www.microsoft.com/downloads/details.aspx?FamilyId=CEBF3C7C-7CA5-408F-
88B7-F9C79B7306C0&displaylang=en
And look for MPSRPT_SQL.EXE. Download it and run it on the machine. Dismiss
the prompted dialog boxes and the readme window (usually a Notepad) after
you read them. After the Command Prompt window closes itself, collect the
cab file the tool has generated and sent it to me.
Information Two: Screenshot
=====================Please also help me to capture a screenshot when the error message
"Establish connection to SQL Server and checking Windows security" appears.
1. When the error appears, please press the Print Screen key (PrtScn) on
your keyboard.
2. Click Start, click Run, type MSPAINT, and click OK.
3. In Paint, click Paste under the Edit Menu, click Save under the File
menu, type a file name for the snap shot, choose JPEG as "Save as type",
click "Desktop" on the left pane, and click Save.
Please send above files to me at v-adamqu@.microsoft.com
If anything is unclear in my post, please don't hesitate to let me know and
I will be glad to help. Thank you for your efforts and time.
Have a nice day!
Best regards,
Adams Qu
MCSE, MCDBA, MCTS
Microsoft Online Support
Microsoft Global Technical Support Center
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.
--
| From: "Quintin Zammit" <qztarantino@.newsgroup.nospam>
| Subject: Establishing connection from SQL MMC fails
| Date: Wed, 26 Sep 2007 19:27:40 +0200
| Lines: 30
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3138
| X-RFC2646: Format=Flowed; Original
| Message-ID: <eNQDMKGAIHA.4880@.TK2MSFTNGP03.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: 80.85.100.38
| Path: TK2MSFTNGHUB02.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP03.phx.gbl
| Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:26395
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Hi,
|
| I have Win2003 and SQL server 2000 installed. I have restarted SQL
services
| and the services crashed and could not be killed. I have killed the
| processes manually but when I open SQL Server Enterprise Manager, expand
SQL
| Server group and click on the desired SQL Server group it is failing and
the
| MMC becomes not responding.
|
| It fails on 'Establing connection to SQL Server and checking Windows
| security'
|
| I have tried to telnet on the respective SQL Server port and the port
| responds ok. I have also done a netstat and the server is listening on
the
| respective port.
|
| From the sql error logs, the following information is being logged
|
| 2007-09-26 16:45:04.32 spid8 63 transactions rolled forward in
database
| 'msdb' (4)
| 2007-09-26 16:45:34.04 spid5 Starting up database 'tempdb'
| 2007-09-26 16:50:33.06 spid8 Time out occurred while waiting for
buffer
| latch type 4,bp 0xf97e40, page 1:1508), stat 0x9, object ID
4:2073058421:0,
| EC 0x42CAC3F0 : 0, waittime 3000. Continuing to wait.
| 2007-09-26 16:50:33.06 spid8 Waiting for type 0x4, current count
| 0x10000a, current owning EC 0x42CAC3F0.
|
| Your suggetions will be highly appreciated.
|
|
|
||||Dear Quintin,
I am just writing to see if you have had the opportunity to try the
troubleshooting steps or collect the requested information. If you have any
questions with the previous information I've provided, please don't
hesitate to let me know.
I appreciate your time and look forward to hearing from you.
Have a nice day!
Best regards,
Adams Qu
MCSE, MCDBA, MCTS
Microsoft Online Support
Microsoft Global Technical Support Center
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.
| X-Tomcat-ID: 141089102
| References: <eNQDMKGAIHA.4880@.TK2MSFTNGP03.phx.gbl>
| MIME-Version: 1.0
| Content-Type: text/plain
| Content-Transfer-Encoding: 7bit
| From: v-adamqu@.online.microsoft.com (Adams Qu [MSFT])
| Organization: Microsoft
| Date: Thu, 27 Sep 2007 09:48:43 GMT
| Subject: RE: Establishing connection from SQL MMC fails
| X-Tomcat-NG: microsoft.public.sqlserver.server
| Message-ID: <TcGwYuOAIHA.5204@.TK2MSFTNGHUB02.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| Lines: 126
| Path: TK2MSFTNGHUB02.phx.gbl
| Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:26437
| NNTP-Posting-Host: tomcatimport2.phx.gbl 10.201.218.182
|
| Dear Quintin,
|
| Thank you for posting here.
|
| From the problem description, I understand that the error message
| "Establish connection to SQL Server and checking Windows security" is
| encountered when trying to connect to the SQL Server 2000 via the
| Enterprise Manage. If I have misunderstood about your concern, feel free
to
| let me know.
|
| Before moving on, I suggest that you try the following method to check if
| you could connect via osql.exe
|
| 1. Click Start, click Run, type cmd, and then click OK
| 2. At the command prompt, type the following command, and then press
ENTER.
|
|
| OSQL -E -S <server name>\<instance name>
| OSQL -E -S tcp:<computer name>,<port number>
|
| Also, you could try SQL authentication by using sa
|
| OSQL -S <server name>\<instance name> -Usa -P<password>
|
| 3. Please check if we can successfully connect to the instance of SQL
| Server 2000 by using osql.exe.
|
| If any error message appears, please let me know the extract error
message
| we received.
|
| Please also help me gather the following information for the further
| research:
|
| Information One: SQL MPS Report
| =====================| Please help create a MPSReport on the problematic machine, which will
| collect ERRORLOG, Windows event log and other helpful information about
the
| SQL Server. To create a MPSReport, please visit the following web site:
|
http://www.microsoft.com/downloads/details.aspx?FamilyId=CEBF3C7C-7CA5-408F-
| 88B7-F9C79B7306C0&displaylang=en
|
| And look for MPSRPT_SQL.EXE. Download it and run it on the machine.
Dismiss
| the prompted dialog boxes and the readme window (usually a Notepad) after
| you read them. After the Command Prompt window closes itself, collect the
| cab file the tool has generated and sent it to me.
|
| Information Two: Screenshot
| =====================| Please also help me to capture a screenshot when the error message
| "Establish connection to SQL Server and checking Windows security"
appears.
|
| 1. When the error appears, please press the Print Screen key (PrtScn) on
| your keyboard.
| 2. Click Start, click Run, type MSPAINT, and click OK.
| 3. In Paint, click Paste under the Edit Menu, click Save under the File
| menu, type a file name for the snap shot, choose JPEG as "Save as type",
| click "Desktop" on the left pane, and click Save.
|
| Please send above files to me at v-adamqu@.microsoft.com
|
| If anything is unclear in my post, please don't hesitate to let me know
and
| I will be glad to help. Thank you for your efforts and time.
|
| Have a nice day!
|
| Best regards,
|
| Adams Qu
| MCSE, MCDBA, MCTS
| Microsoft Online Support
|
| Microsoft Global Technical Support Center
|
| Get Secure! - www.microsoft.com/security
| =====================================================| When responding to posts, please "Reply to Group" via your newsreader so
| that others may learn and benefit from your issue.
| =====================================================| This posting is provided "AS IS" with no warranties, and confers no
rights.
|
| --
| | From: "Quintin Zammit" <qztarantino@.newsgroup.nospam>
| | Subject: Establishing connection from SQL MMC fails
| | Date: Wed, 26 Sep 2007 19:27:40 +0200
| | Lines: 30
| | X-Priority: 3
| | X-MSMail-Priority: Normal
| | X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
| | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3138
| | X-RFC2646: Format=Flowed; Original
| | Message-ID: <eNQDMKGAIHA.4880@.TK2MSFTNGP03.phx.gbl>
| | Newsgroups: microsoft.public.sqlserver.server
| | NNTP-Posting-Host: 80.85.100.38
| | Path: TK2MSFTNGHUB02.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP03.phx.gbl
| | Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:26395
| | X-Tomcat-NG: microsoft.public.sqlserver.server
| |
| | Hi,
| |
| | I have Win2003 and SQL server 2000 installed. I have restarted SQL
| services
| | and the services crashed and could not be killed. I have killed the
| | processes manually but when I open SQL Server Enterprise Manager,
expand
| SQL
| | Server group and click on the desired SQL Server group it is failing
and
| the
| | MMC becomes not responding.
| |
| | It fails on 'Establing connection to SQL Server and checking Windows
| | security'
| |
| | I have tried to telnet on the respective SQL Server port and the port
| | responds ok. I have also done a netstat and the server is listening on
| the
| | respective port.
| |
| | From the sql error logs, the following information is being logged
| |
| | 2007-09-26 16:45:04.32 spid8 63 transactions rolled forward in
| database
| | 'msdb' (4)
| | 2007-09-26 16:45:34.04 spid5 Starting up database 'tempdb'
| | 2007-09-26 16:50:33.06 spid8 Time out occurred while waiting for
| buffer
| | latch type 4,bp 0xf97e40, page 1:1508), stat 0x9, object ID
| 4:2073058421:0,
| | EC 0x42CAC3F0 : 0, waittime 3000. Continuing to wait.
| | 2007-09-26 16:50:33.06 spid8 Waiting for type 0x4, current count
| | 0x10000a, current owning EC 0x42CAC3F0.
| |
| | Your suggetions will be highly appreciated.
| |
| |
| |
| |
|
|

Errors attaching a database after tlog filled.

I have a database, "heat", that crashed after the transaction log filled the HDD.
Since then I have been unsuccessful in bring the database back up.

I'm not concerned with restoring what's in the transaction log, only the data file.

The current state is as follows:

The database is not attached.

When I try to attach the database using the transaction log (I made some additional room on the drive), I receive "Error 9003..."

I noticed in the forum the same error, so I tried the fix provided which was to attach only the data file. I renamed the log file, and tried to attach the data file but received the following error:

Error 1813: Could not open new database "heat". CREATE DATABASE is aborted.
Device activation error. The physical file name "D:\Microsoft SQL Server\MSSQL\data\heat_Log.LDF may be incorrect.

Any help would br greatly appreciated.
-AndyWhat commands are you using to attatch the database ??|||I'm using the Attach database command in enterprise manager.

Ok, I restored a backup of the database, then pulled a switcharoo (I have copies of all of the files, so I'm not overwriting anything).
The database is now suspect. Any ideas?|||try using sp_attach_single_file_db 'dbname','Physical path of the MDF file'

dont know about EM though , never use it at all|||Originally posted by monza700
When I try to attach the database using the transaction log (I made some additional room on the drive), I receive "Error 9003..."


Just a tad confusing...you don't attach using the tranny...

you don't mean that, right?

How did you clean up the hard drive?

What % is left

You need to set up alerts to tell you you're at a threshhold...

How big is the db btw, and the hard drive?|||I tried running the sp_attach_single_file_db command and received the following:

Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'heat'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'D:\Microsoft SQL Server\MSSQL\data\heat_Log.LDF' may be incorrect.

Thanks for any/all help.|||In response:

Q: you don't mean that, right?
A: No, sorry, I'm trying to attach the mdf file.
Q: How did you clean up the hard drive?
A: deleted unneeded backups that were on the same drive.
Q: What % is left
A: 6GB
Q: You need to set up alerts to tell you you're at a threshhold...
A: I will do that once I get the database fixed (hindsight)
Q: How big is the db btw, and the hard drive? [/SIZE][/QUOTE]
A: about 1GB.|||First, you restore the mdf

Second, I think (only on rare occasions that is), to attach, you first need to have dettached...

Why not just do a RESTORE?

Like:

ALTER DATABASE TaxReconDB_Prod SET SINGLE_USER WITH ROLLBACK IMMEDIATE

RESTORE DATABASE TaxReconDB_Prod
FROM DISK = 'D:\Tax\BackUp\TaxReconDB.dmp'
WITH MOVE 'TaxReconDB_Data' TO 'D:\Database\NJROS1D151DEV\MSSQL$NJROS1D151DEV\Dat a\TaxReconDB_Prod_Data.MDF'
, MOVE 'TaxReconDB_Log' TO 'D:\Database\NJROS1D151DEV\MSSQL$NJROS1D151DEV\Dat a\TaxReconDB_Prod_Log.LDF'
, REPLACE

ALTER DATABASE TaxReconDB_Prod SET READ_WRITE|||If you notice, the restore takes tyhe mdf and the ldf from the last time the database was dumped...

the ldf will contain all uncommitted transactions..

If you did tranny dumps after that, and want/need to recover them, they'll have to be applied as well, in order...|||No dump files (dmp) are available.
Unfortunately I came in sort of after the fact. And some people thought they would try deleting the transaction log.
I also found out that before any of this happened the database was set to FULL recovery mode. Would that be causing any issues?

Also, the database has already be detached.

(I'm feeling a Microsoft Tech Support call coming up).

Thanks again,
Andy|||There's nothing in

D:\Microsoft SQL Server\MSSQL\data\

with a .mdf extension?

(or ,bkp perhaps?)

Can you look in Enterprise manager to see if there are any maint. plans set up., or jobs running maint. stored procedures?

That's very odd...

Tecjh support can't help you with what you don't have...

And Database recovery FULL is no problem...just have to make sure to dump the tranny logs with a scheduled job...

If it's no big deal you could use simple...

Then just dump the database...

What did this poor database do, to deserve this?

And what did use to contain, just out curiosity?|||Ok, the solution went something like this:
Restored an old backup of the database.

Stopped the SQL service.

Replaced the new datafile with the old data file.

Started the SQL service.

Ran the following code on it (placed the database into a readonly, single user, dbo state a.k.a. Emergency state):
sp_configure 'allow updates',1
go
reconfigure with override
go
update sysdatabases
set status=-32768
where name = 'heat'
go
sp_configure 'allow updates',0
go
reconfigure with override
go

Copied all of the objects and data from the bad database into a new one.

Deleted the bad database.

Copied all of the objects and data from the new database into another database with the name of the original.

Everything is A-O.K.

Thanks again for all of your help.

Andy