Service Pack 4 (SP4) for Microsoft SQL Server 2005 is now available for download.
SQL Server 2005 SP4 includes SQL Server 2005 SP3 cumulative update 1 to 11, customer requested fixes, along with instances of the SQL Server 2005 SP4 database Engine support for DAC operations.
It can be downloaded from
Error:
SQL Server has encountered 2 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
Cause:
When we take the database OFFLINE this error raised.
Reference: http://support.microsoft.com/kb/917828
I reproduced the error below;
Issue 1:
| Msg 916, Level 14, State 1, Line 1 The server principal "Test_Login" is not able to access the database "testdb" under the current security context. |
1. Created database ‘testdb’
2. Created the login ‘Test_Login’. The login not granted access on the database ‘testdb’
3. On logging on ‘Test_Login’, tried accesing the database ‘testdb’ and got the error.
From the error message, when the server principal ( login) does not have access on the database the error occurs.
Resolution 1:
Grant the database access for the login. For example;
| Use testdb Create User Test_Login for Login Test_Login |
Issue 2:
The database is in “Restricted Mode”
Try the query below, to access the database in different user context
| Use testdb |
Msg 916, Level 14, State 1, Line 1
The server principal "Test_Login" is not able to access the database "testdb" under the current security context.
Reference :
"The server principal LoginName is not able to access the database DatabaseName under the current security context"
Recently when i tried TSM TDPSQL backup with the new server on SQL Server 2008 failed with the error below.
“ TSM TDP SQL Error: Could not load file or assembly 'Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified. Process Exit Code 1914. The step failed. “
Resolution:
After investigation, installed the TSM TDP SQL version 5.5.4.0 from 5.5.0. This resolved the issue.
Are you curious to find the Database Backup / Restore status?
Do you have to find out the status the DBCC CHECKDB ?
Do you have to report when the ROLLBACK would finish ?
Do you want judge how long the Database Shrink will take?
Here is the answer!
The query listed below can be used to find the Percentage of work completed for the following commands:
We have to replace the where condition filter based on the requirement.
| SELECT command, |
The default behavior of SSMS when we login, it will show all the databases available on the SQL Server Instance. Irrespective of access on the databases user has.
The reason behind is, to load the SSMS faster. Otherwise, while loading the SSMS SQL Server has to verify for each databases if the user has access or not. Also if any of the databases or offline or closed, it has to figure out if that database has to be listed or not. All this takes time.
Hence by default the SSMS will show all the databases on SSMS.
If we really required to show only the database user has access, please use the command below to restrict the show all databases on SSMS,
DENY VIEW ANY DATABASE TO login_name
When VIEW ANY DATABASE is revoked, a user can only see master, tempdb, any database he owns, and the user’s current database context.
Error: 912, Severity: 21, State: 2
Microsoft has confirmed that this is a problem in the Microsoft products that are listed
This problem was first corrected in SQL Server 2008 Service Pack 1
Cause:
The upgrade process includes a script to create the SP_Syspolicy_Create_Purge_job stored procedure. The procedure creates the syspolicy_purge_history job and hardcodes the owner of the job to be the sa account. Because the sa account is renamed, the job creation fails.
Workaround: recreate the sa account
Here Let me show the step by step on SQL Server 2008 SP 2 Installation
Are you getting the error message below on a SQL Server Job?
Resolution:
See if the SQL Agent Service account have sufficient privileges.
I have encountered this error when I change the SQL Server Agent account.
Resolved giving proper rights on the folder.
The following steps show the processing order for a SELECT statement.
FROM
ON
JOIN
WHERE
GROUP BY
WITH CUBE or WITH ROLLUP
HAVING
SELECT
DISTINCT
ORDER BY
TOP
This is the reason where we can not use column alias on Where clause, however we can use it on Select / Order by.
If interested on the logical order of the query processing poster, download it from here.
Reference: http://msdn.microsoft.com/en-us/library/ms189499.aspx
Please refer the http://msdn.microsoft.com/en-us/library/ms143504.aspx
on setting up Windows Service Accounts.
It discuss about the
The default instance could be an installation of
SQL Server 2000,
SQL Server 2005,
SQL Server 2008, or
SQL Server 2008 R2.
Only one installation of SQL Server, regardless of version, can be the default instance at one time
SQL Server 2008 SP 2 released on 29 Sep 2010.
To download the SQL Server 2008 SP2 release please select from the links below:
Today I got the error below on the SQL Server job it runs a SSIS Package.
Error:
Msg 7302, Level 16, State 1, Server USIVA007, Line 1
Cannot create an instance of OLE DB provider "OraOLEDB.Oracle" for linked server
"SYS01_ORACLE01"
Resolution:
Strange but recreating of the Linked Server connection, resolved the issue.
This seminar series targeting the audience across the world. It is begin from Chennai, India.
What is unique about the seminar & why i am interested in it
What can we get out of the two day session;
| Day 1: Sep 24th 2010 | Day 2: Sep 25th 2010 |
| Resolution to popular misconception on SQL Server features | Evils of Index and workarounds |
| The unknown Tricks and Tips of sub queries | The Intelligence of Statistics |
| The Relation between External and Internal Joins | Query Optimization for unknown values |
| Triggers and Views | Query Optimization techniques |
| Question hour | Question hour |
Contacts : For clarifications, please call +91 98202 24429
Further details: http://www.solidq.in/Training/DirectionsSeminar/SQLChennai.aspx
About the Solid Quality Mentor:
Website: http://www.solidq.com/
About the seminar presenter – Pinal Dave:
Pinal Dave is a Database Architect and Corporate Trainer specializing in SQL Server Query Optimization and Performance Tuning having 7 years of rich, hands-on experience. He has been awarded as the Microsoft Most Valuable Professional (MVP) in SQL Server technology and is also a Microsoft Certified Trainer (MCT)
Website: http://blog.sqlauthority.com/
There is a whitepaper in detail SQL Server 2005 /2008 Maintenance for MOSS ( Sharepoint ).
The SQL Server 2008 R2 Update for Developers Training Kit is ideal for developers who want to understand how to take advantage of the key improvements introduced in SQL Server 2008 and SQL Server 2008 R2 in their applications, as well as for developers who are new to SQL Server. The training kit is brought to you by Microsoft Developer and Platform Evangelism.
Download it from here, it sizes 216.5 MB.
It contains PPT Slides, Video, Demo and Hand on Lab
Register or login to experience the SQL Server 2008 R2 virtual lab.
The environment also host the SharePoint 2010 and MS Office 2010.
http://www.previewsqlserver.com/index.htm
I attended the SQL Data Camp in Chennai and received a RedGate book as a token of one the most active participant of the event.
Are you one looking to find answer for questions like below.,
· How to find who dropped a database?
· How to find who created the database?
· How to find who altered the database?
· How to find who altered the database configurations?
· How to find who dropped the schema?
· How to find who altered the schema?
· How to find who altered the server configuration?
· How to find who modified the login?
· How to find who modified the table?
If it is then, are you wondering is that possible on SQL Server?
Yes, SQL Server can beginning from version SQL Server 2005 onwards.
That is where SQL Server Default trace come into picture.
What is SQL Server Default Trace?
Default trace provides troubleshooting assistance to database administrators by ensuring that they have the log data necessary to diagnose problems the first time they occur.
To get the default trace options
SELECT * FROM ::fn_trace_getinfo(default)
To know what is audited on default trace
SELECT Trc.EventID, Trc.ColumnID, Evt.name as Event_Description, Col.name as Column_Description
FROM ::fn_trace_geteventinfo(1) Trc
JOIN sys.trace_events Evt ON Trc.eventID = Evt.trace_event_id
JOIN sys.trace_columns Col ON Trc.columnid = Col.trace_column_id
Example:
Below is the screenshot “Schema Changes History” of Standard Reports at Server Level.
See that “it shows the event of Drop and Alter of the database
The same can be queried from SQL Server Default Trace like;
select * from fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Log\log.trc',DEFAULT)
where EventClass in (46,47,164) and EventSubclass = 0 and DatabaseID <> 2
Usage
Default trace can be queried to find answers on
Points to ponder
--- Draw Circle in SQL Server 2008
declare @g Geometry
set @g = 'Point(10 5)'
select @g.STBuffer(5)
Error
• Msg 170, Level 15, State 1, Server MS, Line 1
Line 1: Incorrect syntax near '-'.
The sqlsp.log file indicates:
Process Exit Code: (0)
Error running script: sp_vupgrade_replication (1)
Action CleanUpInstall:
Installation Failed.
Resolution:
1. Check if distribution database exist though the Replication is dropped
2. If so drop the distribution database
3. To drop the distribution database by over - restoring of some user database
4. Perform the patching / SQL SP again.
Error:
Error Number : 29537
Error Description : MSP Error: 29537 SQL Server Setup has encountered the following problem: [Microsoft][SQL Native Client][SQL Server]CREATE DATABASE failed.
Some file names listed could not be created. Check related errors.. To continue, correct the problem, and then run SQL Server Setup again.
CAUSE:
By default the setup creates the Temp_MS_AgentSigningCertificate_database Database on specified default database location,
If the location doesn't exist, it fails to create the database and the setup fails.
RESOLUTION:
1. In SSMS, go to Server Properties > Database Settings > Database Default Locations ,check default location for Data and Log .
2. Try to access locations specified at Data and Log, Make sure files are available there.
3. If any of the files missing from above location , we hit the above error.
4. In that case, change the path to point to reall location of mdf/ldf files .
5. Setup should now succeed.
1. Reference: a recently applied update, kb955706, failed to install
To delete te
--Delete all .bak files in the named directory and its subfolders where the file modified date is more than 2 days old.
| EXEC xp_cmdshell 'FORFILES /p "L:\MSSQL$PRODSQL2KWEB\Backup" /s /m *.bak /d -2 /c "CMD /C echo Deleting the file : @file del /Q /F @FILE && del /Q /F @FILE " ' |
/*
FORFILES Parameters
Parameter Name Description
/p Path
/m Search Mask (default is *.*)
/s Subdirectories will be searched recursively if this parameter is included
/c <command> Command to be executed against each file in the result set, commands must be enclosed in double-quotes, default is "cmd c/ echo @file"
/d Date range for file selection, using Last Modified Date as the criterion for the file. When the /d parameter is in the form of MM/DD/YYYY, file meeting the criteria of +/- the specified date are included. When in the format of a smallint (-32,768 - 32,768) the files +/- the files with a modified date +/- that number of days from the current date are included in the file result set.
Variables
Variable Name Description
@FILE File name
@FNAME File name without extension
@EXT File extension
@PATH Full path of the file
@RELPATH Relative path of the file
@ISDIR Evaluates as TRUE if the file type is a directory
@FSIZE File size in bytes
@FDATE Last modified date stamp on the file
@FTIME Last modified timestamp on the file
*/
In starting with SQL Server 2005 and above a SQL Server user or login does not need to have SYSADMIN privileges to use the SQL Server Profiler.
It can be accomplished by,
| GRANT ALTER TRACE to [Login] |
BOL explanation:
By default, running SQL Server Profiler requires the same user permissions as the Transact-SQL stored procedures that are used to create traces. To run SQL Server Profiler, users must be granted the ALTER TRACE permission
References: Permissions Required to Run SQL Server Profiler
------ Query to find when the User Table Last accessed ---------
WITH LastActivity (ObjectID, LastAction)
AS
(
SELECT object_id AS TableName, Last_User_Seek as LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = db_id(db_name())
UNION
SELECT object_id AS TableName,last_user_scan as LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = db_id(db_name())
UNION
SELECT object_id AS TableName,last_user_lookup as LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = db_id(db_name())
)
SELECT OBJECT_NAME(so.object_id)AS TableName,
MAX(la.LastAction)as LastSelect
FROM
sys.objects so
LEFT JOIN LastActivity la
ON so.object_id = la.ObjectID
WHERE so.type = 'U'
AND so.object_id > 100
GROUP BY OBJECT_NAME(so.object_id)
ORDER BY OBJECT_NAME(so.object_id)
------ Query to find when the Database Last accessed ---------
SELECT name, last_access =(
select X1= max(bb.xx)
from ( select xx =
max(last_user_seek)
where max(last_user_seek)is not null
union all
select xx = max(last_user_scan)
where max(last_user_scan)is not null
union all
select xx = max(last_user_lookup)
where max(last_user_lookup) is not null
union all
select xx =max(last_user_update)
where max(last_user_update) is not null) bb)
FROM master.dbo.sysdatabases d
left outer join sys.dm_db_index_usage_stats s
on d.dbid= s.database_id
group by d.name
SQL Server Query to deny user on remote login feature, let user connect to database engine only from localhost (or 127.0.0.1).
This query below applies to SQL Server 2005 with SP2 and above higher version. It uses the SQL Server Logon Trigger
| CREATE TRIGGER Deny_Remote_Login_Login1_trigger ON ALL SERVER WITH EXECUTE AS 'sa' FOR LOGON AS BEGIN IF ORIGINAL_LOGIN()= 'Login1' AND (SELECT * FROM sys.dm_exec_sessions WHERE is_user_process = 1 AND original_login_name = 'Login1' --- Checking the given login and Host_Name not in ( 'SQLServerHostName') --- Connecting Remotely and Session_ID = @@SPID ) BEGIN PRINT 'RemoteLogin Attempt attempt of ' + ORIGINAL_LOGIN()+' denied, please login locally' ROLLBACK; END END;
|
The SQL Server System Configuration Checker cannot be executed due to WMI configuration on the machine Error:2147749896 (0×80041008).
Resolution: Copy and run the DOS batch file below
| @echo on |
1. On SQL Server 2000 connect to Enterprise Manager
On SQL Server 2005 / 2008 connect to SQL Server Management studio
2. Expand Replication à Publication, select the Publication from which the subscribers to be removed.
On SQL Server 2005 / 2008 from the Replication à Local Publication select the Publication
3. Right click on the Publication
4. Select the Subscription tab
5. Select the Subscriber and click Delete button, that’s it
6. If you hold the “Shift” key you can delete more than one subscriber. However you have to execute the sp_subscription_cleanup each subscribers manually.
7. Rather if you delete one subscriber at a time, it is simple and no need to execute the stored procedures at subscriber.
1. Backup of all the System + User Database
2.Windows Login account with "Local Administrator" privileges on all the nodes involved
3. Ensure all the recourses part of the SQL Server Cluster resource group are online.
I recommend to reboot both nodes before installation. (to avoid pending reboot, hanging installers, terminal sessions etc. problems.)
4. Cross check if the cluster failover on the nodes involved successfully.
5. shut down all applications that use SQLServer (because you know the instance(s) will get offline at some point(s) during the upgrade)
6. Disable if any DDL Triggers
7. Verify if following services online on the nodes involved.
8. Pause Database Mirroring
Log on the active node, apply the Service pack. ( It is opposite in case of SQL Server 2008 ! ). You cannot install SP3 from any passive nodes in the failover cluster.
Please note SP will not update the client tools on the passive node. you need to do that manually; though it is cluster aware.
Reference:
How to install SQL Server 2005 SP3
SQL Server 2005 SP3 Release Notes
SQL Server 2008 failover cluster rolling patch and service pack process
Microsoft makes it easier than ever to attend SQL Server 2008 training, at little to no cost, without any travel necessary.
It just needed a email account on hotmail or msn.
Subject matter includes:
· Database Administration
· Database architecture
· Database, programming and security.
Click the courses below to see full descriptions or visit Microsoft Learning to browse the course catalog
Use Command below to connect as DAC
SQLCMD -A |
It is on by Default for local server.
For remote(via network) enable it by running query below
sp_configure |
For Clusters, recommended to enable remote admin connections as no local DAC available
---- Query to Find if DAC - Dedicated Admin Connection used -----
select s1.session_id, s1.program_name,s1.host_name,t1.name from sys.tcp_endpoints t1 join on t1.endpoint_id = s1.endpoint_id where t1.name = --- End of the Query ----- |
--- Query to check the DAC ---- --- Scheduler_ID = 255 & Status = "VISIBLE ONLINE (DAC) --- select |
Usage:
when SQL Server hangs, SQL Server does not respond, when SQL Server does not accept new connection
Applies to | Version Range | Type | Description | Target Version | Release Date | Security Bulletins | KB | Download | |
SQL Server 2000 | Below 8.00.2039 | Service Pack | SQL Server 2000 SP4 | 8.00.2039 | 6-Jun-05 | ||||
SQL Server 2000 | 8.00.2039-8.00.2054 | GDR | SQL Server 2000 SP4 GDR | 8.00.2055 | 10-Feb-09 | ||||
SQL Server 2000 | 8.00.2148-8.00.2281 | QFE | SQL Server 2000 SP4 QFE | 8.00.2282 | 10-Feb-09 | ||||
SQL Server 2005 | Below 9.00.4035 | Service Pack | SQL Server 2005 SP3 | 9.00.4035 | 15-Dec-08 | N/A | |||
SQL Server 2005 | 9.00.4035-9.00.4052 | GDR | SQL Server 2005 SP3 GDR | 9.00.4053 | 12-Oct-09 | ||||
SQL Server 2005 | 9.00.4205-9.00.4261 | QFE | SQL Server 2005 SP3 QFE | 9.00.4262 | 12-Oct-09 | ||||
SQL Server 2008 | Below 10.00.2531 | Service Pack | SQL Server 2008 SP1 | 10.00.2531.00 | 27-Aug-09 | N/A |
| ||
It lists only the latest SQL Server Service Pack and Security Hot fixes as on 22nd April 2010 | |||||||||
Software Inventory Assistance
Whether we have just a few computers or a large network, a software inventory tool can make it easier to quickly know what we have and how it's being used.
The Microsoft Software Inventory Analyzer (MSIA) is a free tool available for download that can help us with our software inventory. It is built specifically to be a starting point to working with SAM.
We can use the MSIA to scan and inventory the Microsoft software that is installed on a single computer, or on multiple computers throughout a network. It generates a report that provides details of all installed Microsoft products, including the type and the number of licenses. The MSIA will work with networks that have 250 or fewer computers. The latest MSIA 5.1 features enable you to:
This information is useful for you, if you are taking care of the system administration, licensing and inventory of Microsoft software products in your organization.
BgInfo – Microsoft Windows SysInternal tool
How many times have you walked up to a system in your office and needed to click through several diagnostic windows to remind yourself of important aspects of its configuration, such as its name, IP address, or operating system version If you manage multiple computers you probably need BGInfo. It automatically displays relevant information about a Windows computer on the desktop's background, such as the computer name, IP address, service pack version, and more. You can edit any field as well as the font and background colors, and can place it in your startup folder so that it runs every boot, or even configure it to display as the background for the logon screen.
Below is the TSQL Query to find when the database last accessed for SQL Server 2005 / 2008
-- Query to find when the database last accessed on SQL Server 2005 / 2008 --- select d.name, x1 = (select X1= max(bb.xx) from ( select xx = max(last_user_seek) where max(last_user_seek) is not null union all select xx = max(last_user_scan) where max(last_user_scan) is not null union all select xx = max(last_user_lookup) where max(last_user_lookup) is not null union all select xx = max(last_user_update) where max(last_user_update) is not null) bb) FROM master.dbo.sysdatabases d left outer join sys.dm_db_index_usage_stats s on d.dbid= s.database_id group by d.name |
![clip_image002[4]](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjDgo1WzBTP8mQskJ6HwXhcQBLFoco0LUaGWHbTPM455bRFukXHUOnVkOK7eAgxEtYURmiF8jVp9hvd5f7anGbUeoHourc255Z4NvpvHRwq7BXPUkkn6TSshwgA2Co6jm_sou0XGA/s1600-h/clip_image002%5B4%5D%5B2%5D.jpg "clip_image002[4]")
