Quick & Dirty way to identify orphan files

7. January 2010 by admin.

Here is a quick and dirty script to identify orphan database files - that is, files that are no longer in the system catalog. It uses several undocumented stored procedures and is not particularly clean SQL, but it works.

To do: come up with a version that uses LogParser to do the same thing.

DECLARE @SQL VARCHAR(MAX)
CREATE TABLE #temp1 (DRIVE CHAR(1), MB_FREE INT)
INSERT INTO #temp1 (DRIVE, MB_FREE)
EXEC xp_fixeddrives
CREATE TABLE #TEMP2(SUBDIRECTORY VARCHAR(256),
DEPTH INT, [FILE] INT, FULL_PATH VARCHAR(256), COMMAND VARCHAR(512))
SELECT @SQL=COALESCE(@SQL,”)+ ‘INSERT INTO #TEMP2 (SUBDIRECTORY, DEPTH, [FILE]) EXEC Master.dbo.xp_DirTree “‘ + DRIVE +
‘:\MSSQL\DATA”, 1, 1; UPDATE #TEMP2 SET FULL_PATH = ”’ + DRIVE + ”’ + ”:\MSSQL\DATA\” + SUBDIRECTORY WHERE FULL_PATH IS NULL; ‘
FROM #temp1
PRINT @SQL
EXEC (@SQL)
DELETE
FROM #TEMP2
WHERE [FILE] = 0
OR SUBDIRECTORY LIKE ‘%CER’
OR SUBDIRECTORY LIKE ‘MSSQLSYSTEMRESOURCE%’
OR SUBDIRECTORY LIKE ‘distmdl%’
OR FULL_PATH IN
(
SELECT PHYSICAL_NAME
FROM SYS.MASTER_FILES
)
SELECT FULL_PATH, ‘DEL ‘ + FULL_PATH AS COMMAND
FROM #TEMP2
DROP TABLE #temp1
DROP TABLE #TEMP2

Posted in Uncategorized | Print | 1 Comment »

Moving Master and Resource databases

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 »

Converting an existing Perfmon .BLG file to CSV

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 »

Moving

3. April 2008 by admin.

Yahoo 360 is a lame duck, so I’m moving my postings over to this web site.

Posted in Uncategorized | Print | No Comments »

Retina WiFi Scanner

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 »

Earlier Postings

11. February 2007 by admin.

…stuff imported from an earlier journal

---

---

2/3/07  I stumbled across a number of useful SQL security tools at SQLSecurity.com: http://www.sqlsecurity.com/Tools/FreeTools/tabid/65/Default.aspx

---

1/23/07  I ran into a problem configuring SQL XML support in IIS 6.0. When I went to do a query It was coming back with a 404 (page not found). The IIS log entry looks like this:

2007-01-24 01:04:07 127.0.0.1 GET /Northwind sql=select+*+from+Customers+For+XML+Auto&root=Customers 80 MACBETH\Administrator 127.0.0.1 Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+5.2;+.NET+CLR+1.1.4322;+.NET+CLR+2.0.50727) 404 2 1260

The last part of the string, ‘1260′ is the Win32 status. Doing a ‘net helpmsg 1260 ‘ from the command prompt shows a problem with policies:

Windows cannot open this program because it has been prevented by a software restriction policy.

Doing some searching, I found the following useful articles:

HTTP 404.x-File or Directory Not Found (IIS 6.0)
http://www.microsoft.com/technet/prodtechnol/WindowsServer2003/Library/IIS/0f4ac79a-dc2b-4a5f-89c1-d57266aa6ffe.mspx?mfr=true

IIS 6.0: ASP.NET Is Not Automatically Installed on Windows Server 2003
http://support.microsoft.com/kb/332124/

“HTTP Error 404 - File or Directory not found” error message when you request dynamic content with IIS 6.0
http://support.microsoft.com/?id=315122

Enabling Web Service Extensions (IIS 6.0)
http://www.microsoft.com/technet/prod … 2f-b029-aea2b0418bea.mspx

---

12/15/2006  I finally caught up with the times and replaced my AC97 audio with a SoundBlaster Audigy. The difference is quite noticeable. I also recently discovered EAC or Exact Audio Copy, a freeware program that accurately transcribes your audio CDs to WAV files. The combination of EAC and a decent sound card essentially turns your PC into a high-end CD player.

---

11/15/06  I purchased my fourth pneumatic nailer. This beauty is a framing nailer that shoots 2″ to 3 1/2″ nails. It makes quick work of many tasks around the home. By the way, did you know that “he who dies with the most tools wins”?

---

11/3/06 Today is my last day at Citigroup and two days after my 7th anniversary. Company management decided to eliminate the Diners Club systems development group based here in Denver. My job was among 168 of those affected.  It’s a shame because it was probably the best software development team within Citigroup.

---

7/15/06   I have decided to install security cameras around the house. One thing that I needed was connectors for 75 ohm RG6 cable. Most RG6 connectors are the ‘F’ style, while most security cameras use BNC connectors. I also needed replacement solder-tab batteries for our Interplak toothbrushes. After checking with several suppliers, I placed my order with All Electronics Wednesday evening. The parts arrived via USPS Saturday afternoon — very quick turnaround!

---

6/25/06  We have to relocate our gas meter. The utility and the building department have pretty stringent requirements for this, so Friday I took the Denver homeowner’s plumbing exam and pulled a plumbing permit. The plumbing inspection requires that you pressurize the gas line to 10 PSI and connect a gauge. You must demonstrate to the inspector that the gas piping can maintain pressure for at least 15 minutes. Not having a suitable gauge or test fixture, I went General Hardware, purchased a few parts and the built one shown here. The parts include a 0-30 psi gauge, a Schrader valve for applying and bleeding the compressed air, a 3/4″ tee, and the necessary reducers for the gauge and the Schrader valve.

---

6/19/06  A colleague recommended a really useful site for finding useful security tools: http://sectools.org/.  Fyodor, the creator of Nmap, and the maintainer of the site http://www.insecure.org, surveyed security professionals and created a listing of the 100 most popular security tools.  Many of these tools have multiple uses. For example, the Paros proxy is a useful tool if you maintain and troubleshoot web sites.

---

6/1/06  Are your favorite troubleshooting tools not installed on the machine that you are currently working on? One solution is to install your applications on a USB drive a site called PortableApps.com has a number of applications that have been built to run off of a USB or CDROM drive.

---

4/20/06  Hooray for the retarded kid! I finished up the OS course and took a DANTES exam. Only 7 semester hours to go!

---

2/21/06  Two useful measures of energy density are Watt hours/Liter (volumetric energy density) or Watt hours/kilogram (gravimetric energy density).  A gallon of gasoline has an energy density of 9000 watt hours per liter while a lead acid battery has an energy density of 40 watt hours per liter.  This helps explain why electric cars are not yet common.
Liquid hydrogen has a substantially lower volumetric energy density than gasoline, only 2600 watt hours per liter.  Don Lancaster has an excellent tutorial on energy fundamentals: http://tinaja.com/glib/energfun.pdf.

---

1/6/06  Downloaded and installed GIMP on Linda’s XP computer. GIMP stands for GNU Image Manipulation Program. It seems to be a nice alternative to Photoshop.

---

1/2/06  Hooray for the retarded kid!  I’m finishing up my last fourteen semester hours of undergraduate work.  Today I am starting a course on operating systems.

---

12/28/05  Used an updated version of a familiar utility autoruns.exe from the sysinternals.com website.  There are all sorts of places within Windows that a program can be automatically launched.  This includes services as well as programs launched as the logged-in user.  These guys seemed to have figured it all out.

---

12/27/05  ps command usage on AIX:

• to display all processes with memory utilization use ps gv
• to display all processes with memory utilization and command line ps gev

Interesting options from the man page: (none of these are preceeded by a - sign)

• e  Displays the environment as well as the parameters to the command, up to a limit of 80 characters.
• ew  Wraps display from the e flag one extra line.
• eww  Wraps display from the e flag as many times as necessary.
• g  Displays all processes.
• u  Displays user-oriented output. This includes the USER, PID, %CPU, %MEM, SZ, RSS, TTY, STAT, STIME, TIME, and COMMAND fields.
• v  Displays the PGIN, SIZE, RSS, LIM, TSIZ, TRS, %CPU, %MEM fields.
• w  Specifies a wide-column format for output (132 columns rather than 80). If repeated, (for example, ww), uses arbitrarily wide output. This information is used to decide how much of long commands to print.

---

12/5/05  Grep Examples (in a Win32 environment)

• Search a file for lines containing “cat” or “dog”:   grep -i “cat\|dog” file.txt    in this case the escaped | symbol is a logical OR operator
• Search a file for lines containing both “cat” and “dog”:   grep “cat” file.txt | grep “dog”  in this second case, the | symbol is used for redirection

---

11/18/05:   Found a useful Win32 utilities site. http://www.nirsoft.net/ Of particular note: MyUninstaller. Way more useful that the stock add/remove programs control panel applet.11/17/05: The Unix chsh utility can be used to change your working shell, but

• This will only work if you have permissions to the passwd file
• BOKS managed systems periodically push down the passwd file, which will overwrite your changes

Posted in Uncategorized | Print | 1 Comment »