Find SQL Traces, currently running

SELECT * FROM sys.traces -- Will show all the traces

You will get so many details by running the above statement regarding the current traces.
By default one trace is running.
You can see the location/Path where the trace file being sitting, Start time, Stop time, How much max. size the trace file holds up to, in how many files it Can hold the data end so on.

Note: You can give your own size and own number of files while starting the Trace.

SELECT * FROM fn_trace_getinfo(0) -- Show all traces

It will give you minimum but almost all important fields information as result. But you have to process the trace number as Input in the function.

SELECT * FROM fn_trace_getinfo(NULL)
will to same as above.

If you decided to Stop/Delete a trace which is currently running, you can use below commands.(Note: You have to provide the trace id)

sp_trace_setstatus  @traceid =  2,  @status =  0    -- It will stop the trace by providing Traceid..

 Even though the trace is stopped, it still showing up on the traces list. You can completely delete that by using following command.

sp_trace_setstatus  @traceid =  2,  @status =  2   -- It will delete the trace from the list by providing Traceid..

Quick check : What is running on my server at the moment

You can quickly run SP_Who2 to see what is running on your server at the moment. Of course it will give you  current transactions and pending (currently processing) transactions also called as open transactions.

If you use SP_Who2 Active, you will just get Current transactions.

Apart from these commands you can get interesting information with dmv's, introduced in sql 2005 and also applicable for later versions of 2005.

DECLARE @TimeStamp DATETIME
SET @TimeStamp = DATEADD(MINUTE,-10,getdate())  -- You can pass your own string here

SELECT login_time
        ,last_request_start_time
        ,last_request_end_time
        ,DATEDIFF(millisecond,last_request_start_time,last_request_end_time) AS 'Milli'  -- Dynamic column
        ,host_name
        ,PROGRAM_NAME
        ,host_process_id
        ,client_interface_name
        ,login_name
        ,nt_user_name
        ,total_scheduled_time
        ,total_elapsed_time
        ,reads
        ,writes
        ,logical_reads
        ,original_login_name
    FROM sys.dm_exec_sessions WHERE
    last_request_end_time > @timeStamp
    ORDER BY login_time

SQL Server Versions History

The glance you can find the version history of the sql server as released...

Version	Year	Release Name	Codename
1.0 (OS/2)	1989	SQL Server 1.0 (16 bit)	-
1.1 (OS/2)	1991	SQL Server 1.1 (16 bit)	-
4.21 (WinNT)	1993	SQL Server 4.21	SQLNT
6.0	1995	SQL Server 6.0	SQL95
6.5	1996	SQL Server 6.5	Hydra
7.0	1998	SQL Server 7.0	Sphinx
-	1999	SQL Server 7.0 OLAP Tools	Palato mania
8.0	2000	SQL Server 2000	Shiloh
8.0	2003	SQL Server 2000 64-bit Edition	Liberty
9.0	2005	SQL Server 2005	Yukon
10.0	2008	SQL Server 2008	Katmai
10.25	2010	SQL Azure DB	CloudDatabase
10.5	2010	SQL Server 2008 R2	Kilimanjaro (aka KJ)
11.0	2012	SQL Server 2012	Denali
12.0	2014	SQL Server 2014	Not Named Yet.....

Do you know this? (Smallest VLF in size) / (Minimum Size of Transactional Log)

VLF: Virtual Log File.

The smallest size of VLF is 256 KB.

The minimum size of the transactional log 512KB.

 Please check at My Previous VLF related Post to know little more about VLF's.

How to find Foreign Keys from the database

--      Find all Foreign key on the database with TableName and Referenced TableName
  
    SELECT Name AS ForeignKeyName, OBJECT_NAME(parent_object_id) AS BaseTableName,
            OBJECT_NAME(referenced_object_id) AS ReferecedTableName --, *
    FROM sys.foreign_keys order by BaseTableName

--     TableName And Number of Foreign keys on each table on the database.
    SELECT OBJECT_NAME(parent_object_id) AS TableName ,COUNT(*) AS foreingKey_Count
    FROM sys.foreign_keys
    GROUP BY OBJECT_NAME(parent_object_id)
    ORDER BY TableName

Orphans users

> Find list of the Orphans in the database

                                    sp_change_users_login 'report'

> Fix them Automatically in real fast :)

                                    sp_change_users_login 'auto_fix','loginName'
If it still not fix yet, the following may helpful..

> You can create newLogin same as the 'OrphonUser' which by running the report..Then run the following
                                    exec sp_change_users_login 'update_one' , 'OrphonUser', 'NewLogin';

Dropping list of table at a time

I dont know you are femilier with this statement of not, But you can drop selected tables by issueing one drop statement as below.

DROP TABLE Table1,Tbl2,MyTab

Get all tables information in the database

Some times we may think to see single table information mainly the row count and will get the result easily by typing sp_spaceused tableName.
But how to get all the tables information within the database. Please have this script.
DECLARE @SpaceUsed TABLE
(
 TableName VARCHAR(100)
 ,No_Of_Rows BIGINT
 ,ReservedSpace VARCHAR(15)
 ,DataSpace VARCHAR(15)
 ,Index_Size VARCHAR(15)
 ,UnUsed_Space VARCHAR(15)
 )
DECLARE @sqlstring VARCHAR(500)
SET @sqlstring  =  'exec sp_spaceused ''?'''
INSERT INTO @SpaceUsed EXEC sp_msforeachtable @command1=@sqlstring
SELECT * FROM @SpaceUsed
Of course, you could use the following too. But always depends on the format you want the cost to execute.
DECLARE @sqlstring VARCHAR(500)
 SET @sqlstring = 'exec sp_spaceused ''?'''
 EXEC sp_msforeachtable @command1=@sqlstring

Get DB roles

DECLARE @database nVARCHAR(128)=NULL
DECLARE @user VARCHAR(20)=NULL
DECLARE @dbo CHAR(1)=NULL
DECLARE @access CHAR(1)=NULL
DECLARE @security CHAR(1)=NULL
DECLARE @ddl CHAR(1)=NULL
DECLARE @datareader CHAR(1)=NULL
DECLARE @datawriter CHAR(1)=NULL
DECLARE @denyread CHAR(1)=NULL
DECLARE @denywrite CHAR(1)=NULL
DECLARE @dbname VARCHAR(200)
DECLARE @sqlstr VARCHAR(8000)
CREATE TABLE #DBROLES (
DBName SYSNAME NOT NULL,
UserName SYSNAME NOT NULL,
db_owner VARCHAR(3) NOT NULL,
db_accessadmin VARCHAR(3) NOT NULL,
db_securityadmin VARCHAR(3) NOT NULL,
db_ddladmin VARCHAR(3) NOT NULL,
db_datareader VARCHAR(3) NOT NULL,
db_datawriter VARCHAR(3) NOT NULL,
db_denydatareader VARCHAR(3) NOT NULL,
db_denydatawriter VARCHAR(3) NOT NULL,
Cur_Date DATETIME NOT NULL DEFAULT GETDATE()
)
DECLARE DBName_Cursor CURSOR FOR
SELECT name FROM master.dbo.sysdatabases
WHERE name in ('master')
ORDER BY name
OPEN DBName_Cursor
FETCH NEXT FROM DBName_Cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sqlstr = ' Insert into #DBROLES ( DBName, UserName, db_owner, db_accessadmin,
db_securityadmin, db_ddladmin, db_datareader, db_datawriter,
db_denydatareader, db_denydatawriter )
SELECT '
+''''+@dbName +''''+ ' as DBName ,UserName, '+CHAR(13)+ '
Max(CASE RoleName WHEN ''db_owner'' THEN ''Yes'' ELSE ''No'' END) AS db_owner,
Max(CASE RoleName WHEN ''db_accessadmin '' THEN ''Yes'' ELSE ''No'' END) AS db_accessadmin ,
Max(CASE RoleName WHEN ''db_securityadmin'' THEN ''Yes'' ELSE ''No'' END) AS db_securityadmin,
Max(CASE RoleName WHEN ''db_ddladmin'' THEN ''Yes'' ELSE ''No'' END) AS db_ddladmin,
Max(CASE RoleName WHEN ''db_datareader'' THEN ''Yes'' ELSE ''No'' END) AS db_datareader,
Max(CASE RoleName WHEN ''db_datawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_datawriter,
Max(CASE RoleName WHEN ''db_denydatareader'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatareader,
Max(CASE RoleName WHEN ''db_denydatawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatawriter
FROM (
SELECT b.name as USERName, c.name as RoleName
FROM '
+ @dbName+'.dbo.sysmembers a '+CHAR(13)+
' JOIN '+ @dbName+'.dbo.sysusers b '+CHAR(13)+
' on a.memberuid = b.uid JOIN '+@dbName +'.dbo.sysusers c
on a.groupuid = c.uid )s
GROUP BY USERName
ORDER BY UserName'
--Print @sqlstr
Execute (@sqlstr)
FETCH NEXT FROM DBName_Cursor INTO @dbname
END
CLOSE DBName_Cursor
DEALLOCATE DBName_Cursor
SELECT * FROM #DBRoles
WHERE ((@database is NULL) OR (DBName LIKE '%'+@database+'%')) AND
((@user is NULL) OR (UserName LIKE '%'+@user+'%')) AND
((@dbo is NULL) OR (db_owner = 'Yes')) AND
((@access is NULL) OR (db_accessadmin = 'Yes')) AND
((@security is NULL) OR (db_securityadmin = 'Yes')) AND
((@ddl is NULL) OR (db_ddladmin = 'Yes')) AND
((@datareader is NULL) OR (db_datareader = 'Yes')) AND
((@datawriter is NULL) OR (db_datawriter = 'Yes')) AND
((@denyread is NULL) OR (db_denydatareader = 'Yes')) AND
((@denywrite is NULL) OR (db_denydatawriter = 'Yes'))
ORDER BY UserName
DROP TABLE #DBROLES

SSIS Package Error while exporting data to EXCEL

I have been told to create a SSIS package which populate the data in Excel and send to the requested users.
I have succeded creating every task in the process. But, while executing the pacage I have got below error. So after little bit investigation I used the below solution and made it work. It may helpful to one of you.

If you have some other issue as you can see many, Please send me the issue I will try to make it work for you.

Problem :

Error: 0xC00F9304 at SQLToExcel, Connection manager "Excel Connection Manager": SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available.
Error: 0xC020801C at Data Flow Task, ReportExcel [16]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC00F9304.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Error: 0xC0047017 at Data Flow Task, SSIS.Pipeline: component "ReportExcel" (16) failed validation and returned error code 0xC020801C.
Error: 0xC004700C at Data Flow Task, SSIS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at Data Flow Task: There were errors during task validation.
SSIS package "SQLToExcel.dtsx" finished: Failure.

Solution :

The below solution may solve your problem.

Right click on Project
Select Debugging
In Debug Options
Run64BitRuntime make it to False.

System View Permissions

Select permissions on system views for regular user
GRANT VIEW SERVER STATE TO LoginName

Deny permissions on system views for regular user
DENY VIEW SERVER STATE TO LoginName

Identity Column

By using this simple query you can find particular table has identity column on it or not and other valuable information.

SELECT   OBJECT_NAME(OBJECT_ID) AS TableName,
             Name AS ColumnName, -- Which column that identity property set on
             Seed_value, -- Current Seed Value
             Increment_Value,  -- Current Increment value
             Last_Value, -- Last value that entered in that column
             IS_NOT_FOR_REPLICATION -- whether it is replicated(1) or Not(0)
    FROM     SYS.IDENTITY_COLUMNS
    WHERE OBJECT_NAME(OBJECT_ID) = 'Srvs_DailyCheck' -- Please enter your table name