The following is very handy to check for any unused index’s on a MSSQL database, this should only be run once the database has been up for at least a week or when all scheculed jobs have been allowed to run at least once. The only change I made is to display the table and index name.

Display any unused indexs in a specific database

Transact-SQL

1

2

3

4

5

6

7

8

9

10

11

12

SELECTo.name,i.name,u.*

FROM[sys].[indexes]i

INNERJOIN[sys].[objects]oON(i.OBJECT_ID=o.OBJECT_ID)

LEFTJOIN[sys].[dm_db_index_usage_stats]uON(i.OBJECT_ID=u.OBJECT_ID)

ANDi.[index_id]=u.[index_id]

ANDu.[database_id]=DB_ID()--returning the database ID of the current database

Find a stored procedure (or any text) inside a scheduled on on SQL Server 2008 and 2012 by querying the sysjobs and sysjobsteps system tables.
The following provides the name of the job and the step name. It uses the standard % wildcard so replace myjob in “%myjob%” below

We recently had an issue where MSDB was growing very quickly, it turned out it was due to a huge increase in data we was directly emailing to users.
We now run a weekly job just to maintain the MSDB email tables as whole copies of emails, attachments and logs are kept. I first run a stored procedure designed to maintain the tables and then directly delete from the tables (I would expect these to delete nothing).

Credit: Microsoft TechNet
On some occasions a few MS SQL databases have exhausted tempdb space, this was due to users leaving computers running for weeks on end and the program using different isolation levels or open transactions.
I use the following in SQL in see which process ID have been running the longest, as its a select statement it can of course be amended to your requirements (Adding an order by clause or just selecting the columns you require).

Run in tempdb

Transact-SQL

1

2

3

SELECT*

FROMsys.dm_tran_active_snapshot_database_transactions

ORDERBYelapsed_time_secondsDESC;

You should see something like the following, rather than kill of the transaction if it is a user try and educate the user to come out of transactions when finished.

On ocassion I need to look at what is in the EDI IN modules intermediary tables, scheme.eiordtm has an annoying key_code which it combines multiple objects. It also doesn’t always have the same amount of characters. As the key_code on scheme.eiordtm starts with the key_code from scheme.eiorhdm all I do is find out how many characters are in key_code from scheme.eiorhdm and use the SQL LEFT function on the join.
Additional columns can be added but this is just the defaults I use.

Method of working out check digit for an SSCC shipping code in SQL on a system with Sage Line 500 and Datalinx WHM (Although its easy enough to change, the first “set @SSCC” basically gets the digits, the rest of the code simply calculates the check digit).
This proc uses the Datalinx WHM Pallet ID and Sage stock warehouse code as Sage Stock lot numbers may contain text and slashes.
If called from a Datalinx WHM telnet/SSH script all print lines must be commented out.
GS1 codes can be shorter or longer, remember only 16 digits can be used (The GS1 code goes in from the second digit and digit 18 is always a check code!)
After needing two different versions (One required spaces in between different blocks the other didn’t) I decided to be lazy and specify the field locations. This version expects no spaces, if you need spaces simply change the Substrings and increase the variable SSCC from varchar(18).
Another thing to mind is I don’t need to change the first digit (GS1 states the same SSCC code can not be used within a year).

Method of working out check digit for an SSCC code

Transact-SQL

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

SETANSI_NULLSON

GO

SETQUOTED_IDENTIFIERON

GO

-- =============================================

-- Description: Generate an SSCC code with check digit

-- The first digit is always zero (Unless we re-use lot numbers within a year)

-- Digits 2 to 7 are our GS1 number of 1234567

-- Digits 9 to 17 are out lot number (With +0 taken off the front)

-- Digit 18 is a check digit. Use GS1 Check Digit Rules to Calculate

-- =============================================

-- GS1 Check Digit Rules

-- 1 Starting with the right hand digit of the number, add all the alternate digit values

-- 2 Multiply the result of step 1 by 3

-- 3 Add up all the other remaining digits

-- 4 Add the result of step 2 to the result of step 3

-- 5 The check digit is the smallest number that must be added to this sum to reach a multiple of 10

We needed to enable ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT to specific databases on a SQL Server 2008 instance, I found to do this even with users off I was being shown errors stating it must be done in Single User Mode.
I decided I wanted to do this as quickly as possible so slapped together some SQL code.

I had a user unable to use a script which generated an email, I found the user didn’t have permission, Microsoft state:
To reduce the surface area of SQL Server, Database Mail stored procedures are disabled by default.
To send Database Mail, you must be a member of the DatabaseMailUserRole database role in the msdb database.

You can add the user manually by:
Opening SQL Server Management Studio
Connecting to the server in question
Expand Databases
Expand System Databases
Expand msdb
Expand Security
Expand Users, right click the user or group and click properties
If the user is not in the list right click users and add the user or group
On the Membership tab ensure the user or group is a member of DatabaseMailUserRole

If you are lazy use a script like the one below (It will return a 1 if SQL can’t find the user or the user already has access, 0 means it was successful)

Notes:
I found using Windows Active Directory Groups required an extra step, by default all users have access to the Public Profile but not groups on SQL Mail.
Groups need to be added via “Manage Profile Security” –> “Private Profiles” tab.
As the group would be added select the AD group from the drop down box.
Ensure Access is ticked and click Next and OK.
As I have only done this the once I haven’t looked up if this can be done directly in T-SQL.

A while ago we added error trapping to the batch file called by a SQL script, this was to ensure if a program failed the rest of the process would not run:

After ensuring the program would return an error level if something went wrong we then I then used the ErrorLevel and an if statement. If the error level is equal to or greater than 1, I make it go to the end of the file and exit with code 1 (By default its zero which normally means the batch or program executed as expected)

MS DOS

1

2

3

4

5

6

7

8

9

rem Batch Code here must support returning errors otherwise it will be assumed to have run without issue

echo Error Level %ERRORLEVEL%

IFERRORLEVELGEQ1GOTOEOF

rem add any further Batch Code here

echo Error Level 0 so closing

exit

:EOF

echo Reached End of file due to program error

exit/b1

We then modified our existing code in SQL to ensure the batch program is run and the exit code is captured in a variable. It can then be dealt with and the example below can easily be changed to suit whatever is required.