Shrinking MS SQL database

Here is an easy way to shrink the log file of the a MS SQL 2000 or 2005 database.

The simplest way seems to be to use Enterprise Manager if your using SQL 2000 or SQL Server Management Studio if your using SQL 2005 to use the UI. The quickest is probably to copy and paste the SQL though. You can decide.

Using the UI
SQL Server Studio Management
1. Change Recovery Mode to Simple by getting properties on Database Options. Then choose Simple for Recovery model.
2. It can be found by right clicking on the database and choosing tasks. Choose Shrink and then Files. Be sure to select the File type as 'Log'
3. Change Recovery Mode to Full by getting properties on Database Options. Then choose Full for Recovery model.


Using SQL
USE MyDatabase;
GO

-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE MyDatabase
SET RECOVERY SIMPLE;
GO

-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE ('MyDatabase_Log', 1);
GO

-- Reset the database recovery model.
ALTER DATABASE MyDatabase
SET RECOVERY FULL;
GO

NOTE: MyDatabase_Log is by default correct. However, if a database has been renamed by default the Logical file names don't get updated. So, likely if the MyDatabase was renamed to MyDatabaseOld the Logical file name (the first parameter to DBCC SHRINKFILE) would still be MyDatabase. The name can be verified usign MS SQL Server Management Studio for SQL 2005 or Enterprise Manager for SQL 2000.

NOTE: This script can be executed from MS SQL Server Management Studio even if the database is on a SQL 2000 instance.

NOTE: If the Logical File name has an & in it you will need to put single quotes around the value as noted above. Otherwise, the single quotes are optional.

NOTE: If the log file doesn’t shrink, you may need to put the database in single user mode (under Properties | Options). Then run the DBCC SHRINKFILE command. 

If all else fails and you get desperate, you can detach your database (you may need to put it in single user mode first especially if you are out of disk space), then manually go to the file system and manually delete the log file. Then attach the database again; a new log file will be created.

WARNING: With any of this, you will lose the log of transactions since you have deleted the transaction log.

You may also want to check out my entry on how to truncate the transaction log if the above isn’t working for you.

ISAPI Extensions and Filters

What are they? Basically, they are ways to extending Internet Information Services (IIS). They take two different approaches. An ISAPI extension is much like a cgi or .net aspx page, but implemented at a much more low level using something like c or c++. It basically requires that you implement a few methods. The extension is accessed only when a url explicitly requests it. (Actually, in IIS 6 or greater it can be accessed the same way as a filter also. by using HSE_REQ_EXEC_URL.) It would typically reside in the /scripts directory. So an example would be: http://myth/articles/scripts/validate.dll?123456789012543 An ISAPI filter is much like writing an HttpModule for .net, except that it is called for every hit to the web site / or server if configured as such. It basically hooks into all requests to the web site or server and is called prior to .net framework call. Dev Tips: Testing Changes The fastest way to test your changes is to configure the filter IIS to point to your Debug version of the DLL. The best way to build when configured this way seems to be to keep the Services (from Administive Tools on the Start menu) open. When you want to build and test change do the following: 1. Stop the World Wide Web Publishing service. 2. Build (in Visual Studio, etc). 3. Start the World Wide Web Publishing service. 4. Attach to inetinfo.exe if you want to debug 5. Set breakpoint where desired. 6. Hit Url in IE. What methods can I implement? If you look in CHttpFilter class, you will see: virtual DWORD HttpFilterProc(PHTTP_FILTER_CONTEXT pfc, DWORD dwNotificationType, LPVOID pvNotification); virtual BOOL GetFilterVersion(PHTTP_FILTER_VERSION pVer); virtual DWORD OnReadRawData(CHttpFilterContext* pfc, PHTTP_FILTER_RAW_DATA pRawData); virtual DWORD OnPreprocHeaders(CHttpFilterContext* pfc, PHTTP_FILTER_PREPROC_HEADERS pHeaders); virtual DWORD OnAuthentication(CHttpFilterContext* pfc, PHTTP_FILTER_AUTHENT pAuthent); virtual DWORD OnUrlMap(CHttpFilterContext* pfc, PHTTP_FILTER_URL_MAP pUrlMap); virtual DWORD OnSendRawData(CHttpFilterContext* pfc, PHTTP_FILTER_RAW_DATA pRawData); virtual DWORD OnLog(CHttpFilterContext* pfc, PHTTP_FILTER_LOG pLog); virtual DWORD OnEndOfNetSession(CHttpFilterContext* pfc); virtual DWORD OnEndOfRequest(CHttpFilterContext* pfc); virtual DWORD OnAuthComplete(CHttpFilterContext* pfc, PHTTP_FILTER_AUTH_COMPLETE_INFO pAuthComplInfo); virtual DWORD OnSendResponse(CHttpFilterContext*, PHTTP_FILTER_SEND_RESPONSE); virtual DWORD OnAccessDenied(CHttpFilterContext*, PHTTP_FILTER_ACCESS_DENIED); Where do I find out what the events that I can use and what they do? http://msdn2.microsoft.com/en-us/library/ms524855.aspx Where do I find a list of the IIS Server variables that I can use and what they are? http://msdn2.microsoft.com/en-us/library/ms524602.aspx Where can I get a simple example? http://www.codeproject.com/isapi/isapiredirector.asp

Converting String to / from Date using Custom Formatting

In Java it is easy to convert a date as text / string to a Date object. String to Date object Date dateObj = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss").parse("05/18/05 18:15:10"); Date object to String String dateStr = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss").format(dateObj);

Exporting Business Rules from HP OpenView Service Desk to Excel

Using the HP OpenView Service Desk client open the Administrator Console. Then navigate to the Business Logic node. Expand that portion of the tree to show the Database Rules and UI Rules nodes. Click on which ever set of rules you would like to export. Go to the File menu, change Report Style to Table or better yet a Bordered Table. Then choose Print Preview from the same File menu. Click or highlight some of the text in the preview. Then do a control-A and then control-C to select all text and then copy. Go to MS Excel, open a new worksheet, and paste. You will need to make it pretty, but all the data is there. I recommend setting the column height to something like 12.75 if you like the default height that you usually get from a new Excel spreadsheet.

Oracle SQLPlus number of digits to show

Do you ever want to see a really long number in an Oracle column, but SQLPlus prints out something like 2.8151+14 instead of 281510522718928? Simple fix for this one. Once per connection, just type: set num 20 That will allow up to 20 digits to be displayed. Change 20 to whatever you like, but 20 is usually larger than the number of digits I need to see.

HP Open View Service Desk web-api maybe long, but isn't Long

If the title of this blog is confusing, you may also find this blog confusing not because the content is confusing, but because you won't be able to understand what the developers that designed web-api were thinking. HP Open View Service Desk (OVSD) web-api is a set of java api's that have little to do with the web as far as I can tell. It is an api for OVSD that is implemented in Java and jarred. No big deal there, just a strange name. OVSD has a concept of OID and ID for some records like Service Calls. ID is what the unique id that is shown in the UI that end users use. OID is the unique ID that is used in Oracle for relationships. My big conundrum is what were the developers thinking when they created two methods to open (find and load) and service call. The two methods are called openServicecall(). It is overloaded to accept a Long or a long. Long is an object in Java, and long is a primitive in Java. Very different things. One might think that the developers were nice and just provided the ability to pass either an object or primitive. This would make sense, but is not what they did. They made long mean ID and Long mean OID. Only place to get that is in the documentation. Below is the correct snippet for opening a service call by ID (the end user value). ApiSDSession session = null; try { session = ApiSDSession.openSession(server, username, password); IServicecallHome scHome = session.getServicecallHome(); long id = 123456; // NOTE: long MUST be used, NOT Long IServicecall serviceCall = scHome.openServicecall(id); } catch (Exception ex) { // handle exception here } finally { session.closeConnection(); } I hope this saves someone hours of frustration.

SharePoint Recycle Bin

Can you believe it? SharePoint Windows SharePoint Services and SharePoint Portal Server 2003 do NOT come with a way to recover files deleted from the document libraries except restore the entire WSS or SPS site(s) depending how you backed up SharePoint. This requires an installation of SharePoint. Worse yet, this can be extremely time consuming. As it turns out Microsoft heard the cry and came up with a very nice solution. They felt it was needed so much that they published it via gotdotnet workspaces (http://www.gotdotnet.com/Workspaces/Workspace.aspx?id=8437a203-f377-401c-b23d-ae59e6f05b80). The solution is called Recycle Bin. There is even a very detailed installation guide. The installation is tedious, but straight forward and actually takes under 45 minutes from start to finish. The solution does have a way for end users to retrieve the deleted files, but this can be done using a web part or just have your SharePoint administrator get the file from the windows directory (this is where the files end up when they are deleted once you install this). There are some other solutions, but I think this is the best for the money (free) and it doesn't void any support with Microsoft since it doesn't modify the database directly. Here are some other options to look at though:

• Dark Blue Duck Recycle Bin Enabler: http://www.darkblueduck.com/Products/RecycleBinEnablerWSSTRIAL.aspx ($1200++)
• Developer type solution from MSDN: http://msdn.microsoft.com/msdnmag/issues/05/02/RecycleBinforWSS/default.aspx (FREE)
• Bryant Like's Blog describes (voids support with MS): http://blogs.sqlxml.org/bryantlikes/archive/2005/02/14/2776.aspx (FREE)