Q&A archive
Questions and answers Andy Mallon contributed across the Stack Exchange network.
DBA Stack Exchange
Question SQL Server System Databases, as far as I know, always have those same IDs and I have seen lots of maintenance scripts on the internet relying on the predicate WHERE database_id > 4 to exclude them from the actions of the script.
Read answerDBA Stack Exchange
Question We have a database of total size of 3886 GB. Out of which 778 GB is free. So total 3108 GB worth of data is present. Out of which 2458 GB is "LOB Data" and 400 GB is worth "Regular".
Read answerDBA Stack Exchange
Question We have got a procedure on SQL Server agent jobs and this job started to hang on transaction for many hours with wait stats CXCONSUMER and never complete. The problem is after we noticed that it was hanging we kill the job and started manually during …
Read answerDBA Stack Exchange
Question How many transactions per second can SQL Server table triggers handle? If I had a highly transactional table that was inserted into 1,000 times a second, are insert triggers on that table reliable to fire every single time? Asked2019-12-21byJ.D.
Read answerDBA Stack Exchange
Question I have a stored proc spGetSites that returns three columns for SiteName, SiteId and UnitCount. The stored proc runs perfectly fine. I'm trying to store the results of the stored proc to a temp table @Site to use for my report with below syntax. This …
Read answerDBA Stack Exchange
Question For example, I had a database and I want to see which physical backup file was used to restore into it. The problem is that the database was deleted. How can I see the record? Asked2019-11-23bysql-noob Answer Depending on how the database was …
Read answerSuper User
Educorreia is using GitKraken with GitHub, and needs some help navigating the licensing & edition differences to make that combination work with private repos.
Read answerDBA Stack Exchange
Question In quite a bad position here. This afternoon on my tempDB I ran the query: sp_helpfile to locate where my MDF and LOG file were. My MDF was on my C: drive and my LOG file was on a D: drive so I've run the following query to move this Log file from …
Read answerDBA Stack Exchange
Question For example, if I hit the sys dynamic views to select a specific query plan, am I able to insert that query plan into another database's plan cache, that runs the same exact query? (I know the query is hashed and compared to determine when to …
Read answerServer Fault
Tobias asks if it matters where Windows places its page file on a SQL Server server.
Read answerServer Fault
UserSteve is having trouble restoring an encrypted database backup to a new server. I try to walk him though the steps of getting the necessary encryption keys on the destination server.
Read answerSuper User
Hussain Akbar is creating a custom app, and is wondering if he can run both his production Web & SQL Server instances on the same Windows 10 machine to save on licensing. I don't like the sound of running the database on a web server.
Read answerDBA Stack Exchange
Question I need to let MySQL to check the uniqueness of a VARCHAR column which is 5000 max length and is not the primary key. I can use an index of length 500 on the column to speed up query, but how to create an unique key that works?
Read answerSuper User
nilsinelabore is struggling with Excel date/time formats after converting a CSV to Excel.
Read answerDBA Stack Exchange
Question This is a hypothetical question, that flows from the answer by sepupic where they explain that 2TB is the physical limit for SQL log file. if you need it more than 2Tb you add the second log file. I have long held the belief that multiple log files …
Read answerServer Fault
UserSteve is upgrading to a new server and needs to move all his server settings to the new instance. I introduce him to DBA Tools as a quick way to copy configurations and other stuff to the new instance.
Read answerDBA Stack Exchange
Question I have SQL server 2017. I have one 3 TB size database there. Somehow due to long running transaction the database got stuck 'IN Recovery' mode after SQL server Restarted. When I checked the sql error logs it says 2 189 255 seconds remaining (Phase 2 …
Read answerDBA Stack Exchange
Question I have a query that runs every few seconds on a Microsoft SQL Azure Database. It is from a web job that checks if there are actions that need to be done based on rows in a specific table (its a queueing table). Based on the image attached, does …
Read answerDBA Stack Exchange
Question We are planing to migrate an on-premises SQL Server db to AWS RDS SQL Server. My only concern is how can I create a linked server from AWS RDS SQL Server to on-premises SQL Server db? Is it possible? I am checking it because there are many views …
Read answerDBA Stack Exchange
Question I have came across several articles that mentioned that advise on not to store monetary value as decimal, but use integer instead. The reasoning that it does not store actual value and will cause some rounding difference/errors. But i have yet ever …
Read answerDBA Stack Exchange
Question How do I go about pausing transactional replication on the subscriber end. We have a publisher on Server A, a distributor on Server B and a subscriber on Server C. All are in the same network. Some background, we have quite a busy BizTalk server …
Read answerDBA Stack Exchange
Question Does anyone know how to restart a SQL Server Managed Instance? The stop / start / reset are all grayed out, so that’s really. It an option. Any thoughts? Thanks... Asked2019-01-05byKevin Wilkie Answer You don't restart Managed Instance. The beauty of …
Read answerDBA Stack Exchange
Question What is the most efficient way to determine whether row or page or columnstore compression (or none) would be most effective for each index in an Azure SQL Database (standard or premium tier) used for OLTP, where sp_estimate_data_compression_savings …
Read answerDBA Stack Exchange
Question I want to install Microsoft SQL Server LocalDB 2017 on my system. I downloaded SQL Server Express Setup (about 5.1 MB). Then I opened it and chose 'Download Media' option and then chose LocalDB. Then it downloaded SqlLocalDB.msi setup (about 45.9 …
Read answer