Monday, August 18, 2014

SQL Server Bulk Session kill script

Ask for SQL server instance, login and hostname for which the sessions need to be killed and replace the value of @login and @host in below script and execute it on the servers where the session need to be killed.

declare @login varchar (200)
declare @host varchar (200)
declare @id int
declare @host_desc varchar(200)
declare @login_desc varchar (200)
declare @str nvarchar(200)
/* Replace login and hostname value*/
SET @login = '<login-need-to-be-killed>'
SET @host = '<login-from-which-host>'


DECLARE cur_kill CURSOR
STATIC FOR
SELECT  spid SPID,
        convert(sysname,rtrim(loginame)) Login,
        convert(varchar(50),rtrim(hostname)) Host
    FROM
        master.dbo.sysprocesses a (nolock)
        inner join master.dbo.sysdatabases b (nolock) on a.dbid=b.dbid
    WHERE
        spid > 50 and loginame = @login and hostname = @host
    ORDER BY
        cpu desc
      
        OPEN cur_kill
IF @@CURSOR_ROWS > 0
BEGIN
FETCH NEXT FROM cur_kill INTO @Id, @login_desc, @host_desc
WHILE @@Fetch_status = 0
BEGIN
set @str = 'kill ' + cast(@id as nvarchar(max))
--print @str
exec (@str)
PRINT '[KILLED] - ID : '+ convert(varchar(20), @Id ) + ', Login : '+@login_desc+ ', Host : '+convert(varchar(20),@host_desc)
FETCH NEXT FROM cur_kill INTO @Id, @login_desc, @host_desc
END
END
CLOSE cur_kill
DEALLOCATE cur_kill

In Order to run a trace against SQL Server you must be a member of sysadmin. (Enable trace access without granting sysadmin role)

Use below query to enable trace access without granting sysadmin role.


Use Master;
GRANT VIEW SERVER STATE TO [Domain\ADGroup or User];
GRANT SHOWPLAN TO [Domain\ADGroup or User];
GRANT ALTER TRACE TO [Domain\ADGroup or User];




Checkout Deal of the Day on Amazon.

SQL Query to get IP address of local & client machine



SELECT
   CONNECTIONPROPERTY('net_transport') AS net_transport,
   CONNECTIONPROPERTY('protocol_type') AS protocol_type,
   CONNECTIONPROPERTY('auth_scheme') AS auth_scheme,
   CONNECTIONPROPERTY('local_net_address') AS local_net_address,
   CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port,
   CONNECTIONPROPERTY('client_net_address') AS client_net_address;
Tuesday, April 2, 2013

Facebook Feed is not getting displayed in latest release of Skype - version 6.3

Hi Guys, Yes i know this post is something out of the scope of this blog but still i though to write this as i believe most of us uses Skype for communication, and if you have upgraded to Skype latest version 6.3 then you must have noticed Facebook Feed Disconnection on Skype Home Page which is very annoying.

I tried to dig it a bit deeper and found the reason, but don't be happy i do not have the fix for this :(

When you first connect to Facebook using Skype, it ask for some permission. On of them is Permission to access your data when you are offline which has been Deprecated by Facebook recently.




You might see Facebook wall for a while and suddenly it goes and you left with the prompt to connect to Facebook again :)


If you wish to dig it deeper then Login to Facebook and go to Security Setting -> App Setting click on edit against Skype.


And you will get actual error here:





Guys help me with the solution if you have any? or i hope Microsoft/Skype Team will see my post and soon release a fix for this ;)


Thursday, October 4, 2012

Change the email across all Job alerts using script in SQL Server


As a DBA sometimes we get request for add/update/delete of DL’s – Distribution Lists (email groups) across all jobs on a SQL Server Instances. If the frequency of such request is very frequent then it’s better to write a script and run it on requests by simply modifying the email addresses.

Here is how we will do this:

We will take help of 2 tables  msdb.dbo.sysjobsteps and msdb.dbo.sysjobsteps

Query to retrive the list of jobs along with steps which satisfies the condition to search for an email (Simply update the “your-email-address-here” below and execute the query).

SELECT j.name, js.step_name, js.command FROM msdb.dbo.sysjobsteps js WITH(NOLOCK)
LEFT JOIN msdb.dbo.sysjobs j WITH(NOLOCK)
ON j.job_id = js.job_id  
WHERE js.command  like '%<your-email-address-here>%'



Update the old DL with new:

UPDATE msdb.dbo.sysjobsteps
SET command = REPLACE(command,'<search-email-group>','<new-email-group>')
WHERE command  like '%<your-search-term-here>%'
Wednesday, July 4, 2012

Reset MySQL root password in Windows and UNIX

1.    Shutdown the MySQL Server –

a.    Use windows services to shut down: Start > run > services.msc  > MySQL > right click and select STOP 




b.    Using mysqladmin by running following command in command prompt:

mysqladmin -u root shutdown (make sure to provide the correct path for mysqladmin if it has not been added in windows path)

2.     Start the MySQL Server in safe mode (anonymous login):

         C:\>start mysqld-nt --skip-grant-tables (A black screen will flicker and here you go)




        In UNIX environment u has to use below command:

              mysqld_safe --skip-grant-tables

3.    Now you open the command line and try to connect to the root user with empty password by running below command:

             C:\>Mysql -uroot

4.    when you logged in u will get a prompt as

             mysql>

5.    Now select the MySQL database:

             use mysql;




6.    Update the root password by running this command:

             mysql> update user set Password=PASSWORD('<new-password>') WHERE User='root';



7.    Shutdown the MySQL Server:  (it is very important to stop the server at this stage since it is running in anonymous mode):

              mysqladmin -u root shutdown

8.    Now start server again and can log in with new password.
Thursday, June 7, 2012

Simple script to find the free space in sql server db files (data, log) against all databases

Here is a simple SQL script to retrieve the free spaces on db files (data, log).

The below script will give you the File size, used space and free space of currently selected database only.


select
      name
    , filename
    , convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB
    , convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)) as SpaceUsedMB
    , convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) as FreeSpaceMB
    from dbo.sysfiles a


Output:



To retrieve the File size, used space and free space across all databases i have written a script using above query.


-- create a temporary table to insert the above mentioned output against each databases.

create table #db_files(
db_files varchar(300),
file_loc varchar(300),
filesizeMB decimal(9,2),
spaceUsedMB decimal(9,2),
FreespaceMB decimal(9,2))

declare @strSQL nvarchar(2000)
DECLARE @dbName varchar(MAX)
DECLARE @getDBname CURSOR

SET @getDBname = CURSOR FOR
select name from sys.databases

OPEN @getDBname
FETCH NEXT
FROM @getDBname INTO @dbName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @dbName

select @strSQL =
    '
        use ' + @dbname + '
        INSERT INTO #db_files
        select
      name
    , filename
    , convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB
    , convert(decimal(12,2),round(fileproperty(a.name,''SpaceUsed'')/128.000,2)) as SpaceUsedMB
    , convert(decimal(12,2),round((a.size-fileproperty(a.name,''SpaceUsed''))/128.000,2)) as FreeSpaceMB
    from dbo.sysfiles a
    '
    exec sp_executesql @strSQL

FETCH NEXT
FROM @getDBname INTO @dbName

END
CLOSE @getDBname
DEALLOCATE @getDBname
GO

select * from #db_files
-- drop the temporary table
drop table #db_files




Output:






This script will be helpful for lot of SQL Server DBA's out there.. cheers..  

Tuesday, May 29, 2012

Space Management Report - Get Drive Size and Drive Free Space :SQL Server

One of the regular DBA practice is to maintain the free space on Drives where the data files are located.

Here i have created a script (Space Management) which gives us the database files, its location,size, growth rate, the drive where its located, drive size, drive free space etc...

Most of the thing is very easy to retrieve through SQL Queries as those are maintained in tables under msdb databases, but the free space and size of drive is something which require system interaction using master..xp_cmdshell and that part of query is a bit complex :)



declare @Drive table(DriveName char,freespace decimal(9,2), totalspace decimal(9,2),percentfree decimal(9,2))

BEGIN

    SET NOCOUNT ON;

    DECLARE @v_cmd nvarchar(255)
    ,@v_drive char(99)
    ,@v_sql nvarchar(255)
    ,@i int

    SELECT @v_cmd = 'fsutil volume diskfree %d%'
    SET @i = 1

    -- Creating temporary tables to retrive system space information.
    CREATE TABLE #drives(iddrive smallint ,drive char(99))

    CREATE TABLE #temp1(drive char(99),shellCmd nvarchar(500));

    CREATE TABLE #results(drive char(99),freespace decimal(9,2), totalspace decimal(9,2));


    INSERT #drives (drive)

    EXEC master..xp_cmdshell 'mountvol'

    DELETE #drives WHERE drive not like '%:\%' or drive is null


    WHILE (@i <= (SELECT count(drive) FROM #drives))

    BEGIN

         UPDATE #drives
         SET iddrive=@i
         WHERE drive = (SELECT TOP 1 drive FROM #drives WHERE iddrive IS NULL)

         SELECT @v_sql = REPLACE(@v_cmd,'%d%',LTRIM(RTRIM(drive))) from #drives where iddrive=@i

         INSERT #temp1(shellCmd)
         EXEC master..xp_cmdshell @v_sql

         UPDATE #temp1
         SET #temp1.drive = d.drive
         FROM #drives d
         WHERE #temp1.drive IS NULL and iddrive=@i

        SET @i = @i + 1

    END


        INSERT INTO #results
        SELECT bb.drive
        ,CAST(CAST(REPLACE(REPLACE(SUBSTRING(shellCmd,CHARINDEX(':',shellCmd)+1,LEN(shellCmd)),SPACE(1),SPACE(0))
        ,char(13),SPACE(0)) AS NUMERIC(32,2))/1024/1024/1024 AS DECIMAL(9,2)) as freespace
        ,tt.titi as total
        FROM #temp1 bb
        JOIN (SELECT drive
        ,CAST(CAST(REPLACE(REPLACE(SUBSTRING(shellCmd,CHARINDEX(':',shellCmd)+1,LEN(shellCmd)),SPACE(1),SPACE(0))
        ,char(13),SPACE(0)) AS NUMERIC(32,2))/1024/1024/1024 AS DECIMAL(9,2)) as titi
        FROM #temp1
        WHERE drive IS NOT NULL
        AND shellCmd NOT LIKE '%free bytes%') tt
        ON bb.drive = tt.drive
        WHERE bb.drive IS NOT NULL
        AND bb.shellCmd NOT LIKE '%avail free bytes%'
        AND bb.shellCmd LIKE '%free bytes%';

    -- getting all system specific formatted data into @Drive table
    INSERT INTO @Drive
    SELECT LEFT(RTRIM(LTRIM(drive)),1) as drive
     ,freespace
     ,totalspace
     ,CAST((freespace/totalspace * 100) AS DECIMAL(5,2)) as [percent free]
    FROM #results
    ORDER BY drive

    -- Dropping temporary tables
    DROP TABLE #drives
    DROP TABLE #temp1
    DROP TABLE #results

    SELECT
         @@Servername as Server,
         sd.name as Database_name,
        mas.name as File_Name,
        mas.size * 8 / 1024 File_Size_MB,
        CASE [is_percent_growth]  
               WHEN 1 THEN CAST(mas.growth AS varchar(20)) + '%' 
                ELSE CAST(mas.growth*8/1024 AS varchar(20)) + ' MB' 
        END as Growth_rate,     
        drv.DriveName as Drive_Name,
        drv.FreeSpace,
        drv.totalspace,
        CASE type
               WHEN 0 THEN 'Data'  
               WHEN 1 THEN 'Log' 
        END as File_type, 
        mas.physical_name as File_Location
    FROM sys.master_files mas
    LEFT JOIN @Drive drv ON LEFT(mas.physical_name, 1) = drv.DriveName
    LEFT JOIN sys.databases sd ON mas.database_id = sd.database_id
    --WHERE sd.name IN ('db1','db2','db3') -- Specify the database name if this report is needed for specific databases
    --AND mas.growth <>0 -- Condition to retrive the record for growing files only.

END


  • You can restrict the output for some of the databases by providing the database name which is commented by default.

             --WHERE sd.name IN ('db1','db2','db3')  

  •  You can also restrict the output for growing files of the databases only by providing the growth <>0 which is commented by default.
             --AND mas.growth <>0

There are further filters also possible like if someone want to restrict the query upto a size limit of files etc. simply write it down in where clause.


Output:

Output should be like this:




This could be very useful where someone have multiple servers and DBA has to generate weekly/Monthly report for all of the server, in such scenario one can create a SSIS package and execute this query on all the server and generate a single report for all the servers.

Hope this will be helpful for people out there :) let me know if anyone has any issue.




 
© Copyright 2010-2012 Learn MySQL All Rights Reserved.
Template powered by Blogger.com.