SQL Server Agent will not start

06-Feb-1111 Leave a comment

Believe this problem is because I had SQL Server 2008 on my machine and then added SQL Server 2008 R2. Today I had renamed the old MSSQL10 directories with a zz at front to mark them as old. Then SQL Server Agent did not start.
First I had:
Error message when you use SQL Server Configuration Manager to restart the SQL Server Agent service in SQL Server 2008:
“The request failed or the service did not respond in a timely fashion”
http://support.microsoft.com/kb/955494
Suggested using Start > Administrative Tools > Services or Services.msc and start from there.

This led to next problem
The SQL Server Agent (MSSQLSERVER) service on Local Computer started and then stopped.  Some services stop automatically if they have no work to do, for example, the Performance Logs and Alerts service.
http://blogs.msdn.com/b/sqlserverfaq/archive/2009/06/12/unable-to-start-sql-server-agent.aspx
Found that the registry entry
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\SQLServerAgent
ErrorLogFile key was pointing at a location which was 2008 not 2008 R2
Chnaged the key value and SQL Server Agent started OK

SOLVED

Filed under Errors, SQL Configuration, SQL Server Tagged with

SQL Database Restore: Cannot access the specified path or file on the server

06-Feb-1111 Leave a comment

“locate backup file cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists.

If you know that the service account can access a specific file, type in the full path for the file in the file name control in the locate dialog box.”

Solution:
Start > Programs > SQL Server Conifguration Manager > SQL Server Services
Find SQL Server in list and identify the Log On As account

Then go to the backup file location and give permissions to that account. Should now work

Filed under Errors, SQL Configuration, SQL Server

Changing the default location SQL Server Backup

06-Feb-1111 Leave a comment

 

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.2\MSSQLServer

BackupDirectory key modify. Works

http://www.mssqltips.com/tip.asp?tip=1583

Filed under SQL Configuration, SQL Server

SSMS stops working while SQL Server still running

08-Nov-1010 Leave a comment

Aggh
Laptop Win7 SQL Server 2008 R2 SSMS has worked for a couple of months. Suddenly stopped working. SQL Server was still working becase VS-TFS still present.
Log file lookups on Web search suggested all sorts of possible directions to solve including:
Permissions on Network Service, Reinstall SQL Server performance monitors using unlodctr sqlctr.ini or something
Reinstalling SSMS did not work
Anyway what did work was Restoring back two weeks.

Filed under SQL Configuration

SQL Server Autogrowth settings

08-Nov-1010 Leave a comment

 

Do not use default setttings. See
http://support.microsoft.com/kb/315512

You can view the current settings through the database properties in SQL Enterprise Manager (SEM). Or, you can run the following Transact-SQL command:

sp_helpdb [ [ @dbname= ] 'name' ]

Filed under SQL Configuration

SQL Server 2008 Management Tools Basic vs Complete Explained…. – CSS SQL Server Engineers – Site H

29-Jul-1010 Leave a comment

 
 
On SQL Server install there is an option to go for SSMS Basic or Complete. With Basic some features are missing:
You are unable to connect to Server Types other than Database Engine and possibly Compact Database Engine. E.g. Integration Services
Also many menu and toolbar commands are missing.
If you initially install Basic and change your mind and then want Complete then go to install disk and Add Features, picking SSMS complete this time
 
 
 

Filed under SQL Configuration

Understanding “login failed” (Error 18456) error messages in SQL Server

02-Jul-1010 Leave a comment

 
More human readable messages are stored in Log
 
Described here. 2005, but still works in 2008 R2
 
Viewing log

Filed under SQL Configuration, SQL Server

SQL Database Mail – User security to allow facility

05-Jan-1010 Leave a comment

 
Security > Logins > Pick Login > Properties > User Mapping > msdb > tick on
Database role membership include DatabaseMailUserRole
 
msdb has more database role memberships. May be resolution to LinkedServer issue. 
 
PROBLEM
Procedure sp_send_dbmail, line 141 profile name is not valid
 
SOLVED
 
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
    @principal_name = 'ApplicationUser',
    @profile_name = 'AdventureWorks2008R2 Administrator Profile',
    @is_default = 1 ;
 
 

Filed under SQL Configuration, SQL Server, Troubleshooting Tagged with ,

SQL Server Database Mail Stopped

09-Dec-0909 Leave a comment

 
 
 
USE msdb ;
GO
EXECUTE dbo.sysmail_start_sp ;
GO
 
To get it going again. Also possible to right-click on Management > Database Mail > View Database Mail Log
 
 

Filed under SQL Configuration, SQL Server Tagged with

Newer posts →