Space Unused:
Are pages allocated on extends, however this pages are not used yet by any kind of objects. It means, SQL Server has 8 pages by each extent. If an object reside in a page of a certain extend, this space is allocated and used, but the rest of the space of the extend is allocated but unused because there are not objects.
Space Unallocated:
Space (extends) that is not in use, it means extends that are not allocated by SQL Server. Any kind of grow will produce a space unallocated.
Problem.
Overall summary:
Final result: Failed: see details below
Exit code (Decimal): 2064843076
Exit facility code: 787
Exit error code: 324
Exit message: MsiGetProductInfo failed to retrieve ProductVersion for package with Product Code = '{8BDD006D-7863-4D25-9806-08527E285EA4}'. Error code: 1605.
Start time: 2009-07-28 18:30:43
End time: 2009-07-28 18:31:14
Requested action: ComponentUpdate
Log with failure: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20090728_182952\Detail_ComponentUpdate.txt
Exception help link: http%3a%2f%2fgo.microsoft.com%2ffwlink%3fLinkId%3d20476%26ProdName%3dMicrosoft%2bSQL%2bServer%26EvtSrc%3dsetup.rll%26EvtID%3d50000%26ProdVer%3d10.0.1600.22%26EvtType%3d0x4E099C65%400x7B130144
The error happened during the installation of a SQL Server 2008.
Solution / Fix / Workaround
The above remark line show us the product code related with the error. First, is necessary to know that SQL Server 2008 has around of 30 products registered on the Registry. Each product has a Product code and an installation code as well (GUID).
For this case the Product Code is the following:
{8BDD006D-7863-4D25-9806-08527E285EA4}
The Installation Code is build with the product code in reverse form:
D600DD8368752D460894AE582E72580
One time that you got the Installation Code look into the Windows Registry and delete all the entries found.
Then try again the installation and the result must to be SUCCESSFULLY.
BACKUP DATABASE db_name TO disk='C:\db_name.bkp' WITH COPY_ONLY
This option COPY_ONLY, can be performed for any backup type. COPY_ONLY has certains behaviors depending of the backup type:
* A log backup taken using copy_only, does not truncate the transaction log.
* A data backup taken using copy_only cannot be used as base backup for differential or incremental backups.
* A differential backup has not effect with copy_only option.
This option is present when sp_configure is executed or when sys.configuration is queried, but this functionality is unavailable since SQL Server 2005, it is obsolete. Direct updates to system objects are not allowed. If sp_configure is used to enable the option, this configuration has not effect. In previous versions as SQL Server 2000 this functionality is available.
Testing the option
The following code enable the option “allow updates”, then a table is created and finally an update to system table is executed:
sp_configure 'allow updates', 1
GO
RECONFIGURE with override
GO
CREATE TABLE TBLTEST (TESTFIELD INT )
GO
update sys.objects
set name = 'TBLTEST_1'
where name = 'TBLTEST'
GO
When the update to system table is triggered the following error is shown:
Msg 259, Level 16, State 1, Line 1
Ad hoc updates to system catalogs are not allowed.
This option is not advanced option and is not necessary enable the “show advanced options”. To understand in a better way if a certain option is or is not advanced, if it is dynamic or is necessary restart, please refer SQL SERVER – Server Configurations Options – Catalog View – sys.configurations
SQL Server 2005 introduces Dynamic Management View, one of this objects allows us monitoring the progress for different tasks and process in SQL Server.
select percent_complete, estimated_completion_time, cpu_time, total_elapsed_time, *
from sys.dm_exec_requests
this query is very helpful in the daily work to monitoring different tasks.
The Server configuration option called Agent XPs is used to enabled the SQL Server Agent extended stored procedures on the server. When this option is disabled, the SQL Server Agent node is not enabled in SQL Server Management Studio and you can not use the SQL Server Agent Service.
When the SQL Server agent is restarted, the extended stored procedures are enabled automatically.
To enabled this option:
sp_configure 'show advanced options', 1
GO
RECONFIGURE with override
GO
sp_configure 'Agent XPs', 1
GO
RECONFIGURE with override
GO
sp_configure 'show advanced options', 0
GO
RECONFIGURE with override
GO
This option is an advanced option and is necessary enable first the “show advanced options”. To understand in a better way if a certain option is or is not advanced, if it is dynamic or is necessary restart, please refer SQL SERVER – Server Configurations Options – Catalog View – sys.configurations
The sys.configuration catalog view allow us to see the same values than sp_configure with “show advanced options” enabled. Even this Catalog view show us extra information because is possible to know if an option is or is not an advanced option, or if is or is not dynamic option.
Important Columns,
Value: Configured Value for the option
Minimum: Minimum Value for the option
Maximum: Maximum Value for the option
Value_in_use: current running value for the option.
Is_dynamic: it column allow us to know if the option is dynamic or not. 1 = the value takes effect when the RECONFIGURE statement is executed. 0 = the value takes effect when the SQL Server is restarted.
Is_advanced: it column allow us to know if the option is advanced. 1 = advanced option, it means that the option is displayed and can be changed just when “show advanced options” is set through sp_configure.
Now we are going to see statements using sys.configurations that are equivalent than sp_configure.
Normal View:
Using sys.configurations
select * from sys.configurations where is_advanced = 0
Using sp_configure:
sp_configure 'show advanced options', 0
GO
RECONFIGURE with override
GO
sp_configure
GO
Advanced Option View:
Using sys.configurations
select * from sys.configurations where is_advanced = 1
Using sp_configure:
sp_configure 'show advanced options', 1
GO
RECONFIGURE with override
GO
sp_configure
GO
Dynamic Option View
Using sys.configurations
To see all the dynamic options:
select * from sys.configurations where is_dynamic = 1
To see all the non dynamic options:
select * from sys.configurations where is_dynamic = 0
Using sp_configure is impossible to know if an option is dynamic.
Conclusion.
Using sys.configurations to see the server configuration is better than sp_configure because with a single select statement is possible to view all the server options with extra information as is_dynamic and is_advanced values, this extra information is impossible to obtain with sp_configure. Any changed necessary on the server configuration has to be performed with sp_configure.
To force a database to bring OFFLINE you can use the next statement:
alter database DBNAME set offline with rollback immediate
this is a quickly way to force a database to set it OFFLINE, when you use this statement a rollback occurs for all the transactions running.
Problem.
OLE DB provider "SQLNCLI" for linked server "(null)" returned message "Invalid authorization specification".
OLE DB provider "SQLNCLI" for linked server "(null)" returned message "Invalid connection string attribute".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "(null)" reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "(null)".
The error happened when the OPENROWSET statement was used.
The syntax used for the error is the following.
select * from openrowset('SQLOLEDB.1','Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=db_test2;Data Source=.\bkp','select * from dbo.table2')
Solution / Fix / Workaround
The right syntax for my query was the next one:
select * from
openrowset('SQLNCLI','Server=.\bkp;Uid=test_user;Pwd=test;Database=db_test2;','select * from dbo.table2')
When Adhoc Distributed Queries Options is enable (value = 1), SQL Server allow use Ad hoc queries through OPENROWSET and OPENDATASOURCE functions using OLEDB provider.
OPENROWSET and OPENDATASOURCE use OLE DB providers, this functions must be used only for data that is infrequently accessed. For data that is frequently accessed, is better use linked servers.
Example:
I have two SQL Server instances:
The first one is the localhost (.), I am going to called Server 1, and the second one is a SQL Server named instance localhost\BKP (.\BKP), I am going to called Server 2.
Server 2.
create database db_test2
GO
use db_test2
GO
create table table2
(
field1 int,
field2 varchar(10)
)
go
insert into table2 values (1,'test')
GO
Server 1.
On the server 1, I check and the Ad Hoc Distributed Queries is disabled. ( run_value = 0 )
sp_configure 'Ad Hoc Distributed Queries'
/*
Result
name minimum maximum config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
Ad Hoc Distributed Queries 0 1 0 0
*/
Then, I test my Ad hoc query using OPENROWSET.
select * from
openrowset('SQLNCLI','Server=.\bkp;Uid=test_user;Pwd=test;Database=db_test2;','select * from dbo.table2')
I got the following error because the Ad Hoc Distributed Queries option is not enabled.
Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.
To execute the Ad hoc query is necessary enable the option and try again the query.
sp_configure 'Ad Hoc Distributed Queries',1
GO
reconfigure with override
GO
/*
Result.
Configuration option 'Ad Hoc Distributed Queries' changed from 1 to 1. Run the RECONFIGURE statement to install.
*/
select * from
openrowset('SQLNCLI','Server=.\bkp;Uid=test_user;Pwd=test;Database=db_test2;','select * from dbo.table2')
/*
Result.
field1 field2
----------- ----------
1 test
(1 row(s) affected)
*/
Days ago I was working to find out the root cause of a SUSPECT state in a productive database. When I started to work in the database, it was already online. Somebody restart the MSSQL service and it bring online the database, but nobody knows what was the cause of the issue, it task is my job. :-)
Please be aware that the database name, data file or log file names are not the real values.
1. First I saw that the size of Transaction Log was several times the size of the entire db. It is bad, but this is not the cause of the suspect database.
2. I started to checking the ERRORLOG and I saw the system was configured to archive almost 60 ERROLOG. It is strange.
4. When I was checking the ERROLOG.10, I saw the following error:
2009-07-03 22:40:02.78 spid51 udopen: Operating system error 32(The process cannot access the file because it is being used by another process.) during the creation/opening of physical device c:\Productive_DB_LOG_FILE.ldf. 0
2009-07-03 22:40:02.78 spid51 FCB::Open failed: Could not open device c:\ Productive_DB_LOG_FILE.ldf for virtual device number (VDN) 2.
In the first error I can see that the log file can not be blocked by SQL Server. We knows that SQL Server block the data files and log files when it try to start the database. The issue here is, the database was not offline at any moment. The system was working during long period of time and I did not see any message where the database was offline. So, if the database was ONLINE, what was the cause of the log file was not blocked by SQL Server?
At this moment I knew that the cause of the database was in SUSPECT state, was because a log file for the database was blocked by other process and can not be blocked by SQL Server when it try start the database.
5. When I was checking the ERRORLOG files, I saw several messages (not errors) as the following.
2009-07-03 22:33:08.85 spid51 Analysis of database 'PRODUCTIVE_DB' (9) is 100% complete (approximately 0 more seconds)
2009-07-03 22:40:02.74 spid51 Starting up database 'PRODUCTIVE_DB'.
Mmmmmm Starting up database several times?,
What is the cause that the database is starting in several times? I thought in the option AUTO_CLOSE at db level. SQL Server has an option that close the database automatically when the last user close his connection to database. It option is AUTO_CLOSE.
6. Then I checked if the AUTO_CLOSE is enabled with the next statement:
SELECT DATABASEPROPERTYEX('PRODUCTIVE_DB','ISAUTOCLOSE')
Result.
1
If IsAutoClose is 1, the option is enabled.
As per SQL Server BOL.
IsAutoClose: Database shuts down cleanly and frees resources after the last user exits.
7. Conclusion.
At this moment I know that AUTO_CLOSE option is enable. So, if this option is enable, it cause that SQL Server frees the resources of the database after the last user finish his connection and when a new user is connected to the database, it is started and it generate a message of “Starting Database” in the ERROLOG files. Hence, I am ready to conclude: AUTO_CLOSE option is enabled, this option caused that all resources are frees and caused that the data files and log files of the database are not blocked anymore, then this is cause that another process was able to block the log file and it happened, finally, when SQL Server try to start the database and block the log file again, it was not possible and SQL Server marks in SUSPECT state the PRODUCTIVE_DB.
