-->
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.

Archives: April 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, 658 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, 366 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, 331 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, 665 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, 303 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, 1,435 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, 284 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, 354 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, 384 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, 232 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, 184 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, 462 reads

Posted in Mission: SQL Homeostasis on 19 April 2018