SQL Backup Monitoring and Reporting

A task that all DBA´s has is backup and restore and to ensure they works. I found an excellent article by Chad Miller on Sqlservercentral that provides great scripts and a good explanation how it works. Enjoy and make use of it in your environment.

http://www.sqlservercentral.com/articles/Backup/66564

Regards
Stefan

Database restore last time

Ever wanted to know when your database was last restored and from where? I found the script to do it on mssqltips.com
The information you will get is the following:
Restored database, restored by, restore type, restore start, restored from and restored to.
You never know when your boss gets really crazy and wants to know everything and then this can get useful and sometime it will get useful to you as well.

Stefan

Linked Oracle servers on x64

A couple of weeks ago i installed a server with windows 2003 x64 and SQL 2005 for one of our production servers that will be migrated to a new domain. In the old domain it was installed on a x86 system but now it had grown so much so we needed the extra memory usage of x64.
The system is running fine on x64 but we have a linked server to a oracle database and that wouldn´t work when we set it up in the same way as on the x86 system. After some googling I found a excellent article on mssqltips.com which explains every steps on the way and it works perfectly.

Regards
Stefan

Maintenance script

I found a great maintenance script for backup, integrity check and index optimization in SQL Server 2005 and SQL Server 2008. The solution is based on stored procedures, functions, sqlcmd and SQL Server Agent jobs.

You can read about it and download it here

Paul S Randall has an interesting article here about validating backups. It´s good to have a reminder about that sometimes, even if we know how important it is.

Regards
Stefan

Default trace in SQL Server 2005

Have you ever had a problem with your sql server and the boss wanted to know who or what was responsible?
Of course you didn´t have a trace running, because you haven´t had any problems with the DB before.
You are not completely lost if you haven´t disabled the default trace which is on by default. The following query will tell you if it´s enabled or not.
select * from sys.configurations where configuration_id = 1568

In the following articles you will find a good beginners guide and which events are captured in the default trace.
http://www.sqlservercentral.com/articles/SQL+Server+2005/64547/#
http://blogs.technet.com/vipulshah/archive/2007/04/16/default-trace-in-sql-server-2005.aspx

Regards
Stefan

Too many VLF:s or to few?

I found an excellent blog post by Kimberly L. Tripp yesterday about transaction log performance and VLF:s. I later investigated a couple of our own databases that I have taken over and they are clearly in need of a little bit more maintenance then I have done so far.
The recommendations are maybe around 50 or a little bit more depending on the size of the db/log. I found out that we had over 3000 VLF:s in one medium sized database. This is however quite easy to fix. The secret is capacity planning to avoid this problem.

Here are the full article from Kimberly.

http://www.sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx
http://www.sqlskills.com/blogs/kimberly/post/8-Steps-to-better-Transaction-Log-throughput.aspx

Happy reading
Stefan

Moving tempdb Error

Here´s a good article about when you fuck up with the move of tempdb, forgetting the file extension and the Sqlserver won´t start.

http://jmkehayias.blogspot.com/2009/01/error-5123-severity-16-state-1-when.html

Stefan