August 6, 2010 at 1:51 am
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
August 6, 2010 at 3:49 am
Hi,
1. See if you perform dbcc dbreindex query. First it will drop the index and recreate it. So
log size should be increased.
2. Intially dbcc dbreindex query will update for all index statistics. Not for column statistics
3. After recreates an index. then you must update the column statistics for all objects
4. finally you have to take the log backup.then it will reduce the log size.
Thanks & Regards
Balaji.G
August 6, 2010 at 3:55 am
Hi,
Thanks for your reply.
For the point 3 how can i update column statistics for all objects.
And also how can i take the log backup.
Is the query not performing the above said operations?
Iam not aware of these things.
Regards
Ravi
August 6, 2010 at 4:07 am
Stop shrinking your database! You're harming performance.
Shrinking causes massive fragmentation and will just result in the data file growing again next time data gets added. When that happens, the entire system will slow down as the file is expanded. Also repeated shrinks and grows will cause fragmentation at the file-system level, which is hard to fix.
See - http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/
http://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/ (plus everything it links to)
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
August 6, 2010 at 4:08 am
balaji.ganga (8/6/2010)
4. finally you have to take the log backup.then it will reduce the log size.
No it will not! Backing up the log marks the space in the log as reusable. It does not change the physical size of the log file.
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
August 6, 2010 at 4:19 am
The database size will increase when you are rebuilding the index.
Reindexing and Shrinking is a very bad idea. When you shrink the database the indexes will be fragmented, which defeats the very purpose of re-indexing !
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
August 6, 2010 at 4:34 am
Thanks for ypur replies.
so what can i do , just like shrink DB for a schedule task with out performing REindexing?
Regards
Ravi
August 6, 2010 at 4:39 am
No, no, no! Don't regularly shrink your database. You're hurting performance for no good reason. Databases grow. It's normal. It's expected. Shrinking them is just going to force a grow next time data's added.
Just reindex. Leave the shrink out.
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
August 6, 2010 at 4:47 am
Thanks for your replies.
So how can i reduce the size of the DB.For some systems it need to decrease the size of the DB.
August 6, 2010 at 4:49 am
Why do you need to reduce the size of the DB?
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
August 6, 2010 at 4:53 am
For some branches the we need to provide space as the data is growing by the new applications installed on it.
August 6, 2010 at 5:00 am
If the data is growing, you're going to either need to archive and delete data or provide more disk space. Shrink isn't compression. It's not going to reduce the size of the data in the database. If the data volume is growing, the database has to grow.
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
August 10, 2010 at 12:16 am
Hi,
Column Statistics for all objects
select 'update statistics' name from sysobjects where xtype='u'
now you get for all objects from your database. then you have to copy all the result objects and paste it. Then you will execute those query. Now Statistics will be updated.
Example
update statistics employee
update statistics author
update statistics book
Thanks & Regards
Balaji.G
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply