Using xp_delete_file in maintenance plans

There is no official documentation on the usage of the Stored Procedure xp_delete_file. In my research I have found that it can be used outside of the Maintenance plans. I also ran into some issues with it not deleting the backup files that I wanted it to delete in sub-directories.

It looks like in certain versions of MSSQL 2005 that the option to remove files from sub-directories is just not there.

The only resolution is to upgrade or grab the complete accumulative service pack according to Microsoft.

On one of the servers that I wanted to implement this on is going away soon so I didn't want to apply any updates but I still needed to implement a Maintenance plan that would remove old backups. The solution was to create a "Maintenance Cleanup Task" for every back folder containing the backup files that I wanted to have deleted. Luckily it was only 9 folders that I had to deal with.

For using xp_delete_file out side of the Maintenance plan here is some information I have gathered.

Usage:
SQL Server Version 9.0.1399
EXECUTE master.dob.xp_delete_file 0,N'E:SQLbackupdir',N'bak',N'06-03-2011 08:00:00'

On different versions I noticed you could use the parameter at the end to tell it to go into sub-directories but only the first level.

EXECUTE master.dbo.xp_delete_file 0,N'E:SQLbackupdir',N'bak',N'06-03-2011 08:00:00',1

So here it is broken down.

,N'E:\SQLbackupdir' - path of the directory that has the backup files
,N'bak' - extention of the backup file
,N'06-03-2011 08:00:00' - it will remove everything older than this date
,1 - some version have this parameter its for sub-directories

MySQL Error 1050 table already exists error

In MySQL when running on a Windows server with "Make table names case insensitive" set to 2. You will get a table exists error when trying to create a table that has previously been created. I have found that this occurs if you use upper case letters in the name of the table. For example: Create Table JohnTempTable (ud1 int, ud2 varchar(10)); Drop Table JohnTempTable; Now when you try and create the same table you get a table exists error. The work around I found is to create your original table using all lower case. example: Create Table johntemptable (ud1 int, ud2 varchar(10)); Now you can drop and recreate the table without the table exists error.

Using NOLOCK in sql select statements

It may not be necessary to use NOLOCK after the from clause in your scripts. Microsoft has put out some info on the use of NOLOCK and I have come to the conclusion that using NOLOCK should be the exception to the rule and not the rule. Here is a link that explains.

http://blogs.msdn.com/b/sqlcat/archive/2007/02/01/previously-committed-rows-might-be-missed-if-nolock-hint-is-used.aspx

Enjoy the article its quite long.

Thanks

Copy your Outlook Signatures to a new system

If you use Outlook 2007 and you have Signatures that you use. When you purchase a new system you can take them with you to the new system here is how:

If you have windows XP you will usually find you Signatures under
C:\Documents and Settings\\Application Data\Microsoft\Signatures\

If your login name is Linda under XP then usually the will be named Linda. Your path would look like this:
C:\Documents and Settings\Linda\Application Data\Microsoft\Signatures\

You can always do a search of your system to find the location of your signatures as well.

Once you find where your signatures are you can copy them to your new station or on a USB drive to transfer. Copy everything out of the \Signatures directory and place it back into the directory on your new drive which will be something like this:
C:\Users\AppData\Roaming\Microsoft\Signatures\

If you can't see the AppData directory you probably need to change your folder and view options. All Windows operating systems hide by default system folders. You will need to click on your computer and bring up File Explorer and go to Folder and Search Options in Windows 7. Then click the View tab. Click on "Show hidden files, folder, and drives". Save by clicking OK. You will now be able to browse to the above locations.

John
Ledyard Consulting
ledyardconsulting.com

Revision Backups

Backups are great for catastrophic failures but what about the daily backup of your files as you are working on them. For example you have a file you have been editing all day and you have saved it a few times. You can’t go back and grab the original file because you saved over the top of it. This is where File Hamster comes running to the rescue. File Hamster will save a revisionists history of your document every time you save it so you can go back in time and restore it. You can choose any save point that you want to look at. There are some advanced features as well that are very cool. You can choose network drives local drives any drive on your system. Very cool tool and exelent for the workplace as well as home.

Try it for free: http://www.filehamster.com/

John Ledyard
IT Consultant
Ledyard Consulting
support@ledyardconsulting.com

Make sure data is good then backup

As it is important to backup data it is just as important to verify that your data is good. I have been called to the scene of a server crash several times in hopes of having me get the database back up and running by using their backups only to find that the backups of their database are corrupt.
This is not a good situation to be in and often times leads to data loss with employees having to enter months of work back into the database.
So make sure you have a good backup plan. You should have "verify backup" enabled on your backup jobs. That will not secure you yet but will insure that at least your backup file is good. It will not insure that your data is not corrupt. To do that you will need to run database maintenance checks that check the database for inconsistencies weekly if not nightly. That is where my company comes in handy. We can insure your data is good and create nightly or weekly checks of your database to make sure it is not being corrupted.

Now how does corruption happen. So many times I hear "I was just using my database yesterday and today the server crashed and now it is corrupt". Well one scenario that I have run into more than once is that a hard drive controller has failed and that is what has caused the database to become corrupted. Usually on a mirrored or stripped set of hard drives this occurs more frequently. The controller starts placing things in the wrong area of the drive and forgets where it put it and this is what causes the corruption. (very simply explained)

I have seen businesses lose countless hours of work and money by not having regular backups and maintenance done on databases. So much money and time could have been saved if just two simple things where occurring: backup and database integrity checks.

John Ledyard
IT Consultant
Ledyard Consulting
(916) 715-8328
www.ledyardconsulting.com

Anti-Virus software

It is very important to have the latest Anti-Virus software installed and updated with the latest virus definitions. If you need a quick solution I have come to like the free Anti-Virus software AVG. You can link to the free software here. It is their basic security suite and if you like it you can purchase their upgraded product for better protection.

John Ledyard
Computer Consultant
Ledyard Consulting

Slow Computers

I field a lot of questions regarding slow computers. It has been my experience that if you have a sudden slow down in your computer you need to check and see what was recently installed and you should also check and see if you have a virus or not. One of the companies that I have always trusted to scan and detect infections is Symantec. They have a free scan you can preform to see if you are infected with anything. Here is the location Click here: Now continue to click on: "Continue to Symantec Security Check" and not the "Download now". This opens a new window and you can click on Start under the Virus Detection section. This will start some programs that you will need to let run.

Once your scan is complete it will give you results of the scan. You should have a better feeling now if you have no infections. If you do get some infections listed then it might be a good idea to try and remove them. This is were we professionals come in handy because there can be a lot of variables involved in removing the infection(s).

Most people don't notice their computer slowing down until it is a crawl and most times the best course of action is to reload the operating system. The clean install is the most effective method in getting that computer back to brand new speeds but doing this can be a pain. If you don't have a copy of the original disks that came with the computer it makes it very difficult to get back to original form because of missing drivers and programs.

If you have done the scan and you have no virus check the space on your hard drive. If there is enough room on the hard drive then check your memory and see if you have enough. You can upgrade your memory and sometimes you might be surprised at the increased speed.

John Ledyard
Ledyard Consulting

Screen Resolution - Whats that?

While working with web pages for my business, Ledyard Consulting and having friends test them, I often get reports back that they can't see a part of the web page and have to use their scroll bars to see the complete page. There is a simple solution to this and that is changing your screen resolution.

A non-technical explanation of screen resolution would be it is what makes your letters and words look large or small but gives you less or more viewing room. A low-resolution would mean not much space to view things like when you look at a web page it cuts graphics off on the left side and bottom. Though the bottom is always cut of no matter the resolution, you can improve the right side. A high-resolution would mean you can see more of the page with out having to scroll so much.

When you look at web pages and find that you can't see very much of the content of that page without using your bottom or side scroll bar, then maybe you should try changing your screen resolution.

Your screen resolution can easily be changed by right clicking anywhere on your desktop. Let me give you the quick guide.

1. Right click on the Desktop
2. Click on Properties or (Vista) Personalize
3. Click on Settings Tab or (Vista) Click on Display Settings
4. Screen resolution slider click and hold and drag

After you right click on the Desktop you will see a menu come up. On the menu you will click on "Properties". If you use Vista you can click on "Personalize". Now you should see a window with tabs if you are using XP click on Settings. In Vista you will see a list and you will need to click on "Display Settings". After clicking on "Settings" or "Display Settings" you should now see a window and in that window in the bottom left area you will see a slider. If you click and hold on that slider you can change your display settings by moving the slider to the left or to the right. A good setting to start out with is "1024 by 768" and and then you can increase it by going to "1280 by 1024". What this does for you is gives you more room on your desktop. You can see more of the web page or more of the document that you where working on.

Now sometimes people say,"My screen is too small. I can't see anything". There is a program that you can use but I have never liked it. It is a magnifier. If it is installed you will find it by going to "Start", "All Programs", "Accessories", "Accessibility" then Magnifier. Check it out maybe you will like it.

By John Ledyard
Ledyard Consulting
Computer, Network and SQL dB consultant
Elk Grove, CA

The Data Center Water Incident

The False Sense of Security

When I worked for an association I had an experience I will never forget. I was the IT Manager and I managed a network with several servers. The servers where kept in locked cabinet inside a data center with raised floors, power conditioning, temperature and humidity control and a Halon fire suppression system designed to suppress the fire with out damaging your computer systems. So basically this room was engineered with protecting computer systems in mind giving me a false sense of safety.

One morning when I was getting ready to go into work I received a call that the server was down. When I entered the office area just out side the data center I noticed that the ceiling tiles had collapsed onto desks and all over the floor. It was then explained to me that water was leaking from the floor above. The floor above had an instant hot water heater installed in a kitchen area for the tenants in that section of the building. It had burst and sent water spewing for hours before it was noticed. The water had made its way through the foundation and was leaking to the floor below. Outside of the data center there was lots of damage but inside if the data center that housed the servers there was only two panels that collapsed. One on top of my desk and the other right on top of the, that’s right, the cabinet that housed the servers. No where else in the entire room was there more water leaking then right where the cabinet stood. The water made its way into the cabinet and traveled to the 4^th server in the rack and took it out.

The server that was affected was a very robust server designed to be redundant in most all its hardware. The server had 4 drives and was using a striped configuration. To our best efforts we could not get the system back online after the system was dried out of course. The Raid controller was damaged by the water among other unknown components.

To top it all off the backups where not being done because we where still awaiting approval of the expense to correct the failing backup drive. We did have a backup form Friday before but this happened on Wednesday. So my supervisor the accounting manager who had no experience in IT matters, decided to send the drives away to see if we could get them recovered against my recommendation to just restore from the backup we had and have accounting input in the work that they had done from the missing days. The total missing days were 5 working days. Which I found out later would have only taken them about 16 hours to input back in the lost days plus days that we where down because of the damaged server.

So what is the lesson in all of this? Make sure you have a good disaster recovery plan for your company. Insure that your backups are working and please expedite requests for backup drives and tapes from your IT staff.

John Ledyard

Ledyard Consulting