Audit Trace Procedure _____________________ 1. Enable C2 Auditing from DB Options. 2. Restart SQL Server. 3. Check Location of Audit Logs in MSSQL folder in C:\ 4. Create AuditDB database with log and datafile in location D:\ 5. Create temp_trc table in AUDITDB database using below command : use auditdb SELECT IDENTITY(int, 1, 1) AS … Continue reading Create C2 Native Auditing for SQL Server
SQL 2008
Change MAXDOP (Maximum Degree of Parallelism) SQL Server
USE AdventureWorks; GO EXEC sp_configure 'show advanced options', 1; GO RECONFIGURE WITH OVERRIDE; GO EXEC sp_configure 'max degree of parallelism', 12; GO RECONFIGURE WITH OVERRIDE; GO
Creating Encryption Key Backup for SSRS 2008R2
Creating Encryption Key Backup for SSRS 2008R2 - r for taking backup of key -f for file location -i is the instance name of the Reporting Services Are you sure you want to extract the key from the report server? Yes (Y)/ No (N) : Y SECURE THE FILE IN A SAFE LOCATION The … Continue reading Creating Encryption Key Backup for SSRS 2008R2
Create Indexed Views SQL Server 2005 and 2008
SQL Server supports creating indexes on a view. It is the same concept as Materialized Views in Oracle. The main difference in the syntax in normal views and indexed views is the clausa "with schemabinding" Now go ahead amd create the index on the view you just created.
Sql server 2008 database mirroring manual failover
In case your mirroring has been suspended and you have tried everything else and nothing works. Your last option is to break the mirroring and restore the mirror database and connect the client application to the mirror site. Before proceeding with this please note that doing this will require you to rebuild the Data Mirroring … Continue reading Sql server 2008 database mirroring manual failover
Change Modes Database Mirroring SQL Server 2008
Incase you want to change the mode of the mirroring from High Performance to High Safety. You can do it from the command line. ALTER DATABASE SET PARTNER SAFETY OFF; Please note that SQL Server 2008 Standard Edition does not allow changing the safety level. It will throw the below error "Msg 1473, Level 16, … Continue reading Change Modes Database Mirroring SQL Server 2008
The mirror database has insufficient transaction log data
After you have restored the mirror database and configured Data mirroring, when you start the mirroring it throws this error. The solution to resolving this is to restore the backup of the transaction log to the Mirror database. 1. Take backup of the TLOG at Principal site BACKUP LOG TO DISK = N'E:\transaction_log_bkp\Tlog_Bkp.trn' WITH NOFORMAT, … Continue reading The mirror database has insufficient transaction log data
TRANSFER SQL SERVER LOGINS BETWEEN 2 DIFFERENT SERVERS
1. RUN BELOW SCRIPT ON SOURCE SERVER 2. RUN BELOW COMMAN ON SOURCE SERVER TO CREATE LOGIN SCRIPTS 3. APPLY THE LOGIN SCRIPTS ON DESTINATION DATABASE FOR EG:
Execute Job from Stored Procedure
To Execute a job from inside a stored procedure. Normally if the above statement is not used you will get an error like "The module depends on the missing object 'sp_start_job'."
Delete Large Number of Rows SQL Server
To delete rows without locking a table which has say more than 100 million rows to be deleted. Or a large table with archived data has to be deleted. You can use the below function to do this deletion.
SimpleSQLDBA | Shadab Mohammad 