Windows: Determine the Last Reboot Date/Time of a Computer

Windows: Determine the Last Reboot Date/Time of a Computer
 Windows Server 2003 and Windows XP:
 
     Run the following at a command prompt:
        net statistics server
 
     The second line that is returned will look something like this:
           Statistics since 11/12/2013 1:23:45 PM
 
     which for the majority of cases* will be the server uptime.

     * The value is actually the uptime of the Server service.  Unless that service
     has been restarted since server boot-time, which is not a common action,
     it will be the same as the server uptime.

Windows Server 2008, Windows 7 and Windows 8:
 
     Start Task Manager by right-clicking the Taskbar, click the Performance tab
     and note the value for Uptime near the lower-right of the screen.
 
     - or -
 
     Run the following from a command prompt:
          systeminfo | find "Time:"
 
     The output will look something like this:
           System Boot Time: 11/12/2013, 1:23:45 AM

Steps to Install a SQL Server 2008 Service Pack

How to Install a SQL Server 2008 Service Pack

In this article we will take a look at how to install a SQL Server 2008 Service Pack. It is the primary responsibility of a SQL Server Database Administrator to make sure all the SQL Servers within the organizations are applied with the latest Security Updates, Cumulative Updates and Services Packs which are released by Microsoft from time to time. In this article we will take a look at how to install a SQL Server 2008 Service Pack.

Important Note: It is highly recommended that database administrator should backup all User and System database along with Resource database before applying any Security Updates, Cumulative Updates or Service Packs. Resource database is a read only database which stores all the system objects which are included in SQL Server. This database is invisible in management studio and this was first introduced in SQL Server 2005. To know more about Resource database read the article titled Resource Database

Best Practices to follow before installing a SQL Server Service Pack

1.  Perform a full backup of all User, System and Resource database.

2. Note down of the important SQL Server Configuration Settings, Startup Parameters, Linked Servers, and Script out SQL Server Agent Jobs, Script out SQL Server Logins, Memory Utilization, CPU and Disk Utilization etc.

3. Create an appropriate Service Pack Deployment and Rollback Plan

4. Always install Service Packs first in Development environment and then test all applications which are using SQL Server.

5. Once everything looks good in development environment, then only install Service Pack in QA environment. Test all applications which are using SQL Server in QA environment and also test your rollback plan.

6. If everything look good in both Development and QA environment then plan to install Service Pack in a Production environment by communicating an appropriate downtime window to the stake holders and database/application users.

7. Once the Service Pack is installed successfully in a Production environment perform sanity checks to confirm all applications are working fine.

8. Reboot the SQL Server once Service Pack / Cumulative Updates or a Security Updates are applied successfully on the server.

9. If everything looks goods then release the Production environment for user activities and monitor the environment closely for a week or two to make sure there are no unusual spikes in CPU and Memory Utilizations.

Install SQL Server 2008 Service Pack

Let us now start with installing SQL Server 2008 Service Pack 2. You can follow the steps mentioned in this article to apply Service Packs on all Editions of SQL Server 2008. If you are using an RTM version of SQL Server 2008 then you can directly apply SQL Server 2008 Service Pack 2 as this has all the fixes of Service Pack 1.

1. Identify which Version and Edition of SQL Server you are using in your environment and then download the latest available SQL Server 2008 Service Pack from Microsoft website. 

2. Right click the downloaded SQL Server 2008 Service Pack Executable file and then select Run as Administrator option from the drop down as shown in the below snippet.

3. In the Welcome screen of SQL Server 2008 Service Pack, the Setup wizard will check whether all the prerequisites are met before the service pack installation begins. If you encounter any error then you need to resolve them before continuing with the service pack installation.

4. In the License Terms screen you must accept the Microsoft Software License Terms and then click Next to continue with the service pack installation.

5. In the Select Features Screen you can choose the database instance on which you want to install SQL Server 2008 Service Pack. If you want to install service pack only on a default instance then you need select MSSQLSERVER as shown in the below snippet. If you would like to install service pack on a named instance then you need to select the named instance to install service pack on the named instance. Click Next to continue with the service pack installation.

6. In the Check Files In Use screen it will check for services and applications which are using files that SQL Server 2008 Service Pack Setup will require. If there are any such services and applications which are using files required for the service pack setup then you need to stop the process or else you should restart the SQL Server before applying SQL Server Service Pack. Click Next to continue with the service pack installation.

7. In the Ready to Update screen you will be able to see a quick summary of all the features which will be updated with the Service Pack. Click Next to continue with the service pack installation.

8. In the Update Progress screen you will be able to see the progress of service pack installation

9. Once the Service Pack installation is complete you will be able to see the below screen. Click Next to complete with the service pack installation.

10. In the Complete screen you will be able to see a message Your SQL Server 2008 update operation has completed successfully. Click the Close button to complete the service pack installation.

Execute the below mentioned TSQL query to verify the Service Pack Installation.

SELECT
            SERVERPROPERTY('ProductVersion') AS ProductVersion,
            SERVERPROPERTY('ProductLevel') AS ProductLevel,
            SERVERPROPERTY('Edition') AS Edition,
            SERVERPROPERTY('ResourceLastUpdateDateTime') AS 'ResourceLastUpdateDateTime',
            SERVERPROPERTY('ResourceVersion') AS 'ResourceVersion' 
GO

It is highly recommended that you must backup all the System databases (Master, Model, MSDB, ReportServer, and ReportServerTempDB) including Resource database immediately after the successfully installation of SQL Server 2008 Service Pack or a Cumulative Update. It is also a good practice to reboot the SQL Server once the SQL Server Service Pack or a Cumulative Update installation is completed.

Conclusion

In this article you have seen how to apply a SQL Server 2008 Service Pack and the best practices which you need to follow before installing a service pack.

Important note: this will be the last service release of any kind for SQL Server 2008 R2 (there will be no cumulative updates for SP3). So if you are not moving to 2012 and were waiting for some sign to deploy fixes, this may be it.

My usual disclaimer: this update is NOT for SQL Server 2008 (or SQL Server 2012). Only apply to systems where SELECT @@VERSION returns 10.50.xxxx, where xxxx is < 6000.  

Reference:
http://www.mytechmantra.com/LearnSQLServer/Install_SQL_Server_2008_Service_Pack_P1.html
http://www.mytechmantra.com/LearnSQLServer/Install_SQL_Server_2008_Service_Pack_P2.html

Shrinking tempdb without restarting SQL Server

Ok, so even if you’re a seasoned veteran T-SQL coder, at some time you will write a query that runs away and supersizes the tempdb database. This, in turn, might fill up your disk and cause other server-related problems for you. At that point, you may find out the hard way that shrinking tempdb isn’t like shrinking any other database.

Here are some tricks that I’ve tried successfully – but bear in mind that your mileage may vary.

Tempdb stores temporary tables as well as a lot of temporary (cached) information used to speed up queries and stored procedures. For the best chances in shrinking tempdb, we’re going to clear these different caches (except for the temp tables, which you should drop manually).

First off, the easy way out

It’s worth mentioning. If you’re not running a production-like environment, your best bet is to restart the SQL Server service. This will return tempdb to its default size, and you won’t have to worry about all the potential pitfalls of this article. But since you’re reading this, chances are you can’t just restart the server. So here goes:

Warning: These operations remove all kinds of caches, which will impact server performance to some degree until they’ve been rebuilt by the SQL Server. Don’t do this stuff unless absolutely neccessary.

DBCC DROPCLEANBUFFERS

Clears the clean buffers. This will flush cached indexes and data pages. You may want to run a CHECKPOINT command first, in order to flush everything to disk.

CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS;
GO

DBCC FREEPROCCACHE

Clears the procedure cache, which may free up some space in tempdb, although at the expense of your cached execution plans, which will need to be rebuilt the next time. This means that ad-hoc queries and stored procedures will have to recompile the next time you run them. Although this happens automatically, you may notice a significant performance decrease the first few times you run your procedures.

DBCC FREEPROCCACHE;
GO

DBCC FREESYSTEMCACHE

This operation is similar to FREEPROCCACHE, except it affects other types of caches.

DBCC FREESYSTEMCACHE ('ALL');
GO

DBCC FREESESSIONCACHE

Flushes the distributed query connection cache. This has to do with distributed queries (queries between servers), but I’m really not sure how much space they actually take up in tempdb.

DBCC FREESESSIONCACHE;
GO

.. and finally, DBCC SHRINKFILE

DBCC SHRINKFILE is the same tool used to shrink any database file, in tempdb or other databases. This is the step that actually frees the unallocated space from the database file.

Warning: Make sure you don’t have any open transactions when running DBCC SHRINKFILE. Open transactions may cause the DBCC operation to fail, and possibly corrupt your tempdb!

DBCC SHRINKFILE (TEMPDEV, 20480);   --- New file size in MB
GO

Don’t set the new size too low! Make a realistic estimate of the largest “normal” size that tempdb will assume during normal day-to-day operation.

That’s it. If everything works the way it should, you should now be able to verify the new size of tempdb.

A word about shrinking database files

Best practice is to try to minimize the use of file or database shrinking as much as possible. Whenever you shrink a database file and it re-grows later on, you are potentially creating fragmentation on your physical storage medium. This is because the sectors that the file used to occupy may now very well be occupied by other information (just a few bytes are enough). When SQL Server wants to grow that database file, the newly added portion of the file will need to be placed elsewhere on the disk, thus creating fragmentation.

The number one mortal sin in this context is “autoshrink“, because it may very well add to the drive fragmentation every time it runs, which could be very frequently.

As a rule of thumb, never ever autoshrink a database. And try to be very restrictive when it comes to shrinking databases or files in general, unless it’s a one-off operation to fix the aftermath of a runaway query.

Oracle RMAN Backup error - ORA-01008

ORA-01008: not all variables bound while doing RMAN backup

Recovery Manager: Release 11.2.0.2.0 - Production on Tue Jul 217 13:026:22 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

RMAN>

connected to target database: QSTPRDR (DBID=3992020636)

RMAN>

connected to recovery catalog database

RMAN> 2> 3> 4> 5> 6> 7> 8> 9>

DBGSQL:     TARGET> select  nvl(max(al.recid), '0'),nvl(max(al.recid), 0)   into  :txtparmvalue, :parmvalue   from  v$archived_log al  where  al.status in ('X', 'A')    and  al.is_recovery_dest_file = 'YES'    and  al.creator = 'RMAN'

DBGSQL:        sqlcode = 1008

allocated channel: t1

channel t1: SID=1726 device type=SBT_TAPE

channel t1: Veritas NetBackup for Oracle - Release 6.5 (2009050106)

DBGSQL:     TARGET> select  nvl(max(al.recid), '0'),nvl(max(al.recid), 0)   into  :txtparmvalue, :parmvalue   from  v$archived_log al  where  al.status in ('X', 'A')    and  al.is_recovery_dest_file = 'YES'    and  al.creator = 'RMAN'

DBGSQL:        sqlcode = 1008

released channel: t1

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of backup command at 27/07/2015 13:28:36

RMAN-03014: implicit resync of recovery catalog failed

ORA-01008: not all variables bound

RMAN>

Recovery Manager complete.

I noticed that resync catalog or crosscheck archivelog all did not help in this case and this error appears due to a BUG in 11.2.0.2. You can read more about this issue at Oracle Metalink doc ID: 1280447.1

Current fix is to flush shared_pool and you will be able to start rman backup again.

SQL> alter system flush shared_pool;

sqlserver index rebuild

Below is the script to identify the fragmentation report for particular database.

USE DatabaseName -- use the database name here

GO

SELECT object_name(IPS.object_id) AS [TableName],

   SI.name AS [IndexName],

   IPS.Index_type_desc,

   IPS.avg_fragmentation_in_percent,

   IPS.avg_fragment_size_in_pages,

   IPS.avg_page_space_used_in_percent,

   IPS.record_count,

   IPS.ghost_record_count,

   IPS.fragment_count,

   IPS.avg_fragment_size_in_pages

FROM sys.dm_db_index_physical_stats(db_id(N'ManageSoft'), NULL, NULL, NULL , 'DETAILED') IPS

   JOIN sys.tables ST WITH (nolock) ON IPS.object_id = ST.object_id

   JOIN sys.indexes SI WITH (nolock) ON IPS.object_id = SI.object_id AND IPS.index_id = SI.index_id

WHERE ST.is_ms_shipped = 0

ORDER BY 1,5

GO

The above script is only to know the fragmentation status, if you do not want the fragmentation report you ignore the above script.

Directly run the below script to rebuild the indexes of the particular database.

USE DatabaseName --Enter the name of the database you want to reindex

 DECLARE @TableName varchar(255)

 DECLARE TableCursor CURSOR FOR

SELECT table_name FROM information_schema.tables

WHERE table_type = 'base table'

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName

WHILE @@FETCH_STATUS = 0

BEGIN

DBCC DBREINDEX(@TableName,' ',90)

FETCH NEXT FROM TableCursor INTO @TableName

END

ORA-01031: insufficient privileges on Windows

sqlplus / as sysdba does not works (ORA-01031: insufficient privileges),

But sqlplus sys/oracle as sysdba works fine.
*********************************************************************************

C:\Windows\system32>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Dec 2 17:15:06 2013

Copyright (c) 1982, 2010, Oracle. All rights reserved.

ERROR:
ORA-01031: insufficient privileges

==================================================================================
Verified remote_login_passwordfile to be EXCLUSIVE
==================================================================================
C:\Windows\system32> sqlplus sys/oracle as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Dec 2 17:15:06 2013

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
SQL> show parameter remote

NAME TYPE VALUE
———————————— ———– ——————————
remote_dependencies_mode string TIMESTAMP
remote_listener string
remote_login_passwordfile string EXCLUSIVE
remote_os_authent boolean FALSE
remote_os_roles boolean FALSE
result_cache_remote_expiration integer 0
SQL>

==============================================================================================
Verified sqlnet.ora file is all good, for Windows SQLNET.AUTHENTICATION_SERVICES should be NTS
==============================================================================================
sqlnet.ora Network Configuration File: C:\Oracle\product\11.2.0\dbhome_1\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.

# This file is actually generated by netca. But if customers choose to
# install “Software Only”, this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

=========================================================================================
Verified user to be part of ORA_DBA group, which is missing below
=========================================================================================
c:\Oracle>echo %username%
malesh

c:\Oracle>NET LOCALGROUP ORA_DBA
Alias name     ORA_DBA
Comment        Oracle DBA Group

Members
——————————————————–
NT AUTHORITY\SYSTEM
The command completed successfully.

=================================================================================================
Using MMMC ( microsoft management console) Add the user “malesh” to be part of ORA_DBA OS group 
=================================================================================================
Start => All Programs => oracle – oraDB11g_home1 => Configuration and Migration Tools => Administration Assistant for Windows

OR

On command Prompt Type below
===========================
“C:\Oracle\product\11.2.0\dbhome_1\MMC Snap-Ins\ORAMMC11.exe” “C:\Oracle\product\11.2.0\dbhome_1\MMC Snap-Ins\Administration Assistant\orammcadm11″

=> expand Oracle Managed Objects => Computers => => Right Click on OS Database Administrators – computer => Add/remove
=> Under Domain Drop down Select your hostname => Select your username => click Add => OK
finally Close your MMMc ( microsoft management console) => save it while closing.

=========================================================================================
Verify again user “malesh” to be part of ORA_DBA group which was missing
=========================================================================================

c:\Oracle>NET LOCALGROUP ORA_DBA
Alias name     ORA_DBA
Comment        Oracle DBA Group

Members
——————————————————–
malesh
NT AUTHORITY\SYSTEM
The command completed successfully.

================================================================================================================
As above we see the user “malesh” is now part of LOCALGROUP ORA_DBA, Let’s check if sqlplus works as expected.
================================================================================================================
Close the command prompt if already open and re-open command prompt as administrator.

c:\Oracle>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Dec 2 19:11:09 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

Query to identify chained rows and how to fix it

Query to identify chained rows and how to fix it

The following query can be used to identify tables with chaining problems:

TTITLE 'Tables Experiencing Chaining'
SELECT owner, table_name,
       NVL(chain_cnt,0) "Chained Rows"
  FROM all_tables
 WHERE owner NOT IN ('SYS', 'SYSTEM')
       AND NVL(chain_cnt,0) > 0
ORDER BY owner, table_name;

The above query is useful only for tables that have been analyzed. Note the NVL function to replace a NULL with a zero -- tables that have not been analyzed will appear to have been.

The following steps explain how to list all of the chained rows in any selected table:

1. Create a table named CHAINED_ROWS using the following script (taken from Oracle's utlchain.sql script):

   CREATE TABLE chained_rows (
     owner_name VARCHAR2(30),
     table_name VARCHAR2(30),
     cluster_name VARCHAR2(30),
     partition_name VARCHAR2(30),
     subpartition_name VARCHAR2(30),
     head_rowid ROWID,
     analyze_timestamp DATE
   );

2. Issue the ANALYZE command to collect the necessary statistics:

   ANALYZE TABLE  LIST CHAINED ROWS;

3. Query the CHAINED_ROWS table to see a full listing of all chained rows, as shown below:

   SELECT *
     FROM chained_rows
    WHERE table_name = 'ACCOUNT';
   
   Sample Output:
   Owner_name   Table_Name    Cluster_Name    Head_Rowid   Timestamp
   -----------------------------------------------------------------
   QUEST        ACCOUNT       00000723.       0012.0004    30-SEP-93
   QUEST        ACCOUNT       00000723.       0007.0004    30-SEP-93

   The following is an example of how to eliminate the chained rows:

   CREATE TABLE chained_temp AS
       SELECT * FROM
        WHERE rowid IN (SELECT head_rowid
                          FROM chained_rowS
                         WHERE table_name = '');
   DELETE FROM
       WHERE rowid IN (SELECT head_rowid
                         FROM chained_rows
                        WHERE table_name = '');
   INSERT INTO
       SELECT * FROM chained_temp;

4. Drop the temporary table when you are convinced that everything has worked properly.

   DROP TABLE chained_temp;

5. Clean out the CHAINED_ROWS table:

   DELETE FROM chained_rows
         WHERE table_name = '';

Even when you analyze your tables without the LIST CHAINED ROWS option (i.e., ANALYZE COMPUTE STATISTICS;), a column of USER | ALL | DBA_TABLES called CHAIN_CNT stores the number of chained and migrated rows at the time the ANALYZE was run. Likewise, when you use DBMS_STATS to gather statistics in Oracle 9i (remember the ANALYZE command is deprecated for statistics collection in Oracle 9i) the CHAIN_CNT column is populated.

If you are using the rule-based optimizer and you have set OPTIMIZER_MODE to CHOOSE (the default), don't forget to remove the statistics from your tables and indexes using ANALYZE DELETE STATISTICS;. 

You can also obtain the overall number of chained and migrated rows read by your instance since startup time using the V$SYSSTAT table.

SELECT SUBSTR(name, 1, 30) "Parameter", value "Value"
  FROM v$sysstat
 WHERE name = 'table fetch continued row';