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 check the transaction log size in SQL Server

Question : Is the Log Size on DBCC SQLPERF(LOGSPACE) the max size the log file has ever been or an amount of reserved space? Answer: It is the current size allocated to the transaction log. It isn’t the max size. You can get the max size from the sys.database_files max_size column. You have 3 main variables to manipulate at the CREATE DATABASE or ALTER DATABASE Size Growth rate Max size There are some considerations in setting the MAX SIZE. Let’s say you’re in the middle of a transaction and the log file limit is reached , the in-flight transaction will... Read more →

Help command in sql for undocumented commands

Question: I can get the help details for a dbcc sql command via the DBCC HELP function. For example, something like DBCC HELP('CHECKDB') will display the usage details for the DBCC CHECKDB. A very useful function which saves me time from checking the MSDN site. But if I’m using an undocumented command such as DBCC AUTOPILOT I get : Msg 8987, Level 16, State 1, Line 1 No help available for DBCC statement 'AUTOPILOT'. Is there a way of accessing DBCC HELP details for sql undocumented tasks? Answer: The way to accesss the DBCC HELP details for undocumented sql commands... Read more →

7 Essential Steps to Recover a Corrupt SQL Server Database

This post - 7 Essential Steps to Recover a Corrupt SQL Server Database - is written by a Guest Blogger. Andrew Jackson (LinkedIn - @AndrewJackson) - is a SQL and SQL Server blogger too. He likes to share about SQL Server and the problems related to it as well as their solution and also handles database related user queries, Server or database maintenance, database management etc. How to Deal with Corruption In SQL Server Database ? Backups are evidently a DBAs foremost approach when caught in a critical crisis condition. However, there are instances where responding to corruption is a... Read more →

How to get the sql object name using the Resource Id

When doing some SQL Server performance troubleshooting I was using the sys.dm_os_waiting_tasks DMV to identify what tasks were waiting on resources. One of the columns available is resource description. The resource description column is the description of the resource that is being consumed. Typically you will see something like this resource id : 24:1:13345565 How can you find out the object name being referenced? The pattern of the numerical description is : :: First we can try obtaining the database name with the db-id select db_name(24) This gives us the database name. Next we can identify the file name with... Read more →