A little something about everything

If you did not set the ..

Full Error:

'Agent XPs' component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Agent XPs' by using sp_configure. For more information about enabling 'Agent XPs' see "Surface Area Configuration" in SQL Server Books Online. (Object Explorer)

Fix Options:

1. if using GUI open services, locate SQL Server Agent, double click and set startup type to automatic then click start.
2. if you prefer to use command line you can just do the following on the master database:

   EXEC sp_configure 'show advanced', 1; 
    RECONFIGURE; 
    EXEC sp_configure 'allow updates', 0; 
    RECONFIGURE; 
    EXEC sp_configure 'Agent XPs', 1; 
   RECONFIGURE; 
   GO

NOTICE!! BACKUP DATABASE FILES BEFORE DOING THIS, DO IT AT YOUR OWN RISK!

I don't want anyone sending me hate mail because this did not work, however it does work (most of the time).

If you are getting these errors:

• cannot be opened due to inaccessible files or insufficient memory or disk space
• the log cannot be rebuilt because there were open transactions/users when the database was shutdown
• unable to open the physical file
• the system cannot find the file specified
• error 5120
• huge ldf file, restore from backup hangs at 100%
• HDD IO maxed out on ldf file during restore from .bak file
• how to restore just the mdf

If your getting errors like the above and have tried all other solutions, you can try the following. Be sure to make a backup of your .mdf / .bak file.

In my case I had a database that for some reason created a 600GB ldf file and corrupted itself. While attempting to restore from a backup it would say 100% completed but would continue to run for hours/days. If I stopped the process it would kill the database and not allow me to use it. Here is what needs to be done:

First restore from your .bak file if you have one.

Example code:

RESTORE
DATABASE [dbname] FROMDISK=N'DRIVE:\BACKUPDIR\BACKUPFILE.bak'WITHFILE= 1,NOUNLOAD,REPLACE,STATS=10
GO

in most cases this will fix your database, IF this backup hangs at 100% for too long you can do the following:

now using the resource monitor verify that the mdf file is done writing (VERY IMPORTANT) select disk sort by file, check to see that the disk is only read/write to the .ldf file and not the .mdf file. Once the system has stopped writing to the .mdf file do the following.

1. stop mssql service
2. go into the DATA folder and rename the dbname.mdf file to dbname.mdfx
3. start the mssql service
4. delete the database (IMPORTANT, UNCHECK option to delete backups)
5. create new database with the same name and give proper user owner access
6. stop the mssql service
7. delete the dbname.mdf & dbname.ldf files
8. rename the dbname.mdfx to dbname.mdf
9. start the mssql service and wait a minute for the service to fully start before issuing the following command:

ALTER DATABASE dbname SETEMERGENCY;
GO
ALTER DATABASE dbname SET single_userWITHROLLBACKIMMEDIATE;
DBCC
checkdb('dbname', repair_allow_data_loss);
GO
GO

In order to fix this bug you can simply run this command on the effected database after you have used the backup (sql2000) / restore sql2005/2008) or detach attach methods.

command to run:

 exec sp_change_users_login'Update_One','dbusername','dbusername'

Replace "dbusername" with the problem user account. both should be the same.

This will remove the ghost user from the sql 2000 server and replace it with the new user on your sql 2005 / 2008 server. Be sure to have the user created before running this command.

Errors associated with this are as follows:

"login name must be specified Database user"

"The database principal owns a schema in the database, and cannot be dropped"

"user group or role already exists in the current database"

"can't delete sql user in database"