[PL] Sezon spotkań grupowych oficjalnie otwarty

Z początkiem trzeciej dekady lutego mogę śmiało powiedzieć, że sezon spotkań grup społecznościowych mogę uznać za otwarty. W ubiegłym roku (zwłaszca w drugiej połowie) tych spotkań było mało, a wrocławski oddział Polish SQL Server User Group przeszedł w wymuszoną drzemkę wegetacyjną. Nie ukrywam, że oceniam ten stan jako moją porażkę roku ubiegłego w tej materii (a’propos podsumowań), ale życie zawodowe oraz prywatne spowodowało, że nie starczyło nie tylko czasu ale przede wszystkim motywacji do organizowania spotkań.

W styczniu wróciłem do spotkań z PLSSUG Wrocław, a odzew grupy był znaczący -ponad trzydzieści pięć osób uczestniczyło w 24 Spotkaniu PLSSUG Wrocław. Oprócz opowiedzenia tego ci się dzieje w samej grupie, miałem okazję opowiedzenia w dość niekonwencjonalny sposób o nowościach w SQL Server 2012 (sesja typu “no slide”). Nikt nie rzucił pomidorem (może bali się pobrudzić ściany w ośrodku…), więc nie było chyba tak najgorzej.

W lutym pojawiły się dwa wystąpienia:

  • pierwsze na 72 Spotkaniu KGD.NET w Krakowie poprowadziłem sesję na temat “SQL Server 2012 Backup & Restore – praktyki lepsze i gorsze” koncentrując się bardziej na dyskusji i wymianie poglądów oraz doświadczeń między uczestnikami (ponad pięcdziesiąt osób), niż tylko na poprowadzeniu prezentacji. Dyskusja wywiązała się ciekawa, kilkanaście osób miało doświadczenia z backupem, kilka z restore, kilka z precedurami.
  • drugie na 25 Spotkaniu PLSSUG Wrocław, gdzie wystąpiłem z tą samą sesją, ale dla zupełnie, zupełnie innej publiczności. Już na początku dyskusji okazało się ze backup robią wszyscy {jedna osoba nawet bardziej korporacyjne: FULL (raz dziennie) >> DIFF (co kilka godzin) >> LOG (co kilka chwil)} a i z restorem kilka osób ma doświadczenia.

Obie sesje z założenia miały dotyczyć narzędzia firmy RedGate: SQL Backup Pro, niestety na skutek awarii dysku i braku czasu na odbudowę środowiska musiałem ominąć część z pokazami demo. Obiecałem jednak jednej i drugiej grupie, że demo i tak poprowadzę, więc w marcu ta sesja powinna się pojawić jako powtórzona na portalu VirtualStudy. Mam nadzieję, że wszyscy będą wówczas usatysfakcjonowani.

Ciekawym punktem była sesja o RavenDB. Szymon Pobiega, główny organizator spotkania zapewne  tak sobie umyślił, że się dwa światy zderzą i że się nawet pobijemy ze sobą o tego Not Only SQL, ale nic z tego. Sesja ciekawa, choć kontrowersyjna – ten brak relacji trochę mnie odrzuca. Ale warto się przyjrzeć tematowi – co szczególnie polecam hobbystom SQLowym.

Sam nie wiedziałem co ze sobą zrobić w tym temacie – jednak motywacja to waży aspekt, ale teraz mogę śmiało powiedzieć: JEST MOC, ze spotkań grupy JEST MOC!!! Dzięuję Szymonowi, Michałowi, Jarkowi, Marcie  oraz wszystkim przyjaciołom i sympatykom z Krakowa za zaproszenie i towarzystwo. Dziękuję także Michałowi, Tomaszowi, Bogdanowi oraz całej grupie wrocławskiej, która wciąż motywuje do działania!

Następne spotkania już bardziej regularnie – marzec, kwiecień, maj…

[EN] Sunday Market – SQL Server Maintenance Solution by Ola Hallengren

On last SQLBits 9 – Query Across Mersey (September 2011, Liverpool, UK) I had great opportunity and pleasure to be a host as my SQLBits Helper role for not very famous, but full professional specialist from Denmark (work place)/Sweden (living place) – Ola Hallengreen at his session. Just back for the past for a moment – here is the abstract of his session: How do you do database maintenance in an enterprise environment? I have developed a stored procedure based solution that has become extremely popular in the SQL Server community. In this session I will go through how the solution works, how it can be used in different scenarios for backup, integrity check, index and statistics maintenance and talk some about the background for the design. You can also bring your questions about the solution or about database maintenance in general. And links for video recording is here: Slide Deck 75 KB + Demo Code 3 KB + MP4 Video Low 64 MB + MP4 Video Med 87 MB + MP4 Video High 205 MB +  MP4 Video HD 382 MB.

In this post I want to write some words about his great solution. Great because is powerful tool, and great because is free (like all tools in Sunday Market series). Based on Ola’s web site: The SQL Server Maintenance Solution comprises scripts for running backups, integrity checks, and index and statistics maintenance on all editions of Microsoft SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 and SQL Server 2012. The Solution is based on stored procedures, functions, the sqlcmd utility, and SQL Server Agent jobs.

In one Maintenance Solution script package you can find a lot of scripts in three very important components, where all l are:
Supported versions: SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 and SQL Server 2012
Depends on: CommandExecute, DatabaseSelect and CommandLog

SQL Server Backup
Procedure name: DatabaseBackup
Purpose: Back up databases
Example below:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[DatabaseBackup]

 

@Databases nvarchar(max),

@Directory nvarchar(max) = NULL,

@BackupType nvarchar(max),

@Verify nvarchar(max) = ‘N’,

@CleanupTime int = NULL,

@Compress nvarchar(max) = NULL,

@CopyOnly nvarchar(max) = ‘N’,

@ChangeBackupType nvarchar(max) = ‘N’,

@BackupSoftware nvarchar(max) = NULL,

@CheckSum nvarchar(max) = ‘N’,

@BlockSize int = NULL,

@BufferCount int = NULL,

@MaxTransferSize int = NULL,

@NumberOfFiles int = 1,

@CompressionLevel int = NULL,

@Description nvarchar(max) = NULL,

@Threads int = NULL,

@Throttle int = NULL,

@Encrypt nvarchar(max) = ‘N’,

@EncryptionType nvarchar(max) = NULL,

@EncryptionKey nvarchar(max) = NULL,

@LogToTable nvarchar(max) = ‘N’,

@Execute nvarchar(max) = ‘Y’

 

AS

#####  #####  #####

SQL Server Integrity Check
Procedure name: DatabaseIntegrityCheck
Purpose: Check the integrity of databases
Example below:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[DatabaseIntegrityCheck]

 

@Databases nvarchar(max),

@PhysicalOnly nvarchar(max) = ‘N’,

@NoIndex nvarchar(max) = ‘N’,

@ExtendedLogicalChecks nvarchar(max) = ‘N’,

@TabLock nvarchar(max) = ‘N’,

@LogToTable nvarchar(max) = ‘N’,

@Execute nvarchar(max) = ‘Y’

 

AS

##### ##### #####

SQL Server Index and Statistics Maintenance
Procedure name: IndexOptimize
Purpose: Rebuild and reorganize indexes and update statistics
Example below:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[IndexOptimize]

 

@Databases nvarchar(max),

@FragmentationLow nvarchar(max) = NULL,

@FragmentationMedium nvarchar(max) = ‘INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,

@FragmentationHigh nvarchar(max) = ‘INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,

@FragmentationLevel1 int = 5,

@FragmentationLevel2 int = 30,

@PageCountLevel int = 1000,

@SortInTempdb nvarchar(max) = ‘N’,

@MaxDOP int = NULL,

@FillFactor int = NULL,

@PadIndex nvarchar(max) = NULL,

@LOBCompaction nvarchar(max) = ‘Y’,

@UpdateStatistics nvarchar(max) = NULL,

@OnlyModifiedStatistics nvarchar(max) = ‘N’,

@StatisticsSample int = NULL,

@StatisticsResample nvarchar(max) = ‘N’,

@PartitionLevel nvarchar(max) = ‘N’,

@TimeLimit int = NULL,

@Indexes nvarchar(max) = NULL,

@Delay int = NULL,

@LogToTable nvarchar(max) = ‘N’,

@Execute nvarchar(max) = ‘Y’

 

AS

##### ##### #####

 

From Ola’s WEBSITE You can download separated scripts like:

CommandLog.sql / DatabaseSelect.sql / CommandExecute.sql / IndexOptimize.sql / DatabaseIntegrityCheck.sql / DatabaseBackup.sql 

or get all in one file

 MaintenanceSolution.sql   

 

Enjoy!

[EN] SQL Server 2008 R2 Service Pack 1 Cumulative Update 4 is available

LOGO__SQLServer2008R2Just few days before last X-Mass Microsoft released cumulative update for SQL Server 2008 R2 SP1. It’a a huge update with fixing a lot of bugs for this SQL Server version.

In this Cumulative Update 4 [version build: 10.50.2796.0] Microsoft resoleved & fixed a lot of bugs for many components of SQL Server.

Below You can find short information about fixed bugs:

VSTS BUG: 834628 | 955850
FIX: Error message when you try to use the binaries from the SQL Server System CLR Types package (SQLSysClrTypes.msi): “Unable to load DLL ‘SqlServerSpatial.dll’: This application has failed to start because the application configuration is incorrect”

VSTS BUG: 765507 | 970198
FIX: You receive an incorrect result when you run a query that uses the row_number function in SQL Server 2008 or in SQL Server 2008 R2

VSTS BUG: 843693 | 2159286
FIX: The Remote Registry service crashes when an application tries to retrieve performance counter data from an instance of SQL Server on another computer

VSTS BUG: 826730 | 2445812
FIX: A column that uses the DATETIME data type does not replicate to an Oracle subscriber by using a transactional replication in SQL Server 2008 or in SQL Server 2008 R2

VSTS BUG: 783498 | 2510788
FIX: Success message is incorrectly logged when a distributed transaction of an SSIS 2005 package, of an SSIS 2008 package or of an SSIS 2008 R2 package is aborted

VSTS BUG: 799308 | 2515286
FIX: “There is no function ‘{urn:schemas-microsoft-com:xml-sql}:variable()” error for an XQuery query that updates the value of an XML column in SQL Server 2005, in SQL Server 2008 or in SQL Server 2008 R2

VSTS BUG: 818397 | 2550375
FIX: Deadlock might occur in sys.dm_database_encryption_keys DMV in SQL Server 2008 R2 when you run log shipping from many databases to one secondary server

VSTS BUG: 738050 | 2562753
FIX: Slow performance when an MDX query uses unary operators on parent/child hierarchy members in SSAS 2008 R2 if you define a scope calculation on these members and if the query accesses multiple dates in a time dimension

VSTS BUG: 818842 | 2567366
FIX: Slow performance when you restore a database in SQL Server 2008 R2 if CDC is enabled

VSTS BUG: 818849 | 2580224
FIX: Incorrect results when you commit the Unpivot transformation to a SSIS package in SSIS 2008 or in SSIS 2008 R2

VSTS BUG: 799296 | 2583994
FIX: SSIS Checkpoint restores an incorrect variable value for an Execute SQL Task item of a For Loop Container item in SSIS 2008 or in SSIS 2008 R2 if an SSIS package executes again after it fails or stops

VSTS BUG: 818843 | 2588506
FIX: SQLGetDiagRec function does not return errors in SQL Server 2008 or in SQL Server 2008 R2 if more than 2100 query parameters are sent

VSTS BUG: 799325 | 2588970
FIX: “Server: Msg 7359” error when you run a distributed query in SQL Server 2008 or in SQL Server 2008 R2 if a schema version is updated more than five times

VSTS BUG: 799293 | 2591461
FIX: “Unexpected error from SE OLEDB interface” error when you run a full-text search query in SQL Server 2008 or in SQL Server 2008 R2

VSTS BUG: 818848 | 2597229
FIX: Report Builder 3.0 stops responding if SSRS 2008 R2 uses forms authentication

VSTS BUG: 799305 | 2598903
FIX: SQL Server Agent job randomly stops when you schedule the job to run past midnight on specific days in SQL Server 2005, in SQL Server 2008 or in SQL Server 2008 R2

VSTS BUG: 829282 | 2603910
FIX: Backup fails in SQL Server 2008 or in SQL Server 2008 R2 if you enable change tracking on the database

VSTS BUG: 799312 | 2606301
FIX: High memory usage when you run Replication Snapshot Agent in SQL Server 2008 or in SQL Server 2008 R2

VSTS BUG: 791492 | 2606918
FIX: NULL value in the Retention column in sysmergepublications table and effects on the metadata cleanup process when you set up a merge replication environment in a republishing topology in SQL Server 2008 R2

VSTS BUG: 818853 | 2616117
FIX: Incorrect mail profile is displayed in the Alert System page in SQL Server 2008 R2

VSTS BUG: 798464 | 2616718
FIX: Poor performance when you synchronize the data from a SQL Server 2008 R2 publisher to a SQL Server compact 3.5 subscriber

VSTS BUG: 834621 | 2618676
FIX: Transaction log backup is created even though the full backup is canceled in SQL Server 2008 or in SQL Server 2008 R2

VSTS BUG: 834618 | 2618680
FIX: “Not registered correctly” error when you use a pull subscription and the Replication Interactive Conflict Resolver

VSTS BUG: 805159 | 2620762
FIX: Acknowledgement messages are not sent by a SQL Server 2008 R2 Service Broker target

VSTS BUG: 807413 | 2622668
FIX: Error message when you delete some records of a table at the publisher if the schema name of the table is not [dbo]

VSTS BUG: 818851, 818852 | 2622823
FIX: ghost_record_count values keep increasing in SQL Server 2008 R2

VSTS BUG: 809033 | 2624272
FIX: Error 9013 when you try to start the SQL Server services after you apply SQL Server 2008 R2 SP1 for SQL Server 2008 R2

VSTS BUG: 818844 | 2624527
FIX: Clustered index is not used in the execution plan in SQL Server 2008 R2 if a query uses two or more partitions

VSTS BUG: 812723 | 2626637
FIX: DRILLTHROUGH statement does not work if it is run against some attribute members whose values contain one or more double quotation marks in SSAS 2008 R2

VSTS BUG: 834637 | 2628126
FIX: “Fulltext index error during compression or decompression” error when you create a full-text index in SQL Server 2008 or in SQL Server 2008 R2 if the primary key is set to a BIGINT column

VSTS BUG: 815917 | 2628712
FIX: An infinite loop occurs in a hierarchy that contains a circular relationship in SQL Server 2008 R2

VSTS BUG: 804578 | 2629168
FIX: Internal error when you update a cellset that is returned after you run a query against a perspective in SSAS 2008 R2

VSTS BUG: 817160 | 2629456
FIX: Access violation when you insert a record into a new empty partition in SQL Server 2008 or in SQL Server 2008 R2

VSTS BUG: 816667 | 2632021
FIX: A SSRS 2008 R2 report that has toggle items is not paginated

VSTS BUG: 821110 | 2633271
FIX: SQL Server services cannot be stopped on a computer that has SQL Server 2008 R2 Express Edition installed

VSTS BUG: 822390 | 2634571
FIX: Improvements for the DBCC CHECKDB command may result in faster performance when you use the PHYSICAL_ONLY option

VSTS BUG: 834649 | 2635827
FIX: Access violation when you run a DBCC CHECKDB command against a database that contains a table that has a spatial index in SQL Server 2008 or in SQL Server 2008 R2

VSTS BUG: 827315 | 2638216
FIX: Error message when you run a query that uses an MDX reserved keyword as a parameter name in SSAS 2008 R2

VSTS BUG: 834640 | 2638448
FIX: Access violation when a query is compiled or recompiled in SQL Server 2008 or in SQL Server 2008 R2

VSTS BUG: 828867 | 2639301
FIX: Error message when you review a SSRS 2008 R2 report that has a tablix data region if the region contains some chart or gauge or map items

VSTS BUG: 831931 | 2641923
FIX: Incorrect custom color distribution when you use numeric values for the Start Value and the End Value of custom color distribution buckets in SSRS 2008 R2

VSTS BUG: 832040 | 2641971
FIX: “Error: Data conversion failed” error when you use multiple characters as the column delimiter in Flat File Connection Manager in SSIS 2008 R2

VSTS BUG: 834705 | 2643340
FIX: Action property does not work when you view an SSRS 2008 R2 report in Report Manager

VSTS BUG: 836483 | 2644396
FIX: Merge replication changes are missing on a SQL Server Compact subscriber when they subscribe to a partition in SQL Server 2008 R2

VSTS BUG: 835438 | 2645648
FIX: Export procedure fails when you export a 64-bit SSRS 2008 R2 report that contains multiple levels of document map to the PDF format

VSTS BUG: 822171 | 2646224
FIX: Query plan is displayed as an XML document incorrectly when you set the SHOWPLAN_XML option to ON in SQL Server 2008 R2 SP1

VSTS BUG: 842206 | 2647120
FIX: Incorrect page numbers are displayed when you export a SSRS 2008 R2 report into PDF format

VSTS BUG: 844587 | 2648158
FIX: “The process could not execute” error message when you set a nonzero value in the MaxCmdsInTran parameter of the Log Reader Agent in SQL Server 2008 R2

VSTS BUG: 845530 | 2649359
FIX: An access violation occurs in the “sqlservr!CSbMsgDispatcher::CSbReceivedDialog::AppendMsg” class in SQL Server 2008 R2

VSTS BUG: 836497 | 2651629
FIX: Error message when you use DTA to tune one or more queries against a database that contains many objects in SQL Server 2008 R2

VSTS BUG: 834224 | 2652582
FIX: “Invalid authorization specification” error message when you run the SQL Server 2008 R2 Analysis Services Deployment Wizard by using the SQL Server Authentication mode

VSTS BUG: 837758 | 2653893
FIX: It takes a long time to restore a database in SQL Server 2008 R2

Microsoft has recommended to test all hotfixes before updating in production environment. More information about this release You can find with the article: Cumulative Update 4

If You want to go directly to download section jump to Request Hotfix Download Site, where You can choose version (for 32-bit, 64-bit, itanium). This release is prepared as multilingual package.