Logical file ‘XYZ_Log2′ is not part of database ‘XYZ’. Use RESTORE FILELISTONLY to list the logical file names.
I was restoring Databases as a routine monthly job. Suddenly in between I came across an error which didn’t let me restore a Database. I restored other Databases and left this one to do at the end. Finally I picked it up again and saw that some new kind of error I was facing. The error message says to use “RESTORE FILELISTONLY”, I check the syntax on MSDN and found that this Database had two log files (LDF) earlier, but now configured for only one. And as I was using the same old script to Restore it, I was getting this error.
Let’s try to reproduce it in a standalone box:
–> I took a backup of AdventureWorks2012 Database on my machine.
I created the RESTORE script and added another line of log file at line 8 below:
USE [master] GO RESTORE DATABASE [AdventureWorks2012] FROM DISK = N'E:\Softwares\MS_bits\AdventureWorks2012.bak' WITH FILE = 1, MOVE N'AdventureWorks2012_Data' TO N'E:\MSSQL11\DATA\AdventureWorks2012_Data.mdf', MOVE N'AdventureWorks2012_Log' TO N'E:\MSSQL11\Log\AdventureWorks2012_Log.ldf', MOVE N'AdventureWorks2012_Log2' TO N'E:\MSSQL11\Log\AdventureWorks2012_Log2.ldf', NOUNLOAD, REPLACE, NOUNLOAD, STATS = 5 GO
On executing I got an error as expected, shown below:
Msg 3234, Level 16, State 2, Line 1
Logical file ‘AdventureWorks2012_Log2′ is not part of database ‘AdventureWorks2012′. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
As suggested in the Error Message, I used the “RESTORE FILELISTONLY” command to check what all files this Database is using:
RESTORE FILELISTONLY from disk=N'E:\Softwares\MS_bits\AdventureWorks2012.bak'
And I could see only two, one MDF & one LDF, as shown below:
LogicalName PhysicalName Type FileGroupName AdventureWorks2012_Data E:\MSSQL11\DATA\AdventureWorks2012_Data.mdf D PRIMARY AdventureWorks2012_Log E:\MSSQL11\Log\AdventureWorks2012_log.ldf L NULL
So, I removed the extra LDF log file option from the RESTORE script, as shown below:
RESTORE DATABASE [AdventureWorks2012x] FROM DISK = N'E:\Softwares\MS_bits\AdventureWorks2012.bak' WITH FILE = 1, MOVE N'AdventureWorks2012_Data' TO N'E:\MSSQL11\DATA\AdventureWorks2012_Data.mdf', MOVE N'AdventureWorks2012_Log' TO N'E:\MSSQL11\Log\AdventureWorks2012_Log.ldf', --MOVE N'AdventureWorks2012_Log2' TO N'E:\MSSQL11\Log\AdventureWorks2012_Log2.ldf', NOUNLOAD, REPLACE, NOUNLOAD, STATS = 5 GO
The above statement executed fine and the Database restored successfully.
So, I learnt a new thing here about the “RESTORE FILELISTONLY” command. This command tell us what all Database files (MDF, NDF, LDF) a Backup file contains without actually Restoring the Database.
Off topic | ERROR: The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)
Today while setting up the DEV Environment as part of Windows Server 2012 & SQL Server 2012 upgrade I faced this error.
I was installing an MSI from one system via a client (MSI deployment tool) to another remote system (on same domain) and was getting this error:
The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)
I checked on internet all possible options to resolve this error, which were:
1. “Remote Procedure Call (RPC)” service should be running on the remote computer.
2. “Windows Management Instrumentation” service should be running on the remote computer.
3. “TCP/IP NetBIOS Helper” service should be running on the remote computer.
4. “DCOM Server Process Launcher” service should be running.
5. File and printer sharing should be enabled, on LAN properties.
I checked all the above options and lot of other suggestions available on internet forums and they were all set correctly.
–> Finally my colleague suggested me to check the “Group Policy Object Editor”, and check the firewall properties “Remote Administration Exception” and “File and Printer Sharing Exception”. And yes he was right, they were not enabled.
So to enable the properties you have to go to “Group Policy Object Editor” by executing gpedit.msc command. A winodw will open, goto: Computer Configuration -> Administrative Templates -> Network -> Network Connections -> Windows Firewall -> Domain Profile:
Here, enable the following properties:
- Allow inbound remote administration exception
- Allow inbound file and printer sharing exception
And when I enabled these two, the MSI deployed successfully without any error!!!
SQLwithManoj now on Windows 8 Store
Dear Readers,
Today I’m very happy to announce the release of Windows 8 App for this blog and its availability on Windows 8 store.
I’ve created this Windows 8 App and published to the Windows 8 Marketplace.
SQLwithManoj Windows 8 App
This is first Windows 8 App developed by me and I faced lot of challenges while developing and deploying it to the Marketplace.
Please download this App on your Windows 8 PC and let me know your feedback.
Thanks!!!
Disable/Enable multiple SQL Jobs at once
Seems to be a simple topic. But yes when it comes to do these type of tasks we tend to recall the syntax and end up searching internet (bing/google) for the solution.
–> Disabling a single SQL Job can be done simply through SSMS. Right click on the SQL Job and select Disable. To enable it back simply select Enable for a disabled Job.
This can also be done by a TSQL query as shown below:
USE msdb;
GO
-- Disable a SQL Job:
EXEC dbo.sp_update_job
@job_name = N'syspolicy_purge_history',
@enabled = 0 ;
GO
select enabled, * from sysjobs where name = 'syspolicy_purge_history'
GO
–> Now if you’ve to Disable Multiple or All the Jobs in SQL Agent, how will you do it?
Selecting All SQL Jobs on SSMS and right clicking won’t give you the Disable option. And here it become more tricky as there is only way to do this by TSQL query. But there is no single TSQL query defined in SQL Server to Disable all SQL Jobs at once. So, we will have to create a Dynamic SQL which will create Script for all SQL Jobs dynamically to Disable each and every SQL Job. Let’s see how:
USE msdb; GO -- Disable Multiple SQL Jobs at once: DECLARE @dynSql NVARCHAR(MAX) = '' SELECT @dynSql += N'exec msdb.dbo.sp_update_job @job_name = ''' + name + N''', @enabled = 0;' + CHAR(10) + CHAR(13) FROM msdb.dbo.sysjobs WHERE enabled = 1 ORDER BY name; PRINT @dynSql;
-- Here is the output of above PRINT statement: exec msdb.dbo.sp_update_job @job_name = 'ExecuteSPuspGetBillOfMaterials', @enabled = 0; exec msdb.dbo.sp_update_job @job_name = 'syspolicy_purge_history', @enabled = 0;
Simple Copy-Paste the the above Dynamically generated SQL Script and Execute it, it will Disable all SQL Jobs at once.
SET STATISTICS IO and TIME – What are Logical, Physical and Read-Ahead Reads?
In my previous post [link] I talked about SET STATISTICS IO and Scan Count.
Here in this post I will go ahead and talk about Page Reads, i.e Logical Reads and Physical Reads.
Type of Reads and their meaning:
- Logical Reads: are the number of 8k Pages read from the Data Cache. These Pages are placed in Data Cache by Physical Reads or Read-Ahead Reads.
- Physical Reads: are the Number of 8k Pages read from the Disk if they are not in Data Cache. Once in Data Cache they (Pages) are read by Logical Reads and Physical Reads do not (or minimally) happen for same set of queries.
- Read-Ahead Reads: are the number of 8k Pages pre-read from the Disk and placed into the Data Cache. These are a kind of advance Physical Reads, as they bring the Pages in advance to the Data Cache where the need for Data/Index pages in anticipated by the query.
–> Let’s go by some T-SQL Code examples to make it more simple to understand:
USE [AdventureWorks2012] GO -- We will crate a new table here and populate records from AdventureWorks's Person.Person table: SELECT BusinessEntityID, Title, FirstName, MiddleName, LastName, Suffix, EmailPromotion, ModifiedDate INTO dbo.Person FROM [Person].[Person] GO -- Let's Clear up the Data Cache or memory-buffer: DBCC FREEPROCCACHE DBCC DROPCLEANBUFFERS GO -- Run the following block of T-SQL statements: SET STATISTICS IO ON SET STATISTICS TIME ON SELECT * FROM dbo.Person SELECT * FROM dbo.Person SET STATISTICS IO OFF SET STATISTICS TIME OFF GO
Output Message: (19972 row(s) affected) Table 'Person'. Scan count 1, logical reads 148, physical reads 0, read-ahead reads 148, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 31 ms, elapsed time = 297 ms. (19972 row(s) affected) Table 'Person'. Scan count 1, logical reads 148, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 16 ms, elapsed time = 186ms.
The Output Message above shows different number of Reads for both the queries when ran twice.
- On first execution of the Query, the Data Cache was empty so the query had to do a Physical Read. But as the optimizer already knows that all records needs to be fetched, so by the time query plan is created the DB Engine pre-fetches all records into memory by doing Read-Ahead Read operation. Thus, here we can see zero Physical Reads and 148 Read-Ahead Reads. After records comes into Data Cache, the DB Engine pulls the Records from Logical Reads operation, which is again 148.
- On second execution of the Query, the Data Cache is already populated with the Data Pages so there is no need to do Physical Reads or Read-Ahead Reads. Thus, here we can see zero Physical & Read-Ahead Reads. As the records are already in Data Cache, the DB Engine pulls the Records from Logical Reads operation, which is same as before 148.
Note: You can also see the performance gain by Data Caching, as the CPU Time has gone down to 16ms from 31ms and Elapsed Time to 186ms from 297ms.
–> At our Database end we can check how many Pages are in the Disk for the Table [dbo].[Person].
Running below DBCC IND statement will pull number of records equal to number of Pages it Cached above, i.e. 148:
DBCC IND('AdventureWorks2012','Person',-1)
GO
The above statement pulls up 149 records, 1st record is the IAM Page and rest 148 are the Data Pages of the Table that were Cached into the buffer pool.
–> We can also check at the Data-Cache end how many 8k Pages are in the Data-Cache, before-Caching and after-Caching:
-- Let's Clear up the Data Cache again: DBCC FREEPROCCACHE DBCC DROPCLEANBUFFERS GO -- Run this DMV Query, this time it will run on Cold Cache, and will return information from the pre-Cache'd data: ;WITH s_obj as ( SELECT OBJECT_NAME(OBJECT_ID) AS name, index_id ,allocation_unit_id, OBJECT_ID FROM sys.allocation_units AS au INNER JOIN sys.partitions AS p ON au.container_id = p.hobt_id AND (au.type = 1 OR au.type = 3) UNION ALL SELECT OBJECT_NAME(OBJECT_ID) AS name, index_id, allocation_unit_id, OBJECT_ID FROM sys.allocation_units AS au INNER JOIN sys.partitions AS p ON au.container_id = p.partition_id AND au.type = 2 ), obj as ( SELECT s_obj.name, s_obj.index_id, s_obj.allocation_unit_id, s_obj.OBJECT_ID, i.name IndexName, i.type_desc IndexTypeDesc FROM s_obj INNER JOIN sys.indexes i ON i.index_id = s_obj.index_id AND i.OBJECT_ID = s_obj.OBJECT_ID ) SELECT COUNT(*) AS cached_pages_count, obj.name AS BaseTableName, IndexName, IndexTypeDesc FROM sys.dm_os_buffer_descriptors AS bd INNER JOIN obj ON bd.allocation_unit_id = obj.allocation_unit_id INNER JOIN sys.tables t ON t.object_id = obj.OBJECT_ID WHERE database_id = DB_ID() AND obj.name = 'Person' AND schema_name(t.schema_id) = 'dbo' GROUP BY obj.name, index_id, IndexName, IndexTypeDesc ORDER BY cached_pages_count DESC; -- Run following Query, this will populate the Data Cache: SELECT * FROM dbo.Person -- Now run the previous DMV Query again, this time it will run on Cache'd Data and will return information of the Cache'd Pages:
Query Output: -> First DMV Query Run: cached_pages_count BaseTableName IndexName IndexTypeDesc 2 Person NULL HEAP -> Second DMV Query Run: cached_pages_count BaseTableName IndexName IndexTypeDesc 150 Person NULL HEAP
So, by checking the above Output you can clearly see that only 2 Pages were there before-Caching.
And after executing the Query on table [dbo].[Person] the Data Pages got Cache’d into the buffer pool. And on running the same DMV query again you get the number of Cache’d Pages in the Data Cache, i.e same 148 Pages (150-2).
-- Final Cleanup DROP TABLE dbo.Person GO
More on MSDN on SET STATISTICS IO : http://msdn.microsoft.com/en-us/library/ms184361.aspx
