Why is VSS complaining about SQL Data files

I recently came across a question on why a NTBackup of a Text File on a drive reported the following error in the application event logs:

Event Type:    Error
Event Source:    VSS
Event Category:    None
Event ID:    6005
Description:
Sqllib error: Database %1 is stored on multiple volumes, only some of which are being shadowed.

The interesting part is that the drive that stores the Text file that I am backing up also has a LDF file for a database. The MDF file of the same database resides on another drive. The intelligent SQL Writer was able to find out that only one file for the database in question is on this drive and the other file(s) is not present on the same drive. Hence, it reported the error causing confusion in our minds. A red cross in any log file is a BAD thing!! Not in this case. 🙂

I have already blogged about the behavior of NTBackup/VSS when a Snapshot is created in a previous post.

The conclusion is that if you have database files residing on multiple drives and you are using VSS to backup non-SQL database files on only one of these drives, then you can ignore the above error (if that is the only error). This is yet again another of the infamous by-design behaviors! 🙂

Find FullText Search settings information for all catalogs & databases (in a jiffy)

Good morning folks – Ever worked on full text search in SQL Server and found it frustrating due to lack of information when anything goes bad & you are left poking around on what information to collect. Here is a script that you can use to collect Full-text (FTS) information for catalogs in ALL your databases. This will tell you the following,

  • Catalog Name & catalog ID
  • Item count
  • Population Status
  • Is Change Tracking enabled or not
  • Last Crawl – was it FULL, Incremental or UPDATE. Also gives you time of last crawl
  • Path of catalog storage location
EXEC sp_msforeachdb 'IF EXISTS (select * from ?.sys.fulltext_catalogs) BEGIN PRINT ''In Database: ?'' SELECT cat.name AS [CatalogName], cat.fulltext_catalog_id AS [CatalogID], FULLTEXTCATALOGPROPERTY(cat.name,''LogSize'') AS [ErrorLogSize], FULLTEXTCATALOGPROPERTY(cat.name,''IndexSize'') AS [FullTextIndexSize], FULLTEXTCATALOGPROPERTY(cat.name,''ItemCount'') AS [ItemCount], FULLTEXTCATALOGPROPERTY(cat.name,''UniqueKeyCount'') AS [UniqueKeyCount], FULLTEXTCATALOGPROPERTY(cat.name,''PopulateStatus'') AS [PopulationStatus], tbl.change_tracking_state_desc AS [ChangeTracking], tbl.crawl_type_desc AS [LastCrawlType], tbl.crawl_start_date AS [LastCrawlSTARTDate], tbl.crawl_end_date AS [LastCrawlENDDate], ISNULL(cat.path,N'''') AS [RootPath], CAST((select(casewhen exists(select distinct object_id from sys.fulltext_indexes fti where cat.fulltext_catalog_id = fti.fulltext_catalog_id and OBJECTPROPERTY(object_id, ''IsTable'')=1)then 1 else 0 end)) AS bit) AS [HasFullTextIndexedTables] FROM ?.sys.fulltext_catalogs AS catLEFT OUTER JOIN ?.sys.filegroups AS fg ON cat.data_space_id = fg.data_space_id LEFT OUTER JOIN ?.sys.database_principals AS dp ON cat.principal_id=dp.principal_idLEFT OUTER JOIN ?.sys.fulltext_indexes AS tbl ON cat.fulltext_catalog_id = tbl.fulltext_catalog_id PRINT '''' END'

How to check database statistics last updated date/time & Percent Rows modified in SQL Server

Well, here goes my first post – I will keep it simple. As a DBA you’ve probably heard people talking about statistics ALL the time. You probably have jobs/maintenance plans setup to update statistics. But if you ever ran into an issue with slow query performance and wanted to do a quick check on the statistics for ALL indexes in the database, you would use either dbcc show_statistics or sp_autostats. But they work per index/table. Here is a way to do this for the entire database using TSQL query,

— For SQL 2005/SQL 2008

select a.id as 'ObjectID', isnull(a.name,'Heap') as 'IndexName', b.name as 'TableName',
stats_date (id,indid) as stats_last_updated_time
from sys.sysindexes as a
inner join sys.objects as b
on a.id = b.object_id
where b.type = 'U'

Note: Rowmodctr values displayed above are not entirely reliable starting with SQL 2005 onwards. But, you can rely on last updated time to make a decision if the statistics need to be updated.
Read here for more

Many of you might have lot of tables or have large databases where you only want to update statistics using ‘UPDATE STATISTICS’ or ‘sp_updatestats’, but only for those tables that have large row modifications. Here is a script that you can use to get the output of each index that has significant row modifications. You can pipe this to a temp table and choose to update statistics on only these tables. You can do this by looking at the “ModifiedPercent” column.

select
schemas.name as table_schema,
tbls.name as table_name,
i.name as index_name,
i.id as table_id,
i.indid as index_id,
i.rowmodctr as modifiedRows,
(select max(rowcnt) from sysindexes i2 where i.id = i2.id and i2.indid < 2) as rowcnt,
convert(DECIMAL(18,8), convert(DECIMAL(18,8),i.rowmodctr) / convert(DECIMAL(18,8),(select max(rowcnt) from sysindexes i2 where i.id = i2.id and i2.indid < 2))) as ModifiedPercent,
stats_date( i.id, i.indid ) as lastStatsUpdate
from sysindexes i
inner join sysobjects tbls on i.id = tbls.id
inner join sysusers schemas on tbls.uid = schemas.uid
inner join information_schema.tables tl
on tbls.name = tl.table_name
and schemas.name = tl.table_schema
and tl.table_type='BASE TABLE'
where 0 < i.indid and i.indid < 255
and table_schema <> 'sys'
and i.rowmodctr <> 0
and i.status not in (8388704,8388672)
and (select max(rowcnt) from sysindexes i2 where i.id = i2.id and i2.indid < 2) > 0

— For SQL 2000

SELECT 'Index Name' = i.name, 'Table Name' = o.name,
'Statistics Date' = STATS_DATE(i.id, i.indid)
FROM sysobjects o, sysindexes i
where o.id = i.id

You can put this in a cursor for every database if you want to and play around with it. Will post back another script which will help you decide what criteria you want to pick when updating indexes. Stay tuned….

Addition: February 4, 2012: An updated version of the script is available here.

Initial Data Collection Script

Where would you be able to use it?

The cases where you can use this script effectively are:

1. Performance issues where a quick initial look at the general state of the server is very valuable.

2. Situations where issue is actually happening right now. However, that said, it is always good to collect post mortem data as well for further diagnostics. Performance Dashboard is a good option here but if you want a snapshot of the server and store it somewhere, then this script can help you.

3. Situations where the problem is happening and you need to quickly verify that you are not hitting some known issues.

How is this different from the other scripts that are already existing?

There are scripts already out there which do the same thing. The advantage here is the fact that the output is in HTML format with bookmarks which makes it easy to determine what information is available. Also, it is easy to get to the sections that are of interest in the current scenario.

Usage

  1. Download the script using the link given at the bottom of the page and save it to a file named INITIAL_DIAG.SQL. Open the script file in a SSMS Query Window.
  2. Press CTRL+SHIFT+F so that the output results are put into a file. Doing this will not produce a message or any other notification.
  3. Execute the script and specify INITIAL_DIAG.html as the output file name so that we can get the output in the require HTML format.

Sample Output

********************************************************************************************
INITIAL MICROSOFT PSS DATA COLLECTION
********************************************************************************************

INDEX
1. General Server Configuration
a. Server Info
b. Non-default sp_configure settings
c. Server Settings
d. Active Trace Flags
e. Profiler Trace Information
2. Memory Diagnostics
a.DBCC MEMORYSTATUS output
b.Memory Clerks rollup output
3.Procedure Cache Statistics
4.Database Diagnostics
a.Replicated Databases
b.Database File Usage Stats
c.Index Statistics
d.sp_helpdb output
e.DBCC SQLPERF (LOGSPACE)
f.Hypothetical Indexes
5.Missing Indexes report
6.Blocking Information
7.Batch/Query Statistics

########################################

****************** SERVER INFO *********
########################################

—————————————————————–
Microsoft SQL Server 2005 – 9.00.3257.00 (X64)
Jun 12 2008 16:47:07
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.0 (Build 6001: Service Pack 1)

—————————————————————
SQL SERVER NAME: SERVER1

CLUSTERED: No

MACHINE NAME: SERVER1
NET BIOS NAME: SERVER1

SQL PID: 5076
FTS Installed: YES
Security Mode: MIXED
Current SPID: 51

Click here to download the script.

Modification: June 20, 2012: Added additional code to account for the following:

  • Added code to identify read-write workload ratios
  • Added code to find out non-Microsoft DLLs loaded in SQL address space using sys.dm_os_loaded_modules
  • Added code to get output for SQL Server 2012 instances

How to troubleshoot Windows Internal Database setup issues

Windows Internal Database is bundled along with Windows applications that need to use a backend database solely for their application purpose. Eg: WSUS, Sharepoint.

The application’s installation files will call the MSI package for installing the Windows Installer Database. In case the application setup fails due to a failure in installing the Windows Internal Database, then you would need the MSI log file for the WID MSI package. This can be found normally in the <System Drive:>\Windows folder and would have the following naming convention: *WSSEE*.log. If you are unable to locate the file, then find out all *.log files in the folder which were created recently.

Once you have located the file, you would need to search for the following string “Return Value 3” in the file.

Eg:

MSI (s) (E0:88) [16:02:14:252]: Product: Windows Internal Database — Error 1402. Could not open key: <Reg Key Name>.  System error 5.  Verify that you have sufficient access to that key, or contact your support personnel.

Error 1402. Could not open key: <Reg Key Name> System error 5.  Verify that you have sufficient access to that key, or contact your support personnel.
Action ended 16:02:14: InstallFinalize. Return value 3.

If you check what Operating System error 5 stands for, you will find out that it is a permissions issue.

C:\>net helpmsg 5

Access is denied.

Based on the error message, you will have to take corrective actions and re-run the setup.