Blog Archives
Corruption and Verifying Backups
This morning I had a conversation with a Subject Matter Expert and Application Administrator who asked me quite simply for a verified backup before he does an install on Saturday. My response was that I could test it manually for them and I was willing to do so but currently with our infrastructure the nightly backups are not tested without manual intervention. I have tried to change this policy for some time and unfortunately it may not occur until something bad happens. With this in mind, I do the best I can to minimize the risk given that I cannot automate a solution to restore databases on a regular basis as I would prefer. How can you minimize the risk?
- Use the WITH CHECKSUM option on your backup scripts. Read Paul Randall’s The Importance of Validating Backups article on SQL Server Central for more information.
- Use the RESTORE VERIFYONLY command (although we use Ola Hallengren’s Backup Solution which is the verify option which runs the command).
- Use the WITH CHECKSUM on the RESTORE command to recheck the page checksums in the backup as well as the backup file.
- Automate or manually restore your databases from scratch on a regular basis. (This is where we are lacking and where I want to take my servers).
- Once you have restored, then run DBCC CHECKDB against the restored database.
If you are not doing all five then you cannot say confidently that your backups are verified. However, if you are doing all five keep in mind that there is no fool proof guarantee against corruption, this merely minimizes the destruction by having viable backups. I hope this helps….Enjoy!
SQL Nexus, Quirkiness Workaround
SQL Nexus Report Options
Yesterday, I wrote about the “Realtime – Server Status” report quirkiness where essentially the report does not work when you import SQL Server 2008 data and essentially it does not contain any information as to why it does not work, only a blank screen. Craig Purnell (Blog | Twitter) shortly thereafter sent me an updated report library file that works with 2008. That is exciting! I ran a diff on the two files and found that Craig updated two lines with the same change in order for this to work. The filename is “Realtime – Server Status.rdl” located in the Reports subfolder of your SQL Nexus installation folder. Below is the line that needs to be changed (remember to do it in both places):
Original line: SELECT @ts_now = cpu_ticks / CONVERT (float, cpu_ticks_in_ms) FROM sys.dm_os_sys_info
Updated Line: SELECT @ts_now = ms_ticks FROM sys.dm_os_sys_info
Thanks Craig, now I have results that look like the following:
Realtime Server Status
SQL Nexus, Quirkiness Continued
SQL Nexus Report Options
As we continue our SQL Nexus journey, we continue to point out some quirkiness in the amazingly useful SQL Nexus tool. One of the items in the Reports section that I find quirky is the “Realtime – Server Status” report which on the surface sounds like an essential tool that I would rely heavily upon. However, when I select that report, the child screen goes blank and nothing happens.
Some of the other reports, such as the “SQL Server 2000 Perf Stats” report at least prompt you with a “The database doesn’t have necessary data to run this report” dialog box. That makes sense in this case because I am running from a SQL 2008 R2 source import. As we discovered early on in this blog series, the “ReadTrace_Main” report only works if your trace file import is successful. But the “Realtime – Server Status” report gives us nothing. No error, no suggestion, no data, nothing whatsoever.
After some careful research, actually I just loaded a SQL Server 2005 import, I discovered that this only works for 2005. Sadly, it appears to be a deprecated feature. This is a neat report that shows a dashboard reflecting the health of the server. This is quirky and there should at least be a message telling you that it will not work with 2008. Enjoy!
SQL Nexus, Adding Additional Collection Events
As we continue our SQL Nexus journey, I would like to point out something interesting that I learned from reading the SQL Nexus chapter in the race car book (aka Professional SQL Server 2008 Internals and Troubleshooting). The author, Christian Bolton, noted that the default Trace events that are collected when you run StartSQLDiagTrace2008R2.cmd from the performance scripts that we mentioned earlier in this series provide a good baseline for your monitoring activity. However, he noted added the following events in order to enable additional functionality necessary to compare execution plans and drill down to the statement level:
- EventType “Performance”, Event name “Show Plan Statistics”
- EventType “Stored Procedures”, Event name “SP:StmtCompleted”
- EventType “Stored Procedures”, Event name “SP:StmtStarting”
- EventType “TSQL”, Event name “SQL:StmtCompleted”
- EventType “TSQL”, Event name “SQL:StmtStarting”
In order to enable these simply edit the XML file, SQLDiagPerfStats_Trace2008.xml, changing the above items from false to true. After you collect these new events by running the CMD file, then import the collection into SQL Nexus and check out your new results. Enjoy!
SQL Nexus, More Quirkiness
As we continue our SQL Nexus journey, there are a few light things I would like to point out before the long holiday weekend. We will rejoin this journey probably next week after Memorial Day.
The first thing I noticed, after our debacle with the trace files not loading, was that several of the reports do not work as they should. After those were resolved, I noticed that some of the reports are not exactly what they state they are. For example, I connected to a SQL 2008 R2 box and imported the results from a detailed 2008 performance script (see previous posts) and yet when I click on the “SQL Server 2008 Perf Stats” report I get the error below. That is strange, what is stranger is that when I select the “SQL Server 2005 Perf Stats” report then I get results. I have talked with other DBAs who have experienced similar problems. Enjoy your holiday weekend!
Weird SQL Nexus Errors
Migrating SQL Server Database Logins with sp_help_revlogin
You’ve migrated your database to a new box, with no problems. You created the login that goes with this database, and all is right in the world. However, when your user goes to connect to it they get the dreaded error 18456, login failed. But wait, I created the login, right?
Not so fast there, accidental DBA. You are going to need the help of a nifty stored procedure called sp_help_revlogin. This stored procedure, provided by Microsoft in the link below, will generate a T-SQL script that will copy the logins, their passwords and their corresponding SIDs (or security identification number, which is usually the culprit here). This is also a great utility when the database has numerous users with various security levels and passwords. Really now, who wants to write all of that down and recreate it? Even with screenshots, it can turn into a large, fat-fingering and time consuming event. Mind-numbing, I would say. Why do something manually when you can do it automagically? Just think of all those happy logins that will not be orphaned by the dark side?
Here is also a code snippet to check for orphaned logins, for your enjoyment. Enjoy!
USE <database_name>; GO sp_change_users_login @Action='Report'; GO
SQL Swampland 