SQL Server Snippets | Paul Hewson

On a server with a large number number of SQL Agent jobs the error log can become quite large. This can cause problems if it is located on the C: drive. The solution is to move it to a drive that is less critical or has more space by using this script. Replace the string within the angle brackets to an existing drive and folder. USE msdb;  GO EXEC msdb . dbo . sp_set_sqlagent_properties @errorlog_file = N'<New_Errorlog_Location>\SQLAGENT.OUT' GO   You will need to restart SQL Agent service for this change to take place. [This piece of code is ideal for a Snippet ]

To disable a SQL Agent job you can right click on it and choose Disable. The trouble is if you want to disable a large number of them at once this can be time consuming and a nuisance. You can also  disable a job by running the following command: EXEC msdb . dbo . sp_update_job @job_name = 'Your job name'        , @enabled = 0 You can get 'Your job name' from msdb.dbo.sysjobs so all we need to do is query that table and plug the result into the script below which will generate a list of scripts we can use: USE [msdb] GO SELECT    'EXEC msdb.dbo.sp_update_job @job_id=N''' + CONVERT ( NVARCHAR ( 36 ), job_id ) + ''',               @enabled=0 GO' , name FROM msdb . dbo . sysjobs WHERE name LIKE 'LSRestore_%' In this case I wanted to return all the jobs that are involved with a log ship restore. The results returned look like this: Click to enlarge To generate the script

Sometimes I'm in a position where I have to restore a production database to a development server, but there are a whole bunch of logins (belonging to developers) that don't exist in production which do on the development instance that need adding to a role so they can perform DML. A way to get around this is to create a separate database and table on the development server which has a list of the logins and the roles they need to be added to and add a piece of TSQL similar to that below to the database restore job which adds the logins to the correct role:   -------------------------------- -- Create the database -------------------------------- CREATE DATABASE UserManagement CONTAINMENT = NONE ON PRIMARY (        NAME = N'UserManagement'        , FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\UserManagement.mdf'        , SIZE = 5120 KB        , FILEGROWTH = 1024 KB        ) LOG ON (   

I have been in the situation many times where I have got a long list of strings that either I need to insert into a table or use in a query. The trouble is they all need a comma and quotes around them so they can be used in SSMS. After much frustration I have come across a solution that works well for me - a macro in Notepad++ which will add the commas and quotes for me. If you have a list of strings like: master model msdb db1 db2 And want to put quotes and commas around them all to include in a query so it looks like: SELECT NAME FROM sysdatabases WHERE NAME NOT IN (                 'master'               , 'model'               , 'msdb'               , 'db1'               , 'db2'               ) Then follow the steps below: If you don't already have Notepad++ installed go  here  and install it Run Notepad++ and paste your list of strings into the window. Next you need to record your macr

I have used the technique below to delete rows in batches which includes pauses to let other process access the table. USE IT AT YOUR OWN RISK!! I take NO responsibility if you run it on your own system - in fact that goes for any of the code on my blog. DECLARE @MaxID INT DECLARE @MinID INT DECLARE @Date DATETIME DECLARE @MyTableVar TABLE ( ID INT ) SET @Date = GETDATE () IF EXISTS ( SELECT * FROM tempdb .. sysobjects WHERE id = OBJECT_ID ( 'tempdb..#DelRows' ))     DROP TABLE #DelRows CREATE TABLE #DelRows ( ID INT ) /* To try and reduce table scans on large tables we get the minimum and maximum ID (assuming ID is a Clustered Index)   of the date range and use those as the minimum and maximum criteria for the filter */ -- Find latest ID in range SET @MaxID = (               SELECT TOP 1 t1 . Table1_ID               FROM dbo . Table1 t1 WITH ( NOLOCK )                        WHERE t1 . TableDate <

There are three main ways to access the SQL Error Log: via the SSMS GUI, via the system stored procedure sp_readerrorlog or the extended stored procedure xp_readerrorlog . The code below I have close to hand in a Snippet  and I probably use it every day. It copies the contents of the SQL error log and optionally the SQL Agent error log to a temporary table. Once there you query it for specific information. This can be also be done by using parameters 3 and 4 of sp_readerrorlog but for me this is a little more flexible.   /* sp_readerrorlog can take 4 optional parameters, they are:     1. The number of the error log file you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc...     2. Log file type: 1 or NULL = error log, 2 = SQL Agent log     3. Search string 1: String one you want to search for     4. Search string 2: String two you want to search for to further refine the results */ ---------------- -- Current SQL Error log --------------- IF EXIS

In previous version of SQL server (around about version 7)  if you changed the hostname of the Windows server you had to execute a SP so that the SQL Server instance would start up. This is not necessarily the case in modern versions of the product but I have found that Log Shipping doesn’t work unless you do so. If you don’t bother to make any changes to SQL after changing the hostname you will find the value returned by SELECT @@SERVERNAME returns the old name and not the new one, which explains the issue with log shipping as it seems to depend on this. In order to rename the SQL instance you have to execute a stored procedure that is normally related to adding or dropping a linked server – Microsoft’s website says that sp_dropserver, “Removes a server from the list of known remote and linked servers on the local instance of SQL Server.” But it turns out we also need to execute it if we change the name of the Windows host. Don’t worry, there is no impact in running the SP, it

Have you ever known then name of a column but couldn't remember what table it was in. Or wanted to create a join and need to find matching column names? Well this snippet of code will return the name of a table which has a column name you specify: SELECT t . NAME AS table_name        , SCHEMA_NAME ( schema_id ) AS schema_name        , c . NAME AS column_name FROM sys . tables AS t INNER JOIN sys . columns c ON t . OBJECT_ID = c . OBJECT_ID WHERE c . NAME LIKE '%<column_name>%' ORDER BY schema_name        , table_name ; [This piece of code is ideal for a  Snippet ]

If you do lots of database restores from one server to another you may often come across server logins becoming "orphaned" from their associated database users. There is an easy fix for this by using sp_change_users_login  but what if there are multiple logins that have become orphaned? The cursor below should fix them all for you: ----------------------------------------- -- Auto fix all orphaned user accounts ----------------------------------------- DECLARE @SQL VARCHAR ( MAX )   SET @SQL =        'DECLARE @userVar VARCHAR(30)               DECLARE users CURSOR               FOR               SELECT NAME               FROM sys.database_principals               WHERE type = ''s''                      AND NAME NOT IN (                            ''guest''                            ,''sys''                            ,''INFORMATION_SCHEMA''                           

In SQL Server Management Studio 2012 Microsoft introduced “snippets”. These are shortcuts to commonly used pieces of code that can be inserted in the query window (a similar facility for SQL 2008 was available with the free SSMSTools Pack but the developer now charges for the 2012 version). When you right click on the window a menu similar to an Intellisense shortcut menu pops up where you can choose some pre-cooked pieces of TSQL to insert. The good news is that it is possible to create your own snippets that appear as a menu option of My Code Snippets. The bad news is from Microsoft’s site on the subject you have to learn yet another xml schema to create one. Click to enlarge But wait, there’s more good news… some kind soul has created a nifty little tool that will do all the hard work for us, Snippets Generator . All you have to do is copy your code in to the main window, fill in the name, author and description fields, click Generate Snippet and it saves the file in