VirtualObjectives

Microsoft SQL Server


Tools:


Program Version Licence Description
SQL Server Surveyor
1.3.6.15

A tool which allows database administrators to easily create reports of their SQL Servers for the purposes of documentation, auditing, comparison with other servers, etc.
New features include:
* Suitable for SQL Server 2008, 2005, 2000
* Windows scheduled jobs now uses both AT and SCHTASKS.
* Print preview button.
* Records username and authentication method.
* Updated user login dialog box.
* Handles common HTML character codes in the report.

Unless specified, all programs are completely free and do not contain spyware or adware, or are crippled in any way.
All programs support Windows 2000, Windows XP. Unless specified, there is no support for Windows Vista or Windows 7.

SQL Scripts:


SQL Script Description
Query Active Directory \ LDAP Query Windows Active Directory (AD) (LDAP) and return all employee account records into a temporary table, overcoming the 1000 \ 1500 record limit imposed by AD.
Check Blocking
Check Blocking using SaveTableAsHTML
Check for blocking activity taking longer than N number of minutes and send an email containing the connection details of all those affected.
There are two versions; one uses SaveTableAsHTML to produce a HTML table while the other does not.
Check Date Format Compare date format of the SQL Server with a predefined date format.
Check Failed Jobs Check whether any scheduled jobs have failed and email the errors to the administrator(s).
Count Records in a Database Count the number of rows in each table of a database.
Deleting records from large tables How to delete a large number of records based upon a certain criteria without blowing out the transaction log.
Display all Triggers in a Database Display all triggers (active or inactive) in a database.
Display Server Disk Space Display the server's disk space.
Get Daylight Saving Dates Get the pervious and next daylight saving transition dates (and times) from the server's Registry.
Kill Processes Generate a script that can be used to kill connections by SPID.
Server Drive Space Monitoring Stored procedure to monitor the amount of free disk space (in MiB) on a server.
Using ActiveX to Assign Global Variables in DTS Packages How to use ActiveX Script Task to apply values stored in Global Variables to various objects in a DTS package.
FTP Scripts Scripts to easily transfer one or more files between SQL Server and an FTP server.
Saving SQL Query Results to Comma Delimited Files (CSV)
Save query results to a CSV file or text. Supports delimiters of your choice.
  • Better handling of @DBUltra and @Header parameters.
  • Incorporated SaveDelimitedColumnsNullQuoted by using the new @NullQuoted parameter.
  • Added @DateTimeStyle parameter to allow formatting of date\time columns.
  • Support for data types datetimeoffset, xml, geometry, geography.
  • BCP supports wide columns and now uses unicode (-w).
Saving SQL Query Results as HTML
Save query results to a HTML file or text.
  • @CSS parameter now uses predefined styles as well as the ability to supply your own.
  • Better handling of @DBUltra and @Header parameters.
  • SQLCMD replaces OSQL for data generation.
  • Added @DateTimeStyle parameter to allow formatting of date\time columns.
  • Support for data types datetimeoffset, xml, geometry, geography.
Logins Audit Audit the level of access on SQL Server. It obtains members for database roles as well as server role members. It also creates three html reports which are then emailed.
Reindex a Database Simple and effective script to reindex a database.
Search All Tables Search all columns of all tables of a selected database for a given search string.
Search and Replace Search a selected database for a string and output a script which can be used to replace the search string with another value.
Search Default Trace The default trace in SQL Server contains useful information which can help administrators with finding out when events happen. This script will allow you to display the server's default trace.
SQL Server Restart Notification Get notified by email when your SQL Servers are restarted.
This method relies on using the "run on Agent Manager startup" option in SQL Jobs.
SQL Server Restart Notification using sp_procoption Get notified by email when your SQL Servers are restarted.
This method uses sp_procoption which sets a stored procedure for autoexecution.
Server Side Tracing

Stored procedure that allows for the easy collection of server-side trace files. With added steps you can even start server side tracing after the SQL Server has been restarted.

SQLDiag for SQL Server This article explains how to use SQLDiag as a Service to get more thorough diagnostics than the standard Profiler traces or Server Side Tracing.

Tips:


Problem/Issue Description
Microsoft SQL Server Management Studio is too Slow Microsoft SQL Server Management Studio (SSMS) can take over 45 seconds to start. This tip will explain how to resolve this.
Running DTS Packages under SQL Server 2005 and 2008 Find out how you can run SQL Server 2000 DTS Packages on SQL Server 2005, 2008 and 2008 R2.
Getting DTS Working in SSMS How you set up your SSMS client to allow you to edit SQL 2000 DTS packages.
Understanding "login failed" (Error 18456) error messages Learn how to interpret Error 18456 messages.