Archive for July, 2013

Error Description: Length specified in network packet payload did not match number of bytes read; the connection has been closed. Please contact the vendor of the client library. [CLIENT: xxx.xxx.xxx.xxx]

Todays, on one of the SQL Servers from the clients that I work with closely, we have received the Error: 17836, Severity: 20, State: 1. Error Description: Length specified in network packet payload did not match number of bytes read; the connection has been closed. Please contact the vendor of the client library. [CLIENT: xxx.xxx.xxx.xxx]

Error 17836 Severity 29 State 1

Usually, the first step I would take is to do a nslookup of the ip address listed in the end of the error message [CLIENT: xxx.xxx.xxx.xxx] to find out what computer it is connecting from.  Due to my busy schedule today and based on my past experience with this error, I had sent an email to my network team friends (yes, make sure you make friends with the network team) and asked them very nicely if they were doing any port scanning test. I got a reply faster you can say Bingo! My network team friends had acknowledged that they were running port scanning on the servers. The most likely cause is that the port scanning software is sending data packets to SQL Server and SQL Server does not know what to do with it and then it closes the connection.

If the network team is not running any port scanning or testing, it is within your best interest to find out quickly to see who or what is hitting your server on the specified port for any suspicious activities.

Enabling Network Level Authentication on XP Machine for Terminal Access

Great news to my Windows XP friends, if you are trying to connect to Windows Server 2008 or Windows Server 2008 R2 and you have been receiving the annoying message:

“The remote computer requires Network Level Authentication, which your computer does not support”

RDP Disconnected

There’s help and hope and here’s the solution from Aaron Thirling at http://mysupportportal.net/Cultrix/Knowledgebase/Article/View/37/0/enabling-network-level-authentication-on-xp-machine-for-terminal-access:

Configure Network Level Authentication

To enable NLA in XP machines; first install XP SP3, then edit the registry settings on the XP client machine to allow NLA

  1. Click Start, click Run, type regedit, and then press ENTER.
  2. In the navigation pane, locate and then click the following registry subkey: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa
  3. In the details pane, right-click Security Packages, and then click Modify.
  4. In the Value data box, type tspkg. Leave any data that is specific to other SSPs, and then click OK.
  5. In the navigation pane, locate and then click the following registry subkey: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders
  6. In the details pane, right-click SecurityProviders, and then click Modify.
  7. In the Value data box, type credssp.dll. Leave any data that is specific to other SSPs, and then click OK.
  8. Exit Registry Editor.
  9. Restart the computer.

I hope this will help save you hours of frustration like I did trying all other solutions posted online.

Should you update statistics after index rebuild?

Pop Quiz Hot Shot: Should you update statistics after index rebuild?

The short answer is NO! This is because index rebuild will update the statistics with a full scan. If you update the statistics manually after the index rebuild, you will be updating it using the default, which is a sampled scan. This is a common mistake!

When not explained correctly, this concept can even throw the very seasoned or senior DBAs into loops of confusions.  Here, I will redirect you to one of the most trusted sources and authorities for SQL Server, Paul Randal. In his posting “Rebuilding Indexes and Updating Statistics,” Paul writes:

The problem I’ve been seeing is people rebuilding indexes and then updating statistics. So why is this a problem? Well, it depends :-)

  • If your default for updating statistics is to do a sampled scan, then having rebuild update the statistics with a full scan (as a side effect), and then proceeding to manually update them again with the default sampled scan, means that after both operations you’re left with sampled statistics. You’ve wasted resources doing the sampled scan AND you’ve lost the ‘free’ full-scan statistics that the index rebuild did for you.
  • If your default is to do a full scan, then you don’t lose out on having the best statistics, but you do waste resources by unnecessarily updating statistics a second time.

So what’s the solution?

The simple answer is not to update statistics on indexes that have just been rebuilt.

The more complicated answer is to:

  1. Have a list of indexes (F) that you know will cause workload performance problems if they get fragmented
  2. Have a list of indexes (S) that you know will cause workload performance problems if the statistics don’t get regularly updated
  3. Evaluate fragmentation for list F, and choose to reorganize, rebuild, or do nothing
  4. For all indexes in list S that were not rebuilt in step 3, update statistics

What can you do? Well, you can start writing your customized script for index and update statistics maintenance or you can take a look at Ola Hallengren’s “SQL Server Index and Statistics Maintenance” script. I would opt for the latter. Why reinvent the wheels or sacrifice the precious time you could spent on other also very interesting topics of SQL Server such as performance tuning or deep dive research into SQL Server Replication?

REFERENCES:

Rebuilding Indexes and Updating Statistics
http://www.sqlskills.com/blogs/paul/search-engine-qa-10-rebuilding-indexes-and-updating-statistics/

SQL Server Index and Statistics Maintenance
http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

The Amazing SQL Server Maintenance Solution Script

Similar to all SQL Server DBAs, over the past years, I have written my customized versions of the SQL Server maintenance jobs: Backup (full, differential, tlog), index maintenance, and update statistics. I have even posted and updated them in my blogs in the recent months. After close examinations and reviews of Ola Hallengren’s SQL Server Maintenance Solution over the past month, I will be using Ola’s “MaintenanceSolution” script in new server builds for setting SQL Server maintenance jobs whenever applicable or possible. I will be borrowing some ideas from it when needed.  It is amazing and it is free.

Ola’s SQL Server Maintenance Solution script are not new to the SQL Server community, many of us who follow SQL Server postings on the popular SQL Server websites or magazines have heard of or even read about the positive reviews from many industry experts.  Note that you can’t just blindly run the script and then call it a day. Similar to going to a fancy and expensive restaurant, the chefs can cook the best dishes for you on their good days. But, you still need to pick up the fork, spoon, or diner knife to enjoy the meal yourself. The following are the steps that I had tested in my development server (needless to say that’s a fancy term for my laptop):

  1. Download the script from http://ola.hallengren.com/ or here.
  2. Create a DBA utility database to store the stored procs and objects to be created by the script. I have called it DBAUtility in my development server.
  3. Open the MaintenanceSolution.sql script and modify the values for the @BackupDirectory , @OutputFileDirectory , and the @LogToTable arugments arguments.
    Arguments to Modify
  4. Execute the script again the DBAUtility database.
  5. Go to each of the SQL Server Agent jobs that were created and modify the values for the required arugments. Instead of SQLCMD, you can modify the jobs to run TSQL scripts. You can go to http://ola.hallengren.com/ for the references on the values for the arguments.
    SQL Server Maintenance Jobs
  6. Open the “Job Activity Monitor” window and then review each of the jobs that were just added and make sure their commands are correct and then add the schedules accordingly.

REFERENCES:

SQL Server Maintenance Solution
http://ola.hallengren.com/

How to find the recent restore information for a database or databases?

Have you ever wondered when was the last time a specific database or databases were restored and by whom?  This critical piece of information will help you to follow up and understand the reason behind the restore(s).

The following script was originally posted by Thomas LaRock on www.mssqltips.com. http://www.mssqltips.com/sqlservertip/1724/when-was-the-last-time-your-sql-server-database-was-restored/ I have modified it to add the “Restore Option” and “Recovery Option” columns.  Be aware of the @dbname and the @days variables. @dbname is set to NULL as the default and will query all databases that had restores for the days specified in the @days variable. You can specify the database name to be queried and also the number of days to query by simply keying the values in these two corresponding variables.

DECLARE @dbname sysname, @days int
SET @dbname = NULL --substitute for whatever database name you want
SET @days = -30 --previous number of days, script will default to 30
SELECT
 rsh.destination_database_name AS [Database],
 rsh.user_name AS [Restored By],
 CASE WHEN rsh.restore_type = 'D' THEN 'Database'
  WHEN rsh.restore_type = 'F' THEN 'File'
  WHEN rsh.restore_type = 'G' THEN 'Filegroup'
  WHEN rsh.restore_type = 'I' THEN 'Differential'
  WHEN rsh.restore_type = 'L' THEN 'Log'
  WHEN rsh.restore_type = 'V' THEN 'Verifyonly'
  WHEN rsh.restore_type = 'R' THEN 'Revert'
  ELSE rsh.restore_type
 END AS [Restore Type],
 CASE WHEN rsh.replace = 1 THEN 'With Replace'
	WHEN rsh.replace = 0 THEN 'Not Specified'
 END AS [Restore Option],
 CASE WHEN rsh.recovery = 1 THEN 'With Recovery'
	WHEN rsh.recovery = 0 THEN 'With Norecovery'
	WHEN rsh.recovery is null THEN 'With Recovery'
 END AS [Recovery Option],
 rsh.restore_date AS [Restore Started],
 bmf.physical_device_name AS [Restored From],
 rf.destination_phys_name AS [Restored To]
FROM msdb.dbo.restorehistory rsh
 INNER JOIN msdb.dbo.backupset bs ON rsh.backup_set_id = bs.backup_set_id
 INNER JOIN msdb.dbo.restorefile rf ON rsh.restore_history_id = rf.restore_history_id
 INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id
WHERE rsh.restore_date >= DATEADD(dd, ISNULL(@days, -30), GETDATE()) --want to search for previous days
AND destination_database_name = ISNULL(@dbname, destination_database_name) --if no dbname, then return all
ORDER BY rsh.restore_history_id DESC
GO

Here’s an example what the output looks like:
Database Restores History

REFERENCES:

When was the last time your SQL Server database was restored?
http://www.mssqltips.com/sqlservertip/1724/when-was-the-last-time-your-sql-server-database-was-restored/

msdb.dbo.restorehistory
http://msdn.microsoft.com/en-us/library/ms187408(v=sql.90).aspx

msdb.dbo.backupset
http://technet.microsoft.com/en-us/library/ms186299(v=sql.90).aspx

msdb.dbo.backupmediafamily
http://technet.microsoft.com/en-us/library/ms190284(v=sql.90).aspx