Steps to monitor the SQL Server Services
I have a need to monitor the SQL Server Services in my environment. Are there any programmatic options to do so? Check out this tip to learn more.
Biography Jugal Shah is the founder of SQLDBPool.com, where he is writes articles on SQL Server Administration and Development. Jugal has 8+ years of extensive experience in SQL Server Database administration and development. Jugal has worked on SQL Server 2000, 2005, 2008 and 2008 R2. In his current assignment, Jugal is managing large critical databases. His expertise is in database security, performance tuning and implementing high availability solutions. He can be contacted at jugal.shah@sqldbpool.com. He has been recognized as a SQL Server MVP since 2010.
- MSSQLTips Awards: Trendsetter (25+ tips) - 2013
Steps to Move SQL Server Log Shipping Secondary Database Files
With SQL Server is it possible to move the secondary database involved with Log Shipping to a different drive without disturbing the Log Shipping configuration? If so, what are the steps to accomplish this task? Check out this tip to learn more.
Steps to add Log Shipping monitor into an existing SQL Server
I have a requirement to add the Log Shipping Monitor for an existing installation. I have heard you can only complete this by rebuilding the Log Shipping infrastructure. Is that true? Are there any other options? In this tip I will explain how we can a
Prevent Truncation of SQL Server Management Studio Results
While working with the Results to Text option in SSMS, you may come across a situation where the output from dynamically generated data is truncated. In this article I will guide you on how to fix this issue and print all the text for the Results to Text
Steps to Check the Host Name for a Clustered SQL Server Instance
While troubleshooting a SQL Server cluster failover issue, it is essential to know the time needed for the cluster failover and the node name where SQL Server was running before the failover occurred. In this tip, I will show you the different options to
PowerShell script to find files that are consuming the most disk space
As you know, SQL Server databases and backup files can take up a lot of disk space. When disk is running low and you need to troubleshoot disk space issues, the first thing to do is to find large files that are consuming disk space. In this article I wi
SQL Server Management Studio Error 916
When connecting to SQL Server using Management Studio (SSMS), with the limited permissions, you do not see any user databases or receive Error 916 when expanding the database list from Object Explorer. The error message is The server principal Login Nam
Using a PowerShell Script to delete old files for SQL Server
Many clients are using custom stored procedures or third party tools to backup databases in production environments instead of using database maintenance plans. One of the things that you need to do is to maintain the number of backup files that exist on
Different ways to make a table read only in a SQL Server database
In some cases there may be a need to make a SQL Server table read only. There are several different options for doing this and in this tip we cover various ways that you can make a table read only in a SQL Server database.
Setting the PowerShell Execution Policy
Recently I moved PowerShell script files to a production environment and when executing it from the command prompt, I got this error: File cannot be loaded because the execution of scripts is disabled on this system. Please see “get-help about_signing” f
SQL Server Login Failure Error 18456, Severity 14, State 10
Recently I experienced a situation where a SQL Server login failed with this error message: Error: 18456, Severity: 14, State: 10. Login failed for user 'SQL_Login'. Reason: Password validation failed with an infrastructure error. Check for previous e
PowerShell to Register All SQL Instances to Central Management Server
Managing multiple SQL Servers has its challenges. One of the big challenges is having to collect data from each of the servers you manage and figure out which servers need attention. SQL Server has introduced a new feature called Central Management reposi
How to use RunAs command for SSMS if option does not exist
As a best practice in the industry, a DBA often has two logins that are used to access SQL Server; one is their normal Windows login and the other is an admin level login account which has sysAdmin rights on the SQL Server boxes. In addition most of the t
Steps to Drop an Orphan SQL Server User when it owns a Schema or Role
Recently I had an issue while dropping an orphaned user for an obsolete login. I was unable to drop the user and it failed with the below error messages: Msg 15421, Level 16, State 1, Line 1 The database principal owns a database role and cannot be dropp
Different ways to monitor Log Shipping for SQL Server databases
Log shipping has been an option for creating a failover server for SQL Server for quite some time. In this tip, we look at different ways that you can monitor the status of your log shipped databases.
Steps to change the server name for a SQL Server machine
In this tip we look at the steps within SQL Server you need to follow if you change the physical server name for a standalone SQL Server.
Standardize SQL Server Installations with Configuration Files
If you have a requirement to install multiple SQL Server instances with the same settings, you most likely want to do it without following the numerous manual installation steps. The below tip will guide you through how to install a SQL Server instance wi
Create a 32-bit DSN on 64-bit machine for SQL Server
During the install of an application on a 64-bit Windows machine, the application was unable to list the ODBC System DSN because it had been setup as a 64-bit DSN and the application needed a 32-bit system DSN for connectivity to SQL Server. In this tip,
Configuring SQL Server to Use Multiple Ports
In this tip we walk through how to configure a SQL instance to listen on multiple TCP/IP ports.
Script to Get Available and Free Disk Space for SQL Server
Often we face the situation where we need to check the total disk space and available disk space for both physical and LUN/Mount drives. The extended stored procedure XP_FixedDrives is unable to help us in this scenario, so we have to log into the machine
Restoring a SQL Server database that uses Change Data Capture
When restoring a database that uses Change Data Capture (CDC), restoring a backup works differently depending on where the database is restored. In this tip we take a look at different scenarios when restoring a database when CDC is enabled.
SQL Server Central Management Servers System Tables
I have SQL Server Central Management Servers setup in my environment. How can I get a list of the registered servers and their associated properties? Are there any queries I can issue? Check out this tip to learn more.
Customize SQL Server Management Studio Query Window
Learn how to customize SSMS query window for standard comment block and frequently used SQL Server commands.
Turn Off SQL Server Management Studio Auto Recovery Feature
By default the Auto Recovery feature is enabled for SSMS and because of this when opening SSMS it may hang or become unresponsive for some time if the previous session was closed unexpectedly. There is not a way to turn this feature off from within SSMS,