You use certain little tips and tricks with the SQL Server tools which are not documented anywhere. This is one of them. I needed to find out the version of a SQL Server instance from which the Profiler Trace was captured. I opened the Profiler trace using SQL Profiler 2008. I clicked on the Trace properties and lo behold I had the SQL Server instance name and version.
I got the SQL Server release, version and the instance name (Trace provider name).
Over the past years, I have seen multiple DBAs execute DBCC CHECKDB with REPAIR ALLOW DATA LOSS to repair the SQL Server database corruption in their database without understanding repercussions. Even when you are working with CSS on such issues, CSS engineers actually explain what the command does and also send across a disclaimer stating why this command should NOT be executed on a database and what the repercussions are. Alas due to a lack of a sound database backup strategy, a lot of DBAs are forced to resort to losing data. In a nutshell, Repair Allow Data Loss tries to first repair the corruption in the page but if it cannot, then it checks if it can drop the page and repair the error and hence the name Allow Data Loss! I had a question for all the DBAs who advocate the use of this command:
How would you like your Payroll DBA to run the command? (Good enough food for thought!!)
The only way to compare how much data you lost would be to perform a CHECKDB before running the CHECKDB with REPAIR ALLOW DATA LOSS and after the CHECKDB has completed. The row counts returned by the two CHECKDB outputs would have to be compared. If CHECKDB is failing due to system catalog corruption, then you are plain out-of-luck.
What most people fail to understand that CHECKDB can verify integrity of the database based on the SQL database rules imposed on it like referential integrity, data length, record structures, page structures, header correctness etc. But these are database integrity rules that we are aware of. What about the business logic rules that you imposed while designing the database.
Eg: Let’s say you had a database table called Employee which depended on a Department ID valued to be present in the Department table. No foreign key relationship exists between the two tables. You experienced corruption in the database and used Repair Allow Data Loss command along with CHECKDB which dropped a few pages from the Department table. However, CHECKDB cannot know that a Department ID is missing in the Department table because you didn’t create a Foreign Key between the two tables. So when your application now tries to retrieve the Department ID of the Employee, it will BARF on you! This is the kind of scenario we specifically want to avoid which is why we advise against the use of data loss commands.
SQL Server wouldn’t know what data is supposed to present in the table rows. The only thing that SQL can verify while you are retrieving data or checking integrity of the database is that the column data and column datatype match. I have heard questions like “Why can’t SQL Server figure out if the value is correct?”. Well, would you know why a person’s name is Uncle Sam and not Uncle Bill? 🙂 So, how would SQL Server know that the FirstName column (an example) was supposed to store Sam and not Bam. As per SQL Server, both strings match the column datatype, hence from a structural and logical integrity, the data is correct.
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! 🙂
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,
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'
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.
TroubleshootingSQL 