Create snapshot on mirror database Sql Server 2008 -- Run below command to find logical filename of database against which you creating a mirror select name, type_desc, physical_name from sys.master_files where database_id = db_id('QIIB_MSCRM') mscrm ROWS C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\QIIB_MSCRM.mdf mscrm_log LOG C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\QIIB_MSCRM_log.LDF -- mscrm is name of file. Now go ahead … Continue reading Create Snapshot on Mirror Database SQL Server 2008
Author: Shadab Mohammad
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%' );
Mutiple Inserts. Normal method VS Union ALL method
I know the eternal war between DBA’s and Developers rages on :D, While most developers prefer to make multiple inserts. DBA’s always use UNION all. While highly debatable which is fast performance wise. But if you are doing thousands of insert’s UNION ALL is definitely faster. I have narrowed down inserts from 40 second to … Continue reading Mutiple Inserts. Normal method VS Union ALL method
MRemote multi-tab remote connection manager
If you have multiple remote connections like VNC/ RDP/ SSH/ TELNET. There is a good software to manage multiple connections and save it for future use. It keeps all connections in a uniform tab-based format. Very powerful and useful tool. The best part : It is completely free and open source ! mRemote : full-featured, … Continue reading MRemote multi-tab remote connection manager
Alternative to GREP in Windows
C:\Program Files\Windows Resource Kits\Tools>findstr /? Searches for strings in files. FINDSTR [/B] [/E] [/L] [/R] [/S] [/I] [/X] [/V] [/N] [/M] [/O] [/P] [/F:file] [/C:string] [/G:file] [/D:dir list] [/A:color attributes] [/OFF[LINE]] strings [[drive:][path]filename[ ...]] /B Matches pattern if at the beginning of a line. /E Matches pattern if at the end of a line. /L … Continue reading Alternative to GREP in Windows
SQL SERVER – Query to find number Rows, Columns, ByteSize for each table in the current database – Find Biggest Table in Database (via Journey to SQLAuthority)
USE DatabaseName GO CREATE TABLE #temp ( table_name sysname , row_count INT, reserved_size VARCHAR(50), data_size VARCHAR(50), index_size VARCHAR(50), unused_size VARCHAR(50)) SET NOCOUNT ON INSERT #temp EXEC sp_msforeachtable 'sp_spaceused ''?''' SELECT a.table_name, a.row_count, COUNT(*) AS col_count, a.data_size FROM #temp a INNER JOIN information_schema.columns b ON a.table_name collate database_default = b.table_name collate database_default … Read Morevia Journey to SQLAuthority
Error : the RPC security information for the publisher is missing or invalid
This is genereally an error which will be throw when you have a replicated set of data in SQL Server and if you are trying to perform a DML statement on the table it gives this error. This error is generally related to network transaction in DCOM service in Windows. To allow the network transaction, … Continue reading Error : the RPC security information for the publisher is missing or invalid
Access Oracle tables from SQL Server 2005
In a heterogeneous environment it sometimes becomes necessary to access information across different database. In this exercise I will demonstrate how you can access Oracle db tables in SQL Server 2005. 1. First you need to install Oracle client on the SQL server 2005 server. You can install using the instant client option. I am … Continue reading Access Oracle tables from SQL Server 2005
SimpleSQLDBA | Shadab Mohammad 