25. February 2011 by Bennett.

My first ship, the USNS H. H. Hess, is currently enroute to Brownsville, TX where it will be cut up into scrap metal and recycled.  The Hess was an oceanographic survey vessel that was operated by the Military Sealift Command from roughly 1978-1992.  The ship was originally built in 1965 as the SS Canada Mail,  a C4 Mariner class cargo ship that was operated by American Mail Lines.  The ship was converted into it’s survey role in 1976.

In its time it was the worlds largest floating data center with all sorts of cool computers and electronic instrumentation.   It performed bathymetric, magnetic field, and gravity surveys in support of the Navy’s FBM program.  The crew consisted of roughly 40 civilian mariners, 10 oceanographers, two commissioned naval officers, 18 enlisted Navy technicians, and several of defense contractor represenatives.

The ship was big, comfortable and well-appointed.  There were three dining rooms, two libraries, a conference room that doubled as a movie theater, nice staterooms, a workout room, a crew’s lounge and a large basketball court.   My favorite parts of the ship were the electronics shop - I pretty much had it to myself and built my first Z80-based computer there - and the bow, which offered sun, fresh air, and quiet solitude - a great place to study and contemplate life.

The photo below is a webcam capture that shows the Hess entering the Gatun locks of the Panama Canal on 2/18/2011.

Posted in Personal | Print | 2 Comments »

23. August 2010 by Bennett.

One of my daily tasks is to do a quick check of each SQL Server instance using Activity Monitor, sp_who2, or a DMV-based script.  Sometimes I get busy and forget to do this task.  Today I broke down and wrote a simple script that is executed by a SQL Agent job.  It runs at 8:00 AM, and just goes out to all the instances and checks to see if any backups are still running — if any are still running, an e-mail alert is raised.

The essence of the script follows.  You would might want to modify it to iterate through a list of instances.

IF EXISTS
(SELECT * FROM instance.MASTER.sys.sysprocesses
WHERE cmd = ‘backup database’
AND program_name = ‘SQL Management’)
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘Master’,
@recipients = ‘mailbox@domain.com’,
@body = ‘Backup job is still running on instance’,
@subject = ‘Backup job is still running on instace’,
@importance =  ‘high’;
END


Posted in SQL Server | Print | No Comments »

7. April 2010 by Bennett.

I created a small script to collect SQL Server file utilization data into a repository.  I’m collecting the space used by the files as well as the space used internally by SQL Server.  I’m doing this across all production databases and servers, so the T-SQL fileproperty function was not all that useful since it only works against the current database.  The solution was to use SMO in a PowerShell script to collect the data since it’s very easy to iterate across multiple servers and databases.  I’ll write up the solution at a later date, but for now I wanted to mention a minor fact that I learned today.

The SMO Database class has a Status property which returns at least two values, “Normal” and “Offline”.  You can use it to avoid trying to get info for offline databases.  For example:

if ( $db.status -eq “Normal”) { #exclude offline databases
#do something

}

Posted in Powershell, SQL Server | Print | 1 Comment »

25. February 2010 by Bennett.

Here are several trivial scripts for updating the system catalog, moving files via robocopy, and adjusting filegrowth values. I hope someone finds them useful.

– generate system catalog changes
– *** note: need to manually edit destination path volume letter in output ***
SELECT ‘ALTER DATABASE ‘ + DB_NAME() + ‘ SET OFFLINE’

SELECT ‘ALTER DATABASE ‘ + DB_NAME() + ‘ MODIFY FILE
(
NAME = ‘ + NAME + ‘,
FILENAME = N”’+ filename + ”’
);’
FROM dbo.sysfiles
– where filename like ‘i:\%’
– order by size desc

SELECT ‘ALTER DATABASE ‘ + DB_NAME() + ‘ SET ONLINE’

– generate robocopy statements to be executed under command prompt
– *** note: need to manually edit destination path volume letter in output ***
SELECT ‘RoboCopy ‘ +
LEFT(filename,(LEN(filename) - CHARINDEX(‘\’, REVERSE(filename)))+1) + ‘ ‘ +
LEFT(filename,(LEN(filename) - CHARINDEX(‘\’, REVERSE(filename)))+1) + ‘ ‘ +
RIGHT(filename,(CHARINDEX(‘\’, REVERSE(filename)))-1)
FROM dbo.sysfiles

– modify filegrowth
SELECT ‘ALTER DATABASE ‘ + DB_NAME() + ‘ MODIFY FILE
(
NAME = ‘ + NAME + ‘,
Filegrowth = 128MB
);’
FROM dbo.sysfiles

Posted in T-SQL | Print | 1 Comment »

28. January 2010 by Bennett.

Here is a trivial script to show where a particular index resides. It saves clicking around the SSMS GUI.


SELECT i.name, i.type_desc, i. is_primary_key, i.is_unique, s.name AS [Filegroup]
FROM sys.indexes i
INNER JOIN sys.data_spaces s
ON i.data_space_id = s.data_space_id
WHERE i.name IS NOT NULL
AND i.name NOT IN (‘clust’, ‘clst’, ‘nc1′, ‘nc2′, ‘nc3′, ‘nc’, ‘cl’)
ORDER BY s.data_space_id, i.name

Posted in T-SQL, SQL Server | Print | No Comments »

29. July 2009 by Bennett.

Pretty trivial, really:

SELECT ‘ALTER TABLE ‘ + OBJECT_NAME(PARENT_ID) + ‘ ENABLE TRIGGER ‘ + [NAME]
FROM sys.triggers    

…add a where clause if needed.  Paste the output back into the query window and modify as needed.  You can also slap some code around it to generate some dynamic SQL like this:

DECLARE @SQL VARCHAR(MAX)
SELECT @SQL=COALESCE(@SQL,”)+ ‘ALTER TABLE ‘ + OBJECT_NAME(PARENT_ID) + ‘ ENABLE TRIGGER ‘ + [NAME] + ‘; ‘
FROM sys.triggers
PRINT @SQL  

Posted in T-SQL | Print | No Comments »

29. June 2009 by Bennett.

You may encounter the error message “SQL SERVER is in SINGLE USER MODE and only one administrator can connect” when moving the Master and Resource databases when following the procedure in BOL.  It happens right after you bring up SQL Server in Master-only, single-user mode using the T3608 trace flag like this:

NET START MSSQLSERVER /f /T3608

For me the problem was that a remote process was grabbing the lone connection faster than I could.  The fix was to go into SQL Server Configuration Manager, Expand SQL Server Network Configuration, drill down into Protocols for MSSQLSERVER,  and then change the TCP/IP settings to point to a different port number.   In the example below, I pointed to port 14331 instead of 1433.

Once the port settings were changed, I stopped and restarted SQL Server and was able to finish moving the system databases.  Don’t forget to change back to port 1433 once you’re done!!!!

References:

http://msdn.microsoft.com/en-us/library/ms345408(SQL.90).aspx

Posted in Uncategorized | Print | 1 Comment »

11. June 2009 by Bennett.

You can use LogParser (a free, unsupported utility from Microsoft) to quickly import a CSV file into a SQL Server table using syntax like this:

logparser “SELECT * INTO MyTable FROM d:\MyFile.csv” -i:csv -o:SQL -server:MyServer -database:MyDatabase -createTable:ON

There are many other uses for LogParser.  Two of the most common are analyzing Windows Event Logs and IIS logs. 

Posted in Windows OS, SQL Server | Print | No Comments »

28. January 2009 by Bennett.

Multiply SQL database pages by 0.0078125 to get space in Megabytes
Multiply SQL database pages by 0.00000762939453125 to get space in Gigabytes

Here are a couple of examples of where this is useful:

This query uses a DMV to return TempDB utilization by object category such as user, internal, and version store:

SELECT
SUM(user_object_reserved_page_count)*0.0078125 as usr_obj_mb,
SUM(internal_object_reserved_page_count)*0.0078125 as internal_obj_mb,
SUM(version_store_reserved_page_count)*0.0078125  as version_store_mb,
SUM (unallocated_extent_page_count)*0.0078125 as freespace_mb,
SUM (mixed_extent_page_count)*0.0078125 as mixedextent_mb
FROM sys.dm_db_file_space_usage 

The next query returns space utilization , in Megabytes, by filegroup:

select name, filename, cast(size * 0.0078125 as int)as size_mb
from sysfiles
ORDER BY FILENAME
compute sum(cast(size * 0.0078125 as int))

Posted in T-SQL, SQL Server | Print | No Comments »

7. January 2009 by Bennett.

I am frequently asked to refresh development databases with production data.  The usual way to do this is to  back up the production database and then restore over the development database.  SQL Server 2005 has a new backup option, “WITH COPY_ONLY”.   This option allows you to perform a full backup without truncating the log and breaking the log chain.  The COPY_ONLY option is not supported in the SQL Server Management Studio (SSMS) GUI, so you have to perform the backup via a script.

It’s important to note that a backup that is created with the COPY_ONLY option cannot be restored with the SSMS GUI.  Instead you have to restore via a script.

SQL Server 2008 supports the COPY_ONLY option in the SSMS GUI.

Posted in SQL Server | Print | 1 Comment »

17. December 2008 by Bennett.

You may run across the situation where you need to import an existing Perfmon (or System Monitor) binary log file into and Excel spreadsheet or SQL database.  The obvious first step is to convert it to CSV format.  It took googling numerous forums to figure out how to do this, so I thought that I’d document it here for future reference.

The relog.exe utility, which ships with the OS, has the ability to convert between Perfom log file formats and merge several log files into one file.  It has some nice filtering capabilities including filtering by time, counters, and nth record.  Example syntax for simply converting between file formats:

relog -f csv inputfile.blg -o outputFile.csv

You can display all the options by simply typing relog -? at the command prompt.

Posted in Utilities, Windows OS, Uncategorized | Print | 1 Comment »

19. November 2008 by Bennett.

I ran into a situation today where I was generating dynamic SQL that appeared to be valid.  I could take the generated SQL strings and execute them with no issues, but when executed dynamically, I got the error message “Must declare the scalar variable “@someVar”, even though the variable in question was declared properly.  I learned that the problem occurred because the variables were declared outside of the scope of the dynamic SQL that I was executing.  I was pointed to the following article:  http://support.microsoft.com/kb/262499.  This article describes how to use input and output parameters with the sp_executesql stored procedure.  Another good article on dynamic SQL parameters can be found on the SimpleTalk web site here.

Posted in T-SQL | Print | No Comments »

6. October 2008 by Bennett.

I learned an interesting tidbit while reading Inside Microsoft SQL Server 2005: Query Tuning and Optimization.  The Max Server Memory configuration value applies to the cache buffer pool and not SQL Server as a whole.  Here is a link to an MSDN article that explains it:  http://msdn.microsoft.com/en-us/library/ms180797.aspx

This book goes on to discuss how to detect, measure, and remedy various forms memory pressure.  I followed some of the examples in the book and came up with these statistics on one server:

memory_stats1.html

One thing that is kind of interesting about these statistics is that it clearly shows that the configured server memory is significantly less than the memory used by sqlserver.exe.

Posted in SQL Server | Print | No Comments »

18. August 2008 by Bennett.

I’m not a big proponent of shrinking files. There are a lot of postings on DBA forums citing why this practice should be avoided. However we have a development server with a lot of databases, and the log files need to be shrunk on a pretty regular basis.

I wrote a little query to dynamically build the list of databases and logical filenames and then run DBCC SHRINKFILE to shrink all the log files. Here’s the script:

SET nocount ON;
CREATE TABLE #tempFileInfo
(
  dbName VARCHAR(256),
  logicalName VARCHAR(256),
)

DECLARE @DBName VARCHAR(256)
DECLARE @SQL VARCHAR(MAX)

DECLARE CursorDB CURSOR FOR
SELECT [name] FROM sys.databases WHERE database_id > 4 AND state_desc = ‘ONLINE’
OPEN CursorDB

FETCH NEXT FROM CursorDB INTO @DBName
WHILE @@FETCH_STATUS = 0
  BEGIN
    SET @DBName = ‘[’ + @DBName + ‘]’
    SET @SQL = ’select ‘ + CHAR(39) + @DBName + CHAR(39) + ‘, [name] from ‘ + @DBName + ‘.sys.sysfiles WHERE filename LIKE ‘+ CHAR(39) + ‘%ldf’ +CHAR(39)
    –print @SQL
    INSERT INTO #tempFileInfo
    EXEC (@SQL)
    FETCH NEXT FROM CursorDB INTO @DBName
  END
CLOSE CursorDB
DEALLOCATE CursorDB

SET @SQL = ”
SELECT @SQL=COALESCE(@SQL,”)+ ‘USE ‘ + dbName + ‘ DBCC SHRINKFILE(’ + CHAR(39) + logicalName + CHAR(39) + ‘, 1); ‘
FROM #tempFileInfo

PRINT @SQL
EXEC(@SQL)

DROP TABLE #tempFileInfo

Posted in T-SQL, SQL Server | Print | No Comments »

9. June 2008 by Bennett.

The TCP Chimmney Offload feature was introduced in Windows Server 2003 SP2.  It can offload certain functions from the OS to the network adapter card.  A lot of our newer HP Servers have a Broadcomm Ethernet chipset that supports this function.   It isn’t without its issues.  To determine if it is in use you can run the netstat –nt command like this:

C:\>netstat -nt

 Active Connections

   Proto  Local Address          Foreign Address        State           Offload State
  TCP    192.168.4.97:445        192.168.4.13:2167       ESTABLISHED     InHost
  TCP    192.168.4.97:1433       192.168.4.95:3560       ESTABLISHED     InHost
  TCP    192.168.4.97:1433       192.168.4.95:3617       ESTABLISHED     InHost

The last column shows the offload status.  In this example the offload state is shown as InHost for all connections, so TCP offload is not enabled.

Posted in Windows OS | Print | No Comments »

23. May 2008 by Bennett.

You can use the PL-SQL to_char function to format the output of SYSDATE like this:

select to_char(sysdate,’DD/MM/YYYY HH24:MI:SS’) from dual;

Posted in SQL | Print | No Comments »

7. May 2008 by Bennett.

I used several books to study for the ICCP DBA exam including:

• Modern Database Management, 8th Edition by Hoffer, Prescott and McFadden
• Database Systems: Design, Implementation & Management 6th Edition by Rob and Coronel
• Database Administration: The complete Guide to Practices and Procedures by Craig S. Mullins
• An Introduction to Database Systems 5th Editon by C.J. Date

Of these, Modern Database Management was my favorite. Chapter 6 of this book covers physical database design and performance. There is a section in this book that has guideline for improving query performance. Here are a few of the  recommendations:

• Use compatible data types, i.e., avoid type conversions
• Write simple queries
• Break complex queries into multiple, simple parts
• Don’t nest one query inside another (just because you can use a subquery doesn’t always mean that you should)
• Don’t combine a table with itself, i.e. avoid self-joins.
• Create temporary tables for groups of queries.

Posted in T-SQL | Print | No Comments »

4. May 2008 by Bennett.

Today while applying patches to a couple of mission-critical servers, I was reminded of something:  reboots issued through the terminal services client are not always successful.  The server can get stuck in an odd state where it is running but terminal services is not working.  For me the workaround has been to issue a remote reboot command using the shutdown utility:

shutdown /r  /m \\server_name_to_reboot /t 0  /f

Posted in Windows OS | Print | No Comments »

10. April 2008 by Bennett.

It’s been a while since I worked on my cluster lab. The first thing that I did was to tear down the old cluster and start over from scratch. The first lesson from this round of testing came when the cluster administrator scanned the hardware resoruces and threw a warning message that stated that only one adapter was found on the node. This warning is caused by fact that the two cluster nodes are interconnected via a crossover cable and the second node is shut down. With the node shut down, there is no physical layer link on the Ethernet card, so the OS thinks that the cable is disconnected.

I got past the error by simply plugging the cluster member’s private network connection into a spare switch that I had lying around. This provided a physical layer link which was what the OS needed to report a valid network connection. After that I rescanned the resources and the cluster installation proceeded.

Posted in Windows OS | Print | No Comments »

7. April 2008 by Bennett.

Here is a very simple and elegant way to strip the time component from a datetime value:

CAST(FLOOR(CAST(@date AS float)) AS datetime)

Written as a function:

CREATE FUNCTION BareDate
(
– Add the parameters for the function here
@date datetime
)
RETURNS datetime
AS
BEGIN
RETURN CAST(FLOOR(CAST(@date AS float)) AS datetime)
END
GO

When we pass in the value ‘2008-02-12 13:25:33.3′, it returns the value 2008-02-12 00:00:00.000

Other ways of stripping the time component:

SELECT DATEADD(dd,(DATEDIFF(dd,0,backup_start_date)),0)

or:

SELECT CAST(DATEDIFF(dd,0,backup_start_date) AS Datetime)Using the convert has performance issues.  I’ll have to come up with numbers for all these: or

SELECT CONVERT(Datetime, CONVERT(NCHAR(10), backup_start_date, 121))

Posted in SQL Server | Print | No Comments »

3. April 2008 by Bennett.

Today I was reminded of something that I knew but forgot. The IIS Metabase Explorer for IIS6 (or MetaEdit for IIS 4 and 5) is a very useful tool. I needed to blow away an inheritable attribute of the HttpRedirect property. The Metabase explorer made the task ridiculously simple.

The Metabase Explorer is part of the IIS 6.0 Resource Kit.

Posted in IIS | Print | No Comments »

1. April 2008 by Bennett.

Here is an interesting Microsoft Knowledgebase article titled Disk performance may be slower than expected when you use multiple disks in Windows Server 2003, in Windows XP, and in Windows 2000.

It explains how a partition misalignment can cause significant I/O by causing reads and writes to straddle track boundaries. The fix is to use the diskpart.exe utility to create an alignment offset when you first create the partition.

This is similar to what you do with SAN storage to align the starting offset to “significant” boundaries. See the 3/31/08 post for more details.

Posted in Performance, SQL Server | Print | 1 Comment »

31. March 2008 by Bennett.

There is an EMC whitepaper titled Using diskpar and diskpart to align partitions on Windows Basic and Dynamic Disks. This paper describes how legacy Windows issues cause a single disk I/O to span multiple tracks. This puts an unnecessary load on the SAN and reduces performance. The fix is to align the partition. Below is a sample session using the DISKPART utility. Comments are in red.

Invoke Diskpart

C:\>diskpart

Microsoft DiskPart version 5.2.3790.3959

Copyright (C) 1999-2001 Microsoft Corporation.

On computer: MYTESTBOX

You must select the disk to act on

DISKPART> select disk 5

Disk 5 is now the selected disk.

Create a partition using the EMC-recommended alignment offset

DISKPART> create partition primary align=64

DiskPart succeeded in creating the specified partition.

Select the partition / volume that you just created

DISKPART> select volume=0

Volume 0 is the selected volume.

Assign a drive letter

DISKPART> assign letter=o

DiskPart successfully assigned the drive letter or mount point.

Exit Diskpart

DISKPART> exit

Leaving DiskPart…

C:\>

You can now use the disk management MMC to format the partition

Posted in Windows OS, Performance | Print | 6 Comments »

30. March 2008 by Bennett.

FCIV.EXE is a handy little utility to snap a MD5 hash of a single file or multiple files. Optionally it can recurse directories and produce XML output. http://support.microsoft.com/kb/841290

MD5SUM.EXE is a more commonly used utility that runs on a number of different platforms. A good Wikipedia article with links can be found here and a Win32 binary can be found here.

Posted in Utilities | Print | No Comments »

28. March 2008 by Bennett.

Here’s a very basic log filtering /review script that you can run on a daily basis to review your SQL Server logs. It shows the last two days of log entries filtering out logon and backup entries.

– daily log review script
SET nocount ON
CREATE TABLE #Errors
(
LogDate DateTime,
ProcessInfo NVARCHAR(40),
ErrorText NVARCHAR(2048)
)
INSERT INTO #Errors
EXEC master..sp_readerrorlog -1

SELECT * FROM #Errors
WHERE DATEDIFF(DAY, LogDate, GETDATE())<2
AND ProcessInfo <> ‘Logon’
AND ProcessInfo <> ‘Backup’
DROP TABLE #Errors

I’m currently cooking up some VBScript to filter and e-mail log entries on a daily basis. I’ll publish that soon.

Posted in T-SQL | Print | No Comments »

3. March 2008 by Bennett.

Performance logging will not start on its own at boot time. Here’s how to get around that problem.

 

Applies to: W2k3, XP

1. Using the Performance MMC, create a set of counter logs.
2. Use a specific name to save the settings, e.g., SQLPERF.
3. At the command prompt, try starting and stopping performance logging using the logman.exe utility:
   1. logman start sqlperf
   2. logman stop sqlperf
4. Set the Windows registry to automatically start performance counters at system startup
   1. Open the Registry Editor, regedit.exe.
   2. Navigate to HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\Run
   3. Add a string value. A good name would be logman.
   4. Set the value equal to logman start sqlperf (or the command in step 4.a. that successfully started performance logging).

References:

1. http://support.microsoft.com/kb/303133
2. http://www.jsifaq.com/SF/Tips/Tip.aspx?id=4455

Posted in Windows OS, Performance | Print | No Comments »

24. January 2008 by Bennett.

A new version of the SQL Server 2005 Best Practices Analyzer (BPA) is here.

On a related note, the BPA reported several operating system issues that I was unaware of, including an issue where SQL Server’s working set gets paged to disk during large file copies. More info and a link to the Technet article discussing this issue can be found here.

Posted in Windows OS, SQL Server | Print | No Comments »

22. January 2008 by Bennett.

Compatible with sql server 2k5:

USE master
CREATE TABLE #tempresults
(


[name] sysname,
db_size NVARCHAR(13),
[owner] sysname,
[dbid] smallint,
created NVARCHAR(11),
[status] NVARCHAR(600),
compatibility_level tinyint

)
INSERT INTO #tempresults
EXEC sp_helpdb
SELECT db.name, tr.db_size, db.compatibility_level,is_auto_shrink_on, state_desc, recovery_model_desc, page_verify_option_desc

FROM sys.databases db
JOIN #tempresults tr
ON db.name =tr.name

WHERE dbid >4 –This clause will exclude system DBs,master, model, msdb, and tempdb
ORDER BY db.name
DROP TABLE #tempresults

Posted in T-SQL, SQL Server | Print | No Comments »

14. January 2008 by Bennett.

I found an easy way to render code snippets in HTML. It is called the Simple Talk Code Prettifier. In addition to rendering, it also converts SQL keywords to uppercase and gives you proper indentation.  Also supports VB.  http://www.simple-talk.com/prettifier/default.php

By the way, the Simple Talk blog http://www.simple-talk.com is pretty cool.  My puny effort here pales by comparison.

Posted in SQL Server | Print | No Comments »

9. January 2008 by Bennett.

Here are a couple of different ways to search for a column name within a SQL Server database:

– using information_schema views (preferred method)

SELECT sc.table_name
   FROM information_schema.columns sc
   INNER JOIN information_schema.tables st
     ON sc.table_name = st.table_name
   WHERE st.table_type = ‘base table’
   AND sc.column_name = ‘ColumnToFind’
   ORDER BY sc.table_name

– old school
SELECT name
   FROM sysobjects
   WHERE id IN
   (
      SELECT id
        FROM syscolumns
        WHERE name = ‘ColumnToFind’ )
        AND xtype = ‘U’
        ORDER BY name

Posted in T-SQL, SQL Server | Print | No Comments »

8. January 2008 by Bennett.

Script to change SQL Server database modes via systables. This lets you set modes, such as EMERGENCY, that cannot be set via the sp_dboption stored procedure.

– change database mode via systables
USE Master
GO
– Determine the original database status
SELECT [Name], DBID, Status
  FROM master.dbo.sysdatabases
GO
– Enable system changes
sp_configure ‘allow updates’,1
GO
RECONFIGURE WITH OVERRIDE
GO
– Update the database status
UPDATE master.dbo.sysdatabases
   SET Status = 16
     WHERE [Name] = ‘DatabaseNameGoesHere’
GO
– Disable system changes
sp_configure ‘allow updates’,0
GO
RECONFIGURE WITH OVERRIDE
GO
– Determine the final database status
SELECT [Name], DBID, Status
   FROM master.dbo.sysdatabases
GO

/*Modes

1 = autoclose; set with sp_dboption.
4 = select into/bulkcopy; set with sp_dboption.
8 = trunc. log on chkpt; set with sp_dboption.
16 = torn page detection, set with sp_dboption.
32 = loading.
64 = pre recovery.
128 = recovering.
256 = not recovered.
512 = offline; set with sp_dboption.
1024 = read only; set with sp_dboption.
2048 = dbo use only; set with sp_dboption.
4096 = single user; set with sp_dboption.
32768 = emergency mode.
4194304 = autoshrink.
1073741824 = cleanly shutdown.

Note that these are bit values. For example, 24 = 8+16, so 24 means that truncate log on checkpoint AND torn page detection
are both set.
*/

Posted in T-SQL, SQL Server | Print | No Comments »

21. December 2007 by Bennett.

I am happy to report that I graduated from Excelsior College with a BS in Computer Information Systems and a GPA of 4.0!

Posted in Personal | Print | No Comments »

23. October 2007 by Bennett.

On a personal note, I am happy to report that I passed the ICCP Database Administration Exam at the mastery level. Passing this exam completes my degree requirements.

Posted in Personal | Print | No Comments »

3. September 2007 by Bennett.

Interesting tool: http://winmerge.org/

To quote the authors: WinMerge is an Open Source visual text file differencing and merging tool for Win32 platforms. It is highly useful for determing what has changed between project versions, and then merging changes between versions.

I have previously used to use windiff.exe to diff two files. This is or was part of the Windows Resource Kit. More recently I have used the freeware Context editor http://context.cx, which has a very good diff tool built in.

On a related note, Here’s a cool trick for finding registry differences between two machines: Export the subkey to a file for each machine and use a file diff tool to compare the differences. By default, the registry editor exports unicode, so either your diff tool has to support unicode, or you need to use the optional Win NT4/9x output format for your export.

Posted in Utilities, Open Source | Print | No Comments »

7. May 2007 by Bennett.

Here’s a handy script to return space used by table:  spaceused.html

Posted in T-SQL, SQL Server | Print | No Comments »

16. April 2007 by Bennett.

I recently built an Windows Server 2003 cluster lab using a couple of PCs, two Adaptec AHA2940UW controllers and an old SCSI drive. When configuring the SCSI HBAs, it is important to remember to disable SCSI resets.

Note that when shopping for surplus SCSI HBAs, the AHA2940UW is probably the oldest card that you can use. It has a flash-programmable BIOS.   You can download the last BIOS version produced for that card from Adaptec’s website.  The older 2940 (without the U in the part number) uses pluggable EPROMS.

Posted in Windows OS | Print | No Comments »

20. February 2007 by Bennett.

While working on a client’s Exchange server, I stumbled across an interesting knowledgebase article: SMTP tar pit feature for Microsoft Windows Server 2003. Tarpits insert a delay into certain SMTP communications that are associated with spam or with other unwanted traffic. They work by slowing all responses that contain SMTP protocol 5.x.x error codes. For the Microsoft SMTP server the delay value is configurable. There are some caveats as to whether or not this will be effective in your environment and whether or not it will slow legitimate traffic. It is definitely worth checking out.

On a related note, it would be interesting to put a LaBrea Tarpit on the top and bottom IPs of an Internet facing subnet. For example, if I owned subnet 207.103.204.0/24, I would bind addresses 207.103.204.1 and 207.103.204.254 to a LaBrea host. Zombie computers often do linear address space scans looking for victim machines. Having a tarpit at either end of the subnet could could significantly slow zombie activity. It would also tend to clean up much of that junk that you see in your HTTP or IDS logs.

Posted in Windows OS | Print | No Comments »

18. February 2007 by Bennett.

Wouldn’t it be nice to have the time once a week to run the dcdiag against all of your domain controllers and review the results? (yeah, right) Well, I wrote a script to automate that process. It works like this: First there is a subroutine that enumerates the DCs and puts the result into an array of strings. Next there is a function that runs DCDiag against the list of DCs looking for the string “failure”. If that string is found it concatenates the server name and failure string to a global string variable. At the end of this function, if a failure was found, the function returns “True”. If the function returns true, the script e-mails an alert with the necessary information.

Posted in Windows OS | Print | No Comments »

13. February 2007 by Bennett.

I audited my Wireless Access Point using the free Retina WiFi Scanner from eEye Digital Security. According to it, my WPA PSK key length was too short, so I increased it to around 25 characters. If you look at the scanner’s installation directory, there is a file named wepdic.txt. This is the wordlist that the scanner uses to attempt to brute force WEP keys. In theory you should be able to replace it with a larger wordlist.

Cain & Abel has been added to my list of “must have” security tools.  Essential for doing password audits.

Posted in Uncategorized | Print | No Comments »

13. February 2007 by Bennett.

Today I came across an interesting problem while changing the SQL server service to run on a non-administrative domain account. Previously the service was configured to log in as localsystem, which has full rights locally, but no domain rights. With this change, the service would start and then terminate. Here is a SQL error log snippet:

2007-02-13 15:05:01.29 spid5 Clearing tempdb database.
2007-02-13 15:05:01.58 spid5 Encountered an unexpected error while checking the sector size for file ‘k:\mssql\MSSQL\data\tempdb.mdf’. Check the SQL Server error log for more information.
2007-02-13 15:05:01.62 spid11 Starting up database ‘Student’.
2007-02-13 15:05:01.72 spid10 Starting up database ‘Diners’.
2007-02-13 15:05:01.80 spid9 Starting up database ‘distribution’.
2007-02-13 15:05:02.10 spid8 Starting up database ‘Inventory’.
2007-02-13 15:05:02.72 spid11 Starting up database ‘NorthwindReportData’.
2007-02-13 15:05:02.80 spid10 Starting up database ‘TSQLDB’.
2007-02-13 15:05:02.83 spid9 Starting up database ‘SSEMDB’.
2007-02-13 15:05:03.57 spid5 CREATE DATABASE failed. Some file names listed could not be created. Check previous errors.
2007-02-13 15:05:03.57 spid5 WARNING: problem activating all tempdb files. See previous errors. Restart server with -f to correct the situation.


Making the service account a member of the administrators group, fixed the problem, but of course this is not a good idea. I decided to use the SysInternals File Monitor, filemon.exe, to see if I could find the cause of the problem. What I found was that mssql.exe was trying to access the root of the volume where the SQL data files exist and was getting an access denied error. The fix was to grant the service account list access to the root of that volume. With that change, the sqlserver.exe service came up and stayed up. I also found a Technet article that points to the same solution for a similar problem: PRB: Error 5177 May Be Raised When Creating Databases

By the way, SysInternals was recently acquired by Microsoft and Mark Russinovich is now a Microsoft Employee. It appears that the great SysInternals utilities continue to be maintained.

Posted in SQL Server | Print | No Comments »