Shrink all SQL Server logfiles

After backing up the log files on a SQL server, the logs are truncated and you might want to reclaim the disk space held by those empty log files. This little Transact-SQL script will do just that.

DBCC SQLPERF(logspace)
GO
DECLARE @logsize AS INT = 256; -- MB
DECLARE @databasename AS VARCHAR(256);
DECLARE @filename VARCHAR(256);
DECLARE @cmd VARCHAR(MAX);
DECLARE curdb CURSOR FOR
    SELECT f.name AS filename, d.name AS databasename
    FROM msdb.sys.master_files f
        INNER JOIN master.sys.sysdatabases d
        ON d.dbid = f.database_id
    WHERE type = 1 AND state = 0 AND size > @logsize AND database_id > 4
OPEN curdb
FETCH NEXT FROM curdb INTO @filename, @databasename
WHILE @@FETCH_STATUS = 0
BEGIN
    --PRINT @filename
    SET @cmd = ('USE [' + @databasename + ']; ')
    SET @cmd = @cmd + 'DBCC SHRINKFILE ([' + @filename + '], ' + CAST(@logsize AS VARCHAR) + ');';
    EXEC (@cmd)
    FETCH NEXT FROM curdb INTO @filename, @databasename
END
DEALLOCATE curdb
GO
DBCC SQLPERF(logspace)
GO

Minimalist’s build script for ASP.NET projects

When deploying ASP.NET web sites you have a number of options ranging from full blown (up) MSI installers, MSDeploy or the KISS option: xcopy deployment. If the target IIS set up this is a very clean way to redistribute your application.

First download a 7za.exe archiver and put it in your solution root. Then create a make.bat file in your web project (build action = none) and paste the following section in it.

FOR %%A IN (%Date%) DO SET today=%%A
set archive=NextBigThing-%today%.zip
set z=..\7za.exe
cd ..
del %archive%
echo Creating %archive%
%z% a -tzip -r -mx9 %archive% -x!obj\*.* favicon.ico global.asax robots.txt
   web.config App_Data Bin\*.dll Content\*.png
   Content\*.gif Content\site.min.css Scripts\site.min.js Views\*.cshtml
   Views\*.config

Finally add the following line to your web project’s Post-build event command line property:

..\make

Run the build again and you will be granted with a zip file named with today’s date containing the files needed to run your application.

Note you might need to tweak the last line in the script to include the files your app needs.

SharePoint List Performance (or how I saved 94%)

In a recent performance test session, I had created a thousand documents in a document library. They all had a 20 custom metadata fields filled out with data. The unit under test read all this data along with the common file information like this:

SPUser user = listItem.File.CheckedOutByUser;
result.CheckedOutBy = user != null ? user.LoginName : null;
result.Author = new SPFieldUserValue(m_web, (string)row["Author"]).User.LoginName;
result.Editor = new SPFieldUserValue(m_web, (string)row["Editor"]).User.LoginName;
result.Link = m_serverUrl + listItem.File.ServerRelativeUrl;
result.Version = listItem.File.UIVersionLabel;

Using SPList.GetItems(SPQuery) it took 5042 mS. The data was used in request/response scenario so this delay was totally unacceptable as this obviously meant the user should be waiting for five seconds while SlugPoint™ was digging through the list.

First thought was to avoid the repeatedly creation of SPUser instances for the first three properties. Usually only a dozen users creates all the documents so a bit of caching can be applied with this class:

class UserCache
{
    SPWeb m_web;
    Dictionary<string, string> m_map = new Dictionary<string, string>();

    public UserCache(SPWeb web)
    {
        m_web = web;
    }

    public string GetLoginName(string fieldValue)
    {
        if (string.IsNullOrEmpty(fieldValue))
            return null;
        string loginName;
        if (!m_map.TryGetValue(fieldValue, out loginName))
        {
            loginName = new SPFieldUserValue(m_web, fieldValue).User.LoginName;
            m_map.Add(fieldValue, loginName);
        }
        return loginName;
    }
}

Replacing the first couple of lines in the original code with the following code snippet reduced the execution time to 3017 mS saving 42%.

m_userCache = new UserCache(web);
:
result.CheckedOutBy = m_users.GetLoginName("CheckoutUser");
result.Author = m_users.GetLoginName("Author");
result.Editor = m_users.GetLoginName("Editor");

The Link property can be resolved by reading the FileRef field directly instead of creating the SPFile instance. Replacing that line with the following reduced the execution time to 2903 mS saving a further 2%.

result.Link = m_serverUrl + listItem[SPBuiltInFieldId.FileRef];

The last line looks much like the preceding one, so I was expecting a similar saving when replacing it with:

result.Version = (string)listItem["_UIVersionString"];

Instead the operation now took 286 mS a further saving of 52%, which means it now only took 6% of the original processing time. I did not have the time to Reflector the SPFile.UIVersionLabel property but will propably stay away from that one in the near future.

My conlusion is for performance you should pull values from the list item fields directly instead of reading properties from the object model.

DecoratorStream

In scenarios where you want to extend functionality of a simple System.IO.Stream instance, it is convenient to have a decorator. Neither the original stream or the client instance will have to know anything about the extension as it is still just a Stream.
This can be used to add position tracking or to manage lifetime. For instance you might want to dispose a System.Net.HttpWebResponse instance that created the stream when the stream is being closed.
The most straightforward way to achieve this is to copy/paste the code below and inherit that class to create your stream extender. Of course you could also have done this with a dynamic proxy plus interceptor.

abstract class DecoratorStream : Stream
{
    Stream m_stream;

    protected DecoratorStream(Stream stream)
    {
        m_stream = stream;
    }

    public override int ReadByte()
    {
        return m_stream.ReadByte();
    }

    public override int Read(byte[] buffer, int offset, int count)
    {
        return m_stream.Read(buffer, offset, count);
    }

    public override long Seek(long offset, SeekOrigin origin)
    {
        return m_stream.Seek(offset, origin);
    }

    public override IAsyncResult BeginRead(byte[] buffer, int offset, int count, AsyncCallback callback, object state)
    {
        return m_stream.BeginRead(buffer, offset, count, callback, state);
    }

    public override IAsyncResult BeginWrite(byte[] buffer, int offset, int count, AsyncCallback callback, object state)
    {
        return m_stream.BeginWrite(buffer, offset, count, callback, state);
    }

    public override bool CanRead
    {
        get { return m_stream.CanRead; }
    }

    public override bool CanSeek
    {
        get { return m_stream.CanSeek; }
    }

    public override bool CanTimeout
    {
        get
        {
            return m_stream.CanTimeout;
        }
    }

    public override bool CanWrite
    {
        get { return m_stream.CanWrite; }
    }

    public override void Close()
    {
        m_stream.Close();
    }

    public override int EndRead(IAsyncResult asyncResult)
    {
        return m_stream.EndRead(asyncResult);
    }

    public override void EndWrite(IAsyncResult asyncResult)
    {
        m_stream.EndWrite(asyncResult);
    }

    public override void Flush()
    {
        m_stream.Flush();
    }

    public override long Length
    {
        get { return m_stream.Length; }
    }

    public override long Position
    {
        get
        {
            return m_stream.Position;
        }
        set
        {
            m_stream.Position = value;
        }
    }

    public override int ReadTimeout
    {
        get
        {
            return m_stream.ReadTimeout;
        }
        set
        {
            m_stream.ReadTimeout = value;
        }
    }

    protected override void Dispose(bool disposing)
    {
        try
        {
            m_stream.Dispose();
        }
        finally
        {
            base.Dispose(disposing);
        }
    }

    public override void SetLength(long value)
    {
        m_stream.SetLength(value);
    }

    public override void Write(byte[] buffer, int offset, int count)
    {
        m_stream.Write(buffer, offset, count);
    }

    public override void WriteByte(byte value)
    {
        m_stream.WriteByte(value);
    }

    public override int WriteTimeout
    {
        get
        {
            return m_stream.WriteTimeout;
        }
        set
        {
            m_stream.WriteTimeout = value;
        }
    }
}

JavaScript commas in IE

While working with JQuery UI DataTables I got some strange JS errors in IE. And not from any-better-browser.

'style' is null or not an object

My Google cortex provided the answer: IE javascript engine treats array/object initialization different from other engines.
While other browsers simply ignores if the initialization has a trailing comma, IE don’t.
Searching through code to find commas ain’t my kind of fun, so the following regular expression will find the instances for you.

,\s*?[\}\]]

I use Derek Slager’s better regex tester regularly (pun intended).

Follow

Get every new post delivered to your Inbox.