Archive

Archive for the ‘Common’ Category

Token-based server access validation failed with an infrastructure error, Error: 18456

Login failed for user <Server name>\User1′. Reason: Token-based server access validation failed with an infrastructure error. Check

http://blogs.msdn.com/b/sqlserverfaq/archive/2010/10/27/troubleshooting-specific-login-failed-error-messages.aspx

 

Categories: Common

sp_rename will not change sys.syscomments table definition.


exec sp_rename 'usp_test','uspp_test'
select text from sys.syscomments
where id=object_id('uspp_test')

The text column is still showing as “create procedure usp_test”.

It breaks to run “sp_refreshsqlmodule”

Invalid object name ‘dbo.uspp_test’.

To be able to resolve it, run again “alter procedure ” statement.

Categories: Common

Excel import using XML and T-SQL

Categories: Common Tags: ,

TempDB Misconceptions around TF 1118

http://www.sqlskills.com/blogs/paul/misconceptions-around-tf-1118/

[Edit 2012:] 4a) What is Paul’s recommendation for using trace flag 1118? Everyone should turn it on, on all instances of SQL Server, from SQL Server 2000 onwards. There’s no down-side to having it turned on.

http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-1230-tempdb-should-always-have-one-data-file-per-processor-core/

But there’s now even better guidance, and what I also recommend. At PASS in 2011, my good friend Bob Ward, who’s the top guy in SQL Product Support, espoused a new formula: if you have less than 8 cores, use #files = #cores. If you have more than 8 cores, use 8 files and if you’re seeing in-memory contention, add 4 more files at a time.

http://technet.microsoft.com/library/Cc966545

Use TF-1118. Under this trace flag SQL Server allocates full extents to each tempdb object, thereby eliminating the contention on SGAM page. This is done at the expense of some waste of disk space in tempdb. This trace flag has been available since SQL Server 2000. With improvements in tempdb object caching in SQL Server 2005, there should be significantly less contention in allocation structures. If you see contention in SGAM pages, you may want to use this trace flag. Cached tempdb objects may not always be available. For example, cached tempdb objects are destroyed when the query plan with which they are associated is recompiled or removed from the procedure cache.

Categories: Common Tags: , ,

Bulk load data conversion error (overflow) SQLINT (BCP)

2013/03/07 2 comments

http://msdn.microsoft.com/en-us/library/ms189110%28v=sql.90%29.aspx

Only SQLCHAR is allowed for fmt files if the file storage type is Char.

 

 

1 Data files that are stored in character format use char as the file storage type. Therefore, for character data files, SQLCHAR is the only data type that appears in a format file.

2 You cannot bulk import data into text, ntext, and image columns that have DEFAULT values.

When you bulk export data from an instance of SQL Server to a data file:

  • You can always specify char as the file storage type.
  • If you enter a file storage type that represents an invalid implicit conversion, bcp fails; for example, though you can specify int for smallint data, if you specify smallint for int data, overflow errors result.
  • When noncharacter data types such as float, money, datetime, or int are stored as their database types, the data is written to the data file in the SQL Server native format.
Categories: Common Tags: , ,

Paging file for SQL server

BACKUP DATABASE TO DISK = N‘NUL’

http://sqlservermct.wordpress.com/2011/10/07/backup-database-to-disk-n%E2%80%98nul%E2%80%99-%E2%80%93-and-misconceptions/

Veeam can truncate log base on some option.

http://www.veeam.com/vmware-backup/help-center/v6_1/hyperv/index.html?vmware_replica_guest_processing.htm

It make backup chain broken.
When they perform truncate log, you can see 2 of the log like this.

SQL VSS is normal behavior to create SnapShot on disk.
But, Veeam Backup and replication with truncate log isn’t good option to manage database.

I recommend that do not enable truncate log on Veeam replication manager.

select top 10 * 
  from msdb.dbo.backupset
 where is_snapshot=1

--It's due to SQL VSS.
--Program_Name : "Microsoft SQL Server VSS Writer"
--ex)
--BACKUP DATABASE [DBNAME] TO VIRTUAL_DEVICE='{4C5C20F9-FE32-4FCA-BE43-FFFFFFFFFFFF}1' WITH SNAPSHOT,BUFFERCOUNT=1,BLOCKSIZE=1024

select top 10 * 
  from msdb.dbo.backupmediafamily
 where physical_device_name='NUL'

--It's due to Veeam.
--Program_Name : "Veeam Backup and Replication"
--ex)
--BACKUP LOG [DBNAME] TO DISK = 'NUL'

uniqueidentifier : GUID in SQL

1. newsequentialid()

http://msdn.microsoft.com/en-us/library/ms189786%28v=sql.100%29.aspx

This is one of the function to generate sequence GUID to reduce fragmentation.

But, it’s not grantee uniqueness.
It’s wrapped by windows function UuidCreateSequential, http://msdn.microsoft.com/en-us/library/aa379322%28VS.85%29.aspx.

Creates a GUID that is greater than any GUID previously generated by this function on a specified computer since Windows was started.
After restarting Windows, the GUID can start again from a lower range, but is still globally unique.
When a GUID column is used as a row identifier, using NEWSEQUENTIALID can be faster than using the NEWID function.
This is because the NEWID function causes random activity and uses fewer cached data pages. Using NEWSEQUENTIALID also helps to completely fill the data and index pages.

2. uniqueidentifier sorting.

http://sqlblog.com/blogs/alberto_ferrari/archive/2007/08/31/how-are-guids-sorted-by-sql-server.aspx

3. uniqueidentifier converting

SELECT CONVERT(uniqueidentifier, 0x000102030405060708090A0B0C0D0E0F)
UNION ALL
SELECT CONVERT(uniqueidentifier, 0x00000000000000000123456789ABCDEF)
UNION ALL
SELECT CONVERT(uniqueidentifier, 0x000000000123456789ABCDEF00000000)
UNION ALL
SELECT CONVERT(UNIQUEIDENTIFIER, '00000000-0123-4567-89AB-CDEF00000000')

Cross-Database Access

Backup chain is broken due to Veeam Backup configuration improperly.

http://www.veeam.com/vmware-backup/help-center/v6_1/hyperv/index.html?vmware_replica_guest_processing.htm

Please select this one, “Do not truncate logs”.

——————————————————————————————————

Use the Truncation logs section to define the scenario of transaction log handing:

•Select Truncate logs on successful backup only if you want Veeam Backup & Replication to trigger truncation of logs only after the job is finished successfully. In this case, Veeam agent will wait for the replication to complete, and then will trigger truncation of transaction logs. If truncation of transaction logs is not possible for some reason, the logs will remain untouched in the VM guest OS till the next start of the Veeam agent.

•Select Truncate logs immediately if you want Veeam Backup & Replication to trigger truncation of logs in any case, no matter whether the job finishes successfully or fails.

•Select Do not truncate logs if you do not want Veeam Backup & Replication to truncate logs at all. This option is recommended if you are using another tool to perform guest-level replication and this tool maintains consistency of the database state. In such scenario, truncation of logs with Veeam Backup & Replication will break the guest-level replication chain and cause it to fall out of sync.

———————————————————————————————————————–

When I checked backup list, it’s truncate log like this.

BACKUP LOG xxxDB TO DISK = ‘NUL’

So, physical_device_name is presented as ‘NUL’.

select bs.*
	 , bf.*
  from msdb.dbo.dbo.backupset bs
  join msdb.dbo.[backupmediafamily] bf
    on bs.media_set_id = bf.media_set_id
 where 1=1
   and database_name='MyDB' and type='L'
 order by backup_start_date desc

Snapshot and Synonyms

Categories: Common Tags: ,

Dedicated Administrator Connection

http://www.brentozar.com/archive/2011/08/dedicated-admin-connection-why-want-when-need-how-tell-whos-using/

http://msdn.microsoft.com/en-us/library/ms178068%28v=sql.105%29.aspx

To use in SSMS please follow up those steps.

1. Enable SQL Server Browser.

Many of case for Network-Related error message is due to this reason.

2. Please use “Database Engine Query“, File>New> Database Engine query

This is keyword.

Do not open using  object explorer since DAC does NOT support multiple sessions.

3. Add “ADMIN:” in front of server name

ADMIN:localhost

DeadLock handling

Errorhandling

Categories: Common Tags: ,

RAISERROR : Database Engine Error Severities

http://msdn.microsoft.com/en-us/library/ms164086.aspx

Severity level Description
0-9 Informational messages that return status information or report errors that are not severe. The Database Engine does not raise system errors with severities of 0 through 9.
10 Informational messages that return status information or report errors that are not severe. For compatibility reasons, the Database Engine converts severity 10 to severity 0 before returning the error information to the calling application.
11-16 Indicate errors that can be corrected by the user.
11 Indicates that the given object or entity does not exist.
12 A special severity for queries that do not use locking because of special query hints. In some cases, read operations performed by these statements could result in inconsistent data, since locks are not taken to guarantee consistency.
13 Indicates transaction deadlock errors.
14 Indicates security-related errors, such as permission denied.
15 Indicates syntax errors in the Transact-SQL command.
16 Indicates general errors that can be corrected by the user.
17-19 Indicate software errors that cannot be corrected by the user. Inform your system administrator of the problem.
17 Indicates that the statement caused SQL Server to run out of resources (such as memory, locks, or disk space for the database) or to exceed some limit set by the system administrator.
18 Indicates a problem in the Database Engine software, but the statement completes execution, and the connection to the instance of the Database Engine is maintained. The system administrator should be informed every time a message with a severity level of 18 occurs.
19 Indicates that a nonconfigurable Database Engine limit has been exceeded and the current batch process has been terminated. Error messages with a severity level of 19 or higher stop the execution of the current batch. Severity level 19 errors are rare and must be corrected by the system administrator or your primary support provider. Contact your system administrator when a message with a severity level 19 is raised. Error messages with a severity level from 19 through 25 are written to the error log.
20-24 Indicate system problems and are fatal errors, which means that the Database Engine task that is executing a statement or batch is no longer running. The task records information about what occurred and then terminates. In most cases, the application connection to the instance of the Database Engine may also terminate. If this happens, depending on the problem, the application might not be able to reconnect.Error messages in this range can affect all of the processes accessing data in the same database and may indicate that a database or object is damaged. Error messages with a severity level from 19 through 24 are written to the error log.
20 Indicates that a statement has encountered a problem. Because the problem has affected only the current task, it is unlikely that the database itself has been damaged.
21 Indicates that a problem has been encountered that affects all tasks in the current database, but it is unlikely that the database itself has been damaged.
22 Indicates that the table or index specified in the message has been damaged by a software or hardware problem.Severity level 22 errors occur rarely. If one occurs, run DBCC CHECKDB to determine whether other objects in the database are also damaged. The problem might be in the buffer cache only and not on the disk itself. If so, restarting the instance of the Database Engine corrects the problem. To continue working, you must reconnect to the instance of the Database Engine; otherwise, use DBCC to repair the problem. In some cases, you may have to restore the database.

If restarting the instance of the Database Engine does not correct the problem, then the problem is on the disk. Sometimes destroying the object specified in the error message can solve the problem. For example, if the message reports that the instance of the Database Engine has found a row with a length of 0 in a nonclustered index, delete the index and rebuild it.

23 Indicates that the integrity of the entire database is in question because of a hardware or software problem.Severity level 23 errors occur rarely. If one occurs, run DBCC CHECKDB to determine the extent of the damage. The problem might be in the cache only and not on the disk itself. If so, restarting the instance of the Database Engine corrects the problem. To continue working, you must reconnect to the instance of the Database Engine; otherwise, use DBCC to repair the problem. In some cases, you may have to restore the database.
24 Indicates a media failure. The system administrator may have to restore the database. You may also have to call your hardware vendor.
Categories: Common Tags: ,

Could not continue scan with NOLOCK due to data movement

FIX: You receive error 605 and error 824 when you run a query that inserts data into a temporary table in SQL Server

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

Trace flag 4199 is added to control multiple query optimizer changes previously made under multiple trace flags

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

Categories: Common Tags: ,

Useful SQL Server Management Studio Keyboard Shortcuts

Categories: Common Tags: ,

server information include port #

http://smartypeeps.blogspot.com/2006/11/t-sql-script-to-find-nw-port-of-sql.html

declare @Server as varchar(128) 
declare @KeyToInterogate as varchar(200)
declare @Version as varchar (512) 
declare @PortNumber as varchar(8) 

set @Server = @@ServerName 
set @Version = left(@@Version, 38) 
set @KeyToInterogate = 'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP'

if charindex('\',@@ServerName) > 0
begin
set @KeyToInterogate = 'SOFTWARE\Microsoft\Microsoft SQL Server\'
set @KeyToInterogate = @KeyToInterogate + substring(@@ServerName,charindex('\',@@ServerName) + 1,len(@@ServerName) - charindex('\',@@ServerName)) 
set @KeyToInterogate = @KeyToInterogate + '\MSSQLServer\SuperSocketNetLib\Tcp'
end

exec xp_regread 
@rootkey = 'HKEY_LOCAL_MACHINE', 
@key = @KeyToInterogate, 
@value_name = 'TcpPort', 
@value = @PortNumber output 

exec master..xp_regread
                     'HKEY_LOCAL_MACHINE'
                    , 'SOFTWARE\Microsoft\Windows NT\CurrentVersion'
                    , 'ProductName'

exec master..xp_regread
                     'HKEY_LOCAL_MACHINE'
                    , 'SOFTWARE\Microsoft\Windows NT\CurrentVersion'
                    , 'CSDVersion'
                    
SELECT @@servername AS hostname
, SERVERPROPERTY('Edition') AS Edition
, ISNULL(SERVERPROPERTY('InstanceName'),'') AS InstanceName
, SERVERPROPERTY('MachineName') AS MachineName
, SERVERPROPERTY('ProductVersion') AS ProductVersion
, SERVERPROPERTY('ProductLevel') as ProductLevel 
, @@VERSION
, CASE 
WHEN CONVERT(VARCHAR(255),SERVERPROPERTY('ProductVersion')) LIKE '8.0%' THEN '2000'
WHEN CONVERT(VARCHAR(255),SERVERPROPERTY('ProductVersion')) LIKE '9.0%' THEN '2005'
WHEN CONVERT(VARCHAR(255),SERVERPROPERTY('ProductVersion')) LIKE '10.0%' THEN '2008'
WHEN CONVERT(VARCHAR(255),SERVERPROPERTY('ProductVersion')) LIKE '10.5%' THEN '2008 R2'
END
, cast(@PortNumber as varchar)
, (select top 1 local_net_address from sys.dm_exec_connections
where local_net_address is not null) as IP_address

SELECT
SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY('ProductLevel') AS ProductLevel,
SERVERPROPERTY('Edition') AS Edition,
SERVERPROPERTY('EngineEdition') AS EngineEdition;

Backup Size check including compression backup

use msdb

--select top 10 * from backupfile
--select top 10 * from backupmediaset
--select top 10 * from backupmediafamily

declare @sql nvarchar(4000)
if exists(select OBJECT_NAME(id) from syscolumns where name='compressed_backup_size') begin
	set @sql = '
	select top 1 @@servername as servername, case when sum(compressed_backup_size)convert(varchar(8),getdate()-30,112)
	group by convert(varchar(8), backup_start_date, 112)
	order by sum(backup_size) desc
	'
end else begin
	set @sql = '
	select top 1 @@servername as servername, ''N'' compressed_backup
	, convert(numeric(10,2),sum(backup_size/1024/1024/1024.0)) as [backup_size(GB)] from msdb.dbo.backupset with(nolock)
	where backup_start_date>convert(varchar(8),getdate()-30,112)
	group by convert(varchar(8), backup_start_date, 112)
	order by sum(backup_size) desc
	'
end

exec(@sql)

Remote Replication(Long distance Replication)

http://msdn.microsoft.com/en-us/library/aa237436%28v=sql.80%29.aspx

http://msdn.microsoft.com/en-us/library/ms151762.aspx

http://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=5&ved=0CGUQFjAE&url=http%3A%2F%2Fdownload.microsoft.com%2Fdownload%2Fd%2F9%2F4%2Fd948f981-926e-40fa-a026-5bfcf076d9b9%2FReplicationAndDBM.docx&ei=_6wqUJeCEdH1iQKl_YHADg&usg=AFQjCNGQ4F4LY3sb4jwY4iIvqUqCmT8NqA&sig2=NUGwh6APmlhMy5N89jW9HA&cad=rja

1. Most importance is Pull subscription.

Consider Pull or Anonymous Subscriptions

The Distribution or Merge Agent runs on the Distributor for push subscriptions, and on Subscribers for pull or anonymous subscriptions. Using pull or anonymous subscriptions can increase performance by moving Distribution or Merge Agent processing from the Distributor to Subscribers.

You can also offload agent processing by using Remote Agent Activation. Agent processing can be moved to the Subscriber for push subscriptions and to the Distributor for pull subscriptions. Administration of the agent still takes place at the Distributor for push subscriptions and at the Subscriber for pull subscriptions. For more information, see Remote Agent Activation.

Anonymous subscriptions, which are especially useful for Internet applications, do not require that information about the Subscriber be stored in the distribution database at the Distributor for transactional replication and reduces the storage of information about the Subscriber in the publishing database for merge replication. This reduces the resource demands on the Publisher and Distributor because they do not have to maintain information about anonymous Subscribers.

Anonymous subscriptions are a special category of pull subscriptions. In regular pull subscriptions, the Distribution or Merge Agent runs at the Subscriber (thereby reducing the resource demands on the Distributor), but still stores information at the Publisher. When a publication supports anonymous subscriptions, the publication is configured to always have a snapshot ready for new Subscribers.

For transactional replication, this means that every time the Snapshot Agent runs, a new snapshot will be generated. Typically, a snapshot is not generated if there are no new Subscribers waiting for a snapshot or no Subscriber needs to be reinitialized at the time the Snapshot Agent is run. So while anonymous Subscribers can reduce the resource demands at the Distributor, the tradeoff is that a snapshot is generated more often. With merge replication, a new snapshot is always generated when the Snapshot Agent runs regardless of the type of subscriptions supported by the publication.

2. Change default distribution agent setup.

http://msdn.microsoft.com/en-us/library/ms147328.aspx

 

3. Performance test

http://msdn.microsoft.com/en-us/library/dd263442.aspx

Performance Indicators Test Scenarios SQL Server 2005 on Windows Server 2003 (A) SQL Server 2008 on Windows Server 2008 (B) Performance Gains or Losses
 
[(A-B)/B]*100
CPU Utilization (%) All

15%

15%

0%

Memory All

99%

99%

0%

Push Replication 1-GB 226.12 (minutes) 110.42 (minutes)

104.78%

 
1,000,000 1k character records
Pull Replication 1-GB 174.87 (minutes) 12.5 (minutes)

1298.96%

 
1,000,000 1k character records
Linked Server 10-MB 107.6 (minutes) 113.6 (minutes) –5.28%
 
10,000 1k character records
Push Replication 112-MB 247.07 (minutes) 59.13 (minutes)

317.84%

 
100,000 varbinary (max) records
Pull Replication Records 112-MB 223.18 (minutes) 1.95 (minutes)

11345.13%

 
100,000 varbinary (max) records
Snapshot Replication 11.3-GB 10,100,000 1k records Not tested 22.75 (minutes) Comparison not available
Follow

Get every new post delivered to your Inbox.