March 25, 2008 at 2:34 pm
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
March 25, 2008 at 3:16 pm
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
March 25, 2008 at 3:19 pm
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
March 25, 2008 at 3:40 pm
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!
March 26, 2008 at 12:34 am
Mh (3/25/2008)
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
March 26, 2008 at 1:56 pm
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
March 26, 2008 at 11:59 pm
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
March 27, 2008 at 8:32 am
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!!
March 27, 2008 at 2:33 pm
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
March 27, 2008 at 3:35 pm
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!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply