Feeds:
Posts
Comments

Sub CopyData2NewSheet()

Dim strOriginalSheetName As String
Dim strCopySheetName As String
Dim LastRow As Long, LastCol As Integer, Col As Integer, Row As Long, I As Long
Dim strCellData As String

strOriginalSheetName = “Sheet_Name”
strCopySheetName = strOriginalSheetName & “_Copy”

ThisWorkbook.Sheets(strOriginalSheetName).Activate

Sheets.Add After:=ActiveSheet
ActiveWorkbook.ActiveSheet.Name = strCopySheetName

ThisWorkbook.Sheets(strOriginalSheetName).Activate

LastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
LastCol = ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column

For Row = 1 To LastRow

For Col = 1 To LastCol

ThisWorkbook.Sheets(strOriginalSheetName).Activate

If Len(Cells(Row, Col)) = 2 Then

strCellData = Cells(Row, Col)
ThisWorkbook.Sheets(strCopySheetName).Activate

Cells(Row + Row, Col) = Left(strCellData, 1)
Cells(Row + Row + 1, Col) = Right(strCellData, 1)

Else

strCellData = Cells(Row, Col)
ThisWorkbook.Sheets(strCopySheetName).Activate

Cells(Row + Row, Col) = strCellData

End If

Next Col

ThisWorkbook.Sheets(strOriginalSheetName).Activate

Next Row

ThisWorkbook.Sheets(strCopySheetName).Activate

‘Insert Black Rows Between Every 2 Rows

For I = 2 To 1000 Step 3

Range(“A” & I).Select
ActiveCell.EntireRow.Insert

Next I

ThisWorkbook.Sheets(strCopySheetName).Activate

MsgBox “Data Copied Sucessfully…”

End Sub

Select (SYSCONS.Refer_Table_Name) As PK_Table_Name,(SYSCONS.Refer_Column_Name) As PK_Column_Name,(SYSO.Name) As FK_Table_Name,(SYSC.Name) As FK_Column_Name From SysObjects SYSO,SysColumns SYSC,
(Select SFK.Constid [ConstraintID],SFK.fkeyid [TableID], SFK.fkey [ColumnID],SC.Name As Refer_Column_Name,SO.Name As Refer_Table_Name,SFK.Rkeyid [TableID refered], SFK.Rkey[ColumnID refered] From dbo.SysForeignKeys SFK
Inner Join dbo.SysColumns SC on SFK.Rkeyid = SC.id and SFK.Rkey = SC.colid
Inner Join dbo.SysObjects SO on SC.id = SO.id)SYSCONS
Where SYSC.ColId = SYSCONS.ColumnID And SYSC.id = SYSCONS.TableId And SYSO.Id=SYSC.Id

—Table Size Information Periodically in SQL Server 2005

USE [master]

GO

CREATE DATABASE [DBA_Activities]

GO

—===========================================

USE [DBA_Activities]

GO

CREATE TABLE [dbo].[DATABASE_TABLE_SIZE_LOG](
[Database_Name] [sysname] NOT NULL DEFAULT (”),
[Table_Name] [sysname] NOT NULL,
[Row_Count] [int] NULL,
[Reserved] [varchar](50) NULL,
[Data] [varchar](50) NULL,
[Index_Size] [varchar](50) NULL,
[Unused] [varchar](50) NULL,
[Get_Date] [datetime] NULL
) ON [PRIMARY]

–====================================================

USE [msdb]

GO

/****** Object:  Job [DATABASE_TABLE_SIZE_LOG]    Script Date: 01/21/2012 13:04:28 ******/

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

/****** Object:  JobCategory [Database Maintenance]    Script Date: 01/21/2012 13:04:28 ******/

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N’Database Maintenance’ AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N’JOB’, @type=N’LOCAL’, @name=N’Database Maintenance’

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

END

DECLARE @jobId BINARY(16)

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N’DATABASE_TABLE_SIZE_LOG’,

@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’Database Maintenance’,

@owner_login_name=N’sa’, @job_id = @jobId OUTPUT

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

/****** Object:  Step [Step1]    Script Date: 01/21/2012 13:04:28 ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’Step1′,

@step_id=1,

@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’DECLARE @EachDBCmd VARCHAR(8000)

SET @EachDBCmd = ”USE [?];

IF ””?”” NOT IN (””tempdb””,””master””, ””model””, ””msdb””)

BEGIN

DECLARE @EachTableCmd VARCHAR(8000)

SET @EachTableCmd = ””EXEC sp_spaceused ”””””” + CHAR(63) + ””””””””

INSERT INTO DBA_Activities.dbo.DATABASE_TABLE_SIZE_LOG(Table_Name,Row_count,Reserved,data,index_size,unused)

EXEC sp_MSforeachtable @EachTableCmd

UPDATE DBA_Activities.dbo.DATABASE_TABLE_SIZE_LOG SET Database_Name = ””?””,Get_Date=(SELECT CONVERT(CHAR(10), GETDATE(), 101)) WHERE Database_Name = ””””

END

EXEC sp_MSforeachdb @EachDBCmd’,

@database_name=N’DBA_Activities’,

@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’Sche1′,

@enabled=1,

@freq_type=8,

@freq_interval=17,

@freq_subday_type=1,

@freq_subday_interval=0,

@freq_relative_interval=0,

@freq_recurrence_factor=1,

@active_start_date=20111003,

@active_end_date=99991231,

@active_start_time=220000,

@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:

—==========================================================

Select * From dbo.DATABASE_TABLE_SIZE_LOG

Restore Master Database

        Reasons to Backup Master Database – Why Should Master Database Backedup (Pinal Dave) :- http://blog.sqlauthority.com/2009/02/15/sql-server-reasons-to-backup-master-database-why-should-master-database-backedup/

1.Backup the Master Database.

2.For Testing purpose intentionally delete any login from SQL Server.

4. Login was Deleted from SQL Server.

Now we will restore the Master Database From Backup file

1.Open SQL Server Configuration Manager

2.Stop the SQL Server Service.

3. Add -m; Parameter in SQL Server Startup Parameters for start the SQL Server with Single User Mode.

4. Start the SQL Server Service.

5.Open command prompt for restore a master database.

6.Now Open SQL Server Using SSMS Check the Deleted Login Id restored.

--===========================Monitor DB Size Growth Report In SQL Server 2005=======================

USE [master]
GO

CREATE DATABASE [DBA_Activities]
GO

--====================================================================================================

USE [DBA_Activities]
GO

CREATE TABLE [dbo].[Get_DB_Details_DBA](
	[Server_Name] [varchar](100) NULL,
	[Database_Name] [varchar](100) NULL,
	[Logical_File_Name] [sysname] NOT NULL,
	[Physical_File_Name] [nvarchar](520) NULL,
	[File_Size_MB] [int] NULL,
	[Status] [sysname] NOT NULL,
	[Recovery_Mode] [sysname] NOT NULL,
	[Free_Space_MB] [int] NULL,
	[Free_Space_Per] [varchar](7) NULL,
	[Get_Date] [datetime] NULL
) ON [PRIMARY]

--====================================================================================================

USE [DBA_Activities]
GO

CREATE  PROC   [dbo].[Get_Database_Size_Growth]
(
     @dtFromDate DateTime=Null,
     @dtToDate DateTime=Null,
     @Option Varchar(10)='DAY_WISE' --DAY_WISE,MONTH_WISE,YEAR_WISE

)
AS

BEGIN

Declare @SQL Varchar(Max)

Declare @intFromMonth int
Declare @intToMonth int

Declare @intFromYear int
Declare @intToYear int

Declare @intI int
Declare @intDiff int

Declare @dtDate datetime

If @dtFromDate Is Null And @dtToDate Is Null
   Begin
      Set @dtFromDate = (Select Min(Get_Date) from Get_DB_Details_DBA)
      Set @dtToDate= (Select Max(Get_Date) from Get_DB_Details_DBA)
   End

If @dtFromDate Is Null
   Begin
      Set @dtFromDate = (Select Min(Get_Date) from Get_DB_Details_DBA)
   End

If @dtToDate Is Null
   Begin
      Set @dtToDate= (Select Max(Get_Date) from Get_DB_Details_DBA)
   End

If @dtFromDate > @dtToDate
   Begin
      RAISERROR ('From date should be less than To date.',10,1)
      Return
   End

If @intFromMonth < 1 Or @intFromMonth > 12
   Begin
      RAISERROR ('From Date Month between 1 to 12.',10,1)
      Return
   End

If @intToMonth < 1 Or @intToMonth > 12
   Begin
      RAISERROR ('To Date Month between 1 to 12.',10,1)
      Return
   End

If @intFromYear < 1753 Or @intFromYear > 9999
   Begin
      RAISERROR ('From Date Year between 1 to 12.',10,1)
      Return
   End

If @intToYear < 1753 Or @intToYear > 9999
   Begin
      RAISERROR ('From Date Year between 1 to 12.',10,1)
      Return
   End

Set @SQL = 'Select Database_Name'

If @Option = 'DAY_WISE'

   Begin

     Set @dtDate = @dtFromDate

     While (@dtDate <= @dtToDate)

       Begin
         Set @SQL = @SQL + ',(Sum(Case When Get_Date=' + char(39) + CONVERT(VARCHAR(10), @dtDate, 110) + char(39) + ' Then File_Size_MB Else Null End)/1024.0) As ' + char(39) + REPLACE(CONVERT(VARCHAR(11), @dtDate, 106), ' ', '-') + char(39)
         Set @dtDate = @dtDate + 1
       End

   End

If @Option = 'MONTH_WISE'

   Begin

      Set @intFromMonth = Month(@dtFromDate)
      Set @intToMonth = Month(@dtToDate)

      Set @intFromYear = Year(@dtFromDate)
      Set @intToYear = Year(@dtToDate)

      --Set @dtFromDate = (CONVERT(DATETIME, (CONVERT(Varchar(4),@intFromYear) + '-' + CONVERT(Varchar(2),@intFromMonth) +  '-' + '01') ,20))
      --Set @dtToDate = (CONVERT(DATETIME, (CONVERT(Varchar(4),@intToYear) + '-' + CONVERT(Varchar(2),@intToMonth) +  '-' + '01') ,20))

      Set @intI = 0
      Set @intDiff = (DateDiff(mm,@dtFromDate,@dtToDate))

      Set @dtDate = @dtFromDate

      While (@intI <= @intDiff)

         Begin
           Set @dtDate= (DateAdd(mm,@intI,@dtFromDate))
           Set @SQL = @SQL + ',(Sum(Case When Get_Date=' + char(39) + CONVERT(VARCHAR(10), @dtDate, 110) + char(39) + ' Then File_Size_MB Else Null End)/1024.0) As ' + char(39) + REPLACE(CONVERT(VARCHAR(11), @dtDate, 106), ' ', '-') + char(39)
           Set @intI=@intI+1
         End

   End

If @Option = 'YEAR_WISE'

   Begin

      Set @intFromYear = Year(@dtFromDate)
      Set @intToYear = Year(@dtToDate)

      --Set @dtFromDate = (CONVERT(DATETIME, (CONVERT(Varchar(4),@intFromYear) + '-' + 'Jan' +  '-' + '01') ,20))
      --Set @dtToDate = (CONVERT(DATETIME, (CONVERT(Varchar(4),@intToYear) + '-' + 'Jan' +  '-' + '01') ,20))

      Set @intI = 0
      Set @intDiff = (DateDiff(yy,@dtFromDate,@dtToDate))

      Set @dtDate = @dtFromDate

      While (@intI <= @intDiff)

         Begin
           Set @dtDate= (DateAdd(yy,@intI,@dtFromDate))
           Set @SQL = @SQL + ',(Sum(Case When Get_Date=' + char(39) + CONVERT(VARCHAR(10), @dtDate, 110) + char(39) + ' Then File_Size_MB Else Null End)/1024.0) As ' + char(39) + REPLACE(CONVERT(VARCHAR(11), @dtDate, 106), ' ', '-') + char(39)
           Set @intI=@intI+1
         End

   End

Set @SQL = @SQL + 'From Get_DB_Details_DBA Group By Database_Name Order by Database_Name'
EXECUTE(@SQL)

END

GO

--====================================================================================================

USE [msdb]
GO

/****** Object:  Job [Get_DB_Details_DBA]    Script Date: 10/04/2011 14:40:30 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [Database Maintenance]    Script Date: 10/04/2011 14:40:30 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Get_DB_Details_DBA',
		@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'Database Maintenance',
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Step1]    Script Date: 10/04/2011 14:40:30 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Step1',
		@step_id=1,
		@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'DECLARE @DBSizeDetails TABLE
(Server_Name VARCHAR(100),
Database_Name VARCHAR(100),
Logical_File_Name sysname,
Physical_File_Name NVARCHAR(520),
File_Size_MB INT,
Status sysname,
Recovery_Mode sysname,
Free_Space_MB INT,
Free_Space_Per VARCHAR(7),
Get_Date datetime)

DECLARE @command VARCHAR(5000)
SELECT @command = ''Use ['' + ''?'' + ''] SELECT
@@servername As Server_Name,
'' + '''''''' + ''?'' + ''''''''+ '' As Database_Name,
sysfiles.name As Logical_File_Name,
sysfiles.filename AS Physical_File_Name,
CAST(sysfiles.size/128.0 AS int) As File_Size_MB,
CONVERT(sysname,DatabasePropertyEx(''''?'''',''''Status'''')) AS Status,
CONVERT(sysname,DatabasePropertyEx(''''?'''',''''Recovery'''')) AS Recovery_Mode,
CAST(sysfiles.size/128.0 - CAST(FILEPROPERTY(sysfiles.name, '' + '''''''' +
''SpaceUsed'' + '''''''' + '' ) AS int)/128.0 AS int) AS Free_Space_MB,
CAST(100 * (CAST (((sysfiles.size/128.0 - CAST(FILEPROPERTY(sysfiles.name,
'' + '''''''' + ''SpaceUsed'' + '''''''' + '' ) AS int)/128.0)/(sysfiles.size/128.0))
AS decimal(4,2))) AS varchar(8)) + '' + '''''''' + ''%'' + '''''''' + '' AS Free_Space_Per,
GETDATE() as Get_Date FROM dbo.sysfiles''
INSERT INTO @DBSizeDetails
(Server_Name, Database_Name, Logical_File_Name, Physical_File_Name, File_Size_MB, Status,
Recovery_Mode, Free_Space_MB, Free_Space_Per, Get_Date)
EXEC sp_MSForEachDB @command

INSERT INTO Get_DB_Details_DBA (Server_Name, Database_Name, Logical_File_Name, Physical_File_Name, File_Size_MB, Status,Recovery_Mode, Free_Space_MB, Free_Space_Per,Get_Date)
SELECT
Server_Name, Database_Name, Logical_File_Name, Physical_File_Name, File_Size_MB, Status,
Recovery_Mode, Free_Space_MB, Free_Space_Per,CONVERT(CHAR(10), Get_Date, 101) FROM @DBSizeDetails Where Database_Name Not In (''master'',''msdb'',''tempdb'',''model'')
',
		@database_name=N'DBA_Activities',
		@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'Sche1',
		@enabled=1,
		@freq_type=4,
		@freq_interval=1,
		@freq_subday_type=1,
		@freq_subday_interval=0,
		@freq_relative_interval=0,
		@freq_recurrence_factor=0,
		@active_start_date=20101029,
		@active_end_date=99991231,
		@active_start_time=200000,
		@active_end_time=235959,
		@schedule_uid=N'3b2022cd-96b6-42df-8cab-d3be37d711b4'
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

--====================================================================================================

USE [DBA_Activities]
GO

Exec Get_Database_Size_Growth
Exec Get_Database_Size_Growth '05-May-2011'
Exec Get_Database_Size_Growth '05-May-2011','05-Jun-2011'
Exec Get_Database_Size_Growth '05-May-2011','05-Oct-2011','MONTH_WISE'
Exec Get_Database_Size_Growth NULL,NULL,'MONTH_WISE'
Exec Get_Database_Size_Growth '05-May-2011','05-May-2012','YEAR_WISE'

--====================================================================================================

While Loop

CREATE TABLE [dbo].[ID_Table](
	[Id] [bigint] NULL,
	[Name] [varchar](50) NULL
) ON [PRIMARY]

GO

DECLARE @INTX BIGINT
DECLARE @STRSQL NVARCHAR(400)

SET @INTX=1

WHILE (@INTX <= 100)
   BEGIN
      SET @STRSQL='INSERT INTO ID_Table VALUES(' + CONVERT(VARCHAR(50),@INTX) + ',' + CHAR(39) + 'NAME' + CHAR(39) + ')'
      EXEC(@STRSQL)
      SET @INTX=@INTX+1
  END

I got a question from Interview. Run the Truncate Command in Log Shipping Primary Database What are the impact will happen in Secondary database.

Truncate Command impacts will  forward to the Secondary Database. another question raised in your mind. since Truncate command is non logged command so how it will make a impact on second Database.

Truncate Table  command will not delete the data’s row by row basic. instead of  SQL Server just deallocating a pages from a table so this operation captured by a log file. when this log backup file will restore to the secondary database the same thing will happen in secondary database.

DBCC Log (<database name>,4)