Microsoft Sql Server 2008 Error 945 Msdb Suspect Rating: 9,5/10 508 votes

Fighters

I have received following question nearly 10 times in last week though emails. Many users have received following error while connecting to the database. This error happens when database is dropped for which is default for some of the database user. When user try to login and their default database is dropped following error shows up.

Recreate Corrupt MSDB database. If your msdb goes suspect then you have two choices, restore it from a backup or recreate it (and then recreate any scheduled jobs). SQL Database recovery software can repairs the corrupt data from Microsoft SQL Server 2000, 20005 and 2008 database. SQL Database recovery tool restore all the mdf. Database 'msdb' cannot be opened due to inaccessible files or insufficient memory or disk space. (Microsoft SQL Server, Error: 945) Then also connects to Database. But clients databases are not visible. See the SQL Server errorlog for details. 2016-08-10 18:02:56.17 spid36s Problems recording information in the msdb.suspect_pages table.

Cannot open user default database. Login failed.
Login failed for user ‘UserName’. (Microsoft SQL Server, Error: 4064)

The fix for this problem is very simple.

Fix/Workaround/Solution:

First click on Option>> Button of “Connect to Server” Prompt.

Now change the connect to database to any existing database on your server like master or msdb.

Now click on connect and it will successfully log you in. Once you login in the system run following statement for your username and it should fix your problem. Make sure to replace [test] with your username and master with yourdatabase name.

ALTER LOGIN [test] WITH DEFAULT_DATABASE = master
GO

Watch SQL in Sixty Seconds video to Resolve Connection Error:

Reference : Pinal Dave (http://www.SQLAuthority.com)

By: Daniel Farina Last Updated: 2015-06-24 Comments (9) Related Tips: More >Disaster Recovery

Ms Sql Server Error Codes


Problem

You have to fix a corrupt SQL Server msdb database and to make things worse, there is no recent database backup. If you run setup with the option REBUILDDATABASE you will also rebuild the master database and lose all the logins unless you backup and restore the master database. Is this the best option? Keep reading and you will see that there is another way to rebuild the msdb database without compromising the master database.

Solution

Dealing with corruption of system databases is one of the most feared situations for SQL Server DBAs. Luckily, there are a few different options to fix the corrupt SQL Server instance to get back up and running. You can get a list of those methods in John Grover's tip on How to recover a suspect msdb database in SQL Server. The last method John proposes in his tip is to 'Use a SQL Server Template MSDB Database', but this option has two paths depending on what SQL Server version you are working with. If you have an instance of SQL Server 2008 or later, you can copy the database template in the BinnTemplates subfolder of the instance root directory. But if you are working with a previous version then you have to use the method that I will expose in this tip.

Most of the articles about msdb database corruption refer to a physically corrupted database, but what if instead you have logical corruption? I mean, for example if someone accidentally or deliberately modified some of the stored procedures of the msdb database. In that case, using the template msdb database won't be the best approach because you will lose all the job definitions, Integration Services Packages stored in msdb, etc.

The possible solution to the previous scenario relies on the Install subfolder of SQL Server instance root directory, which is usually C:Program FilesMicrosoft SQL ServerMSSQL13.MSSQLSERVERMSSQLInstall. There is a file in that folder named instmsdb.sql which is the msdb creation script. Let's see how we can use it.

Option 1 - Rebuild a Corrupt msdb SQL Server Database

This procedure is quite straightforward. It consists of starting the SQL Server instance with trace flag 3608. This trace flag prevents the instance from automatically recovering any database except master. But databases will be started and recovered when accessed, so if we want to rebuild the msdb database it is a wise to avoid connections to that database.

The first step is to stop the SQL Server Instance and all the dependent services like Integration Services, Analysis Services, Reporting Services and any other application that use SQL Server. You can use any administrative tool for that purpose like the Services Manager. The screen shot below shows a command window with the commands (i.e. NET STOP '/f' parameter is to start SQL Server service with its minimal configuration and in single user mode; we want to be the only ones to access the instance. The last parameter /m'SQLCMD' tells the instance to only accept connections for an application that identifies as SQLCMD. That's to avoid other applications to connect to the instance.

After the instance starts up, we open a Command window and change the folder to the install folder of the instance root directory and from there we connect to it with the SQLCMD application and detach the msdb database.

The –E parameter is used to establish a trusted connection, you can use a SQL Server login like sa if you want to. The –Q parameter executes the quoted commands and exits.

Now you can stop the instance and start it up again normally, I mean without any special consideration.

The next step is to rename the msdb database files; otherwise the next step will fail. By default these files are in the DATA sub-folder for the SQL Server instance.

If you try to connect to the instance using SQL Server Management Studio you will get an error message, but you can still use SQL Server Management Studio by selecting NewDatabase Engine Query, like on the next image.

Now to recreate the msdb database you can use the next SQLCMD command.

The '-iinstmsdb.sql' parameter instructs SQLCMD to execute the instmsdb.sql script, of course you have to be in the same folder as the script. The '–ologfile.txt' option will save the output to a file named logfile.txt in the same folder.

Also you can execute this script by dragging the instmsdb.sql file into SQL Server Management Studio. The next image is a screen capture showing the execution.

Sql Server 2008 Error Code 126

Option 2 - Recover Stored Procedures in the SQL Server msdb Database

If your msdb database has altered stored procedure and you want to get them back to the original version, you can get the original code from the instmsdb.sql file. I tested that if you run the instmsdb.sql script it won't drop and recreate the system tables in msdb if they exist, but it will drop and recreate the stored procedures. The next script was copied from instmsdb.sql file and intends to create the sysjobs table. Notice that the CREATE TABLE statement is inside an IF block that is executed if the sysjobs table doesn't exist, so it doesn't try to recreate the table.

In contraposition, the next script also extracted from instmsdb.sql script creates the sp_agent_start_job stored procedure. Notice that before the CREATE PROCEDURE there is an IF block that drops the sp_agent_start_job stored procedure if it exists.

The best part of this fix is that you don't have to stop the instance and start it with a trace flag in order to execute the instmsdb.sql script. The next three images are screen captures that I took showing the contents of the sysjobs table before executing instmsdb.sql script, the script execution and the contents of the sysjobs table after executing instmsdb.sql script.

Microsoft Sql Server 2008 Error 945 Msdb Suspect Update

Here we can see that there is one job called SampleJob.

In the next screen shot we see the execution of instmsdb.sql.

In this final screenshot after instmsdb.sql has been run, we can see that job SampleJob still exists. So all of the data stays intact after running instmsdb.sql against an existing msdb database.

Download naruto perjanjian perdamaian dengan sasuke. • Naruto Shippuden 319 - The Living Soul Inside the Puppet • Naruto Shippuden 318 - A Hole in the Heart: The Other Jinchuriki • Naruto Shippuden 317 - Shino vs. • Naruto Shippuden 324 - The Unbreakable Mask and the Popped Bubbles • Naruto Shippuden 323 - The Five Kage Assemble • Naruto Shippuden 322 - Madara Uchiha • Naruto Shippuden 321 - Reinforcements Arrive • Naruto Shippuden 320 - Run, Omoi!

Next Steps
  • The following tip will go through the basics of system databases: SQL Server System Databases.
  • To learn about how to safeguard system databases take a look at Best Practices Backup System Databases in SQL Server.
  • This tip will show you step by step the process of Restoring SQL Server system databases msdb and model.
  • The following tip shows other ways to recover the msdb database: How to recover a suspect msdb database in SQL Server.
  • Check out System Databases Tips Category.

Microsoft Sql Server 2008 Error 945 Msdb Suspect Download

Last Updated: 2015-06-24 Microsoft Sql Server 2008 Error 945 Msdb Suspect



About the author

Sql Server 2008 Error 926

Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.
View all my tips

Sql Server Errors List


Coments are closed
Scroll to top