DbReindex & shrink DB - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

DbReindex & shrink DB

Author

Topic

mirravi
Starting Member

4 Posts

Posted - 2010-08-06 : 05:45:37

Hi,
Iam new to administrator , but i have given a script and i need to analyse it. It will DBREINDEX and also shrinks the data base.
but when i run the script , the size of the data base increased.
could any one please help me in anlysing the script.

Here is the script:

USE [msdb]
GO

/****** Object: Job [SBO Database Maint <##DBNAME##>] Script Date: 02/19/2010 09:16:51 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 02/19/2010 09:16:51 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'SBO Database Maint <##DBNAME##>',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Stop Trigger Job] Script Date: 02/19/2010 09:16:51 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Stop Trigger Job',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=4,
@on_success_step_id=2,
@on_fail_action=4,
@on_fail_step_id=2,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'--Stop the trigger job
Declare @dbname_Var varchar(100)
select @dbname_Var = db_name()

Declare @StopTriggerName_var varchar(100)
Set @StopTriggerName_var = ''Trigger_LogShip_''+@dbname_Var
Declare @StopTriggercommand_var nvarchar(200)
Set @StopTriggercommand_var = N''exec msdb..sp_update_job @job_name ='' + @StopTriggerName_var+ '', @enabled = 0''

exec(@StopTriggercommand_var)

GO',
@database_name=N'<##DBNAME##>',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [DBCC DBREINDEX / UPDATE STATISTIC] Script Date: 02/19/2010 09:16:51 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'DBCC DBREINDEX / UPDATE STATISTIC',
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=4,
@on_success_step_id=3,
@on_fail_action=4,
@on_fail_step_id=3,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'--DBCC DBREINDEX / UPDATE STATISTIC Loop
Declare @dbname_Var varchar(100)
select @dbname_Var = db_name()
Declare @Application_var varchar(100)
Set @Application_var = @dbname_Var + '' Maintnance Job''
Declare @EmailHeader_var varchar(100)
Set @EmailHeader_var = @dbname_Var + '' Maintnance Job Error''

SET ARITHABORT ON
SET QUOTED_IDENTIFIER ON

declare @tabname sysname
declare @dbstring varchar(300)
declare @exec_string varchar(300)

declare tabDBCC cursor for select table_name from information_schema.tables where table_type = ''base table''

open tabDBCC
fetch next from tabDBCC into @tabname

select @dbstring = DB_NAME()
print ''Starting DBCC DBREINDEX / UPDATE STATISTICS for database '' + upper(@dbstring)

while (@@fetch_status = 0)
begin
print ''Reindexing table '' + upper(@tabname)
select @exec_string = ''dbcc dbreindex (['' + @tabname + ''])''
exec(@exec_string)

print ''UPDATE STATISTICS '' + upper(@tabname)
select @exec_string = ''UPDATE STATISTICS ['' + @tabname + ''] WITH FULLSCAN''
exec(@exec_string)

fetch next from tabDBCC into @tabname
end
close tabDBCC
deallocate tabDBCC

print ''Finished DBCC DBREINDEX / UPDATE STATISTICS for database '' + upper(@dbstring)

IF @@ERROR <> 0
BEGIN
INSERT INTO [PGMSMQClient].[dbo].[EmailNotifications] ([Application],[EmailHeader],[EmailBody],[ID_UserType],[DateSent],[DateAdded],[Completed])
VALUES (@Application_var,@EmailHeader_var,''Indexing/Update Statics loop had a error'',1,null,getdate(),0)
END',
@database_name=N'<##DBNAME##>',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Shrink] Script Date: 02/19/2010 09:16:51 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Shrink',
@step_id=3,
@cmdexec_success_code=0,
@on_success_action=4,
@on_success_step_id=4,
@on_fail_action=4,
@on_fail_step_id=4,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'-- Shrink Database
Declare @dbname_Var varchar(100)
select @dbname_Var = db_name()

Declare @Application_var varchar(100)
Set @Application_var = @dbname_Var + '' Maintnance Job''
Declare @EmailHeader_var varchar(100)
Set @EmailHeader_var = @dbname_Var + '' Maintnance Job Error''

Declare @ShrinkName_var varchar(100)
Set @ShrinkName_var = ''Trigger_LogShip_''+@dbname_Var
Declare @Shrinkcommand_var nvarchar(200)
Set @Shrinkcommand_var = N''DBCC SHRINKDATABASE(''+@dbname_Var+'', 10, TRUNCATEONLY)''

declare @err int
exec @err = sp_executesql @Shrinkcommand_var

IF @@ERROR <> 0 or @err <> 0
BEGIN
INSERT INTO [PGMSMQClient].[dbo].[EmailNotifications] ([Application],[EmailHeader],[EmailBody],[ID_UserType],[DateSent],[DateAdded],[Completed])
VALUES (@Application_var,@EmailHeader_var,''Shrink failed'',1,null,getdate(),0)
END
',
@database_name=N'<##DBNAME##>',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Start Trigger Job] Script Date: 02/19/2010 09:16:51 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Start Trigger Job',
@step_id=4,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'--Start the trigger job
Declare @dbname_Var varchar(100)
select @dbname_Var = db_name()

Declare @StartTriggerName_var varchar(100)
Set @StartTriggerName_var = ''Trigger_LogShip_''+@dbname_Var
Declare @StartTriggercommand_var nvarchar(200)
Set @StartTriggercommand_var = N''exec msdb..sp_update_job @job_name ='' + @StartTriggerName_var+ '', @enabled = 1''

exec(@StartTriggercommand_var)',
@database_name=N'<##DBNAME##>',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'WhenItRuns',
@enabled=1,
@freq_type=8,
@freq_interval=64,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20100129,
@active_end_date=99991231,
@active_start_time=190900,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

Thanks in advance

Regards
Ravi

Kristen
Test

22859 Posts

Posted - 2010-08-06 : 06:06:08

"and also shrinks the data base"

I don't mean to sound harsh, but you should NEVER Shrink a database to a scheduled. You should RARELY shrink a database at all - examples would be where there has been a one-time delete of old data and the database size can now be reduced, or a huge transaction that has (i.e, accidentally) increased the size of the LOG file.

mirravi
Starting Member

4 Posts

Posted - 2010-08-06 : 06:50:59

Thanks for your reply.
so i can do only reindexing/update statistics.
How can i decrease the size of DB other than shrinking and deleting old data, as for some systems need to provide space.

Regards
Ravi

Kristen
Test

22859 Posts

Posted - 2010-08-06 : 08:45:43

SHRINKing is pointless. The database will just grow back again tomorrow. Each time you shrink you fragment the tables / indexes, and the file itself can be fragmented when it grows back again. Like I said, if you have a one-time need to Shrink the database that's fine, but if you are doing it every day its alternative cycles of Shrink / Grow - why not just leave it at the "grown" size that it gets to every day anyway?

Increase the frequency of TLog backups (if FULL recovery model) to keep the TLog file as small as possible - every 15 minutes is a good balance. Once and hour, or once a day is not often enough. We increase TLog backup frequency to every 2 minutes during index rebuild (as that is usually our highest transaction load.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2010-08-06 : 16:25:28

check : ALTER INDEX with the REORGANISE option for defrag or REBUILD , depending on what you are trying to do. A REORGANISE will not update statistics - so you would need to do a statistics update with a REORGANISE

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources