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

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

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

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

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

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

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

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 server.  The DBA has… Read more

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

 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

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

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