-
DBCC SHRINKFILE isn't working
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.
-
What's original file size used when create db? Didn't see that on dbs other than tempdb.
-
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."
-
-
No...I am trying to shrink just regular database files.
-
Yes, should reset file size. In fact, I never saw that on my servers. Did you check free space in the db?
-
The size of the database is 141,762.38 MB and the space available is 44,262.82 MB.
-
Free space in data file or log file? Possible file expanded before rebooting?
-
What kind of file growth setting you have for the database? Is it allocating fixed number of MBs?
-
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.
-
--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
-
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.
-
Can you see anything on the sql server errorlog regarding, database expanding or any stored procedure fired?
-
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.
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|