Technical2

Microsoft Office 2010 look:

For Cloud Computing it is

http://apps.gov
https://www.cloudkick.com

SAP NetWeaver 7.1 - MDM Links

http://www.sdn.sap.com/irj/sdn/nw-mdm

How to set event log security locally or by using Group Policy in Windows Server 2003

Windows Server 2003 permits administrators to customize security access rights to their event logs. These settings can be configured locally or through Group Policy. This article describes how to use both of these methods.

You can grant users one or more of the following access rights to event logs:

• Read
• Write
• Clear

Important You can configure the security log in the same way. However, you can change only Read and Clear access permissions. Write access to the security log is reserved only for the Windows Local Security Authority (LSA).

Configure Event Log Security Locally

Important This section, method, or task contains steps that tell you how to modify the registry. However, serious problems might occur if you modify the registry incorrectly. Therefore, make sure that you follow these steps carefully. For added protection, back up the registry before you modify it. Then, you can restore the registry if a problem occurs. For more information about how to back up and restore the registry, click the following article number to view the article in the Microsoft Knowledge Base:

How to back up and restore the registry in Windows

The security of each log is configured locally through the values in the following registry key:

HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\Eventlog

For example the Application log Security Descriptor is configured through the following registry value:

HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\Eventlog\Application\CustomSD

And the System log Security Descriptor is configured through the following:

HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\Eventlog\System\CustomSD

The Security Descriptor for each log is specified by using Security Descriptor Definition Language (SDDL) syntax. For more information about SDDL syntax, see the Platform SDK, or visit the Microsoft Web site mentioned in the "References" section of this article.

To construct an SDDL string, note that there are three distinct rights that pertain to event logs: Read, Write, and Clear. These rights correspond to the following bits in the access rights field of the ACE string:

• 1= Read
• 2 = Write
• 4 = Clear

The following is a sample SDDL that shows the default SDDL string for the Application log. The access rights (in hexadecimal) are bold-faced for illustration:

O:BAG:SYD:(D;; 0xf0007;;;AN)(D;; 0xf0007;;;BG)(A;; 0xf0007;;;SY)(A;; 0x5;;;BA)(A;; 0x7;;;SO)(A;; 0x3;;;IU)(A;; 0x2;;;BA)(A;; 0x2;;;LS)(A;; 0x2;;;NS)

For example, the first ACE denies Anonymous Users read, write, and clear access to the log. The sixth ACE permits Interactive Users to read and write to the log.

Modify Your Local Policy to Permit Customization of the Security of Your Event Logs

1. Back up the %WinDir%\Inf\Sceregvl.inf file to a known location.
2. Open %WinDir%\Inf\Sceregvl.inf in Notepad.
3. Scroll to the middle of file, and then put the pointer immediately before [Strings].
4. Insert the following lines:
   MACHINE\System\CurrentControlSet\Services\Eventlog\Application\CustomSD,1,%AppLogSD%,2
   
   MACHINE\System\CurrentControlSet\Services\Eventlog\System\CustomSD,1,%SysLogSD%,2
5. Scroll to the end of the file, and then insert the following lines:
   AppLogSD="Event log: Specify the security of the application log in Security Descriptor Definition Language (SDDL) syntax"
   
   SysLogSD="Event log: Specify the security of the System log in Security Descriptor Definition Language (SDDL) syntax"
6. Save and then close the file.
7. Click Start, click Run, type regsvr32 scecli.dll in the Open box, and then press ENTER.
8. In the DllRegisterServer in scecli.dll succeeded dialog box, click OK.

Use the Computer's Local Group Policy to Set Your Application and System Log Security

1. Click Start, click Run, type gpedit.msc, and then click OK.
2. In the Group Policy editor, expand Windows Setting, expand Security Settings, expand Local Policies, and then expand Security Options.
3. Double-click Event log: Application log SDDL, type the SDDL string that you want for the log security, and then click OK.
4. Double-click Event log: System log SDDL, type the SDDL string that you want for the log security, and then click OK.

Use Group Policy to Set Your Application and System Log Security for a Domain, Site, or Organizational Unit in Active Directory

Important: To view the group policy settings that are described in this article in the Group Policy editor, first complete the following steps, and then continue to the "Use Group Policy to Set Your Application and System Log Security" section:

1. Use a text editor such as Notepad to open the Sceregvl.inf in the %Windir%\Inf folder.
2. Add the following lines to the [Register Registry Values] section:
   MACHINE\System\CurrentControlSet\Services\Eventlog\Application\CustomSD,1,%AppCustomSD%,2
   MACHINE\System\CurrentControlSet\Services\Eventlog\Security\CustomSD,1,%SecCustomSD%,2
   MACHINE\System\CurrentControlSet\Services\Eventlog\System\CustomSD,1,%SysCustomSD%,2
   MACHINE\System\CurrentControlSet\Services\Eventlog\Directory Service\CustomSD,1,%DSCustomSD%,2
   MACHINE\System\CurrentControlSet\Services\Eventlog\DNS Server\CustomSD,1,%DNSCustomSD%,2
   MACHINE\System\CurrentControlSet\Services\Eventlog\File Replication Service\CustomSD,1,%FRSCustomSD%,2
3. Add the following lines to the [Strings] section:
   AppCustomSD="Eventlog: Security descriptor for Application event log"
   SecCustomSD="Eventlog: Security descriptor for Security event log"
   SysCustomSD="Eventlog: Security descriptor for System event log"
   DSCustomSD="Eventlog: Security descriptor for Directory Service event log"
   DNSCustomSD="Eventlog: Security descriptor for DNS Server event log"
   FRSCustomSD="Eventlog: Security descriptor for File Replication Service event log"
4. Save the changes you made to the Sceregvl.inf file, and then run the regsvr32 scecli.dll command.
5. Start Gpedit.msc, and then double-click the following branches to expand them:
   Computer Configuration
   Windows Settings
   Security Settings
   Local Policies
   Security Options
6. View the right panel to find the new "Eventlog" settings.

Use Group Policy to Set Your Application and System Log Security

1. In the Active Directory Sites and Services snap-in or the Active Directory Users and Computers snap-in, right-click the object for which you want to set the policy, and then click Properties.
2. Click the Group Policy tab.
3. If you must create a new policy, click New, and then define the policy's name. Otherwise, go to step 5.
4. Select the policy that you want, and then click Edit.
   
   The Local Group Policy MMC snap-in appears.
5. Expand Computer Configuration, expand Windows Settings, expand Security Settings, expand Local Policies, and then click Security Options.
6. Double-click Event log: Application log SDDL, type the SDDL string that you want for the log security, and then click OK.
7. Double-click Event log: System log SDDL, type the SDDL string that you want for the log security, and then click OK.

Scripting OS Level Configuration - Deploying WinXP OS thro' Scripting - SYSPREP Tool

Requirements for running the Sysprep tool

Before you can use the Sysprep tool, your computer hardware and related devices must meet the following requirements:

• The reference and destination computers must have compatible HALs. For example, Advanced Programmable Interrupt Controller (APIC)–based MPS (multiprocessor systems) must use the same APIC HAL. A standard HAL Programmable Interrupt Controller (PIC)–based system is not compatible with either the APIC HAL or the MPS HAL.
• The reference and destination computers must have the same Advanced Configuration and Power Interface (ACPI) support.
• The Plug-and-Play devices on the reference and destination computers, such as modems, sound cards, network adapters, and video cards, do not have to be from the same manufacturer. However, the drivers for these devices must be available.
• Third-party disk-imaging software or disk-duplicating hardware devices are required. These products create binary images of a computer's hard disk and either duplicate the image to another hard disk or store the image in a file on a separate disk.
• The size of the hard disk on the destination computer must be at least the same size as the hard disk on the reference computer. If the destination computer has a larger hard disk, the difference is not included in the primary partition. However, you can use the ExtendOemPartition key in the Sysprep.inf file to extend the primary partition if it was formatted by using the NTFS file system.

Prepare a reference computer for Sysprep deployment

To prepare a reference computer for Sysprep deployment in Windows XP, follow these steps:

1. On a reference computer, install the operating system and any programs that you want installed on your destination computers.
2. Click Start, click Run, type cmd, and then click OK.
3. At the command prompt, change to the root folder of drive C, and then type md Sysprep.
4. Insert the Windows XP operating system CD into the appropriate CD-ROM or DVD-ROM drive, and then open the Deploy.cab file in the \Support\Tools folder.
   
   Note To make sure that you are using the correct version of the Deploy.cab file for your service pack, use the Deploy.cab file that is distributed with that service pack. Visit the following Microsoft Web site to download the correct version for your service pack:
   http://www.microsoft.com/windowsxp/downloads/default.mspx (http://www.microsoft.com/windowsxp/downloads/default.mspx)
5. Copy the Sysprep.exe file and the Setupcl.exe file to the Sysprep folder.
   
   Note If you use the Sysprep.inf file, copy this file to the Sysprep folder also. For the Sysprep tool to function correctly, the Sysprep.exe file, the Setupcl.exe file, and the Sysprep.inf file must all be in the same folder.
6. Remove the Windows XP operating system CD.
7. At the command prompt, type cd Sysprep to change to the Sysprep folder.
8. To run the Sysprep tool, type the following command at the command prompt:
   Sysprep /optional parameter
   Note For a list of parameters, see the "Sysprep parameters" section.
   
   If you run the Sysprep.exe file from the %systemdrive%\Sysprep folder, the Sysprep.exe file removes the whole folder and the contents of the folder after it finishes.
9. Microsoft recommends that after you run the Sysprep.exe file on a reference computer, isolate the reference computer from the local network where the image will be subsequently deployed to avoid any NetBIOS conflicts. This permits the Sysprep tool to complete the Setup program, join the domain that is specified, and use the new computer name that is specified in the Sysprep.inf answer file.

Create the Sysprep.inf answer file

The Sysprep.inf answer file is a text file that scripts the answers for a series of graphical user interface (GUI) dialog boxes. To create a Sysprep.inf answer file that is used by the Sysprep tool, you can use a text editor or you can use the Setup Manager tool that is included on the Windows XP CD and is also included with the Microsoft Windows XP Resource Kit. The answer file must be renamed to Sysprep.inf, and must reside in the Sysprep folder in the root of the drive where Windows XP is installed, or these files can reside on a floppy disk. If the Sysprep folder is named differently, the Setup program ignores it. There is not a parameter for specifying the Mini-Setup Wizard answer file.

Sysprep.inf answer file format and parameters

• The Sysprep.inf answer file format and parameters are the same as the Unattended.txt file for Windows XP, but only the following sections and parameters are supported:
  
  [Unattended]
  ExtendOemPartition
  OemPnPDriversPath
  OemSkipEula
  InstallFilesPath
  KeepPageFile
  ResetSourcePath
  UpdateHAL
  UpdateUPHAL
  UpdateInstalledDrivers
  TapiConfigured
  
  [GuiUnattended]
  AdminPassword
  Autologon
  AutoLogonCount
  OEMDuplicatorString
  OEMSkipRegional
  OEMSkipWelcome
  TimeZone
  
  [UserData]
  Supports the same set of entries as the Unattend.txt file.
  [LicenseFilePrintData]
  Supports the same set of entries as the Unattend.txt file.
  [GuiRunOnce]
  Supports the same set of entries as the Unattend.txt file.
  [Display]
  Supports the same set of entries as the Unattend.txt file.
  [RegionalSettings]
  Supports the same set of entries as the Unattend.txt file.
  [Networking]
  Supports the same set of entries as the Unattend.txt file.
  [Identification]
  Supports the same set of entries as the Unattend.txt file.
  [TapiLocation]
  [Sysprep]
  Automatically generates the entries in the [SysprepMassStorage] section.
  [SysprepMassStorage]
  Allows you to use the same image on computers with different mass-storage devices.

Use the Setup Manager tool to create the Sysprep.inf answer file

To install the Setup Manager tool and to create an answer file, follow these steps:

1. Open My Computer, and then open the Support\Tools folder on the Windows XP CD.
2. Double-click the Deploy.cab file to open it.
3. On the Edit menu, click Select All.
4. On the Edit menu, click Copy To Folder.
5. Click Make New Folder.
   
   Type the name that you want for the Setup Manager folder, and then press ENTER. For example, type setup manager, and then press ENTER.
6. Click Copy.
7. Open the new folder that you created, and then double-click the Setupmgr.exe file. The Windows Setup Manager Wizard starts.
8. Follow the instructions in the wizard to create an answer file.

For additional information about how to create an answer file, click the following article number to view the article in the Microsoft Knowledge Base:

Sysprep parameters

You can use the following optional parameters with the Sysprep command in Windows XP:

• -activated - Do not reset the grace period for Windows product activation. Use this parameter only if you have activated the Windows installation in the factory.
  
  Important The product key that you use to activate the Windows installation must match the product key that is located on the COA sticker that is attached to that particular computer.
• -audit - Restarts the computer in Factory mode without having to generate new security IDs (SIDs) or process any items in the [OEMRunOnce] section of the Winbom.ini file. Use this command-line parameter only if the computer is already in Factory mode.
• -bmsd - Populates all the available mass storage devices in the [SysprepMassStorage] section.
• -clean - Clears the critical devices database that is used by the [SysprepMassStorage] section in the Sysprep.inf file.
  
• -factory - Restarts in a network-enabled state without displaying Windows Welcome or mini-Setup. This parameter is useful for updating drivers, running Plug and Play enumeration, installing programs, testing, configuring the computer with customer data, or making other configuration changes in your factory environment. For companies that use disk imaging (or cloning) software, Factory mode can reduce the number of images that are required.
  
  When all the tasks in Factory mode are complete, run the Sysprep.exe file by using the -reseal parameter to prepare the computer for end-user delivery.
• -forceshutdown - Shuts down the computer after the Sysprep.exe file finishes.
  
  Note Use this parameter with computers that have an ACPI BIOS that do not shut down correctly with the default behavior of the Sysprep.exe file.
• -mini - Configures Microsoft Windows XP Professional to use Mini-Setup instead of Windows Welcome. This parameter does not affect Microsoft Windows XP Home Edition, where the first-run experience is always Windows Welcome.
  
  Note that if you plan to use the Sysprep.inf file to automate Mini-Setup, you must either run the Sysprep tool by using the -mini switch, or click to select the MiniSetup check box in the GUI interface. By default, if you do not choose to run Mini-Setup, Windows XP Professional runs the Windows Welcome.
• -noreboot - Modifies registry entries (SID, OemDuplicatorString, and other registry entries) without the computer restarting or preparing for duplication. This parameter is mainly used for testing, specifically to see if the registry is modified correctly. Microsoft does not recommend this option because making changes to a computer after the Sysprep.exe file has run may invalidate the preparation that was completed by the Sysprep.exe file. Do not use this parameter in a production environment.
• -nosidgen - Runs the Sysprep.exe file without generating new SIDs. You must use this parameter if you are not duplicating the computer where you are running the Sysprep.exe file or if you are preinstalling domain controllers.
• -pnp - Runs the full Plug and Play device enumeration and installation of previous devices during Mini-Setup. This command-line parameter has no effect if the first-run experience is Windows Welcome.
  
  Use the -pnp command-line parameter only when you must detect and install previous, non-Plug and Play devices. Do not use the sysprep -pnp command-line parameter on computers that only use Plug and Play devices. Otherwise, you will increase the time that it takes for the first-run experience without providing any additional benefit to the user.
  
  Note When unsigned drivers are unavoidable, use the UpdateInstalledDrivers=yes parameter in conjunction with OemPnPDriversPath= and DriverSigningPolicy=ignore instead of the -pnp command-line parameter to provide a more seamless installation.
• -quiet - Runs the Sysprep.exe file without displaying onscreen confirmation messages. This is useful if you are automating the Sysprep.exe file. For example, if you plan to run the Sysprep.exe file immediately after the unattended Setup program finishes, add the sysprep -quiet command to the [GuiRunOnce] section of the Unattend.txt file.
• -reboot - Forces the computer to automatically restart, and then starts Windows Welcome Mini-Setup, or Factory mode, as specified. This is useful when you want to audit the computer and verify that the first-run experience is operating correctly.
• -reseal - Clears the Event Viewer logs and prepares the computer for delivery to the customer. Windows Welcome or Mini-Setup is set to start the next time that the computer restarts. If you run the sysprep -factory command, you must seal the installation as the last step in your preinstallation process. To do this, run the sysprep -reseal command or click the Reseal button in the Sysprep dialog box.

Management lessons from the Battle of Panipat

Throughout history, wars have left an indelible mark on human psyche. Serious debates have been held on the morality of and the strategic necessity for war.

And yet, like every dark cloud that has a silver lining, wars too at times leave a society wiser.

India too is no stranger to wars. And there are many lessons to be learnt from each of those battles -- management lessons, to be precise.

This one picks out management gems from the Third Battle of Panipat (1761 AD).

After the death of Aurangzeb, the Mughal empire was on the decline. The Marathas had captured most of the territories in the south. Slowly, they captured most of central and north India too.

They occupied Delhi and captured Lahore by defeating the son of Ahmad Shah Abdali, the Afghan ruler.

Abdali built his army and left Afghanistan to stop the expansion of the Maratha empire. He sought an alliance with Najib Khan, the chief of the Rohilla soldiers. Even the Nawab of Awadh had extended his support.

The confident Marathas were led by Sadashivrao Bhau. They had a large army, but very few allies.

Most of the kings from the Jat, Sikh and Rajput kingdoms did not support the Marathas as they resented the heavy tax imposed on them during the Maratha capture of north India. A few of them even struck favourable agreements with Abdali.

Lesson: One needs allies; the need is even stronger when one is operating in a new territory/segment.

Whether the company is doing an acquisition or is entering into a partnership, the agreement should try to be a 'win-win' one where the minority stakeholder should feel respected and heard.

This will help the partnership flourish. Similarly, if a manager takes care of his team, his team rally for him in his hour of need.

The Marathas attacked the Afghan garrison on the banks of the river Yamuna at Kunjpura, north of Delhi, and killed or enslaved them.

The Yamuna was in spate and Abdali, who was on the other side, could not do anything to save them. Along with his soldiers, he took a risk, reached the south of Delhi and crossed the Yamuna there, where it was relatively easier.

He wanted to encircle Marathas. The Marathas were unaware of this as they were preoccupied with the skirmish at Kunjpura.

Lesson: One should learn from a setback and improve; one must have the determination to win. Sometimes, a better plan emerges that could increase the probability of success as in Abidali's case, where he saw his troops decimated on the other side of the river.

Similarly, one may need to take snap decisions depending on the situation.

An individual with a good network and a company that has good market intelligence can get the latest information, which could be crucial in planning and providing competitive advantage.

When the stakes are high, lack of intelligence can invariably prove fatal, as in the case of how Abdali crossing the Yamuna went unnoticed by the Marathas.

When the Marathas realised that the south of the Yamuna was captured by Abdali, they dug their heels in near Panipat to deny him access to Afghanistan.

Slowly, Abdali encircled the Maratha army and cut off their supply line.

The Afghans continued to get the supplies they needed due to their agreements with a few north Indian kingdoms. Therefore, their need to open up the route towards Afghanistan was not that urgent.

But it was a desperate situation for the Maratha army who were expecting reinforcements from the south of the Narmada instead of the neighbouring states.

Lesson: It is important to do 'what if' contingency analysis planning for various scenarios. This upfront analysis may trigger a new partnership or alignment which could benefit the company.

Sometimes, one has to provide new concessions to gain the required support and solidify one's position. Both wars and businesses can benefit from a deep multi-step scenario analysis.

There were tensions in the Maratha army due to Abidali's strategy.

In the next two months, the skirmishes between the two left many, including Najib Khan's soliders, dead.

Abdali sensed a deadlock and decided to sign a peace treaty with the Marathas.

The Marathas were keen as well, but Najib Khan advised against the treaty and prevailed upon Abdali to delay his decision.

Lesson: Empower and trust your team members.

One should have complementary skills among the senior management so that the leader can tap into a breadth of expertise.

Finally, decisions are taken by the leader but an environment that encourages different voices and dissenting opinions is more effective than the 'do as I tell you to do' attitude. This is what encouraged Najib to convince Abdali.

Even though Najib failed miserably in the initial phase, Abdali continued to give him a chance to prove himself.

Similarly, a manager needs to have faith in his team despite early setbacks, provided they possess the right skills; after all, every mistake is a learning experience.

The Marathas and the Afghans prepared their armies for a major battle, but waited as the Afghans didn't want to attack first and the Marathas were waiting for reinforcements to arrive.

Eventually, the deteriorating situation forced the Marathas to attack the Afghans before their reinforcements arrived. The Marathas had better French guns and made significant progress. Ibrahim Gardi played a key role in getting early breakthroughs for the Marathas.

By noon, it appeared that the Marathas would win.

Faced with imminent defeat, Abdali called on his highly trained reserve soldiers and cannon-mounted camels. From these camels, they could fire artillery which would go over their own infantry and started decimating the Maratha army.

The Maratha artillery, initially used to weaken the enemy defence, was now placed behind the infantry and thus became ineffective. Besides, Bhau did not have any significant reserves that could join them with artillery for a counter-attack.

Eventually, the tired Maratha forces could not match Abdali's reserve army. This turned the tide in Abdali's favour. By the end of the day, the Marathas had lost the battle.

Lesson: One should not give up the fight till the last moment.

The leader's behaviour during critical times can either motivate to overcome the challenge or lead to a psychological defeat where victory was possible.

It is also important to have a backup plan that can be used if the original one is not working, as Abdali did with the reserve troops and cannons on camels.

The leader should be bold in making changes in the team to ensure that a fresh mind can look into the issue from a new perspective and provide a solution, instead of the same tired mind being stuck in the same track of thought.

Both sides suffered huge losses. Soon after the battle, Abdali's alliance was consumed by infighting, forcing him to return to Afghanistan after anointing Shah Alam II as the Delhi emperor.

The third battle of Panipat ended the great Marathas' advance towards north-west India. However, after a few years under Peshwa Madhav Rao, they recaptured Delhi and retained it till 1818.

Lesson: To rise after a fall requires grit and determination.

It is not sufficient just to learn from mistakes and take corrective action. Changing the mindset of the team from being a loser to having faith in itself is a must. Then the team will rise and face the challenge again with confidence.

Projects that are failing or not doing well can be turned around by adopting this strategy.

Managing Free Space

Managing Free Space can become a problem quickly, especially when you have to manage multiple database servers, in multiple environments, each server with multiple Hard Drives / LUNs. Some companies spend money on monitoring tools like SCOM, others try to manage it in-house by developing their own software/processes/reports to show free space per volume per.

You could quickly determine Free Space per HDD/LUN by executing this tsql command, but it doesn’t tell you the TOTAL space per HDD/LUN: xp_fixeddrives. It will give you the free space in MB per drive.

When I connect to a server to free up space, the first thing I do is open explorer, click on My Computer and arrange the columns in this order by dragging the columns around:

1) Name

2) Free Space

3) Total Size

This makes it easier to read. See the diagram below to get some idea. You can ask explorer to sort according to a column by clicking on the column, but sadly it doesn’t handle it well/correctly.

Warning: When checking space, be very careful not to overlook MB and think it is GB.

Space saving techniques:

- Find what is no longer used/needed and delete/archive it. E.g.

- Clean out C:\Temp and C:\Windows\Temp. Windows won’t let you delete the temp files that are currently in use. Delete the blue $...$ files in the Windows folder. Drawback is you won’t be able to rollback any Windows Updates that was applied. I would leave this to a last resort for the really desperate.

- Find out which sql backup files (.BAK/.UBAK) are lying around and delete/archive the ones that are no longer needed.

- Find out which detached database files are lying around and delete/archive them

- Shrink each of the files per database to reclaim the allocated free space. Jonathan Kehayias wrote a pretty good article called “Monitor free space in the database files” to achieve just that. You can find it at: http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=FileSpaceMon

- Determine the space allocation and usage per table and then dealing with that. E.g. Dropping/Archiving tables that are not needed any longer.

This article focuses on finding (a) detached db files and (b) old backup files.

To achieve this I mainly use the DOS DIR command that searches for the .MDF, .NDF, .LDF, .BAK and .uBAK files. Then I compare the list of database files (.MDF, .NDF, .LDF) that were found against sysaltfiles to see which of these files aren’t in use by the instance. If you’re awake you’ll realize that this is only suitable for servers that has only 1 sql instance, unless you run the code against each sql instance. This is because when you run the code, it is like saying "WHICH FILES HAS THE DISKS GOT THAT ISN'T IN A PARTICULAR SQL INSTANCE". Ideally we would like to say: "WHICH FILES HAS THE DISKS GOT THAT AREN'T USED BY ANY SQL INSTANCE ON THIS SERVER". Still it is very handy on single instance servers.

Enough talk - let’s get to the good stuff. This code is compatible with SS2000, 2005 and 2008.

There is 1 pre-requisite; your SQL Server must allow DOS commands to run. So if you have prevented this, you’ll have to re-enable this functionality for the script to run. I do a quick check at the beginning of the script to determine if the pre-requisite is enabled.

The script is broken into 3 Sections listed below:

1. Gather data into temp tables
2. Reports that pulls out what we are interested in. I created 4 reports for you. Look at the examples further on:
   • REPORT 1: POTENTIAL SPACE SAVING (SUMMARY) PER VOLUME
   • REPORT 2: POTENTIAL SPACE SAVING (DETAILED) REPORT (FOR DATABASE FILES) PER DRIVE/VOLUME
   • REPORT 3: POTENTIAL SPACE SAVING (DETAILED) REPORT (FOR DATABASE FILES) DESC BY WASTAGE
   • REPORT 4: POTENTIAL SPACE SAVING (DETAILED) REPORT (FOR .BAK/.UBAK FILES), FROM BIGGEST TO SMALLEST
3. Discard temp tables

Procedure to get this working: Run the code in Section 1. Then run each report in turn in Section 2. Run Section 3 to do the cleanups. Should you want to discuss anything RE this, you can contact me on: paul_els@hotmail.com

Script Section 1 – Gathering data needed for our reports

-- OBTAIN A LIST OF ALL THE DRIVES ON THE SERVER

set nocount on

CREATE TABLE #Drives (DriveLetter char(1), MBFree int)

INSERT INTO #Drives

EXEC master..xp_fixeddrives

-- DECLARE VARIABLES

DECLARE @CurrentDriveLetter CHAR(1), @MaxDriveLetter CHAR(1), @EXECSTR varchar(1024)

-- FIND THE FIRST AND LAST DRIVES FOR THE LOOP

SELECT @CurrentDriveLetter = Min(DriveLetter), @MaxDriveLetter = Max(DriveLetter) from #Drives

-- CREATE THE TABLE TO HOST THE LIST OF FILES

CREATE TABLE #Files (autono_id int NOT NULL IDENTITY (1, 1), RawData varchar(255), FilePath varchar(255), DriveLetter CHAR(1), [FileName] varchar(255), FileSize varchar(17), FileSizeInMB decimal(18,2), FileSizeInGB decimal(18,2))

WHILE @CurrentDriveLetter <= @MaxDriveLetter

BEGIN

-- STORE THE FILES WE ARE LOOKING FOR IN THE #FILES TABLE

-- PRINT STR('dir ' + STR(@CurrentDriveLetter) + ':\*.mdf;*.ndf;*.ldf /s')

SELECT @EXECSTR = 'dir ' + CONVERT(VARCHAR(1),@CurrentDriveLetter) + ':\*.mdf;' + CONVERT(VARCHAR(1),@CurrentDriveLetter) + ':\*.ndf;' + CONVERT(VARCHAR(1),@CurrentDriveLetter) + ':\*.ldf;' + CONVERT(VARCHAR(1),@CurrentDriveLetter) + ':\*.ubak;' + CONVERT(VARCHAR(1),@CurrentDriveLetter) + ':\*.BAK /s' -- string in the drive letter later

INSERT INTO #Files (RawData)

EXEC master..xp_cmdshell @EXECSTR

-- PRINT @EXECSTR

select @CurrentDriveLetter = MIN(DriveLetter) from #Drives where DriveLetter > @CurrentDriveLetter

END

-- CLEAN UP #FILES

update #Files

set FilePath = REPLACE(RawData,'Directory of ','')

where RawData like '%Directory of %:%'

update #Files

set FilePath = SubString(FilePath, 2, 255)

where FilePath is not null

delete from #Files

where RawData is NULL

or RawData = 'File Not Found'

or RawData like '%Volume%'

or RawData like '%File(s)%'

or RawData like '%Dir(s)%'

or RawData like '%Total Files Listed:%'

update #Files set [FileName] = substring (RawData, 40, 255) where FilePath is NULL

update #Files set FileSize = substring (RawData, 22, 17) where FilePath is NULL

update #Files set FileSize = replace(substring (RawData, 22, 17),',','') where FilePath is NULL

update #Files set DriveLetter = substring(FilePath, 1, 1) where FilePath is not NULL

update #Files

set FileSizeInMB = CONVERT(decimal(18,2), FileSize) / 1024 / 1024,

FileSizeInGB = CONVERT(decimal(18,2), FileSize) / 1024 / 1024 / 1024

DECLARE @autono_id int, @fp varchar(255), @drive char(1)

select top 1 @autono_id = autono_id, @fp = [FilePath], @drive = DriveLetter

from #files F1

where FilePath is not null

and autono_id < (select max(autono_id) from #Files where FilePath is NULL)

order by autono_id desc

WHILE @autono_id IS NOT NULL

BEGIN

update #Files

set [FilePath] = @fp, DriveLetter = @Drive

where autono_id > @autono_id and [FilePath] is NULL

DELETE from #Files where [FileName] is null AND DriveLetter = @Drive AND autono_id > @autono_id

SELECT @autono_id = NULL, @fp = NULL, @drive = NULL -- RESET FLAGS

select top 1 @autono_id = autono_id, @fp = [FilePath], @drive = DriveLetter

from #files F1

where FilePath is not null

and autono_id < (select max(autono_id) from #Files where FilePath is NULL)

order by autono_id desc

END

delete from #Files where FileName is NULL or FilePath like '%i386%' or FilePath like '%ia64%'

Script Section 2 – Run each report respectively

-- REPORT 1: POTENTIAL SPACE SAVING (SUMMARY) PER VOLUME

select 'DriveLetter' = Ltrim(rtrim(LEFT(DriveLetter,1))),

'Potential Saving (in MB)' = LTRIM(STR(SUM(FileSizeInMB))),

'Potential Saving (in GB)' = LTRIM(STR(SUM(FileSizeInGB)))

from #Files

where REPLACE(FilePath + '\' + [FileName], ':\\', ':\') not in (select Ltrim(rtrim(filename)) from master.dbo.sysaltfiles)

group by Ltrim(rtrim(LEFT(DriveLetter,1)))

order by Ltrim(rtrim(LEFT(DriveLetter,1)))

-- REPORT 2: POTENTIAL SPACE SAVING (DETAILED) REPORT (FOR DATABASE FILES) PER DRIVE/VOLUME

select 'Drive' = Ltrim(rtrim(LEFT(DriveLetter,1))),

'FileName' = REPLACE(FilePath + '\' + [FileName], ':\\', ':\'), FileSizeInMB, FileSizeInGB

from #Files

where REPLACE(FilePath + '\' + [FileName], ':\\', ':\') not in (select Ltrim(rtrim(filename)) from master.dbo.sysaltfiles)

and right(FileName,3) <> 'bak' -- EXCLUDE .BAK/.UBAK FILES

and FileSizeInMB > 0 -- YOU CAN STIPULATE YOU ARE LOOKING FOR FILES LARGER THAN X MB

order by Ltrim(rtrim(LEFT(DriveLetter,1))) asc, 3 desc

-- REPORT 3: POTENTIAL SPACE SAVING (DETAILED) REPORT (FOR DATABASE FILES) DESC BY WASTAGE

select 'Drive' = Ltrim(rtrim(LEFT(DriveLetter,1))),

'FileName' = REPLACE(FilePath + '\' + [FileName], ':\\', ':\'), FileSizeInMB, FileSizeInGB

from #Files

where REPLACE(FilePath + '\' + [FileName], ':\\', ':\') not in (select Ltrim(rtrim(filename)) from master.dbo.sysaltfiles)

and right(FileName,3) <> 'bak' -- EXCLUDE .BAK/.UBAK FILES

and FileSizeInMB > 0 -- YOU CAN STIPULATE YOU ARE LOOKING FOR FILES LARGER THAN X MB

order by 3 desc

-- REPORT 4: POTENTIAL SPACE SAVING (DETAILED) REPORT (FOR .BAK/.UBAK FILES), FROM BIGGEST TO SMALLEST

select DriveLetter, 'FileName' = REPLACE(FilePath + '\' + [FileName], ':\\', ':\'), FileSizeInMB, FileSizeInGB

from #Files

where FilePath + '\' + FileName not in (select filename from master.dbo.sysaltfiles)

and right(FileName,3) = 'bak'

and FileSizeInMB > 0 -- YOU CAN STIPULATE YOU ARE LOOKING FOR FILES LARGER THAN X MB

order by FileSizeInMB desc, REPLACE(FilePath + '\' + [FileName], ':\\', ':\') asc

Script Section 3 – Cleanup/Discard the temp tables

drop table #Files

drop table #Drives

Examples of the output of each report:

REPORT 1: POTENTIAL SPACE SAVING (SUMMARY) PER VOLUME

· REPORT 2: POTENTIAL SPACE SAVING (DETAILED) REPORT (FOR DATABASE FILES) PER DRIVE/VOLUME

Notice:

o It is sorted by Drive, by FileSizeInMB desc.

o Not everything that is listed can be deleted - use your own discretion.

· REPORT 3: POTENTIAL SPACE SAVING (DETAILED) REPORT (FOR DATABASE FILES) DESC BY WASTAGE

Notice: It is virtually the same report as Report 2, except that the sort order is from most to least.

REPORT 4: POTENTIAL SPACE SAVING (DETAILED) REPORT (FOR .BAK/.UBAK FILES), FROM BIGGEST TO SMALLEST

Summary

I built this script to help me to quickly identify where old sql backup files and detached db files are lying around in order to save space by getting rid of them. It is very effective at achieving this goal. There is no excuse for not setting up maintenance jobs, but in a multi DBA environment someone else may have left files lying around. The code can be extended to search for other file extensions quite easily. Further you can even precede the code with code to determine if the pre-requisite is met, e.g. are DOS commands are allowed by SQL? The code below is a step in that direction:

DECLARE @sqlversion sql_variant

SELECT @sqlversion = SERVERPROPERTY('productversion')

IF LEFT(CONVERT(VARCHAR(255),@SQLVERSION),2) <> '8.'

BEGIN

-- START CHECKING SP_CONFIGURE FOR XP_CMDSHELL OPTION --

CREATE TABLE #xp_cmdshell (OptionName varchar(255), minval int, maxval int, configval int, runval int)

INSERT INTO #xp_cmdshell

EXEC master..sp_configure

declare @runval int

select @runval = runval from #xp_cmdshell where OptionName = 'xp_cmdshell'

drop table #xp_cmdshell

if @runval is null

begin

RAISERROR ('enable "show advanced options" before you run this code', -- Message text.

10, -- Severity,

16 -- State,

)

end

if @runval = 1

begin

print '' -- The pre-requisites are enabled, so we can continue

end

else -- IF xp_cmdshell is NOT enabled

begin

RAISERROR ('enable xp_cmdshell before you run this code', -- Message text.

10, -- Severity,

16 -- State,

)

end

-- FINISH CHECKING SP_CONFIGURE FOR XP_CMDSHELL OPTION --

END