Developers Club geek daily blog

Story about msdb of 42 GB in size

1 year, 1 month ago
Recently minute was issued to look why the old test server godlessly braked … I had no relation to it, but I was overcome by sports interest to understand that is with it not so.

First of all opened Resource Monitor and looked at a total load. Process of sqlserv.exe loaded the CPU under 100% and created big disk queue which was for 300 … while value above of unit is already considered problem.

In the analysis of disk activity noticed continuous IO operations in msdb:

D:\SQL_2012\SYSTEM\MSDBData.mdf
D:\SQL_2012\SYSTEM\MSDBLog.ldf

Looked at the msdb size:

SELECT name, size = size * 8. / 1024, space_used = FILEPROPERTY(name, 'SpaceUsed') * 8. / 1024
FROM sys.database_files

also included the hand person mode:

name         size           space_used
------------ -------------- ---------------
MSDBData     42626.000000   42410.374395
MSDBLog      459.125000     6.859375

The data file occupied 42 GB … Having taken a small break I began to understand what the reason of such unhealthy volume of msdb and how to overcome problems with server performance.

Read more »


DataGrip release (ex-0xDBE) 1.0 — new IDE for SQL

1 year, 1 month ago
Hi! We let out IDE for work with databases.

One and a half years we did 0xDBE according to the program of early access (EAP). And here, we understood that it is time to put end under our work. We thank all who tried 0xDBE on the projects and wrote us — you very much helped. We will miss this name too.

Now IDE is called DataGrip.



The supported DBMS

DataGrip is universal IDE for work with MySQL, PostgreSQL, Oracle, SQL Server, Sybase, DB2, SQLite, HyperSQL, Apache Derby and H2.

Work with objects of a DB and code generation

DataGrip provides tools for work with database objects. If you create or change the table, add or change a column, an index, a key in already existing, use the graphic interface. Similar changes are followed by generation of the corresponding script — you can execute the made changes in base at once or copy the generated DDL request in the editor and work already directly with a code.


Read more »


XML, XPath and threefold grief with a performance

1 year, 1 month ago
Trip to Dnipropetrovsk, chronic sleep debt the last couple of days, but a pleasant bonus on arrival to Kharkiv … A winter weather which motivates on writing something interesting …

For a long time in plans was to tell about "reefs" during the work with XML and XQuery which can lead to tricky problems with performance.

Generally, for those who often uses SQL Server, XQuery and likes to parsit values from XML it is recommended to get acquainted with the following material …

Read more »


Free tools for developers of databases

1 year, 1 month ago
After reading of article about useful and free plug-ins for SSMS — TOP (10) free plug-ins for SSMS, I decided to share the list of free tools which can be very useful to developers of databases, and not only. Some tools, paid, however contain rather full-function free editions which with the head are enough for small projects.

Read more »


TOP (10) free plug-ins for SSMS

1 year, 1 month ago
Before beginning to work with SQL Server I sense that and did not see databases in the person … I remember that set me SQL Server Management Studio 2005 and gave a task actively "to twist pedals".

After few weeks, as if so to speak, my performance on writing of requests was obviously around a plinth …

More experienced colleague bewildered looked at me and advised to deliver couple of plug-ins for SSMS

After that to work it became explicit more cheerfully.

In this post I want to share mine a top the list of free plug-ins for SSMS which I most often used.

Read more »


Dynamic T-SQL and as it can be useful

1 year, 1 month ago
In our projects we should solve different problems. For a solution of some of them we use dynamic T-Sql (further in the text of dynamic sql).

For what dynamic sql is necessary? Everyone solves for himself. In one of projects with the help of dynamic sql we solved problems of creation of dynamic reports, in others — migration of data. Also dynamic sql is irreplaceable in cases when it is required to create/change/obtain data or objects, but values/names come as parameters. Yes, it can seem absurdity, but there are also such tasks.


Read more »


We use TSQL for game in "Sudoka"

1 year, 1 month ago
After by means of TSQL it was successful "Head" (article) is solved I decided to try to solve on it "Sudoka" (thanks for idea of shavluk).

The solution to a pike perch turned out surprisingly rather simple.

The basic scheme has the following appearance:

Read more »


SQL Server 2016 CTP3.1 — that new to the developer?

1 year, 1 month ago
Until recently in my memory the announcement of SQL Server 2016 which was personally presented by Satya Nadella was postponed. And suddenly, suddenly, steel one behind one to leave fresh Community Technology Preview (at the moment the freshest version – CTP3.1). In process of acquaintance to the new version, there was a wish to share impressions more and more …

Further overview of new syntax counters of new SQL Server 2016.

#1 – DROP IF EXISTS


CREATE TABLE dbo.tbl (
    a INT, b INT,
    CONSTRAINT ck CHECK (a > 1),
    INDEX ix CLUSTERED (a)
)

If earlier before removal of object it was necessary to do check:

IF OBJECT_ID(N'dbo.tbl', 'U') IS NOT NULL
    DROP TABLE dbo.tbl

That more compact syntax appeared now:

DROP TABLE IF EXISTS dbo.tbl

Read more »


We use TSQL for game in "Head"

1 year, 1 month ago
Recently I remembered the remarkable intellectual game "Head" with which I got acquainted in school days.

The other day I asked a question – it will be how difficult to implement algorithm of this game for the computer rival?

As I most of all like to work with relational data and my favourite language SQL is, I decided to combine business with pleasure and to try to write this algorithm using only TSQL. It is my first attempt to write AI using only possibilities of SQL.

The archive with files can be downloaded at the following link – scripts.
All words in the dictionary in an uppercase, and also in it is mute letters "E" and "Yo" are considered for one (as "Е").

The following scheme was as a result created:

Read more »


Instant File Initialization – the killer feature for SQL Server

1 year, 2 months ago
When SQL Server reserves the new place on a disk, it initializes it in zero. This behavior can be disconnected and as a result to reduce runtime of some operations and to lower load of a disk subsystem.

The possibility of reservation of the place on a disk without initialization is called Instant File Initialization (instant initialization of files). This feature is not strongly known though its use became possible since SQL Server of 2005.

What advantages can be got from Instant File Initialization:

1. To accelerate creation of the new database
2. To reduce delays and to reduce time necessary for increase in data files
3. To reduce time of start of SQL Server as initialization of tempdb will be faster
4. To reduce time at recovery from a backup copy as before recovery of SQL Server reserves the space for files, and then transfers to them information from a backup.

Read more »