THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Damian Widera

  • SQLRally Nordic - after conference thoughts

    Hello All

    I was extremely honored to be able to participate in the SQLRally Nordic this year. The event took place this week in Copenhagen and I think it was a great success - from every angle. Many thanks to the organizers and many thanks to the community for being in that place. There was a great atmosphere during the whole event and what I like the most as a speaker - I had a great room for my speech! 

    I hope that the event will take place also next years but now I would like to invite to the annual PLSSUG conference to Wrocław. Let's meet in May. At least half of the tracks will be in English and you could meet also may world-class speakers. Just visit the page




  • Memory leak occurs when you run DBCC CHECKDB against a database in SQL Server 2014 - fixed in SQL Server 2014 CU6

    Great news today!

    At the end of January it was a Connect item  ( that described a possible memory leak on the SQL Server 2014.

    The details are as follow: 

    sys.dm_os_memory_clerks for type = MEMORYCLERK_SQLQUERYEXEC and sys.dm_os_memory_objects for type = MEMOBJ_COLUMNBATCH show ever increasing values when columnstore tables are integrity checked. For example, if you run DBCC CHECKDB on a database that contains columnstore tables or you run DBCC CHECKTABLE on a columnstore table you will find that the memory shown for the above mentioned clerk and object types is ever increasing.  

    The good news is that the problem is fixed and the solution will be available in the SQL Server 2014 CU6. Find more details: 





  • SQL Server 2012 SP2 Cumulative Update 4 Released - information from the SQL Server SE Operations Team

    As per information from Microsoft:

    "It is my pleasure to announce the release of SQL Server 2012 SP2 Cumulative Update 4 on behalf of the team.  SQL Server 2012 SP2 Cumulative Update 4 incorporates 42 issues.

    This CU will be available for download from the associated cumulative KB article that has also been published.  Customers are directed to contact CSS to get the CU build or obtain the hotfix package through the new self-service feature by clicking on the “Hotfix Download Available” button found at the top of the KB article."

    To me the most important fix is the one regarding the possibility of having errors 17066 or 17310 during SQL Server startup (immediately after database recovery is complete and client connections are enabled.). Check out the link for details

    Link to the official site of this update:




  • Kimberly Tripp is going to visit PLSSUG in January (online meeting)

    Paul Randal has recently proposed that SQLSkills could give sessions on the user groups meetings. I emailed him immediately and - making the long story short - the PLSSUG (Polish SQL Server Users Group) will be hosting Kimberly Tripp on 29 th January at 6PM local time. Please visit the  community webpage 

    Here are some details:

    Stored Procedure Optimization Techniques

    Presenter: Kimberly L. Tripp

    Abstract: Kimberly will discuss stored procedure caching and the potential for performance problems due to a problem known as parameter sniffing. There are some common solutions and while they can drastically improve performance, they can also cause other problems. Sometimes a hybrid solution is needs, from this session, you’ll know the best way to write procedures so that stable requests are cached and unstable requests are not (giving you the most optimal performance without wasting resources).


    Bio: Kimberly L. Tripp, President / Founder,

    Kimberly has been a Microsoft SQL Server MVP and a Microsoft Regional Director for over 10 years; she's an author, consultant, and top-rated speaker having spoken at PASS, TechEd, SQLintersection, and numerous other events around the world. She has over 20 years of experience designing and tuning SQL Server solutions with a focus on optimizing procedural code, indexing strategies, statistics, and partitioning. When she's not tuning SQL servers, she's diving and photographing all sorts of underwater critters such as frogfish with her husband, Paul S. Randal.

    I hope that this is great news.

    And I promise - this will not be the only meeting :).

    More details will follow on the community webpage (as well as information how to participate in that meeting) -






  • SQLDay 2015 - should we plan it now? Yes - check out who will join us :)

    SQL Folks, SQL Geeks and others....

    Is it the right time  to plan the next conference when the previous was finished last week? YES!!!! It is the right time!!! The PLSSUG is planning to have the biggest community conference ever. It will start at 11th May (Monday - preconf) and will be till Wednesday, 13th May (at least). The first great news is that Adam Machanic will be with us. So do not wait and register for his preconf and sessions :). Adam will not be the only superstar next year but more details will follow. Remember, that this year Bob Ward, Klaus Aschenbrenner and Alberto Ferrari were the special guests. And do not forget about the local leaders - you should have seen them last week in Lublin. So you know what am I talking about.

    So, stay tuned, vist the PLSSUG page frequently





  • Conference - ITAD Bielsko Biała

    Good evening

    Today I had a lecture on the academic conference ITAD ( That is amazing that 300  people came and took part in the event. 

    I was talking about the SQLCLR which seems to be my main topic in the last months. That is because I use it over and over again in my projects. The idea was to share best practices to the people who probably start practicing this feature.  Many thanks to the organizers for invitation, congratulations once again - it was really impresive event. Hope to see you next year as I promised you to show you some TSQL stuff that makes SQL Server fly (not cry).

    If you want to see what I was talking about you can download the materials from the drive  . Many thanks to Adam Machanik for help in preparing the demos and sharing knowledge about the SQLCLR 



  • SQL Day Lite 2014 Lublin - part II - photos

    Hello again

    I am pleased to announce that pictures taken during the conference are available. Just take a look here. Thanks to Kamil Nowiński the photos.

    It is also a good moment to say "thanks" to the PLSSUG (Polish SQL Server Users Group) for the organization. Again - it was fabulous :) Over 100 people were attending the Saturday's conference and also over 45 were with us on Friday during the workshops (preconf). Please visit the PLSSUG site for the recorded sessions (comming soon...)

    The agenda of the conference was as follow:

    Łukasz Grala - SQL Server Analysis Services - Multidimensional vs Tabular

    me - All you wanted to know about the DBCC 

    Grzegorz Stolecki - Reporting Services - the rendering report extensions

    Łukasz Grala - What everybody (not only DBA's) should know about the backup

    Marek Adamczuk & Paweł Potasiński - Windowing Functions

    Maciej Pilecki - Event Notification 




  • SQLDAY Lite 2014 - Lublin.

    I had the priviledge to participate in the SQLDayLite 2014 conference that was held in Lublin this week. 
    I had the opportunity to prepare one-day workshop (on Friday) and also gave a speech on Saturday.
    The workshop was not easy :) as it was dedicated to the SQLCLR programming.  So we (me & participants) did a really deep dive into the SQLCLR :). So it was really demo intensive day for all of us :)
    On Saturday I was talking about the DBCC and its usage. I hope people enjoyed that talk especially that  I did show how to make a corruption in the db (for training purposes)

  • My MVA course about SQL Server 2014 & Azure

    If you are tired today maybe you could take your time and spend the whole weekend with the SQL Server 2014 and Azure?

    The new MVA course is ready :) and waiting for you. Just visit the link and enjoy. Watch out - it's in Polish :)

    The topics covered:

    • backup encryption
    • manual backup to Azure storage
    • smart backup :)
    • buffer pool extension
    • SQL Server files integration with Azure storage

    more comming....

  • SQL Server 2014 CU4 is available for downloading

    The SQL 2014 CU4 is available :)

    There are some interesting facts:

    ·         66 issues were solved

    BI\Analysis Services


    BI\Collaborative Insights


    BI\Reporting Services


    Data Quality Services\Cleanse\Export Results


    Integration Services


    Integration Services\Tasks Components


    Integration Services\Tools


    Master Data Services\Client


    Master Data Services\Server


    Master Data Services\Setup


    SQL Connectivity


    SQL Engine


    SQL Engine\Column Stores


    SQL Engine\DB Management


    SQL Engine\Extreme OLTP


    SQL Engine\High Availability and Disaster Recovery


    SQL Engine\Management Services


    SQL Engine\Programmability


    SQL Engine\Query Execution


    SQL Engine\Query Optimizer


    SQL Engine\Replication


    SQL Engine\Search


    SQL Engine\Service Broker


    SQL Engine\Spatial


    SQL Engine\SQLOS


    SQL Engine\Unstructured Data


    SQL Server Client Tools


    SQL Server Client Tools\Database Performance Tools


    SQL Server Client Tools\Powershell


    SQL Server Client Tools\SSMS



    ·         The release build is 12.0.2430.0 

    You can find more details on the official page:






  • Transactional replication - how to initialize a subscription from a backup

    Today I had a chance to initialize my scubscription in transactional replication from backup. The database is quite big (120GB) and I thought it I would be better to use this method to start the replication process.

    Unfortunatelly this is not possible from the replication wizard and you have to change the publication and subscription definition manually.

    Here are the steps - if you work with new replication:

    1) Create a publication script and update manually the  sp_addpublication command by changing the @allow_initialize_from_backup parameter  to "true". In my case it was set to "automatic" as I used the wizard to generate the script. I think that it is usefull to set the other parameter: @immediate_sync to "true", too. 

    2) Run the script on the publisher server and then create a backup of the database 

    3) Restore the database on the subscriber.

    4) Go back to the publisher and create a subscription  script but dont run it. Look into it and add these parameters to the sp_addsubscription procedure:

    @sync_type = "initialize with backup" 

    @backupdevicetype = "disk"  (you could add "tape" or "logical" apart from "disk")

    @backupdevicename = "path to the backup"

    when you yse the logical backup device then put the name od this device.


    I know that most of you know that a replication can be started using that technique. I worte this blog post in order to not forget about that "feature" in  the future







  • Swedish SQL Server User Group - my sessions in September

    I had a great privilege to have two sessions in Sweden in September. The first was done in Malmo and the second I gave in Stockholm. 
    The talks were about waits statistics in the SQL Server ("All you should know about waits"). I realized that during 90 minutes I am not able to tell just *ALL* about the wait statistics so I slightly modified the topic to "All IMPORTANT you should know..."
    If you are interested in the presentation deck you can find it here
    Thanks to Swedish MVP's and especially to Johan Ahlen,  Steinar Andersen and Lars Utterstrom for inviting me to Sweden
  • Trace flags - TF 7806

    The trace flag 7806 is necessary when a DBA would like to use the dedicated administrator connection (DAC) in the SQL Server Express. The DAC is turned off when you install the SQL Server but it is a good practice to turn it on after the installation is done. The DBA will really need the have access to the unresponsive database server and having DAC active he/she has more chance to do the work. However this feature is not supported in the SQL Server Express edition by default. 

    Microsoft has added a trace flag 7806 to enable this feature in the SQL Server Express.

    As the flag is a global flag we have to turn it on the service level. You should add a parameter –T7806 to the parameter list and restart the service.

    After you did that you are now able to use the DAC in the SQL Server Express. For example you could try to connect using sqlcmd tool:

    Sqlcmd –S localhost\sqlexpress –E –A

    I made an assumption that on your local server there is an instance of the SQL Server Express which is called "sqlexpress" and you  connect to this instance using your Windows credentials

    When you are connected try to run that query:

    SELECT S.session_id FROM sys.tcp_endpoints as E JOIN sys.dm_exec_connections as S

    On E.endpoint_id = S.endpoint_id

    WHERE = ‘Dedicated Admin Connection’


    Last remarks – the DAC can be used only by sysadmins

  • Trace flags - TF 1117

    I had a session about trace flags this year on the SQL Day 2014 conference that was held in Wrocław at the end of April. The session topic is important to most of DBA's and the reason I did it was that I sometimes forget about various trace flags :). So I decided to prepare a presentation but I think it is a good idea to write posts about trace flags, too.

    Let's start then - today I will describe the TF 1117. I assume that we all know how to setup a TF using starting parameters or registry or in the session or on the query level. I will always write if a trace flag is local or global to make sure we know how to use it.

    Why do we need this trace flag? Let’s create a test database first. This is quite ordinary database as it has two data files (4 MB each) and a log file that has 1MB. The data files are able to expand by 1 MB and the log file grows by 10%:

    USE [master]




    ( NAME = N'TF1117',

         FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\TF1117.mdf' ,

         SIZE = 4096KB ,


         FILEGROWTH = 1024KB


    ( NAME = N'TF1117_1',

         FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\TF1117_1.ndf' ,

         SIZE = 4096KB ,


         FILEGROWTH = 1024KB


     LOG ON

    ( NAME = N'TF1117_log',

         FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\TF1117_log.ldf' ,

         SIZE = 1024KB ,

         MAXSIZE = 2048GB ,

         FILEGROWTH = 10%



    Without the TF 1117 turned on the data files don’t grow all up at once. When a first file is full the SQL Server expands it but the other file is not expanded until is full. Why is that so important? The SQL Server proportional fill algorithm will direct new extent allocations to the file with the most available space so new extents will be written to the file that was just expanded. When the TF 1117 is enabled it will cause all files to auto grow by their specified increment. That means all files will have the same percent of free space so we still have the benefit of evenly distributed IO. The TF 1117 is global flag so it affects all databases on the instance. Of course if a filegroup contains only one file the TF does not have any effect on it.

    Now let’s do a simple test. First let’s create a table in which every row will fit to a single page: The table definition is pretty simple as it has two integer columns and one character column of fixed size 8000 bytes:

    create table TF1117Tab


         col1 int,

         col2 int,

         col3 char (8000)



    Now I load some data to the table to make sure that one of the data file must grow:

    declare @i int

    select @i = 1

    while (@i < 800)


          insert into TF1117Tab  values (@i, @i+1000, 'hello')

           select @i= @i + 1


    I can check the actual file size in the sys.database_files DMV:

    SELECT name, (size*8)/1024 'Size in MB'

    FROM sys.database_files 



    As you can see only the first data file was  expanded and the other has still the initial size:


    name                  Size in MB

    --------------------- -----------

    TF1117                5

    TF1117_log            1

    TF1117_1              4

    There is also other methods of looking at the events of file autogrows. One possibility is to create an Extended Events session and the other is to look into the default trace file:



    DECLARE @path NVARCHAR(260);


             CHARINDEX('\', REVERSE([path])), 260)) + N'log.trc'

    FROM    sys.traces

    WHERE   is_default = 1;

    SELECT    DatabaseName,  






                  FileType =

                            CASE EventClass       

                                 WHEN 92 THEN 'Data'      

                                 WHEN 93 THEN 'Log'  


    FROM sys.fn_trace_gettable(@path, DEFAULT)

    WHERE   EventClass IN (92,93)

    AND StartTime >'2014-07-12' AND DatabaseName = N'TF1117'

    ORDER BY   StartTime DESC;


    After running the query I can see the file was expanded and how long did the process take which might be useful from the performance perspective.



    Now it’s time to turn on the flag 1117.

    DBCC TRACEON(1117)


    I dropped the database and recreated it once again. Then I ran the queries and observed the results. After loading the records I see that both files were evenly expanded:

    name                  Size in MB

    --------------------- -----------

    TF1117                5

    TF1117_log            1

    TF1117_1              5

    I found also information in the default trace. The query returned three rows. The last one is connected to my first experiment when the TF was turned off.  The two rows shows that first file was expanded by 1MB and right after that operation the second file was expanded, too. This is what is this TF all about J


  • Memories about Tadeusz Golonka

    Today at 10:55 AM, Tadeusz Golonka - my greatest  Mentor and Teacher  passed away. I had te opportunity to met Tadek in person several times last years. It was always a great experience to see how he shared his energy and passion. I was always impressed and had a lot of new ideas after such meeting or lecture.

    I can remember the meeting  in early 2009 and his briliant speech he did for us, the MVP community in Poland. We spent two days together and he talked to us all the time. He gave us examples how to share IT passion to other people and how to be better person for others. He was the greates Mentor I have ever met - I realized this during that meeting. My greates dream was and still is to be "like Tadek". Many Times I just went to events to see / hear him on stage ("in action"). I always wanted to have his energy, empathy and passion. Now I have to live without his good words and advices....

    Let me put here the words that Adam Cogan wrote on Tadek's profile on Facebook. I just can't write about that fatal accident. 

    "The circumstances of Tadeusz Golonka death are too tragic. Tad stood up to offer his seat to an elderly lady, he lost his balance and then he slipped and hit the tram door hard. He then fell out of the tram and hit the metal barriers that separate the tram rails from the street. It was a severe accident....

    .. So horrible.  At first it was a miracle is that he survived... he fought for several days.  My thoughts are with his lovely family. The family have asked for blood donations as a symbolic gift. Tad received a lot of blood.  Thank you Tad, you were a wonderful person. I will remember you as a kind man, a gentleman. "

    RIP Tadeusz- You will never ever be forgotten. You are with us all the time



More Posts Next page »
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement