-->
SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Mission: SQL Homeostasis

Drupal is a Senior SQL Server consultant with over 20 years of experience in engineering innovative SQL Server solutions for high growth organizations. A truly SQL Server specialist and IT generalist, Drupal is a certified MCDBA, Oracle OCP-DBA and IBM WebSphere Administrator. He is also ITIL V3 certified at the Foundation and Intermediate levels as well as PMP.

Powershell One Liners: Get current cpu utilization on all your SQL Servers

# Assuming you have list of servers in servers.txt, each server name in its own line

$ComputerNames = get-content servers.txt


# Method 1 - Using the Get-Counter cmdlet
# Notice that here you can also easily add the interval and max sample…

Read more

0 comments, 256 reads

Posted in Mission: SQL Homeostasis on 8 October 2018

Powershell One Liners: Get status of SQL Instances on All SQL Servers


# Assuming you have list of servers in servers.txt, each sever name in its own line

$ComputerNames = get-content servers.txt

Get-WmiObject -Query "select * from win32_service where PathName like '%%sqlservr.exe%%'"  -ComputerName $ComputerNames | ft -Property PSComputerName, @

Read more

0 comments, 143 reads

Posted in Mission: SQL Homeostasis on 8 October 2018

SQL Server monitoring with built-in features

Do you think you need to use commercial tools like SQL Diagnostic Manager, SQL Sentry, Spotlight etc.. ?  Or do you think the built-in features are enough? There are complelling arguments in favor of each.

In this blog I hope to touch on every built-in feature to gather the performance…

Read more

0 comments, 224 reads

Posted in Mission: SQL Homeostasis on 7 October 2018

Check if any database has auditing configured


Here is a DMV script to check whether a or any database has the auditing configured and running, along with few other useful attributes if it does.


Few things to keep in mind:

  • Auditing is available from SQL version 2008 and up....
  • Until very recently (SQL version 2016 and up),…

Read more

0 comments, 107 reads

Posted in Mission: SQL Homeostasis on 18 September 2018

Find Orphaned databases in SQL Server

Do you ever wonder if there are any databases in your environment that may just be there but not being used?

I needed to make a comprehensive list of such databases in order to clean up old databases and prep old/existing environment and migrate and consolidate them into a…

Read more

0 comments, 117 reads

Posted in Mission: SQL Homeostasis on 18 September 2018

DMV To List Foreign Keys With No Index

I was entrusted with a new database for a critical application that was having general performance issue and the CPU usage constantly over 70%.

It turned out that I did not have to sweat much to quickly and significantly improve the database performance and also bring down CPU % way…

Read more

6 comments, 1,005 reads

Posted in Mission: SQL Homeostasis on 10 May 2018

How To Audit Data Changes In SQL Server?


Scenario:

I was at a new client, with their previous and only DBA / DEVELOPER/ MASTER OF ALL of 8 years all suddenly gone with no documentation.

Their business critical application was having data integrity issues. Some data were updated incorrectly while some others were not updated at all, showing…

Read more

0 comments, 2,018 reads

Posted in Mission: SQL Homeostasis on 10 May 2018

How to get alerted anytime SQL Service is restarted?


You may and should have monitoring in place to monitor state of your servers, services, jobs, critical and not critical errors etc.

Here I just wanted to share a quick script to create a SQL Server agent job to alert you whenever SQL Server service is started/restarted.

If you have…

Read more

0 comments, 575 reads

Posted in Mission: SQL Homeostasis on 27 April 2018

How to find out whether your database is transactional, analytical or hybrid?


If you are a DBA, especially Infrastructure DBA, Production DBA or Operations DBA then you don't necessarily always know what type of application load is being handled by your SQL Servers.  In some cases you don't even need to know to do your job.

But if you are a performance…

Read more

0 comments, 327 reads

Posted in Mission: SQL Homeostasis on 26 April 2018

Gather Always On Configuration using a DMV query


If you were like me, you would know your AG configuration by heart. However, what if you get busy with increasing responsibilities in other areas, projects etc.? Therefore, you do not just want to rely on your memory. On the other hand, maybe you want to simply collect snapshot of…

Read more

0 comments, 303 reads

Posted in Mission: SQL Homeostasis on 26 April 2018

Check health and status of Availability Group using DMVs






-- Health and status of WSFC cluster. These two queries work only if the WSFC has quorum
SELECT * FROM sys.dm_hadr_cluster
SELECT * FROM sys.dm_hadr_cluster_members


-- Health of the AGs
SELECT ag.name agname, ags.* FROM sys.dm_hadr_availability_group_states ags INNER JOIN sys

Read more

0 comments, 505 reads

Posted in Mission: SQL Homeostasis on 26 April 2018

Get SQL server database size, location and volume info using DMVs




SELECT * FROM sys.master_files AS f 
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) v; 

Read more

0 comments, 275 reads

Posted in Mission: SQL Homeostasis on 23 April 2018

Easy to use WMI commands to query windows system



WMI has been around for a while and it has grown significantly over the years. With Microsoft's focus on powershell, WMI is maybe getting less visible in the public eyes.

This is not to say I am not a fan of powershell. But it can be bit daunting to get…

Read more

0 comments, 941 reads

Posted in Mission: SQL Homeostasis on 20 April 2018

SQL query to shrink all log files on sql instance



USE master

exec sp_MSforeachdb '

use [?]
 print ''?''
 print cast(databasepropertyex(''?'', ''Updateability'') as varchar(200))

 if databasepropertyex(''?'', ''Updateability'') = ''READ_WRITE ''

   dbcc shrinkfile(2,1)

'





Read more

0 comments, 253 reads

Posted in Mission: SQL Homeostasis on 20 April 2018

T-SQL Developer Interview Questions with Answers




What SQL command do you use to pull and filter data from a table ?  


SELECT [Column List] FROM [Table Name] WHERE [Condition = TRUE]
OR
SELECT * FROM [Table Name] WHERE [Condition = TRUE]


How do you update data (what command do  you use)


Syntax to update data without…

Read more

0 comments, 321 reads

Posted in Mission: SQL Homeostasis on 20 April 2018


GUID or Integer Primary Key?


If you are designing a new data model or maybe you are looking to improve an existing one.  One of the key decision you would be making is what should be the primary key in each table.

Now first rule or best practice is that…

Read more

0 comments, 0 reads

Posted in Mission: SQL Homeostasis on 20 April 2018

SQL server database size, location and volume info

For one of my clients, I needed to know and document not only database names, size and locations but also disk volume information as well.

Now there is an easier way to get the same info using a DMV.

https://sqlpal.blogspot.com/2018/04/get-sql-server-database-size-location.html


Here is another way to retrieve same information using OLE…

Read more

0 comments, 328 reads

Posted in Mission: SQL Homeostasis on 19 April 2018

Generate SQL script to extract user permissions from a SQL database

When you restore a database, it will also restore all permissions along with it. But what if you are restoring over an existing database and it has different sets of permissions and you would like to preserve those after the restore?

For example, due to security policy of your organization…

Read more

0 comments, 187 reads

Posted in Mission: SQL Homeostasis on 19 April 2018

How to check whether and when sql index statistics were updated last time?


I was working for a client on their very critical and heavily used database.  The application was an E-commerce website with a ratio of 1 write to 400 reads. 

The application would experience sporadic performance issues.  The expected response time was in sub-second and this expectation was met in general.…

Read more

0 comments, 150 reads

Posted in Mission: SQL Homeostasis on 19 April 2018

How to start SQL Server job using TSQL and wait for it to complete?



If you ever had a need to start a SQL job using sp_start_job stored procedure in msdb then you know once the job is started successfully, it returns the control back to the user immediately and does not wait for the job to complete.

But what if you want your…

Read more

0 comments, 414 reads

Posted in Mission: SQL Homeostasis on 19 April 2018