Hi, Joe.
Is there an equivalent to Access's compact/repair?
One can “compact†a SQL Server database by using the SQL Server Enterprise
Manager console or Transact-SQL to manipulate database settings. Enterprise
Manager can be used to manually shrink the database using the “Shrink
Database†dialog window or to automatically shrink the database using the
database’s “Autoshrink†setting. The Transact-SQL needed to shrink the
database or transaction log files are:
DBCC SHRINKDATABASE (DBName [, targetPercent] [, {NOTRUNCATE | TRUNCATEONLY}])
or:
DBCC SHRINKFILE ({FileName | FileID} { [, targetSize] | [, {EMPTYFILE |
NOTRUNCATE | TRUNCATEONLY} ] })
Use DBCC SHRINKDATABASE to shrink all of the data files for the database and
DBCC SHRINKFILE to shrink an individual data file or transaction file.
If you are wondering if the Transact-SQL can be run from within Access, I
imagine (as I’ve never tried this myself) that one could use a SQL
Passthrough Query in the Access SQL View pane to write a stored procedure
that uses the user ID and password in the connection string of a user that
has permissions to run these database commands (usually the “sa†user), and
then run this query from Access.
SQL Server doesn’t have an equivalent “repair†feature that Access has, but
since the “repair†is mainly just cleaning up incomplete transactions
(something that database servers do automatically by “rolling back†any
incomplete transactions) and repairing very simple data structure boo-boos
like lost pointers to multi-page records (which is why “repair†doesn’t
always work to save a corrupted Access database), there really isn’t a need
for this “manual repair†feature in SQL Server.
Shrinking the database or files may not help much with the query
optimization (as compacting in Access does) that uses the distribution
statistics kept on the indexes, though. The statistics will need to be
updated by using the “UPDATE STATISTICS†command in Transact-SQL:
EXEC (“UPDATE STATISTICS “ + @sTableName)
where @sTableName is the local variable indicating the name of the table.
One can specify the size and number of the data files and transaction log
files at creation time, but one can also use Enterpise Manager settings or
use Transact-SQL to automatically expand each of these files by a size
percentage or by an absolute size as needed.
For more information, read the BOL. The latest version can be downloaded
from the following Web page:
http://www.microsoft.com/downloads/...b1-a420-445f-8a4b-bd77a7da194b&DisplayLang=en
HTH.
Gunny
See
http://www.QBuilt.com for all your database needs.
See
http://www.Access.QBuilt.com for Microsoft Access tips.
(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)
- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.
