Thursday, 3 November 2022

SCRIPT TO KILL ALL ACTIVE CONNECTIONS BEFORE A BACKUP OR RESTORE


Use Master

Go


Declare @dbname sysname


Set @dbname = 'IEMS'


Declare @spid int

Select @spid = min(spid) from master.dbo.sysprocesses

where dbid = db_id(@dbname)

While @spid Is Not Null

Begin

        Execute ('Kill ' + @spid)

        Select @spid = min(spid) from master.dbo.sysprocesses

        where dbid = db_id(@dbname) and spid > @spid

End



-- RESTORE DATABASE

RESTORE DATABASE XXXX_DB FROM DISK = 'D:\Backups\XXXX_DB-20221031-190000.BAK'

GO

-- IF THE DATABASE IS TO BE RE-WRITTEN OR REPLACED

RESTORE DATABASE XXXX_DB FROM DISK = 'D:\Backups\XXXX_DB-20221031-190000.BAK'

WITH REPLACE

GO


SCRIPT TO CHECK DATABASE BACKUP OR RESTORE STATUS


USE master

GO




SELECT

req.session_id,

database_name = db_name(req.database_id),

req.status,

req.blocking_session_id,

req.command,

[sql_text] = Substring(txt.TEXT, (req.statement_start_offset / 2) + 1, (

(

CASE req.statement_end_offset

WHEN - 1 THEN Datalength(txt.TEXT)

ELSE req.statement_end_offset

END - req.statement_start_offset

) / 2

) + 1),

req.percent_complete,

req.start_time,

cpu_time_sec = req.cpu_time / 1000,

granted_query_memory_mb = CONVERT(NUMERIC(8, 2), req.granted_query_memory / 128.),

req.reads,

req.logical_reads,

req.writes,

eta_completion_time = DATEADD(ms, req.[estimated_completion_time], GETDATE()),

elapsed_min = CONVERT(NUMERIC(6, 2), req.[total_elapsed_time] / 1000.0 / 60.0),

remaning_eta_min = CONVERT(NUMERIC(6, 2), req.[estimated_completion_time] / 1000.0 / 60.0),

eta_hours = CONVERT(NUMERIC(6, 2), req.[estimated_completion_time] / 1000.0 / 60.0/ 60.0),

wait_type,

wait_time_sec = wait_time/1000,

wait_resource

FROM sys.dm_exec_requests as req WITH(NOLOCK)

CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) as txt

WHERE req.session_id>50

AND command IN ('BACKUP DATABASE', 'BACKUP LOG', 'RESTORE DATABASE', 'RESTORE LOG')

Sunday, 11 October 2020

Git - How does it work?

Article by - Michael J

The Bare Minimum You Need To Know To Work With Git

Filed under: Technical Articles — Michael J. Swart @ 9:00 am

I don’t like using git for source control. It’s the worst source control system (except for all the others). My biggest beef is that many of the commands are unintuitive.

Look how tricky some of these common use cases can be: Top Voted Stackoverflow Questions tagged Git. The top 3 questions have over ten thousand votes! This shows a mismatch between how people want to use git and how git is designed to be used.

I want to show the set of commands that I use most. These commands cover 95% of my use of git.
stupid git

Initial Setup

One-time tasks include downloading git and signing up for github or bitbucket. My team uses github, but yours might use gitlab, bitbucket or something else.

Here’s my typical workflow. Say I want to work on some files in a project on a remote server:

Clone a Repository

My first step is to find the repository for the project. Assuming I’m not starting a project from scratch, I find and copy the location of the repository from a site like github or bitbucket. So the clone command looks like this:

git clone https://github.com/SomeProject/SomeRepo.git

This downloads all the files so I have my own copy to work with.

Create a Branch

Next I create a branch. Branches are “alternate timelines” for a repository. The real timeline or branch is called master. One branch can be checked out at a time, so after I create a branch, I check out that branch. In the diagram, I’ve indicated the checkout branch in bold. I like to immediately push that branch back to the remote server. I can always refer to the remote server as “origin”. All this is done with these commands:

git branch myBranch
git checkout myBranch 
git push -u origin myBranch

Change Stuff

Now it’s time to make changes. This has nothing to do with git but it’s part of my workflow. In my example here I’m adding a file B.txt.

Stage Changes

These changes aren’t part of the branch yet though! If I want them to be part of the branch. I have to commit my changes. That’s done in two parts. The first part is to specify the changes I want to commit. That’s called staging and it’s done with git add. I almost always want to commit everything, so the command becomes:

git add *

Commit Changes

The second part is to actually commit the files to the branch with a commit message:

git commit -m "my commit message"

Push Changes

I’m happy with the changes I made to my branch so I want to share them with the rest of the world starting with the remote server.

git push origin myBranch

Create a Pull Request and Merge to master

In fact I’m so happy with these changes, I want to include them in master, the real timeline. But not so fast! This is where collaboration and teamwork become important. I create a pull request and then if I get the approval of my teammates, I can merge.

It sounds like a chore, but luckily I don’t have to memorize any git commands for this step because of sites like github or bitbucket. They have a really nice web site and UI where teams can discuss changes before approving them. Once the changes are approved and merged, master now has the changes.

Once it’s merged. Just to complete the circle, I can pull the results of the merge back to my own computer with a pull

git pull
git checkout master

Other Use Cases

Where Am I?
To find out where I am in my workflow, I like to use:

git status

This one command can tell me what branch I’m on. Whether there are changes that can be pushed or pulled. What files have changed and what changes are staged.

Merge Conflicts
With small frequent changes, merge conflicts become rare. But they still happen. Merge conflicts are a pain in the neck and to this day I usually end up googling “resolving git merge conflicts”.

Can’t this Be Easier?

There are so many programs and utilities available whose only purpose is to make this stuff easier. But they don’t. They make some steps easy, and some steps impossible. Whenever I really screw things up, I delete everything and start from scratch at the cloning step. I find I have to do that more often when I use a tool that was supposed to make my life easier.

One Exception
The only exception to this rule is Visual Studio Code. It’s a real treat to use. I love it.

Maybe you like the command line. Maybe you have a favorite “git-helper” application. No matter how you use git, in every case, you still have to understand the workflow you’re using and that’s what I’ve tried to describe here.

Where To Next

If you want to really get good at this stuff. I recently learned of a great online resource (thanks Cressa!) at https://learngitbranching.js.org/. It’s a great interactive site that teaches more about branching. You will very quickly learn more than the bare minimum required. I recommend it.

Friday, 25 October 2019

Rebuild or Reorganize index SQL Server

Introduction
There are two solutions available to you depending on the severity of your issue
Replace with your own values, as follows:
  • Replace XXXMYINDEXXXX with the name of an index.
  • Replace XXXMYTABLEXXX with the name of a table.
  • Replace XXXDATABASENAMEXXX with the name of a database.

Solution 1. Indexing

Rebuild all indexes for a table in offline mode
ALTER INDEX ALL ON XXXMYTABLEXXX REBUILD
Rebuild one specified index for a table in offline mode
ALTER INDEX XXXMYINDEXXXX ON XXXMYTABLEXXX REBUILD

Solution 2. Fragmentation

Fragmentation is an issue in tables that regularly have entries both added and removed.
Check fragmentation percentage
SELECT  
    ips.[index_id] ,
    idx.[name] ,
    ips.[avg_fragmentation_in_percent]
FROM    
    sys.dm_db_index_physical_stats(DB_ID(N'XXXMYDATABASEXXX'), OBJECT_ID(N'XXXMYTABLEXXX'), NULL, NULL, NULL) AS [ips]
    INNER JOIN sys.indexes AS [idx] ON [ips].[object_id] = [idx].[object_id] AND [ips].[index_id] = [idx].[index_id]

Fragmentation 5..30%

If the fragmentation value is greater than 5%, but less than 30% then it is worth reorganising indexes.
Reorganise all indexes for a table
ALTER INDEX ALL ON XXXMYTABLEXXX REORGANIZE
Reorganise one specified index for a table
ALTER INDEX XXXMYINDEXXXX ON XXXMYTABLEXXX REORGANIZE

Fragmentation 30%+

If the fragmentation value is 30% or greater then it is worth rebuilding then indexes in online mode.
Rebuild all indexes in online mode for a table
ALTER INDEX ALL ON XXXMYTABLEXXX REBUILD WITH (ONLINE = ON)
Rebuild one specified index in online mode for a table
ALTER INDEX XXXMYINDEXXXX ON XXXMYTABLEXXX REBUILD WITH (ONLINE = ON)

Connect to SQL Server with .bat file to run .SQL commands.

Save the following commands in a notepad with .bat as extension-

SQL Auth Mode:

REM name of the server that hosts the SQL Server DB
set svr= localhost
REM name of the DB
set dbname=databaseNAME
REM credentials of the user that has permissions to create DB objects
set uname=USERname
set pwd=PASSword

osql -S %svr% -d %dbname% -U %uname% -P %pwd% -i "Create_Table"  -o "Create_Table.txt"  -n


Windows Auth Mode:

REM name of the server that hosts the SQL Server DB
set svr= localhost
REM name of the DB
set dbname=databaseNAME

osql -E -S %svr% -d %dbname% -i "Create_Table.sql"  -o "Create_Table.txt"  -n


Wednesday, 23 October 2019

How to search for Arabic words using Thesaurus XML file in SQL Server.


Accent insensitive search in full-text catalog!


Turning off Accent insensitive option for existing search catalog will allow to search Arabic words using the Thesaurus feature in SQL Server.

In SQL server 2008 noise words have been replaced by stop words. http://msdn.microsoft.com/en-us/library/ms142551.aspx
And it is as you suspected; ”been” is classified as a stop word in the standard list for the English language. When the fulltext catalog was created with accent sensitivity the word “béen” was hit in searches. But when I created the index with accent insensitivity “béen” became equal to “been” and was therefore treated as a stop word. This explains the weird behavior and why “béen” would not be hit when accent insensitivity was set.
I used the following query to find the stoplist for my index. It was set to the value 0 meaning the default list was being used.
select fulltext_catalog_id,stoplist_id, * from sys.fulltext_indexes;
And this to get the list of words from the default list for the language.
select ssw.stopword, slg.name as language
from sys.fulltext_system_stopwords ssw
join sys.fulltext_languages slg
on slg.lcid = ssw.language_id and ssw.language_id = 1033;
The question is what I should do about this. Changing language from English doesn’t seem like such a good option since we have users from Sweden, Denmark, Estonia, Great Britain and soon Finland. “Béen” is actually the last name of one users.
UPDATE
I ended up with this solution.
-- Create stoplist copy from system stoplist
CREATE
 FULLTEXT STOPLIST SL FROM SYSTEM STOPLIST;
-- Set the index to use the new stoplist
ALTER
 FULLTEXT INDEX ON searchtable set stoplist SL
-- Removing "stop" word from stoplist
ALTER FULLTEXT STOPLIST SL
DROP 'been' LANGUAGE 'English';

Thanks for all the help finding this Erland.