Minimising / Compacting MS SQL Server DB

Hello Group

I'm from an ACCESS background and don't know too much about SQL Server.

In ACCESS you would periodically do a COMPACT on a database to help keep it's size down, particularly if lots of records had been deleted.

Do you need to worry about this on a MS SQL Server database, and if so how do you go about it?

Thanks

PeteB

 

Hi Bruce

I've done some reading on the DBCC SHRINKDATABASE command and this seems to be the thing to use.

I've seen examples of it being used in Enterprise Manager (which doesn't apply with our web interface, of course) and lots of sytax examples but nothing that makes it clear as to how I should implement it in our shared environment.

If for example I wanted to SHRINK the sample PUBS database, where do I apply DBCC SHRINKDATABASE to it? Does it go in a procedure to be called or is there a command line type structure or ...?

If it does go into a Stored Procedure or similar are there any example to look at, please?

Thanks

 

Hi Bruce

I've tried to read up on, and have tried to applytheinformation that you've supplied over a couple of hours, but unfortunately I can't fathom WHERE to apply this information.

Where do I put the USE and SHRINKDB commands? In a Function, Procedure, Command Line?

Sorry for being a bit thick on this.

Thanks

PeteB

 

You can use those command by connecting to the SQL server using query analyzer.

 

Thanks, Danny.


That at least tells me that the command was successful even though the size doesn't seem to have changed. Perhaps there's not enough slack in the databases to reclaim yet.


In my reading I saw that it was recommended as of SQL Server 2000 NOT to use DBCC ShrinKDB, but DBCC ShrinkDatabase instead.


Both of these commands returned'successful'from Query Analyzer and with the same results.


Thanks again.


PeteB

 

I've been a SQL Server DBA for 15 years. I first used SQL Server 4.5.


SQL Server was designed to automaticallyrecover disk space. Under normal load conditions, there is never a need to ShrinkDB. Given 24 hours, SQL server will handle the problem itself. ShrinkDB is only necessary after some type of massive activity. Like a bulk load of 500k records or an update that alters 100k key values. And even then, if given 24 hours, SQL Server will recover the space itself.


Bottom line ... forget it. Let SQL server managedisk spaceitself. Besides, to ShrinkDB effectively, all users "should" be forced OFF, effecively killing your application for the time needed to ShrinkDB (could be 30 to 90 seconds depending on load).


But ... if you insist ... here's a batch file (and stored procedure) to kill all connections and then shrink a database.


========================
begin batch file script
========================
echoMyServerName MyDatabaseName process kill
isql -b -S MyServerName -E -U MyUserName -P MyPassword -Q "sp_KillProcess 'MyDatabaseName', 'true'"


echo shrink MyServerName MyDatabaseName
osql -SMyServerName -E -Q"DBCC SHRINKDATABASE (MyDatabaseName, 2)"
========================
end batch file script
========================


========================
begin stored proc
========================
CREATE PROC sp_KillProcess
(
@dbname sysname = NULL, --Database name
@optvalue varchar(5) = NULL --Option value, either 'true' or 'false'
)
AS
BEGIN
DECLARE @dbid int, @spid int, @execstr varchar(15)
--Only the following options require that, no other connections should access the database
IF @dbname IS NOT NULL
BEGIN
SET @dbid = DB_ID(@dbname) --Getting the database_id for the specified database
SET @spid = (SELECT MIN(spid) FROM master..sysprocesses WHERE dbid = @dbid)
WHILE @spid IS NOT NULL
BEGIN
-- IF @spid <> @@SPID --To avoid the KILL attempt on own connection
--BEGIN
SET @execstr = 'KILL ' + LTRIM(STR(@spid))
EXEC(@execstr) --Killing the connection
SET @spid = (SELECT MIN(spid) FROM master..sysprocesses WHERE dbid = @dbid AND spid > @spid)
--END
END
END
END
========================
end stored proc
========================

 

Thanks for your comprehensive reply.

I'll do as you suggest and do nothing. Would you expect to see the amount of space being used in both data and log files go up and down based on this automatic activity?

Thanks again.

 

Depends on property settings of each DB. In Enterprise Manager, right click a database and choose Properties. Examine two tabs, DataFiles and TransactionLog. If [AutomaticallyGrowFiles] is checked SQL Server will manage space itself. As far as seeing the actual space used going up-n-down, SQL server tries to be fairly smart. It doesn't do any work unless it "really" decides it needs to. So it watches the [InMegabytes] and [ByPercent] and intelligently tries to accomodate. If your db doesn't dramatically grow or dramatically shrink, you might actually go for months and never see the physical size actually change.

Intelligent space use is true of most RDBMS engines today. Older versions of Oracle, Sybase, Ingress and DB2, used to have the problem as well as what I'd call desktop DB"wanna-be's" like Access, FoxPro, and of course old dBase. Bottom line ... it's good practice to check physical size once in awhile but in honesty, with modern RDBMS, it's, "Don't waste sleep over it."

 

I wish my bank manager could give me such peace of mind.


Thanks again


PeteB