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

SQL Server – default trace FAQ

Has someone deleted a table?

Are you trying to track auto grow events?  Problem scenarios such as Database autogrow and slow database recovery

When did SQL memory usage change? Read SQL Server Performance ,memory pressure and memory usage  for SQL Server memory  analysis

SQL Server security  changes?

The default trace… Read more

0 comments, 975 reads

Posted in SQLServer-DBA on 11 April 2012

SQL Database roles and members

Q. How can I obtain the members of a role within a database?

A.  One method is to use sp_helprolemember


EXEC mydatabase.dbo.sp_helprolemember

DBRole		MemberName		MemberSID
My_Admin_Role	DOMAIN\muUser	0x010500000000000515000000313CF1C8B4034LF676BJA5EE7C0D0000

For a comprehensive security audit on database roles – read Powershell sql server security audit Read more

0 comments, 898 reads

Posted in SQLServer-DBA on 10 April 2012

BACKUP LOG with TRUNCATE_ONLY is discontinued

As  a follow up to an earlier posts - SQL Server - BACKUP LOG WITH NO_LOG

 As of SQL Server 2008 , BACKUP LOG with TRUNCATE_ONLY is discontinued.   I still find plenty of SQL Server maintenance scripts using BACKUP LOG with TRUNCATE_ONLY.

 In SQL SQL Server 2008 – an… Read more

0 comments, 4,707 reads

Posted in SQLServer-DBA on 5 April 2012

SQL Server - ALTER DATABASE to set a smaller FILEGROWTH for this file.

This error message can appear in the SQL Server Error Log files .

Autogrow of file 'MY_DB' in database 'MY_DB' took 455320 milliseconds.  Consider using ALTER DATABASE to set a smaller FILEGROWTH for this file.


What does this mean ?


1)       Need to analyse how you are managing… Read more

0 comments, 2,634 reads

Posted in SQLServer-DBA on 4 April 2012


First thing this morning – a request from a DBA to confirm if Differential BACKUPS can be restored on a database in SIMPLE RECOVERY mode.  Yes , you can.

 A few assumptions are made :

1) A Differential base exists

2) The Differential base is not a COPY_ONLY database .More… Read more

0 comments, 14,605 reads

Posted in SQLServer-DBA on 23 March 2012

Event ID 7026 and i8042prt troubleshooting

Event ID 7026 and i8042prt is a recurring error in the Windows Event Logs. I usually see this error on a server reboot .

It’s normal for a server to not have a keyboard or mouse attached. I usually suppress the error , by creating a headless entry.

 The … Read more

0 comments, 5,898 reads

Posted in SQLServer-DBA on 22 March 2012

Troubleshooting Event ID 9 - The device, xxxxx, did not respond within the timeout period.

The System logs returned :

 The device, xxxxxxxxxxxxxx, did not respond within the timeout period.

 Some common causes

1)      SCSI controller  firmware

2)      Incorrect transfer settings

3)      SCSI Cabling

4)      Other controllers creating bus contention


Record all the device model numbers and firmware revisions, and check with the… Read more

0 comments, 1,263 reads

Posted in SQLServer-DBA on 19 March 2012

Cached blocking history with sys.dm_db_index_operational_stats

Transactions generate IO, latching  and  locking on tables and indexes  , while attempting to access data. The sys.dm_db_index_operational_stats  DMV returns aggregated data on this activity.

Warning : sys.dm_db_index_operational_stats returns data  only as long as the metadata cache object that represents the heap or index is available. A frequently used object… Read more

0 comments, 1,123 reads

Posted in SQLServer-DBA on 15 March 2012

DBA supertech . Specialise or generalise?

Specialise or a generalise?  It all depends on how you define these terms. 

A  SQL Server DBA may be considered to be a specialist  by a Enterprise Architect but a generalist by a SSIS expert.

The DBA is currently in great demand and based on predictions  re: User Devices… Read more

0 comments, 690 reads

Posted in SQLServer-DBA on 13 March 2012

Factors that can delay Log Truncation – LOG BACKUP

When log records remain active for a long time – truncation can be delayed. The delays can cause the transaction logs to fill. This can lead to a Error 9002

 In the Full Recovery mode , a Log Backup  will delete the commited transaction log records. In the Simple Recovery… Read more

0 comments, 1,112 reads

Posted in SQLServer-DBA on 12 March 2012

SQL Server - Kerberos and KRB_AP_ERR_MODIFIED

On a routine scan of Event Viewer System, Logs I found this message on 4 different servers  hosting SQL Server installations


The kerberos client received a KRB_AP_ERR_MODIFIED error from the server MYCOMPUTER$.  This indicates that the password used to encrypt the kerberos service ticket is different than that on…

Read more

0 comments, 2,384 reads

Posted in SQLServer-DBA on 9 March 2012

SQL Server – netstat monitoring and tuning performance

To display network connections and various network interface statistics – netstat is a handy command – line based tool.  

 Why is it useful for the DBA ?

 1)       Execute netstat –an to view all active connections (TCP and UDP ports)  on a serverThe DBA has… Read more

0 comments, 1,239 reads

Posted in SQLServer-DBA on 8 March 2012

SQL Server – sys.master_files and list all database files

Use the system view “sys.master_files”  for a single view of databases and database files.

Instead of using sys.sysfiles  joing to sys.sysdatabases use the sys.master_files


select DB_NAME(database_id),mf.name as [file_name],physical_name
 FROM sys.master_files as mf


See Also

SQL Database Status with sys.databases

SQL Server Database Status,monitoring and Restores Read more

0 comments, 1,373 reads

Posted in SQLServer-DBA on 7 March 2012

SQL Server 2005 to SQL Server 2008 R2 upgrade considerations

 1)      SQL Server 2008 R2 Reporting Services doesn’t support Windows 2003 (or R2) Itanium based servers

2)      SQL-DMO support is stopped in SQL Server 2008 Express

3)      Cannot enable Web Assistant with sp_configure. MS recommend using Reporting Services

4)      Surface Area Configuration Tool is discontinued in SQL Server 2008.

5)     … Read more

0 comments, 1,116 reads

Posted in SQLServer-DBA on 6 March 2012

SQL Server – How to troubleshoot query timeouts

Common reasons for query time-outs are :

1)     The application starts using a query not optimal for the index

2)     Hardware changes\ Configuration changes

3)     Increased load

If you suspect the query time-out is due to memory issue , continue reading.

Debugging a query-timeout is tricky. If it’s a Production… Read more

0 comments, 3,803 reads

Posted in SQLServer-DBA on 4 March 2012

sql server scripts and managing databases

Managing a large SQL Server inventory requires an efficient management processes.

Following on from DBA  productivity and less is more  , this post outlines the system I use to manage the DBA scripts across the database server environment

 1)       Develop a script library covering the essentials of DBA database management… Read more

0 comments, 1,226 reads

Posted in SQLServer-DBA on 1 March 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,822 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,796 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,060 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,205 reads

Posted in SQLServer-DBA on 20 February 2012

Older posts