Database Experts

Checks the logical and physical integrity of all the objects in the specified database by performing the following operations: ·          Runs DBCC CHECKALLOC on the database. ·          Runs DBCC CHECKTABLE on every table and view in the database. ·          Runs DBCC CHECKCATALOG on the database. ·          Validates the contents of every indexed view in the database. ·          Validates link-level consistency between table metadata and file system directories and files when storing varbinary(max) data in the file system using FILESTREAM. ·          Validates the Service Broker data in the database. This means that the DBCC CHECKALLOC, DBCC CHECKTABLE, or DBCC CHECKCATALOG commands do not have to be run separately from DBCC C...

1.Create Table for Insert the Audit Values. USE [master] GO /****** Object:  Table [dbo].[ServerLogonHistory]    Script Date: 07/17/2012 12:22:49 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[ServerLogonHistory](     [Id] [int] IDENTITY(1,1) NOT NULL,     [Login_User] [varchar](100) NULL,     [Database_User] [varchar](50) NULL,     [SPID] [int] NULL,     [LogonTime] [datetime] NULL,     [Host_Address] [varchar](100) NULL,     [Last_Program] [varchar](max) NULL,     [Database_Used] [varchar](30) NULL,     [LogOffTime] [datetime] NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO 2.Create SERVER Trigger to Audit Login Details /****** Object:  DdlTrigger [Trg_LogonEntry]    Script Date: 07/17/2012 10:23:52 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER...

Use this script to take backup of transaction logs. 1.Create [Transaction_Logs_backup] Stored Procedure. set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go CREATE Procedure [dbo].[Transaction_Logs_backup] as DECLARE @name VARCHAR(50) -- database name DECLARE @path VARCHAR(500) -- path for backup files DECLARE @fileName VARCHAR(500) -- filename for backup DECLARE @fileDate VARCHAR(20) -- used for file name DECLARE @Sql varchar(500)-- Script to take backup of transaction SET @path = '\\192.168.0.242\Database Backup\192.168.0.110\Transaction_logs\' SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),105) DECLARE db_cursor CURSOR FOR SELECT name FROM master.dbo.sysdatabases WHERE name  not IN ('master','model','msdb','tempdb') OPEN db_cursor  FETCH NEXT FROM db_cursor INTO @name  WHILE @@FETCH_STATUS = 0  BEGIN         SET @fileName = @path +'Transaction_log_'+ @name + '_' + @fileDate + '.trn'        SET @...

1.Take Database backup. 2.Backup Transaction Logs. Use this link, Click Here 3.Use this script to shrink transaction log. SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE procedure Trunc_Transaction_log as begin --Check Transaction log Usage DBCC SQLPERF(logspace) -- step 1. get hold of the entire database names from the database server CREATE TABLE #TransactionLogFiles (DatabaseName VARCHAR(150), LogFileName VARCHAR(150) ) DECLARE DataBaseList CURSOR FOR SELECT name FROM SYS.sysdatabases WHERE NAME NOT IN ('master','tempdb','model','msdb','distribution') -- step 2. insert all the database name and corresponding log files' names into the temp table DECLARE @DataBase VARCHAR(128) DECLARE @SqlScript VARCHAR(MAX) OPEN DataBaseList FETCH NEXT FROM DataBaseList INTO @DataBase WHILE @@FETCH_STATUS <> -1 BEGIN SET @SqlScript = 'USE [' + @DataBase + '] INSERT INTO #Trans...

Database Experts: Microsoft SQL Server 2005/2008:Create Read Only Us... : 1.Create Read Only User. CREATE LOGIN [ ROUser ] WITH PASSWORD=N''*****', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHE...

1.Create Read Only User. CREATE LOGIN [ ROUser ] WITH PASSWORD=N''*****', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO 2.Grant VIEW DEFINITION Role to selected SCHEMA.Default Schema is DBO. USE DatabaseName GRANT VIEW DEFINITION ON SCHEMA:: dbo TO [ ROUser ] About Me

1.Check Database Compatibility Level. select name,compatibility_level from sys.databases where name like ' %DBNAME% ' Current version is sql server 2000 where compatibility_level=80   EXEC sp_dbcmptlevel DBNAME , 90;--Set SQL Version 2000 to 2005. GO Check Changes compatibility_level  About Me