# Database Discussions > MS SQL Server 7/MS SQL Server 2000 >  DBCC SHRINKFILE isn't working

## azuchetto

When I execute a DBCC SHRINKFILE or try shrinking database files through enterprise manager it works fine, except when I reboot the server the files return to the original size.  Here is the statement I used:

DBCC SHRINKFILE (filename, filesize)

I have also tried using the TRUNCATEONLY option.

----------


## rmiao

What's original file size used when create db? Didn't see that on dbs other than tempdb.

----------


## azuchetto

I am not sure, as I wasn't here when it was set up.  No documentation of course.  Is it stuck at the original size?  I thought by specifying the size in the DBCC SHRINKFILE statement you are resetting the minimum file size.  

Books Online says:
"Use DBCC SHRINKFILE to reduce the size of a file to smaller than its originally created size. The minimum file size for the file is then reset to the newly specified size."

----------


## MAK

are you shrinking tempdb

----------


## azuchetto

No...I am trying to shrink just regular database files.

----------


## rmiao

Yes, should reset file size. In fact, I never saw that on my servers. Did you check free space in the db?

----------


## azuchetto

The size of the database is 141,762.38 MB and the space available is 44,262.82 MB.

----------


## rmiao

Free space in data file or log file? Possible file expanded before rebooting?

----------


## skhanal

What kind of file growth setting you have for the database? Is it allocating fixed number of MBs?

----------


## azuchetto

The data file free space is 28,974.07 MB and the log file free space is 15,989.5 MB.  I am only trying to shrink the data file, not the log file.  I'm  pretty sure the file growth occurs when SQL Server starts.  I've tried shrinking a few times and every time the file stays at the new size until the server is rebooted (SQL Server restarts).  

The data file is set to automatically grow at a fixed size of 500 MB.

----------


## MAK

--Any startup procedure that you have on the server. Run this following command

use master
go
select name from sysobjects where type ='p' and  OBJECTPROPERTY(id,'ExecIsStartup')=1
go

if you find any procedure listed, then execute the following command

sp_helptext procedurename

----------


## azuchetto

These are the only two start up procedures:
sp_MSrepl_startup (used for replication)
sp_LEServer_Start (used for Log Explorer)
Just to make sure I looked at both stored procs and they don't do anything to database size.

----------


## MAK

Can you see anything on the sql server errorlog regarding, database expanding or any stored procedure fired?

----------


## azuchetto

It's been a couple months since I last tried to shrink the files.  So I don't have log files dating that far back.  As you could imagine, it is a rather lengthy after hours operation, so I haven't had a chance to try again since.  The log files that I do have, which include several reboots (we do one scheduled reboot every weekend) do not contain anything suspicious.

----------


## azuchetto

I found some new info. at msdn2.  It says:

_Consider the following information when you plan to run a shrink operation:

A shrink operation is most effective after an operation that creates lots of unused space, such as a truncate table or a drop table operation. 

Most databases require some space to be available for regular day-to-day operations. If you repeatedly shrink a database and notice that the database size grows again, this indicates that the space that was shrunk is required for regular operations. In these cases, repeatedly shrinking the database is a wasted operation.

A shrink operation does not preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree. This is another reason not to repeatedly shrink the database._



I think what might be happening is when I shrink the file it causes some fragmentation.  Then after the server reboots I have a job that runs DBCC REINDEX against any index with excesive fragmentation.  This reindexing causes the file sizes to grow.

----------


## rmiao

Why rebuild index when start sql? You can do that during off hours.

----------


## azuchetto

The whole process is done during off hours.  We do a server reboot, disk defrag, and index rebuilding at 2am every Sunday morning.

----------


## MAK

Your DBCC Shrink file job should executed after the DBCC reindex job.

----------


## azuchetto

Thanks for the advice.  Next time I do a shrink I will turn off the reindex job.  Then if the file doesn't grow, that will be a good indication that the reindexing was the cause of the file growth.

----------

