Внутренняя ошибка буфер предоставленный для считывания значения столбца слишком мал

Answers

• Do you have latest backup..restote order shoud be Latest full backup(no recovery)…latest Diff backup taken after ful bkp(no recovery) and then rn log backps taken after diff backup u applied .

  I thinks its best solution …

  .I presume this is some hardware issue..no so sure about it..

  Get ur harware checked may be some Bad sectors…

  Also you can try running DBCC checkdb in single_user mode ( try if it allows) and post result here

  Hope this helps.keep us posted

  ---

  Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

  • Proposed as answer by

    Monday, June 3, 2013 9:14 AM

  • Marked as answer by
    Fanny Liu
    Tuesday, June 4, 2013 9:58 AM

Ответы

• Do you have latest backup..restote order shoud be Latest full backup(no recovery)…latest Diff backup taken after ful bkp(no recovery) and then rn log backps taken after diff backup u applied .

  I thinks its best solution …

  .I presume this is some hardware issue..no so sure about it..

  Get ur harware checked may be some Bad sectors…

  Also you can try running DBCC checkdb in single_user mode ( try if it allows) and post result here

  Hope this helps.keep us posted

  ---

  Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

  • Предложено в качестве ответа

    3 июня 2013 г. 9:14

  • Помечено в качестве ответа
    Fanny Liu
    4 июня 2013 г. 9:58

Про плюсы и минусы сжатия можно почитать в оригинальной статье //infostart.ru/public/114634/ или на msdn https://msdn.microsoft.com/ru-ru/library/cc280449(v=sql.120).aspx основным плюсом идет экономия ввода вывода и места на диске, основным минусом — существенное увеличение регламентных операций СУБД и небольшое увеличение нагрузки на процессор.

В management studio правой кнопкой мыши на базе, new query, вставляем текст, нажимаем execute (или F5):

DECLARE @Table_catalog NVARCHAR(128)
DECLARE @Table_schema NVARCHAR(128)
DECLARE @Table_name NVARCHAR(128)
DECLARE @Index_Name NVARCHAR(128)

DECLARE @cmd VARCHAR(4000)

-- включение сжатия для таблиц
DECLARE TableNameCursor CURSOR
FOR
SELECT Table_catalog, Table_schema, Table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY Table_catalog, Table_schema, Table_name

OPEN TableNameCursor

FETCH NEXT FROM TableNameCursor INTO @Table_catalog, @Table_schema, @Table_name
WHILE @@fetch_status = 0
BEGIN

PRINT @Table_catalog + '.' + @Table_schema +  '.' + @Table_name
SET @cmd = 'ALTER TABLE [' + @Table_catalog + '].[' + @Table_schema + '].[' + @Table_name + '] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)'
EXEC (@cmd)

FETCH NEXT FROM TableNameCursor INTO @Table_catalog, @Table_schema, @Table_name
END
CLOSE TableNameCursor
DEALLOCATE TableNameCursor

-- включение сжатия для индексов
DECLARE IndexCursor CURSOR
FOR
SELECT  DB_NAME(), schemas.name, tables.name, indexes.name
FROM sys.schemas as schemas inner join sys.tables as tables inner join sys.indexes as indexes on tables.object_id = indexes.object_id on schemas.schema_id = tables.schema_id
ORDER BY schemas.name, tables.name, indexes.name;

OPEN IndexCursor

FETCH NEXT FROM IndexCursor INTO @Table_catalog, @Table_schema, @Table_name, @Index_Name
WHILE @@fetch_status = 0
BEGIN

PRINT @Table_catalog + '.' + @Table_schema + '.' + @Table_name +  ': ' + @Index_Name

SET @cmd = 'ALTER INDEX [' + @Index_Name + '] ON [' + @Table_catalog + '].[' + @Table_schema + '].[' + @Table_name + '] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)'
EXEC (@cmd)

FETCH NEXT FROM IndexCursor INTO @Table_catalog, @Table_schema, @Table_name, @Index_Name
END
CLOSE IndexCursor
DEALLOCATE IndexCursor

-- делаем шринк базы - возвращаем свободное место на диск
SELECT @cmd=(
SELECT 'DBCC SHRINKDATABASE('''+ DB_NAME() + ''')'
)
EXEC (@cmd)

Скрипт выводит в раздел messages ход работы

Для того, чтобы результаты сохранились после реструктуризации, следует добавить триггеры из оригинальной статьи.

Для выключения сжатия надо запустить скрипт, заменив в нем DATA_COMPRESSION = PAGE на DATA_COMPRESSION = NONE