So, tomorrow marks one year since I started blogging here on SQL Server 365 and what a year it has been. I can honestly say that I have taken a great deal away from this experience which has been so rewarding, the only disappointment I have is that I didn't start blogging earlier.
I set out with the goal of blogging something I had learned about SQL Server every day for a year which I soon found out was a little too optimistic. I do however still feel very satisfied with what I have achieved and happy that people have taken something away from my posts so far. After all the main reason for me starting the blog was to give something back to the fantastic SQL Community which has given me so much over the years. You really are fantastic!
So here you go the SQL Server 365 First Year In Numbers!
Site Hits - 7,974
Total Posts - 62
Total Comments - 39
Top 5 Posts
Don't be a Balloon - 1676 Views
SQL Server Error Logs - 907 Views
Determining Active Directory Group Membership - 242 Views
Database Mirroring Error - 151 Views
A Script A Day - Day 16 - Database Restore - 99 Views
Top 5 Countries
United States - 3587 Views
United Kingdom - 1461 Views
India - 324 Views
Russia - 235 Views
Germany - 227 Views
Top 5 Browsers
Internet Explorer - 3051 Views
Chrome - 1949 Views
Firefox - 1939 Views
Safari - 381 Views
Opera - 244 Views
Top 5 OS's
Windows - 6411 Views
Macintosh - 413 Views
Linux - 232 Views
iPhone - 232 Views
Android - 191 Views
I have some great ideas for next year and among other posts plan to do a series on Indexing, share a maintenance routine I've been working on and provide a plethora of scripts on Mirroring, Snapshots to name a few. I will even be delving into the world of Powershell and how it helps ease the pain of a fail-over in the event of invoking DR!
If there is anything you would like me to to do a post on or would like to do a guest post then please get in touch and we can arrange it! ckwmcgowan@gmail.com or @ckwmcgowan
Happy New Year!
Chris
Chris McGowan's SQL Server perspicuity, for all things SQL related and some, no doubt, not!
Monday, 31 December 2012
Wednesday, 19 December 2012
Active Directory Group Membership and Powershell
I’ve done my fair share of work with Active Directory
over the years and LDAP has helped immensely, it did however let me down
recently. Well when I say LDAP let me
down it was probably my knowledge of LDAP (or lack thereof) that let me down.
For an audit I needed to list all groups a random set of
users where members of. After struggling
for an hour or so I decided to admit defeat with LDAP and instead turn to a
trusty friend of mine Powershell, and It didn’t let me down!
I downloaded ActiveRoles Management Shell for Active
Directory from Quest (see the link below);
Launching this I ran Get-Command to display a list of all
available cmdlets;
From the extensive list of cmdlets I used get-qadmemberof
and piped this to the out-file cmdlet to write the results to c:\sql\username.txt
get-qadmemberof ‘domain\username' | out-file c:\sql\username.txt
Voila! With one simple line of code for each of the
random users I was able to get the results, sweet.
Enjoy!
Chris
Friday, 30 November 2012
Tables without a Primary Key
As well as
creating the script for yesterday’s post on finding Tables without a Clustered
Index I tweaked the script slightly to get all tables without a primary
key. A Primary Key constraint enforces
uniqueness of a record in a table. I am extremely anal with this particular
standard that I try to enforce, to say I don’t take kindly to folks that don’t
create a Primary Key on their tables is an understatement.
There are
a few things that will need changing;
·
@notify_email_operator_name=N'Chris'
·
SET
@EmailProfile = ''Chris''
·
SET
@EmailRecipient = ''Chris@SQLServer365.co.uk''
You can
also obviously change the schedule accordingly to suit your needs.
Again I
would be interested to know the results on your servers, leave me a comment
with the email subject text from the resulting email.
/*
-----------------------------------------------------------------
Find tables without a Primary Key
-----------------------------------------------------------------
For more SQL resources, check out
SQLServer365.blogspot.co.uk
-----------------------------------------------------------------
You may alter this code for your own
purposes.
You may republish altered code as long as
you give due credit.
You
must obtain prior permission before blogging this code.
THIS CODE AND INFORMATION ARE PROVIDED
"AS IS"
-----------------------------------------------------------------
*/
-- Set database
context
USE [msdb]
GO
IF EXISTS (SELECT job_id FROM
msdb.dbo.sysjobs_view WHERE
name = N'Alert -
TablesWithoutPrimaryKey')
EXEC msdb.dbo.sp_delete_job @job_name
= N'Alert -
TablesWithoutPrimaryKey',
@delete_unused_schedule=1
GO
BEGIN TRANSACTION
DECLARE
@ReturnCode INT
SELECT
@ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND
category_class=1)
BEGIN
EXEC
@ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <>
0) GOTO
QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC
@ReturnCode =
msdb.dbo.sp_add_job @job_name=N'Alert -
TablesWithoutPrimaryKey',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'This job runs every Monday at 07:09 and will email an
operator if there are any tables without a Primary key in any user database.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa',
@notify_email_operator_name=N'Chris', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <>
0) GOTO
QuitWithRollback
/******
Object: Step [Find tables without a
Primary Key] Script Date: 11/29/2012
13:31:29 ******/
EXEC
@ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Find tables without a Primary Key',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'-- Set database context
USE master;
GO
-- Declare
variables
DECLARE
@EmailProfile VARCHAR(255)
DECLARE
@EmailRecipient VARCHAR(255)
DECLARE
@EmailSubject VARCHAR(255)
DECLARE
@IndexUsageStats INT
DECLARE
@TableCount INT
DECLARE
@DatabaseCount INT
-- Set variables
SET @EmailProfile
= ''Chris''
SET
@EmailRecipient = ''Chris.McGowan@SQLServer365.co.uk''
-- Drop temporary
table if exists
IF
OBJECT_ID(''tempDB.dbo.#NoPrimaryKey'') IS NOT NULL
DROP TABLE #NoPrimaryKey;
-- Create
Temporary Table
CREATE TABLE
#NoPrimaryKey
(
DatabaseName VARCHAR(255) ,
SchemaName VARCHAR(255) ,
TableName VARCHAR(1000)
);
INSERT INTO
#NoPrimaryKey
EXEC
sp_msforeachdb ''USE [?];
IF ''''?'''' NOT
IN (''''master'''', ''''model'''',''''msdb'''', ''''tempdb'''',
''''ReportServer'''', ''''ReportServerTempDB'''', ''''Distribution'''')
BEGIN
SELECT DB_NAME()
AS DatabaseName, SCHEMA_NAME(s.[schema_id]) AS SchemaName,
OBJECT_NAME(t.[object_id]) AS TableName
FROM sys.tables t
INNER JOIN
sys.objects o ON o.[object_id] = t.[object_id]
INNER JOIN
sys.schemas s ON s.[schema_id] = o.[schema_id]
WHERE
OBJECTPROPERTY(t.[OBJECT_ID],''''TableHasPrimaryKey'''') = 0
AND
OBJECTPROPERTY(t.[object_id],''''IsUserTable'''') = 1
AND
o.is_ms_shipped = 0
ORDER BY
DatabaseName, SchemaName, TableName ASC;
END
''
-- Check for tables without a Primary key
IF EXISTS (
SELECT 1
FROM #NoPrimaryKey)
BEGIN
DECLARE @tableHTML NVARCHAR(MAX);
SET @tableHTML = N''<style
type="text/css">''
+ N''.h1 {font-family: Arial,
verdana;font-size:16px;border:0px;background-color:white;} ''
+ N''.h2 {font-family: Arial,
verdana;font-size:12px;border:0px;background-color:white;} ''
+ N''body {font-family: Arial,
verdana;} ''
+ N''table{font-size:12px;
border-collapse:collapse;border:1px solid black; padding:3px;} ''
+ N''td{background-color:#F1F1F1;
border:1px solid black; padding:3px;} ''
+ N''th{background-color:#99CCFF;
border:1px solid black; padding:3px;}''
+ N''</style>'' +
N''<table border="1">'' + N''<tr>''
+
N''<th>DatabaseName</th>''
+ N''<th>SchemaName</th>''
+
N''<th>TableName</th>''
+ N''</tr>''
+ CAST(( SELECT td = DatabaseName,
'''',
td = SchemaName,
'''',
td = TableName,
''''
FROM #NoPrimaryKey
FOR
XML PATH(''tr'') ,
TYPE
) AS NVARCHAR(MAX)) +
N''</table>'';
-- Count tables
SELECT @TableCount = COUNT(1) FROM
#NoPrimaryKey;
-- Count tables
SELECT @DatabaseCount =
COUNT(DISTINCT DatabaseName) FROM #NoPrimaryKey;
-- Set subject
SET @EmailSubject = ''ALERT - '' +
CAST(@TableCount AS VARCHAR(100)) + ''
Tables without a Primary Key on '' + @@SERVERNAME + '' accross '' +
CAST(@DatabaseCount AS VARCHAR(100)) + '' databases''
-- Email results
EXEC msdb.dbo.sp_send_dbmail @profile_name
= @EmailProfile,
@recipients = @EmailRecipient,
@subject = @EmailSubject,
@body = @tableHTML, @body_format =
''HTML'';
END
GO
',
@database_name=N'master',
@flags=12
IF (@@ERROR <> 0 OR @ReturnCode <>
0) GOTO
QuitWithRollback
EXEC
@ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <>
0) GOTO
QuitWithRollback
EXEC
@ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'(07:09) Monday',
@enabled=1,
@freq_type=8,
@freq_interval=2,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20121129,
@active_end_date=99991231,
@active_start_time=70900,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <>
0) GOTO
QuitWithRollback
EXEC
@ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <>
0) GOTO
QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
Enjoy!
Chris
Thursday, 29 November 2012
Tables without a Clustered Index
Yesterday
while adding some new logic to an existing process I noticed an unacceptable
level of performance degradation with the routine. Investigation led me to find that a new table
had slipped through the net and been created without a clustered index. Needless to say adding a clustered index and
rebuilding the non-clustered indexes on the table improved the performance of
the routine significantly.
Being a DBA,
this got me thinking about how this had slipped through the net, how I could
prevent it in future and equally as important how many other existing tables didn't have a Clustered Index? I knocked
together the below script which will create a SQL Job that is scheduled to run
every Monday at 07:07 and email an operator if there are any tables without a
clustered index in all user databases.
There are
a few things that will need changing;
·
@notify_email_operator_name=N'Chris'
·
SET
@EmailProfile = ''Chris''
·
SET
@EmailRecipient = ''Chris@SQLServer365.co.uk''
You can
also obviously change the schedule accordingly to suit your needs.
I would be
interested to know the results on your servers, leave me a comment with the
email subject text from the resulting email.
/*
-----------------------------------------------------------------
Find tables without a clustered index
-----------------------------------------------------------------
For more SQL resources, check out
SQLServer365.blogspot.co.uk
-----------------------------------------------------------------
You may alter this code for your own
purposes.
You may republish altered code as long as
you give due credit.
You must obtain prior permission before
blogging this code.
THIS CODE AND INFORMATION ARE PROVIDED
"AS IS"
-----------------------------------------------------------------
*/
-- Set database
context
USE [msdb]
GO
IF EXISTS (SELECT job_id FROM
msdb.dbo.sysjobs_view WHERE
name = N'Alert -
TablesWithoutClusteredIndex')
EXEC msdb.dbo.sp_delete_job @job_name
= N'Alert -
TablesWithoutClusteredIndex',
@delete_unused_schedule=1
GO
BEGIN TRANSACTION
DECLARE
@ReturnCode INT
SELECT
@ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND
category_class=1)
BEGIN
EXEC
@ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <>
0) GOTO
QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC
@ReturnCode =
msdb.dbo.sp_add_job @job_name=N'Alert -
TablesWithoutClusteredIndex',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'This job runs on a Monday morning at 07:07 and will email
if there are any tables without a clustered index in any user database.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @notify_email_operator_name=N'Chris', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <>
0) GOTO
QuitWithRollback
/******
Object: Step [Find tables Without a
Clustered Index] Script Date:
11/29/2012 12:20:42 ******/
EXEC
@ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Find tables Without a Clustered Index',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'-- Set database context
USE master;
GO
-- Declare
variables
DECLARE
@EmailProfile VARCHAR(255)
DECLARE
@EmailRecipient VARCHAR(255)
DECLARE
@EmailSubject VARCHAR(255)
DECLARE
@IndexUsageStats INT
DECLARE
@TableCount INT
DECLARE
@DatabaseCount INT
-- Set variables
SET @EmailProfile
= ''Chris''
SET
@EmailRecipient = ''Chris.McGowan@SQLServer365.co.uk''
-- Drop temporary
table if exists
IF
OBJECT_ID(''tempDB.dbo.#NoClusteredIndex'') IS NOT NULL
DROP TABLE #NoClusteredIndex;
-- Create
Temporary Table
CREATE TABLE
#NoClusteredIndex
(
DatabaseName VARCHAR(255) ,
SchemaName VARCHAR(255) ,
TableName VARCHAR(1000)
);
INSERT INTO
#NoClusteredIndex
EXEC
sp_msforeachdb ''USE [?];
IF ''''?'''' NOT
IN (''''master'''', ''''model'''',''''msdb'''', ''''tempdb'''',
''''ReportServer'''', ''''ReportServerTempDB'''', ''''Distribution'''')
BEGIN
SELECT
DISTINCT DB_NAME() AS DatabaseName,
SCHEMA_NAME(s.[schema_id]) AS SchemaName, OBJECT_NAME(i.[object_id]) AS
TableName
FROM sys.indexes i
INNER JOIN
sys.objects o ON o.[object_id] = i.[object_id]
INNER JOIN
sys.schemas s ON s.[schema_id] = o.[schema_id]
WHERE i.INDEX_ID =
0
AND
OBJECTPROPERTY(i.[object_id],''''IsUserTable'''') = 1
ORDER BY
DatabaseName, SchemaName, TableName ASC;
END
''
-- Check for
tables without a clustered index
IF EXISTS (
SELECT 1
FROM #NoClusteredIndex)
BEGIN
DECLARE @tableHTML NVARCHAR(MAX);
SET @tableHTML = N''<style
type="text/css">''
+ N''.h1 {font-family: Arial,
verdana;font-size:16px;border:0px;background-color:white;} ''
+ N''.h2 {font-family: Arial,
verdana;font-size:12px;border:0px;background-color:white;} ''
+ N''body {font-family: Arial,
verdana;} ''
+ N''table{font-size:12px;
border-collapse:collapse;border:1px solid black; padding:3px;} ''
+ N''td{background-color:#F1F1F1;
border:1px solid black; padding:3px;} ''
+ N''th{background-color:#99CCFF;
border:1px solid black; padding:3px;}''
+ N''</style>'' +
N''<table border="1">'' + N''<tr>''
+
N''<th>DatabaseName</th>''
+
N''<th>SchemaName</th>''
+ N''<th>TableName</th>''
+ N''</tr>''
+ CAST(( SELECT td = DatabaseName,
'''',
td = SchemaName,
'''',
td = TableName,
''''
FROM #NoClusteredIndex
FOR
XML PATH(''tr'') ,
TYPE
) AS NVARCHAR(MAX)) +
N''</table>'';
-- Count tables
SELECT @TableCount = COUNT(1) FROM
#NoClusteredIndex;
-- Count tables
SELECT @DatabaseCount =
COUNT(DISTINCT DatabaseName) FROM #NoClusteredIndex;
-- Set subject
SET @EmailSubject = ''ALERT - '' +
CAST(@TableCount AS VARCHAR(100)) + ''
Tables without a Clustered Index on '' + @@SERVERNAME + '' accross '' +
CAST(@DatabaseCount AS VARCHAR(100)) + '' databases''
-- Email results
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @EmailProfile,
@recipients = @EmailRecipient,
@subject = @EmailSubject,
@body = @tableHTML, @body_format =
''HTML'';
END
GO',
@database_name=N'master',
@flags=12
IF (@@ERROR <> 0 OR @ReturnCode <>
0) GOTO
QuitWithRollback
EXEC
@ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <>
0) GOTO
QuitWithRollback
EXEC
@ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'(07:07) Monday',
@enabled=1,
@freq_type=8,
@freq_interval=2,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20121129,
@active_end_date=99991231,
@active_start_time=70700,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <>
0) GOTO
QuitWithRollback
EXEC
@ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <>
0) GOTO
QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
Enjoy!
Chris
Subscribe to:
Posts (Atom)