Backup History

As someone who has helped design systems, I know auditing can take a back seat to more ‘pressing’ requirements; however, as anyone responsible for maintaining a system will know, a good audit trail can be immensely helpful in tracking down a problem. When it comes to backup information, SQL Server actually has a lot of good detail which can be very useful. For example, SQL Server can tell you the very first time you took a backup of your database. This script can give you this information by database.

[sourcecode language=”sql”]
Select MIN(backupset.backup_finish_date), backupset.database_name, backupset.server_name
FROM msdb.dbo.backupset
INNER JOIN msdb.dbo.backupmediafamily ON backupset.media_set_id = backupmediafamily.media_set_id
GROUP BY backupset.database_name, backupset.server_name
Order by backupset.database_name, backupset.server_name
[/sourcecode]

The very first backup?

While it may be nostalgic to look at the very first backup you took, it is not practical to keep all that information around?   Why is this?  Because, as Books On Line puts it–“When a restore [or Backup] is performed, backup history tables and restore history tables are modified.”  Just like any audit table, this information grows and as each operation is performed, more data is added to the mix.  Over time these operations can actually be slowed because of the amount of data.

History Cleanup

Just like any good audit table, we need to have a plan to trim the fat on a regular basis.  What to trim?  Well, my thoughts are they should line up with your requirements for data longevity.  If the policy says we keep backups around (even on tape) for 6 months, then I would make it six months and run the cleanup process every week.  Your time may vary by application and in that case, you can set it for the longest requirement in the environment.  Regular cleanup is the key–you never know how long those servers are going to be around.  Isn’t that right all you SQL 2000 boxes out there?   🙂

Script It Out

Removing old records is straightforward with stored procedure sp_delete_backuphistory.  You only have to provide a date and SQL Server will remove ALL backup history up to that date.  I normally have a SQL Agent job, scheduled to run once a week, looking something like this.

[sourcecode language=”sql”]
USE msdb
GO
DECLARE @BackupDate DATETIME;
SET @BackupDate = GETDATE() – 365;

Exec sp_delete_backuphistory @BackupDate;
[/sourcecode]

We Can Pivot If You Want To

Screen Shot 08-01-14 at 10.59 PMSo I get that I am a little late to the party here; however, I find myself having to pivot from time to time and am always looking back for code as a refresher.  While pivot is pretty cool, it always takes me a second to connect all the dots so I hope this post help do that.

So, with Pivot, we want to take data that looks like the data on the right–there are multiple rows for each ProductId and would like to get each product ID on one line with the quantities from each Bin.

When we are finished, the data will look like this.

Screen Shot 08-01-14 at 10.59 PM 001

One row for each product id

In my example, I am using the [Production].[ProductInventory] table in the adventureworks2012 database.  To make a row become columns, we must know ALL possible values we want to make as a column.  We specify these in the FOR line of the pivot syntax.  This query will create the columns for each value I specified in the Shelf column

[sourcecode language=”sql”]SELECT [ProductID],[A],[B],[C],[D],[E],[F],[G],[H],[I],[J],[K],[L]
FROM [Production].[ProductInventory]
PIVOT
(
SUM (Quantity)
FOR SHELF IN ([A],[B],[C],[D],[E],[F],[G],[H],[I],[J],[K],[L])
) AS PVT [/sourcecode]

Screen Shot 08-01-14 at 11.13 PMI should see something like this.  We are getting closer–we have our columns, but we still a line for each record.  We only want to see one line per product id.  This is where we put the columns we want from the table and the pivot syntax together in an almost sub-query like format and select the columns as we want them to display.

The syntax is something like this.  The first two rows are the columns I want, the second two are the columns I need from the table, and then add the pivot syntax followed by an order by and presto, you have your data the way you want it.

[sourcecode language=”sql”]SELECT [ProductID],[A],[B],[C],[D],[E],[F],[G],[H],[I],[J],[K],[L]
FROM
(SELECT [ProductID], Shelf, Quantity
FROM [Production].[ProductInventory]) p
PIVOT
(
SUM (Quantity)
FOR SHELF IN ([A],[B],[C],[D],[E],[F],[G],[H],[I],[J],[K],[L])
) AS PVT
ORDER BY [ProductID]
[/sourcecode]

All the World’s a Stage

I didn’t realize Pinal Dave had published his site stats last year and I recently came upon them and was very impressed with those numbers.  Kudos to him for all his effort.  My blog on the other hand would need him to chop off about 6 digits and we might be getting a little closer.  My blog won’t every be that popular; however, it is interesting to see where people are coming from and it makes me think the few posts I have up are helping someone.  The SQLFamily has always been about helping others and I am glad to play a small part.

Thanks Mom for all the hits.  🙂

Screen Shot 08-01-14 at 04.31 PM

Enqueueing A Message

After restoring a SharePoint database to the development environment, I came across the following error which would repeat every minute.

An exception occurred while enqueueing a message in the target queue. Error: 15404, State: 19. Could not obtain information about Windows NT group/user ‘DomainUser’, error code 0x6e.

I saw a post about ensuring the SharePoint service account had the appropriate access and after verifying mine were good, I saw a suggestion to change the database owner.  While it seemed rather strange, I thought it would at least check the owners of all the databases. I used this query:

[sourcecode language=”sql”]SELECT name, suser_sname(owner_sid) FROM sys.databases[/sourcecode]

This lists all the databases and their owners and they all seemed good–A NULL in the second column would indicate an unresolved user; however, I went ahead and change them all to SA as there were various owners.

[sourcecode language=”sql”]USE DBX
GO
EXEC sp_changedbowner ‘sa'[/sourcecode]

The error stopped occurring in the log and life resumed. 🙂

Sizing the SQL Server Error Log

Sometimes SQL Server generates so many messages you think it actually enjoys it.  While there may be some other factors in creating a new log by default, it appears to me the most common scenario is when SQL Server restarts.  If your systems are anything like mine, you want SQL Server to stay up and this can cause the logs to get very large and this makes finding specific message more painful than it needs to be.

To me it makes sense to separate the logs by day. EXEC sp_cycle_errorlog will create a new SQL Server error log.  I normally create a job that runs a midnight and creates a new error file.  By default, SQL Server only keeps 6 error log files and I normally increase that number to 30.

Files To Keep
If the server has already been set up, I normally use the GUI by right clicking on SQL Server Logs and choose configure. If I am setting up the server, I have this piece of code in my scripts.Screen Shot 08-01-14 at 01.59 PM

[sourcecode language=”sql”]
USE [master]
GO
EXEC xp_instance_regwrite N’HKEY_LOCAL_MACHINE’, N’SoftwareMicrosoftMSSQLServerMSSQLServer’, N’NumErrorLogs’, REG_DWORD, 30
GO
[/sourcecode]

Cycling the Logs
This code will create a new job to run at midnight and cycle the logs.

[sourcecode language=”sql”]

USE [msdb]
GO

/****** Object: Job [Cycle Error Log] Script Date: 08/01/2014 13:31:39 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 08/01/2014 13:31:39 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]’ AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N’JOB’, @type=N’LOCAL’, @name=N'[Uncategorized (Local)]’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N’Cycle Error Log’,
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N’No description available.’,
@category_name=N'[Uncategorized (Local)]’,
@owner_login_name=N’sa’, @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Cycle Error Log] Script Date: 08/01/2014 13:31:40 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @[email protected], @step_name=N’Cycle Error Log’,
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N’TSQL’,
@command=N’EXEC sp_cycle_errorlog’,
@database_name=N’master’,
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @[email protected], @name=N’Midnight Ride’,
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20140801,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959
— @schedule_uid=N’bca9d15e-e47f-40a4-a236-e14020f77b76′
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

[/sourcecode]