Month: September 2011

Diagnosing Database Restores – Trace Flag 3004

Recently one of my fellow DBA had an issue with a Database Restore, where he was restoring a database using a full backup(from GUI) and the wizard was progressed upto 100% and it stayed there for more than 30 Minutes and he pinged me to see what’s going on!! Well, The actual issue was the database which he took the backup had 16000+ VLF’s!!(Very Very Bad sign of Log file sizing) and the log file size was close to 78 GB and there was a hugeeee open transaction(which SQL will Undo while restoring the Backup). Note he was creating a brand new database, So SQL will be creating brand new files and remember transaction log file will be Zero Initialized always as apposed to Data files(If Instant File Initialization is enabled on your Instance).

Our focus in this post is not regarding VLF’s and how Log file should be managed, but to identify what’s happening behind the scenes with your Database Restore and kind of identifying current Phase of restore process. For this you can make use of  Undocumented trace flag 3004 along with 3605 to get the events logged in our Error Log. You can enable this trace flag either globally or at session level. (No need of  touching your start up parameters for these trace flags) Please see the below Screenshot.

For demo purpose, Let’s restore a Full Backup of a database(it has an Open Transaction) and let’s see how our Error Log looks like with this trace flag being enabled.

I’ve restored the DB,  see the Error Log in the below Screenshot.

Focus on these steps where you can note the actual time SQL Server took for Zeroing your Log File, Redo,Undo and all other required Information.

1.Zeroing C:\Program Files\Microsoft SQL Server\MSSQL10_50.PROD\MSSQL\DATA\3005_Demo_1.ldf
from page 1 to 20584 (0x2000 to 0xa0d0000)
2.Restore: Waiting for log zero on 3005_Demo
3.Zeroing completed on
C:\Program Files\Microsoft SQL Server\MSSQL10_50.PROD\MSSQL\DATA\3005_Demo_1.ldf
4.Restore: LogZero complete
5.Starting up database ‘3005_Demo’.
6.The database ‘3005_Demo’ is marked RESTORING and is in a state that does not allow recovery to be run.
7.Starting up database ‘3005_Demo’.
8.Recovery is writing a checkpoint in database ‘3005_Demo’ (19).
9.Recovery completed for database 3005_Demo (database ID 19) in 9 second(s)
(analysis 20 ms, redo 1801 ms, undo 7690 ms.)
10.Restore is complete on database ‘3005_Demo’. The database is now available.
Database was restored: Database: 3005_Demo, creation date(time): 2011/08/23(23:16:47), first LSN: 38:19:1 blabla

Hope this is informative!

When was SQL Server Restarted??

As a DBA seldom we see cases where a User reports “Hey Mr DBA, my application went down for a minute or even few minutes, Now everything appears to be Okay. Can you pls take a look what happened?” Well, our very First place should be SQL Server Error Logs( My Personal favorite to start troubleshooting any issue) and let’s assume in this case that our Instance has been restarted and we see it in our Error Logs. So this is the traditional way to find out regarding SQL Server restarts – The SQL Server Error Logs.

Let’s jump into another scenario where you want to know when was the SQL Server restarted. It would be little annoying to cycle through all the error logs in SSMS if you are recycling your Logs on a daily basis and maintaining multiple Error Logs(PS we can have upto 99 Archives of Error Logs listed in our SSMS). So what would be our easiest bet to figure out when was the Instance restarted??

Method 1:

TempDB Creation date:

Since we all know Tempdb will be created each time you restart SQL Server, Tempdb creation date will give us the answer.

Alternate Methods:

1st Session(Session_id = 1) creation time and by starting SQL Server 2008, we can query DMV sys.dm_os_sys_info.

FYI Please see the below Screenshot

Note: Notice a slight difference in Milli seconds( could be seconds) in tempdb creation time and the time when SQL created it’s first session. But that’s Okay! Choose your Best method. I use tempdb creation date(since I’m used to it since SQL 2005). May be I’ve to start using DMV on new boxes!!

Hope this helps!

SQL Server Encryption – Part5 Encrypting Data(TDE) vs Encrypting Connections

This is the final part of Encryption series where i’m going to show how to encrypt connections in SQL Server. In the previous 4 parts of this series we’ve seen how to enable TDE for your Database, what EFS is and what Bitlocker is. If you recall correctly, all we discussed was how to encrypt our Database(Physical Files on the Disk), not securing Connections. In other words the data will be encrypted as long as it stays on the disk but not encrypted while SQL is sending data over to clients as you can see below!!

Note: TDE encrypts Data on Disk, Connections are not Encrypted using TDE.

So…..how to encrypt data across Wires(in other words how to Encrypt Connections)?

We have to use 128 Bit SSL(Secure Socket Layer) within SQL Server to encrypt Connections btw SQL Server and Client(s), which leads to slower performance but your SQL Server is Very Very Secured! Basically we’ve to Install a trusted certificate in our windows Certificate Store on our Physical Server where our SQL Server Instance is living on and we’ve to let SQL know that “Hey Mr.SQL Server! we’ve a certificate for you..make use of it for every connection”.

Step 1: Install Certificate(We can work with our Server/Network Team on this)

Step2: Go to your SQL Server Config Manager and enable Encryption as shown below.(I’ve 3 Instances on this Server, I’ll show you on PROD Instance)

Navigate to network Configuration, Right click on Network Protocols and select Properties as shown below.

Now, all your Certificates will be shown in the drop down menu under “Certificates” tab as shown below(I don’t have any on my Server)

Now, on Flags tab you can choose either Force Encryption to Yes or leave to default No as shown below.

If you Choose YES – Clients who are not encrypted are not allowed to connect.( In other words, any client which doesn’t allow encryption is declined to connect)

If you choose NO – Clients will be seeing encryption as optional.

Note: SSMS(Let’s assume it as a client application now for time being) knows how to encrypt connections. We can choose an Option to Encrypt my connection while connecting to the Database Engine as shown below.

As you can see, I’m checking the Box to Encrypt my connection from SSMS client while connecting to SQL Server DB Engine.

Hope this helps for a Kick Start on Encryption!!

GeekBench – Bench Marking your Server!!

GeekBench……One of the Best Benchmarking tools available for Server Admins! Well, I didn’t mean that’s a useful tool only for Server Administrators. We, as a SQL Server DBA can run this tool against our servers to obtain Geek bench scores! You can run this on your Laptops as well!!Yeyyyyyy…..Check for your Geek bench Scores if you brought a brand new laptop 😉

For more info, please visit http://www.primatelabs.ca/geekbench/

Actually I’ve downloaded latest version of Geek bench(Trail version/Tryout mode) and the Scores are as follows…

Just Click on Run Benchmarks and the results from my laptop are as follows!! Please note, in trial mode, X64 benchmarks are disabled 😦

As you can see Geek Bench gave my laptop score 2542 😦 Which is obviously a very poor Score. (Well, i bought this Laptop for 550$ almost 2 years ago…..and it is happily handling 3 Instances of SQL Server 🙂 )

Actually you can submit your Scores to geek bench to compare and contrast with all the other submissions on a similar build. For example You can see the below screenshot for all the Dell Studio 1555 scores being submitted.

As you can see this guy hardly exceeded a score beyond 3000 and my laptop scored 2500+! Cheers…

If you are interested in my total scores, please click here

SSMS(SQL Server Management Studio) Standard Reports and my Personal Favs!!

If you are already working on SQL Server 2008 since decent amount of time, you might have already known the inbuilt standard reports which we can popup Just by right clicking on an object in our Object Explorer.

In this Blog post, i would like to show some interesting In Built Reports which you(we) as a DBA might(Should) be interested! Before going any further, please see the below Screenshot for what i’m actually referring to if you are new to SQL Server.

Server Dashboard: This is a very useful report especially for a DBA to understand his Instance Configurations, Settings at a very high level in a single glance. I really like this guy and would recommend you to open this fab report as frequent as you can on your Server.

As you can see, at a single glance it is giving my Server(Well, It’s actually SQL Instance) startup time, Instance name, Version, Stand alone or clustered, AWE Settings, memory settings, CPU’s visible to SQL and many more….My personal favorite in this report is “Non Default Configuration Options“. Basically this tiny section is giving me a clear picture of settings which are deviating from default SQL Server settings, which is really reallyyyyyyyyyyy useful for a DBA. Let’s assume a scenario where a DBA from your team is leaving and he/she is doing a Knowledge Transfer to you. Just by pulling this info, you can ask him/her “Hey i’m seeing the following Non Default Configuration Options on this Instance, Can you pls. explain why??…”

Transaction Log shipping Status: This is a single place where you can get all the cool information regarding all the thresholds, last backed Up t-log, copied t-log backup, restored t-log backup(with File names and LSN details as well…Huhuuuuuuuuu That’s awesome!!) Note: For accurate and more meaningful info, you’ve to pull this report from both your Prod and DR Servers, to see if your DB is out of sync.

Schema Changes History: You can see the DDL Changes being made on your Databases. For example If some one dropped a Table and if you as a DBA are looking for an option to track him/her down….Well, this is one of the places 🙂

Note: The above information will be pulled from SQL Server Default Trace. So the Data will be not stay for ever! You can see only the info what has been captured by your DEF trace. very Imp….!

Sooo…are these the only Inbuilt Reports(which you are seeing in the first Screenshot) we have in SSMS?? Nope!!  If we Right click on the Database in Object Explorer, you’ll get completely different set of reports! See the below Screenshot.

My personal favorites are

Backup and Restore Events: Please see the below Screenshot!

It gives me the Avg time it is taking for backup Operations! You can use this as a Benchmark for your backup duration (well, there is much better way to pull that info from MSDB, I’m Just talking about basic Benchmarking here ;))  If you expand Successful Backup/Restore  Operations, it tells you what was backed up/restored, the location of files, when it was performed and who(the Login) performed the operation…Well one more way to track these operations!!

Disk Usage:

You might think that it only Shows, the sizes of your Data and log files on your Disk. Well, there is one of the Most important hidden gem in this report. Auto Shrink/Auto Grow Events as you can see below. Tadaaaaaaaaaaaa!

This gives me a clear picture that my log file has auto grown several times on 9/7/2011, Which means as a DBA I’ve to consider changing my File Growth settings on my database. This is the Most important factor which might be hindering performance of your Database( For the sake of  this example I’ve changed the autogrowth options to 1 MB on data and 10% on my log file and i inserted 10000 rows. Well this is just my test lab, but imagine the same happening on your Live Production database!!!! Really Really bad).

Note: If you see any Auto Shrink Operations in the above report, ha ha you are gone. It’s time to take action immediately!! I’m not going to deal with what Shrinking does to a Database in this post, for now…Just remember, Setting Auto Shrink option turned ON on any database is the worst possible thing you could potentially do to your Database!!

I recommend to explore all these Inbuilt, completely free and most fabulous reports and get familiarize with what each report can do for you as a DBA. Hope this Post helps!