EZManage SQL Pro - Recommended MS SQL Server Management Tool

Hello.
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?

http://www.futureitsoft.com/

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

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
)
AS

BEGIN
SET NOCOUNT ON

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

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


--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
SET ROWCOUNT 4
DELETE #errlog
SET ROWCOUNT 0


SET @sql = 'SELECT
CONVERT(DATETIME,LEFT(err,23)) [Date],
SUBSTRING(err,24,10) [spid],
RIGHT(err,LEN(err) - 33) [Message],
controw
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

SET NOCOUNT OFF
PRINT 'Error log successfully imported to table: ' + @log_name
END

How to view MSSQL 2005 logins

hello all,
use this to view your 2005 server logins


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


Enjoy

Itay

How to view backup history

Hello all,

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

SELECT
sysdb.name, bkup.description, bkup.backup_finish_date,
case
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)
as
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
= sysdb.name
where backup_finish_date >
DATEADD(DAY, -60, (getdate()))
ORDER BY sysdb.name, bkup.backup_finish_date desc


thanks to homebrew01

itay

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 A.name 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
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)
'Scheduled At'
FROM sysjobs A , CTE B
WHERE A.job_id = B.job_id
AND
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)
AND
(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
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 http://mssqlonline.blogspot.com/

enjoy

itay

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

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


Enjoy and don’t forget to leave some comments
Itay

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
go
select *
from INFORMATION_SCHEMA.TABLES
where TABLE_SCHEMA = USER and
TABLE_CATALOG = 'tempdb'
go

Enjoy and don’t forget to leave comments.
Itay.