If you’ve never read Paul Randal’s blog on the SQLSkills website you should check it out. Paul recently posted a stored procedure that can be used to tell which percentage of a database’s data has changed so you can determine which type of backup to take. The procedure can be scheduled and its output analyzed over time to strengthen your backup and recovery strategy (because we know that you already have one, you just need to improve upon it ).  The cool thing about code like this is that it can be bundled into other maintenance procedures and it can integrate with monitoring and maintenance tools.  For instance, you could run this procedure on a schedule with predefined diff thresholds that would flag the  appropriate type of backup job for execution.  You could then use enterprise monitoring tools like MOM or SCOM to determine the best maintenance windows to guarantee the backups run at the right time, and voila!  You’ve got the beginnings of an automated backup and recovery strategy.

There are two ways you can go about this. One would be to set the offline databases to single user mode:

ALTER DATABASE SET SINGLE_USER

…then back them up. Any users attempting to access the databases will receive the following error:

Server: Msg 924, Level 14, State 1, Line 1
Database '‘ is already open and can only have one user at a time.

Once the backups have run the databases can be taken back offline.

The other way would be to bring the databases online and change the access permissions to deny access to all users but the one used to run the Legato backups.

The approach you take will necessarily depend on your company’s security policies and the reason the databases are offline to begin with. If it were me, I’d try to avoid constantly changing database states and simply address the access rights to the database.

I would bet you’re running into an issue where too many virtual log files (VLFs) have been created for your database. Each physical SQL Server log file is internally divided into a number of VLFs. VLFs have no fixed size, and there is no fixed number of VLFs for a physical log file. SQL Server’s Database Engine chooses the size of the virtual log files dynamically while it is creating or extending log files, but information is available on how this works. VLFs are added as follows when the transaction log file grows:
< 64MB = 4 VLFs
64MB - 1GB = 8 VLFs
> 1GB = 16 VLFs

To understand, in more detail, transaction log architecture read: MSDN Virtual Log Files (SQL Server 2000) or MSDN Transaction Log Physical Architecture (SQL Server 2005)

To see how many VLFs exist in your database issue the [undocumented] DBCC LOGINFO command. The number of rows returned is equivalent to the number of VLFs in your database. The Status column tells you a VLF is still active if the value is 2. Don’t be alarmed when you see the output of DBCC LOGINFO, you can rectify the situation; if you haven’t noticed slowdowns you should still be aware of how your logs are configured and how many VLFs exist in your database. VLFs affect system performance when log files are defined with a small initial size and use small growth_increment values. Too many VLFs can slow down database startup and also log backup and restore operations. To see how following some [egad] proven best practices to ensure good transaction log throughput read: Kimberly Tripp’s 8 Steps to better Transaction Log throughput

In your case you’ll likely have to:
Shrink your transaction log file. In SQL Server 2000 many DBAs probably know that multiple BACKUP LOG or DBCC SHRINKFILE commands were necessary to shrink a transaction log. To understand why and to see how to use DBCC SHRINKFILE in SQL Server 2005 for this condition read: KB Article How to use the DBCC SHRINKFILE statement to shrink the transaction log file in SQL Server 2005
…then address the configured size of your transaction log file. I say file, because there’s little to no benefit to creating multiple transaction log files (read Kimberly Tripp’s 8 Steps article above…). You want to attempt to adequately size the transaction log and allow for an auto-growth rate (to ensure you don’t get transaction log full errors) that will ensure very infrequent growth.  You also want to ensure you understand enough about your workload to ensure log truncation operations don’t burden the system (you don’t want the log truncating every second).

To read a practical case, with examples, of how small increment values and auto-shrink can be detrimental to database performance read: Tibor Karaszi’s Blog on why to be restrictive with DB file shrinking

The goal of a good backup / recovery strategy is consistent data availability in the event of a corruption or disaster. So, while it’s helpful to determine how frequently you need full and differential backups, you definitely need to consider other factors. Database backups are only helpful if they can be restored. Also, where and how you store backups is important. What happens if your production database server goes down, or what if the hosting facility loses power in a flood or earthquake? Sure, not all scenarios apply to everyone, but storage, redundancy and consistency checks are all crucial in ensuring a successful backup / recovery strategy.

Disaster recovery strategies are outside the scope of this post, however leveraging the following SQL Server features can help strengthen your backup / recovery strategy and avoid integrity problems when you least expect them while maintaining data availability:

DBCC CHECKDB
You’ll almost certainly want to use DBCC CHECKDB to find corruptions before they become a problem. Paul Randal wrote CHECKDB for SQL Server 2005, and frequently posts in-depth, yet concise information on SQL Server internals and architecture on his blog. His series entitled CHECKDB From Every Angle tackles many of the topics you’ll need to consider and dispels the myth that you don’t have time for consistency checking.

BACKUP DATABASE…COPY_ONLY
If you’re performance tuning your production database or you have developers that need a copy of production data this option might help. You can create a full database backup without affecting the differential bitmap in the database. Consider this scenario: you perform full database backups every Monday, Wednesday, and Friday, and perform differential backups in between. Your development staff needs a full backup on Tuesday, but you know that doing so will affect the differential bitmap of your database. Enter the COPY_ONLY option. Using Copy-Only Backups lets you keep the developers happy and keep your backup strategy sound.

CREATE DATABASE…AS SNAPSHOT
Auditing production data is an industry in itself, but many shops rely on fast, ad-hoc reporting to satisfy management requests. These same shops often cannot afford to risk allowing ad-hoc statement executions for fear of data being accidentally modified. In these situations, database snapshots offer a good solution. A snapshot is a complete, read-only copy of a database that can be created extremely quickly and that consumes very little space on disk. When data is read from the snapshot, it is actually being read from the primary database. As data changes in your primary database, SQL Server writes the previous data to a sparse file. The snapshot will then read the sparse file for the data that has been changed and continue to read the primary database for data that has not changed.

One final note - Quest’s Litespeed for SQL Server product’s low impact, high-performance compression technology can reduce storage costs (data can be compressed up to 95%) and SQL backup and recovery windows while maintaining complete control over the backup and recovery process. It even supports object-level recovery!! It’s definitely worth a look.

Original question: Does a full backup and restore of a database rebuild table indexes and update statistics? We were seeing some slow query response times for a query running on a production server compared to the test server. We ran profiler trace, looked at execution plans and dbcc showcontig between the two databases and everything is pretty much the same except the same query in production was over 10 times slower than the one ran in the test environment with the same hardware. So in a rather desperate measure we did a full backup and then restore of the production database and now the same query is running about the same time as the test server. Hence my question about table indexes and statistics?

To answer the first part of the question, nothing is updated with a database restore; the database backup saves the current database as an as-is image and the restore restores that image. If you had out-of-date statistics before the backup, you’ll still have them afterwards.

With the information provided, it would appear that your backup/restore operations fixed a physical file (extent) fragmentation problem (see a previous post on fragmentation in SQL Server). Assuming your testing environment was created from a production database backup, you wouldn’t have had the physical fragmentation to contend with in that environment because the extents would have been restored to the file system in physical order.

You’re going to have to create a plan to deal with physical file fragmentation in your production environment. In addition, index maintenance should be planned to deal with logical fragmentation. Microsoft SQL Server 2000 Index Defragmentation Best Practices is a very worthwhile article to read on the topic. Your best bet will be to either run DBCC SHOWCONTIG, or used the information in sys.dm_db_index_physical_stats to determine the extent of logical / extent fragmentation.

One more topic of note. The SQL Server Storage Engine Blog ran a great series on fragmentation explaining SQL Server structures that’s worth a read. Gaining an understanding of these structures will go a long way in better understanding what fragmentation is and how it can affect the various structures in SQL Server.

Question continued…1) When I issue the create file command, will it be log shipped (I think it will)? 2) Will it fail when the drive specification is not replicated on the secondary servers (I think it will)? 3) What will that impact of that be and how can I work around it? 4) How will log-shipping continue when the secondary datafile is not created (or not created the same) as the primary server? And finally 5) What are your recommendations for doing this with minimal interruption.

The situation you’ve encountered is certainly not rare. At 50,000 feet: yes, you can add a file, yes it can be on different paths on different servers, yes the process will be logged, and yes doing so will throw an error. There’s a fifth yes though, and that’s, “Yes, there’s a simple workaround.”

While the process isn’t as “seamless” as Microsoft says it is at the beginning of the article this Microsoft KB article provides the details you’ll need to add a new database file. To sum up what you’re going to see in the article, you’re going to add the new file, SQL Server’s going to throw an error, then you’re going to manually restore the transaction log backup using WITH MOVE. Once you’ve done that, everything really will be seamless.

Q: I have a database DB1 with 250 tables and second database DB2 with 25 tables which are also in DB1 . Now i want to copy data of only 25 same tables from DB1 to DB2 . What is the best way to do this ?? This is done almost daily, so we can’t use import export wizard. I created a secondary filegroup and added tables in that group. Now will take the backup using secondary Filegroup. Is this the right way to solve the problem?

Kevin Kline says: No, I don’t believe your approach is the best solution.

The import/export wizard is the best way to go and is very easy to schedule. The import/export wizard is actually just a tiny subset of the features available in SQL Server Integration Services (SSIS) of SQL 2005 and Data Transformation Services (DTS) in SQL 2000.

Assuming you’re running SQL Server 2000, you should create a simple daily job to move the data over that can be set in a schedule using DTS. You can get started with DTS by reading the content on this URL: http://support.microsoft.com/kb/222073/en-us  

Q: I dont know why and how, all tables which we create now are becoming system tables by default. Actually the scenario is:

I took a backup of a database [test].
i also took an sql script file of the [test] db.
i deleted the [test] db.

then from the sql query analyser i run the script. i dont know how but all tables are becoming system tables.
I dropped the db again and restored the data backup to the [test] db newly created. then its ok but newly created tables are becoming automatically as system tables, system views etc. how to solve the problem.

Bryan Oliver says: I think you problem may be an orphaned user in SQL Server 2005, search for topic in Books Online “Troubleshooting Orphaned Users”. This has a solution for your exact problem without doing any ad-hoc updates to system tables.

If this is not correct, can you resend your question with some more insight into your issue, SQL Server Version, etc?

Q:   My application generates log files rapidly - how do I truncate the size of the log in the SQL server without affecting the data files? This will help me to save space on the hard disk.

Kevin Kline says:   If the application is not mission critical, then run the database in SIMPLE recovery mode. This will automatically truncate the log every minute or so.

If the application is critical and you need to be able to recover from a disaster, then use BACKUP to clear the log at regular intervals, say every 15 or 30 minutes.

Hope this helps.

Technorati Tags:
sql server, data files, database backup, database recovery
Add to: | Technorati | Digg | del.icio.us | Yahoo | reddit |

Q:   I swear I have done this before, but I can’t find out how to save my life. I have a need to attach a database and then execute a large number of inserts/updates. To make things faster, I want to turn off SQL logging. How? If there is a problem then I just start over, so recovery is not an issue. Anything else that would make this process faster? Thanks.

Iain Kick says:   To turn off logging you need to switch the database to simple recovery mode. This can be done using SQL Server Management Studio:

Right click the database once you have attached it -> Properties -> Options -> Recovery Model

or using the following T-SQL script:

USE master;
GO
ALTER DATABASE databasename
SET RECOVERY SIMPLE;
GO

Also, make sure you take a copy of your detatched database files before you attach it or you will be unable to go back.

Technorati Tags:
sql server, sql logging, database recovery, SQL Server Management Studio
Add to: | Technorati | Digg | del.icio.us | Yahoo | reddit |