Unable to start services after patching - msdb110_upgrade.sql' encountered error 574, state 0, severity 16.

Question : During a recent round of patching , I've been frequently seeing msdb110_upgrade.sql' encountered error 574, state 0, severity 16 in the Event Viewer when the service for the SQL Server Instance is attempting to start. How can I resolve this issue? The service will not currently start. I've checked in the SQL Server Error log file via a text editor and can see A problem was encountered granting access to MSDB database for login '(null)'. Make sure this login is provisioned with SQLServer and rerun sqlagent_msdb_upgrade.sql The lines in the SQL Error Log are: 2018-09-09 13:41:56.88 spid5s Granting... Read more →

How to convert sys.master_files size column into GB

Question: I need to convert the sys.master_files size column into GB. Currently when I run select name,size from sys.master_files I get back the file size as KB. I've tried doing something - size/1024/1024 - but I'm getting incorrect results. How do I get the current sys.master_files size unit into the GB format? Answer: The size column in the sys.master_files is an int data type and is represented as 8-KB pages. In the example you've given above - you're not taking into account the 8-KB pages. You need to multiply the size value by 8. i.e size * 8 Find an... Read more →

Disaster recovery and database recovery error preparation

Having a well designed and tested disaster recovery plan is essential for managing IT systems. All systems have their own peculiar requirements . Creating a Disaster Recovery Plan will help in minimising risk and measuring the impact of a DR. But despite all the best planning It is worth testing the DR plans regularly to actually check the recovery from the disaster occurs. As a DBA having knowledge of the potential recovery issues and how to get out of them will help you and the more exposure you have to the common issues will help keep the stress levels under... Read more →

Hacker Habituals

An interesting article on Wired about rehabilitating hackers , rather than treating them as outcasts. Despite the portrayal of hackers in movies and sci-fi fiction - there are real lives behind these hackers . IT security, data security, sql injection are all in the news daily - so the more we undertand the wider context - but better we can design security techniques. The National Crime Agency is launching a hacker ‘rehab’ programme directed towards young computer criminals. The Prevent programme is not just for hackers that have been arrested, but also for young hackers with cautions and police officer... Read more →

Powershell Script - Backup SQL databases to a remote drive

Question: Do you have a powershell script to backup SQL databases on a single SQL Server Instance , and direct them to a network share or a local drive? Also , I'd like an email notification with a screen output sent to my email box. The purpose of the script is not to replace the current backup scripting but is for ad-hoc uses Answer: There are plenty of different ways to manage the process of backing up SQL Server databases with Powershell. In this script , the powershell cmdlet Invoke-sqlcmd is used to connect to a sql server , and... Read more →