Error : Msg 3117, Level 16, State 4 The log or differential backup cannot be restored because no files are ready to rollforward

Error : Msg 3117, Level 16, State 4 The log or differential backup cannot be restored because no files are ready to rollforward

I restored a Full backup and then tried restoring a transaction log with NORECOVERY and thats how i obtained this error. And that exactly is the issue.

Inorder for trn logs to be restored, full backup restore should have been done with NORECOVERY.

Perform it that way and restores work like charm.

MvM

Cannot connect to .Login failed for user ''. Reason: Server is in script upgrade mode. Only administrator can connect at this time.

TITLE: Connect to Server
------------------------------
Cannot connect to MYINSTANCE.
------------------------------
ADDITIONAL INFORMATION:
Login failed for user 'MY_LOGIN'. Reason: Server is in script upgrade mode. Only administrator can connect at this time.

Error 18401


This is something which I faced when I was trying to connect to my SQL Server instance, just after applying SQL Server 2008 R2 SP2+CU2. I was a little puzzled as this was the first time when I faced this issue.

Just to let you know, this isnt a big deal. This has already been reported as a BUG.

https://connect.microsoft.com/SQLServer/feedback/details/416860/error-18401-after-starting-service-after-successfull-sp1-install

I havent had done anything specific other than the normal run for the patch installation.

1. Apply SP2
2. Reboot
3. Apply CU2.
4. Check SSMS

ISSUE & CAUSE:
***************
SP installation completes successfully but some of the scripts (Ex: sqlagent100_msdb_upgrade.sql) in the SP will be applied only after the SQL Server service starts the next time. These scripts were getting applied to the instance and I was trying to connect to the instance during that time. This caused the error mesage to pop up.

RESOLUTION:
*************
Patience is the only solution to this, as you have to wait some time for the scripts to be applied completely and after this has been done, any one whos authorized to connect to the DB will be allowed to connect as normal.

I did the same and that worked !!!

MvM

Files Are Not Deleted From Recycler Folder

I have encountered an issue today where the drive was filling up. I deleted some old backups from the drive and still there were some hidden files which were taking up space. when i checked, it was the RECYCLER folder. This was keeping some of the very old deleted files, which i had to delete manually and release space to the drive. Here below is the link to the MS site about this and how to delete the contents of this folder.

http://support.microsoft.com/kb/229041

To resolve this issue, delete the files from an MS-DOS prompt. To do this, follow these steps:


1.Click Start, click Run, type cmd.exe in the Open box, and then click OK.

2.Change to the drive and folder where you deleted the files. For example if you deleted a file from the C:\Windows folder, type cd\windows at the C: prompt, and then press ENTER.

3.From that folder type cd recycler, and then press ENTER.

4.From the Recycler folder type dir, and then press ENTER. You may see some UserSID folders where SID is the security ID for each user who deleted files in that folder.

5.Type cd userSID, and then press ENTER.

6.Type del *.*, and then press ENTER. If you receive an error message that indicates some files are open, quit all the programs running on your computer.

7.Type cd.., press ENTER, and then repeat steps 5-7 for each folder in the Recycler folder.

8.Type exit, and then press ENTER.

MvM

Cumulative update package 3 for SQL Server 2008 R2 Service Pack 2

Build 10.50.4266.0 (cumulative update)


This cumulative update package was released on October 15, 2012.

The parent build of this build is 10.50.4263.0.

For more information about this build, click the following article number to view the article in the Microsoft Knowledge Base:

2754552 http://support.microsoft.com/kb/2754552

Cumulative update package 3 for SQL Server 2008 R2 Service Pack 2

SQL Server 2008 R2 SP2 CU2 fails - sqlagtctr.dll

I have faced isssues with few systems now regarding the DLL 'sqlagtctr.dll'

====== From C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log ======


Final result: The patch installer has failed to update the following instance: MSSQLSERVER. To determine the reason for failure, review the log files.

MSSQL10_50.MYSERVER-sqlagtctr.dll. ---> Microsoft.SqlServer.Configuration.Sco.ScoException: The process cannot access the file 'C:\Windows\system32\perf-MSSQL10_50.MYSERVER-sqlagtctr.dll' because it is being used by another process. ---> System.IO.IOException: The process cannot access the file 'C:\Windows\system32\perf-MSSQL10_50.MYSERVER-sqlagtctr.dll' because it is being used by another process.

MSSQL10_50.MYSERVER-sqlagtctr.dll. ---> Microsoft.SqlServer.Configuration.Sco.ScoException: The process cannot access the file 'C:\Windows\SysWOW64\perf-MSSQL10_50.MYSERVER-sqlagtctr.dll' because it is being used by another process. ---> System.IO.IOException: The process cannot access the file 'C:\Windows\SysWOW64\perf-MSSQL10_50.MYSERVER-sqlagtctr.dll' because it is being used by another process.

With workarounds from Microsoft sites, I have renamed these DLL’s and have completed the upgrade successfully.

http://blogs.msdn.com/b/karthick_pk/archive/2012/07/16/the-process-cannot-access-the-file-c-windows-system32-perf-mssql10-50-mssqlserver-sqlagtctr-dll-because-it-is-being-used-by-another-process.aspx

http://social.msdn.microsoft.com/Forums/en-US/sqlsetupandupgrade/thread/06fc5c42-9d5d-4331-a4b9-210559411eee

https://connect.microsoft.com/SQLServer/feedback/details/576412/-the-process-cannot-access-the-file-c-windows-syswow64-perf-mssql10-50-mssqlserver-sqlagtctr-dll-because-it-is-being-used-by-another-process

A similar issue to the one I mentioned above, that might occur with upgrading of cluster resources during SP2 install. (http://msdn.microsoft.com/en-US/library/hh995349(v=sql.15).aspx)


2.2 Setup Might Fail if SQAGTRES.dll Is Locked by Another Process

Issue: A SQL Server setup operation might fail with this error: Upgrading of cluster resource C:\Program Files\Microsoft SQL Server\MSSQL10_50.\MSSQL\Binn\SQAGTRES.DLL on machine failed with Win32Exception. Please look at inner exception for details. The root cause is that C:\Windows\system32\SQAGTRES.DLL is locked by another process and Setup was not able to update it.

Workaround: Rename C:\Windows\system32\SQAGTRES.DLL to a temporary name such as C:\Windows\system32\SQAGTRES_old.DLL, and then select the Retry option on the setup error message. That will allow Setup to continue. After a reboot, you can delete the temporary file C:\Windows\system32\SQAGTRES_old.DLL.

MvM

Msg 7311, Level 16, State 2, Line 1 Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI10" for linked server " ". The provider supports the interface, but returns a failure code when it is used.

I was trying to run a job from a SQL Server 2008 R2 64bit Test machine which connects to a 32bit SQL Server 2000 Production machine through a Linked Server. When I tried running the query after creating the Linked Server, it throwed up the following error message.

OLE DB provider "SQLNCLI10" for linked server "FOCUS" returned message "Unspecified error".

OLE DB provider "SQLNCLI10" for linked server "FOCUS" returned message "The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.".

Msg 7311, Level 16, State 2, Line 1

Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI10" for linked server "FOCUS". The provider supports the interface, but returns a failure code when it is used.

Since I had ran this query directly on PROD, I was pretty sure that the query works fine. Then I found the link from MS site while googling.

http://support.microsoft.com/kb/906954

Then I found that it basically needs a stored procedure (sp_tables_info_rowset_64) to fix the issue between 32bit and 64bit. The complete procedure is available in multiple sites. But the fix only requires few lines of it. The code is listed below.

create procedure sp_tables_info_rowset_64

@table_name sysname,

@table_schema sysname = null,

@table_type nvarchar(255) = null

as

declare @Result int set @Result = 0

exec @Result =sp_tables_info_rowset @table_name, @table_schema, @table_type

go

MvM

Restore nightly backups script

I am used to getting requests from users to restore Test databases with that of PROD. This could be ad-hoc or on schedule. The below script takes inputs as DB Name, Prod Backup location and backupfile name and restores the Test db. I have made modifications to suit my requirement.

----------------------------------------------------------------------------------------

-- Restore database from a backup file

-- NOTE: If the database gets locked in Single user mode, execute

-- the below command on it and it will restore the Multiuser mode:

-- ALTER DATABASE yourdatabasename SET MULTI_USER

-- Modify the @databaseName, @restoreDirectory & @backupFileName as per your requirement.

---------------------------------------------------------------------------------------

USE master

GO

-- Declare the variables

DECLARE @backupFileName varchar(100),

@restoreDirectory varchar(100),

@databaseDataFilename varchar(100),

@databaseLogFilename varchar(100),

@databaseDataFile varchar(100),

@databaseLogFile varchar(100),

@databaseName varchar(100),

@execSql nvarchar(1000)

-- Set the name of the database to restore

SET @databaseName = 'YOUR_DATABASE_NAME'

-- Set the path to the directory containing the database backup file.

-- IMPORTANT:: Make sure there is a \ character at the end of your directory path as below;

SET @restoreDirectory ='LOCATION OF BACKUPFILE' -- such as '\\NETWORK\BKUP\DB\'

-- Create the backup file name based on the restore directory, the database name and today's date & time.

-- The formatting has been done as per my backup file requirement.

SET @backupFileName = @restoreDirectory + @databaseName +'_backup_'+convert(varchar,getdate(), 112)+'.bak'

PRINT @backupFileName

-- Get the data file and its path

SELECT @databaseDataFile =RTRIM([Name]),

@databaseDataFilename =RTRIM([Filename])

FROM master.dbo.sysaltfiles AS files

INNER JOIN master.dbo.sysfilegroups AS groups

ON files.groupID = groups.groupID

WHERE dbid =

(

SELECT dbid

FROM master.dbo.sysdatabases

WHERE [Name] = @databaseName

)

-- Get the log file and its path

SELECT @databaseLogFile = rtrim([Name]),

@databaseLogFilename = rtrim([Filename])

FROM master.dbo.sysaltfiles AS files

WHERE dbid =

(

SELECT dbid

FROM master.dbo.sysdatabases

WHERE [Name] = @databaseName

)

AND

groupID = 0

PRINT 'Killing active connections to the "'+ @databaseName + '" database'

-- Create the sql to kill the active database connections

SET @execSql = ' '

SELECT @execSql = @execSql +'kill '+convert(char(10), spid)+ ' '

FROM master.dbo.sysprocesses

WHERE db_name(dbid)= @databaseName

AND dbid<> 0

AND spid <> @@spid

-- EXEC (@execSql)

PRINT 'Restoring "'+ @databaseName +'" database from "'+ @backupFileName + '" with '

PRINT ' DATA file "'+ @databaseDataFile +'" located at "'+ @databaseDataFilename +'"'

PRINT ' LOG file "'+ @databaseLogFile +'" located at "'+ @databaseLogFilename + '"'

SET @execSql = 'restore database [' + @databaseName + ']

from disk = ''' + @backupFileName + '''

with

file = 1,

move ''' + @databaseDataFile +''' to '+''''+ @databaseDataFilename + ''',

move ''' + @databaseLogFile +''' to '+''''+ @databaseLogFilename + ''',

norewind,

nounload,

replace'

PRINT @execsql

EXEC sp_executesql @execSql

---------------End of Restore script---------------------------------------

MvM

Biggest Tables in a database - SQL Server 2000

The below mentioned script is only for SQL Server 2000 and I already have posted a simpler script to find the same from SQL 2005/2008.

declare @id int

declare @type character(2)

declare @pages int

declare @dbname  sysname

declare @dbsize dec(15,0)

declare @bytesperpage dec(15,0)

declare @pagesperMB dec(15,0)

create table #spt_space

( objid int null, rows int null, reserved dec(15) null,

data dec(15) null, indexp dec(15) null, unused dec(15) null)

set nocount on

-- Create a cursor to loop through the user tables

declare c_tables cursor for

select id

from sysobjects

where xtype = 'U'

open c_tables

fetch next from c_tables into @id

while @@fetch_status= 0

begin

/* Code from sp_spaceused */

insert into #spt_space(objid, reserved)

select objid= @id, sum(reserved)

from sysindexes

where indid in (0, 1, 255) and id = @id

select @pages =sum(dpages)

from sysindexes where indid < 2and id = @id

select @pages = @pages +isnull(sum(used), 0)

from sysindexes where indid = 255 and id = @id

update #spt_space

set data = @pages

where objid= @id

/* index: sum(used) where indid in (0, 1, 255) - data */

update #spt_space

set indexp =(selectsum(used)

from sysindexes where indid in(0, 1, 255) and id = @id)- data

where objid= @id

/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */

update #spt_space

set unused = reserved-(selectsum(used)

from sysindexes where indid in(0, 1, 255) and id = @id)

where objid= @id

update #spt_space

set rows= i.rows

from sysindexes i

where i.indid < 2 and i.id = @id and objid= @id

fetch next from c_tables into @id

end

select top 25

Table_Name = (select left (name,25) from sysobjects where id =objid),

rows = convert(char(11), rows),

reserved_KB = ltrim(str(reserved * d.low / 1024.,15,0)+' '+'KB'),

data_KB = ltrim(str(data * d.low / 1024.,15,0)+' '+'KB'),

index_size_KB = ltrim(str(indexp * d.low / 1024.,15,0)+' '+'KB'),

unused_KB = ltrim(str(unused * d.low / 1024.,15,0)+' '+'KB')

from #spt_space, master.dbo.spt_values d

where d.number = 1

and d.type= 'E'

order by reserved desc

drop table #spt_space

close c_tables

deallocate c_tables

MvM

Database Restore History

USE msdb;

GO

Select top 20

DBRestored= destination_database_name,

SourceFile= physical_name,

RestoreDate= restore_date

from RestoreHistory h

inner join BackupSet b

on h.backup_set_id = b.backup_set_id

inner join BackupFile f

on f.backup_set_id = b.backup_set_id

order by RestoreDate desc

GO

MvM

List Indexes and their size

SELECT OBJECT_NAME(i.OBJECT_ID)AS TableName,

i.name AS IndexName, i.index_id AS IndexID,

(8 *SUM(a.used_pages))/1024 AS 'Indexsize(MB)'

FROM sys.indexes AS i

JOIN sys.partitions AS p ON p.OBJECT_ID= i.OBJECT_ID AND p.index_id = i.index_id

JOIN sys.allocation_units AS a ON a.container_id = p.partition_id

GROUP BY i.OBJECT_ID, i.index_id,i.name

ORDER BY OBJECT_NAME(i.OBJECT_ID), i.index_id

MvM

List all indexes & their included columns in a database

SELECT '['+Sch.name+'].['+ Tab.[name]+']' AS TableName,

Ind.[name] AS IndexName,

SUBSTRING ((SELECT', '+ AC.name

FROM sys.[tables] AS T

INNER JOIN sys.[indexes] I ON T.[object_id] = I.[object_id]

INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.[object_id]

AND I.[index_id] = IC.[index_id]

INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id]

AND IC.[column_id] = AC.[column_id]

WHERE Ind.[object_id] = I.[object_id]

AND Ind.index_id = I.index_id AND IC.is_included_column = 0

ORDER BY IC.key_ordinal

FOR XML PATH('')), 2, 8000) AS KeyCols,

SUBSTRING ((SELECT', '+ AC.name

FROM sys.[tables] AS T

INNER JOIN sys.[indexes] I ON T.[object_id] = I.[object_id]

INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.[object_id]

AND I.[index_id] = IC.[index_id]

INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id]

AND IC.[column_id] = AC.[column_id]

WHERE Ind.[object_id] = I.[object_id] AND Ind.index_id = I.index_id

AND IC.is_included_column = 1

ORDER BY IC.key_ordinal

FOR XML PATH('')), 2, 8000) AS IncludeCols

FROM sys.[indexes] Ind

INNER JOIN sys.[tables] AS Tab ON Tab.[object_id] = Ind.[object_id]

INNER JOIN sys.[schemas] AS Sch ON Sch.[schema_id] = Tab.[schema_id]

ORDER BY TableName

MvM

List larger tables in a database

I have tried this query only on sql 2008. This will extract all tables with number of rows, reserved,data,index and unused space in MB.

select

name =object_schema_name(object_id)+'.'+object_name(object_id),

rows =sum(case when index_id < 2 then row_count else 0 end),

reserved_MB = 8*sum(reserved_page_count)/1024,

data_MB = 8*sum(case when index_id<2

then in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count

else lob_used_page_count + row_overflow_used_page_count end)/1024,

index_MB = 8*(sum(used_page_count)-sum(case when index_id<2

then in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count

else lob_used_page_count + row_overflow_used_page_count end))/1024,

unused_kb = 8*sum(reserved_page_count-used_page_count)

from sys.dm_db_partition_stats

where object_id> 1024

group by object_id

order by rows desc;

MvM