How to restore SQL Server (32 Bit) performance counters in Win 64Bit?

If we have x64 Windows and X86 SQL.

In this case, for looking into X86 instances of SQL, we need to use the 32 bit equivalent of Performance Monitor. We can find that in C:\Windows\SysWOW64 or open that from RUN Command by typing the following.

perfmon /32

Now how we do we load the Performance Objects and counters back (in case they are not present. I guess that’s not present and that’s what led you to this post in the first place.)

1) First unload the counters, to remove any remnants. We use the unlodctr component(shipped with Windows) to do that.

We need to run the following from the Run command.

unlodctr mssql$<instance name>

--> If we are dealing with a default instance, then we need to run

unlodctr mssqlserver

If it is not loaded, we will get a well self explanatory message.

2) Then we need to identify the proper INI file using which we would load the counters. The actual path for the counter can be located in the following location

HKLM\SYSTEM\Services\Currentcontrolset\Services\MSSQL$<instance name>\Performance\

The Key in this Location would be PerfIniFile

(For default instance HKLM\SYSTEM\Services\Currentcontrolset\Services\MSSQLServer\Performance\PerfIniFile)

For Named Instance the file name would be in the format perf-<instancename>sqlctr.ini

For Default instance it would be in the form of sqlctr.ini

The file is present in the Binn Directory for the respective instance. Using the Windows Desktop Search facility we might save ourselves some time, if we are not sure where to find the specific file.

 

3) Now use the lodctr utility (from RUN Command) to load the Objects and counters using the identified INI file from above step. We need to specify the complete path for the INI file as the only option for lodctr.

lodctr <complete path for the ini file identified from the above steps)

On a clustered instance the unlodctr and lodctr works the same way, its only that we might have to failover the SQL to each node and run the same steps on each node.

That's all :)

Note: There may be some other causes as well but here I presented the most typical cause of disappearing SQL performance counters and the steps how to restore them. 

URL: http://mssql-tech.blogspot.com/2011/12/how-to-restore-sql-server-32-bit.html

For further details please refer to the links below:

Other References:

http://www.sqlcoffee.com/Tuning02.htm

http://support.microsoft.com/kb/227662

http://blogs.msdn.com/b/sqlserverfaq/archive/2009/03/18/what-should-we-do-when-the-sql-server-performance-counters-are-missing.aspx

How to resolve 'File system error: A FileStore error from WriteFile occurred. Physical file...' error message?

Cubes processing failed in SSAS 2008 R2

Error:

File system error: A FileStore error from WriteFile occurred. Physical file:  \\?\L:\Microsoft SQL Server\MSSQL.3\OLAP\Data\MSMDCacheRowset_xxx.tmp.
Logical file:

Workaround:

Check the dimensional property "ProcessingGroup" for all the relevant dimensions. If it's set to 'ByTable' try setting it to 'ByAttribute'

 

The reason this can cause processing issues with large dimensions (# of members, # of attributes, etc.) is because when using the ByTable setting, it will try to put the entire dimension into memory.

URL: http://mssql-tech.blogspot.com/2011/11/how-to-resolve-file-system-error.html


References:

http://stackoverflow.com/questions/420193/process-dimension-fails-with-message-a-filestore-error-from-writefile-occurred

http://social.msdn.microsoft.com/forums/en-US/sqlanalysisservices/thread/709ca808-12b1-49cd-9013-7f354a1b26b0/

http://mssqlbase.blogspot.com/2010/04/process-olap-dimensions-error-file.html

http://blogs.msdn.com/b/sqlcat/archive/2009/04/01/using-processinggroup-dimension-property-option-bytable-vs-byattribute-may-error-with-string-keys.aspx

How to resolve 'Saving changes is not permitted' error message in SQL Server 2008 R2?

You came across with this error in SQL Server Management Studio  everytime when you made some modifications in a table, i.e. adding relationships.

Error:

Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created

Workaround:

This is by design and can be quickly fixed in Management Studio by unchecking a property. To fix this in Management Studio, go to Tools -> Options then go to the Designer Page and uncheck "Prevent saving changes that require table re-creation".

URL: http://mssql-tech.blogspot.com/2011/11/how-to-resolve-saving-changes-is-not.html

Other References:

http://weblogs.asp.net/pabloperalta/archive/2008/09/23/sql-2008-quot-saving-changes-is-not-permitted-quot-error-message.aspx

How to troubleshoot Fatal error 605?

Problem:

While running queries you get the below error message.

Server: Msg 21, Level 21, State 1, Line 1

Warning: Fatal error 605 occurred at ....

Further checking the windows application event logs you find the below error message.

Attempt to fetch logical page %S_PGID in database '%.*ls' belongs to object '%.*ls', not to object '%.*ls'.

Cause:

This error occurs when Microsoft® SQL Server™ detects database corruption. The second object specified in the text not to object '%.*ls' is probably corrupt.

Solution:

Execute DBCC CHECKDB as soon as possible.

1) First try to drop the tables involved in the error message and recreate those tables from scratch.
OR
2) Extract all the data out into a new database.
OR
3) Use DBCC PAGE to try to figure out which tables in the database are affected and drop their statistics.

URL: http://mssql-tech.blogspot.com/2011/11/how-to-troubleshoot-fatal-error-605.html

Other References:

Check out the below links for more details on this topic.

http://msdn.microsoft.com/en-us/library/aa258728(v=sql.80).aspx
http://www.sqlservercentral.com/Forums/Topic967852-266-1.aspx
http://connect.microsoft.com/SQLServer/feedback/details/469811/attempt-to-fetch-logical-page-in-database-2-failed-it-belongs-to-allocation-unit-not-to

How to enable xp_cmdshell in SQL Server 2008?

Problem:

SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.

Cause:
The xp_cmdshell option is a server configuration option that enables system administrators to control whether the xp_cmdshell extended stored procedure can be executed on a system.

Solution:

- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO


- To update the currently configured value for advanced options.
RECONFIGURE
GO


- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO


- To update the currently configured value for this feature.
RECONFIGURE
GO


Reference: Zubair Ahmed
http://mssql-tech.blogspot.com/2011/11/how-to-enable-xpcmdshell-in-sql-server.html

Other References:

Check out the below links for more details on this topic.

http://technet.microsoft.com/en-us/library/ms190693.aspx
http://www.sqlservercentral.com/blogs/brian_kelley/archive/2009/11/13/why-we-recommend-against-xp-cmdshell.aspx

How to attach SQL 2000 database (MDF) without a LDF file?

Repairing DB In Emergency Mode (By-Pass Recovery Mode)

Problem(s):

How to attach the MDF file using query analyzer?

How to bring database back on operation when the LDF file is damaged?

How to By-Pass Recovery Mode and bring the database online back through ‘Emergency Mode’?

Solution:

If  we need to ignore the current LDF file and if the database is not detached properly and if you do not have the backup, follow the below steps to bring up your database. In the below step-7 is a undocumented DBCC command.

1. Create a new database with the same name and same MDF and LDF files
2. Stop sql server and rename the existing MDF to a new one and copy the original MDF to this location and delete the LDF files.
3. Start SQL Server.
4. Now your database will be marked suspect.

Update the sysdatabases to update to Emergency mode. This will not use LOG files in start up.

sp_configure 'allow updates',1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where dbid=DB_ID('DB_NAME')
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO

- Restart sql server. Now the database will be in Emergency mode

- Now execute the undocumented DBCC to create a log file.

dbcc rebuild_log('DB_NAME','G:\SQL_Server_DBs\MSSQL$BDC_BRS003\Data\User Databases\DB_NAME_log.ldf')
go
dbcc checkdb('DB_NAME') -- to check for errors
go

• Now run the below command.
Execute sp_resetstatus DB_NAME

• Restart SQL server and see the database is online.
 
URL: http://mssql-tech.blogspot.com/2011/11/how-to-attach-sql-2000-database-mdf.html


Other References:

http://oldlight.wordpress.com/2007/05/04/hello-world/ http://groups.google.com/group/microsoft.public.sqlserver.server/browse_frm/thread/289d7bb97da73312?hl=en&lr&ie=UTF-8&rnum=1&prev=/groups?sourceid%3Dnavclient%26q%3Djasper%2Bsmith%2Battach%2Blog&pli=1 http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server/13235/suspect-database-is-sql-2000 http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=78376 http://solution-mdf-file-corruption.blogspot.com/2009/12/how-to-repair-mdf-files-not-detached.html

How to check the version and service pack details in SQL Server?

We may use the below script to get the version details.

select @@version

Check out http://support.microsoft.com/kb/321185 for more details.

Reference: Zubair Ahmed (http://mssql-tech.blogspot.com/)

How to get product key details in SQL Server 2008 R2?

We may use the below script to extract product key details in SQL Server 2008 R2

USE master
GO
EXEC xp_regread 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\Microsoft SQL Server\100\BIDS\Setup','ProductID'
GO

Reference: Zubair Ahmed (http://mssql-tech.blogspot.com/)

How to enable the sa login in SQL Server 2008 R2?

If you don't see the 'sa' account in SQL server 2008 under security node even you have already enabled ' SQL Server and Windows Authentication mode' then Just follow the steps below to enable 'sa' login in SQL Server 2008 R2.

Method 1 - Enable the sa login by using Transact-SQL:

ALTER LOGIN sa ENABLE ;
GO
ALTER LOGIN sa WITH PASSWORD = '<enterStrongPasswordHere>' ;
GO

 

Method 2 - Enable the sa login by using Management Studio:

1. In Object Explorer, expand Security, expand Logins, right-click sa, and then click Properties.
2. On the General page, you might have to create and confirm a password for the sa login.
3. On the Status page, in the Login section, click Enabled, and then click OK.

Check out http://msdn.microsoft.com/en-us/library/ms188670.aspx for more details.

Reference: Zubair Ahmed (http://mssql-tech.blogspot.com/)