I am sure most of us have experienced driving a four wheeler. The first time I went behind the wheels, it was an amazing experience. My excitement was multiplied by tension in the face of my father. After a while, I could see a sigh of relief and confidence even in his eyes. This is a classic case of us being pampered, guided and always shadowed by our loved ones. I loved the learning experience then. The real challenge came way later. One of the days while I was driving to office, the car stopped in the middle of the road. I was clueless to what could be the problem. The human instinct was to look at the dashboard for some signals. I checked the fuel level, oil level indicators or for any indicator that could guide me in finding the problem. It was time to call the experts was my opinion. A lot of times, we are faced with situations where the health of the components cannot be diagnosed easily – what can we do?
The above analogy is just an example of what happens inside SQL Server too. If we don’t know or cannot see the problem in hand, how can we fix the problem or even worst how do we know a problem exists on first place? In the recent past, I was quizzed with a similar problem and didn’t know what course of action has to be taken.
During one of the chat sessions with a DBA friend, he said – “most of the testing and integration environments are managed by junior DBA’s and seniors handle only the production environments.” He explained there was a recent incident that caught his attention and wanted to know if there is a way to solve the problem in hand.
This got me curious to what had really happened. He said, one of the test environments crashed just couple of days before a major release cycle. The testing team goes ahead and creates databases for a release cycle and loads tons of relevant data. After the crash, the DBA’s didn’t have any Log backup because it was failing and they didn’t monitor the same. The DBA said the maintenance plans were in place, yet it had failed.
The junior DBA got curious and asked this question to him. There must be something fundamentally wrong here.
FULL recovery acts like SIMPLE
Sometimes we need to understand the basics first. Even though the database was set in FULL recovery model, it acts like Simple recovery model till the first Full backup is taken. This is a well-known concept. To illustrate this, let me take a Log backup of a newly created database. The T-SQL looks like:
BACKUP LOG [Bigdata] TO DISK = N'C:\Backup\Bigdata.bak' WITH NOFORMAT, NOINIT, NAME = N'Bigdata-Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO
This will throw the below error 4214. This is the exact error they were getting too.
Msg 4214, Level 16, State 1, Line 1
BACKUP LOG cannot be performed because there is no current database backup.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
The error is self-explanatory that we don’t have a database backup yet and that will solve the problem. The GUI steps will yield the following error message dialog –
Now that my friend understood this concept, he wanted to be vigilant and proactive in solving this problem. There is no way he can track all the databases created in the test and integration environments. He wanted to know if there is a way to know this ahead of time.
Overall health using Spotlight for SQL Server
There is no way one can keep track of databases that are created randomly by application / testing team every single day. If you have a monitoring tool like Spotlight, this task becomes easy for sure. Yes, we can make a deployment standard that everyone needs to take a Full backup as soon as the database is created. But this required constant monitoring and discipline from users / script writers.
In this example below, I have gone ahead and pointed Spotlight to a newly built server. As soon as this happens, the heatmap view shows us some critical data which needs attention and acknowledgement. Here you can see the tooltip suggest – “Full backups are missing for few databases”. I felt this was a nifty suggestion that can save lives for many.
On double clicking the same, we are taken to the Server home page where we can look at the databases node (marked in Red) and get the detailed information to which all databases are pending for backup.
The learnings here are two folds. We understood the principle that a log backup is not available till the first full backup happens. Secondly, the FULL recovery model acts like Simple recovery model till the first Full backup is taken. To mitigate the risks on business critical environments, it is important to have some monitoring capability like Spotlight for SQL Server.
Try Spotlight on SQL Server free for 30 days!
Spotlight on SQL Server continues to break new ground when it comes to providing DBAs with the ultimate in flexibility to suit their specific needs. Building on the success of the Wait Stat analysis workflow, Spotlight on SQL Server version 11.0 now includes multi-dimensional workload analysis to provide maximum insight into performance issues, an enhanced mobile capability to remotely diagnose issues directly from mobile phones and the introduction of SNMP traps. A fresh new look provides simple to use, powerful visualization to effectively monitor the health of your SQL Server environment.
If you’re looking for additional help on how to optimize your SQL Servers, Visit Dell Software’s SQL Server Resource Center for video, guides and other useful content.
Reference: Pinal Dave (https://blog.sqlauthority.com)
