4

When selecting the Transaction Log file from the dropdown on the Shrink Files screen in SSMS, I get the following error:

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
Additional information: An invalid Floating point operation occurred. (Microsoft SQL Server, Error: 3623)

It is happening on the same database on 2 different servers (restored from a SQL 2008 R2 Enterprise Ed. server to a SQL 2016 Standard Edition server nightly). There are older copies spawned of the same database on QA and Dev from dates 2 months ago, and they do not experience the issue, so it is something that happened recently.

I have tried:

  1. DBCC CHECKDB - no errors
  2. Took DB offline, deleted transaction log, recreated t-log using T-SQL. - No effect on error message
  3. Detached DB, Reattached - No effect on error message
  4. Set DB to FULL recovery mode (was previously on SIMPLE), allowed users to generate a good amount of transactions successfully. - No effect on error message
  5. Set DB back to SIMPLE recovery mode. - No effect on error message
  6. Attempted DBCC SHRINKFILE (<database_name>_log, 1). - No effect on error message. However, running this did generate the following message:

    Msg 8985, Level 16, State 1, Line 24 Could not locate file '<database_name>_log' for database '<database_name>' in sys.database_files. The file either does not exist, or was dropped.

... Which made me decide to go do a select over sys.database_files and see if anything looked out of place. It looks normal to me... look and see if you see anything that looks abnormal:

file_id= 2 
file_guid= 2BA3CDBB-3F3F-4F81-B067-25712493E963 
type= 1
type_desc= LOG  
data_space_id= 0    
name= <database_name>_log
physical_name= L:\MSSQL\Data\<database_name>.ldf
state= 0 
state_desc= ONLINE
size= 64000
max_size= 268435456
growth= 64000   
is_media_read_only= 0
is_read_only= 0
is_sparse= 0
is_percent_growth= 0
is_name_reserved= 0
create_lsn= NULL
drop_lsn= NULL  
read_only_lsn= NULL 
read_write_lsn= NULL    
differential_base_lsn= NULL 
differential_base_guid= NULL    
differential_base_time= NULL    
redo_start_lsn= NULL    
redo_start_fork_guid= NULL  
redo_target_lsn= NULL   
redo_target_fork_guid= NULL 
backup_lsn= NULL

UPDATE Since now I'm able to shrink the log using T-SQL (DBCC SHRINKFILE (_log,...) ), I'm lowering the urgency of a fix. However, I would still like to know if anyone has a fix for the original error, when shrinking using the GUI in SSMS.

Improve this question
3
  • The Msg 8985 error is almost certainly because you ran the SHRINKFILE command from the wrong database (or while the database was detached).
    – Aaron Bertrand
    Commented Jan 23, 2018 at 0:49
  • Is it correct that your server is a SQL Server 2016? Are you maybe using an older version of SSMS?
    – Magier
    Commented Jan 23, 2018 at 9:18
  • Using SSMS 2017, patched to 14.0.17213.0 SQL version is 13.0.4206.0 DB compatibility level is SQL Server 2016 (130)
    – Sil8ntDBA
    Commented Mar 5, 2018 at 17:20

1 Answer 1

Reset to default
1

You should update your question with the full @@version of your server.

You mentioned only that you use SQL Server 2008 R2, if you are only on SP2 this hotfix is for you: Cumulative update package 8 for SQL Server 2008 R2 Service Pack 2

Among the issues it fixes, there is yours:

enter image description here

But I recommend you to patch your server to SP3.

Improve this answer
9
  • It's SQL 2016... I am able to shrink the file with the DBCC SHRINKFILE command... just wondering what could be ailing the GUI.
    – Sil8ntDBA
    Commented Mar 5, 2018 at 17:47
  • But OP says: Attempted DBCC SHRINKFILE (<database_name>_log, 1). - No effect on error message. However, running this did generate the following message: Msg 8985, Level 16, State 1, Line 24... So how are you able to shrink using the command if it produces this error?
    – sepupic
    Commented Mar 6, 2018 at 8:06
  • the error I get is not 8985... this is my error: Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc) Additional information: An invalid Floating point operation occurred. (Microsoft SQL Server, Error: 3623) I get the error when I change the selection from "Data" file to "Log" file in the drop-down menu on the "shrink files" screen in SSMS.
    – Sil8ntDBA
    Commented Mar 6, 2018 at 17:48
  • This is not server error, it's smth that has to do with GUI(SSMS), what is your SSMS version?
    – sepupic
    Commented Mar 7, 2018 at 7:22
  • USE [database_name] GO DBCC SHRINKFILE (N'<database_logical_name>_log' , 30720) GO
    – Sil8ntDBA
    Commented Mar 7, 2018 at 16:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.