tag:blogger.com,1999:blog-105507912024-02-18T20:37:18.924-08:00SQL Server Tips & TricksFree SQL Server Articles and Scripts.
*New: Learn sql server 2005 SeriesAneeshhttp://www.blogger.com/profile/14630205472188972293noreply@blogger.comBlogger255125tag:blogger.com,1999:blog-10550791.post-33664135493043137072015-09-01T11:57:00.001-07:002015-09-01T11:57:23.614-07:00Microsoft SQL Server Licensing - abstractMicrosoft SQL Server Licensing – abstract   Terms used: An OSE is an Operating System Environment. A POSE is a Physical Operating System Environment. A VOSE is a Virtual Operating System Environment. General rules SQL Server licenses are assigned only to physical servers. The number of SQL Server instances in an OSE is unlimited. A physical OSE is counted as Aneeshhttp://www.blogger.com/profile/14630205472188972293noreply@blogger.comtag:blogger.com,1999:blog-10550791.post-48396431781931962362015-07-27T10:45:00.001-07:002015-07-27T10:45:10.735-07:00What's the best PAGE_VERIFY settingI always set this value to CHECKSUM. When CHECKSUM is enabled for the PAGE_VERIFY database option, the SQL Server Database Engine calculates a checksum over the contents of the whole page, and stores the value in the page header when a page is written to disk. When the page is read from disk, the checksum is recomputed and compared to the checksum value that is stored in the page header. This Aneeshhttp://www.blogger.com/profile/14630205472188972293noreply@blogger.comtag:blogger.com,1999:blog-10550791.post-9176625975655887802015-07-27T10:44:00.001-07:002015-07-27T10:44:37.014-07:00SQL Max Memory Limit Too Low: Increase SQL server Maximum Memory from Command PromptSQL Server is well known for being a memory hog. When installed, the default configuration allocates a ridiculous amount of memory (2147483647 MB) to the instance. In effect, this grants SQL Server access to whatever memory is assigned to the server, which it will try to use at times at the expense of operating system tasks. Best practice is therefore to reduce the memory allocated to the Aneeshhttp://www.blogger.com/profile/14630205472188972293noreply@blogger.comtag:blogger.com,1999:blog-10550791.post-35608792968079600672015-03-24T13:41:00.005-07:002015-03-24T13:41:40.582-07:00How to recover space from a huge transaction log fileMost of the novice dba’s forget to take the transaction log backups, which eventually ends up consuming a lot of disk space. This is one of the questions people ask me very often. Here are the steps which I tell them to follow. Take the full backup if the database Check to see if something is keeping SQL from reusing existing log space: SELECT name, log_reuse_wait_desc FROM Aneeshhttp://www.blogger.com/profile/14630205472188972293noreply@blogger.comtag:blogger.com,1999:blog-10550791.post-48596538900932391822015-03-24T13:41:00.003-07:002015-03-24T13:41:27.350-07:00Backup database to disk = ‘Nul’To understand what this is doing, first we need to understand what Nul is, in the context of a file. It is not just a misspelling of NULL. DOS, since the earliest days, has had a number of devices that can be treated like files, eg LPT1, COM1 and CON. NUL is another of those devices (virtual files, if you like) and is equivalent to the unix \dev\nul, a ‘file’ that deletes any data that’s writtenAneeshhttp://www.blogger.com/profile/14630205472188972293noreply@blogger.comtag:blogger.com,1999:blog-10550791.post-82797746159313737872015-03-24T13:41:00.001-07:002015-03-24T13:41:10.858-07:00Identifying Unused DatabasesThis is one of the issues the consultant faces, when there client has little documentation on the systems and the databases they use.  The first step in this process is simply looking at the date & time stamps of the .MDF and .LDF files, which could help in some cases,To evaluate whether it was worth keeping the databases online, I had some investigation to do. Step 1 : Who is Aneeshhttp://www.blogger.com/profile/14630205472188972293noreply@blogger.comtag:blogger.com,1999:blog-10550791.post-67343608309091986622015-03-24T13:40:00.003-07:002015-03-24T13:40:38.617-07:00Difference between DateTime2, DateTime and SmallDateTimeHere are the main differences you should know about these three date types:   Range of Dates Accuracy Size usage Applies to smalldatetime January 1, 1900 - June 6, 2079 one Minute. smalldatetime values with 29.998 seconds or lower are rounded down to Aneeshhttp://www.blogger.com/profile/14630205472188972293noreply@blogger.comtag:blogger.com,1999:blog-10550791.post-29291333330268437332015-03-24T13:40:00.001-07:002015-03-24T13:40:28.062-07:00SQL Maintenance Plan error “Could not generate mail report.An exception occurred while executing a Transact-SQL statement or batch.No global profile is configured. Specify a profile name in the @profile_name parameter.”Some body have this error : Could not generate mail report.An exception occurred while executing a Transact-SQL statement or batch.No global profile is configured. Specify a profile name in the @profile_name parameter. When i execute my maintenance plan.   Resolution The issue is related to the default email profile, you have to set a default email profile in order for the maintenance Aneeshhttp://www.blogger.com/profile/14630205472188972293noreply@blogger.comtag:blogger.com,1999:blog-10550791.post-89291825198231123062013-12-02T14:38:00.001-08:002013-12-02T14:38:50.444-08:00SQL agent DCOM errors Event ID 10016  Here is the work around to resolve this error (Works on SQL 2008, SQL 2012 ) Click Start -> Run -> dcomcnfg Component services –> computers –> my computer –>DCOM config->MSDTSServer100     Right click and select Properties     Security     Launch and Activate permissions – edit Add the local SQL AgentUser and Aneeshhttp://www.blogger.com/profile/14630205472188972293noreply@blogger.comtag:blogger.com,1999:blog-10550791.post-17581603479089212372013-10-03T10:21:00.001-07:002013-10-03T10:21:06.441-07:00How to select max row value using TSqlSomeone asked me this question and it took me a while to figured out the solution. It look very simple with the new features available in SQL Server 2008. Here is the example     if object_id('tempdb..#temp') is not null drop table #temp create table #temp([Name] varchar(10),[m1] int,[m2] int,[m3] int,[m4] int) insert into #temp([Name],[m1],[m2],[m3],[m4])values('N1', 34, 36, Aneeshhttp://www.blogger.com/profile/14630205472188972293noreply@blogger.comtag:blogger.com,1999:blog-10550791.post-47750512607391497952013-10-01T08:09:00.001-07:002013-10-01T08:09:34.440-07:00SQL Server script–Backup historyHere is a very useful script that shows the backup history.   SELECT s.database_name, m.physical_device_name, CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize, s.backup_start_date, s.backup_finish_date, CAST(DATEDIFF(second, s.backup_start_date,s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken, s.backup_start_date, CASTAneeshhttp://www.blogger.com/profile/14630205472188972293noreply@blogger.comtag:blogger.com,1999:blog-10550791.post-12284608052801890722013-09-25T09:08:00.001-07:002013-09-25T09:08:06.453-07:00Pass a variable to a linked server queryWhen you query a linked server, you frequently perform a pass-through query that uses the OPENQUERY, OPENROWSET, or OPENDATASOURCE statement. You can view the examples in SQL Server Books Online to see how to do this by using pre-defined Transact-SQL strings, but there are no examples of how to pass a variable to these functions. This article provides three examples of how to pass a variable to aAneeshhttp://www.blogger.com/profile/14630205472188972293noreply@blogger.comtag:blogger.com,1999:blog-10550791.post-65783570023670719362013-09-18T12:21:00.001-07:002013-09-18T12:21:34.429-07:00SQL Server 2012–CHOOSE () functionToday I am starting my series on ‘What’s new in SQL 2012’ ; One of the interesting functions available is ‘CHOOSE’ which can be used to achieve some of the features of ‘CASE’ statement Syntax CHOOSE ( index, val_1, val_2 [, val_n ] )Arguments indexIs an integer expression that represents a 1-based index into the list of the items following it.If the provided index value has a numeric data Aneeshhttp://www.blogger.com/profile/14630205472188972293noreply@blogger.comtag:blogger.com,1999:blog-10550791.post-87248390360729103572013-09-10T09:09:00.001-07:002013-09-10T09:09:30.015-07:00Msg 8985, Level 16, State 1, Line 1 Could not locate file 'xxx_log' for database ‘xxx' in sys.database_files. The file either does not exist, or was dropped.This happened after doing and in-place upgrade to our existing SQL 2008R2 server. Happened only on one of our database which was on Simple recovery. It look strange to me when the SHRINKFILE command failed. When i compared the logical names on sys.master_files and sys.database_files, the both look different, I try to rename them back to the same file which of course didn't worked , so i thought Aneeshhttp://www.blogger.com/profile/14630205472188972293noreply@blogger.comtag:blogger.com,1999:blog-10550791.post-77559879011400078072013-09-06T11:14:00.001-07:002013-09-06T11:14:44.510-07:00SQL server 2012 upgrade Check list (Non Clustered environment)Here is the in place upgrade checklist I created for migrating from SQL 2008R2 Pre upgrade Checklist Make sure the hardware requirements are satisfied; use System Configuration Checker (SCC) that comes part of sql server setup to verify SQL Server 2012 requires Windows Server 2008 R2 SP1 or later Analyze the legacy sql server using SQL server 2012 Upgrade advisor Ensure that SQL Aneeshhttp://www.blogger.com/profile/14630205472188972293noreply@blogger.comtag:blogger.com,1999:blog-10550791.post-26941221787735182402013-08-15T09:42:00.000-07:002013-08-15T09:45:45.620-07:00SQL SERVER –Unused Index ScriptAs most of you know, an index can improve the performance of a query most of the time; internally sql server has to do a lot of work to maintain those indexes. When I started a new job, I came to see a lot of indexes were created for some of our production tables. So I decided to find a script that returns the indexes that’s not in use. Note : it uses the dmv’s so, this is not a pretty accurateAneeshhttp://www.blogger.com/profile/14630205472188972293noreply@blogger.comtag:blogger.com,1999:blog-10550791.post-43100487797918950092013-07-17T09:46:00.001-07:002013-07-17T14:49:06.418-07:00Error 17832 : The login packet used to open connection is structurally invalid; the connection has been closed. Please contact the vendor of the client library.[Client: xxx.xxx.xxx.xxx ]One of my client was experiencing this strange error while trying to logon to the sql server using windows authentication, he tried logging in from different machines still got the same error.   "ProviderNum: 7, Error: 10054, ErrorMessage: "TCP Provider: An existing connection was forcibly closed by the remote host ..." To resolve this problem, increase the MaxTokenSize Aneeshhttp://www.blogger.com/profile/14630205472188972293noreply@blogger.comtag:blogger.com,1999:blog-10550791.post-87201862232768720522013-07-10T08:43:00.001-07:002013-07-10T08:43:07.939-07:00Cannot shrink log file 2 because the logical log file located at the end of the file is in use.As a dba, several guys asked me this question on many occasions. All they want to do is free up some space from the transaction log file , and while running the DBCC SHRINKFILE() the above error shows up.  As you can see from the error, it does not shrink because the file is in use; So you have two options, either change the recovery to simple, shrink the file then change the recovery back Aneeshhttp://www.blogger.com/profile/14630205472188972293noreply@blogger.comtag:blogger.com,1999:blog-10550791.post-9912352429722776782013-06-26T14:49:00.001-07:002013-06-26T14:49:12.605-07:00Script to find database owners using T-SQL  Database owner information is stored in sys.databases view as owner_sid, in order to get the actual mapped name we need to use suser_sname() function 1: select name, suser_sname(owner_sid)as 'owner' from sys.databases<!--CRLF--> 2: select name, suser_sname(sid)'owner' from sysdatabases<!--CRLF--> Change the database Owner Microsoft provides system stored Aneeshhttp://www.blogger.com/profile/14630205472188972293noreply@blogger.comtag:blogger.com,1999:blog-10550791.post-89571522975089023972013-06-26T07:40:00.001-07:002013-06-26T07:40:58.466-07:00Download Microsoft SQL Server 2014 Community Technology Preview 1  Download SQL Server 2014 CTP1 and install it in your sandboxed environment – then receive deployment and operations guidance and other resources from the experts. Microsoft SQL Server 2014 brings to market new in-memory capabilities built into the core database, including in-memory OLTP, which complements our existing in-memory data warehousing and BI capabilities for the most Aneeshhttp://www.blogger.com/profile/14630205472188972293noreply@blogger.comtag:blogger.com,1999:blog-10550791.post-33007551095753894152013-05-24T13:36:00.001-07:002013-05-24T13:36:33.984-07:00Lost ‘SA’ password,If you ever lost a SA password, you may have thought your only option is to reinstall SQL and re-attach to the DB’s. However, SQL server provides a much better disaster recovery method which preserves objects and data in the master DB. Members of the server’s Local Administrator’s group can access SQL server by starting it in single-user mode. 1.Enable SQL Single-User Mode - Open SQL Server Aneeshhttp://www.blogger.com/profile/14630205472188972293noreply@blogger.comtag:blogger.com,1999:blog-10550791.post-62883064579983055582013-04-17T11:07:00.001-07:002013-04-17T11:07:40.582-07:00Query to find CPU pressureI have found this query to be a very reliable indicator of CPU pressure in SQL Server 2005 -- Check SQL Server Schedulers to see if they are waiting on CPU SELECT scheduler_id, current_tasks_count, runnable_tasks_count FROM sys.dm_os_schedulers WHERE scheduler_id < 255 It is much more useful in my opinion than conventional PerfMon indicators like % CPU utilization or Aneeshhttp://www.blogger.com/profile/14630205472188972293noreply@blogger.comtag:blogger.com,1999:blog-10550791.post-31844943143686777772013-04-17T11:05:00.001-07:002013-04-17T11:05:42.330-07:00Query to find affinity of my schedulers to CPUsselect scheduler_id,              CAST (cpu_id as varbinary) AS scheduler_affinity_mask from  sys.dm_os_schedulers Aneeshhttp://www.blogger.com/profile/14630205472188972293noreply@blogger.comtag:blogger.com,1999:blog-10550791.post-17559076782934705232013-04-17T10:50:00.001-07:002013-04-17T10:50:47.705-07:00What is SPID in SQL ServerSPID which stands for  Server Process ID  is essentially a session or a connection in SQL Server. Every time when the application connects to SQL Server, a new connection (or SPID) is created; this connection has a defined scope and memory space and cannot interact with other SPIDs.Each SPID can have it’s own connection settings. Connection settings can be defined by the connection Aneeshhttp://www.blogger.com/profile/14630205472188972293noreply@blogger.comtag:blogger.com,1999:blog-10550791.post-45028068860461684432013-04-10T14:21:00.001-07:002013-04-10T14:34:10.577-07:00Did DROP TABLE #temp actually deletes the temp table immediately inside a stored procedure
I use temp table on most of my stored procedures for storing the intermediate results, but there was one particular stored procedure some one else in my team created where he used a table variable; He was creating the table variable within a loop, populate the data and processed it, assuming that with each iteration, sql server is going to create another object, the query didn’t generate any Aneeshhttp://www.blogger.com/profile/14630205472188972293noreply@blogger.com