Having custom keyboard shortcuts is nothing new in Mangement Studio . . . → Read More: Another Managment Studio custom Keyboard shortcut post]]> At-least 3 people [Aaron Bertand(Blog|Twitter), Michelle Ufford (Blog|Twitter), Aaron Alton (Blog|Twitter)] in the community have blogged about this topic already here, here and here and not sure if we need another post on this topic. But I thought this might help someone and brings something new hopefully!

Having custom keyboard shortcuts is nothing new in Mangement Studio and most of the times we see stored procedures and occasionally a select statement like the ones listed below are used.

I never thought of a shortcut for a where clause filter until this question came up today. After a couple of tries I am able to make this work. Here is what I had in the shortcut and while running I had to pass the value of the where clause within single quotes like below.

--keyboard shortcut in SSMS
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 

--How to execute the shortcut for a where clause filter
--Note that you need to highlight the complete word below
--including the single quotes to make this work
'Store'

--Here is the message that's generated from the SSMS.

/*------------------------
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =  'Store'
------------------------*/

That’s it folks and its a short post.

                          

SQL Server has versions, editions, service packs, compatibility levels and Internal versions. Most of the people know about the first 4 and if you are wondering about what is the database internal version then you should look at Database Version vs Database Compatibility Level post . . . → Read More: Database Internal version; Create Version and Current Version]]> UPDATE: This post is migrated from my old blog on 05/16/2010.

SQL Server has versions, editions, service packs, compatibility levels and Internal versions. Most of the people know about the first 4 and if you are wondering about what is the database internal version then you should look at Database Version vs Database Compatibility Level post by Jonathan Kehayias and Technet Q&A article by Paul Randal.

Here is a script I wrote to look at what’s the create and current internal database version of all databases. This might be handy if you have to look at multiple databases on different servers.

CREATE TABLE #version (
    VersionID INT
    , VersionName VARCHAR (255)
) 

INSERT #version
    SELECT 515, 'SQL Server 7.0' UNION ALL
    SELECT 539, 'SQL Server 2000' UNION ALL
    SELECT 611, 'SQL Server 2005, VARDECIMAL disabled' UNION ALL
    SELECT 612, 'SQL Server 2005, VARDECIMAL enabled' UNION ALL
    SELECT 655, 'SQL Server 2008' UNION ALL
    SELECT 660, 'SQL Server 2008 R2' 

CREATE TABLE #DBCC (
    ParentObject VARCHAR (255)
    , [Object] VARCHAR (255)
    , Field VARCHAR (255)
    , [Value] VARCHAR (255)
) 

CREATE TABLE #DBCCResults (
    ServerName VARCHAR (255)
    , DBName VARCHAR (255)
    , VersionCreated INT
    , CurrentVersion INT
    , CreatedDate DATETIME
) 

EXEC master.dbo.SP_MSFOREACHDB
    @Command1 = 'USE ? INSERT INTO #DBCC EXECUTE (''DBCC DBINFO WITH TABLERESULTS'')'
    , @Command2 = 'INSERT INTO #DBCCResults (ServerName, DBName, VersionCreated, CurrentVersion, CreatedDate)
                        SELECT TOP 1 @@SERVERNAME
                        , ''?''
                        , (SELECT Value FROM #DBCC WHERE Field = ''dbi_createVersion'')
                        , (SELECT Value FROM #DBCC WHERE Field = ''dbi_version'')
                        , (SELECT Value FROM #DBCC WHERE Field = ''dbi_crdate'')
                        FROM #DBCC'
    , @Command3 = 'TRUNCATE TABLE #DBCC' 

SELECT ServerName
    , DBName
    , v1.VersionName AS VersionCreated
    , v2.VersionName AS CurrentVersion
    , CreatedDate
FROM #DBCCResults dr JOIN #version v1
    ON dr.VersionCreated = v1.VersionID
JOIN #version v2
    ON dr.CurrentVersion = v2.VersionID

DROP TABLE #version, #DBCC, #DBCCRESULTS

                          

Problem

Although I don’t remember who asked this question in the forums but the user was surprised . . . → Read More: T-SQL Tuesday #005 : Who put my database offline?]]> This post is participating in T-SQL Tuesday #005 hosted by Aaron Nelson (@sqlvariant). This is an exciting recurring blog post party invented by Adam Machanic (@adammachanic) few months ago, which gives good exposure to budding blog writers and happy to participate.

Problem

Although I don’t remember who asked this question in the forums but the user was surprised & puzzled to see their database was set to offline. User wanted to know who did this and they didn’t have any kind of special monitoring and are using SQL Server 2005.

fn_dblog is an option?

fn_dblog is an undocumented function that helps to look at the low level actions performed in the transaction log and it is quite useful to do diagnostic adhoc auditing. But don’t use fn_dblog as the main auditing tool and it is not meant for that purpose. Lets see if this function helps us find out who put the database offline.

Lets create a test database, use the fn_dblog function to look at the contents of the transaction log, set the database status as offline.

use master
create database offlineDB
go
use offlineDB
select [Begin Time], [End Time], [Transaction Name], [Operation], [Description], * from fn_dblog (null, null)
go
use master
go
alter database offlineDB set offline
go

Now, lets try to look at the transaction log and see if setting the status of the database offline action is captured.

use offlineDB
go
select [Begin Time], [Transaction Name], [Operation], [Description], * from fn_dblog(null, null)
go

Msg 942, Level 14, State 4, Line 1
Database ‘offlineDB’ cannot be opened because it is offline.

That’s right and it won’t work as the database has to be available to look at the transaction log and in this case the database is still offline. Now, lets set the database to online and check again.

use master
go
alter database offlineDB set online
go
use offlineDB
go
select [Begin Time], [Transaction Name], [Operation], [Description], * from fn_dblog(null, null)
go

Here is what we get when the database comes online. A CHECKPOINT was issued and the fn_dblog shows *ONLY* the active portion of the transaction log and in this case we see only the CHECKPOINT. That concludes that fn_dblog is NOT an option to find out who put the database offline.

DEFAULT TRACE is an option?

Starting from SQL Server 2005, a light weight default trace was added to do monitoring of the activities on the server and the activities tracked are listed below.

At first glance, nothing really stands out that helps us to find who set the database offline. But on closer look there seems to be a possibility. From the above it could be possible that 'Object:Altered Event Class', which indicates an object being altered, might help us. But does is database considered an object? You BET! Look at ObjectType Trace Event Column to figure out the different types of objects covered in SQL Trace.

--Credit goes to Jonathan Kehayias for this query
DECLARE @filename VARCHAR (255)
SELECT @FileName = SUBSTRING (path, 0, LEN(path)-CHARINDEX('\', REVERSE(path))+1) + '\Log.trc'
FROM sys.traces
WHERE is_default = 1;  

--Check if you have any Execution Warnings
SELECT gt.HostName,
       gt.ApplicationName,
       gt.NTUserName,
       gt.NTDomainName,
       gt.LoginName,
       gt.SPID,
       gt.EventClass,
       gt.IntegerData,
       te.Name AS EventName,
       gt.EventSubClass,
       gt.TEXTData,
       gt.StartTime,
       gt.EndTime,
       gt.ObjectName,
       gt.DatabaseName,
       gt.FileName,
  gt.IsSystem
FROM [fn_trace_gettable](@filename, DEFAULT) gt
JOIN sys.trace_events te ON gt.EventClass = te.trace_event_id
WHERE te.name = 'Object:Altered' and gt.ObjectType = 16964 --Database ObjectType
ORDER BY StartTime;

One big disclaimer. Note that the above event captures not only if someone puts the database offline, it also covers any meta-data changes to the database itself like changing the owner, setting databse in read only etc...But it shouldn't be difficult to find out who is playing with the database object.

This is just a tip of the iceberg on how default trace can be used to monitor activities on the server, look for performance problems, look for security related activities etc and report back this information with out setting up any special monitoring. Look out for Jonathan's blog later this year where he will publish a detail white paper on this topic.

Unfortunately, I couldn't help the OP because the OP thought default trace was expensive and turned it off. That's not entirely true and Linchi Shea did some testing to benchmark the performance hit and it was between 1-2% cpu hit which is very very low. I strongly advise to turn ON default trace if you haven't already as it can reap rich benefits from it.

                          

Corruption happens. Be prepared. Read Paul’s blog on CHECKDB-From-Every-Angle & Disaster Recovery, watch the excellent knowledge packed Corruption survival techniques video and practice the sample corrupt databases.

Long story:

Corruption happens and it can happen when you least expect it. There is no hiding from this, even if you are remotely responsible for maintaining and supporting a . . . → Read More: Lessons learned: Recovering from first ‘suspect’ database]]> Short story:

Corruption happens. Be prepared. Read Paul’s blog on CHECKDB-From-Every-Angle & Disaster Recovery, watch the excellent knowledge packed Corruption survival techniques video and practice the sample corrupt databases.

Long story:

Corruption happens and it can happen when you least expect it. There is no hiding from this, even if you are remotely responsible for maintaining and supporting a server, you should be prepared to deal with this. Some DBA’s may not face this for few years, may be entire career and while others are NOT so lucky. Unless you are prepared to deal with corruption before corruption happens, you are guaranteed to be on expressway to unemployment. Its not only the job, its your reputation and references on the line. Fortunately SQL Server has the best community thats willing to share, help and guide and only for the ones who dare to ask!

If you are still reading this then here is my story.

I started my current position about 2 1/2 years ago as a database developer in a team of 10. Over the past year, we had some restructuring(s) and by mid Jan this year I became the sole person responsible to maintain and support the enterprise SQL Servers. And this transition didn’t happen by grooming me over the years, it just happened and here is your access, go manage the servers. To some extent I prepared for this by looking more into the administration side and participating in forums over the past year. Fast forward to mid Feb where I am scrambling to get lots of stuff done. About 200+ quarterly reports are due by monday morning and I am no way near completion on friday and hoped friday night, saturday and sunday will help me get closer to completion by monday morning. Got some decent progress on friday evening and saturday during the day. But on that freaky Saturday night, 2 disks gave up and a memory DIMM failed and crash recovery couldn’t complete cleanly. Voila, we got a suspect database. Note that this is a Data warehouse server, not an OLTP production server.

I am a regular reader of Paul’s blog and learnt few things of what not do when corruption happens and what has to be done to get the server back on track. But I never practiced as my role was different at that time and felt no immediate need. With the knowledge I have, figured out the scope of the problem, weighed my options to restore or repair and came up with the steps I need to follow to recover from corruption. From DBCC CHECKDB, we inferred there is corruption in only one table and the data from this table can be rebuilt 100% from other sources. And in our case, based on the circumstances running “repair_allow_data_loss” seemed appropriate step. Here is the abridged DBCC CHECKDB output.

.Net SqlClient Data Provider: Msg 8936, Level 16, State 1, Line 2
Table error: Object ID 678930882, index ID 1, partition ID 72057595414315008, alloc unit ID 72057595461500928 (type In-row data). B-tree chain linkage mismatch. (14:1415116)->next = (14:1413599), but (14:1413599)->Prev = (14:1415505).
.Net SqlClient Data Provider: Msg 2533, Level 16, State 1, Line 2
Table error: page (14:1415134) allocated to object ID 678930882, index ID 1, partition ID 72057595414315008, alloc unit ID 72057595461500928 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
.Net SqlClient Data Provider: Msg 8976, Level 16, State 1, Line 2
Table error: Object ID 678930882, index ID 1, partition ID 72057595414315008, alloc unit ID 72057595461500928 (type In-row data). Page (14:1415134) was not seen in the scan although its parent (14:1414070) and previous (14:1415115) refer to it. Check any previous errors.

CHECKDB found 0 allocation errors and 75 consistency errors in table ‘SomeTableName’ (object ID 678930882).
CHECKDB found 0 allocation errors and 75 consistency errors in database ‘SomeDBName’.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (SomeDBName).

On that sunday, Ted Kruger (@onpnt) was dealing with a suspect database himself and we connected on Twitter. I asked Ted if he is willing to look at my steps and give feedback. I also sent an email to Paul Randal (@PaulRandal) to look at the scope of the problem and requested feedback on the troubleshooting steps I outlined. Within minutes I am in a parallel conversation with both Paul & Ted and gave feedback that I am on the right track. Here are my steps to recover from suspect database.

1) Take backup of the database.
2) Put the database in single user mode
3) Run CHECKDB with allow data loss
4) Figure out and add the data to this table
5) Run CHECKDB again to make sure its clean.
6) Put the db back into multi_user mode

Although the plan was good, things didn’t go as I expected first. Since we are looking to do repair, it seemed appropriate to take some kind of backup if things went further south. What I didn’t know is if the database is in emergency mode then full backup isn’t allowed. And ran into the below message. I wouldn’t necessarily put it as panic moment but in that moment it crossed my mind to put the database online and take a full backup. Doesn’t it sound awful, it does now but at that moment it seemed appropriate (clearly showing lack of knowledge). I realized my mistake as soon as I hit F5 but wanted to let it finish by itself. Even after an hour and looking at 0 reads and 0 writes I killed the session but it doesn’t want to be killed and got stuck, probably doing some kind of Log Recovery.

.Net SqlClient Data Provider: Msg 3033, Level 16, State 0, Line 1
BACKUP DATABASE cannot be used on a database opened in emergency mode.
.Net SqlClient Data Provider: Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

We had to wait for the session to completely rollback and waited few hours and waited some more and waited some more. After 48 hours and in consultation with Paul again we bounced the server. Once the db came back as suspect again, verified the level of corruption to be the same as earlier, skipped #1, moved on with next steps and the db was back online in few hours and we recovered all of the data with 0 data loss. Although I am being hailed as the one to take care of business at work I know I failed miserably. Even though there was no data loss but the reporting server was down for almost 2.5 days and our reports were late and we have to pull in favors. A very big thanks to both Paul Randal and Ted Kruger for helping me get thru this issue. If I have to rate this, I will give a A+ for SQL Server community (especially Paul & Ted in this situation) and C- for myself for poor execution.

It was a big wake up call and exposed how vulnerable we are. Had this issue happened to the production OLTP server, I could have earned my express ticket for unemployment. If the issue happened again, I am better prepared now to deal with this and currently spending significant time on learning more about database corruption and practicing how to deal with different types of corruption. I said at the beginning of the post and will repeat once again to the readers. If you are remotely responsible for maintaining a SQL Server then read Paul’s blog on CHECKDB-From-Every-Angle & Disaster Recovery, watch the excellent knowledge packed Corruption survival techniques video and practice the sample corrupt databases and be prepared before corruption strikes.

Coming out of this, I have setup geo redundancy (thanks Pete, our Architect) and also setup a daily recurring job that automatically copies the production backup to test restores everyday for our production server. And a playbook to deal with issues like this is work in progress. If you are still reading this, now ask yourself, are you prepared to deal with corruption if corruption happens at this moment on your production server?