Category Archives: Helpful Scripts

Use MSDB to Get Database Backup Size and Total Time For Each

— by Lori Brown

We recently started using a third party software to do our in-house SQL backups so that the backup files are stored in a redundant and safe place. To confirm that the software was indeed compressing backups as it stated it would, we wanted to see what each backup size actually was in SQL so that we could compare that to what the software was telling us.

SQL stores lots of handy backup information in msdb in the backupset and backupmediafamily tables.

https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/backupset-transact-sql

https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/backupmediafamily-transact-sql

Here is my query. I am only wanting the information from the last 24 hours so have filtered the start date by subtracting 1 day from today. I have also provided some commented out options in case someone needs them.

— database backup size and how long it took to do backup

SELECT bs.database_name AS DatabaseName

, CAST(bs.backup_size/1024.0/1024/1024 AS DECIMAL(10, 2)) AS BackupSizeGB

, CAST(bs.backup_size/1024.0/1024 AS DECIMAL(10, 2)) AS BackupSizeMB

–, CAST(bs.compressed_backup_size/1024.0/1024/1024 AS DECIMAL(10, 2)) AS CompressedSizeGB   

       –, CAST(bs.compressed_backup_size/1024.0/1024 AS DECIMAL(10, 2)) AS CompressedSizeMB

, bs.backup_start_date AS BackupStartDate

, bs.backup_finish_date AS BackupEndDate

, CAST(bs.backup_finish_date – bs.backup_start_date AS TIME) AS AmtTimeToBkup

, bmf.physical_device_name AS BackupDeviceName

FROM msdb.dbo.backupset bs JOIN msdb.dbo.backupmediafamily bmf

ON bs.media_set_id = bmf.media_set_id

WHERE

–bs.database_name = ‘MyDatabase’ and   — uncomment to filter by database name

bs.backup_start_date > DATEADD(dd, -1, GETDATE()) and

bs.type = ‘D’ — change to L for transaction logs

ORDER BY bs.database_name, bs.backup_start_date

And, here is the output.

Blog_20170726_1

It turned out that the software was indeed compressing all backups so that was a good thing.

There is a lot more info that can be pulled from msdb regarding backups. Have some fun and experiment with getting information that you need from there. Here are some links to some other backup related topics that we have blogged about already.

https://blog.sqlrx.com/2017/03/02/sql-server-backup-and-restore-primer/

https://blog.sqlrx.com/2013/04/23/backup-database-commands/

https://blog.sqlrx.com/2013/05/28/be-prepared-for-a-crisis/

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!

 

Get a List of Tables That Are Compressed

Find out if any of your tables are compressed in your database using the query below. Compressed tables can save space and make queries run faster.

— Compressed Tables
select distinct t.name AS CompressedTables
from sys.partitions p
inner join sys.tables t
on p.object_id = t.object_id
where p.data_compression > 0

If you don’t have any tables compressed but think you might want to compress some, you can check your data compression savings by running the stored procedure sp_estimate_data_compression_savings for your targeted table.

USE WideWorldImporters;
GO
EXEC sp_estimate_data_compression_savings ‘Sales’, ‘Invoices’, NULL, NULL, ‘ROW’ ;
GO

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-estimate-data-compression-savings-transact-sql

List Partitioned Tables And Other Info About Them

— By Lori Brown @SQLSupahStah

Here is a good way to find out if any of your tables are partitioned in your database using the query below. It is important to know so that you can find out how the tables are partitioned.

— Partitioned Tables

SELECT DISTINCT t.name AS ParitionedTables

FROM sys.partitions p

INNER JOIN sys.tables t

ON p.object_id = t.object_id

WHERE p.partition_number <> 1

If you have partitioned tables here is a good way to find out how it is partitioned up.

— Get partition info

SELECT SCHEMA_NAME(o.schema_id) + ‘.’ + OBJECT_NAME(i.object_id) AS [object]

, p.partition_number AS [p#]

, fg.name AS [filegroup]

, p.rows

, au.total_pages AS pages

, CASE boundary_value_on_right

WHEN 1 THEN ‘less than’

ELSE ‘less than or equal to’ END AS comparison

, rv.value

, CONVERT (VARCHAR(6), CONVERT (INT, SUBSTRING (au.first_page, 6, 1) +

SUBSTRING (au.first_page, 5, 1))) + ‘:’ + CONVERT (VARCHAR(20),

CONVERT (INT, SUBSTRING (au.first_page, 4, 1) +

SUBSTRING (au.first_page, 3, 1) + SUBSTRING (au.first_page, 2, 1) +

SUBSTRING (au.first_page, 1, 1))) AS first_page

FROM sys.partitions p

INNER JOIN sys.indexes i

ON p.object_id = i.object_id

AND p.index_id = i.index_id

INNER JOIN sys.objects o

ON p.object_id = o.object_id

INNER JOIN sys.system_internals_allocation_units au

ON p.partition_id = au.container_id

INNER JOIN sys.partition_schemes ps

ON ps.data_space_id = i.data_space_id

INNER JOIN sys.partition_functions f

ON f.function_id = ps.function_id

INNER JOIN sys.destination_data_spaces dds

ON dds.partition_scheme_id = ps.data_space_id

AND dds.destination_id = p.partition_number

INNER JOIN sys.filegroups fg

ON dds.data_space_id = fg.data_space_id

LEFT OUTER JOIN sys.partition_range_values rv

ON f.function_id = rv.function_id

AND p.partition_number = rv.boundary_id

WHERE i.index_id < 2

AND o.object_id = OBJECT_ID(‘dbo.SomeTableName’);

List Tables That May Be Over Indexed

— By Lori Brown

While not having enough indexes can be bad for query performance, having too many indexes can also be just as bad. Use the query below to get a list of tables in your database that has more than 10 indexes.

— Tables with large number of indexes

select t.name as TablesWithLargeNumInx, count(i.name) as CountIndexes

from sys.indexes i

inner join sys.tables t

on i.object_id = t.object_id

where i.index_id > 0

group by t.name

having count(i.name) > 10

If you suspect that you have too many indexes on your tables, you can also check the sys.dm_db_index_usage_stats dynamic management view to know if indexes on your heavily indexed tables are being used well. (Hint: seeks are good and scans are not so much)

select u.user_seeks, u.user_lookups, u.user_scans

from sys.dm_db_index_usage_stats u

inner join sys.indexes i

on u.object_id = i.object_id and u.index_id = i.index_id

WHERE u.object_id=object_id(‘dbo.SomeTableName’)

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-usage-stats-transact-sql

SQL Toolkit – Get List of Database Options and Features

–By Lori Brown

I am going to start up a thread with some queries that I consider to be important to DBA’s. As a consultant, many times we have to start managing SQL instances without any knowledge of why it has been configured in various ways. We usually try to work with our clients to figure things out but many times even they don’t know what has been done to the SQL configuration and database options or features. This can happen when a software vendor is allowed to set up the SQL instance or when a developer or DBA enables certain things so that work can be done. I have some scripts that I keep in my Toolkit and can use when I need to better understand the environment. At least armed with a little knowledge, I can ask questions and help my clients better understand things.

Here is a query that I use to find out what database options have been set as well as what advanced features maybe enabled. I tried to make the output display in plain English so that if I have to pass the info on to a client, they can understand it as well. Please note that at the end of each column, I have put a comment indicating if the feature is version specific. If you don’t have that version of SQL, simply comment out the lines that don’t apply to you. As new versions of SQL come out, I’ll try to test and keep things in the Toolkit up to date.

— get a list of interesting database settings

SELECT d.name AS DBName

,(CASE d.compatibility_level WHEN 80 THEN ‘SQL 2000’

WHEN 90 THEN ‘SQL 2005’

WHEN 100 THEN ‘SQL 2008’

WHEN 110 THEN ‘SQL 2012’

WHEN 120 THEN ‘SQL 2014’

WHEN 130 THEN ‘SQL 2016’ END) AS Compatibility

,d.user_access_desc AS UserAccessSetting

,(CASE d.is_auto_close_on WHEN 0 THEN ‘OFF’ ELSE ‘ON’ END) AS AutoCloseSetting

,(CASE d.is_auto_shrink_on WHEN 0 THEN ‘OFF’ ELSE ‘ON’ END) AS AutoShrinkSetting

,d.recovery_model_desc AS RecoveryModel

,d.page_verify_option_desc AS PageVerifySetting

,(CASE d.is_auto_create_stats_on WHEN 0 THEN ‘OFF’ ELSE ‘ON’ END) AS AutoCreateStats

,(CASE d.is_auto_update_stats_on WHEN 0 THEN ‘OFF’ ELSE ‘ON’ END) AS AutoUpdateStats

,(CASE d.is_cdc_enabled WHEN 0 THEN ‘OFF’ ELSE ‘ON’ END) AS ChangeDataCaptureSetting

,(CASE d.is_encrypted WHEN 0 THEN ‘OFF’ ELSE ‘ON’ END) AS TDESetting

— Always On info

,ag.name AS AGName

,rs.role_desc AS AGRole

,UPPER(ag.automated_backup_preference_desc) AS AGBkupPref

— Other interesting stuff

,d.containment_desc AS ContainmentSetting — SQL 2012 +

,d.delayed_durability_desc AS DelDurabilitySetting — SQL 2014 +

,(CASE d.is_remote_data_archive_enabled WHEN 0 THEN ‘Stretch-disabled’ ELSE ‘Stretch-disabled’ END) AS StretchDBSetting — SQL 2016 +

,(CASE d.is_query_store_on WHEN 0 THEN ‘Disabled’ ELSE ‘Enabled’ END) AS QueryStoreSetting — SQL 2016 +

FROM sys.databases d

LEFT JOIN sys.availability_databases_cluster adc ON d.group_database_id = adc.group_database_id

LEFT JOIN sys.availability_groups ag ON adc.group_id = ag.group_id

LEFT JOIN sys.dm_hadr_availability_replica_states rs ON ag.group_id = rs.group_id AND d.replica_id = rs.replica_id

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!

Moving Non-clustered Indexes To New Data File

-by Ginger Keys

It is not uncommon for database files to grow to the point you need more disk space. But what if it’s not possible or not in the budget to add more space? There are several creative options you have to move or shrink files, or delete unneeded items from the disk. One solution we recently implemented with a client was to move the indexes of a large database to another drive.

Create File/Filegroup

Only non-clustered indexes can be moved, and they must reside in a different filegroup from the primary filegroup. If you don’t already have a separate file and filegroup created, you must do this first. Make sure you have created a folder path on your system to the drive that will be holding your index files.

blog_20170209_1

Get Space Requirement for Indexes

Determine the non-clustered indexes you have in your database and their size by running this script to ensure you have enough space allocated on the target drive to house the index files.

USE AdventureWorks2016

GO

SELECT

OBJECT_NAME(i.OBJECT_ID) AS TableName,

i.name AS IndexName,

8 * SUM(a.used_pages) AS ‘Indexsize(KB)’

FROM sys.indexes AS i

JOIN sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id

JOIN sys.allocation_units AS a ON a.container_id = p.partition_id

JOIN sys.tables AS t ON i.object_id = t.object_id

WHERE i.type_desc = ‘NONCLUSTERED’

GROUP BY i.OBJECT_ID,i.index_id,i.name

ORDER BY OBJECT_NAME(i.OBJECT_ID),i.index_id

blog_20170209_2

Create and Execute Stored Procedure

The following script will create a stored procedure called MoveIndexToFileGroup that will move an index from one file group to another, and even supports the INCLUDE columns. Run this script against the master database to create the stored procedure: https://gist.github.com/dalenewman/6377911

In order to get a list of tables for your database, run the following script.

Use AdventureWorks2016 –use your database name

GO

SELECT * FROM INFORMATION_SCHEMA.TABLES

blog_20170209_3

After the above stored procedure is created and you have gotten a list of schemas/tables/indexes you want to move, execute the following

EXEC MoveIndexToFileGroup

@DBName = ‘AdventureWorks2016’, –your database name

@SchemaName = ‘HumanResources’, –schema name that defaults to dbo

@ObjectNameList = ‘Shift,  

Department,

                    EmployeeDepartmentHistory,

                    EmployeePayHistory,

                    Employee,

                    JobCandidate’, –a table or list of tables

@IndexName = NULL, –an index or NULL for all of them

@FileGroupName = ‘INDEX’; –the target file group

 

Your indexes will appear in the target destination you created.

blog_20170209_4

Conclusion

There are many useful reasons for moving your non-clustered indexes to a separate drive…in this case ours was simply to create more space for the drive holding the data files. Whatever your reason, the steps above should give you a simplistic way to achieve this.

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!

 

 

 

 

 

 

Find Currently Executing Jobs Running Longer Than 30 Day Average

–By Lori Brown   @SQLSupahStah

I needed to update some of our long running job monitoring code to improve it from the version that we have right now. I like this version because it uses msdb.dbo.syssessions (https://msdn.microsoft.com/en-us/library/ms175016.aspx) to validate that a job is actually running. I also wanted to know the percent difference between the current run duration versus an average duration per job from the past 30 days. I decided to place the calculated average into a table variable and then join on it to get my results. I also used the IIF function (https://msdn.microsoft.com/en-us/library/hh213574.aspx) to help me avoid a divide by zero error that comes up when the average duration equals 0.

I also learned about a function that I had never heard of before, msdb.dbo.agent_datetime. Interestingly, I cannot find documentation of this function from MSDN. However, this had apparently been around for a while. I don’t feel too bad about not knowing about this one since even super smart Pinal Dave had only recently found it too (https://blog.sqlauthority.com/2015/03/13/sql-server-interesting-function-agent_datetime/)

— currently executing jobs running longer than 30 day avg runtime

declare @startdt date

declare @enddt date

declare @JobsAvgDuration table (job_id uniqueidentifier, JobName varchar(250), ThirtyDayAvgDurMin int)

 

set @startdt = getdate()-30

set @enddt = getdate()

 

insert into @JobsAvgDuration

select j.job_id, j.name as JobName,

AVG((h.run_duration/10000*3600 + (h.run_duration/100)%100*60 + h.run_duration%100 + 31 ) / 60) as ThirtyDayAvgDurMin

from msdb.dbo.sysjobs j

join msdb.dbo.sysjobhistory h on (j.job_id = h.job_id)

where j.enabled = 1

and msdb.dbo.agent_datetime(h.run_date, 0) between @startdt and @enddt

group by j.job_id, j.name

order by j.name

 

select j.name as LongRunningJobName,

datediff(mi, a.start_execution_date, getdate()) as MinsRunning,

d.ThirtyDayAvgDurMin,

(datediff(mi, a.start_execution_date, getdate())-d.ThirtyDayAvgDurMin)*100/IIF(d.ThirtyDayAvgDurMin=0,1,d.ThirtyDayAvgDurMin) as PercentDiff

from msdb.dbo.sysjobs j

join msdb.dbo.sysjobactivity a on (j.job_id = a.job_id)

join @JobsAvgDuration d on (d.job_id = j.job_id)

where a.session_id in (select session_id from msdb.dbo.syssessions) — active session

and (a.start_execution_date IS NOT NULL and a.stop_execution_date IS NULL)

and datediff(mi, a.start_execution_date, getdate()) > d.ThirtyDayAvgDurMin

I had to force one of my monitoring jobs to run long so I could show you the results.

blog_20170119_1

All that is left is to put the results into a table or a report and I will be done. Let me know if you have a unique way of monitoring for jobs that run extraordinarily long. We would love to see other solutions!

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!

 

Synchronize Logins on AlwaysOn Replicas

–By Lori Brown   @SQLSupahStah

At one of my clients who has an AlwaysOn failover cluster, I noticed as I was checking up on things on the secondary replica that there were several fewer Windows and SQL logins on the secondary than on the primary. I really did not want to tediously compare the logins on each so instead came up with a script that can be run periodically to script out any logins that need to be synched up between the replicas. I actually found most of the script that I needed on the SQLSoldier’s blog (http://www.sqlsoldier.com/wp/) in a stored procedure that was intended to synch logins between mirrored partners. Since AlwaysOn is Mirroring on steroids I thought that it would work and it did. I did put my own touches on the proc by turning it into a standalone script that uses OPENROWSET to query the AG primary replica and also changed it from automatically executing the statements for missing logins to simply printing them into a script that can be applied as needed.

And now….here is what you came here for….

— Original logic from http://www.sqlsoldier.com/wp/sqlserver/transferring-logins-to-a-database-mirror

— Sync Logins to AlwaysOn Replicas

—     Inputs: @PartnerServer – Target Instance (InstName or Machine\NamedInst or Instname,port)

—     Output: All Statements to create logins with SID and Password for both Windows and SQL logins

—             Will also add logins to server roles

—     Person executing this must be sysadmin

—     Ad Hoc Distributed Queries must be enabled for this to work without setting up a linked server

/*

— Turn on Ad Hoc Distributed Queries so we don’t have to set up a linked server

sp_configure ‘show advanced options’, 1

GO

reconfigure with override

go

sp_configure ‘Ad Hoc Distributed Queries’, 1

GO

reconfigure with override

go

*/

 

Use master;

Go

Declare @MaxID int,

@CurrID int,

@PartnerServer sysname,

@SQL nvarchar(max),

@LoginName sysname,

@IsDisabled int,

@Type char(1),

@SID varbinary(85),

@SIDString nvarchar(100),

@PasswordHash varbinary(256),

@PasswordHashString nvarchar(300),

@RoleName sysname,

@Machine sysname,

@PermState nvarchar(60),

@PermName sysname,

@Class tinyint,

@MajorID int,

@ErrNumber int,

@ErrSeverity int,

@ErrState int,

@ErrProcedure sysname,

@ErrLine int,

@ErrMsg nvarchar(2048)

 

SET @PartnerServer = ‘InstName’

 

Declare @Logins Table (LoginID int identity(1, 1) not null primary key,

[Name] sysname not null,

[SID] varbinary(85) not null,

IsDisabled int not null,

[Type] char(1) not null,

PasswordHash varbinary(256) null)

 

Declare @Roles Table (RoleID int identity(1, 1) not null primary key,

RoleName sysname not null,

LoginName sysname not null)

 

Declare @Perms Table (PermID int identity(1, 1) not null primary key,

LoginName sysname not null,

PermState nvarchar(60) not null,

PermName sysname not null,

Class tinyint not null,

ClassDesc nvarchar(60) not null,

MajorID int not null,

SubLoginName sysname null,

SubEndPointName sysname null)

 

Set NoCount On;

 

If CharIndex(‘\’, @PartnerServer) > 0 — Check for Named Instance

Begin

Set @Machine = LEFT(@PartnerServer, CharIndex(‘\’, @PartnerServer) – 1);

End

Else If CharIndex(‘,’, @PartnerServer) > 0 — Check for Instance with port in connection string

Begin

Set @Machine = LEFT(@PartnerServer, CharIndex(‘,’, @PartnerServer) – 1);

End

Else

Begin

Set @Machine = @PartnerServer;

End

 

— Get all Windows logins from principal server

— using OPENROWSET and Windows Authentication

Set @SQL = ‘Select a.* From OPENROWSET (”SQLNCLI”, ”Server=’+@PartnerServer+‘;Trusted_Connection=yes;”, ”Select P.name, P.sid, P.is_disabled, P.type, L.password_hash

             From master.sys.server_principals P

             Left Join master.sys.sql_logins L On L.principal_id = P.principal_id

             Where P.type In (””U””, ””G””, ””S””)

             And P.name <> ””sa””

             And P.name Not Like ””##%””

             And CharIndex(””’ + @Machine + ‘\””, P.name) = 0”)as a;’

 

–print @SQL

 

Insert Into @Logins (Name, SID, IsDisabled, Type, PasswordHash)

Exec sp_executesql @SQL;

 

— Get all roles from principal server

— using OPENROWSET and Windows Authentication

Set @SQL = ‘Select a.* From OPENROWSET (”SQLNCLI”, ”Server=’+@PartnerServer+‘;Trusted_Connection=yes;”, ”Select RoleP.name as RoleName, LoginP.name as LoginName

             From master.sys.server_role_members RM

             Inner Join master.sys.server_principals RoleP

             On RoleP.principal_id = RM.role_principal_id

             Inner Join master.sys.server_principals LoginP

             On LoginP.principal_id = RM.member_principal_id

             Where LoginP.type In (””U””, ””G””, ””S””)

             And LoginP.name <> ””sa””

             And LoginP.name Not Like ””##%””

             And RoleP.type = ””R””

             And CharIndex(””’ + @Machine + ‘\””, LoginP.name) = 0”)as a;’

 

–print @SQL

 

Insert Into @Roles (RoleName, LoginName)

Exec sp_executesql @SQL;

 

— Get all explicitly granted permissions

— using OPENROWSET and Windows Authentication

Set @SQL = ‘Select a.* From OPENROWSET (”SQLNCLI”, ”Server=’+@PartnerServer+‘;Trusted_Connection=yes;”, ”Select P.name Collate database_default,

                    SP.state_desc, SP.permission_name, SP.class, SP.class_desc, SP.major_id,

                    SubP.name Collate database_default,

                    SubEP.name Collate database_default

             From master.sys.server_principals P

             Inner Join master.sys.server_permissions SP

             On SP.grantee_principal_id = P.principal_id

             Left Join master.sys.server_principals SubP

             On SubP.principal_id = SP.major_id And SP.class = 101

             Left Join master.sys.endpoints SubEP

             On SubEP.endpoint_id = SP.major_id And SP.class = 105

             Where P.type In (””U””, ””G””, ””S””)

             And P.name <> ””sa””

             And P.name Not Like ””##%””

             And CharIndex(””’ + @Machine + ‘\””, P.name) = 0”)as a;’

 

–print @SQL

 

Insert Into @Perms (LoginName, PermState, PermName, Class, ClassDesc, MajorID, SubLoginName, SubEndPointName)

Exec sp_executesql @SQL;

 

Select @MaxID = Max(LoginID), @CurrID = 1

From @Logins;

 

While @CurrID <= @MaxID

Begin

Select @LoginName = Name,

@IsDisabled = IsDisabled,

@Type = [Type],

@SID = [SID],

@PasswordHash = PasswordHash

From @Logins

Where LoginID = @CurrID;

 

If Not Exists (Select 1 From sys.server_principals

Where name = @LoginName)

Begin

Set @SQL = ‘Create Login ‘ + quotename(@LoginName)

If @Type In (‘U’, ‘G’)

Begin

Set @SQL = @SQL + ‘ From Windows;’

End

Else

Begin

Set @PasswordHashString = ‘0x’ +

Cast( As XML).value(‘xs:hexBinary(sql:variable(“@PasswordHash”))’, ‘nvarchar(300)’);

 

Set @SQL = @SQL + ‘ With Password = ‘ + @PasswordHashString + ‘ HASHED, ‘;

 

Set @SIDString = ‘0x’ +

Cast( As XML).value(‘xs:hexBinary(sql:variable(“@SID”))’, ‘nvarchar(100)’);

 

Set @SQL = @SQL + ‘SID = ‘ + @SIDString + ‘;’;

End

 

Print @SQL;

 

If @IsDisabled = 1

Begin

Set @SQL = ‘Alter Login ‘ + quotename(@LoginName) + ‘ Disable;’

Print @SQL;

End

End

Set @CurrID = @CurrID + 1;

End

 

Select @MaxID = Max(RoleID), @CurrID = 1

From @Roles;

 

While @CurrID <= @MaxID

Begin

Select @LoginName = LoginName,

@RoleName = RoleName

From @Roles

Where RoleID = @CurrID;

 

If Not Exists (Select 1 From sys.server_role_members RM

Inner Join sys.server_principals RoleP

On RoleP.principal_id = RM.role_principal_id

Inner Join sys.server_principals LoginP

On LoginP.principal_id = RM.member_principal_id

Where LoginP.type In (‘U’, ‘G’, ‘S’)

And RoleP.type = ‘R’

And RoleP.name = @RoleName

And LoginP.name = @LoginName)

Begin

Print ‘Exec sp_addsrvrolemember @rolename = ”’ + @RoleName + ”’, @loginame = ”’ + @LoginName + ”’;’;

End

Set @CurrID = @CurrID + 1;

End

 

Select @MaxID = Max(PermID), @CurrID = 1

From @Perms;

 

While @CurrID <= @MaxID

Begin

Select @PermState = PermState,

@PermName = PermName,

@Class = Class,

@LoginName = LoginName,

@MajorID = MajorID,

@SQL = PermState + space(1) + PermName + SPACE(1) +

Case Class When 101 Then ‘On Login::’ + QUOTENAME(SubLoginName)

When 105 Then ‘On ‘ + ClassDesc + ‘::’ + QUOTENAME(SubEndPointName)

Else End +

‘ To ‘ + QUOTENAME(LoginName) + ‘;’

From @Perms

Where PermID = @CurrID;

 

If Not Exists (Select 1 From sys.server_principals P

Inner Join sys.server_permissions SP On SP.grantee_principal_id = P.principal_id

Where SP.state_desc = @PermState

And SP.permission_name = @PermName

And SP.class = @Class

And P.name = @LoginName

And SP.major_id = @MajorID)

Begin

Print @SQL;

End

Set @CurrID = @CurrID + 1;

End

 

Set NoCount Off;

GO

 

/*

— Turn off Ad Hoc Distributed Queries

sp_configure ‘Ad Hoc Distributed Queries’, 0

GO

reconfigure with override

go

*/

On my test AG replicas, you can see that there are two users that are not present on the secondary. I need to add them so that if a failover occurs then there will not be any login failures.

blog_20170105_1

Using the script above on SQLSRVTST2, I get the following output and can use that to create my users with their correct permissions. I had to set the @PartnerServer variable to SQLSRVTST1 in the script and enable Ad Hoc Distributed Queries.

blog_20170105_2

……

blog_20170105_3

blog_20170105_4

The nice thing is that the password and SID’s are also scripted for you. There is no need to install sp_hexadecimal and sp_help_revlogin in master. If you have AG replicas with a lot of logins, this can be a real timesaver for you. Hope you enjoy and if you make any improvements, please share them with us!

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!

 

Deadlock Info From system_health Information

— by Lori Brown @SQLSupahStah

Periodically, all DBA’s are going to run into code that fails due to a deadlock. Deadlocks happen when two or more SPIDs\sessions try to access a resource that is locked by each other. In short, something has to give so one is usually chosen by SQL to be the victim meaning one is the loser. Deadlocks can be avoided by designing your databases well and making queries as quick and lightweight as possible. The faster data is retrieved or locked and unlocked the better.

But, how do you figure out what is causing deadlocks if you suddenly run into them? An easy way is to use the information that is already captured in the default system_health session information that is already being gathered by SQL.

Here are queries to retrieve this information from SQL 2008 R2 and SQL 2012 and up.

— get deadlock graphs in SQL 2008

SELECT CAST(event_data.value(‘(event/data/value)[1]’,

                               ‘varchar(max)’) AS XML) AS DeadlockGraph

FROM   ( SELECT   XEvent.query(‘.’) AS event_data

FROM     (   — Cast the target_data to XML

SELECT   CAST(target_data AS XML) AS TargetData

FROM     sys.dm_xe_session_targets st

JOIN sys.dm_xe_sessions s

ON s.address = st.event_session_address

WHERE     name = ‘system_health’

AND target_name = ‘ring_buffer’

) AS Data — Split out the Event Nodes

CROSS APPLY TargetData.nodes(‘RingBufferTarget/

                                     event[@name=”xml_deadlock_report”]’)

AS XEventData ( XEvent )

) AS tab ( event_data );

 

 

— get deadlock graphs in SQL 2012 and up

SELECT XEvent.query(‘(event/data/value/deadlock)[1]’) AS DeadlockGraph

FROM   ( SELECT   XEvent.query(‘.’) AS XEvent

FROM     ( SELECT   CAST(target_data AS XML) AS TargetData

FROM     sys.dm_xe_session_targets st

JOIN sys.dm_xe_sessions s

ON s.address = st.event_session_address

WHERE     s.name = ‘system_health’

AND st.target_name = ‘ring_buffer’

) AS Data

CROSS APPLY TargetData.nodes

(‘RingBufferTarget/event[@name=”xml_deadlock_report”]’)

AS XEventData ( XEvent )

) AS src;

Queries originally from Jonathan Kehayias (One super smart SQL guy!) https://www.simple-talk.com/author/jonathan-kehayias/

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!

Load Windows Event Log Errors into a SQL Table

–By Lori Brown @SQLSupahStah

Part of being a proactive DBA is to get information on what is going on at the server level as well as at the SQL level. We know that the Windows Event logs are around but I find that not many DBA’s check them regularly. You can make this easier by using a vbscript to load errors and warnings from the Event Logs into a SQL table.

Review or scrape the Event Logs at a minimum to find out if any Windows or hardware related errors or warnings are being written. Most hardware vendors write warnings to the Event Logs when they anticipate an error is going to occur, so this gives you the opportunity to be proactive and correct the problem during a scheduled down time, rather than having a mid-day emergency.

Build a SQL table to hold Event Log info:

— table for the Windows Event Log

CREATE TABLE [dbo].[WinEventLog](

[ID] INT IDENTITY(1,1) NOT NULL,

[ComputerName] VARCHAR(128) NULL,

[EventCode] INT NULL,

[RecordNumber] INT NULL,

[SourceName] VARCHAR(128) NULL,

[EventType] VARCHAR(50) NULL,

[WrittenDate] DATETIME NULL,

[UserName] VARCHAR(128) NULL,

[Message] VARCHAR(MAX) NULL

) ON [PRIMARY]

GO

Use the vbscipt code below by saving it into a notepad document with the extension .vbs. Make sure to modify connection strings to connect to your instance and the correct database. And, set the number of days you want to check as well. I have it defaulted to 2 days but you can set it to whatever makes sense for you.

************************

‘VBScript

‘Purpose of script to query Application log for errors

dim strConnect, strComputer, strMessage, RoleStr

dim Category, Computer_Name, Event_Code, Message, Record_Number, Source_Name, Time_Written, Event_Type, User

dim dtmStartDate, dtmEndDate, DateToCheck

dim dtTimeWritten

 

‘Connection string for SQL Server database.

strConnect = “DRIVER=SQL Server;” _

& “Trusted_Connection=Yes;” _

& “DATABASE=<<Database Name>>;” _

& “SERVER=<<SQL Instance Name>>”

 

‘Use this string if SQL Server driver does not work

‘strConnect = “Provider=SQLOLEDB;” _

‘& “Data Source=<<SQL Instance Name>>;” _

‘& “Initial Catalog=<<Database Name>>;” _

‘& “Integrated Security=SSPI;”

 

‘ Connect to database.

Set adoConnection = CreateObject(“ADODB.Connection”)

adoConnection.ConnectionString = strConnect

adoConnection.Open

 

Set dtmStartDate = CreateObject(“WbemScripting.SWbemDateTime”)

Set dtmEndDate = CreateObject(“WbemScripting.SWbemDateTime”)

‘ Set number of days to scrape here

DateToCheck = Date – 2

dtmEndDate.SetVarDate Date, True

dtmStartDate.SetVarDate DateToCheck, True

 

strComputer = “.”

Set objWMIService = GetObject(“winmgmts:” _

& “{impersonationLevel=impersonate}!\\” & strComputer & “\root\cimv2”)

Set colLoggedEvents = objWMIService.ExecQuery _

(“Select * from Win32_NTLogEvent Where Logfile = ‘Application’ and (” & _

“TimeWritten >= ‘” & dtmStartDate & _

“‘ and TimeWritten < ‘” & dtmEndDate & _

“‘) and (EventType = ‘1’ or EventType = ‘2’)”)

 

For Each objEvent in colLoggedEvents

Category = objEvent.Category

Computer_Name = objEvent.ComputerName

Event_Code = objEvent.EventCode

Message = objEvent.Message

Record_Number = objEvent.RecordNumber

Source_Name = objEvent.SourceName

Time_Written = objEvent.TimeWritten

Event_Type = objEvent.type

User = objEvent.User

 

‘Fix single quotes in the message string

strSQ = Chr(39)

strDQ = Chr(34)

if len(Message) > 0 then

strMessage = Replace(Message, strSQ, strDQ)

else

strMessage = ” “

end if

 

dtTimeWritten = WMIDateStringToDate(Time_Written)

 

RoleStr = “SET NOCOUNT ON INSERT INTO WinEventLog (ComputerName, EventCode, RecordNumber,” _

& “SourceName, EventType, WrittenDate, UserName, Message) VALUES” _

& “(‘” & Computer_Name & “‘, ‘” & CLng(Event_Code) & “‘, ‘” & CLng(Record_Number) _

& “‘, ‘” & Source_Name & “‘, ‘” & Event_Type & “‘, ‘” & dtTimeWritten _

& “‘, ‘” & User & “‘, ‘” & strMessage & “‘)”

adoConnection.Execute RoleStr

Next

 

adoConnection.Close

 

Function WMIDateStringToDate(Time_Written)

WMIDateStringToDate = CDate(Mid(Time_Written, 5, 2) & “/” & _

Mid(Time_Written, 7, 2) & “/” & Left(Time_Written, 4) _

& ” ” & Mid (Time_Written, 9, 2) & “:” & _

Mid(Time_Written, 11, 2) & “:” & Mid(Time_Written, _

13, 2))

End Function

************************

To run the vbscript, you can double click it to run it manually or you can set up a SQL job that uses CMDEXEC to call the script.

@subsystem = N’CmdExec’,

@command = N’cscript E:\SQLRX\ScrapeWindowsEventLog.vbs’

Hopefully this will help make you more proactive and knowledgeable about your servers in general.

Enjoy!

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!