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.

2013/08/05 1 comment

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'
Follow

Get every new post delivered to your Inbox.