SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Shrink database


Shrink database

Author
Message
Mh-397891
Mh-397891
Right there with Babe
Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)

Group: General Forum Members
Points: 774 Visits: 1514
I am going thru our SQL jobs for cleaning up unnecessary jobs,
found a job which shrink's the datafile and logfile's of a busy db once a week(sunday's),
do we need to do this and is it okay to do??
The following is the script we use for shrinking:

USE MASTER
GO
ALTER DATABASE [DBname] SET RECOVERY SIMPLE
GO
USE [DBname]
GO
DBCC SHRINKFILE ([DBname_Data], NOTRUNCATE)
GO
DBCC SHRINKFILE ([DBname_Log], NOTRUNCATE)
GO
USE MASTER
GO
ALTER DATABASE [DBname] SET RECOVERY FULL
Steve Jones
Steve Jones
SSC Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: Administrators
Points: 84396 Visits: 19224
You never need to shrink the database, or almost never. This causes fragmentation and can slow your indexes and worsen performance.

Shrinking should only take place rarely when something unusual makes the log or data grow.
http://www.sqlskills.com/blogs/paul/2007/11/13/AutoshrinkTurnItOFF.aspx

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
SQLBill
SQLBill
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6595 Visits: 1083
oooo bad, bad, bad set-up.

The database is in FULL recovery mode which requires the logs to be backed up if SQL Server is going to shrink them. Since logs aren't being backed up, they grow huge. So the database is set in SIMPLE mode and the log shrunk. Then it's put back in FULL recovery mode again.

That's a total waste. If you aren't going to do log backups, just put the database in SIMPLE mode and leave it there. SQL Server will CHECKPOINT the log file and free up space as it goes.

Things to consider: SIMPLE mode does NOT allow you to recover to a point-in-time. You can only recover to the last full backup made. If you can't afford to lose data, leave it in FULL mode, start doing log backups (BACKUP LOG command), and get rid of the switch to SIMPLE and truncating/shrinking the log file.

-SQLBill



Mh-397891
Mh-397891
Right there with Babe
Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)

Group: General Forum Members
Points: 774 Visits: 1514
SQLBill (3/25/2008)
oooo bad, bad, bad set-up.

The database is in FULL recovery mode which requires the logs to be backed up if SQL Server is going to shrink them. Since logs aren't being backed up, they grow huge. So the database is set in SIMPLE mode and the log shrunk. Then it's put back in FULL recovery mode again.

That's a total waste. If you aren't going to do log backups, just put the database in SIMPLE mode and leave it there. SQL Server will CHECKPOINT the log file and free up space as it goes.

Things to consider: SIMPLE mode does NOT allow you to recover to a point-in-time. You can only recover to the last full backup made. If you can't afford to lose data, leave it in FULL mode, start doing log backups (BACKUP LOG command), and get rid of the switch to SIMPLE and truncating/shrinking the log file.

-SQLBill

I agree with you, so basically I shall disable the shrink job which runs the above pasted sql code which truncates data and log files, then will incrase the frequency of the tran log files so that the log does'nt grow.
Just to confirm do we ever shrink the data file? if not, how can we maintain the size if it's growing? Thanks!
GilaMonster
GilaMonster
SSC Guru
SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)

Group: General Forum Members
Points: 118642 Visits: 45547
Mh (3/25/2008)
[quote
Just to confirm do we ever shrink the data file? if not, how can we maintain the size if it's growing? Thanks!


No. If the database is growing it's because the size of the data is increasing. SQL needs some empty space inside the DB to operate properly.

Unless you've done a massive archive or delete, don't shrink the DB at all. It will just grow again.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Mh-397891
Mh-397891
Right there with Babe
Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)

Group: General Forum Members
Points: 774 Visits: 1514
I went ahead to check all the sql jobs in that server and also upon talking to user's they were saying that the system slows down every sunday PM, so I went ahead to check all the jobs, here they are, please let me know all your valuable suggestions. Thanks much!!

1. The above truncate occurs every sunday @6AM which is the above code pasted, I shall disable that job.

2. And also the following code runs as a job every sunday @1PM:

SQLCode:
DBCC FREEPROCCACHE WITH NO_INFOMSGS
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS
DECLARE @db sysname, @sql nvarchar(4000)
DECLARE CRDB CURSOR LOCAL FAST_FORWARD FOR
SELECT name FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','msdb','tempdb','model')
OPEN CRDB
FETCH CRDB INTO @db
WHILE @@FETCH_STATUS=0
BEGIN
PRINT '=================== '+QUOTENAME(@db)+' "'+CAST(GETDATE() as varchar(100))+'" ====================='
SET @sql=N'SET XACT_ABORT OFF'
SET @sql=@sql+N' DBCC DBREINDEX (''?'') WITH NO_INFOMSGS'
SET @sql=@sql+N' IF EXISTS (SELECT * FROM sysindexes WHERE indid=1 AND id=OBJECT_ID(''?''))'
SET @sql=@sql+N' DBCC INDEXDEFRAG (0, ''?'', 1) WITH NO_INFOMSGS'
SET @sql=@sql+N' UPDATE STATISTICS ? EXEC sp_recompile ''?'''
SET @sql=N'USE ['+@db+'] EXEC sp_msforeachtable N'''+REPLACE(@sql,'''','''''')+N''''
EXEC(@sql)
DBCC UPDATEUSAGE (@db) WITH NO_INFOMSGS
DBCC CHECKDB (@db) WITH NO_INFOMSGS
FETCH CRDB INTO @db
END
CLOSE CRDB
DEALLOCATE CRDB

3. Then optimizations maint plan runs every sunday @5PM

4. Integrity checks runs every sunday @1PM
GilaMonster
GilaMonster
SSC Guru
SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)

Group: General Forum Members
Points: 118642 Visits: 45547
Looks like you may have duplicate jobs. The code you posted that runs 1pm does all that the integrity check and optimisations do. You may be able to disable one of those jobs.

That step's also doing a lot of unnecessary stuff. For each table in the DB, it's doing an index rebuild, followed by an index defrag, followed by a statistics update.

Reindex does all that a defrag and a stats update does, and more. You should remove the IndexDefrag and Update Statistics from than.

(Or perhaps disable that job totally)

DBCC FREEPROCCACHE WITH NO_INFOMSGS
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS


And you definitly want to take these two lines out.

That's telling SQL to empty the procedre cache and clear all the data buffers. For a while after that the system will run slower as procedures have to be recompiled and data has to be fetched from disk rather than memory.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Mh-397891
Mh-397891
Right there with Babe
Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)

Group: General Forum Members
Points: 774 Visits: 1514
How about UPDATEUSAGE and CHECKDB?? Do we need them?
when we execute any sql or stored proc will the results get cached, if so is it cached in the disk or memory?
In what order we have to run the following:
DBCC Reindex, optimizations maint plan and Integrity checks
I greatly appreciate all your responses. Thanks!!
GilaMonster
GilaMonster
SSC Guru
SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)

Group: General Forum Members
Points: 118642 Visits: 45547
IMHO, update usage isn't worth running often. The CheckDB is done by the integrity check as well as this piece of home-brewed code.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Mh-397891
Mh-397891
Right there with Babe
Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)

Group: General Forum Members
Points: 774 Visits: 1514
In what order does the following need to run:
1. Reorganize data and index pages
2. Update stats
3. Check database integrity
Also since Reorganize data and index pages takes care of Update stats so we do'nt need to do update stats right?

Thanks!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search