grzech.webio.pl


.. kolejny blog w sieci (głównie na własne potrzeby)


  1. Szukanie baz danych dostępnych dla 'guest'

    SET NOCOUNT ON GO DECLARE @DBName VARCHAR(255) DECLARE @SQL VARCHAR(MAX) DECLARE @SQLEXEC VARCHAR(MAX) CREATE TABLE #TableVar (DatabaseName varchar(256), permission_name varchar(32), State_desc varchar(10)) DECLARE curDatabases CURSOR STATIC FOR SELECT [name] FROM master.sys.databases WHERE database_id > 4 ORDER BY [name] OPEN curDatabases FETCH NEXT FROM curDatabases INTO @DBName WHILE @@FETCH_STATUS = 0 BEGIN SET @SQL = 'insert into #TableVar SELECT DB_NAME() as DatabaseName,permission_name,state_desc FROM sys.database_permissions where grantee_principal_id = user_id(''''guest'''') AND state...

  2. Generowanie T-SQL do odbudowy katalogów FullText

    declare @db_name sysname; declare db cursor for select sd.name as db_name from sys.dm_fts_active_catalogs fts inner join sys.databases sd on fts.database_id = sd.database_id ORDER BY sd.name open db fetch next from db into @db_name while @@fetch_status = 0 begin declare @fts_name sysname; declare fts cursor for select fts.name as fts_name from sys.dm_fts_active_catalogs fts inner join sys.databases sd on fts.database_id = sd.database_id WHERE sd.name = @db_name open fts fetch next from fts into @fts_name while @@fetch_status = 0 begin print 'USE ' + QUOTENAME(@db_name) + '; ALTER FULLTEXT CATALOG...

  3. Problem z uzupełnianiem katalogów Full Text

    Błąd w logach: Error '0x80040e97' occurred during full-text index population for table or indexed Rozwiązanie: Exec sp_fulltext_service 'ft_timeout', 1200000 Źródło: http://support.microsoft.com/kb/2270849

  4. Ustawianie Auto Close = false i Recovery Simple dla wszystkich baz danych

    declare @isql varchar(2000), @dbname varchar(200) declare c1 cursor for select name from master..sysdatabases where (name not in ('master','model','msdb','tempdb')) and (DATABASEPROPERTYEX([Name] , 'IsAutoClose') = 1 OR DATABASEPROPERTYEX([Name] , 'Recovery') = 'FULL') order by name open c1 fetch next from c1 into @dbname While @@fetch_status <> -1 begin select @isql = 'ALTER DATABASE [@dbname] SET AUTO_CLOSE OFF' select @isql = replace(@isql,'@dbname',@dbname) print @isql exec(@isql) select @isql = 'ALTER DATABASE [@dbname] SET RECOVERY SIMPLE' select @isql = replace(@isql,'@dbname',@dbname...

  5. Odrzucenie wszystkich polaczeń do bazy danych SQL Server

    USE master GO SET NOCOUNT ON DECLARE @DBName varchar(50) DECLARE @spidstr varchar(8000) DECLARE @ConnKilled smallint SET @ConnKilled=0 SET @spidstr = '' Set @DBName = 'DB_NAME' IF db_id(@DBName) 0 BEGIN EXEC(@spidstr) SELECT @ConnKilled = COUNT(1) FROM master..sysprocesses WHERE dbid=db_id(@DBName) END

  6. Zmiana Connection timeout w SQL Server

    EXEC SP_CONFIGURE 'remote query timeout', 1800 reconfigure EXEC sp_configure EXEC SP_CONFIGURE 'show advanced options', 1 reconfigure EXEC sp_configure EXEC SP_CONFIGURE 'remote query timeout', 1800 reconfigure EXEC sp_configure Źródło: http://sqlserverplanet.com/troubleshooting/fix-timeout-expired-the-timeout-period-elapsed-prior-to-completion-of-the-operation-or-the-server-is-not-responding/

  7. Zliczenie ilości rekordów i zajętości we wszystkich tabelach danej bazy danych

    SELECT t . NAME AS TableName , p . rows AS RowCounts , SUM ( a . total_pages ) * 8 AS TotalSpaceKB , SUM ( a . used_pages ) * 8 AS UsedSpaceKB , ( SUM ( a . total_pages ) - SUM ( a . used_pages )) * 8 AS UnusedSpaceKB FROM sys . tables t INNER JOIN sys . indexes i ON t . OBJECT_ID = i . object_id INNER JOIN sys . partitions p ON i . object_id = p . OBJECT_ID AND i . index_id = p . index_id INNER JOIN sys . allocation_units a ON p . partition_id = a . container_id WHERE t . NAME NOT LIKE 'dt%' AND t . is_ms_shipped = 0 AND i . OBJECT_ID > 255 GROUP BY t . Name , p . Rows ORDER BY t . Name Źródło...