DBA-Philiac

Registration SYSTEM DSN via command prompt "C:\WINDOWS\regedit.exe" /s " \\Dfcgsts22\d$\temp\DFCGSTS_CBMSProd.reg " "C:\WINDOWS\system32\odbcad32.exe" configsysdsn "SQL Server" "DSN=CBMSProd" http://www.itechtalk.com/thread3075.html http://rssexplorer.codeplex.com/ --------- SELECT DB_NAME ( database_id ) AS DatabaseName , name AS LogicalFileName , physical_name AS PhysicalFileName FROM sys . master_files AS mf ------------ use master; select name, suser_sname(owner_sid) from sys.databases; --- USE msdb; SELECT  A.Name AS JobName,  B.name AS JobOwner  FROM dbo.sysjobs A  inner join master.sys.syslogins B on  A.owner_sid = B.sid where a.enabled = 1; Oracle 9i imported database shows error ORA 600 on dropping user http://www.dbasupport.com/forums/showthread.php?52489-Error-while-drop-user DECLARE @name VARCHAR(50) DECLARE db_cursor CURSOR FOR  SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN

To start SQLSERVER instance in a single user mode and shortens the startup time SQLSERVR -m–c   To add a Windows user to the sysadmin fixed server role in SQL Server 2005 as a failure recovery mechanism SQLCMD -s computername -E Exec sp_addsrvrolemember @loginname=’computername\local account’ GO Finally reboot the machine. Ref: - http://msdn.microsoft.com/en-us/library/ms180965(v=sql.105).aspx - http://support.microsoft.com/kb/937682 - Paul Copley, Sr. DBA

-- Create the monitoring Table create table ztbl_temp_monitor (snap_time date default sysdate, Total_Size_MB int, Used_Size_MB int, Free_Size_MB int, Tablespace_name varchar(30) ); -- Insert command for data insert into ztbl_temp_monitor(Tablespace_name, Total_Size_MB, Used_Size_MB, Free_Size_MB) select b.tablespace_name, b.SizeMB, nvl(a.UsageMB, 0) as UsageMB, b.SizeMB - nvl(a.UsageMB, 0) as FreeMB from (select sum(BLOCKS) * b.VALUE / 1024 / 1024 as UsageMB, tablespace as tablespace_name from v$tempseg_usage a, v$parameter b where b.name = 'db_block_size' group by TABLESPACE, b.VALUE) a, (select sum(bytes) / 1024 / 1024 as SizeMB, tablespace_name from dba_temp_files group by tablespace_name) b where a.tablespace_name(+) = b.tablespace_name; -- Create a procedure to incorporate the insert command CREATE OR REPLACE PROCEDURE zproc_temp_usage_monitor IS BEGIN insert into ztbl_temp_monitor(Tablespace_name, Total_Size_MB, Used_Size_MB, Free_Size_MB) select b.tablespace_name

Take All Backups to a single location DECLARE @name VARCHAR ( 50 ) -- database name   DECLARE @path VARCHAR ( 256 ) -- path for backup files   DECLARE @fileName VARCHAR ( 256 ) -- filename for backup   DECLARE @fileDate VARCHAR ( 20 ) -- used for file name   SET @path = '\\servername\SQLBackups\foldername\'     SELECT @fileDate = CONVERT ( VARCHAR ( 20 ), GETDATE (), 112 )   DECLARE db_cursor CURSOR FOR   SELECT name FROM master . dbo . sysdatabases WHERE name NOT IN ( 'master' , 'model' , 'msdb' , 'tempdb' , 'ReportServer' , 'ReportServerTempDB' , 'anyOtherDB' )     OPEN db_cursor    FETCH NEXT FROM db_cursor INTO @name      WHILE @@FETCH_STATUS = 0    BEGIN           --SET @fileName = @path + @name + '_' + @fileDate + '.BAK'          SET @fileName = @path + @name + '.BAK'          Print 'BACKUP DATABASE ' + @name