I opened SQL Server management studio and tried to connect with the local database and received the error message:
Database 'msdb' cannot be opened. It has been marked suspect.....
I have pasted the only error from the event log I could find below, but it doesn't mean much to me.
In the event I can't find what the problem is, and not being a database administrator, can anyone tell me what I can do to get back in working order without messing up all the databases in my SQL Server 2005. I'm afraid I only have a MASTER backup from several months ago, and am afraid if I use this, I will loose the latest data. I'm a novice, and do not understand the relationship of the master/msdb/model databases to my core custom databases. Can I do some sort of master rebuild without affecting the other databases I have?
I have tried sp_resetstatus but this didn't fix the problem, but it did enable me to actually connect to the SQL server instance so I could access all the other databases.
Many thanks
Gerard
Event log error detail:
The log scan number (152:284:1) passed to log scan in database 'msdb' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.
I'am doing functionality test on DTS packages and saving my DTS packages to meta data services instead of saving them as local packages. We would like to see what information would be provided by saving them this way, but when we try to open the meta data browser (the 3rd icon under DTS) we get the following error:
An error occurred while trying to access the database information. The msdb database could not be opened.
If you are having trouble accessing Meta Data Services on W2k3 and SQL 2000 and get the error that the "msdb database could not be opened" I found that hot fix 912812 on the operating system is the culprit. Remove it and Meta Data goes back to working, although I am being asked to approve the ActiveX control every time the page refreshes.
Hope this helps somebody, to took me long enough to track it down.
If anyone has a resolution to the ActiveX question I'd love to know the answer.
I'm working on a SQL Server 2005 mirroring database test.The idea is to stop main DB server and activate the standby being mirrored (pretendng that principal DB Server is down)
Mirror works fine. But for the test I do the following:
- Stop the principal SQLSERVER - Connect to the standby server and execute the followng:
Hello,Could you explain me please the following error:"Database 'DemoDotNET' cannot be opened due to inaccessible files orinsufficient memory or disk space. (Microsoft SQL Server, Error: 945)"I have checked: free disk space is large enough, I have much enoughmemory, other files in the directory are accessible.Here is tail of ERRORLOG:2006-06-12 21:19:55.68 Logon Error: 18456, Severity: 14, State:16.2006-06-12 21:19:55.68 Logon Login failed for user 'PCRobert'.[CLIENT: <local machine>]2006-06-12 21:20:43.39 Server Server resumed execution afterbeing idle 1 seconds: user activity awakened the server. This is aninformational message only. No user action is required.Please help to solve this.Thank you./RAM/
When I try the open the report file, I got the following error message. Database 'ReportServer$SQLExpressTempDB' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details. Anyony have ideas to fix this error?
I have a view saved on server - mhsvi-datawarehousedatawarehouse.This view, in it's TSQL connects to a databasethat is set up as a linked server. That server is mhsvi-sql2008ainstance1.When I try to add the view to Excel in order to automatically refresh for users as a report - I get the following error - (I get it as well)
The query did not run, or the database table could not be opened.Check the database server or contact your database administrator. Make sure the external database is available and hasn't been moved or reorganized, then try the operation again.I have access to the database where the view is saved and the database that the TSQL calls.
Can anyone point out what's wrong with the following script? I wanted to print all the LOGFILE names in all databases which are not in SIMPLE recovery model. Thanks in advance.
Jim
USE masterDECLARE @db_name nvarchar(100), @log_name nvarchar(100);DECLARE DB_Cursor CURSOR FORselect name from msdb.sys.databases where recovery_model_desc <> 'SIMPLE';OPEN DB_Cursor FETCH NEXT FROM DB_Cursor INTO @db_name WHILE @@FETCH_STATUS = 0 BEGIN USE @db_name -- Can I do this? GO select @log_name = name from sys.database_files where type_desc ='LOG'; print @log_name; ENDCLOSE DB_CursorDEALLOCATE DB_CursorI got the following error:Msg 102, Level 15, State 1, Line 13Incorrect syntax near '@db_name'.Msg 137, Level 15, State 1, Line 1Must declare the scalar variable "@log_name".Msg 137, Level 15, State 2, Line 2Must declare the scalar variable "@log_name".
Greetings, I have just arrived back into the country (NZ) and back into ASP.NET. I am having trouble with the following:An attempt to attach an auto-named database for file (file location).../Database.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share. It has only begun since i decided i wanted to use IIS, I realise VWD comes with its own localhost, but since it is only temporary, i wanted a permanent shortcut on my desktop to link to my intranet page. Anyone have any ideas why i am getting the above error? have searched many places on the internet and not getting any closer. Cheers ~ J
I have been playing with a Standard Version of VS 2005 and SSE 2005 and I just cannot get these two to interact together well. I am sure it is a noob problem but I have seen this error addressed on this forum and I am just not getting it. Here is exactly what I am doing.
I want to create a database within SSE using the Management Studio. Then, I want to connect to it with VS2005. Both SSE and VS2005 are local. I just cant seem to get this to work.
I always seem to get this error "An attempt to attach an auto-named database for file C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataSecond.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share."
One thing... I create the website with Location: HTTP. I don't even know anymore why I do this.. I think because I like to be able to pull up a browser and get to my app. Other than that I am doing nothing special. However, I get the above error when I hit F5 to run the app inside VS2005.
Is there someplace where a complete noob can find a thorough and simple covering of this? Again, I have read through some of the previous posts on this and I don't understand too many of them and it seems as if there are at least 13 causes of which I don't know which one I have. Help!
but after the assignment the LocalConnectionString is different from ConnectionStringWithoutCredentials, there are much more ssce parameters.
What I don't understand is:
why I'm getting this error even after closing the connection ?
Since the error report [32] the ssce conflicting parameters shoud be AUTO_SHRINK_THRESHOLD, FLUSH_INTERVAL and MAX_DATABASE_SIZE
I tried to cut & paste the same values from LocalConnectionString into app.config connection string, at first It seemed to work, but now I got the same error.
I have tried to attatch a database ,created by SQL server Express within a C# application , in SQL server 2005 Enterprise edition, but the following error message appears:
-----------------------------
TITLE: Microsoft SQL Server Management Studio ------------------------------
Attach database failed for Server 'MEDO'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft SQL Server&ProdVer=9.00.1187.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach database+Server&LinkId=20476
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The database 'E:X.MDF' cannot be opened because it is version 611. This server supports version 607 and earlier. A downgrade path is not supported. Could not open new database 'E:X.MDF'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 948)
------------------------------
so , how can I solve this problem , I need to reed the data resides in the tables of 'X' database , how can I do it?? please help me.
I have a new server setup running sql2000 as default instance. I wanted to move all the jobs and dts packages from another server which is running multiple instances of sql server. I backed up and restored the msdb database to the new server and now I can't edit any jobs. I get this error? can't edit or delete the job that originated from MSX server. How do I fix this problem. Any help will be appreciated.
Hi Everyone, Couple of days back my server was down because of hardware problem and it was rectefied and sql server 6.5 started yesterday afternoon and the backup schedules was run in demand yesterday but it failed, and some of the schedules which are suppose to take backup in the night also failed today, it was also not showing anything in the schedule history, and when I run the schedule on demand today again it failed, so I created a newdevice and scheduled a newtask and I run in demand and the history shows that:- schedule history (Could not insert a backup or restore history/detail record in msdb.dbo.sysbackuphistory or sysrestorehistory. This may indicate a problem with the MSDB database. DUMP/LOAD was still successful. (Message 3009)). I checked the sysbackuphistory it shows three days back history, can any body give me the solution, what are the things to be checked, please give me the solution. waiting for the immediate reply.
I have to rename some Originating_Server fields in the msdb database. I'm pretty sure I have to run a script that makes the msdb database editable but I can't find it anywhere. Am I wrong?
The SQL server specified in SSIS service configuration is not present or is not available. This might occur when there is no default instance of SQL Server on the computer. For more information, see the topic "Configuring the Integration Services Service" in Server 2005 Books Online.
Login timeout expired An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. Named Pipes Provider: Could not open a connection to SQL Server [2]. (MsDtsSrvr)
------------------------------
Login timeout expired An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. Named Pipes Provider: Could not open a connection to SQL Server [2]. (Microsoft SQL Native Client)
when i try to restore a database called ddaa, i am getting the following message after the restore is completed.. and the database icon in the enterprize manager is having ddaa(loading).now i am not able to access that database..
the message i am getting is..
database msdb cannot be opened due to anaccessible files or insufficient memory or disk space. could not insert a backup/restore history detail record in the msdb database.this may indicate a problem with the msdb database.
Dear Experts, in one of our servers, the MSDB database is in suspect mode, and i've used the command sp_resetstatus and i restarted the service also but still it is in suspect mode.
how can i change that to normal state? it is asking to check the errorlog.
Vinod Even you learn 1%, Learn it with 100% confidence.
My msdb database is marked as suspect. This means that my maintenance plandoesn't work. I'm a novice when it comes to sql server so now I'm wonderingif anyone could give a hint on how to fix the suspect msdb database? Doesthis database contain any vital information? What could be the reason thatthis database become suspect?Appreciate any suggestions in this matter.Thanks!--mvhHallgeir
A client improperly shutdown a PC running MSDE and it corrupted the MSDB database which contained several DTS packages. I have tried fixing the problem on my own but have exhausted all the simple options like restarting SQL Server.
I googled the problem and found the following SQL script:
USE MasterGO-- Determine the original database statusSELECT [Name], DBID, StatusFROM master.dbo.sysdatabasesGO-- Enable system changessp_configure 'allow updates',1GORECONFIGURE WITH OVERRIDEGO-- Update the database statusUPDATE master.dbo.sysdatabasesSET Status = 24WHERE [Name] = 'msdb'GO-- Disable system changessp_configure 'allow updates',0GORECONFIGURE WITH OVERRIDEGO-- Determine the final database statusSELECT [Name], DBID, StatusFROM master.dbo.sysdatabasesGO I ran it and and it generated the log file below. I restarted MS SQL but the database was still marked as suspect (as I would expect from the log file).
Any help in interpreting and recovering from this problem would be greatly appreciated.
Dear Experts, I'm using sql server 2005, and now i'm trying to restore with one backup. then i'm getting error saying that MSDB database in suspect mode.
how to make the DB to the normal mode.... please help me in this regard
Vinod Even you learn 1%, Learn it with 100% confidence.
HiI have a problem creating a backup in SQL Server 6.5. Although, thebackup produced does restore ok, I want to stop this error messageappearing everytime I make a backup. The error message is:"[SQL Server ]Can't allocate space for object 'sysbackupdetail' indatabase 'msdb' because the 'default' segment is full. If you ran outof space in Syslogs, dump the transaction log. Otherwise, use ALTERDATABASE or sp_extendsegment to increase the size of the segment."this articles appears to describe the problem and the solution:http://support.microsoft.com/defaul...kb;EN-US;152354It recommends creating a stored procedure to delete old entries. Thestored procedure cannot be created due tro a memory problem. What Iwas wondering, although the article doesn't mention it, can a newdevice be created and added to the msdb database. This should fix theproblem or is there problems doing this ?Thanks in advance
When I try to delete a job from Enterprise Manager Console I get the following error: Erro 644: Could not find the index entry for RID '163bd10000010000' in index page (1:553), index ID 0 database 'msdb'
Oh and this is on MSDE.
Here is my complete output of DBCC CHECKDB DBCC results for 'msdb'. DBCC results for 'sysobjects'. There are 280 rows in 6 pages for object 'sysobjects'. DBCC results for 'sysindexes'. There are 143 rows in 6 pages for object 'sysindexes'. DBCC results for 'syscolumns'. There are 1567 rows in 26 pages for object 'syscolumns'. DBCC results for 'systypes'. There are 26 rows in 1 pages for object 'systypes'. DBCC results for 'syscomments'. There are 357 rows in 108 pages for object 'syscomments'. DBCC results for 'sysfiles1'. There are 2 rows in 1 pages for object 'sysfiles1'. DBCC results for 'syspermissions'. There are 116 rows in 1 pages for object 'syspermissions'. DBCC results for 'sysusers'. There are 13 rows in 1 pages for object 'sysusers'. DBCC results for 'sysproperties'. There are 0 rows in 0 pages for object 'sysproperties'. DBCC results for 'sysdepends'. There are 1635 rows in 8 pages for object 'sysdepends'. DBCC results for 'sysreferences'. There are 12 rows in 1 pages for object 'sysreferences'. DBCC results for 'sysfulltextcatalogs'. There are 0 rows in 0 pages for object 'sysfulltextcatalogs'. DBCC results for 'sysfulltextnotify'. There are 0 rows in 0 pages for object 'sysfulltextnotify'. DBCC results for 'sysfilegroups'. There are 1 rows in 1 pages for object 'sysfilegroups'. DBCC results for 'backupset'. There are 1045 rows in 44 pages for object 'backupset'. DBCC results for 'sysjobschedules'. There are 7 rows in 1 pages for object 'sysjobschedules'. DBCC results for 'syscategories'. There are 19 rows in 1 pages for object 'syscategories'. DBCC results for 'systargetservers'. There are 0 rows in 0 pages for object 'systargetservers'. DBCC results for 'backupfile'. There are 1451 rows in 24 pages for object 'backupfile'. DBCC results for 'systargetservergroups'. There are 0 rows in 0 pages for object 'systargetservergroups'. DBCC results for 'systargetservergroupmembers'. There are 0 rows in 0 pages for object 'systargetservergroupmembers'. DBCC results for 'restorehistory'. There are 1 rows in 1 pages for object 'restorehistory'. DBCC results for 'sysalerts'. There are 9 rows in 1 pages for object 'sysalerts'. DBCC results for 'sysoperators'. There are 0 rows in 0 pages for object 'sysoperators'. DBCC results for 'sysnotifications'. There are 0 rows in 0 pages for object 'sysnotifications'. DBCC results for 'restorefile'. There are 2 rows in 1 pages for object 'restorefile'. DBCC results for 'systaskids'. There are 0 rows in 0 pages for object 'systaskids'. DBCC results for 'syscachedcredentials'. There are 0 rows in 0 pages for object 'syscachedcredentials'. DBCC results for 'restorefilegroup'. There are 1 rows in 1 pages for object 'restorefilegroup'. DBCC results for 'logmarkhistory'. There are 0 rows in 0 pages for object 'logmarkhistory'. DBCC results for 'sysdtscategories'. There are 3 rows in 1 pages for object 'sysdtscategories'. DBCC results for 'sysdtspackages'. There are 0 rows in 0 pages for object 'sysdtspackages'. DBCC results for 'sysdtspackagelog'. There are 0 rows in 0 pages for object 'sysdtspackagelog'. DBCC results for 'sysdtssteplog'. Server: Msg 8935, Level 16, State 1, Line 1 Table error: Object ID 2073058421, index ID 1. The previous link (1:343) on page (1:371) does not match the previous page (1:382) that the parent (1:300), slot 32 expects for this page. Server: Msg 8978, Level 16, State 1, Line 1 Table error: Object ID 2073058421, index ID 1. Page (1:371) is missing a reference from previous page (1:343). Possible chain linkage problem. There are 0 rows in 0 pages for object 'sysdtssteplog'. DBCC results for 'sysdtstasklog'. There are 0 rows in 0 pages for object 'sysdtstasklog'. DBCC results for 'sysdbmaintplans'. There are 4 rows in 1 pages for object 'sysdbmaintplans'. DBCC results for 'sysdbmaintplan_jobs'. There are 4 rows in 1 pages for object 'sysdbmaintplan_jobs'. DBCC results for 'sysdbmaintplan_databases'. There are 12 rows in 1 pages for object 'sysdbmaintplan_databases'. DBCC results for 'sysdbmaintplan_history'. There are 724 rows in 23 pages for object 'sysdbmaintplan_history'. DBCC results for 'log_shipping_primaries'. There are 0 rows in 0 pages for object 'log_shipping_primaries'. DBCC results for 'log_shipping_secondaries'. There are 0 rows in 0 pages for object 'log_shipping_secondaries'. DBCC results for 'mswebtasks'. There are 0 rows in 0 pages for object 'mswebtasks'. DBCC results for 'sqlagent_info'. There are 0 rows in 0 pages for object 'sqlagent_info'. DBCC results for 'sysdownloadlist'. There are 0 rows in 0 pages for object 'sysdownloadlist'. DBCC results for 'backupmediaset'. There are 1045 rows in 11 pages for object 'backupmediaset'. DBCC results for 'sysjobhistory'. Server: Msg 8935, Level 16, State 1, Line 1 Table error: Object ID 2073058421, index ID 1. The previous link (1:382) on page (1:564) does not match the previous page (1:371) that the parent (1:300), slot 33 expects for this page. There are 626 rows in 208 pages for object 'sysjobhistory'. CHECKDB found 0 allocation errors and 3 consistency errors in table 'sysjobhistory' (object ID 2073058421). DBCC results for 'sysjobs'. There are 7 rows in 1 pages for object 'sysjobs'. DBCC results for 'backupmediafamily'. There are 1045 rows in 20 pages for object 'backupmediafamily'. DBCC results for 'sysjobservers'. There are 7 rows in 1 pages for object 'sysjobservers'. DBCC results for 'sysjobsteps'. There are 9 rows in 1 pages for object 'sysjobsteps'. CHECKDB found 0 allocation errors and 3 consistency errors in database 'msdb'. repair_rebuild is the minimum repair level for the errors found by DBCC CHECKDB (msdb ). DBCC execution completed. If DBCC printed error messages, contact your system administrator.
We are running SQL Server 2000, SP4. I recently noticed that my DTS packages were missing from the local server. Since I had saved them as Structure Storage Files, I imported them back into DTS Local Packages. That was yesterday. Today I opened the Meta Data browser to see what was in it. (We don't use Meta Data Services.) It displayed a message:"An error occurred while trying to access the database information. The msdb database could not be opened." These (restore of DTS pkgs and Meta Data error) may not be related, but I need to know why I am getting this error, because I am about to upgrade to SQL Server 2005 for Workgroups.
1. Does anyone know what causes this error message?
2. Are local DTS packages stored in the MSDB database?
While checking the SQL server error logs, I notice that the pubs and msdb database are automatically being backed up, even though no job is set up to do so....in addition, its backing up to a directory that I cannot find on our network.....does anybody have an idea of whats going on ?
the path its backingup to is: (FILE=1, TYPE=PIPE: {'.pipedbasql70dbagent0s0'}).
I'm just upgrading my business database to SQL 2005 and hit a problem when executing a stored procedure in the msdb database. Error message reading "EXECUTE permission denied on object 'sp_delete_job'".
Obviously this message tells me that I do not have permissions to execute stored procedures in the MSDB database.
In SQL 2000 I never had to mess around with permissions on this database as the security was already pre-configured.
I'm still familiarising myself with SQL 2005 so rather than making a finger in the air guess I'd like to understand how SQL 2000 was configured and the best solution for resolving this in SQL2005.
I've just imported some SSIS packages onto my instance but my users aren't able to run the jobs for these packages (if thats the write terminology) it keeps failing with this error: Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account. The step failed.
I've been looking around and I've been told I can assign the logins to the database roles in msdb but when I look for the roles in msdb they don't exist. SSIS is definitely installed on the server. How do I go about getting these added?