Hi All!
We have SiteProtector 2.0 SP4 and we have a single MDF file that is
33GB. I have repeatedly purgef data prior to a specific date out of the
database (e.g. exec iss_PurgeAnalysisLog '10/31/2005'). When I query the
database, I see that the older data is gone. In the database properties,
I have set the "auto shrink" option and in the Database Maintenance tab
of the site protector console.
The problem is that the database does not appear to be shrinking.
Instead, we have to reqularly add space to the database file. It is now
so large, we cannot create a database backup because there is not enough
free space.
I have tried running DBCC SHRINKDATABASE("RealSecure", TRUNCATEONLY),
but the size of the file did not change. Reading the instructions on
this MSDN page:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref
/ts_dbcc_3pd1.asp
It says:
TRUNCATEONLY: Causes any unused space in the data files to be released
to the operating system and shrinks the file to the last allocated
extent, reducing the file size without moving any data.
My assumption is that the database file should get smaller. However,
what bothers me is the part "reducing the file size without moving any
data." If there is data in the very last blocks of the data file and no
data is moved, then it seems to me that it cannot truncate anything and
the size does not change, which is what I am seeing.
My first question is why the "auto shrink" option does not appear to
work. Second, I tried to first run something like DBCC
SHRINKDATABASE(RealSecure, 50, NOTRUNCATE) to move the data to the
beginning of the data file and then DBCC SHRINKDATABASE(RealSecure,
TRUNCATEONLY) to truncate the end of it. However, the file does not
change substantially (now about 31GB).
I am fairly certain that the data has been purge from the database, so
my assumption is that I am doing something wrong with the truncation at
the MSSQL Server level. Purging everything older than three months
should bring the DB file down **alot** farther. Is there anything at the
MSSQL Server level that would be preventing me from compacting the
database more than I expect? Anything the SP Server is doing?
Thanks for your time!
Regards,
Jim Mohr
---------------------------------------
"Be more concerned with your character than with your
reputation. Your character is what you really are while
your reputation is merely what others think you are." --
John Wooden
---------------------------------------
Be sure to visit the Linux Tutorial:
http://www.linux-tutorial.info
---------------------------------------
_______________________________________________
ISSForum mailing list
[email protected]
TO UNSUBSCRIBE OR CHANGE YOUR SUBSCRIPTION, go to
https://atla-mm1.iss.net/mailman/listinfo/issforum
To contact the ISSForum Moderator, send email to [EMAIL PROTECTED]
The ISSForum mailing list is hosted and managed by Internet Security Systems,
6303 Barfield Road, Atlanta, Georgia, USA 30328.
