Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Shrink database Expand / Collapse
Author
Message
Posted Tuesday, March 25, 2008 2:34 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 16, 2013 2:48 PM
Points: 262, Visits: 1,503
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
Post #474389
Posted Tuesday, March 25, 2008 3:16 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 8:51 PM
Points: 33,266, Visits: 15,431
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
Post #474404
Posted Tuesday, March 25, 2008 3:19 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, September 4, 2014 3:47 PM
Points: 3,244, Visits: 538
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



Post #474407
Posted Tuesday, March 25, 2008 3:40 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 16, 2013 2:48 PM
Points: 262, Visits: 1,503
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!
Post #474415
Posted Wednesday, March 26, 2008 12:34 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:10 PM
Points: 42,986, Visits: 36,141
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 2008, MVP
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

Post #474534
Posted Wednesday, March 26, 2008 1:56 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 16, 2013 2:48 PM
Points: 262, Visits: 1,503
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
Post #475071
Posted Wednesday, March 26, 2008 11:59 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:10 PM
Points: 42,986, Visits: 36,141
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 2008, MVP
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

Post #475236
Posted Thursday, March 27, 2008 8:32 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 16, 2013 2:48 PM
Points: 262, Visits: 1,503
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!!
Post #475446
Posted Thursday, March 27, 2008 2:33 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:10 PM
Points: 42,986, Visits: 36,141
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 2008, MVP
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

Post #475741
Posted Thursday, March 27, 2008 3:35 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 16, 2013 2:48 PM
Points: 262, Visits: 1,503
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!
Post #475781
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse