Skip to main content

Posts

Showing posts from August, 2013

How to change the location of the SQL Agent error log

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]

How to generate scripts to disable a SQL Agent Job

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:

EXECmsdb.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 FROMmsdb.dbo.sysjobs WHEREnameLIKE'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:
To generate the script to enable all the jobs, simply replace @enabled=0 with @enabled=1

[This piece of code is ideal for a Snippet]


How to add multiple logins to a role

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 -------------------------------- CREATEDATABASEUserManagementCONTAINMENT=NONEONPRIMARY ( NAME=N'UserManagement' ,FILENAME=N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\UserManagement.mdf' ,SIZE= 5120 KB ,FILEGROWTH= 1024 KB )LOGON ( NAME=N'UserManagement_log' ,FILENAME=N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER…

How to add quotes around a list of strings

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:
SELECTNAME FROMsysdatabases WHERENAMENOTIN( 'master' ,'model' ,'msdb' ,'db1' ,'db2' )
Then follow the steps below:
If you don't already have Notepad++ installed go here and install itRun Notepad++ and paste your list of strings into the window. Next you need to record your macro in Notepad++:Click at the beginning of line 1 or where the first string starts and either click on the relevant (play) button or choose Macro --> Start Reco…

How to delete records in batches while reducing locking

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@MaxIDINT DECLARE@MinIDINT DECLARE@DateDATETIME DECLARE@MyTableVarTABLE (IDINT)
SET@Date=GETDATE()
IFEXISTS(SELECT*FROMtempdb..sysobjectsWHEREid=OBJECT_ID('tempdb..#DelRows')) DROPTABLE#DelRows
CREATETABLE#DelRows(IDINT)
/* 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=( SELECTTOP 1 t1.Table1_ID FROMdbo.Table1t1WITH (NOLOCK) WHEREt1.TableDate<DATEADD(HOUR,- 24,@date) ORDERBYt1.TableDateDESC ) -- Find earliest ID in range SET@MinID=( SELECTTOP 1 t1.Table1_ID FROMdbo.Table1t1WITH (NOLOCK) WHEREt1.TableDate>=@Date ORDERBYt1.TableDateAS

An easier way to work with the SQL Server Error Log

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 --------------- IFEXISTS(SELECT 1 FROMtempdb..sysobjectsWH…

How to rename a SQL Server

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 execut…

Find the name of a column in an entire database

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:

SELECTt.NAMEAStable_name ,SCHEMA_NAME(schema_id)ASschema_name ,c.NAMEAScolumn_name FROMsys.tablesASt INNERJOINsys.columnscONt.OBJECT_ID=c.OBJECT_ID WHEREc.NAMELIKE'%<column_name>%' ORDERBYschema_name
,table_name;

[This piece of code is ideal for a Snippet]

How to fix all orphaned SQL Logins

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@SQLVARCHAR(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''                            ,''dbo''                            )                   …

How to create a SQL Snippet

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.


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 your code snippets folder.



Next…

Show all the installed SQL instances on a server

Have you ever wondered if there was more than one instance of SQL Server installed on a physical server but didn’t know how to find out quickly? Well this piece of TSQL below should help which uses the undocumented stored procedure xp_regread. It can be run from the default instance or any of the named instances and will return a value similar to the screen shot below:

DECLARE@ShowAllSQLInstancesTABLE ( ValueNVARCHAR(100) ,InstanceNamesNVARCHAR(100) ,DataNVARCHAR(100) )
INSERTINTO@ShowAllSQLInstances EXECUTExp_regread@rootkey='HKEY_LOCAL_MACHINE' ,@key='SOFTWARE\Microsoft\Microsoft SQL Server' ,@value_name='InstalledInstances'
SELECTInstanceNames
FROM@ShowAllSQLInstances












[This piece of code is ideal for a Snippet.]