Uncategorized

Trace Flags SQL Trace Flag? Trace flags are used to set temporary behavior or characteristics or to switch on or off particular behavior by DB engine. In particular when diagnosing the cause of error by some queries inside the DB engine. Ways to do it? *** user needs “sysadmin” fixed server role to enable or…

Must Known Things about Replication in SQL 2012.

New features in SQL Server 2012 Replication Updatable subscriptions including immediate updating and queued updating with snapshot and transactional publications are discontinued. Four new stored procedures provide replication support for AlwaysOn. sp_redirect_publisher sp_get_redirected_publisher  sp_validate_redirected_publisher  sp_validate_replica_hosts_as_publishers Replication supports the following features on Availability groups: A publication database can be part of an availability group. The publisher…

/********************************************** –Script to find out currently executing query **********************************************/ SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete) AS [Percent Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time], CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min], CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min], CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours], CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2, CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END) FROM sys.dm_exec_sql_text(sql_handle))) FROM sys.dm_exec_requests r WHERE command IN (‘RESTORE DATABASE’,’BACKUP DATABASE’) /********************************************** –Missing…

http://social.technet.microsoft.com/wiki/contents/articles/13198.useful-tsql-scripts-for-your-dba-toolbox.aspx

Copied from good scripting guys experience journal … “AS IT IS” Server Setups / Installations We need to standardize the setup of all the systems we own. By doing this in a consistent manner, not only does it save us work down the road, but it also makes it easier to do our current work.…

Post “AS IT IS” You may want to consider enabling Instant File Initialization (via SE_MANAGE_VOLUME_NAME a.k.a. “Perform volume maintenance tasks”) for your SQL Server startup/service account. This option allows for much faster data file allocations (CREATE AND ALTER FILE) but DOES NOT WORK FOR LOG FILE ALLOCATIONS. This is enabled for each instance via the “Perform…

1. If it’s a physical server, reboot into the BIOS and set power management to “High Performance.” 2. Update device drivers to the latest manufacturer-supplied driver for networking and storage HBAs 3. Configure the storage a. Open Server Manager, File Services, and Disks. b. Online the disks, using NTFS for all of them. c. Format…

Hello Friends, Recently found that shrinking tempdb doesn’t cause any kind of corruption , here the KB supporting the same,., http://support.microsoft.com/kb/307487/en-us

http://sqlserverbuilds.blogspot.com