I recently had an issue where restoring a SQL Server database would take forever just to load the restore window. Right clicking a database, going to Tasks –> Restore –> Database would take MINUTES just to load on a good day.
While I have upgraded and patched the server it has been running since 2008. (You have to love VM Ware allowing you to update hardware and not reinstall).
SQL Server logs all backups/restores to MSDB. You can query all the information from the following system views.
In my case because we have been running so many databases for so long I had over 1,000,000 records in msdb.dbo.backupset. Any time that I used SQL Management Studio to restore a database, or if you deleted a database and instructed it to delete the backup history too, it would take FOREVER.
I created a simple job that deletes backups history over 90 days. Just change “90” to however many days you want to delete and BLAMO everything is good again.
DECLARE @CutOff DATETIME
SELECT @CutOff = DATEADD(day, -90, GETDATE())
EXEC SP_DELETE_BACKUPHISTORY @CutOff
For those of you that are using the new SQL Server 2005 you should be using the Microsoft SQL Server Management Studio. If you go ahead and select a database from Object Explorer, the report button should be highlighted. From there you can run reports like “Server Dashboard”, “Configuration Changes History” and other performance and activity reports.
Go ahead and open up Management Studio, select your default database, “Report” should be highlighted. Now click “Server Dashboard”.
HA! Chances are that you got the following error “sql_handle” is not a recognized table hints option. If it is intended as a parameter to a table-valued function, ensure that your database compatibiity mode is set to 90.
If you believe the error, then you may think that is has something to do with a “sql_handle” or your compatiblity mode. Don’t worry, it has nothing to do with either.
The problem is with your default database. Chances are that it is set to “Master”. You need to set it to a different database that you have created. Why? I don’t know. It may create some temporary tables or run a procedure or function that you can only run from a user created database. You should never change anything in the Master database yourself.
To change a user’s default database, you need to select and expand the Database in SQL Management Studio. Then expand the “Security” folder. Then right-click on the user and go to properties. Then right on the user’s “Login Properties” window you should see “Default database:”. Click on the dropdown box and select a database that the user has access to. Now when you run the reports they should now work.