Thursday, September 23, 2010

Log shipping on SQL Server 2000 Standard edition

Log shipping is not supported on SQL Server 2000 Standard edition. But there are undocumented system stored procedures to create maintenance plans and setup log shipping on the standard edition.

First create the two stored procedures below. (The SPs are designed to keep backups on Primary for 4 days and on Secondary for 72 Hours. The job frequency is 15min. If needed change the SPs as commented.)

SP - 1 on PRIMARY SERVER:

USE [master]
GO
/****** Object: StoredProcedure [dbo].[CreateLogJob] Script Date: 12/09/2009 09:55:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create PROCEDURE [dbo].[CreateLogJob] @ServerNm varchar(255), @DBNm varchar(255)
as

BEGIN TRANSACTION
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
declare @JobName varchar(255)
declare @JobCommand varchar(255)
declare @NameStr varchar(255)

SELECT @NameStr = @DBNm + ' Log Ship Trans Log Backup'
SELECT @JobName = N'' + @NameStr
SELECT @JobCommand = N'EXECUTE master.dbo.xp_sqlmaint ''-D ' + @DBNm + ' -WriteHistory -BkUpMedia DISK -BkUpLog \\' + @ServerNm + '\Logship\ -DelBkUps 4DAYS -CrBkSubDir -BkExt "TRN"''' -- RETENTION ON PRIMARY IS 4 DAYS.

-- Delete the job with the same name (if it exists)
SELECT @JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = @JobName)
IF (@JobID IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @JobID) AND (server_id <> 0)))
BEGIN
-- There is, so abort the script
RAISERROR (N'Unable to import job since there is already a multi-server job with this name.', 16, 1)
GOTO QuitWithRollback
END
ELSE
-- Delete the [local] job
EXECUTE msdb.dbo.sp_delete_job @job_name = @JobName
SELECT @JobID = NULL
END

BEGIN

-- Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT ,
@job_name = @JobName,
@owner_login_name = N'sa',
@description = N'Backup Transaction Log for Log Shipping',
@enabled = 1,
@notify_level_email = 0,
@notify_level_page = 0,
@notify_level_netsend = 0,
@notify_level_eventlog = 0,
@delete_level= 0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- Add the job steps
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID,
@step_id = 1,
@step_name = N'Step 1',
@command = @JobCommand,

@database_name = N'master',
@server = N'',
@database_user_name = N'',
@subsystem = N'TSQL',
@cmdexec_success_code = 0,
@flags = 4,
@retry_attempts = 0,
@retry_interval = 0,
@output_file_name = N'',
@on_success_step_id = 0,
@on_success_action = 1,
@on_fail_step_id = 0,
@on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID,
@start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- Add the job schedules
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID,
@name = N'Schedule 1',
@enabled = 1,
@freq_type = 4,
@active_start_date = 20000801,
@active_start_time = 0,
@freq_interval = 15, -- Every 15 mins
@freq_subday_type = 4,
@freq_subday_interval = 1,
@freq_relative_interval = 0,
@freq_recurrence_factor = 0,
@active_end_date = 99991231,
@active_end_time = 235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID,
@server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:


SP - 2 on SECONDARY SERVER:
USE [master]
GO
/****** Object: StoredProcedure [dbo].[CreateLogFilePlans] Script Date: 12/09/2009 09:55:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create procedure [dbo].[CreateLogFilePlans] @ServerNm varchar(255), @BackupServerNm varchar(255), @DBNm varchar(255)
as

declare @SourceDirStr varchar(255)
declare @DestDirStr varchar(255)
declare @NameStr varchar(255)
declare @cmd varchar(255)

set @SourceDirStr = '\\' + @ServerNm + '\Logship\' + @DBNm + ''
set @DestDirStr = '\\' + @BackupServerNm + '\Logship\' + @DBNm + ''
set @NameStr = @DBNm + ' Log_Shipping'
set @cmd = 'mkdir \\' + @BackupServerNm + '\Logship\' + @DBNm + ''

BEGIN
-- Create directory for log files
exec xp_cmdshell @cmd

PRINT 'Creating log file plans...'

exec msdb.dbo.sp_create_backup_movement_plan
@name = @NameStr, -- Log shipping plan name
@source_dir = @SourceDirStr, -- path to P1 tran logs
@dest_dir = @DestDirStr, -- location to copy P1 logs to
@sub_dir = 0, -- 0 = dest_dir is absolute path
@load_job_freq = 15, -- Load job frequency in minutes
@copy_job_freq = 15 -- copy job frequency in minutes

exec msdb.dbo.sp_add_db_to_backup_movement_plan
@plan_id = NULL, -- Plan ID, optional param
@plan_name = @NameStr, -- Log shipping plan name,equal to @name above
@source_db = @DBNm, -- Primary db name
@dest_db = @DBNm, -- Secondary db name
@load_delay = 0, -- load delay for load job in minutes
@load_all = 1, -- set to 1
@source_server = @ServerNm, -- source server
@retention_period = 72 -- time before deleting t-logs from secondary server

exec ('use msdb grant insert on backup_movement_plan_history to public')
END


After creating the SPs run them in the below syntax.

On PRIMARY:
EXEC [dbo].[CreateLogJob] *PRIMARY_SERVER_NAME*, *DBNAME*

On SECONDARY:
EXEC [dbo].[CreateLogFilePlans] *PRIMARY_SERVER_NAME*, *SECONDARY_SERVER_NAME*, *DBNAME*


-Manu

Internals - BCM and DCM Pages

Have you ever wondered how SQL Server keeps track of the changes that are made after a full backup to take a differential backup? Or how bulk logged changes are taken care of?

Usually we skip these two types of pages in SQL Server. Bulk-logged Change Mapping Page keeps track of any Bulk changes made when the database is changed to Bulk Logged Recovery model.

Differential Changed Mapping Page keeps track of changed pages after the last full backup. So while we take a differential backup all the pages referred in DCM pages will be backed up.

In general a BCM or DCM page can keep track of next 511,230 pages (tracks extents) accounting to approximately 4GB of data. So you can have a BCM or DCM page once every 511,230 pages.

Happy Learning.

Friday, September 10, 2010

No TRUNCATE_ONLY in SQL 2008

The option to take a log backup with TRUNCATE_ONLY is removed in SQL 2008 and future versions. This is used most of the times while we shrink a log file of a database (which is not recommended). You can use the below approach to free space on log files (worked for me).

  1. take a log backup
  2. run CHECKPOINT
  3. shrink the log file.
If it still not works out throwing the message the log file is in use, follow the below steps
  1. Make sure there are no open transactions on the
  2. Restrict the current log file from growing further.
  3. Run some create, insert and drop table statements to make use the rest of the space on the first log file.
  4. create a new log file with auto growth file setting or create with enough space to be used for next 30min.
  5. Then shrink the first log file.
  6. Take a log backup
  7. Remove growth restrictions on first log file and restrict the second log file from growing
  8. Issue the CHECKPOINT command.
  9. Delete the second log file when not in use.
This is one of many approaches you can use. Have fun administering databases. :)

Search a table used in SPs

This was asked in a interview. How can you search a table which is used in all the SPs of a database. Very simple, and may be most of you may have used it already.

SELECT '[' + SPECIFIC_CATALOG + '].[' + SPECIFIC_SCHEMA + '].[' + SPECIFIC_NAME + ']', ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%STRING%'

It is a very basic information but makes it easy while making changes to a table name and like to change the procedures using the table.