Detach and Attach a SQL Server 2008 Analysis Services Database

Detaching an Analysis Service Database Using SSMS


1. Connect to Analysis Service Database Instance using SQL Server Management Studio.


2. In the Object Explorer, expand Databases and then right click the Analysis Service Database and choose the Detach… option from the pop-up menu as shown below. In this example I will be detaching Adventure Works DW database.





3. In Detach Database screen, click OK to detach the Adventure Works DW database.  Note that you can also include a password to encrypt certain data.





Attaching an Analysis Service Database Using SSMS


1. Connect to the Analysis Service Database Instance using SSMS.


2. In the Object Explorer, right click Databases and then select the Attach… option from the pop-up menu. In this example I will be attaching the Adventure Works DW database which we have detached earlier.





3. In the Attach Database screen, you need to specify the folder where Analysis Services DB resides and click OK to attach the database. In addition, you would need to specify the password that was used when you detached the database in the previous step.  Also, if you want to make this read only, select the Read-only check box. 


By default, Analysis Services databases reside in “<drive>:\Program Files\Microsoft SQL Server\<Instance Name>\OLAP\Data\”. In this example, I will be attaching the Adventure Works DW database from “C:\Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\Data\Adventure Works DW.1.db”.





To move an Analysis Database from one server to another you need to move the entire folder where the data exists.  
Also, you need to make sure that SQL Server has the correct folder and file permissions on the new location.

Recover Suspect Database

If a database is marked suspect it cannot be accessed and hence nothing can be performed in the database. In this article I am going to explain how to solve this and bring back the database to normal. Try the below solution

Solution 1:

Step 1: If your database is marked suspect execute the below query,

Use Master

Go

Exec Sp_resetstatus 'Database Name'

The above command will reset the status flag of the suspect database.

Step 2: Once the command is executed you need to restart the SQL services and check the status of the database.

Step 3: Execute the below query to check the integrity of the database

Dbcc Checkdb('Database Name')

WORKAROUND 2:

Step 1: But if you want to avoid any data loss you can bring the suspect database into emergency mode as follows,

Alter Database <Database Name> SET Emergency

This will bring the Database into emergency mode so that you can Export the data using SSIS to another Test server.

Step 2: One you exported you can drop the suspect database and restore it from the latest available backup. Those objects Exported to Test server could be scripted and rerun in the source database after restoring from the backup so that we can minimize the data loss if any.

Find SQL Server data and log files that are almost out of space

On many SQL Servers database file size, either data or log, may be restricted to a maximum size to ensure there is adequate space on the server.  The problem with this is that if your data or log file runs out of space you will get an error message such as the following and your transactions will fail.


Msg 1105, Level 17, State 2, Line 1 Could not allocate space for object 'dbo.table1' in database 'test' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

The general solution I'm suggesting here will use a stored procedure called dbo.usp_get_db_files_near_maxsize. The procedure takes a parameter for space percentage or it will use the default of 10%.  This will then check each file for all databases on the server, including the system databases.

If the SP is run without passing in a parameter it will find all database files, both data and log, that are within 10% of being filled only for files where you have set a maximum size.

Here is the stored procedure and this can be created in master or in your admin database.

CREATE PROCEDURE dbo.usp_get_db_files_near_maxsize (@nearMaxSizePct DECIMAL (5,1) = 10.0)AS
BEGIN
    SET NOCOUNT ON

    CREATE TABLE ##ALL_DB_Files (
                    dbname SYSNAME,
                    fileid smallint,
                    groupid smallint,
                    [size] INT NOT NULL,
                    [maxsize] INT NOT NULL,
                    growth INT NOT NULL,
                    status INT,
                    perf INT,
                    [name] SYSNAME NOT NULL,
                    [filename] NVARCHAR(260) NOT NULL)

    -- loop over all databases and collect the information from sysfiles
    -- to the ALL_DB_Files tables using the sp_MsForEachDB system procedure
    EXEC sp_MsForEachDB
        @command1='use [$];Insert into ##ALL_DB_Files select db_name(), * from sysfiles',
        @replacechar = '$'

    -- output the results
    SELECT
        [dbname] AS DatabaseName,
        [name] AS dbFileLogicalName,
        [filename] AS dbFilePhysicalFilePath,
        ROUND(size * CONVERT(FLOAT,8) / 1024,0) AS ActualSizeMB,
        ROUND(maxsize * CONVERT(FLOAT,8) / 1024,0) AS MaxRestrictedSizeMB,
        ROUND(maxsize * CONVERT(FLOAT,8) / 1024,0) - ROUND(size * CONVERT(FLOAT,8) / 1024,0) AS SpaceLeftMB
    FROM ##ALL_DB_Files
    WHERE maxsize > -1 AND -- skip db files that have no max size
        ([maxsize] - [size]) * 1.0 < 0.01 * @nearMaxSizePct * [maxsize] -- find db files within percentage
    ORDER BY 6

    DROP TABLE ##ALL_DB_Files

    SET NOCOUNT OFF
ENDGO

As a DBA you should run this weekly or even daily to find all database files that are approaching the maximum size limit.  Then it is up to you to fix the problem by adding more space to the file. 

Ideally disk space would not be an issue and you woudl not have to worry about a maximum file size, but even with disk space not costing much there are still some systems that are limited and as a DBA you have to make do with what you have.

Ref: 

http://www.mssqltips.com

OFFSET and FETCH Feature of SQL Server Denali

The OFFSET and FETCH clause of SQL Server Denali provides you an option to fetch only a page or a window of the results from the complete result set. 



Using this feature of SQL Server Denali one can easily implement SQL Server Paging while displaying results to the client.  We will take a look at simple example and then also how you could construct a stored procedure to implement SQL paging.


Let’s go through a simple example which demonstrates how to use the OFFSET and FETCH feature of SQL Server Denali.  You can see below that the TSQL looks the same as what you write today except after the ORDER BY clause we have the OFFSET and FETCH commands.  One thing to note is that you have to use an ORDER BY to use this feature.  The OFFSET basically tells SQL to skip the first 100 rows and the FETCH will get the next 5 rows.

USE AdventureWorks2008R2
GO
SELECT 
  BusinessEntityID
  ,PersonType
 ,FirstName + ' ' + MiddleName + ' ' + LastName 
FROM Person.Person
 ORDER BY BusinessEntityID ASC
  OFFSET 100 ROWS 
  FETCH NEXT 5 ROWS ONLY
GO

The below snippet shows the output when running the above commands.  This shows that the first 100 rows were discarded and the query fetched the next 5 rows in the complete recordset.

Let’s go through another example where we will create a stored procedure which will use the OFFSET and FETCHfeature of SQL Server Denali to achieve sql paging while displaying results to client machines.  In this stored procedure we are passing in a page number and the number of rows to return.  These values are then computed to get the correct page and number of rows.

USE AdventureWorks2008R2
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = 
OBJECT_ID(N'[dbo].[ExampleUsageOfSQLServerDenaliPagingFeature]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[ExampleUsageOfSQLServerDenaliPagingFeature]
GO
CREATE PROCEDURE ExampleUsageOfSQLServerDenaliPagingFeature
 (
  @PageNo INT,
 @RowCountPerPage INT
 )
AS
SELECT
  BusinessEntityID
 ,PersonType
 ,FirstName + ' ' + MiddleName + ' ' + LastName 
FROM Person.Person
 ORDER BY BusinessEntityID
  OFFSET (@PageNo - 1) * @RowCountPerPage ROWS
  FETCH NEXT @RowCountPerPage ROWS ONLY
GO

Let’s go ahead and execute the stored procedure using the below command.  This will give us five records starting at page 21 where the records are ordered by BusinessEntityID.

/* Display Records Between 101 AND 105 BusinessEntityID */
EXECUTE ExampleUsageOfSQLServerDenaliPagingFeature 21, 05
GO

The below snippet shows the output once the above stored procedure is executed successfully. You can see that first 100 (20 pages * 5 rows per page = 100) rows were discarded and the stored procedure fetched only the next 5 rows thereby limiting the number of rows sent to the client.

You have seen in this tip how easily you can achieve SQL Server Paging using the OFFSET and FETCH feature of SQL Server Denali. SQL paging is not as hard as it used to be with this new feature.
Ref:http://www.mssqltips.com

Rectifying Error 8993 in corrupted SQL Server 2008 database

In SQL Server databases is stored in either clustered or non-clustered heaps. In a non-clustered heap, a row always points to another forwarding row to reference data. However, sometimes this behavior is not followed, which is often due to corruption in the MDF (Master Database File) files. Corruption in the MDF files can be due to virus infections, power outages, hardware problems, etc. In such cases, you need to perform MDF recovery using appropriate methods. However, if you are not able to fix the problem then you should use a third-party SQL recovery software to perform MDF file recovery.


Consider a scenario wherein you face the following error message while working on an SQL Server 2008 database.

“Object ID O_ID, forwarding row page P_ID1, slot S_ID1 points to page P_ID2, slot S_ID2. Did not encounter forwarded row. Possible allocation error.”

The root cause of this erroneous situation is that a forwarding row in the heap is referencing to a non-existing forwarded row.

To resolve this issue, you need to recover MDF file. To do this, you should do the following, preferably in the given order:

Resolve hardware issues: You can resolve the hardware issues in the following ways:

By checking the hardware components and the error logs to ascertain that it is a hardware related issue.

By swapping the hardware components to isolate the issue.

By reformatting the hard disks and reinstalling the operating system.

Restore from backup: If it is confirmed that it is not a hardware-related issue, then you should check the database backup. If it is updated and clean, then you should restore the database with its backup.

Run DBCC CHECKDB: If the backup is not updated, then you should run the DBCC CHECKDB command with the suggested repair clause. This command would be able to delete the forwarding row and rebuild the non-clustered indexes.

If the problem is not fixed even after performing the aforementioned methods, then you should use a third-party SQL Server recovery software to recover SQL database. Such SQL recovery tools are read-only in nature and do not overwrite the existing database.