Powershell: Invoke-Sqlcmd command not recognized

The term 'Invoke-Sqlcmd' is not recognized as the name of a cmdlet, function, 
script file, or operable program. Check the spelling of the name, or if a path 
was included, verify that the path is correct and try again.

When trying to run the Invoke-sqlcmd command within the powershell utility, an error is returned suggesting the command is not recognized.

Invoke-Sqlcmd -Query "SELECT GETDATE() AS TimeOfQuery;" -ServerInstance "mysqlserver"

image

This happens because the Invoke-sqlcmd cmdlet is not included as part of PowerShell, but instead it is of sqlps (SQL Server 2008 PowerShell Host). Before using invoke-sqlcmd you should install SSMS or the SQL Server 2008 Feature Pack

When sqlps has been installed, you may then load invoke-sqlcmd into powershell.exe by using the below script from Michiel Wories blog http://blogs.msdn.com/b/mwories/archive/2008/06/14/sql2008_5f00_powershell.aspx

Im still a novice at powershell so Ive ended up building the initiliaze into all my basic scripts using dot-source

# Use Dot-Source to Call the “Initialize-SQLPSEnvironment” Script. .{."\\shared\home\Mohsin.AliPatel0 - DBA8 - Powershell\Powershell_training\Initialize-SqlPsEnvironment.ps1"} $instanceNameList = Get-Content "\\shared\home\Mohsin.AliPatel0 - DBA8 - Powershell\Powershell_training\ServerList.txt" foreach($instanceName in $instanceNameList) { $results += Invoke-Sqlcmd -Query "select SERVERPROPERTY('ServerName') As ServerName, Name, crdate from sysdatabases" -ServerInstance $instanceName } # print results $results

The above script calls the initialize script, and then does a basic SQL Query

Find SQL Server Start Time Quickly

DECLARE @WorkingVariable datetime

SELECT @WorkingVariable = login_time
FROM master..sysprocesses
WHERE cmd='LAZY WRITER'

print @WorkingVariable

The above will simply return the date when the LAZY Writer process started.

SELECT create_date FROM sys.databases WHERE NAME='tempdb'

The above simply checks when Tempdb started

SET NOCOUNT ON
DECLARE @crdate DATETIME, @hr VARCHAR(50), @min VARCHAR(5)

SELECT @crdate=create_date FROM sys.databases WHERE NAME='tempdb'
SELECT @hr=(DATEDIFF ( mi, @crdate,GETDATE()))/60

IF ((DATEDIFF ( mi, @crdate,GETDATE()))/60)=0
  SELECT @min=(DATEDIFF ( mi, @crdate,GETDATE()))
ELSE
  SELECT @min=(DATEDIFF ( mi, @crdate,GETDATE()))
-((DATEDIFF( mi, @crdate,GETDATE()))/60)*60
  PRINT 'SQL Server "' + CONVERT(VARCHAR(20),SERVERPROPERTY('SERVERNAME'))
    +'" has been Online for the past '+@hr+' hours & '+@min+' minutes.'
    + 'Instance start time is ' + CONVERT(varchar(23),@crdate, 113)

IF NOT EXISTS (SELECT 1 FROM master.dbo.sysprocesses 
                            WHERE program_name like N'SQLAgent90%')
 BEGIN
   PRINT 'SQL Server is running but SQL Server Agent <<NOT>> running'
 END
 ELSE BEGIN
   PRINT 'SQL Server and SQL Server Agent are both running'
 END

The above produces user friendly information on how many hours the Server has been up for, and other useful info.

image

Dropping Database – Delete Backup History taking a long time

Ive found this on numerous occasions and decided not to be lazy this time around and investigate why it takes so long to drop a database when the ‘Delete backup and Restore History Information for databases’ checkbox is ticked.

When thinking about it logically, when purging historical data with a where criteria against a set of tables in any database, the task would be faster when you have indexes in place. this is exactly the scenario which causes the GUI to seem like its hanging. However, if you check sys.dm_exec_requests you will be able to see CPU and IO actively moving and a DELETE command against the msdb, in the form of:

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'MyDB'

Running the following will show you which indexes are present on the msdb database as standard:

SELECT t.name as [Table], ind.name as [Index], col.name as [Column], ind.type_desc [Index Type]
FROM sys.indexes ind
inner join sys.index_columns ic
    on ind.object_id = ic. object_id and ind.index_id = ic. index_id
inner join sys.columns col
    on ic.object_id = col.object_id and ic.column_id = col.column_id
inner join sys.tables t
    on ind.object_id = t. object_id WHERE ind.is_unique = 0 and ind. is_unique_constraint = 0
    and t.name in ( 'backupfile', 'backupfilegroup', 'backupmediafamily', 'backupmediaset', 'backupset', 'restorefile', 'restorefilegroup', 'restorehistory' ) ORDER BY t .name, ind.name

…Only 4 indexes are present as standard in the msdb database.

This problem can be alleviated by adding a set of indexes to some key tables in the msdb database.

This script is taken from Here

/************************************************************************ * * * Title: msdb index creation * * Author: Geoff N. Hiten * * Purpose: Index msdb database * * Date: 12/12/2005 * * Modifications: * ************************************************************************/ use msdb
go

--backupset Create index IX_backupset_backup_set_id on backupset(backup_set_id) go
Create index IX_backupset_backup_set_uuid on backupset(backup_set_uuid) go
Create index IX_backupset_media_set_id on backupset(media_set_id) go
Create index IX_backupset_backup_finish_date on backupset(backup_finish_date) go
Create index IX_backupset_backup_start_date on backupset(backup_start_date) go

--backupmediaset Create index IX_backupmediaset_media_set_id on backupmediaset(media_set_id) go

--backupfile Create index IX_backupfile_backup_set_id on backupfile(backup_set_id) go

--backupmediafamily Create index IX_backupmediafamily_media_set_id on backupmediafamily(media_set_id) go

--restorehistory Create index IX_restorehistory_restore_history_id on restorehistory(restore_history_id) go
Create index IX_restorehistory_backup_set_id on restorehistory(backup_set_id) go

--restorefile Create index IX_restorefile_restore_history_id on restorefile(restore_history_id) go

--restorefilegroup Create index IX_restorefilegroup_restore_history_id on restorefilegroup(restore_history_id) go

This small addition will speed up the deletion process completely and make sure the GUI doesnt hang waiting for a background delete against the msdb to complete.

Useful scripts

I found that deleting backup history was slowing down, so i did the following:

USE msdb; GO
UPDATE STATISTICS backupfile; GO
UPDATE STATISTICS backupmediafamily; GO
UPDATE STATISTICS backupmediaset; GO
UPDATE STATISTICS backupset; GO
UPDATE STATISTICS restorefile; GO
UPDATE STATISTICS restorefilegroup; GO
UPDATE STATISTICS restorehistory; GO

And also added the following index, after looking at the estimated execution plan:

Create index IX_backupset_database_name on backupset(database_name) go

Some general queries:

--Find count of records older than certain date select count(*) from backupset with (nolock) where backup_start_date < '8/15/2009' --with database name select * from backupset with (nolock) where backup_start_date < '08/15/2011' and database_name='MyDB' --Find databases which have history data but no longer attached to instance select distinct(database_name) from backupset with (nolock) where database_name not in (select name from sys.databases) --Purge history older than certain date sp_delete_backuphistory @oldest_date='08/15/2009' --Purge history for a particular DB EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'myTest'