SQL Server - Shrink failed for DataFile - Asked By venky c on 21-Sep-11 01:51 AM


Hi All,

While i am trying to shrink the database i am getting below error
db size 120 gb
availabe disk space 80 gb on the disk
Version:
Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)   Mar 29 2009 10:11:52   Copyright (c) 1988-2008 Microsoft Corporation  Standard Edition (64-bit) on Windows NT 5.2 <X64> (Build 3790: Service Pack 2) (VM)
 

TITLE: Microsoft SQL Server Management Studio
------------------------------

Shrink failed for DataFile 'THEMIS_Data'.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.1600.22+((SQL_PreRelease).080709-1414+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Shrink+DataFile&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

A severe error occurred on the current command.  The results, if any, should be discarded. (Microsoft SQL Server, Error: 0)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.2531&EvtSrc=MSSQLServer&EvtID=0&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

Can any one help me on

Thanks ,
Venky

Web Star replied to venky c on 21-Sep-11 02:00 AM

You can run the following:

 

Code Snippet

select session_id, Text

from sys.dm_exec_requests r

cross apply sys.dm_exec_sql_text(sql_handle) t

Suchit shah replied to venky c on 21-Sep-11 02:01 AM

Did I save you some time? Buy me a coffee

Recent Comments

  • http://dbtricks.com/?p=36#comment-438: coool!! thanx
  • http://dbtricks.com/?p=39#comment-437: Thanks a lot. It worked for me.
  • http://dbtricks.com/?p=53#comment-436: Thank you very much. This saved me time and frustration.
  • http://dbtricks.com/?p=34#comment-435: GREAT Thanks! This helped a lot!
  • http://dbtricks.com/?p=36#comment-431: I also had this on XP SP3 when installing 10.2.0.4 patchset and after turning off all services on the...

Most Popular Posts

  • http://dbtricks.com/?p=34
  • http://dbtricks.com/?p=53
  • http://dbtricks.com/?p=36
  • http://dbtricks.com/?p=39
  • http://dbtricks.com/?p=55

Links:

  • Blogroll

    • http://www.dba-oracle.com/
    • http://metalink.oracle.com/
    • http://docs.oracle.com/home_content.html
    • http://www.orafaq.com/
    • http://www.psoug.org/library.html

Tags

http://dbtricks.com/?tag=alter-tablespacehttp://dbtricks.com/?tag=alte-tablehttp://dbtricks.com/?tag=connectionhttp://dbtricks.com/?tag=data-pumphttp://dbtricks.com/?tag=deletehttp://dbtricks.com/?tag=dhcphttp://dbtricks.com/?tag=exphttp://dbtricks.com/?tag=expdphttp://dbtricks.com/?tag=exporthttp://dbtricks.com/?tag=fullhttp://dbtricks.com/?tag=imphttp://dbtricks.com/?tag=impdbhttp://dbtricks.com/?tag=impdphttp://dbtricks.com/?tag=importhttp://dbtricks.com/?tag=installhttp://dbtricks.com/?tag=ldfhttp://dbtricks.com/?tag=odbchttp://dbtricks.com/?tag=optimizerhttp://dbtricks.com/?tag=ora-00600http://dbtricks.com/?tag=ora-01008http://dbtricks.com/?tag=ora-01110http://dbtricks.com/?tag=ora-01113http://dbtricks.com/?tag=ora-01653http://dbtricks.com/?tag=ora-01691http://dbtricks.com/?tag=ora-01758http://dbtricks.com/?tag=ora-12520http://dbtricks.com/?tag=ora-12638http://dbtricks.com/?tag=ora-28001http://dbtricks.com/?tag=ora-39171http://dbtricks.com/?tag=oraclehttp://dbtricks.com/?tag=oracle-clienthttp://dbtricks.com/?tag=oracle-enterprise-managerhttp://dbtricks.com/?tag=oracle-patchhttp://dbtricks.com/?tag=oracle-xehttp://dbtricks.com/?tag=oracle-xe-clienthttp://dbtricks.com/?tag=os-authenticationhttp://dbtricks.com/?tag=oui-has-stopped-workinghttp://dbtricks.com/?tag=processeshttp://dbtricks.com/?tag=recoverhttp://dbtricks.com/?tag=sqlserverhttp://dbtricks.com/?tag=sql-server-2005http://dbtricks.com/?tag=tablespacehttp://dbtricks.com/?tag=transaction-loghttp://dbtricks.com/?tag=windows-2008http://dbtricks.com/?tag=xe

“Shrink failed for Database” when attempting to shrink a data file.

Sometimes, when you try to shrink a data file you may get the following message:

TITLE: Microsoft SQL Server Management Studio
——————————
Shrink failed for Database ‘Data base name’. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Shrink+Database&LinkId=20476
——————————
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————
A severe error occurred on the current command. The results, if any, should be discarded. (Microsoft SQL Server, Error: 0)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=0&LinkId=20476


First, you should know that shrinking data files is not recommended and will hurt performance. Shrinking data files will cause index fragmentation. You can recreate the indexes but it will require the freed space. Shrinking will also cause fragmentation in the server’s file system which will slow it even more. In addition, since every page move is logged to the transaction log, chances are that the transaction log will claim the same space.

However, if in case you decide to shrink the database anyway (for example, after a large and permanent delete, or in case it is a test system) a common reason for this error is lack of space.
Since many times the attempt to shrink a database will come after discovering that the drive is running out of space, it would only make sense that there is not enough space for the shrinking process itself.

To verify, open the sql server error log (usually under Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG) and look for Operating system error 112(There is not enough space on the disk.).

To solve, you should clear some space to allow the process to complete. If you can not free some disk space, Try to http://dbtricks.com/?p=14 and tempdb.

Web Star replied to venky c on 21-Sep-11 02:01 AM

it is possible that the reason is that your recovery mode is is set to full.
to change this:

1) Right click on the db (in SQL server Managment Studio) and click properties.

2) Navigate to the option tab and make Make sure that the recovery model is set to simple.

If a full recovery mode is needed:

Issue regular backups (in the transaction log shipping tab)
and if Auto Shrink is enabled, the file will eventually shrink (though not immediatly)

If you need to shrink the file immediately:

•    Open MS SQL Server Management Studio, connect to Database Engine

•    Select New Query and type:  backup log <db_name> with truncate_only

(this is http://msdn.microsoft.com/en-us/library/ms144262.aspx – Microsoft recommends using Simple mode instead)
•    Execute this query (press F5)
•    Right click on Database name and navigate to Tasks->Shrink ->Files:

Reena Jain replied to venky c on 21-Sep-11 02:07 AM
hi,

First, you should know that shrinking data files is not recommended and will hurt performance. Shrinking data files will cause index fragmentation. You can recreate the indexes but it will require the freed space. Shrinking will also cause fragmentation in the server’s file system which will slow it even more. In addition, since every page move is logged to the transaction log, chances are that the transaction log will claim the same space.

However, if in case you decide to shrink the database anyway (for example, after a large and permanent delete, or in case it is a test system) a common reason for this error is lack of space.
Since many times the attempt to shrink a database will come after discovering that the drive is running out of space, it would only make sense that there is not enough space for the shrinking process itself.

To verify, open the sql server error log (usually under Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG) and look for Operating system error 112(There is not enough space on the disk.).


To solve, you should clear some space to allow the process to complete. If you
venky c replied to Reena Jain on 21-Sep-11 02:24 AM
Hi Reena,

from the following path i did not find any error messages. Only i observed the below message irrespective of shrink Process time

2025-09-20 05:19:15.61 spid1s    A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 1805 seconds. Working set (KB): 44900, committed (KB): 140472, memory utilization: 31%.

I changed recovery model to simple still i am getting error, have 80gb free space on the disk.

Can you please provide any solution asap?

Thanks
Venky

Anoop S replied to venky c on 21-Sep-11 03:10 AM
There are a few reasons why shrink might fail. One possibility is that you don't have sufficient transaction log space for the shrink operation. Check your SQL Server error log.

You should NOT run shrink on a regular basis. Here are two articles that explain why shrinking is almost always a bad idea:

http://www.karaszi.com/SQLServer/info_dont_shrink.asp
http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/13/629059.aspx