I run all or most of my development environments in virtual machines. Every so often, I will forget this and suspend my main machine or just unplug the external drive running my VM and completely forget to shutdown my VM beforehand.
For the most part, this doesn’t seem to cause too many issues. SQL on the other hand seems to get hit by this pretty hard. I believe that there there will be open transactions or running jobs on the server that never fully commit when this happens and as such, there is some corruption on the DB.
I know something is going on when I fire-up the VM again and the CPU spikes up to 100% after the SQL agent starts up or I fire up BizTalk server. All of a sudden I will get a ton of messages in the event viewer complaining that there are logical consistency issues with some DB. Generally, it will be the MSDB.
Event Viewer Log Entry for SQL Server Logical Consistency Issue
This was a pretty hard one to track down a fix for because the MSDB can’t be easily detached and recreated. I tried quite a few things, but nothing seemed to work properly, until I came across this post at sqlteam.com. Basically, what you want to do is set the database to single user mode, run a CHECKDB to repair and then change it back to multi-user mode. Took about 5 minutes to run for me, but after that, everything was golden.
SQL Server Command To Repair MSDB
SET NOCOUNT ON;
-- User Preferences
DECLARE @prefix varchar(5);
SET @prefix = 'usp_';
-- End User Preferences
-- Create CRUD procedures
DECLARE DBtables CURSOR FOR SELECT name FROM sysobjects WHERE xtype = 'u' AND name != 'sysdiagrams'
DECLARE @tblName varchar(max);
OPEN DBtables
FETCH NEXT FROM DBtables INTO @tblName
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN
-- Holds column attributes for each column of the table
DECLARE @tblColumns TABLE
(
Id BIGINT IDENTITY(1,1),
Name VARCHAR(max),
DataType VARCHAR(max),
MaxLength BIGINT,
IsNullable BIT,
IsAnsiPadded BIT
)
-- Holds the primary key attributes
DECLARE @PKey TABLE
(
TABLE_QUALIFIER sysname,
TABLE_OWNER sysname,
TABLE_NAME sysname,
COLUMN_NAME sysname,
KEY_SEQ smallint,
PK_NAME sysname
)
-- If the table variables currently have rows, delete them
IF ((SELECT COUNT(*) FROM @tblColumns) > 0)
BEGIN
DELETE FROM @tblColumns;
END
IF ((SELECT COUNT(*) FROM @PKey) > 0)
BEGIN
DELETE FROM @PKey;
END
-- Insert the table attributes into table variable
INSERT INTO @tblColumns (Name, DataType, MaxLength, IsNullable, IsAnsiPadded)
SELECT c.name , t.name, c.max_length, c.is_nullable, c.is_ansi_padded
FROM sys.columns AS c
INNER JOIN sys.types AS t ON t.system_type_id = c.system_type_id
WHERE (c.object_id = object_id(@tblName))
AND t.system_type_id = t.user_type_id
ORDER BY c.column_id
-- Insert the primary key attributes into table variables
INSERT INTO @PKey
exec sp_pkeys @tblName;
DECLARE @sql NVARCHAR(MAX);
DECLARE @max BIGINT;
DECLARE @min BIGINT;
DECLARE @pkeyName varchar(100);
DECLARE @ColumnName varchar(max);
DECLARE @AnsiPadded BIT;
SET @AnsiPadded = 0;
-- Check whether the current table has a primary key
IF((SELECT COUNT(*) FROM @PKey) = 0)
BEGIN
SET @pkeyName = '';
END
ELSE
BEGIN
DECLARE @schema nvarchar(128);
SELECT @schema = CAST(TABLE_SCHEMA AS NVARCHAR) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_Name = ''+ @tblName + ''
-- Get the primary key column
SELECT @pkeyName = CAST(COLUMN_NAME AS NVARCHAR) FROM @PKey WHERE TABLE_Name = CAST(@tblName as sysname)
END
DECLARE @value varchar(100);
SELECT @value = CAST(DataType AS NVARCHAR) FROM @tblColumns WHERE Name = @pkeyName
-- Get the min Id for table variable
SELECT @min = MIN(Id) FROM @tblColumns;
-- Get the max Id for table variable
SELECT @max = MAX(Id) FROM @tblColumns;
IF(LEN(@pkeyName) > 0)
BEGIN
PRINT 'Creating Procedure: [' + @schema + '].['+ @prefix + @tblName+'Select]'
-- Check the the select procedure currently exists, if so delete
SET @sql = 'IF OBJECT_ID(''[' + @schema + '].[' + @prefix + +@tblName+'Select]'') IS NOT NULL' + CHAR(10)
+ 'BEGIN' + CHAR(10) + CHAR(9)
+ 'DROP PROC [' + @schema + '].[' + @prefix + @tblName+'Select]' + CHAR(10)
+ 'END' + CHAR(10)
exec sp_executesql @sql;
-- Create select procedure
SET @sql = 'CREATE PROC [' + @schema + '].[' + @prefix +@tblName+'Select]' + CHAR(10) + CHAR(9)
+ '@'+@pkeyName + ' ' + @value + CHAR(10) + 'AS ' + CHAR(10) + CHAR(9) + 'SET NOCOUNT ON;' + CHAR(10) + CHAR(9) + 'SET XACT_ABORT ON;'
+ CHAR(10) + CHAR(10) + CHAR(9) + 'BEGIN TRAN' + CHAR(10) + CHAR(10) + CHAR(9) + 'SELECT '
--Loop through table columns
WHILE(@min <= @max)
BEGIN
-- Get column name in table
SELECT @ColumnName = Name FROM @tblColumns WHERE Id = @min;
SET @sql += '[' + @ColumnName +']';
SET @min += 1;
IF(@min <= @max)
BEGIN
-- Column names must be separated by a comma
SET @sql += ', ';
END
END
-- Set the table name and where conditions
SET @sql += CHAR(10) + CHAR(9) + ' FROM [' + @schema + '].[' + @tblName + ']' + CHAR(10) + CHAR(9)
SET @sql += ' WHERE (['+ @pkeyName + '] = @' + @pkeyName + ' OR @' + @pkeyName + ' IS NULL)'
+ CHAR(10) + CHAR(10) + CHAR(9) + 'COMMIT;' + CHAR(10) + CHAR(10) --+ 'GO'
exec sp_executesql @sql;
-- Create Insert procedure
PRINT 'Creating Procedure: [' + @schema + '].[' + @prefix +@tblName+'Insert]'
-- Check the the insert prcedure currently exists, if so delete
SET @sql = 'IF OBJECT_ID(''[' + @schema + '].[' + @prefix +@tblName+'Insert]'') IS NOT NULL' + CHAR(10)
+ 'BEGIN' + CHAR(10) + CHAR(9)
+ 'DROP PROC [' + @schema + '].[' + @prefix + @tblName+'Insert]' + CHAR(10)
+ 'END' + CHAR(10)
exec sp_executesql @sql;
SET @sql = 'CREATE PROC [' + @schema + '].[' + @prefix +@tblName+'Insert]' + CHAR(10) + CHAR(9)
-- Get the min Id for table variable
SELECT @min = MIN(Id) FROM @tblColumns;
-- Get the max Id for table variable
SELECT @max = MAX(Id) FROM @tblColumns;
-- Loop through table columns
WHILE(@min <= @max)
BEGIN
-- Get column name in table
SELECT @ColumnName = Name FROM @tblColumns WHERE Id = @min;
-- The identity column does not belong in the procedure parameters
IF(@ColumnName != @pkeyName)
BEGIN
-- Gets the value data type
SELECT @value = CAST(DataType AS NVARCHAR) FROM @tblColumns WHERE Name = @ColumnName;
SET @sql += '@' + @ColumnName + ' ' + @value;
-- Determines whether the datatype is ansi padded
SELECT @AnsiPadded = IsAnsiPadded FROM @tblColumns WHERE Id = @min;
IF(@AnsiPadded = 1)
BEGIN
-- Gets the ansi padded length
SELECT @value = CASE WHEN LEFT(@value, 1) = 'n' THEN (MaxLength / 2) ELSE MaxLength END FROM @tblColumns WHERE Id = @min;
SET @sql = @sql + CASE WHEN @value <> -1 THEN '(' + @value + ')' ELSE '(MAX)' END;
END
IF(@min < @max)
BEGIN
-- Parameters must be separated by a comma
SET @sql += ',' + CHAR(10) + CHAR(9)
END
END
SET @min += 1;
END
SET @sql += CHAR(10) + 'AS ' + CHAR(10) + CHAR(9) + 'SET NOCOUNT ON;' + CHAR(10) + CHAR(9) + 'SET XACT_ABORT ON;'
+ CHAR(10) + CHAR(10) + CHAR(9) + 'BEGIN TRAN' + CHAR(10) + CHAR(10) + CHAR(9)
+ 'INSERT INTO [' + @schema + '].['+@tblName+'] ('
SELECT @min = MIN(Id) FROM @tblColumns;
-- Get the max Id for table variable
SELECT @max = MAX(Id) FROM @tblColumns;
-- Loop through table columns
WHILE(@min <= @max)
BEGIN
-- Get column name in table
SELECT @ColumnName = Name FROM @tblColumns WHERE Id = @min;
IF(@ColumnName != @pkeyName)
BEGIN
-- Get the column data type
SELECT @value = CAST(DataType AS NVARCHAR) FROM @tblColumns WHERE Name = @ColumnName;
-- timestamps cannot be updated or inserted
IF(@value <> 'timestamp')
BEGIN
-- Set the insert column list
SET @sql += @ColumnName;
IF(@min < @max)
BEGIN
SET @sql += ', '
END
END
END
SET @min += 1;
END
SET @sql += ')' + CHAR(10) + CHAR(9) + 'VALUES ('
SELECT @min = MIN(Id) FROM @tblColumns;
-- Get the max Id for table variable
SELECT @max = MAX(Id) FROM @tblColumns;
-- Loop through table columns
WHILE(@min <= @max)
BEGIN
-- Get column name in table
SELECT @ColumnName = Name FROM @tblColumns WHERE Id = @min;
-- Set the insert values
IF(@ColumnName != @pkeyName)
BEGIN
SELECT @value = CAST(DataType AS NVARCHAR) FROM @tblColumns WHERE Name = @ColumnName;
IF(@value <> 'timestamp')
BEGIN
SET @sql += '@' + @ColumnName
IF(@min < @max)
BEGIN
SET @sql += ', '
END
END
END
SET @min += 1;
END
-- Return the current identity
SET @sql += ')' + CHAR(10) + CHAR(10) + + CHAR(9) + 'SELECT [' + @pkeyName +'] ' + CHAR(10) + CHAR(9)
+ 'FROM [' + @schema + '].['+ @tblName + ']' + CHAR(10) + CHAR(9) + 'WHERE [' + @pkeyName + '] = SCOPE_IDENTITY()'
+ CHAR(10) + CHAR(10) + CHAR(9) + 'COMMIT;' + CHAR(10) --+ 'GO'
exec sp_executesql @sql;
-- Create Update procedure
PRINT 'Creating Procedure: [' + @schema + '].[' + @prefix +@tblName+'Update]'
-- Drop the procedure if it currently exists
SET @sql = 'IF OBJECT_ID(''[' + @schema + '].[' + @prefix +@tblName+'Update]'') IS NOT NULL' + CHAR(10)
+ 'BEGIN' + CHAR(10) + CHAR(9)
+ 'DROP PROC [' + @schema + '].[' + @prefix + @tblName+'Update]' + CHAR(10)
+ 'END' + CHAR(10)
--+ 'GO'
exec sp_executesql @sql;
-- Create update procedure
SET @sql = 'CREATE PROC [' + @schema + '].[' + @prefix +@tblName+'Update]' + CHAR(10) + CHAR(9)
SELECT @min = MIN(Id) FROM @tblColumns;
-- Get the max Id for table variable
SELECT @max = MAX(Id) FROM @tblColumns;
-- Set the parameter list
WHILE(@min <= @max)
BEGIN
-- Get column name in table
SELECT @ColumnName = Name FROM @tblColumns WHERE Id = @min;
SELECT @value = CAST(DataType AS NVARCHAR) FROM @tblColumns WHERE Name = @ColumnName;
-- Cannot modify timestamp fields so we skip them
IF(@value <> 'timestamp')
BEGIN
-- Set parameter list and datatypes
SET @sql += '@' + @ColumnName + ' ' + @value;
SELECT @AnsiPadded = IsAnsiPadded FROM @tblColumns WHERE Id = @min;
IF(@AnsiPadded = 1)
BEGIN
SELECT @value = CASE WHEN LEFT(@value, 1) = 'n' THEN (MaxLength / 2) ELSE MaxLength END FROM @tblColumns WHERE Id = @min;
SET @sql += CASE WHEN @value <> -1 THEN '(' + @value + ')' ELSE '(MAX)' END;
END
IF(@min < @max)
BEGIN
SET @sql += ',' + CHAR(10) + CHAR(9)
END
END
SET @min += 1;
END
SET @sql += CHAR(10) + 'AS ' + CHAR(10) + CHAR(9) + 'SET NOCOUNT ON;' + CHAR(10) + CHAR(9) + 'SET XACT_ABORT ON;'
+ CHAR(10) + CHAR(10) + CHAR(9) + 'BEGIN TRAN' + CHAR(10) + CHAR(10) + CHAR(9)
+ 'UPDATE [' + @schema + '].['+@tblName+']' + CHAR(10) + CHAR(9) + ' SET'
SELECT @min = MIN(Id) FROM @tblColumns;
-- Get the max Id for table variable
SELECT @max = MAX(Id) FROM @tblColumns;
-- Set update conditions
WHILE(@min <= @max)
BEGIN
-- Get column name in table
SELECT @ColumnName = Name FROM @tblColumns WHERE Id = @min;
IF(@ColumnName != @pkeyName)
BEGIN
SELECT @value = CAST(DataType AS NVARCHAR) FROM @tblColumns WHERE Name = @ColumnName;
IF(@value <> 'timestamp')
BEGIN
SET @sql += ' ['+@ColumnName+'] = @' + @ColumnName
IF(@min < @max)
BEGIN
SET @sql += ',' + CHAR(10) + CHAR(9) + CHAR(9) + ' '
END
END
END
SET @min += 1;
END
-- Set where conditions
SET @sql += CHAR(10) + CHAR(9) + ' WHERE [' + @pkeyName + '] = @' + @pkeyName
+ CHAR(10) + +CHAR(10) + CHAR(9) + ' SELECT @' + @pkeyName + ' AS [' + @pkeyName + ']'
+ CHAR(10) + CHAR(10) + CHAR(9) + 'COMMIT;' + CHAR(10)
exec sp_executesql @sql;
PRINT 'Creating Procedure: [' + @schema + '].[' + @prefix + @tblName + 'Delete]'
-- Drop Delete procedure if it currently exists
SET @sql = 'IF OBJECT_ID(''[' + @schema + '].[' + @prefix +@tblName+'Delete]'') IS NOT NULL' + CHAR(10)
+ 'BEGIN' + CHAR(10) + CHAR(9)
+ 'DROP PROC [' + @schema + '].[' + @prefix + @tblName+'Delete]' + CHAR(10)
+ 'END' + CHAR(10)
exec sp_executesql @sql;
-- Get delete parameter
SELECT @value = CAST(DataType AS NVARCHAR) FROM @tblColumns WHERE Name = @pkeyName
-- Set procedure syntax
SET @sql = 'CREATE PROC [' + @schema + '].[' + @prefix +@tblName+'Delete]' + CHAR(10) + CHAR(9)
+ '@'+@pkeyName + ' ' + @value + CHAR(10) + 'AS ' + CHAR(10) + CHAR(9) + 'SET NOCOUNT ON;' + CHAR(10) + CHAR(9) + 'SET XACT_ABORT ON;'
+ CHAR(10) + CHAR(10) + CHAR(9) + 'BEGIN TRAN' + CHAR(10) + CHAR(10) + CHAR(9) + 'DELETE FROM [' + @schema + '].['
+ @tblName + ']' + CHAR(10) + CHAR(9) + 'WHERE [' + @pkeyName + '] = @' + @pkeyName + CHAR(10) + CHAR(10) + CHAR(9) + 'COMMIT;'
+ CHAR(10) + CHAR(10)
exec sp_executesql @sql;
END
ELSE
BEGIN
PRINT CHAR(10) + 'Table ' + @tblName + ' does not have a primary key column and therefore CRUD statements could not be generated' + CHAR(10)
END
END
-- Get the next table to alter
FETCH NEXT FROM DBtables INTO @tblName
END
-- Close cursor
CLOSE DBtables
DEALLOCATE DBtables
PRINT 'FINISHED'