கற்றது கை மண் அளவு கல்லாதது உலகளவு (Known is handful while unknown is an ocean ) – Sharing the little, I know
August 20, 2014
useful posts in forum :
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/d405be64-806b-49ec-80f6-b0bc9d108ea4/tempdb-in-sql-server-2008-r2-standard-edition
Capacity planning for tempdb and internally what uses tempdb :
http://msdn.microsoft.com/en-in/library/ms345368%28v=sql.105%29.aspx
http://msdn.microsoft.com/en-in/library/ms174412%28v=sql.105%29.aspx
November 16, 2011
SQL - Learning Leave a comment
Sp_helpsrvrolemember
this provides the list of logins that are added to the Server roles in that server.
sp_helpsrvrolemember ‘sysadmin’
provides the list of user that is added to sysadmin role in that server .
Source : http://msdn.microsoft.com/en-us/library/ms188772.aspx
May 10, 2011
Issue - Solution, SQL - Learning 1 Comment
When installing SP4 on SQL 2005 it failed with below error
***********************************************************************************************
Product : Database Services (MSSQLSERVER)
Product Version (Previous): 4309
Product Version (Final) :
Status : Failure
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQL9_Hotfix_KB2463332_sqlrun_sql.msp.log
Error Number : 1635
Error Description : Unable to install Windows Installer MSP file
———————————————————————————-
Product : Notification Services
Product Version (Previous): 4309
Product Version (Final) :
Status : Failure
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\NS9_Hotfix_KB2463332_sqlrun_ns.msp.log
Error Number : 1612
Error Description : Unable to install Windows Installer MSP file
———————————————————————————-
Product : Integration Services
Product Version (Previous): 4309
Product Version (Final) :
Status : Failure
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\DTS9_Hotfix_KB2463332_sqlrun_dts.msp.log
Error Number : 1612
Error Description : Unable to install Windows Installer MSP file
Summary
One or more products failed to install, see above for details
Exit Code Returned: 1635
***********************************************************************************************
Tried various options as mentioned in these blogs and links
http://support.microsoft.com/kb/918357
> Check access on Data folder.
> Backup registry setting and reinstall sql support files from installation disk.
Above links didn’t help.
Microsoft engineer provided the MSI Moksha tool which lists you the missing MSI (Installer files) & MSP (Missing patch files) list.
This MSI Moksha tool gives an output which can be saved as a txt file.
**********************************************************************************************
**************
REPORT SUMMARY
**************
Missing installer package file “79367.msi” for “Microsoft SQL Server 2005 Tools”
Missing installer cache file “b394ea49.msp” for Patch “Hotfix 3282 for SQL Server Tools and Workstation Components 2005 ENU (KB953752)” on “Microsoft SQL Server 2005 Tools”
KB Article = “Hotfix;953752;ENU;http://support.microsoft.com/?kbid=953752”
……………………
……………..
…….
**********************************************************************************************
In the output, the report summary section has the information of missing MSI & MSP files and links where we can download those hotfix / patches.
After running the MSI-Moksha tool ,
If that shows MSI’s are missing we need to find the MSI’s from the installation CD/DVD’s …
Say for ex:
The MSI-Moksha tool shows that
Missing installer package file “79367.msi” for “Microsoft SQL Server 2005 Tools”
Search under below category which product that refers to
———————————-
SQL MSI/MSP Filename and Location
———————————-
Product : 07BFD2D4BCEAFB748A59B3A35A4439F4 <==> C:\WINNT\Installer\79367.msi
With product code search for the name of the product in this example..
The product the product code refers is below (under section SQL Server Product and Patch Information)
———————————————
SQL Server Product and Patch Information
———————————————
Microsoft SQL Server 2005 Tools (Product Code = 07BFD2D4BCEAFB748A59B3A35A4439F4)
So I copied the SqlRun_Tools.msi from installation media to C:\WINNT\INSTALLER folder, and renamed it to 79367.msi
Similarly, I have copied all missing MSI’s from installation media and renamed it to the number that MSI-Moksha txt output showed as missing. (by realting the product code and finding the product name and copying that respective MSI )
Ex:
SqlRun_NS.msi – Microsoft SQL Server Notification services (in Disk 1 in installation media)
SqlRun_RS.msi – Microsoft SQL Server Reporting services (in Disk 1 in installation media)
SqlWriter.msi – Microsoft SQL Server VSS Writer (in Disk 1 in installation media)
SqlRun_Tools.msi – Microsoft SQL Server Tools (in Disk 2 in installation media)
After copying MSI’s
Now look for MSP’s
In this example there were files from SP3 missing, so downloaded the file ’ SQLServer2005SP3-KB955706-x86-ENU.exe’ to a temp path (say D:\sqlpatches)
Now extract the files to a temp folder by doing below
Open command prompt go to D:\sqlpatches
Type SQLServer2005SP3-KB955706-x86-ENU.exe /x
Which extracts the SP3 contents to D:\sqlpatches
Now run again the MSI-Moksha and use the option – Repair Missing
And provide the path where the SP3 is extracted in this example ‘D:\sqlpatches’
So it will replace all missing MSP’s in the folder C:\WINNT\INSTALLER ..
New window would display with log information of those patching files whether re-caching of patch file is done or No action taken
Once done we tried with extracting latest hotfix 4262 file (KB970894)
Downloaded ‘SQLServer2005-KB970894-x86-ENU.exe’ to a temp path (D:\sqlpatches)
Now extract the files to a temp folder by doing below
Open command prompt go to D:\sqlpatches
Type SQLServer2005-KB970894-x86-ENU.exe/x
Which extracts the hotfix 4262 contents to D:\sqlpatches
And using the MSI-Moksha Repair Missing option recreates the MSP’s in C:\WINNT\INSTALLER folder.
We tried installing SP4 and it all components were successfully installed.
Note : In our case with just extracting the SP3 and latest hotfix 4262
(the sql instance at that time was at version 4309) all files required for SP4 was gathered under C:\WINNT\INSTALLER
Many other links which posted similar issues:
http://forums.techarena.in/windows-update/1002656.htm
April 13, 2011
SQL - Learning, SQL 2008 R2 - UCP Leave a comment
How to remove UCP from SQL 2008 R2
Precautions:
This must be run from the UCP server.
User must have Sysadmin Previlages.
All managed Services must be removed from UCP before executing.
Execute the below
EXEC msdb.dbo.sp_sysutility_ucp_remove
If this doesn’t automatically drop the database sysutility_mdw , Drop it manually.
Helper : http://technet.microsoft.com/en-us/library/ff487180.aspx
March 29, 2011
SQL - Learning Leave a comment
xp_readerrorlog @p1,@p2,@p3,2p4
@p1 – 0, current log,1 for Archive # 1, 2 for Archive #2 etc
xp_enumerrorlogs helps you find the number
Note: 1- SQL Error log ; 2- SQL Agent log
@p2 -1 for SQL errorlog , 2 for SQL agent log
@p3 – Character string that you need to search
@p4 – within the searched rows with @p3 search @p4 string
Source: http://www.sqlteam.com/article/using-xp_readerrorlog-in-sql-server-2005
March 11, 2011
Issue - Solution, SQL - Learning 1 Comment
Issue :
User is provided below access
Db_datareader,
Db_datawriter,
Db_ddladmin
Still when user try to add new column or alter anything on table using GUI (using design option in GUI) it gives error
You are not logged on as the database owner or system administrator.
You might not be able to save changes to tables that you do not own.
Table <tablename> is set to read only , user doesn’t have enaough rights on this table
The db_ddladmin role can create, drop, and alter objects within the database
The db_datareader role gives implicit access to SELECT against all tables and views in a database.
The db_datawriter role gives implicit access to INSERT, UPDATE, and DELETE against all tables and views in a database.
The same alter works with with T-SQL.
Solution:
You need to provide the user below permission
Alter
View Definition
(Right click DB > Properties > Permissions)
Helper : http://msdn.microsoft.com/en-us/library/aa337286.aspx
March 5, 2011
Performance Knw, SQL - Learning Leave a comment
For Performance Issues mainly monitor 3
Disk activity
Processor utilization
Meomory Usgae
Monitoring Windows I/O and sql server buffer manager corelates or gives an idea on the Disk issues
Disk activity :
PhysicalDisk: % Disk Time – shouldnt be more than 90 %
PhysicalDisk: Avg. Disk Queue Length – more than 2 spindle
If you are using a RAID device, the % Disk Time counter can indicate a value greater than 100 percent. If it does, use the PhysicalDisk: Avg. Disk Queue Length counter to determine how many system requests, on average, are waiting for disk access.
Monitor the Memory: Page Faults/sec counter to make sure that the disk activity is not caused by paging. In Windows, paging is caused by:
Processes configured to use too much memory.
File system activity.
Two counters that can be monitored to determine the amount of I/O generated by SQL Server components are:
SQL Server:Buffer Manager:Page reads/sec
SQL Server:Buffer Manager:Page writes/sec
If the values for these counters approach the capacity limit of the hardware I/O subsystem,
try to reduce the values by tuning your application or database to reduce I/O operations
(such as index coverage, better indexes, or normalization), increasing the I/O capacity of the hardware,
or adding memory. For example, you can use Database Engine Tuning Advisor to analyze typical SQL Server
workloads and produce recommendations for indexes, indexed views, and partitioning to improve server performance
CPU/Processor Utilization :
Processor:% Processor Time ::
This counter monitors the amount of time the CPU spends executing a thread that is not idle.
A consistent state of 80 percent to 90 percent may indicate the need to upgrade your CPU or add more processors
For multiprocessor systems, monitor a separate instance of this counter for each processor.
This value represents the sum of processor time on a specific processor.
To determine the average for all processors, use the System: %Total Processor Time counter instead.
Corresponds to the percentage of time the processor spends on execution of Microsoft Windows kernel commands, such as processing of SQL Server I/O requests. If this counter is consistently high when the Physical Disk counters are high, consider installing a faster or more efficient disk subsystem.
Sp_monitor ::
gives the percentage usage of CPU,I/O, etc
refer http://msdn.microsoft.com/en-us/library/ms188912.aspx
SQL Server keeps track, through a series of functions, of how much work it has done.
Executing sp_monitor displays the current values returned by these functions and shows how much they have changed since the last time the procedure was run.
For each column, the statistic is printed in the form number(number)-number% or number(number).
The first number refers to the number of seconds (for cpu_busy, io_busy, and idle)
or the total number (for the other variables) since SQL Server was restarted.
The number in parentheses refers to the number of seconds or total number since the last time sp_monitor was run.
The percentage is the percentage of time since sp_monitor was last run. For example,
if the report shows cpu_busy as 4250(215)-68%, the CPU has been busy 4250 seconds since SQL Server was last started up,
215 seconds since sp_monitor was last run, and 68 percent of the total time since sp_monitor was last run.
Memory Usage :
Memory: Available Bytes ::
The Available Bytes counter indicates how many bytes of memory are currently available for use by processes.
Low values for the Available Bytes counter can indicate that there is an overall shortage of memory on the computer or that an application is not releasing memory.
Memory: Pages/sec ::
The Pages/sec counter indicates the number of pages that either were retrieved from disk due to hard page faults or written to disk to free space in the working set due to page faults.
A high rate for the Pages/sec counter could indicate excessive paging. Monitor the Memory: Page Faults/sec counter to make sure that the disk activity is not caused by paging.
To determine whether SQL Server or another process is the cause of excessive paging, monitor the Process: Page Faults/sec counter for the SQL Server process instance.
To monitor the amount of memory that SQL Server uses, examine the following performance counters:
Process: Working Set ::
The Working Set counter shows the amount of memory that is used by a process. If this number is consistently below the amount of memory that is set by the min server memory and max server memory server options, SQL Server is configured to use too much memory
SQL Server: Buffer Manager: Buffer Cache Hit Ratio ::
The Buffer Cache Hit Ratio counter is specific to an application. However, a rate of 90 percent or higher is desirable. Add more memory until the value is consistently greater than 90 percent. A value greater than 90 percent indicates that more than 90 percent of all requests for data were satisfied from the data cache.
SQL Server: Buffer Manager: Total Pages
SQL Server: Memory Manager: Total Server Memory (KB) ::
If the Total Server Memory (KB) counter is consistently high compared to the amount of physical memory in the computer, it may indicate that more memory is required.
SOURCE : http://msdn.microsoft.com/en-us/library/ms191246.aspx
+ others
Sharing with you World 