Microsoft SQL server provides three recovery models; full, simple, and bulk-logged. Each have their benefits. The details of the three recovery models is outlined below.
You will need to choose the best option for your scenario. For a production database that you need to be able to recover from, the best option will generally be the full recover model.
Full recovery model:
The full recovery model maintains the entire transaction log history up until the point where the database log file is backed up. The major benefit of the full recovery model, is that you have the ability to restore the database to a specific point in time. For example, you can restore back to 4:23pm, even if a full backup was not taken at that time.
If using the full recovery model, you will need to make sure the transaction logs are backed up reguarly as to prevent the log growing too big and filling up all free space on the server.
Simple recovery model:
The simple recovery model still has a transaction log file, however it will only store transactions for a short period of time, and cant be used to restore to a specific point in time. With the simple recovery model, you can only restore to a database backup file, at the time the backup was performed.
Bulk-Logged recovery model:
The bulk-logged recovery model is similar to the full recovery model, but it handles bulk data (for example, a data import) in a different way. When dealing with bulk data, instead of keeping track of every transaction in the log file, it will simply log the end result after the bulk transactions have occured. This is known as ‘minimal logging’. This helps reduce the impact that logging may have on the performance of the server.
With this model, you wont be able to restore to a specific point in time. You will only be able to restore back to the end of the transaction log backup.
Ideally, you would use this model on an as needed basis. It provides better protection in a disaster over the simple recovery model, however it lacks behind the full recovery model by not being able to recover to a specific point in time.
Sometimes you may encounter data integrity issues with one or more tables on a Microsoft SQL server. These errors may be found during a data integrity check using DBCC CHECKDB, or even via general use of the database. There are 3 options to correct the database integrity issues; safe repair, restore, and non safe repair (in that order).
Its always good practice to take an extra backup before any of these commands are run (specifically the non-safe repair option).
Safe repair using REPAIR_REBUILD:
Change the database to single user mode.
ALTER DATABASE <database_name> SET SINGLE_USER
Repair the database using a safe repair that will not cause data loss (if possible).
DBCC CHECKDB ('<database_name>', REPAIR_REBUILD)
Change the database to back to multi user mode.
ALTER DATABASE <database_name> SET MULTI_USER
Replace <database_name> with the name of your database.
Restore the database from a backup:
If the above safe repair does not work, the best option is to restore the database from a backup. Only if a database backup is not available, look at the next option, which is a non-safe repair.
Non-Safe repair using REPAIR_ALLOW_DATA_LOSS:
This option is non-safe as it will repair the database, by simply removing the data associated with the integrity errors. This option will fix the integrity errors, however it may remove needed data at the same time. Only perform this option if there the safe repair doesn’t work, and there is no good backup.
ALTER DATABASE <database_name> SET SINGLE_USER
Repair the database using a non-safe repair that will potentially cause data loss.
DBCC CHECKDB ('<database_name>', REPAIR_ALLOW_DATA_LOSS)
Change the database to back to multi user mode.
ALTER DATABASE <database_name> SET MULTI_USER
Replace <database_name> with the name of your database.
Microsoft SQL server 2008 has the ability to automatically shrink database files. By default this option is disabled. With automatic shrinking of database files turned on, the SQL server will periodically check the available free space within the database files, and will automatically shrink the files if needed.
If your database has heavy use, it might be best to keep this option disabled to prevent performace degration.
To check what the current auto shrink setting:
SELECT DATABASEPROPERTYEX('<database_name>', 'IsAutoShrink')
Replace <database_name> with the name of your database.
This will output a 1 if auto shrink is enabled, or a 0 if it is disabled.
To enable automatic file shrinking:
ALTER DATABASE <database_name> SET AUTO_SHRINK ON
Replace <database_name> with the name of your database.
To disable automatic file shrinking:
ALTER DATABASE <database_name> SET AUTO_SHRINK OFF
Replace <database_name> with the name of your database.
The Microsoft SQL database server supports the use of Triggers on its tables. Triggers are a special kind of stored procedure that can be executed when records in a table are either inserted, updated, or deleted.
Continue Reading…
The Firebird database engine supports the use of Triggers on its tables. Triggers are basically a set of SQL scripts that can be executed when data in a table is either inserted, updated, or deleted.
Sometimes you may want to temporarily disable a trigger without completely removing it. The steps to do this, and enable the trigger again, are outlined below.
Disable Trigger:
ALTER TRIGGER <trigger name> INACTIVE;
Enable Trigger:
ALTER TRIGGER <trigger name> ACTIVE;
Replace <trigger name> with the name of the trigger you wish to disable or enable.
Refreshing All Views After Table Structure Changes in Microsoft SQL
July 13, 2012 Database, Microsoft SQLAfter making structure changes to a database table, you need to update any views that may be using those tables, otherwise the views may reference the wrong field, and cause all sorts of issues.
The T-SQL script below will refresh all views on a database.
Run this in a new query window in Microsoft SQL Server Management Studio.
Make sure the database you wish to refresh is selected.
DECLARE @ViewName VARCHAR(256) DECLARE cViews CURSOR READ_ONLY FOR SELECT name from sys.views OPEN cViews FETCH NEXT FROM cViews INTO @ViewName WHILE @@FETCH_STATUS != -1 BEGIN EXEC SP_REFRESHVIEW @ViewName PRINT 'View ''' + @ViewName + ''' has been refreshed.' FETCH NEXT FROM cViews INTO @ViewName END CLOSE cViews DEALLOCATE cViews
Microsoft SQL Server allows you to add multiple triggers on a specific table. This is a great feature of Microsoft SQL Server, however one of the issues that can catch people out is in the order that the triggers will ‘fire’. By default, the triggers will run in the order that they were created/added to the database (typically, but not guaranteed). SQL Server does provide a way to set the order of ‘AFTER’ triggers (to an extent), but it must be done using T-SQL statements as opposed to using GUI tools in Management Studio.
Microsoft SQL Server only allows you to set which triggers fires first, and which fires last. This is great if you only have 3 or fewer triggers on a table, but if you have more than 3, you will end up with 2 or more in the middle part, which will end up always running in an undefined order (typically in the order that they were created, but there are no guarantees).
The ‘sp_settriggerorder’ stored procedure allows the setting of the trigger order.
The syntax for this stored procedure is:
sp_settriggerorder 'trigger_name', 'order', 'trigger_type'
| Parameter | Description |
| trigger_name | The name of the trigger that you want to set the order for. |
| order | The order that you want to set it as. The valid options are ‘first’ or ‘last’ (or ‘none’ to clear the ordering). |
| trigger_type | The statement type of trigger, eg ‘insert’, ‘update’, or ‘delete’. |
An example of setting the ‘auditCustomers’ trigger to fire first:
sp_settriggerorder 'auditCustomers', 'first', 'insert'
An example of setting the ‘CopyAddressToInvoice’ trigger to fire last:
sp_settriggerorder 'CopyAddressToInvoice', 'last', 'insert'
Keep in mind that if you ever drop and recreate a trigger, the ordering information will get deleted, and you will need to run the ‘sp_settriggerorder’ statement again.
Minimizing the time it takes for an MS SQL query to run is important when creating an application or website. You can always add more resources on the SQL server, such as RAM and CPU, but that doesn’t really fix the source of the issues, especially if the there are large volumes of SQL transactions. Microsoft SQL Server has the ability to display statistics on queries, to determine the CPU time, and elapsed time of an SQL statement.
You can use these statistics as a guide to determine how long each query may take, and then try and optimize the query, and check the statistics again. Inefficient stored procedures, views with complex joins, recursive triggers, etc, can all potentially increase the query time drastically.
A sample of what the performance statistics information looks like:
SQL Server Execution Times: CPU time = 0 ms, elapsed time = 293 ms.
To turn statistics on, execute the following SQL statement in a SQL Management Studio query window.
SET STATISTICS TIME ON
Now after running each SQL query, you can check the ‘Messages’ window/tab (instead of the ‘Results’ window/tab), to see how long in milliseconds it took to run the query.
To turn statistics off, execute the following SQL statement in a SQL Management Studio query window.
SET STATISTICS TIME OFF
If you want to just enable the statistics for a single query, you can set the statistics to be on, and then off again straight after the query.
For example:
SET STATISTICS TIME ON SELECT * FROM vProducts ORDER BY ProductCode DESC SET STATISTICS TIME OFF
This will output something similar to the following in the ‘Messages’ window/tab.
(163 row(s) affected) SQL Server Execution Times: CPU time = 0 ms, elapsed time = 293 ms.
'TRUNCATE_ONLY' is not a recognized BACKUP option in SQL 2008
January 27, 2012 Database, Microsoft SQLIn Microsoft SQL server 2000 and 2005, as part of the ‘BACKUP LOG’ command, there was an option to truncate the log file, without storing a backup to file. In Microsoft SQL Server 2008, this option has been removed, and you can now no longer use the ‘TRUNCATE_ONLY’ option when performing a transaction log backup.
The truncate only option is used to truncate the log file. This is generally done so you can then shrink the transaction log file, and recover the disk space back to the file system.
The error message that occurs when you try the ‘TRUNCATE_ONLY’ option in Microsoft SQL 2008 is as follows:
Msg 155, Level 15, State 1, Line 1 'TRUNCATE_ONLY' is not a recognized BACKUP option.
To truncate the transaction log file in Microsoft SQL Server 2008, without making an actual transaction log backup (possibly due to free space limitations), you need to change the recovery model of the database to “Simple”, and then change it back to “Full” (or “Bulked Logged” if that’s what it was previously).
In the process of making the database recovery model “Simple”, it truncates the transaction log file, as it is not used with the simple recovery model.
This can be done manually via the Microsoft SQL Server Management Studio tool, or it can be done via SQL statements, and scripted if needed.
SQL statements to change the database model of a specific database:
ALTER DATABASE [<database_name>] SET RECOVERY SIMPLE ALTER DATABASE [<database_name>] SET RECOVERY FULL
After doing the above, you can shrink the transaction log file using the following command:
DBCC SHRINKFILE(<log_name>)
For more information on shrinking the transaction log file, please check out the “How to shrink a Microsoft SQL transaction log file” article.
The transaction log file contains a record of every transaction against the database, since the last transaction log backup. All changes in the database are first saved to the transaction log, before being updated in the main data file.
The transaction log file can allow a database administrator to recover a database back to a specific point in time, rather than simply back to the last full backup. If you find there was some sort of database corruption at 4:30pm, you could recover the database back to 4:29pm using the transaction log file.
Because of these abilities, the transaction log file can actually grow over time, and become excessivly big, and potentially filling up the hard disk space on the machine that Microsoft SQL is running on.
Shrinking the transaction log file required two parts. First of all you need to truncate the log file itself, and then secondly you need to shrink the file itself. The truncate part clears the contents of the file, but it doesn’t actually reduce the file size of the transaction log on the file system.
To truncate/clear out the transaction log, you need to perform a transaction log backup. If for some reason you are not able to do a transaction log backup (usually due to the size/disk space restrictions), you can perform a “TRUNCATE_ONLY” backup, which just clears/truncates the transaction log without creating a backup file.
To do a “truncate only” backup, you would run the following query:
BACKUP LOG [<database_name>] WITH TRUNCATE_ONLY
(Unfortunatly, since Microsoft SQL Server 2008, the TRUNCATE_ONLY options has been deprecated. Instead you need to change the database recovery model to “Simple”, and then set it back to “Full”. This has the same effect as a “TRUNCATE_ONLY” transaction log backup. For more details, check the “‘TRUNCATE_ONLY’ is not a recognized BACKUP option in SQL 2008” article).
Once that has been done, you can actually shrink the transaction log file, which will reclaim the free space from within the file itself, and let the filesystem use it again. This will typically shrink it back down to about 1MB in size.
Run the following script to shrink the transaction log file:
DBCC SHRINKFILE(<log_name>)
Note: you can run “SELECT * FROM SysFiles” to determine what the log name should be. This will be in the “name” column. It has typically got a _LOG tag appended to the name.
Or, for a more automated approach, this will automatically get the log file details, and shrink it (Make sure you have truncated the log file first).
DECLARE @LogFile varchar(256) SELECT @LogFile=name from Sysfiles WHERE UPPER(RIGHT(filename, 4)) = '.LDF' DBCC SHRINKFILE(@LogFile)
For more information on what the transaction log file is, please read the “What is the SQL transaction log file?” article.