Friday, October 25, 2013

SQL Service start up problem after SAN splitting

Post SAN Splitting, the SAN drives were available on DR server but SQL service didn't come online.
Below error message was logged in the error log file.
Message: Starting up database 'model'.
Error: 9004, Severity: 21, State: 1.
An error occurred while processing the log for database 'model'.
If possible, restore from backup.
If a backup is not available, it might be necessary to rebuild the log.

Error: 945, Severity: 14, State: 2.
Database 'model' cannot be opened due to inaccessible files or insufficient memory or disk space.
See the SQL Server errorlog for details.
Could not create tempdb. You may not have enough disk space available.
Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server.
Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.

Tried to start the service in single user mode from command prompt, but same error message was logged.
Restored the model db backup file on other server with same physical file names and dettached the database.
Replaced the model database mdf files and ldf files with the dettached model db files. Now started the service and the services came online.

Monday, September 30, 2013

How to resolve issue with SQL configuration manager?

Got the Below error message while trying to invoke Configuration manager.

Error Message: Cannot connect to WMI Provider. You do not have permission or the server is unreachable. Note that you can only manage SQL server 2005 and later servers with configuration Manager. Inavlid namespace [0x8004100e].

Resolution : Need to search for .mof files in c:\program files\microsoft sql server folder.
The solution is to go to a command prompt and then run mofcomp.

C:\Program Files\Microsoft SQL Server\90\Shared>mofcomp "C:\Program Files\Microsoft SQL Server\90\Shared\sqlmgmproviderxpsp2up.mof"

The output will look like below Microsoft (R) 32-bit MOF Compiler Version 5.1.2600.2180 Copyright (c) Microsoft Corp. 1997-2001. All rights reserved. Parsing MOF file: C:\Program Files\Microsoft SQL Server\90\Shared\sqlmgmprovider xpsp2up.mof MOF file has been successfully parsed Storing data in the repository... Done! If it throws error then we need to rebuild the WMI repository as below:

net stop winmgmt
c: cd %systemroot%\system32\wbem
rd /S /Q repository
regsvr32 /s %systemroot%\system32\scecli.dll
regsvr32 /s %systemroot%\system32\userenv.dll
mofcomp cimwin32.mof
mofcomp cimwin32.mfl
mofcomp rsop.mof
mofcomp rsop.mfl
for /f %%s in ('dir /b /s *.dll') do regsvr32 /s %%s
for /f %%s in ('dir /b *.mof') do mofcomp %%s
for /f %%s in ('dir /b *.mfl') do mofcomp %%s
echo DONE

Monday, September 16, 2013

How to Reset permission for Public role in SQL server?

Few days back user called saying that they are getting the below error in their application.

"The SELECT permission was denied on the object 'sysobjects', database 'mssqlsystemresource', schema 'sys'"

As a temporary fix,we have given sysadmin to carry out their business. Later after troubleshooting we have identified that some how permissions for public role on the server were modified/corrupted. Only sysadmin role could resolve their issue. Other than that no other server role solved thier problem of accessing.

Immediately a question came to my mind "How to reset permission for Public role ?"

To reset the permission for Public role/Guest user, execute the below code on a good server which will provide you the complete rights for PUBLIC role. Copy the output and paste to the server with the issue.

SELECT SDP.state_desc , SDP.permission_name , SSU.[name] AS "Schema" , SSO.[name] , SSO.[type] FROM sys.sysobjects SSO INNER JOIN sys.database_permissions SDP ON = SDP.major_id INNER JOIN sys.sysusers SSU ON SSO.uid = SSU.uid ORDER BY SSU.[name] , SSO.[name] .

Note: Please do not perform this directly on production environment. Better test it in UAT prior to implementing it on Production server.