Home :
In SQL2000 connect thru Query Analyzer and type
sp_dropserver 'oldservername'
Go
sp_addserver 'newservername,'local'
Restart sql service for changes to take effect.
After that, do a check with SELECT @@SERVERNAME to make sure that the new
name is shown.Then delete the server registration,if any,from enterprise manager and reregister.
This happens when setup detects that there are keys that need updating. You may have to shut down all your startup apps.
The registry key with this information is located at:
HKLM\SYSTEM\CurrentControlSet\Control\Session Manager
and is called something like PendingFileRenameOperations. It might be safer to
rename the key and then restore it after your
SQL installation is complete.
The entire operation is described in:
http://support.microsoft.com/support/kb/Articles/q310/3/35.asp.
Though the article mentions to reboot after deleting key...sometimes it wont work and you may have to try setup without rebooting.
Connect to the server thru Query analyzer and execute:
SELECT SERVERPROPERTY('ProductLevel')
This is applicable only for SQL2000.
To optimise the performance its needed that tempdb be placed in a fast I/O subsystem.
ALTER DATABASE tempdb MODIFY FILE can be used to specify the new location of tempdb database.
Refer Moving SQL Server Databases to a New Location with Detach/Attach (Q224071)
How to find the CD Key?Moving Tempdb You can move tempdb files by using the ALTER DATABASE statement. Determine the logical file names for the tempdb database by using sp_helpfile as follows: use tempdb go sp_helpfile go The logical name for each file is contained in the name column. This example uses the default file names of tempdev and templog . Use the ALTER DATABASE statement, specifying the logical file name as follows: use master go Alter database tempdb modify file (name = tempdev, filename = 'E:\Sqldata\tempdb.mdf') go Alter database tempdb modify file (name = templog, filename = 'E:\Sqldata\templog.ldf') go You should receive the following messages confirming the change: File 'tempdev' modified in sysaltfiles. Delete old file after restarting SQL Server. File 'templog' modified in sysaltfiles. Delete old file after restarting SQL Server. Using sp_helpfile in tempdb will not confirm these changes until you restart SQL Server. Stop and restart SQL Server.
Specific paths to registry keys vary based on how you've installed and upgraded SQL Server. In my registry, the CD key is at the following registry path:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\80\registration\CD_Key
For NT 4.0 or earlier
If you get error messages in executing SQL Agent Jobs after migrating users to AD from NT domain, then refer to articles
http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B325363
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/security/security/pre_windows_2000_compatible_access_group.asp
The problem is related to SQL server jobs that fail due to AD permission problems. For a job owner (domain users) to own and run SQL jobs, it is required that the user (or group he belongs to) needs to be added to the "Pre-Windows 2000 Compatible Access" BUILTIN group in the AD domain.
If the above does not fix the problem, then make sa owner of all the jobs and let the DBAs maintain them until a fix is made available - or an sp , or the next release... : (
When a standard sql login owns a job, authentication doesn't need to occur so this problem doesn't happen.
If you get an ActiveX Scripting initialization error then
The ActiveX Scripting Host, used by all tasks that have ActiveX script capability, may be unregistered. This can lead to one of the errors below:
"ActiveX Scripting was not able to initialize the script engine."
"ActiveX Scripting Transform 'DTSTransformation__x' was not able to initialize the script engine."
"ActiveX scripting was not able to initialize the script execution engine"
Normally you can fix this by simply re-registering the DTS ActiveX Script Host DLL (axscphst.dll), using the Regsvr32.exe utility. If this does not solve the problem, then try re-registering the following DLLs in the same way: dtspkg.dll, dtspump.dll.
Regsvr32.exe "C:\Program Files\Microsoft SQL
Server\80\Tools\Binn\axscphst.dll"
(SQL Server 2000)
There are two ways to rename a database. You can use sp_renamedb to rename a database.
sp_renamedb 'OLDDB','NEWDB'
However the data files will remain with the original name, even though the datbase name is different
OR you can copy a database by using BACKUP DATABASE and then RESTORE DATABASE, using a different database name.
It is preferable to do a backup/restore to keep consistency on the naming convention (it helps when you administer lots of databases!)
By definition, sp_spaceused only accepts a single object as a parameter.But in this case you can use the undocumented call sp_MSforeachtable as in:
EXEC sp_MSforeachtable 'sp_spaceused ''?'''
Or an alternative approach would be to write a cursor to loop through the number of tables and execute the sp_spaceused against each.
You can also store the info.in a table for later analysis as illustrated in this example...
CREATE TABLE #TemptableforspaceUsed
(name SYSNAME,
rows INT,
reserved VARCHAR(10),
data VARCHAR(10),
index_size VARCHAR(10),
unused VARCHAR(10))
GO
INSERT #TemptableforspaceUsed
EXEC sp_MSforeachtable 'sp_spaceused ''?'''
Log Explorer is
the only tool available right now that provides information in depth. It is available from Lumigent explorer at www.lumigent.com
you can also use the undocumented fn_dblog() function which returns a row count that represents the number of log records.
select * from ::fn_dblog(null, null)
Another DBCC command that shows details of log utilization is DBCC LOGINFO. This gives more information than DBCC SQLPERF(LOGSPACE) and was described in detail in Andrew Zanevsky's classic article "Shrinking the Transaction Log" in the February 2000 issue of SQL Server Professional. It shows the size and status of each virtual log file in each physical log file:
You can use this to determine whether the log can be shrunk or to calculate space usage for each individual log file. The FileSize column gives each virtual log size in bytes, and you can determine whether a virtual log file is free by examining its Status column—unused virtual logs have a status of zero. This report doesn't give quite the whole story. In addition to the virtual log files, there's some extra information at the beginning of each log file, as shown by the fact that the first StartOffset isn't zero. To calculate the total log size, this initial offset must be included. Here's a query to capture DBCC LOGINFO results into a temporary table and then summarize log utilization by physical file:
CREATE TABLE #loginfo
(FileId int, FileSize int, StartOffset int, FSeqNo int, Status int, Parity smallint, Created varchar(20))
INSERT INTO #loginfo
EXEC ('DBCC LOGINFO')
SELECT FileID, sum(FileSize)+min(StartOffset) AS LogSize,
LogUsed = min(StartOffset)
+sum(CASE WHEN Status<>0 THEN FileSize ELSE 0 END),
LogFree = sum(CASE WHEN Status=0 THEN FileSize ELSE 0 END)
FROM #loginfo
GROUP BY FileId
While creating a table it is possible to mention a filegroup on which the table is to be stored.By default, if no filegroup is mentioned via the ON clause, then the table is created on primary filegroup.
In case you need to move the table to another filegroup, say filegroup1, then you may need to (re)create the clustered index of the table in the resultant filegroup.
CREATE CLUSTERED INDEX indexname ON tablename(columnname) ON filegroup1
There are two ways to determine how long SQL Server has been running:
How to automatically collect space-usage statistics for each of your databases?SELECT datefiff(mi, login_time, getdate()) FROM master..sysprocesses(nolock) WHERE spid = 1 This works because SPID 1 is created when SQL Server starts up, and we can trust the login_time column to give us an accurate idea of when SQL Server started. The second method SELECT DATEDIFF(hh, crdate, getdate()) AS UpTimeHours FROM master..sysdatabases(nolock) WHERE name = 'tempdb' works because SQL Server recreates tempdb each time the server is stopped and started.
To automatically track disk-space consumption i.e. to find the amount of space allocated and used for both the data and the log files the same way that you see in Enterprise Manager's Database Details pane when you're viewing Space Allocated information, but needed the information to be available as T-SQL code, then execute:
EXECUTE( 'dbcc showfilestats with tableresults' )
EXECUTE( 'dbcc sqlperf( logspace ) with tableresults' )
There is no way to schedule daily only one file or one filegroup to be backed up for a database (partial backup) using the database maintenance plan, but it can be done by executing the backup command. The 'backup' command can backup full databases, transaction logs, or one or more files or filegroups. A job can be setup to execute a command or a stored procedure that has the command in it.
DBCC FLUSHPROCINDB will recompile every stored procedure in a given database at once. This is nice in a benchmark environment to flush all compiled procedures before a new test. The command is one of the few that still requires the database ID. To find out the ID of a database, the simplest method is to use the DB_ID function. To run the command, use the following syntax:
DECLARE @dbid int
SELECT @dbid = (SELECT DB_ID('Northwind'))
DBCC FLUSHPROCINDB (@dbid)
There are two ways of reading the error log.
1. Find the path name using
declare @sqlpath sysname,
@datapath sysname,
@logpath varchar (256)
exec master..sp_msget_setup_paths @sqlpath out, @datapath out
select @logpath = @datapath + '\LOG\ErrorLog'
Exec xp_cmdshell 'Type @logpath' OR
exec xp_cmdshell 'type C:\"Program Files"\"Microsoft SQL Server"\MSSQL$Instance\LOG\ERRORLOG'
2. EXEC SP_READERRORLOG
3. To make sp_readerrorlog to read more files, go to sp_readerrorlog undocumented
SELECT
TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMNPROPERTY
(
OBJECT_ID(TABLE_NAME), COLUMN_NAME, 'isIdentity'
) = 1
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE OBJECTPROPERTY( OBJECT_ID(TABLE_NAME), 'TableHasPrimaryKey' ) = 1
Home :