SQL Server 2005 service pack 2 was recently released and already Microsoft has a critical update for it. *Sigh*
Service pack 2 was updated to include this, so you only need to install the critical update if you downloaded service pack 2 prior to March 5, 2007.
Read more →
To defragment your indexes, we now use ALTER INDEX in SQL Server 2005 rather than DBCC DBREINDEX and DBCC INDEXDEFRAG, which have both been deprecated in 2005.
The REBUILD option of ALTER INDEX is equivalent to the old DBCC DBREINDEX; the REORGANIZE option of ALTER INDEX is equivalent to the old DBCC INDEXDEFRAG.
Read more →
EDIT: A new version of this stored procedure is available here.
I've completely modified my isp_Backup stored procedure. The new version supports both SQL Server 2000 and 2005. It also gets rid of the need to use my other backup stored procedures, such as isp_Backup_TLog and isp_Backup_LS.
Read more →
In our production environment, we have two 4-node clusters. One cluster runs at the primary site; the other cluster runs at our disaster recovery site. Each cluster is running 11 SQL Server 2005 instances.
Read more →
Our disaster recovery and database availability solution involves clustering and Database Mirroring in SQL Server 2005. At our primary site and our disaster recovery site, we have a 4 node cluster with several SQL Server 2005 instances on it.
Read more →
To quickly disconnect all non admins from a database, I use this:
ALTER DATABASE Database1 SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE Database2 SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
One of the development teams recently requested that their production databases be copied to a development server on a weekly basis.
Read more →
I've updated my isp_DBCC_CHECKDB stored procedure as I was getting errors on a SQL Server 2005 server when one of the databases was the mirror in a database mirroring scenario or when one of the databases was in a loading state (NORECOVERY option of RESTORE command).
Read more →
We use MOM 2005 to monitor our production environment. We hadn't been receiving any alerts from MOM when a SQL Server 2005 job failed, so I was tasked with tracking the problem down.
Read more →
Check out this article for white papers, scripts, and tools on SQL Server 2005 best practices.
Legacy Comments
Jesse
2006-12-13
re: SQL Server 2005 - Best Practices good resource.
Read more →
For those of you who have been using SQL Profiler 2005 for a while, you probably are already aware of this. We've been using SQL Server 2005 for a few months now, but I hadn't touched SQL Profiler 2005 very much yet.
Read more →
By default, xp_cmdshell is disabled in SQL Server 2005 for security reasons. I am currently using xp_cmdshell in my backup stored procedure to delete files, so I must enable xp_cmdshell on my SQL Server 2005 instances.
Read more →
Here's the stored procedure that I use to update statistics on a database. I don't use sp_updatestats as it doesn't allow you to change the number of rows to sample. I typically run it weekly on the databases that I support.
Read more →
If you've installed multiple instances of SQL Server 2005 on the same database server, perhaps you've noticed Microsoft's naming convention for the file locations. They are now using MSSQL.n, where n is the instance number.
Read more →
Yesterday, a developer sent me an e-mail indicating he was having problems creating a table in SQL Server Management Studio. He was using the GUI rather than the CREATE TABLE T-SQL statement.
Read more →
If you are considering using SQL Server 2005 on a 64-bit platform, then I'd highly suggest reading Michael Otey 's “The Essential Guide to 64-BIT Server” article in SQL Server Magazine's May 2006 issue.
Read more →
Service Pack 1 is now available for SQL Server 2005. You can download it here.
Here is the list of the new features and improvements included in this service pack.
Read more →
A while ago, I posted my DBCC DBREINDEX and DBCC INDEXDEFRAG stored procedures. They perform the DBCC operation on all indexes in the database, which is probably not a good idea.
Read more →
You can find the poster of the SQL Server 2005 System Table Map in the December 2005 issue of SQL Server Magazine. Thanks to Rob Volk, here is the online version.
Read more →
The below stored procedure performs a RESTORE of the newest BAK file in the specified directory (@bkpDir). I recommend putting it into an Admin database (hey just name it Admin!) rather than in master.
Read more →
Over in the SQLTeam forums, eyechart has posted information about running SQL Profiler without sysadmin rights. I recently granted sysadmin rights to a developer so that he could run SQL Profiler in the development environment.
Read more →