Feeds:
Posts
Comments

Archive for January, 2012

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

Read Full Post »

—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

Read Full Post »

        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.

Read Full Post »