Q&A archive
Questions and answers Andy Mallon contributed across the Stack Exchange network.
DBA Stack Exchange
Question In the good old days there was a shortcut ctrl-B for changing the size of the Query & Result panes in the Query Analyzer sql editor. In Query Analyzer, just press ctrl-B and the screen splitter handle was in focus. Then repeatedly press up/down …
Read answerDBA Stack Exchange
Question I have created a 2012 WFC and SQL Server 2014 is clustered on it. The cluster works as expected but have a question on Users: I added a windows domain group to the cluster as sysadmin. When the user tries to log into SQL on the active server the …
Read answerDBA Stack Exchange
Question ReportserverTempdb logfile is 6TB in size and growing and I need help. I am supporting a huge reporting server farm and the client wants reporting services to be in AlawysON Availability group(AAG) and we configured reportserver and …
Read answerDBA Stack Exchange
Question I have a table that has a unique ID, name, email, and some other fields. I want to match this table against another table on email, but sometimes emails are shared. The unique ID is not shared among tables, but I will input the ID into the other …
Read answerDBA Stack Exchange
Question I have a table that is several hundred columns wide. Is there a way to convert each row into a single concatenated string with the column title included without having to list each single column in the query? I'm doing this because the columns …
Read answerDBA Stack Exchange
Question I'm a web developer and I want to export a database to work on it in my local machine. I'm using generate script with data. the script is generated but without the linked servers used by some Views. Is their a way to generate a full script with …
Read answerDBA Stack Exchange
Question I am working with a 500 GB SQL Server database. I need to reduce the size of the database data file after removing a large amount (50%) of the data. I tried re indexing, it's good the database has de-fragmented. But the DB size is not reduced.
Read answerDBA Stack Exchange
Question I am trying to pull all data where a sale date is >= to a certain datetime. When I execute my query I get an error of: Msg 241, Level 16, State 1, Line 15 Conversion failed when converting date and/or time from character string.
Read answerDBA Stack Exchange
Question I'm designing a MongoDB database for an Uber-like application, but since I'm new to NoSQL concept, I have several doubts. I have three main collections - users, requests and messages. User can post a ride request, and if other user wants to give him …
Read answerDBA Stack Exchange
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:
Read answerDBA Stack Exchange
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 …
Read answerDBA Stack Exchange
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 …
Read answerDBA Stack Exchange
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?
Read answerDBA Stack Exchange
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 answerDBA Stack Exchange
Question Consider the following: CREATE PROCEDURE dbo.usp_trantest AS SELECT @@TRANCOUNT as trancount; GO 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 …
Read answerDBA Stack Exchange
Question I recently inherited a SQL Cluster (2008R2) which for the most part behaves itself impeccably. The windows cluster is made up of two nodes running Active/Passive, Node1 and Node2 are dedicated blades in two different data centers. There are 3 SQL …
Read answerDBA Stack Exchange
Question I have Native SSRS configured on a 2012 instance (11.0.2100). I have two reports with images that act as buttons to toggle between the chart report and the detail report. I want to configure the action URLs to dynamically pass in parameters when …
Read answerStack Overflow
Question Imagine we have two nodes participating in SQL 2012 AO. This is a test instance. During one of the index rebuild operation the log was grown up really big (250 GB). We are unable to back it up due to space constraint. What if we backup the Tlog to …
Read answerDBA Stack Exchange
Question I am trying to restore a .bak file (backup taken on SQL Server 2008 R2, compatibility level 100). I am restoring the backup on SQL Server 2012 edition. I get the below error. Unfortunately the original DB from where the DB was backed up is no longer …
Read answerStack Overflow
Question I have a database which is in always-on high availability mode synchronized with another database on a different instance. How can I restore from a .bak file into the primary database using T-SQL? I’m new to high availability and I’ve been advised …
Read answerDBA Stack Exchange
Question I need a quick clarification on copy-only backup vs full backup as it pertains to transaction log numbering and restore if required. I have SQL Server 2012 setup with always on for multiple databases. The availability groups are setup with preferred …
Read answerDBA Stack Exchange
Question I've centralized my farm's jobs in MSX, covering about 70 instances of SQL Server. Seemingly randomly, at least once a week, several instances will stop accepting instructions. Within the MSX window Manage Target Servers, instead of all instances …
Read answer