Microsoft technologies and what I do for fun: March, 2007

Edwin Daniel's profileMicrosoft technologies a...BlogLists

<< First < Previous Next > Last >>

March 27

So your MSDB is getting BIG!

One of the reasons that your MSDB database is growing is that of backup and restore history being written to it as records in the restorehistory table. Backup and restore processes are being recorded in this table together with the restorefile, restorefilegroup, backupfile, backupset, backupmediafamily, and backupmediaset tables. Microsoft has provided a system stored procedure called sp_delete_backuphistory in the MSDB database to clean up backup and restore history stored in the MSDB database. To keep the MSDB from growing too big, especially if you are running transaction log shipping jobs in both the primary and the standby server, it would be a recommended practice to execute this stored procedure on a regular basis. As a lazy DBA as I am, I always look at ways to automate repetitive tasks. I created a SQL script to do this task which should be run once a month. Version 1 is shown below.

DECLARE @strSQL NVARCHAR (44)
DECLARE @dateBack SMALLDATETIME
SET @dateBack=DATEADD(m,-1,GETDATE()) --change the number value to number of months before current date
SET @strSQL = 'msdb..sp_delete_backuphistory ''' + CONVERT(CHAR(11),@dateBack) + ''''
EXEC master..sp_executesql @strSQL

Version 2 is shown below

USE MSDB

DECLARE @dateBack SMALLDATETIME
BEGIN

SET @dateBack=(SELECT DATEADD(m,-1,GETDATE())) --change the number value to number of months before current date
EXEC sp_delete_backuphistory @dateBack

END

Bear in mind that this stored procedure will take quite some time to execute during the first run since it is using the concept of cursors in it's implementation. The stored procedure commits the transaction (deleting records in the tables mentioned above) after each cursor iteration. One way to solve this problem is to create an index on the backupset, restorefile, and restorefilegroup tables in the MSDB database. I have yet to test this but bear in mind that any changes made to the system will not be supported by Microsoft. The Index Tuning Wizard does not display these tables as they are considered to be system tables so this rules out this test method.

3:15 PM | Add a comment | Permalink | Blog it | SQL Server General

March 26

SQL Server 2005 Migration Assistant

With SQL Server 2005 gaining popularity, a lot of people are looking at migrating their database systems to it. What Microsoft did is to make sure migration would be as easy as it can be. As early as SQL Server 2000, Microsoft already came up with the Migration Assistant. SSMA is an available download to help you migrate your existing database system to SQL Server - in my case to SQL Server 2005. SSMA is now available for Oracle, Access and Sybase users. I had a chance to use the version for Access. A friend of mine wanted to create an application to retain the information they have on maintaining and administering their routers, switches and network appliances. They already have an Access database but thought about porting it to the web. I volunteered to create the front-end application for them using ASP.NET 2.0. In this sense, I also recommended that they use SQL Server 2005 Express Edition since it is available as a free download. This means they can take advantage of the features that SQL Server 2005 has to offer. In order to maintain everything they have in their Access database, I decided to use SSMA 2005 for Access. This is also available as a free download from the Microsoft Download Center. Once installed, it will ask you a series of questions and walk you through creating a project that will be used to migrate the Access database to SQL Server 2005. One thing to note is that the tool does not detect the version of SQL Server 2005. This means that you can use your database in any version you might have (Express, Workgroup, Developer, Standard, Enterprise , except for Compact I guess). A simple attach/detach process can be used to move the migrated database into any version. What I'm going to do next - if I have the luxury of time - is to do a test migration for an Oracle database using the SSMA for Oracle tool.

8:17 PM | Add a comment | Permalink | Blog it | SQL Server 2005

March 19

Event ID 6009 : Last Reboot Time - Windows Servers

I always see these wallpapers displaying the last reboot time and date for a Windows Server. Curiousity had me thinking how this information was retrieved. If you looked at the System Event Log, Event ID 6009 will give us an idea when the server was last rebooted. This will help us troubleshoot a lot of reboot related problems, including checking whether patches were applied successfully or not as some patches require a reboot to take effect.

8:48 PM | Add a comment | Permalink | Blog it | Windows administration

March 11

Deploying DST 2007 Patch in Windows Mobile device

Providing IT services to clients in the US has its challenges. One of which is that the US government comes up with different policies which affect the implementation of IT in an organization. Just recently, the new DST 2007 act was passed in the US and is supposed to be implemented in March 11. We have applied patches on our client's Windows and Unix servers to address these issues but apparently, we don't really know the overall impact unless we are a part of it. I had my own shares of experiences on how this DST 2007 act has affected almost every industry in the US which requires IT. I applied the patch on my Windows Mobile device which I alwys use for scheduling and other tasks even though I do not synchronize it with my laptop. Knowing that I will be visiting the US more often than I expect to, I thought I should see how this Microsoft patch will address issues raised by this new challenge. The patch for Windows Mobile devices is as simple as downloading it from the Microsoft website using your device. Just open the cab file and it automatically updates your Windows Mobile device for any DST change. But how do I know it works? Well, I happen to fly to Redmond, Washington for a Microsoft conference from Panama City, Florida on 11th March - exactly the date the DST 2007 is implemented. When I looked at my device, it automatically adjusted every schedule I had for that week (which made me not miss my flight by an hour). But I have been hearing a lot of Americans about the impact of DST 2007. Does it really meet it's purpose? Let the American people decide about that.

8:04 PM | Add a comment | Permalink | Blog it | Windows administration

March 02

Restoring the master database in SQL Server 2000

Bad hair day, unexpected events happen and suddenly your master database is no longer performing well. Your SQL Server is still running but you feel that it is not functioning as it should be. So you decide to restore the master database (that's on the assumption that you have a copy of he latest database backup). The master database does not behave the same as the other user databases as far as restoration is concerned. One thing that you need to do is to restart the SQL Server service in single-user mode. This is better said than done when you are working on a clustered SQL Server instance. You need to run the sqlservr.exe tool using the following switches: -c (tells the SQL Server service to star independently of the Windows NT Service Control manager), -m (tells SQL Server service to be started in single-user mode) and the -sInstanceName. After you were able to start the service, you can connect to SQL Server using your favourite tools (Enterprise Manager, Query Analyzer, osql, etc.) but making sure that there are no applications nor sessions currently connected (remember, this is in single-user mode). Restoring the master database is fairly straight forward. Once the restore is finished, the service is stopped. You restart the service in normal operation and everything is back to normal...I guess. If this still doesn't work for you, rebuilding the master database will have to be done. Just make sure that you have a backup of all your system and user databases if you decide to take this path.