using progress Bar in powershell

March 8, 2015 Leave a comment

Hey all ,

I was looking for an way to pop up an progress BAR for an ongoing progress within an POWERSHELL script ..

figured out powershell inheritably provide an inbuilt cmdlet for doing the same .

Command ::

write-progress

for detailed help and examples on this command use below cmdlet

## get-help

  get-help write-help -full | more

 

Example ::

for ($i = 1; $i -le 100; $i++ )

{

get-process

write-progress -activity “Search in Progress” -status $i% Complete:” -percentcomplete $i;}

 

Example for 2 bars ::

for ($i = 1; $i -le 100; $i++ )

{

get-process

write-progress -activity “Search in Progress” -status $i% Complete:” -percentcomplete $i -CurrentOperation “outerloop” ;

for ($j = 1; $j -le 100; $j++ )

{

get-process

write-progress -Id 1 -activity “Search in Progress” -status $j% Complete:” -percentcomplete $j -CurrentOperation “innerloop”;

}

}

 

progress Bar

Categories: General, Powershell

Query Complete SQL error log in one GO !!

July 14, 2014 Leave a comment

Hi All,

this post coming after a long time.

 

if you need to search a particular word or error code in the SQL error long,then you would have to do it one by one .

i.e .. you will proceed like below (let say for example we are looking for  connection timeout errors in Always ON setup, this could be any error or a

particular  word you are interested in looking for)

to query the current SQL error log

EXECmaster.dbo.Xp_readerrorlog 0,1,N‘A connection timeout’

0 –> current logs  | 1 –> SQL error log (also if you specify nothing in this place .. it is taken as SQL error log by default)

third parameter is something you are searching for in the error log ..

for the previous error log (ie errorlog.1) we have to use EXECmaster.dbo.Xp_readerrorlog 1,1,N‘A connection timeout’

so this becomes tedious over time .. let put this in a loop as below  …

assuming you have total of 30 previous long retained ..

use tempdb

go

CREATE TABLE #read_error_log

(

logdate DATETIME,

processinfo VARCHAR(200),

errorlogtext VARCHAR(max)

)

declare @int int= 0

while (@int <= 30)

begin

INSERT INTO #read_error_log

EXEC master.dbo.Xp_readerrorlog @int,1,N’A connection timeout’

set @int= @int+ 1

End

SELECT *

FROM  #read_error_log

ORDER BY logdateDESC

–TRUNCATE TABLE #read_error_log

–DROP TABLE #read_error_log

 

 

The above query will give you results much faster then query individual error logs ..

Refer below Links for more details on reading error logs ::

http://mssqlwiki.com/tag/sp_readerrorlog-parameters/

http://www.mssqltips.com/sqlservertip/1476/reading-the-sql-server-log-files-using-tsql/

http://strictlysql.blogspot.in/2013/05/xpreaderrorlog-in-sql-server-2012.html

Enjoy :)

 

 

Database Page Basics

December 27, 2013 Leave a comment

Hi all,

i was looking for a some basics to find out to which Table a particular page belongs to ..

Found below you tube Channel which covered everything i was looking for

check out the link and subscribe to the same ..   SQLpassion by  Klaus Aschenbrenner 

 you will cover below basics if you try out the example yourself ,

1>basic Database  PAGE Structure  and how to use DBCC IND , DBCC PAGE  and DBCC TRACEON(3604)

2> how the table partition and allocation units are related each other

3> what are different types of Allocation units  i.e in row ,LOB and row over-flow data

   Please try the example as you go through the the links 

   1 

   2

3

4

Enjoy :)

HOW TO Set up TEST Environment for SQL SERVER 2014 CTP2 or SQL SERVER 2012 PART 2

December 23, 2013 1 comment

Hi ALL,

for setting up TEST env for Server 2012 and above refer to my First post for initial details and videos from 1 to 6

remaining Videos are as below :: –>

once again CREDITS goes to metamanager  (Check out the Channel and Subscribe to the same)

Video number 7 

Video number 8 

 Video number 9

Video number 10

 

CREDITS goes to metamanager 

Thanks a lot for checking out the page .. do comment and share !!!

Enjoy

 

HOW TO Set up TEST Environment for SQL SERVER 2014 CTP2 or SQL SERVER 2012 PART 1

December 23, 2013 2 comments

Hey ALL,

if you are looking forward to set-up a test server for playing around with SQL server 2014 CTP2 release or have some

concepts cleared on always ON on SQL SERVER 2012 .. Follow on ..

Below is the flow of this post ..

1> links for pre-requisite for Setting up SQL server 2014 CTP2 or SQL Server 2012 Environment

2> Video Links for Setting up SQL Server 2012 (Same concepts and set-up is applicable to SQL Server

       2014 CTP2) 

Video links are taken from metamanager  (Check out the Channel and Subscribe to the same)

Credits for Videos goes to metamanager .. Thanks a lot for sharing them … Great for the SQL communities 

Host machine specs

–>  windows 7 ,8 GB RAM,core i5 ,around 150+ GB HD (so anything close to this or higher will be great )

download below ISO Files for

windows SERVER 2012 ,

SQL server 2012

or

SQL server 2014 CTP2

i have preferred to download ISO file (you may use CAB if you prefer , ALL links are for Evaluation Editions only )

1> Windows Server 2012 –> 

http://technet.microsoft.com/en-in/evalcenter/dn205286.aspx

 

download_ISO_file

2>  SQL Server 2012 –> 

http://www.microsoft.com/en-us/download/details.aspx?id=29066

OR

3> SQL Server 2014 CTP2 –>

 http://technet.microsoft.com/en-US/evalcenter/dn205290.aspx

 4>  VMWARE workstation or Hyper-V is required to set-up the Virtual environment

  https://my.vmware.com/web/vmware/info/slug/desktop_end_user_computing/vmware_workstation/10_0

                                                                                               OR

 –> if you are interested in trying out Hyper-V then check out This links  (it is also  present as a feature in Server 2012)

         Once you are done with downloading the OS,SQL Server and VMware (or Hyper-V)

              Follow below Videos to Set-up your Env ..

                                                                             1> 

 

                                                                            2> 

 

                                                                           3> 

 

 

                                                                         4>

 

 

                                                                        5>

 

 

                                                                     6>

 

 

REST of the Videos .. i will share in second post ..

once again CREDITS goes to metamanager  (Check out the Channel and Subscribe to the same)

Enjoy :)

Rename multiple FILES using POWERSHELL

November 30, 2013 Leave a comment

Requirement ::

I needed to upload my Educational certificates onto a particular websites ..

after i was done with scanning my docs .. i never realised that the sites wont accept PDF or any other fromat Documents

which had special Characters in the name Exxmple –> ( ,&,*,@,$ ETC …

in my case the Documents were named Document(1).PDF 

PS_rename

so i needed to get there name changed to just Document1.PDF 

aligned snapshot is the what my docs looked

POWERSHELL SCRIPT ::

to change the names of the PDF files .. used POWERGUI and used below command to set my location to the path which

contained the PDF files

SL stands for SET LOCATION and is equvalent to CD

sl ‘D:\GS\DOCS\TEST_scancopy’

then i used below command to get a count of files that had round brackets [ ( )  ] in their names

PS_rename._2JPGget-childitem -Filter ‘*(*’ | measure

this gave me idea of how many docs need to renamed …

i used a while loop to run for 29 times … to ensure all the PDF files

having *(* present in their name are set to required name ..

The rename-item was used to set the names of PDF files .

below is PS Script i used to rename the docs … as i have around 29 docs .. i have used 30 as the no. of times the loop will run through

___________________SCRIPT  STARTs __________

$int = 1

Write-Host $int

while ($int -ne 30)
{
Write-Host ‘within while loop’
Write-Host $int

$var = ‘Document’+ $int.ToString()+’.PDF’

# the names to which docs will be set

get-childitem -Filter ‘*(*’ | select -first 1 | rename-item -NewName $var

Write-Host ‘renamed to ‘ $var

$int ++

}

___________________SCRIPT  ENDs __________

The SCRIPT ran fine ..

you may use similar approach to tackle .. any other requirement 

specific to your need  ENJOY :) 

PS_rename._4

 

PS_rename._3

Running DBCC CHECKDB on all databases at once !!!

November 26, 2013 1 comment

Writing with Ink

Issue ::

some times it so happens .. that the  storage assigned to SQL servers may take a sudden

unexpected reboot .. and in those moments we may have LOST WRITES in

          databases .. which can cause them to be marked as suspect or causing 

          issues while accessing objects … 

 

in those moments we have to ensure consistancy and allocation across all DBs

are intact .. running DBCC CheckDB manually on each and every option is time

consuming (plus multiple windows  are to be opened)

SCRIPT ::  below script will help us run DBCC checkDB on all DBs once we have count of all DBs present on our instance ..  (the other option you have to achieve the same is to use CHECKDB job .. but with below script you will be able to view the errors in Query analyser itself)

 

1> use sp_helpdb .. to check total number of databases present on your instance …

according to the total number of DBs .. use dbid >= * and dbid <= * to cover all DBs

—Script to Run in different windows

—— by commenting and dbid >= * and dbid <= * part in the where clause

 

 

—————————————-SCRIPT START ————————————————-

DECLARE @name VARCHAR(50) — database name
DECLARE @SQL VARCHAR(256) — SQL command

 

 

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN (‘tempdb’)
and dbid >= 1 and dbid <= 20 —- window 1
–and dbid >= 21 and dbid <= 50 —- uncomment and run from QA window 2
–and dbid >= 51 and dbid <= 100 —- uncomment this and run from QA window 3
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

 

 

WHILE @@FETCH_STATUS = 0
BEGIN
–SET @fileName = @path + @name + ‘_’ + @fileDate + ‘.BAK’
BACKUP DATABASE @name TO DISK = @fileName

 

 

set @SQL = ‘DBCC CHECKDB’ + ‘ ‘ + ‘(‘+@name + ‘)’
print @SQL

exec (@SQL)

 

 

FETCH NEXT FROM db_cursor INTO @name
END

 

 

CLOSE db_cursor
DEALLOCATE db_cursor

 

 

—————————————-SCRIPT END————————————————-

 

DBCC_CHECKDB_ALL

 

you should see the output similar to above ..

go throught the errors and see if Allocation or integrity errors are reported for any DB …

accordingly use Repair_REBUILD or REPAIR_ALLOW_DATA_LOSS of DBCC CheckDB ..

alway prefer to use lastest Good backup over using above repair option !!!!

ENJOY :)

 

 

Follow

Get every new post delivered to your Inbox.