SQL Server 6.5, Unable to Connect, SQL Server is Unavailable
-
Hi,
I am getting this error message "A connection could not be established to FEPNT1 - [DB-Library] Unable to connect: SQL Server is Unavailable or does not exist."
I can't connect to any databases, and I was told that the transaction log is full (Error 1105)
I was also told to increase the size of the transaction log or truncate the transaction log. I was also told that I can
increase the size of the transaction log using the ALTER DATABASE statement. I was also told to truncate the transaction log,
I can set the Truncate Log On Checkpoint database option, or run the DUMP TRANSACTION command with NO_LOG parameter.
Since i can't connect to any database by any method, including Query Analyzer, how can I get to my databases in the first place so I can truncate my log file(s) ?
Can I hack into SQL Server, namely going to the OS and copying the database file(s) and running a command line executable to truncate the log file(s)
since I can't get to them via any windows interface? Please help give direction here.
Thanks!
-
This was removed by the editor as SPAM
-
What tool are you using ? What user ID ? If the user ID is 'sa' and you can't get in then you have real issues since it's default database is master and if the master database transaction log is full then nothing works <PERIOD>. Your only recourse may be to shut down the services and restart them. Just make sure that you've got immediate access to the latest backup files for all databases. I only mention this because a shutdownmight not occur due to open transactions. Since you cannot log in to the SQL Server and issue a 'SHUTDOWN with NOWAIT' you'll be forced to use the Service Control Manager... and if it takes forever, then you may be forced to just poser down the server. The comes automatic recovery (and the potential need for the latest backups).
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Hi,
Using SA trusted connection. Tried shutting down services and restarting, no help. Can't use Service Control Manager either, same error message. Unless there is a way to run SCM from a command line ??
-
What does the SQL Server errorlog have to say ?
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Here you go!!
2006/03/29 11:43:20.76 spid1 Recovering database 'model'
2006/03/29 11:43:20.78 spid1 Recovery dbid 3 ckpt (259,22) oldest tran=(259,0)
2006/03/29 11:43:20.84 spid1 Clearing temp db
2006/03/29 11:43:20.85 kernel initializing virtual device 1, IN_RAM
2006/03/29 11:43:20.96 kernel Read Ahead Manager started.
2006/03/29 11:43:20.96 kernel Using 'SQLEVN60.DLL' version '6.00.000'.
2006/03/29 11:43:21.04 kernel Using 'OPENDS60.DLL' version '6.00.01.02'.
2006/03/29 11:43:21.04 kernel Using 'NTWDBLIB.DLL' version '6.50.409'.
2006/03/29 11:43:21.06 ods Using 'SSNMPN60.DLL' version '6.5.0.0' to listen on '\\.\pipe\sql\query'.
2006/03/29 11:43:21.06 ods Using 'SSMSSO60.DLL' version '6.5.0.0' to listen on '1433'.
2006/03/29 11:43:22.98 spid10 Recovering database 'msdb'
2006/03/29 11:43:22.99 spid11 Recovering database 'pubs'
2006/03/29 11:43:22.99 spid12 Recovering database 'FEDM_dbs'
2006/03/29 11:43:22.99 spid11 Recovery dbid 5 ckpt (779,23) oldest tran=(779,22)
2006/03/29 11:43:22.99 spid10 Recovery dbid 4 ckpt (3591,12) oldest tran=(3591,11)
2006/03/29 11:43:22.99 spid12 Recovery dbid 6 ckpt (2096701,18) oldest tran=(2096701,0)
2006/03/29 11:43:23.03 spid11 1 transactions rolled forward in dbid 5.
2006/03/29 11:43:23.03 spid10 1 transactions rolled forward in dbid 4.
2006/03/29 11:43:23.15 spid1 Recovery complete.
2006/03/29 11:43:23.17 spid1 SQL Server's default sort order is:
2006/03/29 11:43:23.17 spid1 'bin_cp850' (ID = 40)
2006/03/29 11:43:23.17 spid1 on top of default character set:
2006/03/29 11:43:23.17 spid1 'cp850' (ID = 2)
2006/03/29 11:43:23.20 spid1 Launched startup procedure 'sp_sqlregister'
2006/03/29 12:00:00.18 backup TRANSACTION dumped with following info: Database Name:FEDM_dbs, Creation Date and Time:Mar 29, 2006(12:00), Pages dumped:3,
Previous Sequence:38803 11880051, Current Sequence:38803 12960046 Striped:NO, Number of Dump Devices:1, device info
VOLID=SS0007 NAME=fedm_log TYPE=DISK FILE=7)2006/03/29 12:00:00.43 spid10 A history record could not be written to msdb.sysbackuphistory or msdb.sysrestorehistory
2006/03/29 12:00:00.45 spid10 Error : 1105, Severity: 17, State: 1
2006/03/29 12:00:00.45 spid10 Can't allocate space for object 'sysbackuphistory' in database 'msdb' because the 'default' segment is full.
If you ran out of space in Syslogs, dump the transaction log. Otherwise, use ALTER DATABASE or sp_extendsegment to increase the size of the segment.
2006/03/29 12:27:19.26 kernel SQL Server terminating due to 'stop' request from Service Control Manager
-
Have you tried connecting from the server console using Windows Authentication ?
The server is listening for named pipes and TCP/IP on port 1433 - both normal.
As for your space problem, it's in the msdb databse. This will not stop things dead in it's tracks. It's just failing to record backup history and any other scheduled event that emanates from the SQL Server Agent. You may want to look into the registry. If memory serves correctly the sa password is located in a key there (it's encrypted though). Just blank it out and try an sa login.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Got it working. Was a network problem, had to do with named pipes vs TCPIP. \
a) NetLogon services were down – were setup Manual – changes to automatic
b) Default protocol for SQL was Names Pipes – changed to TCP/IP
c) Start and stop services using SQL Server Manager.
Thanks all for your help!!!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply