Primary Data File | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Primary Data File

[V]— Tearing my hair out —- I have recently gone through the process of "archiving" data from some of the active databases. The data files for these databases have been set by the previous dba to auto grow WITH NO MAX! The data files had grown to over 12gig and I have reduced the actual data size to 4 gig but cannot get the data file to release the 8gig that has been freed. – The data file is in Primary group
– I have truncated log files
– I have tried the dbcc shrinkfile Any suggestions?
Use DBCC SHRINKDB to shrink the database files that will take care of the filegroups.
Refer ot the books online for more information. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
is there definitely 8GB free? does sp_spaceused @updateusage=true confirm this? someone posted a very similar problem here recently.. I don’t know if we got to the bottom of it, it was strange. Tom Pullen
DBA, Oxfam GB
Thanks for the suggestions, but I have verified the size of the file and it is correct, I have also run dbcc shrinkdatabase (sql 2000) multiple times, along with dbcc shrinkfile, I have also re-indexed the tables and repeated the process in different orders with no glory. I have noticed that if I continually run the shrink function from enterprise that the file releases some of the freed space by about 20kb at a time. This as you will understand is not a feasible method for releasing 8gb. Is there any thing else you can suggest??
Do you have OS-level file fragmentation? Shot in the dark, but maybe it’s having an effect. Don’t know what your downtime situation is, but I’d consider:- backing up the database (to disk and tape, with verify both). Drop the database. Defragment the drive in Windows. Restore the database, hopefully the data file will be written into a nice new contiguous file. Try your shrink again then. It might not work but it sounds like something iffy on a file level might be happening. Tom Pullen
DBA, Oxfam GB
BOL:
quote:The target size for data and log files as calculated by DBCC SHRINKDATABASE can never be smaller than the minimum size of a file. The minimum size of a file is the size specified when the file was originally created, or the last explicit size set with a file size changing operation such as ALTER DATABASE with the MODIFY FILE option or DBCC SHRINKFILE.
Have you checked initial size of your db?
Sadly the db is mission critical and cannot be dropped without creating a restored copy of the database to another location and re-direct the input to the restored copy while I drop the original, I will then have to implement a replication base to update the original after restore before changing back control. Was hoping not to go to that degree of effort. The original size of the db on creation wsa 2gb, I will keep all posted as to the outcome for future reference.
Thanks for your input.
Stephen, what service pack level are you on? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
Hey Derrick, Im on SP3.
Is it possible to keep database in exclusive mode and run the SHRINK processto get the results. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Perhaps a little background… I work for a company that deals with the communication side of IT – mainly SMS competitions, 1900 IVR diversions etc. So at any one time we may have multiple connections through various tele cariers accessing the DB – and as we all know our view for resources tends not to be shared by the accounts section of the business, so few options exist. The DB is within an environment of 11 Clustered Servers, two blade centres and some 100 odd db’s that at any time may require relationship changes (as competitions can vary structure, charges, and carrier options). The short version…. if you take one piece out of the jigsaw it all comes apart. This is not an urgent issue but certainly one that requires attention at some point, so any suggestions are appreciated. My main restrasint is that it must be live. Thanks.
Have you tried using the target_size option when you use DBCC SHRINKFILE? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
Tried both thru Enterprise and Analyser.
Are you perhaps able to restore a version of the database somewhere else, so that other options could perhaps be tried (without risking the production database?) Cheers
Twan
Check the active portion of transaction log and usage by running DBCC SQLPERF(LOGSPACE) and also SP_HELPDB to determin the files of the database. What kind of job were scheduled during weekends on thsi database?
What kind of activity you will have on this databases in a day? Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
]]>