Q&A archive
Questions and answers Andy Mallon contributed across the Stack Exchange network.
DBA Stack Exchange
Question This is a weird one, and I've run out of ideas for how to go about troubleshooting this. Here's the issue, best as I can sum it up. The SQL Server engine and Agent services are not started when the server first boots when they're set to Automatic. …
Read answerDBA Stack Exchange
Question We have a few Basic Availability groups in production and I've been reading about the limitations of them. I'm concerned about the following limitations: No backups on secondary replica. No integrity checks on secondary replicas. Suppose that we …
Read answerDBA Stack Exchange
Question I am converting the times of some datetimeoffset columns from UTC to Eastern. I am doing this via the command UPDATE MyTable SET MyColumn = MyColumn AT TIME ZONE 'Eastern Standard Time' This seemed to be working correctly (times were shifted 4 hours …
Read answerStack Overflow
Question I have created a stored procedure which takes as inputs a schema name and a table name and outputs an INT containing the largest value in the id column of the table. I have also created a view containing all the schema names, table names, and primary …
Read answerDBA Stack Exchange
Question Inherited a database server to maintain and found that the transaction log is 92 GB. My plan is to do the backup of that and get it brought back down to a regular size, then fix it. The problem is I don't have enough disk space on the machine to do …
Read answerServer Fault
Rich Vieira asks about the feature compatibility of using AlwaysOn Availability Groups and Replication on the same database.
Read answerDBA Stack Exchange
Question I am using the below query to pull back database sizes for our SQL instances. This cannot pull data larger than 2TB back because of the maximum number the int data type can pull back. How do I go about altering the query to return the database size, …
Read answerDBA Stack Exchange
Question I have a publisher with the distributor on the same server which is transactional replication. There is also an active subscriber using push method. I need to set a new subscriber up going to a different server using the same articles as the current …
Read answerDBA Stack Exchange
Question Is there a way to force all database and log backups run on the server to be encrypted? If encryption is not specified, the query will be terminated. Asked2021-09-12byAvi Answer Not in SQL Server There is no option to force BACKUP DATABASE & BACKUP …
Read answerDBA Stack Exchange
Question I have a table that consumes almost 1.2 TB of space and has lots of historical data which are mostly irrelevant. Since performing bulk DELETE is going to cause a lot of heavy-lifting, I'm trying to use the concept of horizontal partitioning to create …
Read answerDBA Stack Exchange
Question Our server where the Microsoft SQL Server 2016 Web Edition server is installed is running very slow. We are thinking of increasing the number of cores licensed on it. However, we need to check how many CPU cores the database is using currently. Our …
Read answerStack Overflow
Question I need to get the values from a Excel Sheet (in a shared folder) to export it and then create a SQL Table in my instance. After of this I will execute a Stored Procedure (I already created) in order to create extended properties for a specific schema …
Read answerDBA Stack Exchange
Question I realize this is likely a simplistic question but my searches only return results for Drive Letters or Database names if I include SQL Server. Please feel free to point me to existing articles or answers, and my apologies in advance :)
Read answerDBA Stack Exchange
Question I’m breaking some new ground and trying to get into Azure Data Studio. I’m having a problem with intellisense that seems to be a bug with Azure Data Studio but I’m not sure. I have issues with basic queries. Foreign keys, select statements and insert …
Read answerDBA Stack Exchange
Question I have an "Basic" pricing tier Azure SQL database with a table of 7 columns, an ID column of int that is the clustered index and primary key, one datetime2(0) column, 3 varchar(100) columns and 2 varchar(MAX) columns, all nullable.
Read answerDBA Stack Exchange
Question Diving on dba.stackexchange.com I found a nice answer that teach me how to open a .bacpac file with File Explorer. I gave it a try with the database AdventureWorks2008R2 and all I did was: Save the database as .bacpac file Rename the file extension …
Read answerDBA Stack Exchange
Question We have a job which imports data daily from a remote SQL Server every night, I'll call it Import-Job. Most of the time this job runs through in about 15 minutes and everything is fine but due to a rather unstable VPN connection sometimes the job …
Read answerDBA Stack Exchange
Question I have seen on this site how to get the the third Friday of the month: select quote_date FROM table where datename(weekday, quote_date) = 'Friday' and datepart(day, quote_date)>=15 and datepart(day, quote_date)<=21; How would I get the first Monday …
Read answerDBA Stack Exchange
Question I have 2 node Windows Failover Cluster with Quorum disk. SQL agent is NOT a resource of the cluster. I needed to enable service broker on the server; for that I need to set SQL Agent offline, run tsql statement then simply put it back online.
Read answerDBA Stack Exchange
Question Is there a query I can run that will show me all tables with an identity column? Trying to figure out if there are any identity based tables whose current identity is nearing upper bound of int32 or int64.
Read answerDBA Stack Exchange
Question We have had an issue this morning with a report failing due to the following error: "Conversion failed when converting date and/or time from character string" The issue is that in the database we had a value (in a VARCHAR field) of: '31.07/2021'
Read answerDBA Stack Exchange
Question Is there a way in SQL Server 2012 to get what the server time zone would be on a particular date/time? In SQL Server 2016, you have AT TIME ZONE, but that's not available in 2012. I have a datetimeoffset and I need to convert it to the server's …
Read answerDBA Stack Exchange
Question I came across some weird behavior: While passing a float value into a varchar column, the values are getting converted from integers into scientific notation, and it's that scientific notation that gets stored as a string. if …
Read answerDBA Stack Exchange
Question I have been using linked server with the old provider (SQLNCLI) without any issue, as recommended by Microsoft, I'm planning to switch to new provider (MSOLEDBSQL). I'm able to add linked server using following T-SQL after installing the drivers EXEC …
Read answer