Trigger to audit permission changes in SQL Server 2005 on any object in any database

Create a table to maintain the records in master database because its a server level trigger:

===========================================================

create table dbo.PermissionAudit (eventtype nvarchar (100), serverLogin nvarchar(100) not null,DBUser nvarchar (100) not null,TSQLText varchar (max), eventdata xml not null)

Trigger Definition

===================== 

Create TRIGGER PermissionAudit

ON All Server

FOR

CREATE_LOGIN,ALTER_LOGIN, DROP_LOGIN, ALTER_AUTHORIZATION_SERVER,GRANT_SERVER,DENY_SERVER, REVOKE_SERVER,

AS

      DECLARE @Eventdata XML

      SET @Eventdata = EVENTDATA()

      INSERT into

            dbo.PermissionAudit

(EventType,EventData, ServerLogin,DBUser,TSQLText)

            VALUES

            (     @Eventdata.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),

                  @Eventdata, system_USER,CONVERT(nvarchar(100), CURRENT_USER),

                  @Eventdata.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)')

            )

Enabling the Trigger

======================

enable trigger PermissionAudit on all server

--Abhishek Soni

Script to find out size of all the databases on a particular SQL Server Instance

select @@SERVERNAME servername, DB_NAME(db.dbid) DatabaseName, Sum(af.size)*8/1024 DBSizefrom sysdatabases db inner join sysaltfiles af on db.dbid = af.dbid where DB_NAME(db.dbid) not in ('master', 'tempdb','model', 'msdb', 'distribution') Group By DB_NAME(db.dbid)

Comments:
==========
The output would have the following columns:

ServerName
DatabaseName
Size (in MB)


--Abhishek Soni

Script to find out free space for each file in every database on a particular SQL Server Instance

SET NOCOUNT ON IF OBJECT_ID('tempdb..#DBINFO') IS NOT NULLBEGINDROP TABLE #DBINFOEND CREATE TABLE #DBINFO (dbname sysname,lname sysname,TYPE varchar(20),[size] decimal(9, 2) NULL ,[used]
)decimal(9, 2) NULL IF OBJECT_ID('tempdb..#LOGINFO') IS NOT NULLBEGINDROP TABLE #LOGINFOEND CREATE TABLE #LOGINFO(DBName sysname,LogSize real,LogSpaceUsed real,Status int) IF OBJECT_ID('tempdb..#TEMPSTATS') IS NOT NULLBEGINDROP TABLE #TEMPSTATSEND CREATE TABLE #TEMPSTATS(fileid int,filegroup int,totalextents int,usedextents int,name varchar(1024),
)filename varchar(1024) DECLARE @dbname sysname,@sql varchar(8000) IF OBJECT_ID('tempdb..#temp_db') IS NOT NULLBEGINDROP TABLE #temp_dbEND SELECT
name INTO #temp_dbFROM master.dbo.sysdatabases

WHERE DATABASEPROPERTY(name,'IsOffline') = 0AND has_dbaccess(name) = 1ORDER BY name WHILE (1 = 1)BEGIN



SET @dbname = NULL SELECT TOP 1 @dbname = nameFROM #temp_dbORDER BY name IF @dbname IS NULL

TRUNCATE TABLE #TEMPSTATS
INSERT INTO #TEMPSTATS
EXECUTE(''DBCC SHOWFILESTATS'')
INSERT INTO #DBINFO (DBNAME, LNAME, TYPE, [SIZE], [USED])
SELECT db_name(), NAME, ''Data''
, totalextents * 64.0 / 1024.0
, usedextents * 64.0 / 1024.0
FROM #TEMPSTATS
INSERT INTO #DBINFO (DBNAME, LNAME, TYPE, [SIZE], [USED])
SELECT db_name(), name, ''Log'', null, null
FROM sysfiles
WHERE status & 0x40 = 0x40'

GOTO _NEXT SET @sql = ' USE ' + @dbname + ' EXEC(@sql) DELETE FROM #temp_db WHERE name = @dbnameEND _NEXT: INSERT
INTO #LOGINFOEXECUTE ('DBCC SQLPERF(LOGSPACE)') UPDATE

#DBINFOSET SIZE = B.LogSize, USED = LogSize * LogSpaceUsed / 100FROM #DBINFO AINNER
JOIN #LOGINFO BON (A.DBNAME = B.DBNAME)AND(A.TYPE = 'LOG') SELECT dbname AS [database name],lname AS [LOGICAL FILE NAME],TYPE,[size] AS [space allocated (MB)],used AS[space used (MB)],[size] - used AS [free space (MB)],cast(used/[size]*100 AS numeric(9,2)) AS [space used %],cast(100-(used/[size]*100) AS numeric(9,2)) AS [free space %]FROM #DBINFOORDER BY dbname, TYPE DROP TABLE #DBINFODROP TABLE #temp_dbDROP TABLE #TEMPSTATSDROP TABLE #LOGINFO SET




Comment
===========
The script would return the following columns:

database name
logical file name
TYPE (data or log)
space allocated (MB)
space used (MB)
free space (MB)
space used %
free space %


--Abhishek Soni NOCOUNT OFF

Job running SSIS or DTS package in SQL Server hangs when using compression in one of the steps (WINZIP, WINRAR etc)

Problem
=======
SQL Server Job which calls a SSIS or DTS package may be hung if you are using any compression software in one or some of the steps

Cause:
========
There may be a pop up from the compression software being opened in the background which waits for response, since you would not notice this when the job is running the job would end up waiting indefinitely on it and you would not get any error

You may run into same issue for any package which might be using a logic/software which gives pop up. In my case the job had been running fine for a while before it started to hang, finally I found out that Winzip had been uninstalled from the server and when it was being called in the package a pop up would appear with Winzip installation wizard. Once I installed Winzip manually the job reverted to normal behavior.

Solution
=======
To find out if the job is actually victim of a pop up dialog box in the background, run the package manually using BIDS (Business Intelligence Development Studio) or DTS Designer and you should get the pop up.

You could also run the package from the command prompt using a command similar as below and you should see the pop up:

1. Go to the path where dtexec.exe is present and run the following:

Syntax
------------

dtexec.exe /f "<complete location of the package with name>" >d:\packagelog.txt

Example
------------
dtexec.exe /f "D:\SSISpackages\abc.dtsx" >d:\packagelog.txt


As per the above command the log for the package would be saved to D drive as packagelog.txt


--Abhishek Soni

Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created

Problem
=======


Message:
=======
Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created
The above dialog box appears in SQL Server 2008 Managemnet Studio (SSMS 2008) when you try and make changes to a table which reuire the table to be dropped and recreated internally.

Solution
========
To get rid of the dialog box follow the below steps to change the SSMS 2008 setting:

On  the Tools menu, click Options, expand Designers, and then click Table and Database Designers. Select or clear the Prevent saving changes that require the table to be re-created check box.

The above is shown the image below:

More Information
==============

The following actions might require a table to be re-created:

• Adding a new column to the middle of the table
• Dropping a column
• Changing column nullability
• Changing the order of the columns
• Changing the data type of a column

--Abhishek Soni