How to fix String or binary data would be truncated

Question: I'm trying to run an INSERT statement , on a table with a large amount of columns and millions of rows. When executing the query statement , I'm getting an error message: INSERT INTO myTable SELECT * FROM myTable1 ; Msg 8152, Level 16, State 30, Line 19 String or binary data would be truncated. The statement has been terminated. How can I fix this issue? Trying to find which specific row and column is causing the problem is the immediate problem. Once I can find which data is triggering the error , I can make the adjustment Answer:... Read more →

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 →