SQL Happenings

My first post of 2012 was a bit ambitious I admit. But I didn’t expect to trip up this quickly. So with that being said, I am still going to post as often as possible, making it a priority. Over the past few days, I finished out a busy work week, in which my company is prepping for a practice DR flip. Being so new, and having zero replication, mirroring, and log shipping experience, forced to shadow the more senior DBAs (which is just fine with me at this point). Yesterday was spent flying to Minnesota, and for a Florida boy, my goodness this is a cold state! Our practice flip was pushed back due to other IT technical difficulties. So as @SQLGator would say, the #sqlawesomeness starts tonight at midnight. In between recovering from 12-15 hour shifts, I will update everyone on how the DR flip went. I’m in Minnesota until Thursday afternoon, so I am hoping to have plenty to blog about, besides the cold weather.  Cheers!

Move to Tumblr?

Planting the seed, Mr. Johnny Appleseed.

As most of my readers know…wait I think I only have one, thanks SQLGator! I recently switched jobs. A long-time friend of mine has wanted to get back into the technical arena for some time now, after being in management for the past several years.  Can we really blame him for wanting to get out of management? The only problem was he just didn’t have that much technical experience, smart guy, just lacked the experience. Lucky for him, he worked for an employer that was more than willing to take chances on people in new positions. With that being said, he had to make it known that he was interested, talk to the right people (His boss, other supervisors) and do what we called, plant the seed.  Plant the little thought, so that when the head-honcho was sitting at home, thinking about whom he could get to fill a position. POP! His name would auto-magically come to mind. Planting the right seeds at the right time, the right people will know you are on the “Open Market”.

Today, I received at text message, telling me that the two positions he had been vying for had just been filled by internal employees. Probably by people less qualified than he. Unfortunately he failed, over much debate, and thought, to plant the seeds required to be considered for these positions. The right people never knew he was even looking and hence never even considered.

Moral of the story? Make your boss aware of your career plans and goals. Plant the seed!

A New Year

So I just recently accepted a new job in Jacksonville, FL, moving from the Tampa Bay area. It’s given me the opportunity to work with SQL 100% of the time. As to where my last position I had to split my time 50/50 as a System Administrator. Starting my new position has made me realize just how much I have to learn, and has truly made me feel like a rookie. I have been reading up as much as possible on replication and t-sql, so at least for the first part of 2012. I am going to concentrate my posts on those two subjects. I’d also like to start concentrating on SQL Server 2012.

And now for a few things:

  •  I’m excited to start attending the Jacksonville SQL Server User Group meetings and getting to know new people in the SQL community
  • I’m excited to maybe hang out with @SQLChicken 
  • I really want to get my first SQL Certification this year
  • I’d like to speak at a local user group meeting
  • I’m sure there are more, and I will be updating this post through-out the week

Well that’s enough for now – cheers!

 

My first post for 2012!

Okay, recently @SQLGator said he was going to make a resolution to update his blog once a day for 2012. I love his ambition, so much that I am going to try this feat. And that’s all I have to say about that, and for post number 1.

How to use SHRINKFILE

I was bouncing around a few of my SQL servers today, and found one of my system drives was below 300mb. Hmmmm can’t be good for business, so I dug a bit deeper. Turns out I had an .ldf file that was nearly 14gbs, ouch! All because the database recovery model was set to FULL, and we were only performing full backups nightly. To remedy the problem is actually very easy.

Backup up the log first:
BACKUP LOG MODEL WITH TRUNCATE_ONLY

Then run this shrink command:
ALTER DATABASE databasename SET RECOVERY SIMPLE
GO
USE databasename
GO
DBCC SHRINKFILE(‘databasename’, 2)

GO

The number 2 represents the target size you want the database in megabytes.

In my case I went from nearly 14GB to only 6.62MB, it gets as close to your target number as possible.

The Curious Case of the Ever Expanding Log File – SHRINK!

Why is one of my system drives nearly full?

After a bit of investigating, I find the modellog.ldf file has grown to an outstanding 50Gbs! Stop traffic!

WHY!

Turns out (Thanks #sqlhelp) the model database was setup for FULL Recovery Model…OUCH!

Okay, simple enough fix! Switch that Recovery Model to Simple, no need for FULL.

Within SSMS, right click the model database (or the db in question), TASK > SHRINK > FILES

In the File Type drop down box, select Log. Ensure the file name is correct, along with the location. Thus far I have chosen  release unused space, and simply clicked OK.

Go ahead check the log now…BAM I went from nearly 50Gbs to a few kb – NICE!

Its easy in T-SQL too!

USE [DBMaint2008]
GO
DBCC SHRINKFILE (N'DBMaint2008_log' , 0, TRUNCATEONLY)
GO

I need results! Via E-mail, please.

I like the idea of taking several issues, ideas, or problems, and combining what I need to get results.
I was recently poking around Red-Gates site and found a nifty little *FREE* app called SQL Scripts Manager. SQL Scripts Manager is a great application for not only SQL Rooks, but for SQL Vets too. It contains over 25 scripts designed to help simplify the everyday life of a SQL DBA, including automating troubleshooting, diagnostic, and maintenance tasks. It features scripts from people like Dan McClain, Gail Shaw, Glenn Berry [twitter|blog], Louis Davidson [twitter|blog], Ola Hallengren, Phil Factor, Rodney Landrum, Tim Ford [twitter|blog] and Tracy Hamlin [twitter].
Specifically I found a sweet little script by Rodney Landrum, perfectly named “Last good backup”.

Lists the date and time of the last good backup of a particular type, for each database on an instance
This script is useful for checking the progress of backups for all databases on an instance. You can quickly see whether a particular type of backup is missing or delayed, and then investigate further if necessary.
If you need to run an ad hoc backup, you can use the Back up database script included with SQL Scripts Manager.

Here is the exact script:

SELECT sd.name AS [Database],

CASE WHEN bs.type = 'D' THEN 'Full backup'
WHEN bs.type = 'I' THEN 'Differential'
WHEN bs.type = 'L' THEN 'Log'
WHEN bs.type = 'F' THEN 'File/Filegroup'
WHEN bs.type = 'G' THEN 'Differential file'
WHEN bs.type = 'P' THEN 'Partial'
WHEN bs.type = 'Q' THEN 'Differential partial'
WHEN bs.type IS NULL THEN 'No backups'
ELSE 'Unknown (' + bs.type + ')'
END AS [Backup Type],
max(bs.backup_start_date) AS [Last Backup of Type]
FROM master..sysdatabases sd
LEFT OUTER JOIN msdb..backupset bs ON rtrim(bs.database_name) = rtrim(sd.name)
LEFT OUTER JOIN msdb..backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
WHERE sd.name 'tempdb'
GROUP BY sd.name,
bs.type,
bs.database_name
ORDER BY sd.name, [Last Backup of Type]

Wow, that’s a pretty powerful, and useful script, I thought to myself “hmmmm, I’d like to have this script run nightly and email me the results, but how?” This would give me (and my boss) a valuable report every morning by receiving an email, detailing the results of the previous nights backups, in a quick, precise, easy to read method.

Enter Twitter and the wonderful hash tag  #SQLHelp.  I posted a question…

Email the results of a query #sqlhelp

Within the matter of 15 minutes I had a conversation taking place with Marcos Freccia [twitter] starting out on twitter, and ending via Gmail.

Marcos gave me this script:

execute sp_send_dbmail
@profile_name = ‘MCITP Profile’, -- DbMail Profile
@recipients = ‘your@email’, -- Your Email
@subject = ‘Product`s list’,
@body = ‘Some Message’,
@body_format = ‘Text’, -- You can use HTML also, but you have to create some html code before.
@importance = ‘High’, -- Importance Level.
@query = ‘select idProduct, name, quantity from dbo.Products’, -- This is the query, also can be a stored procedure
@execute_query_database = ‘MCITP’, — The database that query going to be executed
@attach_query_result_as_file = 0 , -- If you put 0 the query will be showed in the body, 1 will be a file attached in email
@query_result_header = 1 -- if you put 1 you see the colum name, 0 no.

Perfect, with the combination of the script Marcos had provided and Rodney’s script, I could perform exactly what I set out to do.

Here is the script:

execute sp_send_dbmail
@profile_name = 'your servers mail profile',
@recipients = 'who is going to get the email',
@subject = 'SQL Database Backup Report',
@body = 'important',
@body_format = 'Text',
@importance = 'High',
@query = 'SELECT sd.name AS [Database],
CASE WHEN bs.type = ''D'' THEN ''Full backup''
WHEN bs.type = ''I'' THEN ''Differential''
WHEN bs.type = ''L'' THEN ''Log''
WHEN bs.type = ''F'' THEN ''File/Filegroup''
WHEN bs.type = ''G'' THEN ''Differential file''
WHEN bs.type = ''P'' THEN ''Partial''
WHEN bs.type = ''Q'' THEN ''Differential partial''
WHEN bs.type IS NULL THEN ''No backups''
ELSE ''UNKNOWN (''+ bs.TYPE +'')''
END AS [Backup Type],
max(bs.backup_start_date) AS [Last Backup of Type]
FROM master..sysdatabases sd
LEFT OUTER JOIN msdb..backupset bs ON rtrim(bs.database_name) = rtrim(sd.name)
LEFT OUTER JOIN msdb..backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
WHERE sd.name <> ''tempdb''
GROUP BY sd.name,
bs.type,
bs.database_name
ORDER BY sd.name',
@execute_query_database = 'MASTER',
@attach_query_result_as_file = '1',
@query_result_header = '1'

NOTE: The formation change in Rodney’s script where there were single quotes we now have double quotes

If anyone is actually reading this, leave a comment 🙂

A special thanks to Marcos Freccia for his help. And an indirect thanks to Red Gate and Rodney Landrum.

ERROR 3154 – Restore Database Error

So as I am getting more comfortable with t-sql, I am trying to use the GUI less and less. Recently I had to restore a db to a DEV server. Good chance to use t-sql instead of the GUI!

RESTORE DATABASE adventureworks
FROM DISK = 'e:\adventureworks_backup_201107062202.bak'

No can-do sir! Error!
Error 3154: The backup set holds a backup of a database other than the existing database
hmmmm…OK, with a little research here is the resolution, a simple one at that!
Simply add the WITH REPLACE at the end of the RESTORE command! Its that easy!

RESTORE DATABASE adventureworks
FROM DISK = 'e:\adventureworks_backup_201107062202.bak
WITH REPLACE

Out of Cycle Backups?…Yes Please!

I wasn’t really planning on writing anything on this topic, but then I thought about my readers…uh wait…I’m the only one reading this blog, LOL! Here we go!
The setup…
A few management folks at my organization, you know the type, the ones above your pay grade. Thought it would be a wonderful idea to have one of our most important databases backed up to a laptop off site each night. So in the event of a disaster, (Hurricane) the laptop could be scooped up, taken to a hotel, and work could still be processed. So they come to me, and tell me to make it happen….ASAP! After all, hurricane season had just started.

So we will call the source server, “Point A” and the destination laptop “Point B”. I already knew this situation might get a bit complicated. The database on Point A is set to Full recovery mode, a full backup every night, two diffs during the day, followed by transaction logs every 15 minutes, nothing special there. The good news I only had to restore a full backup down to Point B, so its recovery model is simple. I knew once I got the backup to Point B, the rest would be easy. Because, of the way SQL Server backups work, I didn’t want to take an additional Full backup to copy over to Point B, making my backups out of sequence. It’s like being Marty McFly and altering time, we all remember how that turned out! I first thought I was going to have to write a powershell script that would copy my latest backup, and move it via UNC path to Point B. Then I ran across this little jewel. Enter Copy Only Backups, simple, and easy to prove they are working!

So I must admit, as a SQL Rook, I need a few more tools in my tool belt then the seasoned vets out there, and one little helper I really enjoy using is Ola Hallengren’s backup strategy, I consider it my MacGyver Knife at this stage in my career.

Here is a sample script that I am working with using Ola’s method with Copy Only:

EXECUTE dbo.DatabaseBackup <——Download the code here
@Databases = ‘AdventureWorks’,
@Directory = ‘\\LaptopName\ShareName’,
@BackupType = ‘Full’,
@Verify = ‘Y’,
@Checksum = ‘Y’,
@CopyOnly =’Y’

That's it, run this script as a SQL Job within SSMS nightly and now my "Out of Sequence" backup isn't effecting my production backup sequence.
So at this point, now that I have my Full Backup on Point B, I just need to restore it nightly.

RESTORE DATABASE Adventureworks
FROM DISK = ‘C:\ShareName\AdventureWorks.bak’;

This too will be ran from a SQL job on Point B, nightly. Additionally, I do not exactly want to start collecting these out of sequence backups on Point B, so I will run a Cleanup SQL Job, nightly as well. And the final step would be to create a new Full backup sequence of the "Out of Sequence" backup...make sense? lol.

BACKUP DATABASE AdventureWorks
TO DISK = ‘C:\Backups\AdventureWorks.BAK

Now, earlier in the post, I mentioned that it is easy to prove this out of sequence method. Refer back to this link...the jewel The Author uses LSN (log sequence number) information to track the backups, to compare and contrast the difference the LSN numbers when taking a normal backup and then taking a backup with Copy_Only (out of sequence).

So there you have it folks...Please leave your comments, better ideas are always well appreciated, from the SQL Vets out there. Cheerio!