Database Restore History How old is the Database if restored from the Backup file : In lab environment , we always get the copy of production database and restore it. Have you ever wondered how old is the database in lab environment. SELECT DISTINCT TOP 100 …
Database Mail check
Database Mail check to ensure it is operational: USE msdb GO SELECT * FROM sysmail_allitems GO SELECT * FROM sysmail_sentitems GO SELECT * FROM sysmail_unsentitems GO SELECT * FROM dbo.sysmail_faileditems GO SELECT * FROM dbo.sysmail_mailitems GO SELECT * FROM dbo.sysmail_log GO Mail Accounts and Profile …
Temporary Tables Vs Table Variables
Temporary Tables Vs Table Variables Table Variables : No Transaction Log Overhead No Lock Overhead No Recompilations No Rollback Maintenance You can only have one Primary Key or UNIQUE Constraint. You can have only one INDEX Usually it doesn’t trigger Statement or Stored Procedure re-compilations …
Useful Links and URLS
Useful Links and URLS Cassandra Calender: http://www.planetcassandra.org/Calendar Learn Ruby: Code Academy – http://www.codecademy.com/courses/ruby-beginner-en-d1Ylq/0/1?curriculum_id=5059f8619189a5000201fbcb Standford CS Courses: http://see.stanford.edu/see/lecturelist.aspx?coll=63480b48-8819-4efd-8412-263f1a472f5a MIT Open Courseware: http://ocw.mit.edu/courses/find-by-topic/ Khan Academy: https://www.khanacademy.org/ Java JDK Path: /System/Library/Java/JavaVirtualMachines/1.6.0.jdk/Contents/Home David Malan Page: http://cs.harvard.edu/malan/courses/ SQL Server: Nested Loops Explained http://blogs.msdn.com/b/craigfr/archive/2006/07/26/679319.aspx Hash Join Explained http://blogs.msdn.com/b/craigfr/archive/2006/08/10/687630.aspx Merge …
Backup-Zip-Copy-Unzip-Restore-Notify SQL Server Databases
Backup-Zip-Copy-Unzip-Restore SQL Server Databases: I am sure we all try to bring databases from One Environment to Another Environment whether its to accomplish testing, debugging or maintaining the parity between production and lab. I used to spend little bit of time doing these tasks manually , after …
SQL Server : Find SQL Server TCP Port and IP Address
SQL Server : Find SQL Server TCP Port and IP Address SELECT @@SERVERNAME AS ServerName , local_tcp_port,local_net_address FROM sys.dm_exec_connections WHERE session_id = @@SPID Results : ServerName local_tcp_port local_net_address DEVENV\2008R2 59663 192.168.63.223
SQL Server – Create and Parse Comma Seperated List
XML Approach: Reads : 2 Duration:5 ms Substring Approach: Reads : 16 Duration: 14 ms –declare a variable and populate it with a comma separated string DECLARE @SQLString VARCHAR(MAX) SET @SQLString = N’94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, …
Removing Additional Data Files from TempDB
I had a need to remove TempDB DataFiles in one of our environments. When I ran the simple DBCC SHRINKFILE Command, I encountered following error. DBCC SHRINKFILE: Page 4:11283400 could not be moved because it is a work table page. DBCC SHRINKFILE (N’tempdev9′ , EMPTYFILE) …
Quick way to Reindex all tables on SQL Server Database
USE NMPortal GO EXEC sp_MSforeachtable @command1=”print ‘?’ DBCC DBREINDEX (‘?’, ‘ ‘, 80)” GO EXEC sp_updatestats GO
Rename SQL Server Database
Rename SQL Server Database: Change the Database to Single User Mode and Rollback all the existing Transactions. This is done because you need to obtain Exclusive Database Lock. That way you don’t encounter errors related to obtaining database lock. Rename the Database and then Turn …