Recently I have been through interesting forum post on SQL Server performance website that a user asking how to monitor transaction activity between the databases without using a third party tool. Until SQL 2000 version it is not that easy to get more information on what you want, but with SQL 2005 by...
I have had a requirement to see a detailed information about a constraint on a table that includes table fields, type, rules, referenced table and fields for FOREIGN KEYs, etc. Found this useful TSQL to get such information: SELECT k.table_name, k.column_name field_name, c.constraint_type, CASE c.is_deferrable...
By default the SQL Server database data & log files are created on the default data directory which is Program Files or based on where SQL binary files are installed. In order to find what is the default location you can run following TSQL: declare @SmoDefaultFile nvarchar(512) exec master.dbo.xp_instance_regread...
To provide High Availability feature within your database environment you could take advantage of Database Mirroring when using SQL Server 2005. As with all critical databases, minimizing data loss and downtime are of the highest importance. Having "AlwaysOn" configuration within the SQL Server...
Common question and ranting about Database maintenance plans in the forums, in this case I always suggest to use another job using TSQL to drop those older backup files. Andrew Kelly, SQL MVP has contributed an excellent script sample that should get you started in the right direction. You basically...
When I'm performing a performance analysis on a 24/7 application and dealing with PSS I had been given the following TSQL to identify the long running queries against a database. select r.session_id, s.host_name, s.program_name, s.host_process_id, r.status, r.wait_time,wait_type,r.wait_resource,...
As you may aware the PERFMON (SYSMON) utility provides much information to analyze on the systems resource usage, I support and suggest to make use of this tool as much as you can with a default templates within our environment. You may be aware the data is saved as a file with .BLG extension which can...
You may be aware that any login that is created on SQL Server will have permission or privilege to see all databases on that instance. This is due to VIEW ANY DATABASE permission that regulates the exposure of metadata system databases. This permission is granted to the public role. Therefore, by default...