The insights of an SQL geek: 2009

Sunday, May 10, 2009

EZManage SQL Pro - Recommended MS SQL Server Management Tool

EZManage SQL Pro is a powerful AGENTLESS centralized management tool for the optimal performance of Microsoft SQL Servers. Enterprise version for 1-500 servers
FutureIT’s EZManage SQL Pro offers one click management of multiple servers allowing SQL Server databases to run smoothly, efficiently and continuously. Be able to monitor the status of all your SQL servers and databases from one screen, restore to any point in time with no data loss and easily set up notifications. All this while using less disk space and with applications running faster. What could be better?

Central Management of Multiple SQL Servers

  • Monitor the status of all your SQL Servers and databases in one screen with drill- down capabilities
  • Configurable dashboard to view servers and databases
  • Display database locks and their cause (SQL statement)
  • Intervene and release locks
  • Monitor all server jobs in one screen and prioritize
  • Edit scheduled jobs from multiple servers
  • Quick launch of Microsoft remote desktop,
Windows Explorer or a command shell screen on a remote server
Assign multiple servers to groups for visual and logical order and administration

Backup & Restore
  • Backup speed up to 5 times faster than native SQL
  • Restores up to 8 times faster than native SQL
  • Backup encryption (up to 320 bit)
  • Ultra-high compression of backup files by up to 90%
  • Open compressed backups with standard tools
  • Fine tuning options to set optimum backup speed and compression
  • Restore to any point of time in a single click
  • Easy restore and duplication of databases to different servers
  • Sends backup files off-site by FTP, automatically
  • View backup history
  • Perform backups in parallel or sequentially
  • Backup files to TSM and Tape

Built-in Disaster Recovery

  • Restore databases between server on a schedule routine
  • View synchronization statistics
  • Setup full DRP solution in just a few minutes
  • Reduce company downtime to minimum
  • Monitoring and Alerting
  • Send notifications By SNMP, SMS or email
  • Define alert distribution lists
  • Define customs alerts that suite your company needs
  • Set your own notification suites and attach them to multiple databases and servers in a single click

Automatic Ongoing Optimization

  • Intelligent index defragmentation (based on the state of each index)
  • Shrinking of log files to specified size
  • Real optimization of DB statistics
  • All optimization jobs performed according to protocol and correct best practice sequence preventing errors like accidentally shrinking before backup

Multi Server Scripting

Run scripts on multiple DBs
  • Query multiple servers and databases simultaneously and view single results table
  • Export query results to Excel
  • Print a report of the results
  • Customize and execute scripts from our large script library
  • Ease of Use & Implementation

    • Ultra fast implementation based on pre-defined rules and wizards
    • Standard, hassle-free installation
    • Friendly and easy-to-use interface
    • Pure T-SQL communication to all managed servers
    • Agent-less architecture - no installation is needed on the target servers, improving security

    System Supported Platforms

    • 32-bit: Windows 2000, Windows XP, Windows Server 2000, Windows Server 2003, Windows Vista, Windows Server 2008
    • 64-bit: Windows 2003 (x64), Windows Server 2003, Windows XP, Windows Vista, Windows Server 2008
    • IA-64: Windows Server 2003, Windows Server 2008

    System Supported SQL Servers

    • SQL Server 2000 Standard Enterprise and MSDE Editions
    • SQL Server 2005 Standard Enterprise and Express* Editions
    • SQL Server 2008 Standard Enterprise and Express* Editions

    Monday, February 16, 2009

    How to import the error log contents into a specified table?

    Hello all.
    try this Tsql to import your error log into a table.
    usage :
    EXEC sp_import_errorlog 'myerrorlog'

    CREATE PROC sp_import_errorlog
    @log_name sysname,
    @log_number int = 0,
    @overwrite bit = 0


    DECLARE @sql varchar(500) --Holds to SQL needed to create columns from error log

    IF (SELECT OBJECT_ID(@log_name,'U')) IS NOT NULL
    IF @overwrite = 0
    RAISERROR('Table already exists. Specify another name or pass 1 to @overwrite parameter',18,1)
    RETURN -1
    EXEC('DROP TABLE ' + @log_name)

    --Temp table to hold the output of sp_readerrorlog
    CREATE TABLE #errlog
    err varchar(1000),
    controw tinyint

    --Populating the temp table using sp_readerrorlog
    INSERT #errlog
    EXEC sp_readerrorlog @log_number

    --This will remove the header from the errolog
    DELETE #errlog

    SET @sql = 'SELECT
    CONVERT(DATETIME,LEFT(err,23)) [Date],
    SUBSTRING(err,24,10) [spid],
    RIGHT(err,LEN(err) - 33) [Message],
    INTO ' + QUOTENAME(@log_name) +
    ' FROM #errlog ' +
    'WHERE controw = 0'

    --Creates the table with the columns Date, spid, message and controw
    EXEC (@sql)

    --Dropping the temporary table
    DROP TABLE #errlog

    PRINT 'Error log successfully imported to table: ' + @log_name

    Wednesday, January 21, 2009

    How to view MSSQL 2005 logins

    hello all,
    use this to view your 2005 server logins

    SELECT p.sid,, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
    sys.server_principals p LEFT JOIN sys.syslogins l
    ON ( = ) WHERE p.type IN ( 'S', 'G', 'U' ) AND <> 'sa'



    Wednesday, January 7, 2009

    How to view backup history

    Hello all,

    I found this little script that helped me to view all my backup history

    SELECT, bkup.description, bkup.backup_finish_date,
    when type='D' then '** FULL **'
    when type='I' then 'DIFFERENTIAL'
    when type='L' then 'LOG'
    end as Backup_Type,
    (STR(ABS(DATEDIFF(day, GetDate(),(backup_finish_date))))) as 'Days_Ago',
    ceiling(bkup.backup_size /1048576)
    as 'Size Meg' ,
    cast((bkup.backup_size /1073741824) as decimal (9,2))
    as 'Gig',
    server_name, sysdb.crdate
    ,datediff(minute, bkup.backup_start_date, bkup.backup_finish_date)
    as 'Mins'
    ,cast(cast(datediff(minute, bkup.backup_start_date, bkup.backup_finish_date)
    decimal (8,3))/60 as
    decimal (8,1))
    as 'Hours', first_lsn, last_lsn, checkpoint_lsn
    FROM master.dbo.sysdatabases sysdb LEFT OUTER JOIN msdb.dbo.backupset bkup ON bkup.database_name
    where backup_finish_date >
    DATEADD(DAY, -60, (getdate()))
    ORDER BY, bkup.backup_finish_date desc

    thanks to homebrew01


    Tuesday, January 6, 2009

    Find the next running schedule job

    This is a little script I just found and wanted to share.

    USE msdb;
    WITH CTE AS (SELECT schedule_id,
    job_id, RIGHT('0'+CAST(next_run_time AS VARCHAR(6)),6) AS next_run_time, next_run_date
    FROM sysjobschedules)
    SELECT Job_Name,
    'Will be running today at '+SUBSTRING(CONVERT(VARCHAR(10), CASE WHEN SUBSTRING (CONVERT(VARCHAR(10),next_run_time) , 1 ,2) > 12
    THEN SUBSTRING (CONVERT(VARCHAR(10),next_run_time),1,2) -12
    SUBSTRING (CONVERT(VARCHAR(10),next_run_time),1,2) END),1,2)+':'+SUBSTRING (CONVERT(VARCHAR(10), next_run_time),3,2)+':'+SUBSTRING (CONVERT(VARCHAR(10), next_run_time ),5,2)
    'Scheduled At'
    FROM sysjobs A , CTE B
    WHERE A.job_id = B.job_id
    SUBSTRING(CONVERT(VARCHAR(10),next_run_date) , 5,2) +'/'+SUBSTRING(CONVERT(VARCHAR(10),next_run_date) , 7,2) +'/'+SUBSTRING(CONVERT(VARCHAR(10),next_run_date),1,4) = CONVERT(VARCHAR(10),GETDATE(),101)
    THEN SUBSTRING (CONVERT(VARCHAR(10),next_run_time) , 1 ,2) -12
    ELSE SUBSTRING (CONVERT(VARCHAR(10),next_run_time) , 1 ,2) END),1,2)+':'+SUBSTRING (CONVERT(VARCHAR(10), next_run_time ),3,2)+':'+SUBSTRING (CONVERT(VARCHAR(10), next_run_time ),5,2)) >SUBSTRING (CONVERT( VARCHAR(30) , GETDATE(),9),13,7)

    Thanks to



    Monday, January 5, 2009

    The Power of GO

    Hello all.

    Nice little tip for you all.
    If you don't want to use complicated loops, declare counters and so on you can use to power of the GO keyword to the rescue.
    The GO keyword in SQL 2000, 2005 tells the Server to execute the preceding code as one batch
    Use the GO in 2005 to actually tell the server how many times you would like the batch to execute.

    For an example we will create a new table and insert new data 1000 times

    (ID INT IDENTITY (1,1),
    ROWID uniqueidentifier)
    INSERT INTO dbo. TempTable (ROWID)
    GO 1000

    Enjoy and don’t forget to leave some comments

    Thursday, January 1, 2009

    How to get a list of all Temp tables

    Hello all.
    One of my friends asked me today how he can get a list of all Temp tables.
    Well the answer is very simple, that I have to share.
    Just use this:

    use tempdb
    select *
    where TABLE_SCHEMA = USER and
    TABLE_CATALOG = 'tempdb'

    Enjoy and don’t forget to leave comments.