Q&A archive
Questions and answers Andy Mallon contributed across the Stack Exchange network.
DBA Stack Exchange
Question I understand that table partitioning is mostly done for data management. I understand that table maintenance becomes more difficult for large tables, since e.g. an index rebuild cannot fit in memory or because e.g. sorts scale with O(n*log(n)) and …
Read answerDBA Stack Exchange
Question ALTER PROCEDURE [dbo].[usp_BaseScan_CycleCount_Report] @StartDate varchar(10), @EndDate varchar(10) AS BEGIN IF @StartDate AND @EndDate IS NOT NULL SELECT * FROM dbo.CycleCountHistory WHERE RecordDate BETWEEN @StartDate AND @EndDate ELSE SELECT * …
Read answerDBA Stack Exchange
Question My organization had a security audit and was told that we need to DISABLE the "allow remote connection to this server" option for all of our SQL Servers. What impact would this have on connection to the SQL Server via Management Studio (SSMS) from a …
Read answerDBA Stack Exchange
Question When saving a number with leading zeros into a string variable or column, leading zeros are truncated. When I run this query: declare @varchar as nvarchar(50) set @varchar = 011 print @varchar The result I get is 11. Is there any way to get 011?
Read answerStack Overflow
Question I have 2 tables(source and destination) which are respectively NOR_LABOR and ALL_LABOR_DETAILS. In the source table(NOR_LABOR) there is a column feet_produced with the data type nchar(10). In the destination table(ALL_LABOR_DETAILS) there’s a column …
Read answerDBA Stack Exchange
Question Is there a way to view and copy the code that the "Import Flat File to SQL" Wizard ultimately uses, after I have gone through all the steps (chosen file, set up data types for my columns, etc.)? While using the Import Flat File to SQL wizard, I found …
Read answerDBA Stack Exchange
Question we use the SqlTransaction-class in the backend to access data stored in the MS-SQLServer. In some cases we definitely know that no data were changed by this transaction (and no errors occurred). So the question is: How to close transactions in these …
Read answerDBA Stack Exchange
Question I have a stored procedure that begins by declaring a few variables then contains begin tran; After this it performs some validations on the supplied arguments (and increments an error count each time a supplied argument fails validation). If there is …
Read answerDBA Stack Exchange
Question UPDATE MARK M SET ARCHIVE_FLAG = 'N' WHERE EXISTS (SELECT /*+ cardinality(S1, 10) parallel(S1,8)*/ 1 FROM SHFASG S, SHIFT S1 WHERE S.ID = S1.ID AND M.ID = S.MARKID AND ARCHIVE_FLAG <> 'Y'); This is the oracle query that I have, I want to create …
Read answerDBA Stack Exchange
Question Table sysjobschedules contains 2 columns next_run_date and next_run_time. With the help of sysschedules and column enable it should be easy to determine the next running date/time of a specific job. However, when you change a schedule, …
Read answerDBA Stack Exchange
Question I am trying to figure out the way to monitor these 2 events log send queue size - I can see this in perfmon Redo queue size - I can see in dmv but not in perfmon counter Is there any way of using perfmon so that I can calculate the redo queue size …
Read answerSuper User
RonJohn needs to know if his VMs are hyperthreaded. I point him to vCenter for a definitive answer, and to a SQL Server DMV for the next best thing.
Read answerDBA Stack Exchange
Question For SQL Server Enterprise's Always On availability groups in Synchronous Commit mode, is there some amount of latency that can't be exceeded? For example, can it work between very distant data centers, such as between the US and Europe?
Read answerDBA Stack Exchange
Question I have two sql servers, in the first I want to keep the data from the last three months and in the second I want to keep all the data with the purpose of getting historical reports. I know how to do a transactional replica but I don't know how to …
Read answerDBA Stack Exchange
Question I wrote a script that reads job schedules from msdb.dbo.sysschedules and runs sp_update_schedule to change the active_start_time for a given subset of jobs. In each case, sp_update_schedule fails with the message "The specified @schedule_id('xxx') …
Read answerDBA Stack Exchange
Question i am running sql server 2019 on Linux box and trying to connect using a Windows server 2016 client, how can we enable TLS 1.0 for SQL Server 2019 on the Database/instance level. TIA. Asked2020-09-25byRafay Pervaiz Answer TLDR You don't.
Read answerDBA Stack Exchange
Question SQL Server was restarted by mistake, when it came online, database came back in "In Recovery" mode. Check from error it says "Recovery of database 'DB1' (5) is 8% complete (approximately 27146 seconds remain). Phase 2 of 3. This is an informational …
Read answerDBA Stack Exchange
Question I've been handed a graph that comes from SolarWinds showing top wait events broken down by day. The last 2 days there's a part of the daily color coded bar representing a total of 3 hours for ASYNC_NETWORK_IO. Still trying to see if I can get access …
Read answerDBA Stack Exchange
Question (This is just a "reproducer", not the real code, because that's long and complicated.) The output of SET NOCOUNT ON select @@SERVICENAME select @@version is: ----------------------------------------------------------------------------- MSSQLSERVER …
Read answerDBA Stack Exchange
Question I'm working on a web app which is using SQL Server Express (Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64) Oct 28 2016 18:17:30 Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows 10 Pro 6.3 (Build 18362: )).
Read answerStack Overflow
Question I’m trying to make CTE to count blocking sessions on server. Currently I made a function to do so(returns blocking session amount). But still could not write this query with CTE. CREATE FUNCTION dbo.SESSIONS(@SESSION int) RETURNS INT AS BEGIN DECLARE …
Read answerDBA Stack Exchange
Question A couple years ago we ran into a situation where we were changing the database owner from a DBA's login to sa, and immediately thereafter we received a complaint that the service login (which was NOT the DBA's login) no longer had access to the …
Read answerDBA Stack Exchange
Question I can take databases offline (via GUI) but can't bring them back online. The server details are as follow: RDBMS: MS SQL 2016 Express Host: Amazon AWS/RDS Free Tier Details/History of the Problem A few months ago, I created a db instance on Amazon …
Read answerDBA Stack Exchange
Question Here is my situation: I have a WebSocket connection that feeds me data real-time I have an async callback function that gets the data and inserts into a queue I have another thread that reads from the queue and stores it into the Postgres database, …
Read answer