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
SQL 2000
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
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
List all SQL Servers running on your Domain using a single command
Sometimes you want to find all the SQL servers running on your Domain with one quick command. SQLCMD providess facility to do so. Your client workstation should have Microsoft SQL server client installed on it for SQLCMD to work. 1. Go to Command Prompt. If you are on Windows Server 2008. Right Click Command Prompt … Continue reading List all SQL Servers running on your Domain using a single command
Search a specific column name in all tables SQL Server
SELECT name FROM sysobjects WHERE id IN ( SELECT id FROM syscolumns WHERE name = 'COLUMN_NAME' ); SELECT name FROM sysobjects WHERE id IN ( SELECT id FROM syscolumns WHERE name like '%COLUMN_NAME%' );
Find Duplicate Records in SQL Server
Microsoft SQL Server tables should never contain duplicate rows, nor non-unique primary keys. For brevity, we will sometimes refer to primary keys as "key" or "PK" in this article, but this will always denote "primary key." Duplicate PKs are a violation of entity integrity, and should be disallowed in a relational system. SQL Server has … Continue reading Find Duplicate Records in SQL Server
Managing Transaction Logs
What is the Transaction Log? At its simplest, the transaction log is a log of all transactions run against a database and all database modifications made by those transactions. The transaction log is a critical part of the database’s architecture. The transaction log is not an audit log. It’s not there so that the DBA … Continue reading Managing Transaction Logs
SimpleSQLDBA | Shadab Mohammad 