August 27, 2015 at 12:28 pm
Hello Masters of SQL, i have a little problem with my Query, i try to create script which Rebuild one table index after transaction log backup.
In my script first i check or backup is complete in 10 minutes if i find record i do it index rebuild if i don't find i need go back and check again.
The problem is that query finish if don't find a backup record, How i can loop that script that he checking until find the record and then start rebuild index. :ermm:
Use DB
GO
DECLARE @database_name NVARCHAR(50)
SELECT @database_name='DB'`
START:
WHILE EXISTS
(SELECT msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.backup_size
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE database_name = 'DB' and backup_finish_date BETWEEN DATEADD(MINUTE, -10, GETDATE()) AND GETDATE())`
BEGIN
ALTER INDEX [CCOUNTDATEVOUCHER] ON [dbo].[DB] REBUILD PARTITION = ALL WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF );
ALTER INDEX [DATEVOUCHER] ON [dbo].[DB] REBUILD PARTITION = ALL WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF );`
IF NOT EXISTS (SELECT msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.backup_size
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE database_name = 'DB' and backup_finish_date BETWEEN DATEADD(MINUTE, -10, GETDATE()) AND GETDATE())`
GOTO START;
BREAK;
END
August 27, 2015 at 1:11 pm
DECLARE @database_name NVARCHAR(50)
SELECT @database_name=DB_NAME()
WHILE 1 = 1
BEGIN
IF EXISTS(
SELECT 1
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE database_name = @database_name and backup_finish_date BETWEEN DATEADD(MINUTE, -10, GETDATE()) AND GETDATE())
BEGIN
ALTER INDEX [CCOUNTDATEVOUCHER] ON [dbo].[DB] REBUILD PARTITION = ALL WITH ( PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = OFF, SORT_IN_TEMPDB = ON );
ALTER INDEX [DATEVOUCHER] ON [dbo].[DB] REBUILD PARTITION = ALL WITH ( PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = OFF, SORT_IN_TEMPDB = ON );
BREAK;
END --IF
ELSE
WAITFOR DELAY '00:10:00'
END --WHILE
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply