2009 | SansSQL

Thursday, December 3, 2009

Undocumented DATE and TIME functions in SQL

There are some Undocumented Date and time functions that are available in SQL.
These functions are listed below.
Run these functions and check their results, they are self-explanatory.
select {fn current_date()}
select {fn current_time()}
select {fn now()}
select {fn extract(hour from getdate())}
select {fn extract(minute from getdate())}
select {fn extract(second from getdate())}
select {fn extract(day from getdate())}
select {fn extract(month from getdate())}
select {fn extract(year from getdate())}
select {fn dayname(GetDate())}
select {fn monthname(GetDate())}
select {fn month(GetDate())}
select {fn year(GetDate())}

Sunday, November 15, 2009

How to Backup Analysis Service Database

An Analysis Service database can be backed up using SSMS or XMLA Query Editor.
Backup using SSMS:
1. Login to SSAS instance using SSMS
2. Right-Click on the Analysis service database you want to backup.
3. And Choose the option Backup.
4. Now in the Backup Database page, Enter the location to where you want the backup file to be placed.
5. Choose the other options, if required.
6. And Click on OK to backup the database.
Backup Using XMLA Query Editor:
1. Open the XMLA Query Editor using the New Query Option.
2. Enter the below query.
 <Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> 
3. Replace the DatabasesID with the Analysis Service database name which you want to backup.
4. And replace the File to where(location on disk) you want the analysis service database to be backed up to.
5. Execute the Query.

Saturday, November 14, 2009

Backup Compression in SQL Server 2008

Backup Compression is a new feature in SQL Server 2008 that is introduced in SQL Server 2008. This new feature significantly reduces the backup and restore times as well as the size of the backups.
By default, backup compression is turned off on the server level.

Using this feature, the size of the backups can be kept small and the Backup and the restoration time will also be reduced.

To change the default configuration of Backup Compression, execute the below query

EXEC sp_configure 'show advanced options', 1

EXEC sp_configure 'backup compression default', 1

Use the Server Properties

1.       1. Go to the “Server Properties”.
2.       2. Select “Database Settings
3.       3. Click on the Check box “Compress Backup”.
4.       4. Click OK.

To compress the backups, follow the below steps

1.       1. Right-Click on the database which you want to backup.
2.       2. And Select Tasks
3.       3. Under Tasks Menu, Click on the “Back up” Option

4       4. In the backup window, select the “Backup type” and add the Destination.

1.       5. Now go to the “Options” page and select the Compression Options.
There are 3 options under compression
a.       Use the Default Server Setting
b.      Compress Backup
c.       Do not Compress Backup

The option “Use the Default Server Setting” will use the value that is set in server level.
By default, it will not compress the backups. But in our case the default server settings has been changed at the first, so the backups will be compressed.

The option “Compress Backup” will compress the backups and the option “Do not Compress Backup” will not compress the backups.

Now select either option “Use the Default Server Setting” or “Compress Backup” as both options will compress the backup because of the changes done to the server level in the beginning.

.       6. Click OK.
.       7. In the similar way take the backup of the same database setting the Compression option to “Do not  Compress Backup” and check the size difference of the both the backups.
You will find that the size of the compressed backup will be less.

To download the detailed test cases and testing results click here.

This article is also available in pdf format for downloading.
Please Click here to get your copy.

Friday, November 6, 2009

Prevent Saving Changes in SQL 2008

Prevent Saving Changes is a new option that is introduced in SQL Server 2008. This option helps in preventing the unexpected changes that can happen on a table which includes dropping and re-creating the table.
By default, Prevent Saving Changes Option will set to ON in SQL Server 2008.

When you change a table so that you alter the metadata structure of the table, and then you save the table, the table must be re-created based on these changes. This may result in the loss of metadata and in a direct loss of data during the re-creation of the table. If you enable the Prevent saving changes that require the table re-creation option in the Designer section of the SQL Server Management Studio (SSMS) Options window, you receive the below error message

Saving changes is not permitted. The changes that you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created.

Create a table named Tbl_PreventSavingChanges
            1. Expand the Database AdventureWorks.
            2. Right-Click on the Tables and select New Table

            3. Save the Table

Now change the data type of the column ChangingColumn from nchar(10) to nvarchar(10)

And click on save button and notice an error popping out

Now to overcome this error, first click on cancel button and come out of the error message.
            1. Go to "Options" under the "Tools" menu

            2. In the options, expand “Designers” and select “Table and Database Designers

            3. Now, Uncheck the option “Prevent saving Changes that require table re-creation

            4. Click OK
            5. Save the table. Now you will be able to save the table.

This article is also available in pdf format for downloading.
Please Click here to get your copy.

Wednesday, October 28, 2009

Blocked Process Report in SQL 2005 and SQL 2008

Blocked Process Report is a new concept that was introduced in SQL server 2005 onwards. It is an enhancement added to the SQL server profiler.

The Blocked Process Report event class in SQL Server profiler indicates that a task has been blocked for more than a specified amount of time. This event class does not include system tasks or tasks that are waiting on non deadlock-detectable resources.

How to Use:-
To configure the threshold value for Blocked Process Report, use sp_configure.
At first we have to enable the advanced configuration options.
This can be done using the below query

EXEC sp_configure 'show advanced options',1

Then configure the Blocked Process Threshold value. This value will be in seconds. The below query sets the “Blocked Process Threshold” value to 5 seconds.
By default the Threshold value will be 0.

EXEC sp_configure 'blocked process threshold',5

To generate the report, use profiler.

1. Start the Profiler
2. Connect to the server
3. In the Trace Properties, go to Event Selection tab and check the option “Show all events

4. In the events column, expand the “Errors and Warnings” group and select the event “Blocked Process Report

5. Click on run.
6. Now to test the setup, go to SQL Query Editor and run the below query.

USE AdventureWorks
UPDATE HumanResources.Shift SET Name='NewShift' WHERE Name='Night'

7. Now go to a new session or open a new SQL Query Editor and run the below Query.

SELECT * FROM HumanResources.Shift

Since the Begin Tran from the step 6 is still not got the COMMIT or ROLLBACK, this command will be blocked until the COMMIT or ROLLBACK is executed

8. Now go to the profiler and notice that you should have got an event by name “Blocked Process Report” in RED Color.

9. Now click on the Blocked Process Report event to get the detailed Message / Report.

10. In the report it shows 2 sections, one is for the Blocked Process and the other one is for the Blocking Process.
11. Now Execute the below command to release the locks.

This article is also available in pdf format for downloading.
Please Click here to get your copy.

Saturday, October 24, 2009

Central Management Server

A SQL Server CMS is just a central repository that holds a list of managed servers. SQL Server 2008 SSMS introduces a new feature, Multiple Server Query Execution, in Query Editor. Centralize the management and administration of a number of SQL Server instances from a single source can allow the DBA to save significant time and effort. This feature intends to increase the productivity of running same query against multiple servers at once. Some of usage includes:
• Configure group of servers
• Generate report or document from multiple servers
• Analyze result from multiple servers
• Run any SQL query against multiple servers

1. SQL Server 2008 for Registering Central Management Server
2. SQL Server 2008 or SQL Server 2005 or SQL Server 2000 servers other than the registered Central Management Server.

To deploy or test Multiple Server Query Execution, you need to setup central management server in SSMS in SQL Server 2008.

How to:
To register a central management server and run the query against all the registered servers follow the below steps.

1. Open the “Registered Servers” from the “View” Menu in the management studio of SQL server 2008.

2. Right click on the Central Management Servers and select “Register Central Management Server”

3. Then register the SQL 2008 server.

4. Create the sub folders and register the required servers.

5. To run a query against all the servers, right click on the central management server and select “New Query”

6. In the Query editor, type your query and execute it.
Select SERVERPROPERTY('ProductVersion') AS 'Version',
SERVERPROPERTY('ProductLevel') AS 'Level',
SERVERPROPERTY('Edition') AS 'Edition'

7. In the results, you can notice that for each record the respective server name will be displayed.

This article is also available in pdf format for downloading.
Please Click here to get your copy now.

Sunday, October 11, 2009

DBCC DBREINDEX in next versions

DBCC DBREINDEX rebuilds one or more indexes for a table in the specified database.
This was the command which we used to rebuild the indexes.
This feature will be removed in the next version (after 10.0 or SQL Server 2008) of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use Alter INDEX command to reorganize or rebuild indexes instead.
Below query would reorganize the table if table fragementation is between 10.0 to 30.0 and Rebuild the index if table fragementation is above 30.0

use DB_name
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
    object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
INTO #Temp
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL,'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

OPEN partitions
WHILE (1=1)
        FETCH NEXT
           FROM partitions
           INTO @objectid, @indexid, @partitionnum, @frag;
        IF @@FETCH_STATUS < 0 BREAK;
        SELECT @objectname = QUOTENAME(o.name), @schemaname =QUOTENAME(s.name)
        FROM sys.objects AS o
        JOIN sys.schemas as s ON s.schema_id = o.schema_id
        WHERE o.object_id = @objectid;
        SELECT @indexname = QUOTENAME(name)
        FROM sys.indexes
        WHERE  object_id = @objectid AND index_id = @indexid;
        SELECT @partitioncount = count (*)
        FROM sys.partitions
        WHERE object_id = @objectid AND index_id = @indexid;
        IF @frag < 30.0
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' +@schemaname + N'.' + @objectname + N' REORGANIZE';
        IF @frag >= 30.0
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' +@schemaname + N'.' + @objectname + N' REBUILD';
        IF @partitioncount > 1
            SET @command = @command + N' PARTITION=' +CAST(@partitionnum AS nvarchar(10));
        EXEC (@command);
        PRINT N'Executed: ' + @command;
CLOSE partitions;
DEALLOCATE partitions;

Note: Rebuilding a clustered index does not rebuild associated non-clustered indexes unless the keyword ALL is specified

When ALL is specified, all indexes on the table are dropped and rebuilt in a single transaction.
USE DB_name;
ALTER INDEX ALL ON schema.table_name

This piece of information was shared to me by Kumaravyas. 
Thanks to Kumaravyas for sharing this useful info.