Q&A archive
Questions and answers Andy Mallon contributed across the Stack Exchange network.
DBA Stack Exchange
Question We have a 120GB database. There was table with 60GB of data which is useless and we have truncated it. Now database size is 120GB with free space 60GB. The database will not grow up to 60GB in at least 3 months. So can we shrink the data file.
Read answerDBA Stack Exchange
Question I am having trouble getting a trigger to work. What it needs to do is to prevent any customer with last name Glass from being inserted into the database. This is what I have: CREATE TRIGGER no_last_name_glass_trg ON customer AFTER INSERT,UPDATE AS …
Read answerDBA Stack Exchange
Question This question was attempted twice on experts-exchange with no real answer. I know that I should not shrink the log file - that is not the question. If you feel like answering that one should not do it- that has been confirmed many times yes and it is …
Read answerDBA Stack Exchange
Question I'm creating a query that has a column of dates stored as a VARCHAR in the format DD-MMM-YYYY. Could you tell me how I can convert this into date format please? I don't want to alter the original table in any way, as it's not mine to tinker!
Read answerDBA Stack Exchange
Question One of our SQL Server 2014 database transaction logs is growing very fast and I cannot find the root cause for this. Database in Simple recovery mode. During full backup the log file grows to its limits (800GB) and backup fails.
Read answerDBA Stack Exchange
Question We have the ApplicationIntent=ReadOnly properly routing our users to the Secondary ReadOnly replica. Is there a way to make this the default behavior? Apparently the default behavior seems to be ReadWrite, and if you don't specify an …
Read answerDBA Stack Exchange
Question In my environment, I have SQL 2014 with Always On Availability Group for databases in asynchronous with manual fail over. We have 1 primary and 2 secondary replicas--secondary1 and sec2. I have to do failover from primary to secondary1. I know some …
Read answerDBA Stack Exchange
Question I've some logs that I want to extract to a report in order to get the SQL_Agent_Jobs status that runs during the night. If the Job fails then I want to receive the Error Log in a table. I am trying to get that information with this query:
Read answerDBA Stack Exchange
Question In SQL Server, a non-unique nonclustered index on a rowstore table incorporates the base object’s bookmark (RID or clustering key) at all levels of the nonclustered index structure. The bookmark is stored as part of the nonclustered index key at all …
Read answerDBA Stack Exchange
Question I create a bacpac from azure and restore to my local on-prem server. However, it throws an error like “Please create a master key in the database or open the master key in the session before performing this operation”. The reason is one of the column …
Read answerDBA Stack Exchange
Question I am a new DBA at a company that has never had a DBA before me. SQL Servers are never upgraded unless it is part of a Windows Update on the server. Service Packs (or CUs) are never installed. We have a mix of versions from 2000 to 2014.
Read answerDBA Stack Exchange
Question I just executed a query that went pretty fast and the SQL Profiler said it used 30.000 reads. Now I'm looking at this page: https://cloud.google.com/compute/docs/disks/performance and see that a standard persistent disk has 3000 reads IOPS. Does that …
Read answerDBA Stack Exchange
Question What is the MySQL equivalent of SQL Server 2016 Always Encrypted technology or is there anything similar in MySQL that can act as AE? Asked2016-12-20bydwkd Answer The Always Encrypted feature provides end-to-end encryption, which encrypts data both …
Read answerDBA Stack Exchange
Question I need 1100 columns in a single table.I already have 928 columns in same table, now I need to add new 150 columns. I know SQL Server allow maximum 1024 columns. Now I will not create new table. If I create new table and join my require table, I need …
Read answerDBA Stack Exchange
Question I have like 20 Listners on 2014 with approx 500 DBS, what is the best way to migrate these with least downtime, effort My idea is: Stop access Backup dbs Start restore dbs While restore is running: Drop AOG on 2014 Create it on 2016 Done
Read answerDBA Stack Exchange
Question So I have a bit of a problem with some databases I'm trying to migrate to amazon RDS We have about 20 Databases that are all derivatives of others that all share the same family_guid because they are based on backups of others. The problem is that …
Read answerDBA Stack Exchange
Question I have a very strange slow insert issue, here I am providing the table structure, and the insert statement used. The database is clean, contains no other tables, no indices, no constraints, no triggers. I tested this on two different machines, with …
Read answerDBA Stack Exchange
Question I am querying data from a linked server through a view on the origin server. The view has to include a couple of standardized columns, such as Created, Modified and Deleted, but in this case the table on the source server doesn't have any suitable …
Read answerDBA Stack Exchange
Question Does it ever make sense to have a clustered and non clustered index for the same column I inherited a database and a web service that goes with it. Looking at the indexes for duplicates I see an two indexes that point at the same column (1 column and …
Read answerStack Overflow
Question I have 4 tables with dates to which I want to join to a date dimension table but it is returning many duplicates with left joins. Tables are basically a date field which I want to count. mdate datetime, mordate varchar(10),fteam varchar(20) sdate …
Read answerSuper User
vstrien is using the (legacy) SQLPS Powershell module, and running into a bug where importing the module changes your current directory.
Read answerDBA Stack Exchange
Question Using SQL Server Management Studio, open a query window and execute BEGIN TRANIn another window execute select [text], from sys.sysprocesses cross apply sys.dm_exec_sql_text(sql_handle) where status = 'sleeping' and open_tran = 1I see select …
Read answerDBA Stack Exchange
Question We are planning a migration from current mirroring architecture to Availability Groups for several databases. For every Database we have defined the primary/secondary replica configuration. The question that I'm unsure is: do we need to group the …
Read answerDBA Stack Exchange
Question I'm building a stored proc that can take in two separate values that each can be used to identify a single record. One of these values may be null. My logic is as follows: if IN_ORDER_ID is not NULL, save it to V_ORDER_ID. If it is NULL, then …
Read answer