SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Archives: February 2012

SQL Server Agent Job Steps

To list a SQL Server Agent Job Steps use the T-SQL example below.

This example displays the step id, SQL Server Agent Job name, Step name.

The code has 1 input parameter - @job_name


DECLARE @job_name VARCHAR(128)
SET @job_name = 'My SQL Server Agent Job'
SELECT js.step_id ,j.name,js.step_name FROM…

Read more

0 comments, 1,743 reads

Posted in SQLServer-DBA on 28 February 2012

Powershell Get-EventLog and Event Log messages

This post explains how to list Event Log Messages with Powershell Get-EventLog  , on multiple servers and output the results to a HTML file.

It is only one extra step to send an email attachment.

This example iterates through a list of servers and returns Error messages from the System… Read more

0 comments, 1,695 reads

Posted in SQLServer-DBA on 25 February 2012

SQL Server - DBA Team FAQ

Developers constantly ask about placing the latest code\feature\process\third party applications onto  Production Database Servers.

I’m building an FAQ  to supply Developers – as this could save me having very similar conversations .  

 Some other reasons for retaining a process is :

a)       Maintain database standards such as ISO-11179 Naming… Read more

0 comments, 1,035 reads

Posted in SQLServer-DBA on 24 February 2012

SQL Server – Powershell Active Directory search

 Working with Powershell and Active Directory simplifies some complex tasks for the DBA.

Active Directory is LDAP compliant. This means the RFC 1779 and RFC 2247 standards are met.

This example lists all employees on an LDAP path. This method requires knowledge of the LDAP path .

 Common Name… Read more

0 comments, 1,150 reads

Posted in SQLServer-DBA on 20 February 2012

SQL Server – read only database status

This post describes how to return the database read-only status via T-SQL .

The system view – sys.databases  returns a column called is_read_only.  

The column returns either 1 or 0

 1 = read only

0 = read write

   SELECT [name] ,is_read_only  
   FROM sys.databases  
   ORDER BY [name]


See Also

SQL… Read more

0 comments, 1,036 reads

Posted in SQLServer-DBA on 17 February 2012

SQL Server – DBA productivity and less is more

A typical DBA day can include a mixture of Operational, Engineering and Architectural tasks. Whilst maintaining 100% database server availability and acceptable performance levels.

I’m always looking for method to focus my efforts.  Automation, documentation , reporting, monitoring are all ways of simplifying the mountains of information. S

Inspired by… Read more

0 comments, 639 reads

Posted in SQLServer-DBA on 15 February 2012

Powershell default start directory

This post discusses some methods in configuring the Powershell  default start directory

 By default , the installation process will add a value to  the Start In parameter.

When you start Powershell , the default path will be  %HOMEDRIVE%%HOMEPATH%.

 Without opening Powershell , view the variable values through the DOS cmd… Read more

0 comments, 1,639 reads

Posted in SQLServer-DBA on 10 February 2012

SQL Server Product Life Cycle

Part of managing the SQL Server environment , is maintaining the latest Service Packs remain within the Product Support  data range.

The Microsoft web site has a search feature to return the latest dates  about: General availability, maintstream support and Service Pack Support End Dates


Read more

0 comments, 1,060 reads

Posted in SQLServer-DBA on 9 February 2012

Not enough storage is available to complete this operation

This message appears in the TDP SQL dsmsched file.  It’s usually associated with low levels of contiguous memory

BackupVirtualDeviceSet::SetBufferParms: Request large buffers failure on backup device 'TDPSQL-00001BDC-0000'. Operating system error 0x8007000e(Not enough storage is available to complete this operation.). A nonrecoverable I/O error occurred on file "TDPSQL-00001BDC-0000:" 995(The I/O operation…

Read more

0 comments, 3,082 reads

Posted in SQLServer-DBA on 8 February 2012

Send email from Powershell with attachment

This post  describes how to call  to add an email attachment using Powershell.

In an earlier post - SQL Server – Send email using Powershell – the new-object Net.Mail.SmtpClient() Class was used.

To add an email attachment , use New-Object System.Net.Mail.MailMessage and New-Object System.Net.Mail.Attachment, which are part of the .Net… Read more

0 comments, 7,782 reads

Posted in SQLServer-DBA on 7 February 2012

List failed SQL server Jobs with Powershell

This post explains how to list failed SQL Server Jobs , on multiple SQL Server Instances and output the results to a HTML file.

 It is only one extra step to send this file as an attachment.

The example script , returns failed SQL Server Agent Jobs within the last… Read more

0 comments, 2,138 reads

Posted in SQLServer-DBA on 6 February 2012

List SQL Server Instances using Powershell and Get-ItemProperty

This post explains how to list SQL Server Instances installed on the current server, using the Powershell Get-ItemProperty Cmdlet.

The Get-ItemProperty Cmdlet gets the properties of a specific item.

In this example, the command displays the Name and Data from the registry entries in the SQL registry subkey.Notice the  Powershell… Read more

0 comments, 1,533 reads

Posted in SQLServer-DBA on 4 February 2012

SQL Server – COPY TABLE from another SQL Server

To copy a table from another SQL Server Instance is easy. Create a Linked Server on the destination server and reference the Linked Server in the SELECT statement.

1) Create linked server

/****** Object:  LinkedServer [SERVER1\SQL_SERVER_INSTANCE_1]    Script Date: 02/02/2012 09:29:33 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'SERVER1\SQL_SERVER_INSTANCE_1', @srvproduct=N'SQL Server'
/* For…

Read more

0 comments, 929 reads

Posted in SQLServer-DBA on 2 February 2012