SQL Server: Advanced SUSPECT Database Recovery
I hope I never have to use this article, but if I do, it will be a lifesaver. Check out Paul Randal's post on recovering a SUSPECT database that's been detached. He shows not only how to corrupt a database whilst keeping it functional, he also shows just why detaching a SUSPECT database is a horrible idea....and how you can ultimately recover from doing so.
SQL Server Maintenance Plan Recommendation: Use Ola Hallengren’s Pre-Built Scripts
This set of maintenance plans has been recommended by SQL Pros one too many times to ignore. If you have a rather standard environment, check out Ola Hallengren's scripts for SQL Server Backup, Integrity Check, Index and Statistics Maintenance. Actually, if you have a pretty high end environment, you can explore it as well. Ola states:
I have developed a solution for backup, integrity check, index and statistics maintenance in SQL Server 2005, SQL Server 2008 and SQL Server 2008 R2. The solution is based on stored procedures, functions, sqlcmd and SQL Server Agent jobs. The solution has been designed for the most critical enterprise environments and it is used in many organizations around the world. The solution is recommended by SQL Server community experts, it was awarded Gold in the SQL Server Magazine Awards 2010 and it is free.
SharePoint 2010 Gotcha: Associated ECT Column Does Not Show Up in Filter Choices
I have a relational table in SQL Server that I access in SharePoint 2010 as an External Content Type. The table, disks, has an FK (serverID) that is associated with a PK in another table, servers, via an Association Operation. Once I create a list and add it to a webpage, I am unable to select the serverID column or its associated lookup column serverName.
After two days of troubleshooting and research, I decided to just stuff the code into the web part and surprisingly enough, it worked! Not only that, once I forced the query in the code, the column magically appeared in the Field Names drop down in the Filter Criteria. Wanna show SharePoint who's boss? Replace <Query/> in your web part's code with something like this:
<Query>
<Where>
<Eq>
<FieldRef Name="ServerID"/>
<Value Type="Integer">{ServerID}</Value>
</Eq>
</Where>
</Query>
The above is what I used to filter the results using ServerID in the querystring and it worked just as expected.
SQL Server: Block Level Replication Failures Causing Suspect Database
Recently, I worked with a client who uses block level replication software (think XOSoft/ArcServ) for their clustered SQL Server databases. During proactive testing one night, replication was broken and the failover server was brought up. For the first time in years, the procedure failed and the primary application database came up as SUSPECT. Examining Event Viewer revealed the following error:
Source: MSSQLServer Category:2 Type: Error Event ID: 824
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:9718172; actual 0:0). It occurred during a read of page (1:9718172) in database ID 3 at offset 0x00000f6b000000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\myDatabase.mdf'. Additional messages in the SQL Server Serror log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
We backed up the primary server's application database, restored the backup to the failover server and the database came up no problem. We restarted replication, waited for it to complete, stopped replication, brought up the SQL Server engine and again the database was in SUSPECT mode. We then deleted the .MDF and .LDF files and replicated them from scratch. Once replication was complete, we brought up the SQL Server engine on the failover machine and the database came up just fine. We restarted replication, stopped it, brought up the SQL Server and again it was in SUSPECT mode.
Rinse, repeat these steps with a few changes. Uninstall all updates and HIDS software, no luck.
Finally, the client's disk administrator detached the failover cluster's LUN, formatted it, reattached it, rereplicated the database and boom! That seemed to do the trick. Several stops/starts/replication breaks later, the database still comes up healthy. Because we were able to get the database to repeatedly come up healthy (after the restore and initial replication) I, for one, am surprised that this was the solution, but that issue made for a stressful week, and I'm glad the solution was finally found.
SharePoint 2010: External Content Types for DBA’s
One of the biggest annoyances of SharePoint 2007 was the lack of a native ability to work directly with SQL Server tables. SharePoint 2010 has addressed that issue with External Content Types. ECT's combine the best of both worlds -- you can modify and manage tables as usual in SQL Server, and display them (somewhat) easily and beautifully in SharePoint.
It's a bit of a process to understand how it works, but once you do, you're set. The process can be broken down like this:
- Create relational tables/views in a SQL Server database.
- Add the database as a Data Source in SharePoint 2010.
- Setup Secure Store service for SSO in SharePoint if necessary.
- Create an External Content Type.
- Create Association Operation to associate FK/PK*.
- Add Read/Update/Delete/Create Operations to the type in SharePoint Designer.
- Create Lists based off of those Operations.
- Display pretty, functional lists within SharePoint and add, update and delete rows to your heart's content.
- Create SharePoint lists using External Data if you want.
Below is a table that outlines the SQL Server equivalents (or as close as it comes) of ECT's.
| SharePoint | SQL Server | Notes |
| External Content Type | Table, view or stored procedure | One content type, to one SQL Server object. So if you are working with 3 views, you will have 3 External Content Types |
| External Data System | Database | |
| Operation | Query/Statement | Not exactly, but you get the idea. |
| Delete operation | DELETE FROM | Deletes only one record at a time. |
| Read Item Operation | SELECT TOP 1 * | |
| Create Operation | INSERT INTO | |
| Read List Operation | SELECT * FROM | This shows everything in the table unless there is a filter. You must return less than 2000 rows or it'll error out. |
| Update Operation | UPDATE | |
| Association Operation | Primary Key/Foreign Key | |
| Business Data Connectivity Service Set Permissions |
GRANT | All access is denied by default. BDC permissions are set in Central Administration. |
I developed these skills while working on a project that uses PowerShell to gather information about my servers and enter them into SQL Server database. From there, I setup a SharePoint site that not only displays that information in a useful way, but also manages the data from the web front-end. Hooray for SP2010!
SQL Server: Surefire Restore Method for Connection Hammered Databases
Recently, I had a database go into the dreaded SUSPECT mode after a failed byte-level replication. Once I brought it into EMERGENCY mode, an unknown, automated program immediately connected to it from several different servers.
Killing the connections and setting the db to single-user mode only created more headaches because this meant I couldn't be lazy and perform the RESTORE from SSMS; I'd keep receiving these errors: Exclusive access could not be obtained because the database is in use or The database is in single-user mode, and a user is currently connected to it.
I was determined, however. This is a battle I shall not lose! Ultimately, I ended up running the following code, which allowed for a successful restore:
DECLARE @dbname sysname
DECLARE @spid int
SET @dbname = 'myAppsDB'
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
GO
ALTER DATABASE @dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
RESTORE DATABASE @dbname FROM DISK = N'D:\DBBackups\myAppsDB.BAK' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10
GO
In ya face, automated program! And to the victor belong the spoils...
SQL Server: Compress SQL Server Backups using Native Windows Tool
SQL Server 2008 has backup compression built-in, but for SQL Server 2005 and below, you can compress backups using makecab from the command line or xp_cmdshell. Makecab even has a better compression ratio than gzip and does not require additional software to be installed on the server.
makecab D:\backups\dbname.bak D:\backups\dbname.cabSQL: Guaranteed Way to Quickly Shrink the T-Log
My hero, Brad McGehee, has often advised against shrinking the transaction log when it's unnecessary. According to his (really amazing) SQL Server DBA Best Practices PowerPoint Presentation, shrinking too often can lead to increased file fragmentation and excessive use of resources allocated to grow/shrink the file.
Sometimes, however, it is necessary. Often times, getting the transaction log to shrink can be tricky but here's how you can do it. That is, unless the t-log is currently in use (think SharePoint Search crawls), then the query will just wait there until the transactions are complete.
DBCC SHRINKFILE(logicalfilename, 1)
BACKUP LOG MyDatabase WITH TRUNCATE_ONLY
DBCC SHRINKFILE(logicalfilename, 1)
If you don't know the logical filename, run sp_helpfile for a list of database filenames. Be sure to make a FULL backup of your database once the shrink is complete.
Update: This guy also has a great write-up on reasons to avoid shrinking the transaction log when possible.
Slow SQL Query Execution in SQL Server 2005 SP3 x64
Recently, we've come across poor SQL performance on a very powerful SQL box and we were having trouble identifying the exact cause. After some talks with the software vendor and Microsoft, we were pointed to the TokenAndPermUserStore cache.
There are an increasing amount of articles online that speak about this cache, but it would still be difficult for most DBAs to come across by searching the Internet. I want to do my part to help increase visibility of this issue by hopefully providing a match for other people experiencing the issue.
Basically, the TokenAndPermUserStore cache stores security information for user sessions and queries. If your system executes a large amount of Dynamic SQL, the cache may begin to grow larger and larger. Our system was growing this cache into multiple gigabytes before we identified it as a problem.
Use the following query to determine the size of your TokenAndPermUserStore cache in megabytes. If your cache is over a couple hundred megabytes, you may start to experience slower performance with your queries.
SELECT SUM(single_pages_kb + multi_pages_kb)/1024.0 AS "SecurityTokenCacheSize(MB)"
FROM sys.dm_os_memory_clerks
WHERE name = 'TokenAndPermUserStore'
Luckily, there is a way to fix it, as described in this Microsoft KB article:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;959823
A registry key must be created and a trace must be turned on to get SQL Server to maintain the size of the cache. The information provided is somewhat limited, but the registry value is the number of entries allowed in the Token cache. Microsoft provides a formula to calculate how many token cache entries you may want to have. I found it more useful to decide on a size in megabytes that I wanted to limit the cache size to.
For example, if you want to max out the Token Cache at about 128MB, the registry value would be 4CD17 (314,647 entries). With some rough math, that says there are about 2,458 entries per MB of token cache, so adjust for your own needs to get a final value and convert it to Hex with a calculator.
Once the registry value is in place, turn on your trace:
DBCC TRACEON(4621, -1)
You will see the token cache size start to drop. If you want to help it along, you can actually reset it yourself
DBCC FREESYSTEMCACHE('TokenAndPermUserStore')
One last thing to note is that this trace will not restart when your SQL service restarts. If you want to keep the change in place, you will have to add the trace switch to your startup parameters, or find another creative way to set it when the service starts. This registry fix also works for SP4. SQL 2008 has built in these cache limits into sp_configure (access check cache quota and access check cache bucket count).
Hopefully this article has put you on the right path towards identifying an issue with this cache. I've only seen this happen on one of our newest servers, which has more cores and more RAM than any other server that we have.
Deploying Maintenance Plans with PowerShell or ASP.NET
I feel that my job as a DBA is very dynamic and usually keeps me busy with new things... except for deploying maintenance plans. I hate making maintenance plans. Luckily, I at least have a template that I can use to keep everything uniform, but it requires me opening up the plan in Visual Studio every time I want to deploy it to a new server. I'm sure there are many different solutions for this problem, but I came up with a very elegant method that I was able to integrate into my intranet server inventory system.
The first thing I did was make a copy of my existing maintenance plan templates. If you don't have a template, you should be able to export an existing maintenance plan out of one of your existing servers. It can be imported into Business Intelligence Studio from the server as well.
Once I had my templates ready, I edited the Local Server Connection and the backup and cleanup task paths. Instead of setting a real server name or backup path, I used placeholders. I went with [SERVERNAME] for the Local server connection and [BACKUPPATH] in the backup and cleanup tasks.
What I didn't realize before was that the DTSX files that I had been editing were pure XML files in disguise. So really all my PowerShell script needs to do is to read in the file, replace text and write it back out to a temporary DTSX file. Once that was written, I could then use the required assembly and methods to read it in as a package and deploy it to my server.
If you are going to be deploying SQL 2008 packages, you need the 10.0.0.0 version of Microsoft.SqlServer.ManagedDTS assembly. Version 10 will handle both 2005 and 2008 SSIS packages. If you only have version 9.0.x.x installed, you will not be able to deploy 2008 packages.
I got the idea and information on the assembly I needed from MidnightDBA.com. Here's some of the PowerShell code:
Since I need to perform the same steps over and over, I created a function to deploy the package and then call the function for each maintenance plan I want to deploy.
#Load up the assembly
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ManagedDTS") | Out-Null;
#Create function that can be called
function createPackage
{param ($filename, $foldername, $sqlversion)
# Read in my maintenance plan template and replace the place holders
$packageContents = [IO.File]::ReadAllText(".\$sqlVersion\$filename")
$packageContents = $packageContents.Replace("[SERVERNAME]","$ServerName")
$packageContents = $packageContents.Replace("[BACKUPPATH]","$backupPath\$foldername")
# Write the new package. MUST use UTF8 encoding or it will break.
Set-Content -Encoding utf8 ".\out.dtsx" $packageContents
# Load the new package
$Loadedpkg = $app.LoadPackage($pkg, $null, $null)
$Pkgname = $Loadedpkg.Name
#Save the maintenance plan to the server
$app.SaveToSQLServerAs($Loadedpkg, $null, "\\Maintenance Plans\$Pkgname", $ServerName, $null, $null)
Write-Host "$Pkgname deployed to $ServerName"
}
#Now for the main program code...
$app = New-Object Microsoft.SqlServer.Dts.Runtime.Application;
$Loadedpkg = New-Object Microsoft.SqlServer.Dts.Runtime.Package;
$DtsxPath = ".\"
$pkg = "$DtsxPath\out.dtsx"
# If you need to deploy both versions of SQL like me,
# you can have sub-folders with templates for each version if you like.
$SQLVersion = Read-Host "2005 or 2008?"
if($SQLVersion -ne "2005" -AND $SQLVersion -ne "2008")
{
Write-Host "$SQLVersion is not a valid option."
exit
}
#Get the server name to deploy to...
$ServerName = Read-Host "Enter name of SQL Server"
$ServerName = $ServerName.ToUpper()
# pass the name of the template file, the subfolder in your backup path, and the version of SQL.
# i typically have a User folder for user database backups and a System folder for System database backups.
createPackage "HistoryCleanupTemplate.dtsx" "" "$SQLVersion"
createPackage "SystemDBMaintTemplate.dtsx" "System" "$SQLVersion"
createPackage "UserDBMaintTemplate.dtsx" "User" "$SQLVersion"
createPackage "UserTLogMaintTemplate.dtsx" "User" "$SQLVersion"
Voila - your plans should be pushed out to the server.
This code can easily be converted to ASP.NET, which I decided was the better solution. This allows me to deploy maintenance plans directly from a webpage, instead of having to use PowerShell on a computer that may not have the proper assemblies installed. This does require installing Integration Services onto your web server, however.
I have simplified the code to the bare essentials. I'm not doing error checking or anything fancy. Here are the functions that would go in the page code. You are responsible for figuring out how you want the whole thing to work, but I have a basic web page created that allows me to enter the name of my SQL Server (text box), the backup path (text box or dropdown) and the version of SQL Server (dropdown) to determine which templates to use. Finally, I have a label at the bottom of the page that I can use to provide the user with information. I should also mention that your ASP.NET application pool account will need to be able to write the temporary DTSX file out to a folder location.
Protected Sub deployBtn_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles deployBtn.Click
resultsLabel.Text = ""
Dim SQLVersion As String = versionDDL.SelectedValue
'You will retrieve this value from a textbox or dropdown if it changes from server to server.
Dim backupPath As String = "<enter your backup path here>"
'If you wish, you can create the paths with the script to avoid having to do it manually.
IO.Directory.CreateDirectory(backupPath & "\User")
IO.Directory.CreateDirectory(backupPath & "\System")
'Log your work to a simple label so that the user has confirmation of their actions
resultsLabel.Text &= "Backup directories created for " & ServerName & "<BR />"
resultsLabel.Text &= createPackage(ServerName, backupPath, "HistoryCleanupTemplate.dtsx", "", SQLVersion)
resultsLabel.Text &= createPackage(ServerName, backupPath, "SystemDBMaintTemplate.dtsx", "System", SQLVersion)
resultsLabel.Text &= createPackage(ServerName, backupPath, "UserDBMaintTemplate.dtsx", "User", SQLVersion)
resultsLabel.Text &= createPackage(ServerName, backupPath, "UserTLogMaintTemplate.dtsx", "User", SQLVersion)
IO.File.Delete(Server.MapPath(".\") & "out.dtsx")
End Sub
Function createPackage(ByVal serverName As String, ByVal backupPath As String, ByVal filename As String, ByVal folderName As String, ByVal sqlVersion As String) As String
' Read in your package as text out of either a 2005 or 2008 folder containing the templates.
' I assume the folders are in the same location as your asp.net files
Dim packageContents As String = IO.File.ReadAllText(Server.MapPath(".\" & sqlVersion) & "\" & filename)
' Replace your placeholders with the Server and backup paths
packageContents = packageContents.Replace("[SERVERNAME]", serverName)
packageContents = packageContents.Replace("[BACKUPPATH]", backupPath & "\" & folderName)
' Set the path to write your temporary DTSX file.
Dim DtsxPath As String = Server.MapPath(".\")
Dim pkg As String = DtsxPath & "out.dtsx"
' Write the new package to the disk with UTF8 encoding.
IO.File.WriteAllText(pkg, packageContents, System.Text.Encoding.UTF8)
' Create your SSIS objects
Dim app As New Microsoft.SqlServer.Dts.Runtime.Application
Dim Loadedpkg = New Microsoft.SqlServer.Dts.Runtime.Package
Dim pkgName As String
' Load your new temporary package from disk.
Loadedpkg = app.LoadPackage(pkg, Nothing, True)
pkgName = Loadedpkg.Name
' Deploy to SQL Server
app.SaveToSqlServerAs(Loadedpkg, Nothing, "\\Maintenance Plans\" & pkgName, serverName, Nothing, Nothing)
' I choose to return the results of the function as a string with a success message
createPackage = pkgName & " deployed to " & serverName & " successfully.<BR />"
End Function
That just about covers it. As I said previously, I added this functionality into an intranet inventory system I created to manage all of my SQL Servers. When I add a new server to my inventory, I click a link to deploy maintenance plans and move on about my day. I still have to go in just to make sure my proper databases are selected, but for the most part it takes just a few minutes compared to the 15 to 20 minutes I was spending before.
About
-
We are IT consultants
who grew up living and loving life
in Cajun Country.
Authors
Categories
