ict.ken.be

Delivering solid user friendly software solutions since the dawn of time.

Space used by database objects 

Categories: SQL Server
CREATE TABLE #foo(
name varchar(128),
rows int,
reserved varchar(128),
data varchar(128),
index_size varchar(128),
unused varchar(128)
)
DECLARE @objectName nvarchar(128)
DECLARE cursorTables CURSOR FOR
 SELECT so.name
 FROM sysobjects so (nolock), sysindexes si (nolock)
 WHERE so.xtype = 'U' AND si.id = OBJECT_ID(so.name)
 GROUP BY so.name 
 ORDER BY MAX(si.rows) DESC
OPEN cursorTables
FETCH NEXT FROM cursorTables INTO @objectName
WHILE @@FETCH_STATUS = 0
BEGIN
 INSERT INTO #foo
 EXEC ('sp_spaceused ' + @objectName)
 FETCH NEXT FROM cursorTables INTO @objectName
END
CLOSE cursorTables
DEALLOCATE cursorTables
SELECT * FROM #foo
DROP TABLE #foo

Table last index access 

Categories: SQL Server

--only since last restart sql-server
--SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,*

select *
from sys.tables
where name not in
(
select o.name
FROM sys.dm_db_index_usage_stats s
inner join sys.objects o on s.object_id = o.object_id
WHERE s.database_id = DB_ID( 'DATABASE_NAME')
--ORDER by s.last_user_update desc
)
order by modify_date desc

Tables delete by name 

Categories: SQL Server

select *
from sys.tables t
where name like 'TMP_%'
and create_date < getdate() - 3
order by create_date desc

/* 
CREATE PROCEDURE [DBA_MESSAGENT].[SP_System_Delete_TMP_Tables]
AS
BEGIN
SET NOCOUNT ON;

DECLARE @tableName nvarchar(128)
DECLARE cursorTables CURSOR FOR
select t.name from sys.tables t where t.Name like 'TMP_%' and create_date < getdate() - 3
OPEN cursorTables
FETCH NEXT FROM cursorTables INTO @tableName
WHILE @@FETCH_STATUS = 0
BEGIN

 DECLARE @sqlSelect NVARCHAR(4000)
 SET @sqlSelect = 'DROP TABLE [' + @tableName + ']'
 EXEC SP_EXECUTESQL @sqlSelect

 FETCH NEXT FROM cursorTables INTO @tableName
END
CLOSE cursorTables
DEALLOCATE cursorTables
 
END
*/

 

Configuration of pools and asp.net 

Categories: .Net IIS

.Net 4 repair

  • winver.exe
  • %windir%\Microsoft.NET\Framework64\v4.0.30319\SetupCache\Client\setup.exe /repair /x86 /x64 /ia64 /parameterfolder Client /norestart
  • %windir%\Microsoft.NET\Framework64\v4.0.30319\aspnet_regiis.exe –i

Setting up permissions and pools on iis (eg. mojoPortal)

  • Create a new Web Site in IIS and name it mojoportal, leave the IP address as "All Unassigned" but add the host name "mojoportal"
  • Point the web site root to the mojoportal folder and choose a .NET 4 Integrated application pool. 
  • Make note of the user that is the identity on the application pool
  • In Windows Explorer, right click the mojoportal folder and choose properties, on the security tab click Edit..., then click Add..., then click Advanced..., then click Find Now
  • Select the user that is the identity on the application pool, then click OK, give the user read permissions here and click OK.
Page 40 of 43 << < 20 33 34 35 36 37 38 39 40 41 42 43 > >>