Shrinking all online Database Files

In a previous post I described how to shrink a MSSQL log file of a specific database from the SQL prompt.

We can extend this example to iterate through all accessible databases (i.e. attached, online and not readonly) and execute the SHRINKFILE command on each file in these databases.

First we collect all databases and their files:

SET NOCOUNT ON
DECLARE @db NVARCHAR(128), @s NVARCHAR(128)

CREATE TABLE #files (db NVARCHAR(128), name NVARCHAR(128))

DECLARE c CURSOR FOR
SELECT    name
FROM    master..sysdatabases
WHERE    DATABASEPROPERTY(name, 'IsOffline') = 0
AND     DATABASEPROPERTY(name, 'IsReadOnly') = 0
AND     DATABASEPROPERTY(name, 'IsDetached') = 0
AND     DATALENGTH(sid) > 1
ORDER BY name

OPEN c
FETCH c INTO @db

WHILE @@FETCH_STATUS = 0 BEGIN

    PRINT N'scanning ' + @db
    SET @s = N'USE ' + @db +
        N' SELECT ''' + @db + ''', RTRIM(name) FROM sysfiles'

    INSERT INTO #files
    EXEC (@s)

    FETCH c INTO @db
END

CLOSE c
DEALLOCATE c

Table #files now contains the names of the databases and their files.

Next we need to issue a USE database plus the DBCC SHRINKFILE commands:

DECLARE c CURSOR FOR
SELECT    db, name
FROM    #files
ORDER BY db, name

OPEN c
FETCH c INTO @db, @s

WHILE @@FETCH_STATUS = 0 BEGIN

    SET @s = N'
USE ' + @db + N' dbcc shrinkfile (' + @s + N', truncateonly)'
    PRINT @s
    EXEC (@s)

    FETCH c INTO @db, @s
END

CLOSE c
DEALLOCATE c

DROP TABLE #files

Updating graspx (version 0.15)

I fixed a bug generating the correct identifier of asp:ListItems when using the -nc (naming container) option.

The latest version of graspx if available for download here.

Building Visual Studio Solutions from Batch File

In my previous post, Building Visual Studio Solutions from the Command Line, I described the basic steps to be performed when one of my projects is to be built completely, or code needs to be generated.

This post gives a more detailed view of what is going on in the batch file. (Each batch file needs to be adjusted to the project it is used for in terms of functionality)

Handle batch parameters

Each function of the batch file can be invoked by a separate parameter, and a list of variables needs to keep track of whether the function is activated:

set dogenc=
... more variables

If no parameters are passed, show the help screen:

if not "%1"=="" goto params

echo.
echo parameters:
echo.
echo genc ... generate C#
... more info
goto end

Parse each command line parameter:

:params
if "%1"=="" goto endparams

if "%1"=="genc" (set dogenc=1& shift & goto params)
... more parameters
if "%1"=="all" (shift & set dogenc=1 & ... & set doall=1 & goto params)

echo unknown parameter "%1"
goto endend

:endparams

Add code to handle the various functions, such as checking, building, publishing, as described in the previous post.

Building Setup Projects

As msbuild does not build Setup Projects, we have to take a different approach:

"C:\path to\Microsoft Visual Studio 8\Common7\IDE\devenv"
    C:\path to\project.sln /build "Debug"
    /project C:\path to\setup.vdproj /projectconfig "Debug"

Generating Database Script

SQL Server 2000 provides a simple tool to create a SQL script with the definition of all database objects called scptxfr.

"C:\path to\Microsoft SQL Server\MSSQL\Upgrade\scptxfr"
    /s [server] /d [database] /p [password for sa] /f [filename] /a /h

Zip All

Finally, source code, executables, generated scripts and so on are zipped into version-specific archives using 7-zip, which can be controlled from the command line.

Summary

The batch file for my first project of this kind provides the following functionality:

• generate table triggers based on metamodel information
• generate C# constant definitions
• build executables (web, service)
• publish web applications
• build setup projects
• backup database (both as backup and as CREATE script)
• generate wiki information for developers
• generate wiki page stubs for online help
• create version-specific zip files of everything

Shrinking SQL Server Log Files

This script performs a truncate log operation followed by a shrink log file operation.

First it selects the current database name for the parameter of the BACKUP LOG command. Then the filename of the log file of the current database is retrieved to pass it to the SHRINKFILE command.

select * from sysfiles

declare @s nvarchar(100)

print 'truncating ' + db_name()
set @s = 'backup log ' + db_name() + ' with truncate_only'
exec (@s)

select @s = rtrim(name) from sysfiles where groupid = 0
print 'shrinking ' + @s
set @s = 'dbcc shrinkfile (' + @s + ', truncateonly)'
exec (@s)

select * from sysfiles

Recovering defective DVDs

I recently noticed that some DVD players refuse to play a DVD if part of a recording of it is unreadable (even though you wanted to watch a different recording on the same disc).

Time to check DVD quality: Nero CD-DVD Speed provides scanning and testing functionality, but does not help you in recovering partially damaged files.

I found this blog entry on recovering data files from defective discs, which lists a couple of solutions: Unstoppable Copier, CDCheck, and IsoBuster.

I tried IsoBuster, and as it comes with a zillion options, so I trusted the default values. Once it hit an unreadable sector, it asked whether to replace the sector with zeros or random data, and optionally applies your answer to any subsequent defective sector. I selected zeros, and hope that DVD players are clever enough to handle the zero parts.

Extended Functionality in graspx

To fine-tune the steps to automatically build a Visual Studio solution, I needed to implement some long-planned features: One of the targets in writing graspx was to extract all displayed text from the various controls on each ASP.Net form.

UI texts can be stored in a wide array of different controls and their attributes: label Text, hyperlink innerText, page Title, gridview EmptyDataText, validators ErrorMessage, and so on.

To make things more complicated, a control may not be found differently by its ID, but rather one needs to walk the form’s control hierarchy, if a control is placed within a FormView or GridView. Some texts may even be stored in a tag without an identifier, as is the case with ListItems in a DropDownList, or Columns within a GridView.

The new functionality in graspx covers these cases:

The LL command lists the value of all tag/attribute combinations listed in a separate parameter file, and thus equals the sequential execution of single L commands (uppercase “L” is used here for clarity).

The option -nc allows to define naming containers. If a control is found, the ID of the control is composed of the IDs of the parent controls. As an example from the setup files:

asp:GridView    .id
Columns         ix
asp:FormView    .id
ItemTemplate    tag
asp:Content     .ContentPlaceHolderID

For a GridView, the value of the ID attribute is used as naming ID, whereas for the Content (masterpage mechanism), it is ContentPlaceHolderID. Columns within a GridView are addressed by their index. A FormView may hold 3 different templates which are distinguished by their tag.

The working directory, which was the current directory in previous versions, can be set using the -d option; -r allows recursive searches through subdirectories.

The -nodyn option excludes all dynamic expressions ( < % # … % > ) from the search result.

The -utf8 option forces output in UTF8 encoding, which is not the default even for .Net console applications.

graspx is available for download here.