Formatting FAT32 Volumes larger than 32 GB

For an exercise in (ahem) modding a games console, I needed to format an external USB drive to FAT32.
On doing so, I found I couldn't do so...

Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.


C:\Users\RMD>format g: /q /fs:fat32

The type of the file system is NTFS.
The new file system is FAT32.

WARNING, ALL DATA ON NON-REMOVABLE DISK
DRIVE G: WILL BE LOST!
Proceed with Format (Y/N)? y
QuickFormatting 238472M
The volume is too big for FAT32.

The solution I used was to find a tool that would perform the format.
Fat32Format by Ridgecrop Consultants is as good as any, i.e. it is fast and free.

C:\Users\RMD>fat32format g:

Warning ALL data on drive 'g' will be lost irretrievably, are you sure
(y/n) :y
Size : 250GB 488392002 sectors
512 Bytes Per Sector, Cluster size 32768 bytes
Volume ID is 1ee6:304a
32 Reserved Sectors, 59604 Sectors per FAT, 2 fats
7629261 Total clusters
7629260 Free Clusters
Formatting drive g:...
Clearing out 119304 sectors for Reserved sectors, fats and root cluster...
Wrote 61083648 bytes in 2.33 seconds, 25.05 Megabytes/sec
Initialising reserved sectors and FATs...
Done

C:\Users\RMD>

SQL 2012 on Windows 2012 : Install Stuck

My install of SQL Server 2012 on Windows 2012 hung today on

'Enabling OS feature 'NetFx3'

A little searching drove me to this blog post - Error while enabling Windows feature NetFx3

The instructions above show how to resolve it, but you need to add ".NET Framework 3.5 Features" as a feature via the Add Roles and Features Wizard.

Why this couldnt have been detected in the prerequisites part of the install check, I don't know....

SQL 2008 - Debugging failed backups

Here are some steps I followed today on SQL 2008 concerning some failed backups (not my server)...

Maintenance plan history

Viewing the history of the Maintenance plan shows 2 things

1. The Success/Failure of the plan
2. Which Task it has failed on

Detail concerning a failure is buried in a single line error message for each Maintenance Plan task.

Maintenance plan errors tend to obfuscate the true cause as a Maintenance Plan is an Integration Services wrapper around database management tasks.

 " Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.  End ...  The package execution fa...  The step failed. "

A SSIS developer would understand these errors, a DBA maybe not so.

If a task was backing up mulitple databases and the final one failed, there is a lot of text to wade through. Creating log files on our Maintenance plans is the way to go to log at this level.


SQL Server Agent History

Again the agent history hides the details we are after.
When a maintenance plan is scheduled, an agent job is created to run it.
The agent job has a parent entry with step ID 0 which reveals the job outcome.
The maintenance plan is implemented as a single step (step ID 1).
Even if a small part of the plan fails, the whole agent step (and parent job) are seen to fail.
The message output here is displayed over several lines and is more readable. It still lacks the detail we want however.


SQL Server Logs

These can be accessed via management studio or the event viewer.
Focusing here on one database, we can locate the reported cause much easier.

SQL Server Logs show 3 separate entries for each backup failure.

Date                       20/03/2013 9:17:03 AM
Log                          SQL Server (Current - 20/03/2013 9:42:00 AM)

Source                    Backup

Message
BACKUP failed to complete the command BACKUP DATABASE Obfuscated. Check the backup application log for detailed messages.

Generic Backup Failure Message, followed by the detail below

Date                      20/03/2013 9:17:03 AM
Log                         SQL Server (Current - 20/03/2013 9:42:00 AM)

Source                  Backup

Message
Error: 3041, Severity: 16, State: 1.

Yes, the backup has failed, we could search for Severity 16, State 1 if we wished.
The next step tells us more however.

Date                      20/03/2013 9:17:03 AM
Log                       SQL Server (Current - 20/03/2013 9:42:00 AM)

Source                  spid76

Message
BackupDiskFile::CreateMedia: Backup device 'R:\BACKUP\FULL\Obfuscated\Obfuscated_backup_2013_03_18_225452_3071857.bak' failed to create. Operating system error 5(failed to retrieve text for this error. Reason: 15105).

So we need to look up OS Error 5

From a command prompt, lookup the OS error message like this,

NET HELPMSG 5

" Access is denied " is fairly blatantly a security thing that I resolved by giving a more generous range set of permissions to the service account responsible for performing the backups.

I never did get to the bottom of how the permissions changed in the first place though. I suspect a change in group membership for the service account but I'll never be able to prove it.

TSQL : Find the largest table

A script to find the largest table in the current database.

SELECT  t.name, s.row_count
FROM sys.dm_db_partition_stats s
INNER JOIN sys.tables t
ON t.object_id = s.object_id
WHERE s.index_id < 2
ORDER BY s.row_count DESC

This works for SQL 2005 up ...

To do this in SQL 2000, you would have to use sp_spaceused and loop through all tables.

SQL Server 2012 : Offset / Fetch

SQL Server 2012 launches Offset/Fetch which provide server side paging of results sets.

A Simplified example is this -

SELECT
  [columnlist]
FROM
  [table]
ORDER BY [column]
OFFSET [offset] ROWS
FETCH NEXT [rowcount] ROWS ONLY;

The new options are market in red above, and are -

• [offset] - Position to start retrieving rows from.
• [rowcount] - How many rows to retrieve

You've been able to achieve this in the past with a little ingenuity as the table below shows.

SQL Server	Method	Links
2012	OFFSET & FETCH	New T-SQL features in SQL Server 2012 – OFFSET and FETCH OFFSET – FETCH in SQL Server 2012
2005	ROW_NUMBER()	T-SQL: Paging with ROW_NUMBER()
2000	SET ROWCOUNT Trick	A More Efficient Method for Paging Through Large Result Sets