Shrinking SQL DB

[martache27]

Sort of new to the administration of a SQL Db. Is there a utility to shrink down the database once a week?

Thanks!

 

[ptheriault]

Look up DBCC SHRINKDATABASE() in BOL.

- Paul
- Database performance looks fine, it must be the Network!

 

[martache27]

Can you break this down for me? Not sure what your referring to.

 

[ptheriault]

BOL = Books On-Line. They are installed on you local PC when you install the client tools. if they are not there you can download the latest version from msdn.
DBCC SHRINKDATABASE() is the command that you are looking for to shrink your database file.
A database is made up of two files. The data is in the .mdf and the transaction log is in the .ldf. Both files can may need shrinking from time to time. To shrink the datafile you use DBCC SHRINKDATABASE()to shrink the .mdf
and DBCC SHRINKFILE() to shrink the .ldf
Each command has a few options.
This will return the free space at the end of the file back to the OS.
DBCC SHRINKDATABASE(DBName, TRUNCATEONLY)
This will leave 20% free.
DBCC SHRINKDATABASE(DBName, 20)
Carefull not to shrink you db to much if you have autogrow enabled. It will just autogrow again which will cause fragmentation of you db file. Which will hurt overall performance.



- Paul
- Database performance looks fine, it must be the Network!

 

[martache27]

I found the BOL. I will look into shrinking the DB. Thanks for your help!!

 

[ptheriault]

no problem..good luck.

- Paul
- Database performance looks fine, it must be the Network!

 

[SQLBill]

In Enterprise Manager, you can either:

1) right click on the database, select Properties, go to the Options tab and check Auto Shrink - but that isn't recommended - or

2) you can expand Manage, expand SQL Server Agent, and right click on Jobs. Then create a new job. Make one of the steps the shrinkdb command or use the shrinkfile command and use one step for the datafile and one for the log file.

1 isn't recommended because you don't know when it will shrink and it could interfere with other things.

2 is the best as you can schedule it for slow periods.

-SQLBill

Posting advice: FAQ481-4875

 

[martache27]

When using the DBCC Shrinkfile, will I want to select "truncateonly". I don't want to lose any data.

Thanks!

 

[ptheriault]

truncateonly will just return free space at the end of the log file back to the OS. you won't loose any data.

- Paul
- Database performance looks fine, it must be the Network!

 

[martache27]

One more thing.. will DB Auto Shrink cause the SQL to lockup? I experience SQL lock ups on sometimes. The fix is I restart the SQL agent. Coulld this be possible?

 

[ptheriault]

Do not autoshrink the db. It can cause problems.

- Paul
- Database performance looks fine, it must be the Network!

 

[mutley1]

We have loads of servers that have multiple databases so rather that doing a job for each we use this stored procedure. You can load this and then call it from a job - it does a shrink on all user databases.

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE Procedure ShrinkFile_allUserdatabases
AS

SET NOCOUNT ON

CREATE TABLE #LogicalNamelist (
   dbName CHAR (255),
   LogicalNamelist CHAR(50))

INSERT INTO #LogicalNamelist 
SELECT sysdb.Name as dbName, CONVERT(VARCHAR(30),sysalt.name) as dblogicalFileName
FROM    master..sysaltfiles sysalt INNER JOIN master..sysdatabases sysdb ON sysalt.dbid = sysdb.dbid 
WHERE sysdb.dbid > 4 and sysalt.name like '%_log%'and sysalt.name not like '%_dat%'
GROUP BY sysalt.name , sysdb.name
ORDER BY sysalt.name

DECLARE AllDatabases CURSOR FOR
SELECT  dbName,LogicalNamelist
from  #LogicalNamelist

OPEN AllDatabases

DECLARE @DBNameVar NVARCHAR(158)
DECLARE @Statement NVARCHAR(300)
DECLARE @logicalename VARCHAR (128)

FETCH NEXT FROM AllDatabases 
INTO @DBNameVar,@logicalename

WHILE (@@FETCH_STATUS = 0)

BEGIN
   PRINT N'Shrinkingfile ' + RTRIM(@logicalename )+ ' '+ 'of' +' '+  RTRIM(@DBNameVar )
   SET @Statement = N'USE [' + RTRIM(@DBNameVar)  + ']'+ CHAR(13)
    + N'DBCC SHRINKFILE (['+ RTRIM(@logicalename)+ '],' + ' ' + 'TRUNCATEONLY)' + 'WITH NO_INFOMSGS' 

   --PRINT @Statement
   EXEC sp_executesql @Statement
   PRINT CHAR(13) + CHAR(13)
   
    FETCH NEXT FROM AllDatabases 
    INTO @DBNameVar,@logicalename
END

CLOSE AllDatabases
DEALLOCATE AllDatabases

Drop table #LogicalNamelist

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO