April 16, 2008 at 1:17 am
Hi!
Anyone knows a way to find out the initial size of a Primary data file?
Since a data file cannot be shrunk more then to it's initial size, this seems like interesting information.
On a follow up, does anyone know how to work around this? Is it in any way possible to shrink a file to less then it's initial size?
Regards,
Hanslindgren
April 17, 2008 at 9:49 am
you cannot shrink a whole database to smaller than its original size but you can an individual file using dbcc shrinkfile rather than dbcc shrinkdatabase, so basicaly, use dbcc shrinkfile.......
see BOL 'shrinking a database'
---------------------------------------------------------------------
April 17, 2008 at 10:39 am
DBCC SHRINKFILE (DATAFileNAME, target_size)
"target_size
Is the desired size for the file in megabytes, expressed as an integer. If not specified, DBCC SHRINKFILE reduces the size to the default file size.
If target_size is specified, DBCC SHRINKFILE attempts to shrink the file to the specified size. Used pages in the part of the file to be freed are relocated to available free space in the part of the file retained. For example, if there is a 10-MB data file, a DBCC SHRINKFILE with a target_size of 8 causes all used pages in the last 2 MB of the file to be reallocated into any available free slots in the first 8 MB of the file. DBCC SHRINKFILE does not shrink a file past the size needed to store the data in the file. For example, if 7 MB of a 10-MB data file is used, a DBCC SHRINKFILE statement with a target_size of 6 shrinks the file to only 7 MB, not 6 MB."
April 18, 2008 at 1:53 am
Thanks, yes. BOL is usually a good source of wisdom!
But under Remarks:
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.
And then under Result Sets:
MinimumSize The number of 8-KB pages the file could occupy, at minimum. This corresponds to the minimum size or originally created size of a file.
But the important thing I missed was under Arguments:
target_size
Is the desired size for the file in megabytes, expressed as an integer. If not specified, DBCC SHRINKFILE reduces the size to the default file size.
And that was the reason for my file not shrinking more then I wanted it to.
Thanx!
Best Regards,
Hanslindgren
April 18, 2008 at 2:22 am
Check the "size" value in master..sysaltfiles (sql2k) or sys.sysaltfiles (sql2k5)
April 21, 2008 at 2:00 am
Rajan John (4/18/2008)
Check the "size" value in master..sysaltfiles (sql2k) or sys.sysaltfiles (sql2k5)
Correct me if I'm wrong, but isn't this the current size?
April 24, 2008 at 3:54 am
nope. The size column of the sysfiles table of each database will give u the current size (in pages). sysaltfiles givesyou the initial values.
April 24, 2008 at 4:02 am
Rajan John (4/24/2008)
nope. The size column of the sysfiles table of each database will give u the current size (in pages). sysaltfiles givesyou the initial values.
Are you sure? Have you looked at the values recently? Maybe they have changed with a SP or something but on my .2040 version they are exactly the same...
Regards,
Hanslindgren
April 24, 2008 at 6:22 am
hmmm..yes u are right; the size column does get updated in sql2k5. This wasnt the case with 2k. I checked other tables as well like master_files; they also are getting updated with the growth. I will look further and let u know if I find a way. Sorry for misleading
April 25, 2008 at 3:05 pm
I'm running S2K, sp4 and both the size and growth columns are the same value on both tables. This is for all my databases that I know have grown, both manually and auto-grown.
-- You can't be late until you show up.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply