Dave's Technophorical Times

A blog about Microsoft's Technologies!
SharePoint :: MVC :: ASP.NET :: IIS :: SQL Server :: Visual Studio :: MS Access

From here: https://mstechtalk.com/fix-recovery-pending-state-sql-server/

Solution 1 worked like a chalm!

States of SQL Server Databases

If a single or multiple core files cannot be accessed in SQL server, it means that the SQL server database is corrupted. As per the measure of damage in the database, there are different states of SQL database. Some of them are explained below:

Online: If a single file is damaged and cannot be accessed, the database will remain available and online.

Suspect: In case, the transaction log file is damaged and it is creating obstructions on recovery or preventing transaction rollback from completion, it will result in failure of SQL database.

Recovery Pending: This state usually occurs when the SQL server knows that recovery of the database is to be done, but something is creating hindrance before starting it. This state is different from the suspect state as it cannot be declared that database recovery will fail, but it has not started yet.

The state of a database can be known after executing the following query statement:

SELECT name, state_desc from sys.databases
GO

Reasons Behind Recovery Pending State in SQL Server

Once a reason is known, finding a solution becomes a lot easier. When SQL database requires recovery and it cannot be started, it is known as pending state. This situation arises when:

  • The database is not cleanly shut down. In other words, one or more unfulfilled transaction is active at that time and the transaction log file has been deleted.
  • Users attempted to transfer the transaction log files to a new drive to solve performance issues. But, this may leads to corruption of log files.
  • Due to inadequate memory space or disk storage, database recovery cannot be started.

Workaround to Fix Recovery Pending State in SQL Server

There are 2 different manual methods to initiate SQL database recovery, which is marked in the recovery pending state. Both the solutions are described below.

Solution 1: Mark SQL Database in Emergency Mode and Start Forceful Repair

Follow these steps to resolve SQL server database issue:

  1. Execute these queries to fix SQL server database in recovery pending state:
  2. ALTER DATABASE [DBName] SET EMERGENCY;
    GO
    ALTER DATABASE [DBName] set single_user
    GO
    DBCC CHECKDB ([DBName], REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;
    GO
    ALTER DATABASE [DBName] set multi_user
    GO

  3. EMERGENCY mode marks the SQL database as READ_ONLY, deactivates logging, and gives the permission to system admin only
  4. This method is capable of resolving any technical issue and bringing the database back to the accessible state. The database will automatically come out of the EMERGENCY mode.

Solution 2: Mark SQL Database in Emergency Mode, Disconnect the Main Database and Attach it Again

Execute these commands to fix recovery pending state in SQL server:

ALTER DATABASE [DBName] SET EMERGENCY;
ALTER DATABASE [DBName] set multi_user
EXEC sp_detach_db ‘[DBName]’
EXEC sp_attach_single_file_db @DBName = ‘[DBName]’, @physname = N'[mdf path]’

These query statements will help the server to get corruption-free log and create a new one automatically.

Warning: Before performing any manual solution on the SQL server database, make sure that the proper backups of the data are created. In case, any error occurs, the data should still be available. Also, the manual approach is to be performed only when a user has a deep technical knowledge on the topic. If a user is unsure and the steps are not clear, another approach is explained in the next section.

Alternative Solution to Fix Recovery Pending State in SQL Server Database

No doubt, the manual solution to repair SQL server database is an effective technique, but it has to be performed with utmost care. Additionally, there are other disadvantages of the method too. So, it is recommended to use an automated tool like SQL Database Repair Software. According to the technical experts, it is the professional method to repair recovery pending state in SQL server 2012.

Final Words

A SQL server database is a lot more complex and difficult to handle. There are many glitches which occur in SQL database, and they have to be resolved as soon as possible. One such problem is a recovery pending state in SQL server. The multiple manual solutions have been described in the post to fix recovery pending state in SQL server database. Moreover, a third-party tool is also introduced to resolve the problem in a hassle-free manner.

    



From here: https://microsoft.github.io/AzureTipsAndTricks/blog/tip140.html

Note by cbattlegear One important caveat to this process (as shown below). If any writes are happening on the database while you do the export the import will be broken. Best practice is to run CREATE DATABASE AS COPY to create a copy of the database and create an export of the copy.

Right-click on the Database -> click Tasks > Export data-tier application

Now ensure you are connected to your local target SQL server instance (or SQL Azure instance) and right-click on the Database -> click Tasks > Import data-tier application and select the .backpac file that you created earlier. 



From here: https://stackoverflow.com/questions/19077356/how-to-determine-what-fields-were-update-in-an-update-trigger

create trigger utr_Table1_update on Table1
after update, insert, delete
as
begin
    with cte_inserted as (
        select id, (select t.* for xml raw('row'), type) as data
        from inserted as t
    ), cte_deleted as (
        select id, (select t.* for xml raw('row'), type) as data
        from deleted as t
    ), cte_i as (
        select
            c.ID,
            t.c.value('local-name(.)', 'nvarchar(128)') as Name,
            t.c.value('.', 'nvarchar(max)') as Value
        from cte_inserted as c
            outer apply c.Data.nodes('row/@*') as t(c)
    ), cte_d as (
        select
            c.ID,
            t.c.value('local-name(.)', 'nvarchar(128)') as Name,
            t.c.value('.', 'nvarchar(max)') as Value
        from cte_deleted as c
            outer apply c.Data.nodes('row/@*') as t(c)
    )
    insert into Table1_History (ID, Name, OldValue, NewValue)
    select
        isnull(i.ID, d.ID) as ID,
        isnull(i.Name, d.Name) as Name,
        d.Value,
        i.Value
    from cte_i as i
        full outer join cte_d as d on d.ID = i.ID and d.Name = i.Name
    where
        not exists (select i.value intersect select d.value)



These are som items that need to be considered/done when upgrading from MS Access backend to SQL Server backend.

1. Replace # with single quote (') for Dates in SQL.
2. Add Timestamp to all SQL tables.
3. Add dbSeeChanges to all DAO CurrentDB.Execute SQL statements. dbFailOnError should become dbSeeChanges + dbFailOnError
4. Convert all MulitValue fields to flat fields. This usually like multi-select combo baoxes that have to be converted to Listboxes.
5. Convert all Attachment Fields to VarBinary subTables!



QUESTION:

We are using the Bamboo Data-View Web Part in SharePoint 2013 to allow users to filter and export results from a single Library and I have just discovered that the Filter is hidden in a column when you have a Multiple Lines of Text in the grid. This makes the tool pretty useless now so I was hoping that someone has come across a better Web Part?

ANSWER:

So the answer is that Multi-line Text fields in SharePoint are NOT searchable or Filterable, end of story. I have found a solution that works. This solution is to keep a SQL Server table update to date with the library data, less the documents of course, and use a nvarchar(4000) column for the multi-line text fields. We will never exceed that number of characters so this works great plus it's much faster than rendering a SharePoint Library!



https://stackoverflow.com/questions/7892334/get-size-of-all-tables-in-database 

Is there an easy way to determine how much space on disk each table is consuming?

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    t.Name



From here: https://support.microsoft.com/en-ca/help/214459/info-optional-feature-not-implemented-error-message

I got this error when running the code via MS Access VBA.

When you use ActiveX Data Objects (ADO) to pass parameters to a stored procedure, you may receive the following error:

Run-time error '2147217887 (80040e21)':
[Microsoft][ODBC SQL Server Driver] Optional Feature Not Implemented.

This error can occur if you attempt to set the TYPE of a parameter in an ADODB command object's parameters collection to a type that is not supported by the data provider.

For example, using SQL Server 7.0, create a stored procedure on the PUBS database:

   CREATE PROCEDURE GetEmployeeInfo (@thedate datetime, @NumEmployees int output)AS 
SELECT @NumEmployees = count(*) FROM EMPLOYEE WHERE hire_date < @thedate
GO

This stored procedure returns an output parameter of type int indicating the number of employees hired before a given date. The date is passed to the stored procedure as a parameter, and the number of employees is passed to the calling program as an output parameter.

Now create an ADO application to use the stored procedure. The example given is written in Visual Basic.

Private Sub MySubroutine()
Dim dbConnection As ADODB.Connection
Dim dbCommand As ADODB.Command

Set dbConnection = New ADODB.Connection
Set dbCommand = New ADODB.Command

Dim DSNNAME As String
Dim USERNAME As String
Dim PASSWORD As String

DSNNAME = "Pubs"
USERNAME = "sa"
PASSWORD = ""

dbConnection.Open DSNNAME, USERNAME, PASSWORD
dbCommand.ActiveConnection = dbConnection

Dim TheDate As Date
TheDate = Now

dbCommand.CommandText = "GetEmployeeInfo"
dbCommand.CommandType = adCmdStoredProc
dbCommand.Parameters.Append dbCommand.CreateParameter("@thedate", adDBDate, adParamInput, 0, TheDate)
dbCommand.Parameters.Append dbCommand.CreateParameter("@NumEmployees", adInteger, adParamOutput, 0)
dbCommand.Execute

Dim strTheString As String
strTheString = "There are " & dbCommand.Parameters("@numemployees") & " employees who were hired before " & TheDate
MsgBox strTheString, vbOKOnly, "Demonstration"
End Sub

When the sample code is run, it gives this error:

Run-time error '2147217887 (80040e21)':
[Microsoft][ODBC SQL Server Driver] Optional feature not Implemented.

This is because SQL Server does not support the adDBDate datatype. To correct this problem, change the datatype of the @theDate parameter to adDBTimeStamp.

In order to determine the number, names, types, and sizes of parameters needed in a stored procedure, use the Parameters.Refresh method of the command object. You can call this method during development of your application to determine the correct requirements for the stored procedure, then remove the expensive call to Parameters.Refresh after you have gathered the necessary data.



CREATE TABLE #counts
(
    table_name varchar(255),
    row_count int
)

EXEC sp_MSForEachTable @command1='INSERT #counts (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?'
SELECT table_name, row_count FROM #counts ORDER BY table_name, row_count DESC
DROP TABLE #counts

 

The output will be a list of tables and their row counts.

If you just want the total row count across the whole database, appending:

SELECT SUM(row_count) AS total_row_count FROM #counts

will get you a single value for the total number of rows in the whole database.



SELECT 
    DB_NAME(dbid) as DBName, 
    COUNT(dbid) as NumberOfConnections,
    loginame as LoginName
FROM
    sys.sysprocesses
WHERE 
    dbid > 0
GROUP BY 
    dbid, loginame


This issue that I am talking about is a very common issue that I have seen. I have, in my experience, seen many users experiencing this issue; yet, most people do not know how to troubleshoot or fix this issue. Moreover, I have seen this issue on SQL Server 2005; although not tested, I am sure you might encounter this issue in SQL Server 2008 as well.

The issue is, you right-click on the database and choose properties. Now, instead of the database properties opening up, you get the message:

Cannot show requested dialog. (SqlMgmt)
Property Owner is not available for Database ‘[DBName]’. This property may not exist for this object, or may not be retrievable due to insufficient access rights.

You do not face this issue with all databases; you can, in fact, see the properties of some of the databases and face this issue with some of the databases

One reason for this message, is obviously due to insufficient rights. But, say, you are logged in into the SQL Server using a sysadmin account, yet you face this issue. What might be the reason? Correct, the reason is that there is no owner for this database.

If you run the command “sp_helpdb Affected_Datababase_Name“, you would see that the “owner” property would show a NULL value. This issue can happen if the owner of the database is dropped from the Security Logins of the SQL Server. 

 

To fix the issue, run the following command against the affected database:

sp_changedbowner [ @loginame = ] ‘login’
                             [ , [ @map= ] remap_alias_flag ]

Permissions: Requires TAKE OWNERSHIP permission on the database. If the new owner has a corresponding user in the database, requires IMPERSONATE permission on the login, otherwise requires CONTROL SERVER permission on the server. [From Books Online] 

Again, according to the Books Online:


  • After sp_changedbowner is executed, the new owner is known as the dbo user inside the database. The dbo has implied permissions to perform all activities in the database.

  • The owner of the master, model, or tempdb system databases cannot be changed.

  • To display a list of the valid login values, execute the sp_helplogins stored procedure.

  • Executing sp_changedbowner with only the login parameter changes database ownership to login and maps the aliases of users that were previously assigned to dbo to this new database owner.

I hope this post is useful.

from: https://blogs.msdn.microsoft.com/suhde/2009/04/05/property-owner-is-not-available-for-database-dbname/



The Blogger

Dave Stuart I'm a Developer with a passion for coding. I enjoy the challengers that come with the job! SharePoint is one of my expert areas along with .NET Web Development with MVC and good old MS Access VBA coding. I Blog so that I can remember how I did that way back when; PLUS all this stuff is searchable! I constantly study and run my own business, Dafran Inc. I have passed 22 Microsoft Exams since 1998 when I first jumped on the treadmill of knowledge. I hope that you enjoy this Blog as much as I enjoy updating it. All the very best from Calgary, Alberta, Canada. contact me at linkedin @ dafran.ca

Calendar

<<  July 2020  >>
MoTuWeThFrSaSu
293012345
6789101112
13141516171819
20212223242526
272829303112
3456789

View posts in large calendar

Sign in