Thursday, May 21, 2009

DBA database management checklist


For both old and new DBAs there are fundamental procedures that should be addressed and proper processes put in place to handle various areas of database management for SQL Server. Whether you are a full time DBA or this is one of many job roles that you perform the same basic steps should be implemented and adhered to in order to have some peace of mind that you are performing the correct procedures to ensure a healthy running SQL Server environment. For old DBAs these items should be a no-brainer, but often a refresher is good reminder to make sure everything is in place. For new DBAs some of these basic items are not all that apparent and often some of the most basic DBA 101 items are sometimes overlooked. So based on this, what is a good plan to implement to make sure the basic SQL Server DBA items are being addressed?Solution
The following is a checklist of 10 items that should be in place for all of your SQL Server database servers. Some of these items are pretty basic and easy to implement, while others require a higher level of effort to think through what is needed and then to implement the process.These items are not necessarily written in any priority order, because not any one of these items is a complete database management plan it really requires all of these items to be thought about, addressed and implemented.# Item Steps

1 Backups
This is one of the most basic items to be addressed. Everyone knows that a good solid backup plan should be in place for all databases, but time and time again I run across servers where the wrong or no backup plan is in place.

To get started you need to ask yourself a few questions such as the following:
What are you trying to recover from when a failure occurs?
How much data can be lost? A day, one hour, a week, none…
What kind of processing occurs, transaction based, batch loading, a combination?
Can the data be easily recreated if there is a failure or is this the only source of this data?

This is just a short list of questions to ask, but once these are addressed this will allow you to determine;

1) the recovery model for your database and 2) the backup processing.

Depending on your needs your backup plan may look like one of the following:
Daily full backups only
Daily full backups and transaction log backups every hour
Daily full backups, transaction log backups every 15 minutes and differential backups every 4 hours

Note: If you are unsure what to do, start with at least full backups on a daily basis. If your data is very important and you cannot easily recreate the data, change your database recovery model to FULL and implement both full and transaction log backups.

2 Run Integrity Checks
Another area that should be addressed is the integrity of your data. SQL Server offers a few options to address this such as DBCC CHECKDB, DBCC CHECKTABLE, DBCC CHECKALLOC, etc…

These commands check the allocation, structure and logical integrity of all objects in your database. In addition, to running these commands either through maintenance plans, maintenance jobs or via a query window you also need to analyze the data to look for any integrity issues that need to be addressed. This is another area that I see a lot of where the commands are run via maintenance jobs, but no one ever reviews the reports to see if there are any issues that need to be addressed.

For the most part these integrity issues pop up a lot less than they did with earlier versions of SQL Server, but this is still an area that should be part of your DBA procedures.

3 Maintain Indexes
Indexes are those helpful pointers that allow you quick access to your data pages in your database. When indexes are newly created the structure is nice and clean and everything works great by accessing your data via the index instead of having to scan the entire table.

Over time these helpful indexes become fragmented and take up unnecessary space and accessing your data pages is not as efficient as it was when the indexes were first created. This is where index maintenance is a critical DBA process that needs to be in place.

In SQL Server 2000 you have the ability to run index rebuilds across the board for all tables when using maintenance plans. This was an all or nothing approach. In SQL Server 2005, you also have the ability to run index rebuilds as well as index defrags. In addition, you can pick specific tables that you need to manage. Although this is not a perfect process for maintaining indexes it is definitely better than not doing anything.

To take this a step further you can manage your index maintenance table by table or index by index. Some indexes will become fragmented while others may never have an issue based on how the index was created and how data is applied to the table/index. Based on this, by doing the across the board method of index management you are spending unnecessary time addressing a problem that does not exist for some the tables. Therefore the best approach would be to use the tools that SQL Server offers such as DBCC SHOWCONTIG and sys.dm_db_index_physical_stats to identify where the real issues exist and then take steps to address these tables and indexes instead of every table and index in your database.

4 Review Error Logs
There are several areas where SQL Server logs information about processes that are occurring as well as errors that occur. The most used is probably the SQL Server Error Log. This error log gives you startup information, integrity check information, backup information, etc… as well as any SQL Server errors that occur. In addition to this log, there is also a log for SQL Server Agent and now in SQL Server 2005 Database Mail. In addition to these internal SQL Server logs you should also use the Windows Event Log to find other errors that may be occurring or possibly additional information that is not in the SQL Server logs.

Reviewing the logs should be part of your daily routine for checking the health of your system. The ideal way to handle this is to use some tool that automates the alert process when there is an error, but either way you should keep these error logs on your radar list as something to review each day.

5 Manage SQL Server Agent Jobs
SQL Server’s built-in job scheduling tool is a great tool for automating your backups, index rebuilds, integrity checks, etc… But in addition to this tool giving you the flexibility to run these jobs during off hours you also need to make sure you are monitoring job success and failure. This can be automated by setting up SQL Mail (SQL 2000) or Database Mail (SQL 2005) and having failures be sent out to operators that are configured.

This is another area I see all the time where there are several jobs that fail not just once or twice but every single time they were run. Take the time on a daily basis to check out the job failures and address the issue so all of your jobs run successfully.

6 Test Backups
A SQL Server backup is only good if the restore works. No matter how many backups you take of your database if you cannot restore the file when needed there is no point in doing backups in the first place. The only way to determine if your backup/restore process will work is to periodically test on another server. This not only gives you peace of mind that the restore was successful, but this also gives you an idea of how long the entire process will take if you ever need to do this on your production server. Having this little insight and the time it will take to recover you database will go along way when you have people breathing down you neck.

In addition to testing, you should also use the RESTORE VERIFY option when creating your backups. It doesn’t necessarily tell you that the restore will not have any issues, but it will at least prove that SQL Server can read the entire backup file without a problem.

7 Monitor Performance
This is one area that should be a no-brainer if you are responsible for monitoring your SQL Server environment. The database is usually the last thing people think about when they are working with an application, but when the application is slow the database is usually the first thing that is blamed for the poor performance.

The problem with performances monitoring is not that most people don’t do this, it is that they are not sure how to do this. Windows and SQL Server offer built in tools such as Performance Monitor, Profiler, Execution Plans, Index Tuning Wizard, Database Engine Tuning Advisor, etc… In addition, there are a whole bunch of third party tools that allow you to trend performance issues and be alerted when there are issues. Without good data it is very difficult to say when there really is a performance issue and also without this data it is difficult to know where to spend your time fixing issues that will have a big impact versus fixing something that will not have a very big impact.

8 Document
Another thing that should be implemented is a documentation process to document procedures, priority lists, escalation lists, production changes, roll out procedures, etc… A good set of procedures should be established, so everyone that works on your SQL Servers understands the processes that you have put in place as well as to document all changes that occur, so when a problem does arise you can pinpoint when a change was made.

A simple text file could be used to track your changes or since we are all database developers/DBAs why not use SQL Server to document and track the changes. This should be one of the simplest things to implement and there is no reason you can start doing this today.

9 Create and Test Disaster Recovery
Plan Disasters don’t strike all that often and because of this disaster recovery plans are usually not implemented. I am sure just about everyone has thought about this at one point in time, but thinking about a disaster recovery plan and implementing a plan are two totally different things.

As a DBA you need to take the time to determine what kind of issues may arise and how to resolve the problem when it does occur. Think about this from a server level, database level and also down to a table level. Once you have determine what you need to do and how you are going to go about resolving the issue take the time to do some tests. You don’t need to test every single server in your environment, but you should try to test each type of failure that you are trying to recover from.

Another thing to put in place is a priority list for your servers and your databases. This way if there are multiple failures that occur you already have a priority list of what needs to be addressed and the order that they need to be dealt with.

10 Manage Security
Security is also another area that is the DBAs responsibility to monitor. As you probably know security levels exist at the Windows server level, SQL Server server level, database level, object level, etc… There are SQL Server server level roles, database roles and user defined roles. Take the time to analyze your permission structure and make the necessary adjustments to ensure people have the rights they need to work, but not additional rights that may cause potential problems. In addition, to securing your database servers, make sure your database backups and any external programs are also secure, so no one can gain backdoor access to your servers or your data.

Next Steps
Now that you have a list of some basis items to address, check off which items you have already implemented and start addressing the other items.
If you are not sure where to begin, start with some of the simpler items like, backups, documenting changes, reviewing error logs and testing backups/restores.

Sunday, May 17, 2009

Deprecated Features – SQL Server 2008


Almost every dba  uses “BACKUP LOG <mydb> WITH TRUNCATE_ONLY or NO_LOG” statement to truncate the database; Basically when you truncate the log files using the above statement(s), the log chain will be broken and I think because of this, Microsoft is planning to discontinue this feature in future editions of the sql server. As an alternative, we should change the recovery model to Simple and bring it back to the previous state.


   2: GO


   4: GO


   6: GO


   8: GO


Thursday, May 7, 2009

More on log shipping

In this post I will follow up on my previous post on log shipping. I did some test and will give you a detailed explanation of how to set it up for SQL 2005, including preparations and 'what to do' to switch from the primary to the backup server. I also looked into the SQL 2000 compatibility and this is bad news: you cannot log ship from SQL 2000 to SQL 2005. It would have been such a nice way to migrate a database from one version to another but it just won't work.

Preparing for log shipping
there are not a lot of preparations to make:

  • both primary and backup server must be part of the same domain or there must be a trust between the domains
  • SQL Server and SQL Server agent should run using a domain account
  • all logins which are used to access the database should be present on the backup database server
  • a share must be available which this domain account has write access to
  • the share must have enough space to store the transaction logs (and optionally a full backup)
  • create a directory per database you want to use in log shipping
  • the database should not yet exist on the backup server
  • check that the primary backup is not part of any other backup / maintenance plan

The last one is not a real requirement but if the database already exists, it should have a very recent full dump of the primary database as its starting point. In practice, it is easier to remove the database and create the full dump and restore in on the backup machine as part of the setup. In that case you do need additional space for the full dump on your share.
The preparation with regards to logins keeps the amount of work when you have to failover to a minimum. If possible, use AD groups as logins because in that case you do not have to worry about finding out which user accounts are needed when things go wrong. You are also advised to use database roles in your database to grant permissions.

Setting it up
To setup mirroring for a database, execute the following steps. You can do everything from a single instance (or, in fact, from any system on which you have SQL Server Management Studio installed):

  • select the source or primary database
  • from the context menu, select tasks | ship transaction logs ...
  • enable this as a primary database in a log shipping configuration
  • click 'backup settings'
  • specify the location for the transaction logs
    • use the share and directory you prepared earlier (preferred)
    • or use a local path (in which case the backup has to pick it up there so this location must be shared also)
  • specify the time frame during which logs are kept online (default: 72h)
  • specify the time frame to send an alert if no backup was made (default: 1h)
  • specify a job name and a schedule (default: LSBackup_[dbname] / every 15 minutes)
  • review and confirm the backup settings


  • click add on the primary log shipping dialog to add at least one backup server
  • use the 'connect' button to connect with the backup server
  • specify the name of the backup database (default: same as primary)
  • specify how to create the backup database
    • let SQL Server generate a full dump and restore that one (preferred)
    • let SQL Server restore the database from an existing full backup
    • or you take care of setting up the initial database
  • go to the 'copy files' tab
  • specify a temporary working location (preferred: local directory)
  • specify the time frame to keep files in the temporary working location (default: 72h)
  • specify a job name and a schedule (default: LSCopy_[source]_[dbname] / every 15 minutes)
  • go to the 'restore transaction logs' tab
  • select database state of the backup database
  • no recovery mode (default)
  • standby mode (preferred; see below)
  • select 'disconnect users to restore log'
  • delay restoring backup at least ... (default: 0 minutes)
  • alert if no restore occurs within ... (default: 45 minutes)
  • review and confirm the backup server settings
  • repeat above for additional servers if necessary


  • (optionally) check 'use a monitor server instance'
  • click 'settings' to specify the monitor server
  • click 'connect' to specify the name of the monitor server
  • specify how to connect to the monitor server
  • use the proxy account (default; will use the SQL Server agent service account)
  • use a specific SQL account (specify name and password)
  • specify how long history information should be retained (default: 96h)
  • specify name of the alert job (default: LSAlert_[monitorserver])
  • review and confirm the monitor server settings


  • (optionally) script and save the complete settings as T-SQL script
  • click 'Ok' to start setting up the log shipping configuration

This might seem like a lot of work but once you have followed this procedure a few times you will see the logic in the various settings. A lot of defaults are very reasonable, so you only have to review them. The amount of work done by the monitor server is very minimal but if you have an additional server available, it makes sense to set it up. It provides a place to keep information about the status of the log shipping.
With regards to the database state: by default it will be set to 'No recovery' which means you cannot look in the backup database. Now, normally you do not want that, you need the backup to take over in case the primary fails. However, the 'standby mode' makes a lot of sense too: you can use the backup system for reporting or you can use it as a dba to verify that the process is working correctly. Trusting SQL Server is all good and so on but I like to see if changes have arrived in the target database and standby mode allows just that.
Last but not least: about the schedules. By default they will be run every 15 minutes at the exact same time. This might give problems because they should be run in sequence. By modifying the start time to 0:00:00 / 0:05:00 / 0:10:00 (backup / copy / restore) you achieve 2 things: the jobs will not interfere with each other and the lag will be shorter.  If you have large backup files you may want to increase the delay before restoring to give the copy process more time to complete.

Failing over
If, at a certain point, the primary server becomes unavailable, you have this nice backup server to replace it. However, there are a number of things you have to do before you can really use this backup server.

  • try to make a last transaction dump from the primary server (if possible)
  • restore any missing transaction dumps
  • either in the last restore or all by itself issue a 'with recovery' command:

    restore database [dbname] with recovery 

  • check / update security
  • change connection strings to point to the new database

If you used AD groups only and assigned them permissions using database roles, there should be no security updates needed. If you used SQL accounts, you have to remember that the internal IDs of those accounts will differ between primary and backup server. You can either use the sp_change_user_login or simpley remove the SQL account from within the [dbname] | security | users and assign the proper roles to the account in security | logins. Usually, the latter is easier.
The connection string is a bit of a problem. If you have a web application, it is not too bad. You only have to fix (probably) one or two places. For client / server applications it pays off to have a central location where the application picks up its connection string info.

My first tests were not an immediate and complete success. In the end, I found out I violated one of my own requirements. I still have to run the whole test once more but I'm pretty confident that solves the problem. I was pleasantly surprised with the ease of use which resulted from the use of AD groups. Still, this is not an ideal solution, stay tuned from an alternative approach.

Inline variable initialization in SQL Server 2008

This is one of the cool features that I like in sql server 2008. We can initialize the variable inline.

DECLARE @i int = 10 ;

Seems to be a simple, but it saves a 'Select /Set' statement

You can initialize the variable with any constant including the @@ROWCOUNT /@@SPID/ SCOPE_IDENTITY() etc.






SELECT @i --- 3








SELECT @i --- 3



DECLARE @testTab Table (i int
identity, j int

INTO @testTab






SELECT @i --- 3


Wednesday, May 6, 2009

How to fix orphaned SQL Server users

When you restore a Microsoft SQL Server database on a different machine, you cannot access the database until you fix the permissions.

The problem is that the user in the database is an "orphan". This means that there is no login id or password associated with the user. This is true even if there is a login id that matches the user, since there is a GUID (called a SID in Microsoft-speak) that has to match as well.

All of these instructions should be done as a database admin, with the restored database selected.

First, make sure that this is the problem. This will list the orphaned users:

EXEC sp_change_users_login 'Report'

If you already have a login id and password for this user, fix it by doing:

EXEC sp_change_users_login 'Auto_Fix', 'user'

If you want to create a new login id and password for this user, fix it by doing:

EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'



Tuesday, May 5, 2009

Return a CSV of the affected records without using OUTPUT


declare @Tab table ( id int
, i int
,j date

into @Tab select 1,

into @Tab select 1,

into @Tab select 1,

into @Tab select 2,

into @Tab select 2,

into @Tab select 1,


declare @res varchar(max)


update @Tab

set @res =
'')+CAST(id as

j =

where i = 1


from @Tab

select @res


Friday, May 1, 2009

Guidelines for enabling indexes and online index operations

Here are some important points that every new DBA should be aware of about enabling indexes and online index operations, few of them are extracted from SQL 2005 Books online and reliable resources:


  • Clustered indexes must be created, rebuilt, or dropped offline when the underlying table contains large object (LOB) data types: image, ntext, text, varchar(max), nvarchar(max), varbinary(max), and xml.
  • Nonunique nonclustered indexes can be created online when the table contains LOB data types but none of these columns are used in the index definition as either key or nonkey (included) columns. Nonclustered indexes defined with LOB data type columns must be created or rebuilt offline.
  • Indexes on local temp tables cannot be created, rebuilt, or dropped online. This restriction does not apply to indexes on global temp tables.
  • The online option setting (ON or OFF) specified when you create or drop a clustered index is applied to any nonclustered indexes that must be rebuilt. For example, if the clustered index is built online by using CREATE INDEX WITH DROP_EXISTING, ONLINE=ON, all associated nonclustered indexes are re-created online also.

Be aware that the online index operation can cause a deadlock during its process, when the same time if there are any insert and/or update operation activities. Thus causes a deadlock victim from the process it is generated.  To avoid this you could take care of concurrent online index DDL operations with non-clustered indexes or reorganizing those indexes too. Also the online index operation is unique and you cannot perform a create index while rebuilding an existing index online on the same table.

Further you must know about disk space requirements in this indexing operation, both for online and offline index operations. An exception is additional disk space required by the temporary mapping index. This temporary index is used in online index operations that create, rebuild, or drop a clustered index. Dropping a clustered index online requires as much space as creating a clustered index online.

When a update activity that is concurrent is persistent then avoid using online index operation at the same time, as it will not create deadlock operation and transction log & tempdb usage will be quite heavy. Few times this will lead to disk free space issues causing a total downtime to the SQL Server services. So be wise to use online index operation as this could cause a decrease in performance and greater resource usage, especially CPU time, during the index operation. Online index operations are fully logged.