Browse by Tags

All Tags » scripts (RSS)

Reading a large SQL Errorlog

I came across an interesting situation recently where a SQL instance had been configured with the Audit of successful and failed logins being written to the errorlog. ie This meant… every time a user or the application connected to the SQL instance –...
Posted by steveh99999 | with no comments
Filed under:

Database Backup History From MSDB in a pivot table

I knocked up a nice little query to display backup history for each database in a pivot table format. I wanted to display the most recent full, differential, and transaction log backup for each database. Here's the SQL :- WITH backupCTE AS ( SELECT...
Posted by steveh99999 | 3 comment(s)
Filed under:

The Exceptional EXCEPT clause

Ok, I exaggerate, but it can be useful… I came across some ‘poorly-written’ stored procedures on a SQL server recently, that were using sp_xml_preparedocument. Unfortunately these procs were not properly removing the memory allocated to XML structures...
Posted by steveh99999 | 1 comment(s)
Filed under: ,

The overlooked OUTPUT clause

I often find myself applying ad-hoc data updates to production systems – usually running scripts written by other people. One of my favourite features of SQL syntax is the OUTPUT clause – I find this is rarely used, and I often wonder if this is due to...
Posted by steveh99999 | 5 comment(s)
Filed under: ,

DBCC MEMUSAGE in 2005/8 ?

I used to like using undocumented command DBCC MEMUSAGE in SQL 2000 to see which tables were using space in SQL data cache. In SQL 2005, this command is not longer present. Instead a DMV – sys.dm_os_buffer_descriptors – can be used to display data cache...
Posted by steveh99999 | 4 comment(s)
Filed under: ,