2021-04-12 09:35
Question: I use Powershell extensively to manage SQL Server and the Windows OS. A common problem is to identify location of the Windows Recycle Bin and clear the contents down - partic… Read More
Blog Directory > Software Blogs > Programming Blogs > SQLSERVER-DBA.com programming Blog >
Sqlserver-dba.com Blog
sqlserver-dba.com
Tags:
sql server
availability
database
recycle
extended events
join
sql server database
server
log chain
powershell
SQL Server consulting ,SQL Server support, SQL Server Performance Tuning and SQL Server Training
2021-03-30 11:33
I use Powershell extensively in supporting SQL Server & other applications .Powershell is a significant component of the Windows operating system. I won't go into detail but there… Read More
2021-03-29 14:49
Question: I need to extract the securables for a user created SQL Server database role. For example , the Explicit Permissions including the Permission,Grantor,Grant,With Grant a… Read More
2021-03-26 16:17
Question: I've captured extended events messages into an xel file, and trying to read from the file with this query using the Extended Events function sys.fn_xe_file_target_read_file… Read More
2021-03-25 17:03
--return each node in the failover failover cluster instanceSELECT NodeName, status, status_description, is_current_owner FROM sys.dm_os_cluster_nodes;
--is the sql server configured in a fa… Read More
2021-03-23 15:56
OLAP performance tuning
SQL Performance tuning - Asking the right question
Performance Tuning by Committee -
SQL Performance tuning - Drilling into the pr… Read More
2021-03-22 17:08
Question: How can I get a list of databases on an Always On Availability Groups setup , along with the DNS name ?
Answer: This query will list a group of databases associated with availabil… Read More
2021-03-19 15:23
Steps to restore a database from a backup device that was part of an Always On Availability Group, and now needs to be restored
Recovery Scenario : Requesting an older database copy p… Read More
2021-03-18 16:53
Question: How can I view SQL Server Logging on Linux ?
Answer: SQL Server on Windows - logs details into SQL Server Error logs and Application Logs. Windows event viewer details a… Read More
2021-03-14 15:32
I was talking to an Auditor recently - who specialises in large Corporate Audits - and they asked me how would I prove a certain database which is backed up is actually restored to another s… Read More
2021-03-13 09:14
Question: I want to create a report using Powershell where the Stop & Start times of SQL Server related windows services are reported. As well as the username (if available)… Read More
2021-03-11 16:36
Microsoft adutil is the a CLI based utility designed to support Active Directory authentication on Linux \ SQL Server & Linux \ SQL Server Containers.
This page is an FAQ on variou… Read More
2021-03-10 16:15
Clusterless Availability Groups are not true HA as there is no underlying cluster - and generally is intended as read only. I've also seen this set up being used in migration scenarios… Read More
2021-03-09 10:52
In an environment with Active Directory and SQL Server using Kerberos ,Service principal names (spn) become important. Windows supports delegating to NTLM protocol if Kerberos is not availab… Read More
2021-03-01 16:06
Question: I have a large list of Active Directory Groups and would like to extract the users from these Active Directory Groups. How can I script to iterate through a large list of Active Di… Read More
2021-02-24 16:22
Question: I'm trying to add a new database to an existing Clustered Always On High Availability Group , but every time I execute the following statement , I get the error message below… Read More
2021-02-16 09:37
Question: I’d like to increase the memory allocated to our SQL Server instances. Once the ‘maximum server memory…’ is increased, are there any SQL services whi… Read More
2021-02-11 11:23
Question: I've deployed Container based SQL Servers and set up integrated authentication with Active Directory. There are some issues and I need to explore error messages for some AD failure… Read More
2021-02-04 11:38
SQL Server Orphaned Users are a typical item on security vulnerability reports. The next step is to understand why and when SQL orphaned users occur , and thinking about how to set up… Read More
2021-01-29 16:19
Question: I'm setting up different Podman and Docker based Containers for SQL Server. I'd like to know , how can I identify the SQL Server version & build number , if I'm using the mcr.m… Read More
2021-01-27 11:48
Question: Do you have a sql script to list database orphaned users. ? I need to run a daily report , checking if orphaned database users exist and then having a process to work with the appl… Read More
2021-01-22 16:41
Question: I need to configure some applications to use some Domain Controllers . To establish the address - I need to list the Domain Controllers within the Active Directory Forest. Is there… Read More
2021-01-12 12:57
These are queries to manage SQL Server AlwaysOn Availability Groups
Using sys.fn_hadr_is_primary_replica to identify if the database is primary replica
SELECT sys.fn_hadr_is_pri… Read More
2021-01-04 15:48
There is a message found in SQL Server Error Logs similar to
The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/myserver.net:60000… Read More
2020-12-31 11:42
Question: How can I find a list of Active Directory users with a specific search filter on the name column using Powershell? In this case I want to find all Active Directory user… Read More
2020-12-23 10:06
Question: Below is table and expected result. What is the query to achieve this resultTable : Test
ID LOT7065161 47065212 17065212 47065203 17065203 27065203 3
Expected Result of query… Read More
2020-12-09 08:46
Question: I have a bcp command exporting a sql query which outputs to a file . The problem is the file is being saved as Unicode type - causing a subsequent ETL process to fail. How can I fi… Read More
2020-11-18 16:44
Question: I have a SQL Server Linked Server configured , pointing to an ODBC - accessing a MongoDB driver . The test connections all work OK - and no problems running an OPENQUERY select sta… Read More
2020-11-17 09:53
Question: I'm migrating some SQL Server databases to Openshift Containers. The SQL Server is set up with persistent disk , with a dedicated persistent disk partition for the SQL Server defau… Read More
2020-11-09 18:32
Question: I've found an issue we have with BI Connector over MongoDB. I'm extracting data from MongoDB to SQL Server - using the MongoDB ODBC Data Source Configuration and Linked Server. Bas… Read More
2020-11-02 12:20
I had a question from a fellow DBA about how to list every file per SQL Server filegroup , and report on the size in MB. This will give them a quick way of checking which file could be… Read More
2020-10-08 14:36
Question: I want to check if SQL Server Agent is running using t-sql. I know I can check through the services.msc or other methods such as Powershell scripts to report on the SQ… Read More
2020-08-23 12:56
If you want a Powershell script to quickly check if Puppet has initiated a reboot on a Windows server , use this script . It uses the Powershell cmdlet -Get-WinEvent.
Typically - a Pup… Read More
2020-07-28 07:29
In addition to a general database policy and practices of securing databases such as : Security architecture, Asset encryption ,Firewalls & Regular Audits , there is an option to apply T… Read More
2020-07-09 15:30
Question: I need to present the difference between two dates - returned by GETDATE() , which are using the datetime data type, as hours , minutes, seconds i.e hh:mm:ss , not as c… Read More
2020-07-06 09:44
Question: I use the Powershell cmdlet Get-ADPrincipalGroupMembership to get AD group membership of IDs within the current domain, but how can I use Get-ADPrincipalGroupMembership to ch… Read More
2020-07-03 14:51
Configuring a SQL Server with Instant file initialization is a fairly standard part of SQL Server Installation Checklists. There are substantial benefits to the duration taken during a SQL S… Read More
2020-06-30 09:39
During an UPDATE statement on a particular row on a SQL Server table , this error message appeared:
Error Message:The UPDATE statement conflicted with the REFERENCE constraint "F… Read More
2020-06-29 15:28
Question: I want to set up a script to Mount a Disk in an automated way utilising Powershell ? The image exists as an ISO on a network path and requires to be made available as a drive lette… Read More
2020-06-29 13:22
Question: I have received a server from the server build team and I've started a Server set up. During the set up process , I'm getting an error message
The RPC server is unavail… Read More
2020-06-09 14:11
An application owner reported a sudden loss of access to database tables . They'd been reading from the sql tables - but logged off for awhile , logged back on and couldn't see the tables.&n&hell…Read More
2020-05-29 09:52
Question: I'm trying to run an INSERT statement , on a table with a large amount of columns and millions of rows. When executing the query statement , I'm getting an error message:
INSERT IN… Read More
2020-05-13 07:45
Question: I'd like to find all SQL Server Agent Jobs with a certain string within the Command section . There's been a change in reference to a database table, therefore I need to review all… Read More
2020-04-29 14:52
Question: What is the performance impact of enabling Transparent Data Encryption (TDE) on SQL Server database query performance? How can I measure the performance impact ?
Answer: There is a… Read More
2020-03-30 10:42
Question: How can I find a list of AD groups with a specific search filter on the name column. ? In this case I want to find all AD groups in the default OU group with a search s… Read More
2020-03-27 13:39
Powershell SQLVulnerabilityScan cmdlet is a great addition to the Powershell cmdlet libraries. Most of you know it's available through Microsoft Azure - using the interactive Advanced securi… Read More
2020-03-25 15:02
Creating a Credential Object is useful when you require to pass a username and password through Powershell commands.
You might see Credential used in various Powershell cmdlets - such… Read More
2020-03-23 15:17
Question: When attempting to expand the Catalogs in the linked server object defined, I received below message.How can I fix this problem?
The EXECUTE permission was denied on the… Read More
2020-03-02 17:04
If you're troubleshooting SQL Server query errors , you'll already know Extended Events are highly useful and very effective method to capture SQL Server errors.
To use the script you'll nee… Read More
2020-02-25 14:23
I've started working with Docker platform and managing SQL Server Containers. This list are some of the commands I execute most often when using Docker and Containers.
--Pull down a c… Read More
2020-02-17 16:43
Question: In the application error logs there is a message - The DELETE statement conflicted with the REFERENCE constraint FK_xxxxxxxxx
What is causing this error ? How can I troubleshoot?
A… Read More
2020-02-03 10:21
Question: I'm trying to send an email with multiple recipients in Powershell, and getting an exception error message:
Exception calling "Add" with "1" argument(s): "An invalid characte… Read More
2020-01-28 10:09
Question: I've installed the latest SQL Server Management Studio onto a server. I can find the ssms.exe through the directory - but I cannot start the SQL Server Management Studio through th… Read More
2020-01-24 07:59
Question: What is the limit on SQL Server Maximum Columns per table? I have a very wide Excel worksheet and want to migrate the data to a SQL Server table - where we can then manage th… Read More
2020-01-22 09:42
Question: I had a question from a developer who was troubleshooting an application to SQL Server connection via a DSN using ODBC. They wanted the SQL Server ODBC Driver names and platform (3… Read More
2020-01-17 09:33
There are some repeat questions I get from Backup or Operations staff - when they receive alerts for a BACKUP LOG failure.
There are multiple reasons it could be happening ranging from… Read More
2020-01-14 09:40
I created a database to host a data set for some sql performance testing. The default Recovery Model was set to FULL. The test data load insert was executed and it was expected to take… Read More
2020-01-13 15:43
Question: I'm trying to understand for educational purposes , what are the exact steps the SQL Server Backup is taking to progress through a SQL Server backup. Is there a way or method to tr… Read More
2020-01-08 08:51
Step 1 - Ensure MongoDB BI Connector is installed and configured . This will support the access to the MongoDB server. Notes on MongoDB BI Connector
Step 2 - Install MongoDB ODBC Driver for… Read More
2019-12-31 13:14
Question: How can I list shares from a server using Powershell and WMI ? I've been tasked with auditing servers and identifying shares which are not authrorized
Answer: The two m… Read More
2019-12-19 17:04
Question: Is it possible to use SQL Server Database Tuning Advisor through the command line?
Answer: On a server with SQL Server installed you can start the SQL Server Database Tuning Adviso… Read More
2019-12-11 16:19
Question: I'm building a Powershell form GUI utilising the System.Windows.Forms API.
I'm getting an error when passing text value back to be processed from a text box defined by… Read More
2019-12-04 08:14
Question: My head has officially exploded. I've been working on a Powershell script which includes creating a power GUI using the System.Windows.Forms.Form api.
Part of the GUI include… Read More
2019-11-25 17:04
Question: I'm in the process of creating a Powershell form . As part of the Powershell form there are various objects - such as textbox , labels, buttons.
As part of the programming lo… Read More
2019-11-05 14:04
Question : I'm attempting to use Powershell copy-item , to copy a file from a remote location onto a local drive. This is the code I'm executing :
Copy-Item \\myremotelocation\servers… Read More
2019-11-04 11:02
Question: Currently when I execute the sql code - SELECT CURRENT_USER , the return is my login name . I'd like to change the result to display dbo , as there are a number of application code… Read More
2019-10-21 15:53
Question: Don't ask , but I'm restoring some SQL Server 2005 databases onto a SQL Server 2017 - and there is some database compatibility behaviour I was not expecting. When the d… Read More
2019-10-17 10:49
Question: i'm working with Powershell System.Data.DataTable to get values from a SQL Server to generate a report for DBAs. the report is sent via email to the DBA team. As part of the email… Read More
2019-10-15 15:20
Microsoft released a SQL Assessment API module , available to Powershell . What is the SQL Assessment API? It's a new method to assess the SQL Server configurations against a set of SQ… Read More
2019-10-10 09:47
How can you find information about SQL database objects created? We're talking about CREATE DATABASE,CREATE INDEX,CREATE PROCEDURE,CREATE DATABASE.
There are common scenarios where you… Read More
2019-10-08 14:27
Question: There were some recent user security changes on a SQL Server login user account. Is there a way to grab this information - via the default trace? I haven't got a dedicated Extended… Read More
2019-10-02 16:18
Question: I've deployed Powershell script - which includes a process to dump out a resultset to a csv file. I'm having an issue where the file is saved with encoding type Unicode - whe… Read More
2019-09-30 15:13
Question: I'd like to execute a batch sql statement through SQL Server management studio multiple times and avoid using a loop statement or other similar constructs . I'm doing some testing… Read More
2019-09-27 16:30
Question: How can I get the Active Directory groups for a specific user using Powershell? I'm facing a problem where a user is getting a failed login to a SQL Server. This SQL Server has mul… Read More
2019-09-25 14:47
Question: How can I use Powershell to list out Windows users? Are there Powershell cmdlets which can report on Windows users ?
Answer: There are "out of the box" Powershell cmdlets which wil… Read More
2019-09-23 13:07
Question:A vendor sent me some detached MDF files from a SQL Server and asked me to attach. I attempted the ATTACH without a log file option and got this error
The database 'mydb' cann… Read More
2019-09-20 16:43
Question: How can I view all Powershell commands on a server? I've installed multiple Powershell modules for various processes , and have lost track of what's available. Also , I'd like to e… Read More
2019-09-19 09:48
A programmer asked me if he can include the a function to change the database status out of READ_ONLY , add some data and then put it back into READ_ONLY.
These are the basic commands… Read More
2019-09-18 14:05
Question: I'm trying to read the Microsoft SQL Server Extended Event Log File - xel file for the SQL Server Extended Events system_health extended event.
Just opening it up in Notepad… Read More
2019-09-17 12:13
Question: I have a JSON file loaded daily onto a drive . I need to set up a process to read the JSON file and translate into a SQL recordset. Eventually - it will be added to a SQL tab… Read More
2019-09-09 16:03
Question:I see this error in the SQL Server Error Logs . How can I troubleshoot this issue?
The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSS… Read More
2019-09-05 10:43
Question: I received an alert for a regular backup failure . A closer inspection of the sql server agent error logs displayed this error message:
Executed as user: myUser… Read More
2019-08-29 07:40
Question : I found this logged in the SQL Server Error Logs, during some Service Broker troubleshooting.
Service Broker needs to access the master key in the database 'MyDatabase'. Error cod… Read More
2019-08-27 12:44
Question: A server experienced a few problems after the installation of a SQL Server 2016 Cumulative Update. As part of the troubleshooting process, I'd like to rollback the cumulative updat… Read More
2019-08-21 15:41
Question: In daily scans of the SQL Server message logs there is an informational message Recovery is writing a checkpoint in database. What does this mean?
Answer: There is normally an adde… Read More
2019-08-05 14:49
Question: We had an unexpected application outage due to a SQL Login which had Enforce password expiration set . Unfortunately this was not being monitored, and led to a situation where the… Read More
2019-07-26 09:58
Question: I'm getting this Execution timeout error when executing a Powershell script using the $sql.ExecuteReader() method. How can I fix this issue?
Exception calli… Read More
2019-07-25 17:28
To get the total table count of all databases in either one or more SQL Server Instances use this powershell script.
This basic powershell script will give you the basic framework - wh… Read More
2019-07-24 11:47
Question: How can I iterate through a list of SQL Servers , extract the count of the databases , place into a powershell data table and get the database count sum for all the servers?
I'm ha… Read More
2019-07-23 10:52
Question: Do you have a powershell script allowing me to dynamically create a drive - pointing to a network share , and then start an unattended install of SQL Server patching? I'll afdapt t… Read More
2019-07-04 14:49
Often organisations have a SQL server security policy dictating the 'sa' login is disabled. There is some sound reasoning behind this policy. The primary reason is to… Read More
2019-05-22 11:11
Question: How can I DELETE using OPENQUERY? Normally for a SELECT from OPENQUERY , I'll do something like :
SELECT col1 ,col2 FROM OPENQUERY (MY_LINKED_SERVER,'SELECT col1… Read More
2019-05-15 09:57
Method 1 : Query sys.dm_os_windows
SELECT windows_release, windows_service_pack_level, windows_sku, os_language_version
FROM sys.dm_os_windows_info;
Method 2 : Use xp_cmdshell … Read More
2019-05-14 11:10
Question: What are the powershell commands to create to ASSIGN a drive letter and then create a MOUNT POINT?
Answer: These powershell command examples will assign a d… Read More
2019-04-26 06:32
Question: What is the difference between High Availability (HA) and Disaster Recovery (DR)? I often hear these terms being used interchangeably ,and would like to hear some specific de… Read More
2019-04-03 14:09
Question: I need a powershell script which reports on SQL Server logins with Sysadmin privileges. My aim is to automate the process - so if you could provide a script which provides the sql… Read More
2019-03-27 15:20
Question: I was looking through Programs installed on a server with SQL Server installed and noticed Visual Studio components installed.
As part of a SQL Server installation , which parts o… Read More
2019-03-25 16:58
Question: How can I find the SQL Server Instances on a server using Powershell ? Either the local server or remote SQL Servers.
The script should be based on reporting the name after t… Read More
2019-03-07 13:03
Question: I'm currently building a script to return all SQL Servers in the environment . As part of the recordset I need to return the aggregated free space for the drives on the server. So… Read More
2019-03-04 12:49
Question: The server team have added a bunch og memory to a server . How can I get the Total Physical Memory of a server using Powershell? The server is a remote serv… Read More
Subscribe to Sqlserver-dba.com
Get updates delivered right to your inbox!