Problem
While troubleshooting a SQL Server cluster failover issue, it is essential to know the time needed for the cluster failover and the node name where SQL Server was running before the failover occurred. In this tip, I will show you the different options to find the failover time and node name where SQL Server was running before the failover over.
When designing tables, It is a good practice of having one column that is unique and can be a primary key. You can include one of the below type column as Primary Key
– Add Auto Increment Column
– Identify the column which unique for all the rows
Make sure Primary keys should be as small as necessary. Prefer a numeric data type because numeric types are stored in a much more compact format than character formats. Most primary keys will be foreign keys in another table as well as used in multiple indexes. The smaller your key, the smaller the index, the less pages in the cache.
You can execute below script to identify the tables without Primary Key and add the Primary Key into tables as per the above suggestions..
Use <Database Name> SELECT SCHEMA_NAME(schema_id) AS [Schema Name], name AS [Table Name] FROM sys.tables WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasPrimaryKey') = 0 Order by name GO
Script to determine the identity tables and columns in the databases.
USE <DBNAME> --script to determine the table with the identity column select name, OBJECTPROPERTY(id, 'TableHasIdentity') AS TableHasIdentityCol from sysobjects where xtype = 'U' --script to determine the table and columne with the identity on SELECT OBJECT_NAME(id) as TblName, name as ColName FROM syscolumns WHERE status = 0x80 --script to determine the table and columne with the identity on using ColumnProperty SELECT OBJECT_NAME(id) as TblName, name as ColName FROM syscolumns WHERE COLUMNPROPERTY(id, name, 'IsIdentity') = 1
You can use the below script to identify the data, log and backup file drive.
select @@SERVERNAME as svrName, drivename, drivedescription from ( select distinct SUBSTRING(filename,1,3) as drivename, 'data Drive' as drivedescription from master..sysaltfiles where filename like '%.mdf' union select distinct SUBSTRING(filename,1,3) as drivename, 'Log Drive' as drivedescription from master..sysaltfiles where filename like '%.ldf' union select distinct SUBSTRING(physical_device_name,1,3) as drivename, 'Backup Drive' as drivedescription from msdb.dbo.backupmediafamily where physical_device_name like '%.bak' ) tab1
Recently I encountered a situation where i have to find out the database file size on SQL Server 2000. Here is the script which you can use to run on SQL Server 2000. Below script will on SQL Server 2005 or SQL Server 2008 as well.
You can use sys.master_files instead of sysAltfiles in SQL Server 2005 and SQL Server 2008. Please find all the different scripts below.
--SQL Server 2000 select fileID ,name ,filename ,(size*8)/1024 SizeMB ,((size*8)/1024)/1024 SizeGB ,db_name(dbid) as DBName from sysaltfiles where db_name(dbid) = 'master' --SQL Server 2000 select fileID ,name ,filename ,(size*8)/1024 SizeMB ,((size*8)/1024)/1024 SizeGB ,db_name(dbid) as DBName from sysaltfiles where filename like '%.ldf%' --SQL Server 2005/2008 SELECT DB_NAME(database_id) AS DBName, Name AS LogicalName, Physical_Name, (size*8)/1024 SizeMB Physical_Name, ((size*8)/1024)/1024 SizeGB FROM sys.master_files WHERE DB_NAME(database_id) = 'Master' GO
aiDeeva.com 