Shrinkfile

Question

Post reply

Shrinkfile

qwerty 87425

SSC Veteran

Points: 237
More actions
April 29, 2011 at 6:46 pm

#237431

I'm having problems with Shrinkfile. I wish to use the option to migrate the data to other files, and then delete the file.
It takes several hours to operate a single shrink operation. And then it usually gives an error message. It only works sometimes.
Any ideas what could be wrong?
I am, by the way, using SQL Server management Studio. How do I get the file ID number to run it as a SQL statement?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 1

qwerty 87425 (4/29/2011)
And then it usually gives an error message.
...
...
Any ideas what could be wrong?

Not without seeing the error message you're getting. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

qwerty 87425 SSC Veteran Points: 237 More actions · Answer 2

An exception occurred while executing a Transact-SQL statement or batch.

(Microsoft.SqlServer.ConnectionInfo)

Cannot move all contents of file "Data_File_12" to other places to complete the

emptyfile operation. DBCC execution completed. If DBCC printed error

messages, contact your system administrator. (Microsoft SQL Server, Error:

2555)

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 3

What query are you running to empty the file ?

Thank You,

Best Regards,

SQLBuddy

qwerty 87425 SSC Veteran Points: 237 More actions · Answer 4

I'm not running a query, I'm using Tasks > Shrink > Files in the management studio.

Syed Jahanzaib Bin hassan SSChampion Points: 11410 More actions · Answer 5

First thing is you cant delete primary file and Second thing is organize the database with

USE [database name]

GO

DBCC SHRINKDATABASE(N'Database Name', 1 )

GO

It will works for all data files and then apply

USE [Database Name]

GO

DBCC SHRINKFILE (N'Database Name' , EMPTYFILE)

GO

how many drives you have not partitions

Regards,

Syed Jahanzaib Bin Hassan

MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

My Blog

http://www.aureus-salah.com

My Blog
www.aureus-salah.com

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 6

This is how you need to remove a file

http://msdn.microsoft.com/en-us/library/ms189493.aspx

D. Emptying a file

The following example demonstrates the procedure for emptying a file so that it can be removed from the database. For the purposes of this example, a data file is first created and it is assumed that the file contains data.

USE AdventureWorks2008R2;

GO

-- Create a data file and assume it contains data.

ALTER DATABASE AdventureWorks2008R2

ADD FILE (

NAME = Test1data,

FILENAME = 'C:\t1data.ndf',

SIZE = 5MB

);

GO

-- Empty the data file.

DBCC SHRINKFILE (Test1data, EMPTYFILE);

GO

-- Remove the data file from the database.

ALTER DATABASE AdventureWorks2008R2

REMOVE FILE Test1data;

GO

Thank You,

Best Regards,

SQLBuddy