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 2005
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.
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:
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.
Select Random Rows from Table in SQL Server
To Select 1 Random Records from a table in SQL Server SELECT TOP 1 * FROM Table_Name ORDER BY NEWID() ; To Select 10 Random Records from a table in SQL Server SELECT TOP 10 * FROM tAudit ORDER BY NEWID()
Check for Listening Port SQL Server
DECLARE @tcp_port nvarchar(5) EXEC xp_regread @rootkey = 'HKEY_LOCAL_MACHINE', @key = 'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP', @value_name = 'TcpPort', @value = @tcp_port OUTPUT select @tcp_port
Create login SQL Server 2008
1. Go to Login > Properties > Create New Login
Attach Deattach Database T-SQL SQL Server 2005 2008
-- Attach a database USE master; GO CREATE DATABASE ON (FILENAME = 'd:\sql data\dbname.mdf'), (FILENAME = 'd:\sql data\dbname_Log.ldf') FOR ATTACH; GO -- Dettach database and take offline -- USE master; GO ALTER DATABASE SET OFFLINE WITH ROLLBACK IMMEDIATE go EXEC sp_detach_db @dbname = N'dbname'; GO
Bring database offline online T-SQL
-- Bring Database Offline USE master GO ALTER DATABASE YourDatabaseName SET OFFLINE WITH ROLLBACK IMMEDIATE GO -- Bring database online USE master ALTER DATABASE SET ONLINE GO
