Category Archives: Uncategorized

“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 shrink the transaction log and tempdb.

Adding a Transaction Log at runtime to prevent it from running out of space

sometimes, when running a big transaction, the transaction log can become full or simply grow to a huge size . while you can sometimes free some space using “backup log db-name with truncate_only” and trying to shrink the DB using SQL Management studio.


The process is described in the above link but basically it is: right click on the Database name ->tasks->shrink->files and choosing the log file.
This will shrink the db if there is free space in the transaction log but it can only serve as a temporary solution.

If the transaction is still running and you are afraid that the hard drive will run out of disk space, a better solution would be to create another transaction log on a different drive. This will solve the disk space issue and might even improve performance.
To create another transaction log file  using the SQL Server Management Studio:

    1) Expand the DataBases node and choose your Database
    2) Right Click your Database and click Properties.
    3) Select the Files Page
    4) CLick ADD and Navigate to te Database files grid.
    5) Enter a name for the new log file. This name must be unique within the database.
    6) Select the Log file type
    7) select initial size, growth method (auto Growth, fixed Increments or percent). you can also set a maximum size. Set up the path to the new file and file name and Click OK

In Some cases, the file name column might be disabled when adding a log file using the Sql Server Mangment Studio while the transaction is still runing.
In this case you can add the file manually using a query.
Open a query editor and type:


USE master

GO

ALTER DATABASE db-name

ADD LOG FILE

( NAME = addedlog,

FILENAME = ‘I:addedlog.ldf’,

SIZE = 1000MB,

— MAXSIZE = 10000MB,

FILEGROWTH = 10MB)

GO

There are better ways to avoid the Transaction log is full situation and this should be prevented in the the first place by the code but if you got no access to it, this might be a good solution.

Common reasons for IMP-00010 during import

If you try to import a file and you get the following error:

IMP-00010: not a valid export file, header failed verification
IMP-00000: Import terminated unsuccessfully

The cause according to Oracle error description is “Either the file was not generated by Export or it was corrupted”. The first obvious reason might be that the file is corrupted. However, if you downloaded it correctly (using binary transfer in the FTP as opposed to ASCII transfer) and you have no other reason to believe that the file is indeed corrupted. You might want to explore other options.
The most common reason for this error is mismatch of IMP and EXP versions. While it should not be a problem to import a file that was created with an earlier version of EXP using a newer version of IMP, trying to use it the other way will generate the above error. In other words, you can import a file that was created with 10g version of EXP using a 9i version of IMP. If this is the case, try to export the file using a lower version of EXP or try to import the file to a higher version Database. Later you can export it again using a lower version of the EXP utility.
Another reason, over looked many times, is that the file was exported using Data Pump (EXPDP not EXP)  . If you are not the one who exported the file, try to restore it using the Data Pump import utility IMPDP. Since Data Pump is gaining traction and it is creating .dmp files it is possible that the dmp file in your hands was created using it.

Nothing to see (Yet)

The purpose of DB tricks is to serve as an online repository of Data Base  tips I encounter during my work. Sometimes, it can be very frustrating to search (again) for a solution for a problem you already solved.  No matter how important it may look now, chances are you will not remember anything about it when it will happen again after few months. This is the rational behind DB Tricks. In a way, it will be a place where I could store and share solutions and save myself (and maybe you) some research time in the future.

So, in the following days many things are going to change here.  As they say in techcrunch, ” we are in private alpha”. Fisrt post is coming soon.