Question I have AlwaysOn Availability Group setup for my SQL Server. Now I would like to configure SSIS. How do I do it? Below link provides some information but since I am not DBA it’s difficult to understand this article: AlwaysOn Availability Groups and SQL … [Read More]
Question I need to connect to a SQL Server through UFT (Unified Functional Testing) but the problem is SQL Server is present at different domain whereas QTP (HP QuickTest Professional) is present in a different domain. I am able to connect to SQL Server manually … [Read More]
Question So the default location for databases on our server is c:\program files\microsoft sql server\mssql11.mssqlserver\mssql\DATA but due to space limitations we instead use d:\data I have now since corrected the default location to be d:\data However I am unable to move a database that was … [Read More]
Question The TRUSTWORTHY setting can be rather dangerous if you aren’t careful and except for specific circumstances the recommendation is to keep it turned off. However by default the MSDB database has TRUSTWORHTY set ON by default. I’m curious why? I’ve read this entry in … [Read More]
Question I cannot restore a Microsoft SQL Server database due to lack of hard drive space. The original database was 1.6TB and approximately 1.4TB was deleted from it and a backup was taken without shrinking the database. The backup file is only 168GB and was … [Read More]
Edit: I’ve published an updated version of my cheat sheet. Find the latest version by in the sidebar –> I’ve presented my Shortcuts from an Impatient DBA talk a few times now, and I really love it. Everyone learns something, whether you’re a Junior Developer, or a multi-decade … [Read More]
Question Consider the following: When I call usp_trantest manually from within SSMS, the trancount is 0. If I run an SSRS report that contains a dataset that queries the same stored procedure, the trancount is logged as 1. Doing a T-SQL trace of the SSRS … [Read More]
If you’ve ever looked at sys.sysprocesses or sys.dm_exec_requests (or a number of other DMVs), you’ve noticed there is a column called “sql_handle” that contains some binary gobbledygook. Books Online gives the (un)helpful definition as “Hash map of the SQL text of the request.” sp_WhoIsActive In … [Read More]
SQL Server supports two kinds of data compression on rowstore data. Columnstore compression is for a different day. You can compress heaps, clustered indexes (on both tables and views), and nonclustered indexes (on both tables and views). Compression requirements Data compression is available in SQL … [Read More]
What’s xp_sqlagent_enum_jobs? If you landed on this post, I’m guessing you know the answer to this, so I’ll be quick. xp_sqlagent_enum_jobs is an undocumented (and thus, unsupported) extended procedure that gives information on whether a job is currently running, when it last ran, when it runs … [Read More]
Copyright © 2025 | Andy Mallon | am2.co